[CASSANDRA-18994] SAI range query does not play together with "IN"

• Type: Bug
• Status: Resolved
• Priority: Normal
• Resolution: Fixed
• Fix Version/s: 5.0-beta1, 5.0
• Component/s: Feature/SAI
• Epic Link: Storage Attached Indexes (Phase 2)
• Bug Category: Correctness
• Severity: Low
• Complexity: Low Hanging Fruit
• Discovered By: Adhoc Test
• Platform: All

Test and Documentation Plan

CI runs for this fix are in the comment section

• Assignee: Mike Adamson
• Reporter: Stefan Miklosovic

Authors

Mike Adamson

Reviewers

Caleb Rackliffe, Maxwell Guo

• Created: 02/Nov/23 13:18
• Updated: 05/Jan/24 22:23
• Resolved: 24/Nov/23 16:46

Description

I am using schema from the website's quickstart.

cqlsh> DESCRIBE KEYSPACE cycling ;

CREATE KEYSPACE cycling WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true;

CREATE TABLE cycling.cyclist_semi_pro (
    id int PRIMARY KEY,
    affiliation text,
    age int,
    country text,
    firstname text,
    lastname text,
    registration date
) WITH additional_write_policy = '99p'
    AND allow_auto_snapshot = true
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND cdc = false
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND memtable = 'default'
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND extensions = {}
    AND gc_grace_seconds = 864000
    AND incremental_backups = true
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99p';

CREATE CUSTOM INDEX age_sai_idx ON cycling.cyclist_semi_pro (age) USING 'StorageAttachedIndex';

CREATE CUSTOM INDEX country_sai_idx ON cycling.cyclist_semi_pro (country) USING 'StorageAttachedIndex' WITH OPTIONS = {'ascii': 'true', 'case_sensitive': 'false', 'normalize': 'true'};

CREATE CUSTOM INDEX lastname_sai_idx ON cycling.cyclist_semi_pro (lastname) USING 'StorageAttachedIndex' WITH OPTIONS = {'ascii': 'true', 'case_sensitive': 'false', 'normalize': 'true'};

CREATE CUSTOM INDEX registration_sai_idx ON cycling.cyclist_semi_pro (registration) USING 'StorageAttachedIndex';

Then I do:

cqlsh> SELECT * FROM cycling.cyclist_semi_pro WHERE lastname in ('Cantona', 'Boyd');
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

cqlsh> SELECT * FROM cycling.cyclist_semi_pro WHERE lastname in ('Cantona', 'Boyd') ALLOW FILTERING;

 id | affiliation     | age | country | firstname | lastname | registration
----+-----------------+-----+---------+-----------+----------+--------------
  5 |   Como Velocità |  24 |     ITA |     Irene |  Cantona |   2012-07-22
 20 | London Cyclists |  18 |     GBR |    Leslie |     Boyd |   2012-12-15

But check this:

cqlsh> SELECT * FROM cycling.cyclist_semi_pro WHERE registration > '2010-01-01' AND registration < '2015-12-31' and lastname in ('Cantona', 'Boyd') allow filtering;

ReadFailure: Error from server: code=1300 [Replica(s) failed to execute read] message="Operation failed - received 0 responses and 1 failures: UNKNOWN from localhost/127.0.0.1:7000" info={'consistency': 'ONE', 'required_responses': 1, 'received_responses': 0, 'failures': 1, 'error_code_map': {'127.0.0.1': '0x0000'}}

and in the logs:

java.lang.AssertionError: null
	at org.apache.cassandra.index.sai.plan.Expression.add(Expression.java:171)
	at org.apache.cassandra.index.sai.plan.Operation.buildIndexExpressions(Operation.java:136)
	at org.apache.cassandra.index.sai.plan.Operation$AndNode.analyze(Operation.java:303)
	at org.apache.cassandra.index.sai.plan.Operation$Node.doTreeAnalysis(Operation.java:266)
	at org.apache.cassandra.index.sai.plan.Operation$Node.analyzeTree(Operation.java:251)
	at org.apache.cassandra.index.sai.plan.Operation.buildIterator(Operation.java:185)
	at org.apache.cassandra.index.sai.plan.StorageAttachedIndexSearcher$ResultRetriever.<init>(StorageAttachedIndexSearcher.java:151)
	at org.apache.cassandra.index.sai.plan.StorageAttachedIndexSearcher.search(StorageAttachedIndexSearcher.java:107)
	at org.apache.cassandra.db.ReadCommand.executeLocally(ReadCommand.java:431)
	at org.apache.cassandra.service.StorageProxy$LocalReadRunnable.runMayThrow(StorageProxy.java:2184)
	at org.apache.cassandra.service.StorageProxy$DroppableRunnable.run(StorageProxy.java:2581)
	at org.apache.cassandra.concurrent.ExecutionFailure$2.run(ExecutionFailure.java:163)
	at org.apache.cassandra.concurrent.SEPWorker.run(SEPWorker.java:143)

If it is not supported it should not throw and it should print appropriate message.

If it is supported I guess this is a bug?

Issue Links

• duplicates: CASSANDRA-18166 Improve the code model around IndexContext
• Is contained by: CASSANDRA-18166 Improve the code model around IndexContext
• relates to: CASSANDRA-19018 An SAI-specific mechanism to ensure consistency isn't violated for multi-column (i.e. AND) queries at CL > ONE