UNION still not working with 1.0.2

classic Classic list List threaded Threaded
5 messages Options
pgarg pgarg
Reply | Threaded
Open this post in threaded view
|

UNION still not working with 1.0.2

asked by john smith

Here is the exception...

javax.cache.CacheException: Failed to parse query: SELECT "mycache".MyTrx._key, "mycache".MyTrx._val FROM MyTrx WHERE field1 = ? UNION SELECT * FROM MyTrx WHERE field2 = ?
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:782)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:742)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:553)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:465)
    at com.xxx.service.MyService.lambda$start$4(MyService.java:116)
    at com.xxx.service.MyService$$Lambda$41/1174190307.handle(Unknown Source)
    at io.vertx.core.eventbus.impl.EventBusImpl$HandlerRegistration.handle(EventBusImpl.java:1097)
    at io.vertx.core.eventbus.impl.EventBusImpl.lambda$doReceive$175(EventBusImpl.java:737)
    at io.vertx.core.eventbus.impl.EventBusImpl$$Lambda$67/468639332.handle(Unknown Source)
    at io.vertx.core.impl.ContextImpl.lambda$wrapTask$15(ContextImpl.java:263)
    at io.vertx.core.impl.ContextImpl$$Lambda$4/451111351.run(Unknown Source)
    at io.vertx.core.impl.OrderedExecutorFactory$OrderedExecutor.lambda$new$147(OrderedExecutorFactory.java:91)
    at io.vertx.core.impl.OrderedExecutorFactory$OrderedExecutor$$Lambda$2/1870252780.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.h2.jdbc.JdbcSQLException: Column count does not match; SQL statement: SELECT "mycache".MyTrx._key, "mycache".MyTrx._val FROM MyTrx WHERE field1 = ? UNION SELECT * FROM MyTrx WHERE field2 = ? [21002-175]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
    at org.h2.message.DbException.get(DbException.java:172)
    at org.h2.message.DbException.get(DbException.java:149)
    at org.h2.message.DbException.get(DbException.java:138)
    at org.h2.command.dml.SelectUnion.init(SelectUnion.java:264)
    at org.h2.command.Parser.parseSelect(Parser.java:1625)
    at org.h2.command.Parser.parsePrepared(Parser.java:426)
    at org.h2.command.Parser.parse(Parser.java:298)
    at org.h2.command.Parser.parse(Parser.java:270)
    at org.h2.command.Parser.prepareCommand(Parser.java:235)
    at org.h2.engine.Session.prepareLocal(Session.java:436)
    at org.h2.engine.Session.prepareCommand(Session.java:379)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:70)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:779)
    ... 15 more


The query...

String sqlStr = "SELECT * FROM MyTrx WHERE field1 = ? "
+ "UNION SELECT * FROM MyTrx WHERE field2 = ?";

SqlQuery sql = new SqlQuery(MyTrx.class, sqlStr);
sql.setArgs(trxRequest.getField1(), trxRequest.getField2());

List<Entry<Long, MyTrx>> list = myCache.query(sql.setSql(sqlStr)).getAll();



The model...

import org.apache.ignite.cache.query.annotations.QuerySqlField;

public class MyTrx implements Serializable {
    private static final long serialVersionUID = -2604193489375322501L;

    @QuerySqlField(index = true)
    Long id;

    @QuerySqlField(index = true)
    String field1;

    @QuerySqlField(index = true)
    String field2;
    ...
}


How's it configured...

IgniteConfiguration igniteCfg = new IgniteConfiguration();
igniteCfg.setMarshaller(new OptimizedMarshaller(true));

CacheConfiguration<Long, com.xxx.model.MyTrx> myCfg = new CacheConfiguration<>("mycache");
myCfg.setCacheMode(CacheMode.PARTITIONED);
myCfg.setAtomicityMode(CacheAtomicityMode.ATOMIC);
myCfg.setBackups(0);
myCfg.setIndexedTypes(Long.class, com.xxx.model.MyTrx.class);


-----
This post is migrated from now discontinued Apache Ignite forum at
http://apacheignite.readme.io/v1.0/discuss
pgarg pgarg
Reply | Threaded
Open this post in threaded view
|

Re: UNION still not working with 1.0.2

commented by john smith

This seems to work...

String sqlStr = "SELECT * FROM MyTrx WHERE fullName = ? "
+ "UNION SELECT \"mycache\".MyTrx._key, \"mycache\".MyTrx._val FROM MyTrx WHERE acctNumberHash = ?";


But I would assume that UNION SELECT * would be able to figure it out?

-----
This post is migrated from now discontinued Apache Ignite forum at
http://apacheignite.readme.io/v1.0/discuss

pgarg pgarg
Reply | Threaded
Open this post in threaded view
|

Re: UNION still not working with 1.0.2

commented by dmitriy setrakyan

select * works with SqlFieldsQuery which return individual columns, however, when doing just SqlQuery, which returns the whole objects, it is best to omit the select clause altogether, or, when used with unions, specify the exact fields.

-----
This post is migrated from now discontinued Apache Ignite forum at
http://apacheignite.readme.io/v1.0/discuss

pgarg pgarg
Reply | Threaded
Open this post in threaded view
|

Re: UNION still not working with 1.0.2

commented by john smith

Ok fine, but why does the query function replace for the first select but not subsequent ones? In RC3 it seemed to do so...

SELECT "mycache".MyTrx._key, "mycache".MyTrx._val FROM MyTrx WHERE field1 = ? UNION SELECT * FROM MyTrx WHERE field2 = ?

See on the second select it didn't replace. But in RC3 we I didn't have to do any magic, the magic happened by SqlQuery.

Just a thought.

-----
This post is migrated from now discontinued Apache Ignite forum at
http://apacheignite.readme.io/v1.0/discuss

pgarg pgarg
Reply | Threaded
Open this post in threaded view
|

Re: UNION still not working with 1.0.2

commented by dmitriy setrakyan

I will post this question to the dev list. However, in Ignite 1.0 you got a much richer SQL support, including automatic aggregations, group by, etc... (in RC3 you had to do the reduction step manually from Java code).

-----
This post is migrated from now discontinued Apache Ignite forum at
http://apacheignite.readme.io/v1.0/discuss