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

SASI Indexes querying incorrectly return 0 rows

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Fix Version/s: 3.10
    • Component/s: Feature/SASI
    • Labels:
      None
    • Environment:

      Windows, DataStax Distribution

    • Severity:
      Normal

      Description

      I just started working with the SASI index on Cassandra 3.7.0 and I encountered a problem which as I suspected was a bug. I had hardly tracked down the situation in which the bug showed up, here is what I found:

      When querying with a SASI index, it may incorrectly return 0 rows, and changing a little conditions, it works again, like the following CQL code:

      CQL
          CREATE TABLE IF NOT EXISTS roles (
              name text,
              a int,
              b int,
              PRIMARY KEY ((name, a), b)
          ) WITH CLUSTERING ORDER BY (b DESC);
          
          insert into roles (name,a,b) values ('Joe',1,1);
          insert into roles (name,a,b) values ('Joe',2,2);
          insert into roles (name,a,b) values ('Joe',3,3);
          insert into roles (name,a,b) values ('Joe',4,4);
      
          CREATE TABLE IF NOT EXISTS roles2 (
              name text,
              a int,
              b int,
              PRIMARY KEY ((name, a), b)
          ) WITH CLUSTERING ORDER BY (b ASC);
          
          insert into roles2 (name,a,b) values ('Joe',1,1);
          insert into roles2 (name,a,b) values ('Joe',2,2);
          insert into roles2 (name,a,b) values ('Joe',3,3);
          insert into roles2 (name,a,b) values ('Joe',4,4);
      
          CREATE CUSTOM INDEX ON roles (b) USING 'org.apache.cassandra.index.sasi.SASIIndex' 
          WITH OPTIONS = { 'mode': 'SPARSE' };
          CREATE CUSTOM INDEX ON roles2 (b) USING 'org.apache.cassandra.index.sasi.SASIIndex' 
          WITH OPTIONS = { 'mode': 'SPARSE' };
      

      Noticing that I only change table roles2 from table roles's 'CLUSTERING ORDER BY (b DESC)' into 'CLUSTERING ORDER BY (b ASC)'.

      When querying with statement select * from roles2 where b<3, the rusult is two rows:

      CQL
           name | a | b
          ------+---+---
            Joe | 1 | 1
            Joe | 2 | 2
      
          (2 rows)
      

      However, if querying with select * from roles where b<3, it returned no rows at all:

      CQL
           name | a | b
          ------+---+---
      
          (0 rows)
      

      This is not the only situation where the bug would show up, one time I created a SASI index with specific name like 'end_idx' on column 'end', the bug showed up, when I didn't specify the index name, it gone.

        Attachments

          Activity

            People

            • Assignee:
              ifesdjeen Alex Petrov
              Reporter:
              qewryt132465 Qiu Zhida
              Authors:
              Alex Petrov
              Reviewers:
              Pavel Yaskevich
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: