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

Inconsistent query results for different sort orders (ORDER BY)

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Normal
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: Legacy/Core, Legacy/CQL
    • Labels:
      None
    • Environment:

      Cassandra 3.5 on Windows Server 2016. Cluster is geo-replicated with 120 machines in 3 datacenters  (40 in each). Replication factor for the table is 3. 

    • Severity:
      Normal
    • Since Version:
      3.5

      Description

      Cassandra returns inconsistent  (different) results for the following queries which only have sort orders specified in ORDER BY clause different. Table is activities_v2 which has PK as (userid, lastmodified, activityid). Clustering key lastmodified has clustering order DESC. Data types for key columns  are (userId, Text), (lastModified, Timestamp), (activityid, UUID). 

      Query "select activityid, lastmodified from activity_feed.activities_v2 where userid = 'userid' and  (lastmodified, activityid) >= ('2018-04-01T07:29:52.611Z', e3afc72e-c41c-3e01-5397-0d972f6ced71) and (lastmodified, activityid) < ('2018-06-01T07:29:52.611Z', ffffffff-ffff-ffff-ffff-ffffffffffff) ORDER BY lastmodified ASC, activityid DESC;" returns 81 rows as seen below.

      This query returns 

       activityid | lastmodified
      -------------------------------------+--------------------------------
      a5358a6d-669d-3acd-221b-43e62e9adae0 | 2018-04-01 07:29:52.830000+0000
      11f3bc30-4522-dcb1-8dea-a883e2e9514d | 2018-04-01 07:29:53.189000+0000
      cd430200-de20-a614-7269-e9d7528e9cf9 | 2018-04-01 07:29:53.392000+0000
      c78a3000-8c9d-c6ad-1df5-c275c6410175 | 2018-04-01 07:29:53.611000+0000
      e93cde33-e2e0-a2f4-0083-4a1314e83364 | 2018-04-01 07:29:53.845000+0000
      6289441d-2485-6345-5c0a-a06f6b571fab | 2018-04-01 07:31:53.524000+0000
      8b4e8d1c-91c6-c549-a548-d87a297deb9c | 2018-04-01 07:31:53.758000+0000
      abe73ae9-0d7a-bcb5-13dc-b61f555c7e8a | 2018-04-01 07:31:53.993000+0000
      c386e284-c2ee-d511-6122-d6d410e95e34 | 2018-04-01 07:40:26.443000+0000
      54b4c120-042d-1e27-ebd9-85824160c9ea | 2018-04-01 07:40:26.443000+0000
      c386e284-c2ee-d511-6122-d6d410e95e34 | 2018-04-01 07:40:26.662000+0000
      544f224e-9ba1-d4a2-5554-c1a34bbf5fa4 | 2018-04-01 07:40:28.914000+0000
      b90ea728-5fb2-6047-d8ed-f64e6268a45e | 2018-04-01 07:40:28.914000+0000
      963b308c-4c37-39df-4a0b-829026f3d054 | 2018-04-01 07:40:28.914000+0000
      963b308c-4c37-39df-4a0b-829026f3d054 | 2018-04-01 07:40:29.414000+0000
      b90ea728-5fb2-6047-d8ed-f64e6268a45e | 2018-04-01 07:40:29.726000+0000
      586dac57-fa58-9243-de1e-ceefb297dcd9 | 2018-04-06 21:22:33.500000+0000

      ....

      (81 rows)

      When sort order changed in ORDER BY clause as "select activityid, lastmodified from activity_feed.activities_v2 where userid = 'userid' and  (lastmodified, activityid) >= ('2018-04-01T07:29:52.611Z', e3afc72e-c41c-3e01-5397-0d972f6ced71) and (lastmodified, activityid) < ('2018-06-01T07:29:52.611Z', ffffffff-ffff-ffff-ffff-ffffffffffff) ORDER BY lastmodified DESC, activityid ASC;", query returns 142 rows as shown below.

       

      activityid | lastmodified
      -------------------------------------+--------------------------------
      3e2ab8b7-5619-79bf-c992-73d98a1ea9f6 | 2018-04-20 00:54:55.908000+0000
      3073f667-5c9f-4bb7-99fb-5e84c219ac91 | 2018-04-20 00:23:49.899000+0000
      a3e375df-85a5-4e4f-bd74-73743efe1836 | 2018-04-20 00:23:49.805000+0000
      3f91d870-34de-434a-9329-909d66cae0b4 | 2018-04-20 00:23:43.915000+0000
      24179d47-4637-4c8d-a818-1b2b1752f790 | 2018-04-20 00:23:05.445000+0000
      15bf9cb7-4e67-4cf6-818b-3713215dda32 | 2018-04-20 00:23:05.257000+0000
      46c0a730-58c9-42f5-adfd-60261446b9e3 | 2018-04-20 00:23:02.788000+0000
      24ecbe40-1990-4a43-a503-e6bf1ce537fb | 2018-04-19 23:44:15.339000+0000
      833a2351-7fb7-2723-36d9-932f07a9bf1b | 2018-04-19 23:41:57.131000+0000
      9158316d-022f-4150-94e4-229cbff777dc | 2018-04-19 23:41:00.129000+0000
      848d5c2e-af22-4235-a6b8-4dcab83f44e5 | 2018-04-19 23:40:38.377000+0000
      5646edc6-2f91-4e41-96b2-224bd74c7244 | 2018-04-19 23:37:42.294000+0000
      e81c24ff-8b6f-49b3-bdea-d1e178126716 | 2018-04-19 23:27:27.524000+0000
      85d137ce-d88b-4d4a-bace-c85ffdccc137 | 2018-04-19 23:20:45.682000+0000
      c5c09a5d-4ffa-4a80-91b8-c44af30f0741 | 2018-04-19 23:20:07.587000+0000
      c3fc0958-5efb-4555-acca-07e7755cf8cf | 2018-04-19 23:19:55.368000+0000

      ...

      (142 rows)

       

      In both cases, queries ran with Local_Quorum. 

       

       

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              mahars80 Mahmut Arslan
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: