Re: Self Join Query As An Alternative To IN clause

[Alexei Scherbakov]

Hello,

You should pass array of values as a first argument:

Object[] params = new Object[] { new Object[] {"p1", "p2", ...} };

2016-06-06 13:19 GMT+03:00 pragmaticbigdata <amits...@gmail.com>:

> 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.
>



-- 

Best regards,
Alexei Scherbakov