Self Join Query As An Alternative To IN clause

[pragmaticbigdata]

I am using apache ignite 1.6. Executing an in clause query on a cache
containing 1 mil entries took around 1.5 seconds. As a performance
optimization suggested  here
<https://apacheignite.readme.io/docs/sql-queries>  , I tried out a join
clause query but query binding fails. 

        SqlFieldsQuery fieldsQuery = new SqlFieldsQuery("select p.name,
p.price, p.volume, p.discount, p.baseLine, p.uplift, p.FINAL_PRICE,
p.SALE_PRICE from ProductDetails p join table(" +
searchColumn.getColumnName() + " char = ?) i on " +
                "p." + searchColumn.getColumnName() + " = i." +
searchColumn.getColumnName());

        fieldsQuery.setArgs(values);  //values is of type Object[]
        List<Cache.Entry&lt;String, ProductDetails>> productDetails =
Lists.newArrayList();
        Collection<List&lt;?>> res =
productCache.query(fieldsQuery).getAll();

The above query fails with an JdbcSQLException: Invalid value "2" for
parameter "parameterIndex" [90008-175]. The complete trace is

Caused by: class org.apache.ignite.IgniteException: Failed to bind
parameters: [qry=select p.name, p.price, p.volume, p.discount, p.baseLine,
p.uplift, p.FINAL_PRICE, p.SALE_PRICE from ProductDetails p join table(NAME
char = ?) i on p.NAME = i.NAME, params=[phone10, phone100, phone101,
phone102, phone103, phone104, phone105, phone106, phone107, phone108,
phone109, phone1000]]
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:811)
        at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:691)
        ... 37 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to bind
parameters: [qry=select p.name, p.price, p.volume, p.discount, p.baseLine,
p.uplift, p.FINAL_PRICE, p.SALE_PRICE from ProductDetails p join table(NAME
char = ?) i on p.NAME = i.NAME, params=[phone10, phone100, phone101,
phone102, phone103, phone104, phone105, phone106, phone107, phone108,
phone109, phone1000]]
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1787)
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:804)
        ... 38 more
Caused by: javax.cache.CacheException: Failed to bind parameters:
[qry=select p.name, p.price, p.volume, p.discount, p.baseLine, p.uplift,
p.FINAL_PRICE, p.SALE_PRICE from ProductDetails p join table(NAME char = ?)
i on p.NAME = i.NAME, params=[phone10, phone100, phone101, phone102,
phone103, phone104, phone105, phone106, phone107, phone108, phone109,
phone1000]]
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1083)
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:806)
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:804)
        at
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1769)
        ... 39 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to bind
parameter [idx=2, obj=phone100, stmt=prep3: select p.name, p.price,
p.volume, p.discount, p.baseLine, p.uplift, p.FINAL_PRICE, p.SALE_PRICE from
ProductDetails p join table(NAME char = ?) i on p.NAME = i.NAME {1:
'phone10'}]
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.bindObject(IgniteH2Indexing.java:505)
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.bindParameters(IgniteH2Indexing.java:930)
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1080)
        ... 43 more
Caused by: org.h2.jdbc.JdbcSQLException: Invalid value "2" for parameter
"parameterIndex" [90008-175]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
        at org.h2.message.DbException.get(DbException.java:172)
        at
org.h2.message.DbException.getInvalidValueException(DbException.java:218)
        at
org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcPreparedStatement.java:1338)
        at
org.h2.jdbc.JdbcPreparedStatement.setObject(JdbcPreparedStatement.java:451)
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.bindObject(IgniteH2Indexing.java:502)
        ... 45 more

What could be missing?

Thanks.



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Self-Join-Query-As-An-Alternative-To-IN-clause-tp5448.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.