SQL IN Operator

classic Classic list List threaded Threaded
13 messages Options
Mirko Raner Mirko Raner
Reply | Threaded
Open this post in threaded view
|

SQL IN Operator

We need to perform some SqlQueries that use the "IN" operator to test membership in a set, i.e. WHERE name IN ("Alice", "Bob", "Carl").
Unfortunately, this generally seems to result in an exception:

javax.cache.CacheException: Failed to run reduce query locally.
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:553)
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:777)
        at org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:59)
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$3.iterator(IgniteH2Indexing.java:811)
        at org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:59)
        at org.apache.ignite.internal.processors.cache.QueryCursorImpl.getAll(QueryCursorImpl.java:71)

Is there a special trick as to how to initialize the query arguments, or is this generally not supported?
If this is indeed supported, can someone point me to a working example?
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

Mirko,

This query should work. Could you provide more details of what exactly you are doing (code snippet of how you execute the query, partitioned or replicated cache is used). Also I think the stack trace you've posted is missing cause, which is important to understand roots of this issue.

Sergi



2015-07-30 21:10 GMT+03:00 Mirko Raner <[hidden email]>:
We need to perform some SqlQueries that use the "IN" operator to test
membership in a set, i.e. WHERE name IN ("Alice", "Bob", "Carl").
Unfortunately, this generally seems to result in an exception:

javax.cache.CacheException: Failed to run reduce query locally.
        at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:553)
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:777)
        at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:59)
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$3.iterator(IgniteH2Indexing.java:811)
        at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:59)
        at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.getAll(QueryCursorImpl.java:71)

Is there a special trick as to how to initialize the query arguments, or is
this generally not supported?
If this is indeed supported, can someone point me to a working example?




--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-IN-Operator-tp779.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Mirko Raner Mirko Raner
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

Caused by:
javax.cache.CacheException: Failed to execute map query on the node: 4d469cd1-1448-4f4e-a1bc-65f1d746a504, class org.apache.ignite.IgniteCheckedException:Failed to execute SQL query.
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.fail(GridReduceQueryExecutor.java:209)
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onFail(GridReduceQueryExecutor.java:199)
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onMessage(GridReduceQueryExecutor.java:180)
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor$1.onMessage(GridReduceQueryExecutor.java:136)
        at org.apache.ignite.internal.managers.communication.GridIoManager$ArrayListener.onMessage(GridIoManager.java:1608)
        at org.apache.ignite.internal.managers.communication.GridIoManager.processRegularMessage0(GridIoManager.java:690)
        at org.apache.ignite.internal.managers.communication.GridIoManager.access$1500(GridIoManager.java:58)
        at org.apache.ignite.internal.managers.communication.GridIoManager$5.run(GridIoManager.java:653)
        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:744)

Unfortunately, that doesn't add much more detail to what exactly failed here...
I can't paste the code as it is owned by a client, but the problem seems clearly related to the use of the IN operator. Is it OK to pass the set parameter of the query as a java.util.Set? I couldn't find an actual example of a SqlQuery with an IN operator. Is there maybe some test code in the Ignite project that uses this feature and could serve as an example?
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

See my comments below.

Unfortunately, that doesn't add much more detail to what exactly failed
here...

Since it says that map query failed on node 4d469cd1-1448-4f4e-a1bc-65f1d746a504 you need to check logs on that node. I think there exception should be more informative.
 
I can't paste the code as it is owned by a client, but the problem seems
clearly related to the use of the IN operator.

If I don't know steps to reproduce the issue, I can't help you. I suggest you to write a simple artificial test reproducing the problem, it will save us lots of time. Most probably IN operator works correctly but you do something wrong.
 
Is it OK to pass the set
parameter of the query as a java.util.Set?

Again, I'm not sure what do you mean by that. How the query looks like? How do you set query arguments?
 
I couldn't find an actual example
of a SqlQuery with an IN operator. Is there maybe some test code in the
Ignite project that uses this feature and could serve as an example?


You can look at GridCacheCrossCacheQuerySelfTest, I modified some existing there queries to check if IN operator works correctly and it does. The problem is most probably in incorrect usage.

Sergi


 



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-IN-Operator-tp779p799.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Mirko Raner Mirko Raner
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

On Sat, Aug 1, 2015 at 12:53 PM, Sergi Vladykin [via Apache Ignite Users] <[hidden email]> wrote:
[...]
Most probably IN operator works correctly but you do something wrong.

You're probably right about that :-)
Unfortunately, we already restructured our code, and the logs are long gone at this point...

[...] 
How the query looks like? How do you set query arguments?
 
Essentially, what we were doing is this (pseudo-code):

  SqlQuery query = new SqlQuery(ResultType.class, "WHERE name IN ?");
  Set<String> names = getApplicableNames();
  query.setArgs(names);

In addition to sets, we tried arrays and java.util.List as well, but none of them seem to work.
I looked at GridCacheCrossCacheQuerySelfTest, but it's using SqlFieldsQuery not SqlQuery.

If I can find the time I'll try to put together a test case that demonstrates what we're doing and what we're expecting, should be a lot easier to tell where we're going wrong...

Thanks,

Mirko
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

Ok, I understand now. It is a quite common issue, syntax "WHERE name in ?" is wrong, you have to use exact list of parameters like this:

SqlQuery query = new SqlQuery(ResultType.class, "WHERE name IN (?,?,?)");
query.setArgs("name1", "name2", "name3");

Or create user defined  SQL function (see QuerySqlFunction annotation) which will accept Set of names, name and does set.contains(name) and call it like
"WHERE IN_SET(?, name)"

Sergi 

2015-08-04 1:32 GMT+03:00 Mirko Raner <[hidden email]>:
On Sat, Aug 1, 2015 at 12:53 PM, Sergi Vladykin [via Apache Ignite Users] <[hidden email]> wrote:
[...]
Most probably IN operator works correctly but you do something wrong.

You're probably right about that :-)
Unfortunately, we already restructured our code, and the logs are long gone at this point...

[...] 
How the query looks like? How do you set query arguments?
 
Essentially, what we were doing is this (pseudo-code):

  SqlQuery query = new SqlQuery(ResultType.class, "WHERE name IN ?");
  Set<String> names = getApplicableNames();
  query.setArgs(names);

In addition to sets, we tried arrays and java.util.List as well, but none of them seem to work.
I looked at GridCacheCrossCacheQuerySelfTest, but it's using SqlFieldsQuery not SqlQuery.

If I can find the time I'll try to put together a test case that demonstrates what we're doing and what we're expecting, should be a lot easier to tell where we're going wrong...

Thanks,

Mirko


View this message in context: Re: SQL IN Operator

Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Mirko Raner Mirko Raner
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

Thank you, Sergi.
That's exactly what we did wrong!
I can see some issues with this solution when there is a large number of set elements. Also, as you mentioned that it's a common issue, I'm wondering if it would make sense for Ignite to support the "IN ?" syntax with an array or collection as argument (either by translating it to the correct syntax under the hood, or by providing a predefined SQL function for this case?). Apparently, we are not the only ones who expected the "IN ?" syntax to work.
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

I know that it is a common misconception, but we use H2 database engine to process SQL queries, their position on the issue is that they don't want to support that until other databases do the same. Do you know any databases that support such a syntax?

Also there is another more effective workaround which opposite to IN operator can use indexes and supports variable length arrays:

select p._val from Person p, table(name varchar = ?) n where p.name =  n.name

and pass there array of names (String[]) as a parameter, but here you have to use SqlFieldsQuery.

Sergi

2015-08-04 17:44 GMT+03:00 Mirko Raner <[hidden email]>:
Thank you, Sergi.
That's exactly what we did wrong!
I can see some issues with this solution when there is a large number of set
elements. Also, as you mentioned that it's a common issue, I'm wondering if
it would make sense for Ignite to support the "IN ?" syntax with an array or
collection as argument (either by translating it to the correct syntax under
the hood, or by providing a predefined SQL function for this case?).
Apparently, we are not the only ones who expected the "IN ?" syntax to work.



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-IN-Operator-tp779p812.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

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

Re: SQL IN Operator

Sergey,

Having array-based queries is a very nice "secret" feature :) I actually was not aware of it.

Do you mind updating the documentation? Basically, just put some explanation and the example you have in this thread into the Sql Query section here:

D.

On Tue, Aug 4, 2015 at 11:19 AM, Sergi Vladykin <[hidden email]> wrote:
I know that it is a common misconception, but we use H2 database engine to process SQL queries, their position on the issue is that they don't want to support that until other databases do the same. Do you know any databases that support such a syntax?

Also there is another more effective workaround which opposite to IN operator can use indexes and supports variable length arrays:

select p._val from Person p, table(name varchar = ?) n where p.name =  n.name

and pass there array of names (String[]) as a parameter, but here you have to use SqlFieldsQuery.

Sergi


2015-08-04 17:44 GMT+03:00 Mirko Raner <[hidden email]>:
Thank you, Sergi.
That's exactly what we did wrong!
I can see some issues with this solution when there is a large number of set
elements. Also, as you mentioned that it's a common issue, I'm wondering if
it would make sense for Ignite to support the "IN ?" syntax with an array or
collection as argument (either by translating it to the correct syntax under
the hood, or by providing a predefined SQL function for this case?).
Apparently, we are not the only ones who expected the "IN ?" syntax to work.



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-IN-Operator-tp779p812.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.


Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

Dmitriy,

No problem, will do.

Sergi

2015-08-04 21:56 GMT+03:00 Dmitriy Setrakyan <[hidden email]>:
Sergey,

Having array-based queries is a very nice "secret" feature :) I actually was not aware of it.

Do you mind updating the documentation? Basically, just put some explanation and the example you have in this thread into the Sql Query section here:

D.


On Tue, Aug 4, 2015 at 11:19 AM, Sergi Vladykin <[hidden email]> wrote:
I know that it is a common misconception, but we use H2 database engine to process SQL queries, their position on the issue is that they don't want to support that until other databases do the same. Do you know any databases that support such a syntax?

Also there is another more effective workaround which opposite to IN operator can use indexes and supports variable length arrays:

select p._val from Person p, table(name varchar = ?) n where p.name =  n.name

and pass there array of names (String[]) as a parameter, but here you have to use SqlFieldsQuery.

Sergi


2015-08-04 17:44 GMT+03:00 Mirko Raner <[hidden email]>:
Thank you, Sergi.
That's exactly what we did wrong!
I can see some issues with this solution when there is a large number of set
elements. Also, as you mentioned that it's a common issue, I'm wondering if
it would make sense for Ignite to support the "IN ?" syntax with an array or
collection as argument (either by translating it to the correct syntax under
the hood, or by providing a predefined SQL function for this case?).
Apparently, we are not the only ones who expected the "IN ?" syntax to work.



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-IN-Operator-tp779p812.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.



Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator


2015-08-05 11:15 GMT+03:00 Sergi Vladykin <[hidden email]>:
Dmitriy,

No problem, will do.

Sergi

2015-08-04 21:56 GMT+03:00 Dmitriy Setrakyan <[hidden email]>:
Sergey,

Having array-based queries is a very nice "secret" feature :) I actually was not aware of it.

Do you mind updating the documentation? Basically, just put some explanation and the example you have in this thread into the Sql Query section here:

D.


On Tue, Aug 4, 2015 at 11:19 AM, Sergi Vladykin <[hidden email]> wrote:
I know that it is a common misconception, but we use H2 database engine to process SQL queries, their position on the issue is that they don't want to support that until other databases do the same. Do you know any databases that support such a syntax?

Also there is another more effective workaround which opposite to IN operator can use indexes and supports variable length arrays:

select p._val from Person p, table(name varchar = ?) n where p.name =  n.name

and pass there array of names (String[]) as a parameter, but here you have to use SqlFieldsQuery.

Sergi


2015-08-04 17:44 GMT+03:00 Mirko Raner <[hidden email]>:
Thank you, Sergi.
That's exactly what we did wrong!
I can see some issues with this solution when there is a large number of set
elements. Also, as you mentioned that it's a common issue, I'm wondering if
it would make sense for Ignite to support the "IN ?" syntax with an array or
collection as argument (either by translating it to the correct syntax under
the hood, or by providing a predefined SQL function for this case?).
Apparently, we are not the only ones who expected the "IN ?" syntax to work.



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-IN-Operator-tp779p812.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.




