Free form queries broken in 1.0?

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

Free form queries broken in 1.0?

This post was updated on .
asked by john smith

I had the following query...

String sqlStr = "SELECT * FROM MyTrx WHERE fullName ='" + myRequest.getFullName() + "' ";

I know I don't have to do long form for simple query like above, but I'm trying to debug a longer query that uses UNION. So reduced my query to the shortest possible query...

MyModel...

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

    @QuerySqlField(index = true)
    Long id;

    @QuerySqlField(index = true)
    String fullName;
}


The config...

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


And I get...

Apr 07, 2015 3:38:54 PM org.apache.ignite.logger.java.JavaLogger error
SEVERE: Failed to execute local query: GridQueryRequest [reqId=1, pageSize=1024, space=myCache, qrys=[GridCacheSqlQuery [qry=SELECT
    "myCache".MYTRX._KEY __C0,
    "myCache".MYTRX._VAL __C1
FROM "myCache".MYTRX
WHERE SELECT
    MYTRX._KEY,
    MYTRX._VAL,
    MYTRX.ID,
    MYTRX.FULLNAME
FROM "myCache".MYTRX
WHERE FULLNAME = 'johnsmith', params=[]]]]
class org.apache.ignite.IgniteCheckedException: Failed to execute SQL query.
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:597)
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:615)
        at org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:201)
        at org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:117)
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:353)
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:294)
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:714)
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:805)
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:742)
        at org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:534)
        at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:439)
        at com.xxx.service.myCache.lambda$start$4(myCache.java:113)
        at com.xxx.service.myCache$$Lambda$48/1471131776.handle(Unknown Source)
        at io.vertx.core.eventbus.impl.EventBusImpl$HandlerRegistration.handle(EventBusImpl.java:1097)
        at io.vertx.core.eventbus.impl.EventBusImpl.lambda$doReceive$110(EventBusImpl.java:737)
        at io.vertx.core.eventbus.impl.EventBusImpl$$Lambda$69/2022112580.handle(Unknown Source)
        at io.vertx.core.impl.ContextImpl.lambda$wrapTask$3(ContextImpl.java:263)
        at io.vertx.core.impl.ContextImpl$$Lambda$4/400136488.run(Unknown Source)
        at io.vertx.core.impl.OrderedExecutorFactory$OrderedExecutor.lambda$new$180(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: Data conversion error converting "(1, Transaction [id=1, fullName=johnsmith], 1, johnsmith)"; SQL statement:
SELECT
    "myCache".MYTRX._KEY __C0,
    "myCache".MYTRX._VAL __C1
FROM "myCache".MYTRX
WHERE SELECT
    MYTRX._KEY,
    MYTRX._VAL,
    MYTRX.ID,
    MYTRX.FULLNAME
FROM "myCache".MYTRX
WHERE FULLNAME = 'johnsmith' [22018-175]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
        at org.h2.message.DbException.get(DbException.java:161)
        at org.h2.value.Value.convertTo(Value.java:878)
        at org.h2.value.Value.getBoolean(Value.java:387)
        at org.h2.expression.Expression.getBooleanValue(Expression.java:180)
        at org.h2.command.dml.Select.queryFlat(Select.java:529)
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:632)
        at org.h2.command.dml.Query.query(Query.java:297)
        at org.h2.command.dml.Query.query(Query.java:284)
        at org.h2.command.dml.Query.query(Query.java:36)
        at org.h2.command.CommandContainer.query(CommandContainer.java:91)
        at org.h2.command.Command.executeQuery(Command.java:196)
        at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:106)
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:594)
        ... 22 more
Caused by: java.lang.NumberFormatException
        at java.math.BigDecimal.<init>(BigDecimal.java:550)
        at java.math.BigDecimal.<init>(BigDecimal.java:383)
        at java.math.BigDecimal.<init>(BigDecimal.java:806)
        at org.h2.value.Value.convertTo(Value.java:825)
        ... 33 more


-----
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: Free form queries broken in 1.0?

commented by dmitriy setrakyan

John, this is very strange. Can you please provide the a few lines of code showing how you execute the query?

Also, I know 1.0 had problems with Unions which will be fixed in a couple of days, but the query you provided should work fine

-----
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: Free form queries broken in 1.0?

commented by dmitriy setrakyan

John, I just reproduced your issue. Looks like right now select * works only with SqlFieldsQuery. If you remove select * from your query, this will work:

List<Entry<Long, MyTrx>> list = cache.query(new SqlQuery<Long, MyTrx>(MyTrx.class,
    "FROM MyTrx WHERE fullName ='johnsmith'")).getAll());


You can also pass the name as the JDBC parameter:

List<Entry<Long, MyTrx>> list = cache.query(new SqlQuery<Long, MyTrx>(MyTrx.class,
    "FROM MyTrx WHERE fullName = ?").setArgs("johnsmith")).getAll());


-----
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: Free form queries broken in 1.0?

commented by john smith

Hi Dimitri the reason I use free for query or long for is because of UNION. OR queries are dog slow with H2 indexes. I guess a work around would be to run multiple queries in parallel. I'm also wondering if this will also increase perfoamnce :) I guess I can do this by providing closures for each query and let compute engine call them in parallel

-----
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: Free form queries broken in 1.0?

commented by dmitriy setrakyan

John, all the options you are suggesting are valid. Note, if you use compute engine to send the jobs you can run local queries on the node the job is executing on over the data set cached on that node only. To run queries locally, you have to set Query.setLocal(true) flag.

The Unions will be fixed shortly in the upcoming release (sorry for the inconvenience). We have added automatic aggregations into SQL which accidentally affected the Unions. New tests have been added and the fix is coming.

-----
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: Free form queries broken in 1.0?

commented by dmitriy setrakyan

Just re-read my last comment and realized I probably needed some punctuation there :)

What I meant to say is that the compute grid can be used to send a closure to remote nodes. Then, when a closure arrives on some node, you can issue any query locally on that node by turning on the Query.setLocal(true) flag.

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