[CASSANDRA-12223] SASI Indexes querying incorrectly return 0 rows

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

Environment

Windows, DataStax Distribution

• Severity: Normal
• Assignee: Alex Petrov
• Reporter: Qiu Zhida

Authors

Alex Petrov

Reviewers

Pavel Yaskevich

• Created: 18/Jul/16 10:20
• Updated: 16/Apr/19 09:30
• Resolved: 17/Aug/16 21:41

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.