query contains IN on the partition key and an ORDER BY

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

query contains IN on the partition key and an ORDER BY

Abhishek Singh Bailoo
Hi

I have run into the following issue https://issues.apache.org/jira/browse/CASSANDRA-6722 when running a query (contains IN on the partition key and an ORDER BY ) using datastax driver for Java.

However, I am able to run this query alright in cqlsh.

cqlsh:> show version;
[cqlsh 5.0.1 | Cassandra 2.1.2 | CQL spec 3.2.0 | Native protocol v3]

cqlsh:gps> select * from log where imeih in ('862170011627815@2015-01-29@03','862170011627815@2015-01-30@21','862170011627815@2015-01-30@04') and dtime < '2015-01-30 23:59:59' order by dtime desc limit 1;

The same query when run via datastax Java driver gives the following error:

Exception in thread "main" com.datastax.driver.core.exceptions.InvalidQueryException: Cannot page queries with both ORDER BY and a IN restriction on the partition key; you must either remove the ORDER BY or the IN and sort client side, or disable paging for this query
    at com.datastax.driver.core.exceptions.InvalidQueryException.copy(InvalidQueryException.java:35)

Any ideas?

Thanks,
Abhishek.
Reply | Threaded
Open this post in threaded view
|

Re: query contains IN on the partition key and an ORDER BY

Robert Wille-2
Bag the IN clause and execute multiple parallel queries instead. It’s more performant anyway.

On May 2, 2015, at 11:46 AM, Abhishek Singh Bailoo <[hidden email]> wrote:

Hi

I have run into the following issue https://issues.apache.org/jira/browse/CASSANDRA-6722 when running a query (contains IN on the partition key and an ORDER BY ) using datastax driver for Java.

However, I am able to run this query alright in cqlsh.

cqlsh:> show version;
[cqlsh 5.0.1 | Cassandra 2.1.2 | CQL spec 3.2.0 | Native protocol v3]

cqlsh:gps> select * from log where imeih in ('862170011627815@2015-01-29@03','862170011627815@2015-01-30@21','862170011627815@2015-01-30@04') and dtime < '2015-01-30 23:59:59' order by dtime desc limit 1;

The same query when run via datastax Java driver gives the following error:

Exception in thread "main" com.datastax.driver.core.exceptions.InvalidQueryException: Cannot page queries with both ORDER BY and a IN restriction on the partition key; you must either remove the ORDER BY or the IN and sort client side, or disable paging for this query
    at com.datastax.driver.core.exceptions.InvalidQueryException.copy(InvalidQueryException.java:35)

Any ideas?

Thanks,
Abhishek.