Diego Sevilla Ruiz created CASSANDRA-21836:
----------------------------------------------
Summary: A paged SELECT that intersects two SAI indexes with AND
times out when the consistency level requires more than one replica. Once the
data is on disk, the same query returns immediately at ONE or when it is not
paged.
Key: CASSANDRA-21836
URL: https://issues.apache.org/jira/browse/CASSANDRA-21836
Project: Apache Cassandra
Issue Type: Bug
Components: Feature/SAI
Reporter: Diego Sevilla Ruiz
Attachments: docker-compose.yml, reproduce.py, reproduce.sh
h3. Environment
* Cassandra 5.0.9 (Docker image cassandra:5.0.9), the latest 5.0 release on
2026-11-01
* 2 nodes, 16 tokens each, one datacenter, GossipingPropertyFileSnitch
* Keyspace with SimpleStrategy, RF = 2
* Python driver 3.30.1, and cqlsh
* Reproduced with both cassandra.yaml and cassandra_latest.yaml (BIG and BTI
formats)
h3. Steps to reproduce
The attached reproduce.sh runs the whole sequence with the attached
docker-compose.yml and reproduce.py, and exits with status 1 when the timeout
occurs. By hand:
{{CREATE KEYSPACE ks WITH replication = \{'class': 'SimpleStrategy',
'replication_factor': 2};}}
{{CREATE TABLE ks.q (id bigint, d timestamp, owner bigint, score int, title
text, PRIMARY KEY (id, d));}}
{{CREATE INDEX q_owner ON ks.q (owner) USING 'sai';}}
{{CREATE INDEX q_score ON ks.q (score) USING 'sai';}}
Insert 20,000 rows at LOCAL_QUORUM, with id = i, owner = i % 5000, score = i %
50, a fixed d and a short title. Wait for the indexes to be queryable and run
nodetool flush ks on both nodes. Then, in cqlsh:
{{CONSISTENCY LOCAL_QUORUM;}}
{{SELECT id FROM ks.q WHERE owner = 7 AND score >= 0;}}
h3. Expected
4 rows, as returned at ONE.
h3. Actual
{{ReadTimeout: Error from server: code=1200 [Coordinator node timed out waiting
for replica nodes' responses] message="Operation timed out - received only 0
responses." info=\{'consistency': 'ONE', 'required_responses': 1,
'received_responses': 0}}}
The error reports ONE although the query ran at LOCAL_QUORUM.
h3. Results
Synthetic data, randomly chosen owner values, LOCAL_QUORUM, page size 5000,
owner = ? AND score >= 0:
||Rows||Indexes created||Configuration||Result||
|100,000|after the load|cassandra.yaml|40 of 40 ReadTimeout|
|100,000|after the load|cassandra_latest.yaml|40 of 40 ReadTimeout|
|100,000|before the load|cassandra.yaml|40 of 40 ReadTimeout|
|20,000|before the load|cassandra.yaml|40 of 40 ReadTimeout|
|20,000|after the load|cassandra.yaml|40 of 40 ReadTimeout|
In the 100,000 and 20,000 row runs with cassandra.yaml, the same owners
returned correctly at ONE and at LOCAL_QUORUM without paging.
Real dataset (about 197,000 rows, cassandra_latest.yaml, query of the same
shape on owneruserid and score): 25 of 25 randomly chosen owners failed.
h3. Trace
The following sequence repeats, alternating between the two replicas, until the
timeout. Taken from the real dataset:
{{Executing read on stackoverflow.questions using indexes
questions_owneruserid_sai,questions_score_sai}}
{{Read 0 live rows and 0 tombstone cells}}
{{Index query accessed memtable indexes, 6 SSTable indexes, and 6 segments,
post-filtered 0 rows in 0 partitions, and took ...}}
{{Requesting 5000 extra rows from
Full(/172.20.0.2:7070,(8878228422593045259,-9114996066678673620]) for short
read protection}}
The requested range is the one that wraps around the ring. I do not know
whether that is significant.
h3. Other observations
Real dataset, same query shape:
||Variation||Result||
|LOCAL_QUORUM, QUORUM or TWO, paged|ReadTimeout|
|Page size 10|ReadTimeout|
|LIMIT 2|ReadTimeout|
|LIMIT 10|ReadTimeout, after 10 s instead of 5 s|
|ONE or LOCAL_ONE, paged|OK, a few milliseconds|
|LOCAL_QUORUM, not paged|OK, 0.8 s|
|Adding ALLOW FILTERING|ReadTimeout|
|Equality instead of a range: owneruserid = 120 AND score = 3|ReadTimeout|
|Both conditions, but no row matches both|OK|
|One SAI index plus a non-indexed column with ALLOW FILTERING|OK|
|One SAI index alone|OK|
Synthetic data, 20,000 rows, cqlsh at LOCAL_QUORUM:
||Query||Page size 100 or 5000||Paging off||
|owner = 7 AND score >= 0|ReadTimeout|OK, 4 rows|
|owner = 7 AND score = 7|OK, 4 rows|OK, 4 rows|
So equality did not fail on the synthetic data, where every row of an owner
matches it, but did fail on the real dataset, where 1 of the owner's 4 rows
matches. The cause of that difference has not been isolated.
Before a flush, on 20,000 rows held in memtables, the equality query returned
correctly. A separate test with partition key plus one SAI index also returned
correctly at LOCAL_QUORUM, with the data in memory and on disk.
h3. Not tested
* trunk or any version other than 5.0.9
* A single node or RF = 1
* More than two nodes
h3. Workaround
Use ONE or LOCAL_ONE, disable paging for the query, or avoid intersecting two
SAI indexes.
h3. Related
A web search found no ticket describing this loop. CASSANDRA-19007 covers
multi-column filtering on out-of-sync replicas and may be related.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
