Uploaded image for project: 'Cassandra'
  1. Cassandra
  2. CASSANDRA-9970

CQL 3.3 ordering in where clause produces different results when using "IN...ORDER BY"

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Low
    • Resolution: Fixed
    • Fix Version/s: 2.2.1, 3.0 beta 1
    • Component/s: Legacy/CQL
    • Labels:
      None
    • Severity:
      Low
    • Since Version:

      Description

      This is an issue I found with

      [cqlsh 5.0.1 | Cassandra 2.2.0 | CQL spec 3.3.0 | Native protocol v4]
      

      Given this table and data:

      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);
      

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

      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"
      

      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:

      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)
      

      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.

        Attachments

        1. dtest-CASSANDRA-9970.txt
          1 kB
          Benjamin Lerer
        2. 9970.txt
          3 kB
          Benjamin Lerer

          Activity

            People

            • Assignee:
              blerer Benjamin Lerer
              Reporter:
              marrrck Marc Zbyszynski
              Authors:
              Benjamin Lerer
              Reviewers:
              Stefania Alborghetti
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: