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

DELETE query with an empty IN clause can delete more than expected

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Fix Version/s: 3.0.11, 3.10
    • Component/s: Legacy/CQL
    • Labels:
      None
    • Environment:

      Arch Linux x64, kernel 4.7.6, Cassandra 3.9 downloaded from the website

    • Severity:
      Normal
    • Since Version:
      3.9

      Description

      When deleting from a table with a certain structure and using an in clause with an empty list, the in clause with an empty list can be ignored, resulting in deleting more than is expected.

      Setup:

      cqlsh> create table test (a text, b text, id uuid, primary key ((a, b), id));
      cqlsh> insert into test (a, b, id) values ('a', 'b', 00000000-0000-0000-0000-000000000000);
      cqlsh> insert into test (a, b, id) values ('b', 'c', 00000000-0000-0000-0000-000000000000);
      cqlsh> insert into test (a, b, id) values ('a', 'c', 00000000-0000-0000-0000-000000000000);
      cqlsh> select * from test;
      
       a | b | id
      ---+---+--------------------------------------
       a | c | 00000000-0000-0000-0000-000000000000
       b | c | 00000000-0000-0000-0000-000000000000
       a | b | 00000000-0000-0000-0000-000000000000
      
      (3 rows)
      

      Expected:

      cqlsh> delete from test where a = 'a' and b in ('a', 'b', 'c') and id in ();
      cqlsh> select * from test;
      
       a | b | id
      ---+---+--------------------------------------
       a | c | 00000000-0000-0000-0000-000000000000
       b | c | 00000000-0000-0000-0000-000000000000
       a | b | 00000000-0000-0000-0000-000000000000
      
      (3 rows)
      

      Actual:

      cqlsh> delete from test where a = 'a' and b in ('a', 'b', 'c') and id in ();
      cqlsh> select * from test;
      
       a | b | id
      ---+---+--------------------------------------
       b | c | 00000000-0000-0000-0000-000000000000
      
      (1 rows)
      

      Instead of deleting nothing, as the final empty in clause would imply, it instead deletes everything that matches the first two clauses, acting as if the following query had been issued instead:

      cqlsh> delete from test where a = 'a' and b in ('a', 'b', 'c');
      

      This seems to be related to the presence of a tuple clustering key, as I could not reproduce it without one.

        Attachments

          Activity

            People

            • Assignee:
              ifesdjeen Alex Petrov
              Reporter:
              jasontbradshaw Jason T. Bradshaw
              Authors:
              Alex Petrov
              Reviewers:
              Benjamin Lerer
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: