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

Wrong order returned when querying ttl(value) and using SELECT IN + ORDER BY

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Normal
    • Resolution: Duplicate
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      Tested with 3 node cluster on these versions:

      C* 2.0.16.678
      C* 2.1.8.689
      C* 2.1.9.791

    • Severity:
      Normal

      Description

      Setup a 3 node cluster, use these steps to create a keyspace, table and load data:

      CREATE KEYSPACE testksp
      WITH replication =

      {'class': 'SimpleStrategy', 'replication_factor':3}


      AND durable_writes = true;

      use testksp;

      CREATE TABLE test (
      useruid varchar,
      direction varchar,
      last_modified timestamp,
      value varchar,
      PRIMARY KEY ((useruid, direction), last_modified)
      );

      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.010+00:00', 'a value1') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.020+00:00', 'a value2') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'in', '2015-09-18T14:11:15.030+00:00', 'a value3') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'in', '2015-09-18T14:11:15.040+00:00', 'a value4') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.050+00:00', 'a value5') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.060+00:00', 'a value6') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'in', '2015-09-18T14:11:15.070+00:00', 'a value7') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'in', '2015-09-18T14:11:15.080+00:00', 'a value8') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.090+00:00', 'a value9') USING TTL 86400;
      INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.100+00:00', 'a value10') USING TTL 86400;

      Running this query with ttl(value) shows this message:

      cqlsh:testksp> SELECT useruid, value,ttl(value),last_modified,blobAsBigint(timestampAsBlob(last_modified)) FROM test WHERE useruid= 'userid2' AND direction IN ('out', 'in') ORDER BY last_modified;

      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"

      Turning off paging returns the result:

      cqlsh:testksp> paging off;
      Disabled Query paging.

      cqlsh:testksp> SELECT useruid, value,ttl(value),last_modified,blobAsBigint(timestampAsBlob(last_modified)) FROM test WHERE useruid= 'userid2' AND direction IN ('out', 'in') ORDER BY last_modified;

      useruid | value | ttl(value) | last_modified | blobAsBigint(timestampAsBlob(last_modified))
      ------------------------------------------------------------------------------------------------
      userid2 | a value1 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475010
      userid2 | a value2 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475020
      userid2 | a value5 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475050
      userid2 | a value6 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475060
      userid2 | a value9 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475090
      userid2 | a value3 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475030
      userid2 | a value4 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475040
      userid2 | a value7 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475070
      userid2 | a value8 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475080
      userid2 | a value10 | 86277 | 2015-09-18 14:11:15+0000 | 1442585475100

      (10 rows)

      Notice value1 and value10 start and finish, but the other rows are in the wrong order. Without ttl(value) the results are returned value1 to value10 in the correct order.

      Running the same test using C* 2.0.16.678 there is no need to turn off paging. The results are returned with the wrong order when querying with ttl(value).

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                blerer Benjamin Lerer
                Reporter:
                sbassi Sucwinder Bassi
                Authors:
                Benjamin Lerer
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: