SASI Index with Mode SPARSE seems not work without partition key #11
Description
I have a cluster setup with 3 nodes. followed the instruction on page: https://docs.datastax.com/en/dse/5.1/cql/cql/cql_using/useSASIIndex.html
cqlsh> desc table cycling.comments;
CREATE TABLE cycling.comments (
id uuid,
created_at timestamp,
comment text,
commenter text,
record_id timeuuid,
PRIMARY KEY (id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC)
AND bloom_filter_fp_chance = 0.01
AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND comment = ''
AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND crc_check_chance = 1.0
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair_chance = 0.0
AND speculative_retry = '99PERCENTILE';
CREATE CUSTOM INDEX fn_sparse ON cycling.comments (created_at) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'SPARSE'};
qlsh> select * from cycling.comments;
id | created_at | comment | commenter | record_id
--------------------------------------+---------------------------------+-----------------------------------------------+-----------+--------------------------------------
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.016000+0000 | LATE RIDERS SHOULD NOT DELAY THE START | Alex | 357e9fd0-4000-11ea-9e2e-bfebcec25eb6
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-03-21 21:11:09.999000+0000 | Second rest stop was out of water | Alex | 357ddc80-4000-11ea-9e2e-bfebcec25eb6
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-02-14 20:43:20.234000+0000 | Raining too hard should have postponed | Alex | 357c7cf0-4000-11ea-9e2e-bfebcec25eb6
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-02-14 20:43:20.000000+0000 | Raining too hard should have postponed | Alex | 35706f00-4000-11ea-9e2e-bfebcec25eb6
c7fceba0-c141-4207-9494-a29f9809de6f | 2020-01-26 05:53:43.053000+0000 | The gift certificate for winning was the best | Amy | 35888ae0-4000-11ea-9e2e-bfebcec25eb6
c7fceba0-c141-4207-9494-a29f9809de6f | 2017-04-01 13:43:08.030000+0000 | Last climb was a killer | Amy | 358be640-4000-11ea-9e2e-bfebcec25eb6
c7fceba0-c141-4207-9494-a29f9809de6f | 2017-03-22 01:16:59.001000+0000 | Great snacks at all reststops | Amy | 358ad4d0-4000-11ea-9e2e-bfebcec25eb6
c7fceba0-c141-4207-9494-a29f9809de6f | 2017-02-17 08:43:20.234000+0000 | Glad you ran the race in the rain | Amy | 3589c360-4000-11ea-9e2e-bfebcec25eb6
8566eb59-07df-43b1-a21b-666a3c08c08a | 2020-01-26 05:53:43.085000+0000 | Fastest womens time ever way to go amy! | Maryanne | 358d45d1-4000-11ea-9e2e-bfebcec25eb6
8566eb59-07df-43b1-a21b-666a3c08c08a | 2017-04-14 11:16:52.009000+0000 | Not bad for a flatlander | Maryanne | 35913d70-4000-11ea-9e2e-bfebcec25eb6
8566eb59-07df-43b1-a21b-666a3c08c08a | 2017-03-20 21:45:10.101000+0000 | Saggers really rocked it | Maryanne | 358fdde0-4000-11ea-9e2e-bfebcec25eb6
8566eb59-07df-43b1-a21b-666a3c08c08a | 2017-02-13 17:20:17.020000+0000 | Great race on a crappy day | Maryanne | 358e7e50-4000-11ea-9e2e-bfebcec25eb6
fb372533-eb95-4bb4-8685-6ef61e994caa | 2020-01-26 05:53:43.118000+0000 | Great course | Michael | 35924ee1-4000-11ea-9e2e-bfebcec25eb6
fb372533-eb95-4bb4-8685-6ef61e994caa | 2017-04-07 19:21:14.001000+0000 | Thanks for waiting for me! | Michael | 35992cb0-4000-11ea-9e2e-bfebcec25eb6
fb372533-eb95-4bb4-8685-6ef61e994caa | 2017-03-22 09:19:44.060000+0000 | Awesome race glad you held it anyway | Michael | 3595d150-4000-11ea-9e2e-bfebcec25eb6
fb372533-eb95-4bb4-8685-6ef61e994caa | 2017-03-17 03:43:01.030000+0000 | Getting read for the race | Michael | 3594bfe0-4000-11ea-9e2e-bfebcec25eb6
fb372533-eb95-4bb4-8685-6ef61e994caa | 2017-02-16 02:22:11.000000+0000 | Some entries complain a lot | Michael | 3593d580-4000-11ea-9e2e-bfebcec25eb6
9011d3be-d35c-4a8d-83f7-a3c543789ee7 | 2020-01-26 05:53:43.176000+0000 | Can't wait for the next race | Katarzyna | 359b2881-4000-11ea-9e2e-bfebcec25eb6
9011d3be-d35c-4a8d-83f7-a3c543789ee7 | 2017-01-01 17:20:17.020000+0000 | Gearing up for the seaon | Katarzyna | 359c6100-4000-11ea-9e2e-bfebcec25eb6
5b6962dd-3f90-4c93-8f61-eabfa4a803e2 | 2020-01-26 05:53:43.189000+0000 | Thanks for all your hard work | Marianne | 359d2451-4000-11ea-9e2e-bfebcec25eb6
220844bf-4860-49d6-9a4b-6b5d3a79cbfb | 2020-01-26 05:53:43.195000+0000 | A for effort! | Paolo | 359e0eb1-4000-11ea-9e2e-bfebcec25eb6
c4b65263-fe58-4846-83e8-f0e1c13d518f | 2020-01-26 05:53:43.204000+0000 | Closing ceremony was a little lame | Rossella | 359f6e41-4000-11ea-9e2e-bfebcec25eb6
38ab64b6-26cc-4de9-ab28-c257cf011659 | 2020-01-26 05:53:43.213000+0000 | Next time guys! | Marcia | 35a0cdd1-4000-11ea-9e2e-bfebcec25eb6
38ab64b6-26cc-4de9-ab28-c257cf011659 | 2017-02-11 14:09:56.000000+0000 | First race was amazing, can't wait for more | Marcia | 35a365e0-4000-11ea-9e2e-bfebcec25eb6
6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 2020-01-26 05:53:43.242000+0000 | So many great races thanks y'all | Steven | 35a53aa1-4000-11ea-9e2e-bfebcec25eb6
6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 2017-04-05 17:01:00.003000+0000 | Bike damaged in transit bummer | Steven | 35a8bd10-4000-11ea-9e2e-bfebcec25eb6
6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 2017-02-02 01:49:00.002000+0000 | Best of luck everybody I can't make it | Steven | 35a64c10-4000-11ea-9e2e-bfebcec25eb6
e7cd5752-bc0d-4157-a80f-7523add8dbcd | 2020-01-26 05:53:43.269000+0000 | Go team, you rocked it | Anna | 35a95951-4000-11ea-9e2e-bfebcec25eb6
6d5f1663-89c0-45fc-8cfd-60a373b01622 | 2020-01-26 05:53:43.273000+0000 | Next year the tour of california! | Melissa | 35a9f591-4000-11ea-9e2e-bfebcec25eb6
95addc4c-459e-4ed7-b4b5-472f19a67995 | 2020-01-26 05:53:43.276000+0000 | Next year for sure! | Vera | 35aa6ac1-4000-11ea-9e2e-bfebcec25eb6
95addc4c-459e-4ed7-b4b5-472f19a67995 | 2017-02-13 17:40:16.123000+0000 | I can do without the rain@@@@ | Vera | 36d0e960-4000-11ea-9e2e-bfebcec25eb6
The following queries do not work even with Consistency level ALL
cqlsh> SELECT * FROM cycling.comments where created_at >'2017-02-14 20:43:20.000';
id | created_at | comment | commenter | record_id
----+------------+---------+-----------+-----------
(0 rows)
cqlsh> SELECT * FROM cycling.comments where created_at <'2017-02-14 20:43:20.000';
id | created_at | comment | commenter | record_id
----+------------+---------+-----------+-----------
(0 rows)
Have to provide partition key:
cqlsh> SELECT * FROM cycling.comments where id=e7ae5cf3-d358-4d99-b900-85902fda9bb0 and created_at >'2017-02-14 20:43:20.000';
id | created_at | comment | commenter | record_id
--------------------------------------+---------------------------------+----------------------------------------+-----------+--------------------------------------
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.016000+0000 | LATE RIDERS SHOULD NOT DELAY THE START | Alex | 357e9fd0-4000-11ea-9e2e-bfebcec25eb6
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-03-21 21:11:09.999000+0000 | Second rest stop was out of water | Alex | 357ddc80-4000-11ea-9e2e-bfebcec25eb6
(2 rows)