[jira] [Created] (CASSANDRA-9970) CQL 3.3 ordering in where clause produces different results when using "IN...ORDER BY"

["Marc Zbyszynski (JIRA)" <ji...@apache.org>]

Marc Zbyszynski created CASSANDRA-9970:
------------------------------------------

             Summary: CQL 3.3 ordering in where clause produces different results when using "IN...ORDER BY"
                 Key: CASSANDRA-9970
                 URL: https://issues.apache.org/jira/browse/CASSANDRA-9970
             Project: Cassandra
          Issue Type: Bug
          Components: API
            Reporter: Marc Zbyszynski
            Priority: Minor


This is an issue I found with
{code}
[cqlsh 5.0.1 | Cassandra 2.2.0 | CQL spec 3.3.0 | Native protocol v4]
{code}

Given this table and data:

{code:sql}
cqlsh> CREATE TABLE myschema.in_test (
   ...     col_1 int,
   ...     col_2 int,
   ...     col_3 int,
   ...     PRIMARY KEY ((col_1, col_2), col_3)
   ... );
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 1, 1);
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 1, 2);
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 1, 13);
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 2, 10);
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 2, 11);
{code}

This query behaves as expected (as described in [http://docs.datastax.com/en/cql/3.3/cql/cql_using/useQueryIN.html])

{code:sql}
cqlsh> select * from myschema.in_test where col_1=1 and col_2 IN (1,2) order by col_3 desc;
InvalidRequest: code=2200 [Invalid query] message="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"
{code}

But if you swap the order of the statements in the where clause, the query executes without error and doesn't totally honor the _order by_ clause:

{code:sql}
cqlsh> select * from myschema.in_test where col_2 IN (1,2) and col_1=1 order by col_3 desc;

 col_1 | col_2 | col_3
-------+-------+-------
     1 |     2 |    11
     1 |     2 |    10
     1 |     1 |    13
     1 |     1 |     2
     1 |     1 |     1

(5 rows)
{code}

Apologies in advance if this is working as intended, or I'm duplicating an existing issue. Also please let me know if you need any additional info.




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)