Cassandra 3.9, 4 nodes, rf=3
Hi folks, we're see 0 results returned from queries that (a) should return
results, and (b) will return results with minor tweaks.
I've attached the sanitized trace outputs for the following 3 queries (pk1
and pk2 are partition keys, ck1 is clustering key, val1 is SASI indexed
non-key column):
Q1: SELECT * FROM t1 WHERE pk1 = 2017 AND pk2 = 11 AND ck1 >=
'2017-03-16' AND ck1 <= '2017-03-17' AND val1 LIKE 'abcdefgh%' LIMIT 1001
ALLOW FILTERING;
Q1 works - it returns a list of records, one of which has
val1='abcdefghijklmn'.
Q2: SELECT * FROM t1 WHERE pk1 = 2017 AND pk2 = 11 AND ck1 >=
'2017-03-16' AND ck1 <= '2017-03-17' AND val1 LIKE 'abcdefghi%' LIMIT
1001 ALLOW FILTERING;
Q2 does not work - 0 results returned. Only difference to Q1 is one
additional character provided in LIKE comparison.
Q3: SELECT * FROM t1 WHERE pk1 = 2017 AND pk2 = 11 AND ck1 >=
'2017-03-16' AND ck2 <= '2017-03-17' AND val1 = 'abcdefghijklmn' LIMIT
1001 ALLOW FILTERING;
Q3 does not work - 0 results returned.
As I've written above, the data set *does* include a record with
val1='abcdefghijklmn'.
Confounding the issue is that this behavior is inconsistent. For different
values of val1, I'll have scenarios where Q3 works, but Q1 and Q2 do not.
Now, that particular behavior I could explain with index/like problems, but
it is Q3 that sometimes does not work and that's a simply equality
comparison (although still using the index).
Further confounding the issue is that if my testers run these same queries
with the same parameters tomorrow, they're likely to work correctly.
Only thing I've been able to glean from tracing execution is that the
queries that work follow "Executing read..." with "Executing single
partition query on t1" and so forth, whereas the queries that don't work
simply follow "Executing read..." with "Read 0 live and 0 tombstone cells"
with no actual work seemingly done. But that's not helping me narrow this
down much.
Thanks for your time - appreciate any help.
Results found query (which include record where val='abcdefghijklmn'):
Parsing SELECT * FROM t1 WHERE pk1 = 2017 AND pk2 = 11 AND ck1 >=
'2017-03-16' AND ck1 <= '2017-03-17' AND val1 LIKE 'abcdefgh%' LIMIT 1001
ALLOW FILTERING; [Native-Transport-Requests-1]
Preparing
statement [Native-Transport-Requests-1]
Index
mean cardinalities are idx_my_idx:-9223372036854775808. Scanning with
idx_my_idx. [Native-Transport-Requests-1]
Computing ranges to
query [Native-Transport-Requests-1]
Submitting range
requests on 1 ranges with a concurrency of 1 (-1.08086395E16 rows per range
expected) [Native-Transport-Requests-1]
Submitted 1 concurrent range
requests [Native-Transport-Requests-1]
Executing read on keyspace.t1
using index idx_my_idx [ReadStage-2]
Executing
single-partition query on t1 [ReadStage-2]
Acquiring
sstable references [ReadStage-2]
Key cache
hit for sstable 2223 [ReadStage-2]
Skipped 34/35 non-slice-intersecting sstables, included 1
due to tombstones [ReadStage-2]
Key cache
hit for sstable 2221 [ReadStage-2]
Merged data from
memtables and 2 sstables [ReadStage-2]
Read 1 live and
0 tombstone cells [ReadStage-2]
Request complete
No results query:
Parsing SELECT * FROM t1 WHERE pk1 = 2017 AND pk2 = 11 AND ck1 >=
'2017-03-16' AND ck1 <= '2017-03-17' AND val1 LIKE 'abcdefghi%' LIMIT 1001
ALLOW FILTERING; [Native-Transport-Requests-1]
Preparing
statement [Native-Transport-Requests-1]
Index mean cardinalities are idx_my_idx:-9223372036854775808. Scanning with
idx_my_idx. [Native-Transport-Requests-1]
Computing ranges to
query [Native-Transport-Requests-1]
Submitting range
requests on 1 ranges with a concurrency of 1 (-1.08086395E16 rows per range
expected) [Native-Transport-Requests-1]
Submitted 1 concurrent range
requests [Native-Transport-Requests-1]
Executing read on keyspace.t1
using index idx_my_idx [ReadStage-2]
Read 0 live
and 0 tombstone cells [ReadStage-2]
Request complete
No results query:
Parsing SELECT * FROM t1 WHERE pk1 = 2017 AND pk2 = 11 AND ck1 >=
'2017-03-16' AND ck2 <= '2017-03-17' AND val1 = 'abcdefghijklmn' LIMIT 1001
ALLOW FILTERING; [Native-Transport-Requests-1]
Preparing
statement [Native-Transport-Requests-1]
Index mean cardinalities are idx_my_idx:-9223372036854775808. Scanning with
idx_my_idx. [Native-Transport-Requests-1]
Computing ranges to
query [Native-Transport-Requests-1]
Submitting range
requests on 1 ranges with a concurrency of 1 (-1.08086395E16 rows per range
expected) [Native-Transport-Requests-1]
Submitted 1 concurrent range
requests [Native-Transport-Requests-1]
Executing read on keyspace.t1
using index idx_my_idx [ReadStage-2]
Read 0 live
and 0 tombstone cells [ReadStage-2]
Request complete