Alexey Kuznetsov Alexey Kuznetsov
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

Sergi,

Maybe add a color block? (see on same page  "Closing Cursors" blue block).

On Wed, Aug 5, 2015 at 4:32 PM, Sergi Vladykin <[hidden email]> wrote:

2015-08-05 11:15 GMT+03:00 Sergi Vladykin <[hidden email]>:
Dmitriy,

No problem, will do.

Sergi

2015-08-04 21:56 GMT+03:00 Dmitriy Setrakyan <[hidden email]>:
Sergey,

Having array-based queries is a very nice "secret" feature :) I actually was not aware of it.

Do you mind updating the documentation? Basically, just put some explanation and the example you have in this thread into the Sql Query section here:

D.


On Tue, Aug 4, 2015 at 11:19 AM, Sergi Vladykin <[hidden email]> wrote:
I know that it is a common misconception, but we use H2 database engine to process SQL queries, their position on the issue is that they don't want to support that until other databases do the same. Do you know any databases that support such a syntax?

Also there is another more effective workaround which opposite to IN operator can use indexes and supports variable length arrays:

select p._val from Person p, table(name varchar = ?) n where p.name =  n.name

and pass there array of names (String[]) as a parameter, but here you have to use SqlFieldsQuery.

Sergi


2015-08-04 17:44 GMT+03:00 Mirko Raner <[hidden email]>:
Thank you, Sergi.
That's exactly what we did wrong!
I can see some issues with this solution when there is a large number of set
elements. Also, as you mentioned that it's a common issue, I'm wondering if
it would make sense for Ignite to support the "IN ?" syntax with an array or
collection as argument (either by translating it to the correct syntax under
the hood, or by providing a predefined SQL function for this case?).
Apparently, we are not the only ones who expected the "IN ?" syntax to work.



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-IN-Operator-tp779p812.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.







--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: SQL IN Operator

Alexey,

May be you are right. By the way "Closing Cursors" documentation does not seem to be correct, cursors are getting automatically closed only when getAll() method is called :) I will fix that.

Sergi

2015-08-05 13:47 GMT+03:00 Alexey Kuznetsov <[hidden email]>:
Sergi,

Maybe add a color block? (see on same page  "Closing Cursors" blue block).

On Wed, Aug 5, 2015 at 4:32 PM, Sergi Vladykin <[hidden email]> wrote:

2015-08-05 11:15 GMT+03:00 Sergi Vladykin <[hidden email]>:
Dmitriy,

No problem, will do.

Sergi

2015-08-04 21:56 GMT+03:00 Dmitriy Setrakyan <[hidden email]>:
Sergey,

Having array-based queries is a very nice "secret" feature :) I actually was not aware of it.

Do you mind updating the documentation? Basically, just put some explanation and the example you have in this thread into the Sql Query section here:

D.


On Tue, Aug 4, 2015 at 11:19 AM, Sergi Vladykin <[hidden email]> wrote:
I know that it is a common misconception, but we use H2 database engine to process SQL queries, their position on the issue is that they don't want to support that until other databases do the same. Do you know any databases that support such a syntax?

Also there is another more effective workaround which opposite to IN operator can use indexes and supports variable length arrays:

select p._val from Person p, table(name varchar = ?) n where p.name =  n.name

and pass there array of names (String[]) as a parameter, but here you have to use SqlFieldsQuery.

Sergi


2015-08-04 17:44 GMT+03:00 Mirko Raner <[hidden email]>:
Thank you, Sergi.
That's exactly what we did wrong!
I can see some issues with this solution when there is a large number of set
elements. Also, as you mentioned that it's a common issue, I'm wondering if
it would make sense for Ignite to support the "IN ?" syntax with an array or
collection as argument (either by translating it to the correct syntax under
the hood, or by providing a predefined SQL function for this case?).
Apparently, we are not the only ones who expected the "IN ?" syntax to work.



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-IN-Operator-tp779p812.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.







--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com