Cassandra DB: Why less than query failed?

Question

I have created a KEYSPACE and a TABLE with a uuid column as primary key and a timestamp column using an index. All this succeeded like the following picture showed:

cassandra@cqlsh:my_keyspace> insert into my_test ( id, insert_time, value ) values ( uuid(), '2015-03-12 09:10:30', '111' );
cassandra@cqlsh:my_keyspace> insert into my_test ( id, insert_time, value ) values ( uuid(), '2015-03-12 09:20:30', '222' );
cassandra@cqlsh:my_keyspace> select * from my_test;

 id                                   | insert_time              | value
--------------------------------------+--------------------------+-------
 9d7f88bc-5cb9-463f-b679-fd66e6469eb5 | 2015-03-12 09:20:30+0000 |   222
 69579f6f-bf88-493b-a1d6-2f89fac25650 | 2015-03-12 09:10:30+0000 |   111

(2 rows)

and now query

cassandra@cqlsh:my_keyspace> select * from my_test where insert_time = '2015-03-12 09:20:30';

 id                                   | insert_time              | value
--------------------------------------+--------------------------+-------
 9d7f88bc-5cb9-463f-b679-fd66e6469eb5 | 2015-03-12 09:20:30+0000 |   222

(1 rows)

and now query with less than:

cassandra@cqlsh:my_keyspace> select * from my_test where insert_time < '2015-03-12 09:20:30';
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'insert_time < <value>'"

while the first query is successful, why this happened? How should I make the second query successful since that's just what I want?

You can test all this on your own machine. Thanks

CREATE TABLE my_test (
    id uuid PRIMARY KEY,
    insert_time timestamp,
    value text
) ;
CREATE INDEX my_test_insert_time_idx ON my_keyspace.my_test (insert_time);

this is essentially a duplicate of http://stackoverflow.com/questions/13582673/cql-select-query-on-non-key-column — Martin Serrano, Mar 12 '15 at 02:26
For time range queries, see https://stackoverflow.com/q/18697725/32453 — rogerdpack, Feb 12 '18 at 21:48

score 8 · Accepted Answer · answered Mar 12 '15 at 10:21

Cassandra range queries are quite limited. It goes down to performance, and data storage mechanics. A range query must have the following:

Hit a (or few with IN) partition key, and include exact matches on all consecutive clustering keys except the last one in the query, which you can do a range query on.

Say your PK is (a, b, c, d), then the following are allowed:

where a=a1 and b < b1
where a=a1 and b=b1 and c < c1

The following is not:

where a=a1 and c < 1

[I won't go into Allow Filtering here...avoid it.]

Secondary indexes must be exact matches. You can't have range queries on them.

Cassandra DB: Why less than query failed?

1 Answers1