Secondary index range query in cassandra

Question

I am using cassandra 2.1.10. So first, I will clear that I know that the secondary index is an anti-pattern in cassandra. But for testing purposes, I tried:

CREATE TABLE test_topology1.tt ( a text PRIMARY KEY, b timestamp ) WITH 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'} AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'} 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 = '99.0PERCENTILE'; CREATE INDEX idx_tt ON test_topology1.tt (b); 

When I run the following query, it gives me an error.

 cqlsh:test_topology1> Select * from tt where b>='2016-04-29 18:00:00' ALLOW FILTERING; InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'b >= <value>'" 

whereas Blog says that you can enable filtering to query the secondary index. Cassandra installs on a Windows machine.

Answer 1

Range queries in secondary index columns are not allowed in Kassandra up to 2.2.x. However, since post Looks back at the CQL WHERE clause , they are allowed for non-indexed columns if filtering is used:

Direct queries on secondary indexes support only =, CONTAINS or CONTAINS KEY restrictions.

[..]

Secondary index queries allow you to restrict the returned results using the constraints =,>,> =, <= and <, CONTAINS and CONTAINS KEY on non-indexed columns using filtering.

So, given the table structure and index

 CREATE TABLE test_secondary_index ( a text PRIMARY KEY, b timestamp, c timestamp ); CREATE INDEX idx_inequality_test ON test_secondary_index (b); 

The following query fails because the inequality test runs on an indexed column:

 SELECT * FROM test_secondary_index WHERE b >= '2016-04-29 18:00:00' ALLOW FILTERING ; InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'b >= <value>'" 

But the following works because the inequality test runs on an unindexed column:

 SELECT * FROM test_secondary_index WHERE b = '2016-04-29 18:00:00' AND c >= '2016-04-29 18:00:00' ALLOW FILTERING ; a | b | c ---+---+--- (0 rows) 

This still works if you add another index to column c , but also still requires the term ALLOW FILTERING , which for me means that the index in column c is not used in this scenario.

Answer 2

DOES range query works with secondary index using CUSTOM FILTRATION

 cqlsh:spark_demo> create table tt ( ... a text PRIMARY KEY, ... b timestamp ... ); cqlsh:spark_demo> CREATE INDEX ON tt(b); cqlsh:spark_demo> SELECT * FROM tt WHERE b >= '2016-03-01 12:00:00+0000'; InvalidRequest: code=2200 [Invalid query] message="No supported secondary index found for the non primary key columns restrictions" cqlsh:spark_demo> SELECT * FROM tt WHERE b >= '2016-03-01 12:00:00+0000' ALLOW FILTERING; a | b ---+--- (0 rows) cqlsh:spark_demo> 

Answer 3

This will give you the desired results. Use b as a clustering column.

CREATE TABLE test_topology1.tt (text, b timestamp, PRIMARY KEY (a, b))

select * from tt, where b> = '2016-04-29 18:00:00' enable filtering;