cassandra Select on indexed columns and with IN clause for the PRIMARY KEY are not supported

Question

In Cassandra, I'm using the cql:

select msg from log where id in ('A', 'B') and filter1 = 'filter' 

(where id is the partition key and filter1 is a secondary index and filter1 cannot be used as a cluster column)

This gives the response:

Select on indexed columns and with IN clause for the PRIMARY KEY are not supported

How can I change CQL to prevent this?

Answer 1

You would need to split that up into separate queries of:

select msg from log where id = 'A' and filter1 = 'filter';

and

select msg from log where id = 'B' and filter1 = 'filter';

Due to the way data is partitioned in Cassandra, CQL has a lot of seemingly arbitrary restrictions (to discourage inefficient queries and also because they are complex to implement).

Over time I think these restrictions will slowly be removed, but for now we have to work around them. For more details on the restrictions, see A deep look at the CQL where clause.