This applies to Cassandra version 2.x
- Last column in the partition key supports the IN operator
- Last column of the clustering key also supports IN only if
- = is used for all preceding clustering columns
- No ‘collection’ column is selected in the query
Sample table with composite primary key (multi column partition key and multiple clustering columns)
CREATE TABLE test ( p1 int, p2 int, mylist list<text>, c1 int, c2 int, c3 int, PRIMARY KEY ((p1, p2), c1, c2, c3))
Following queries are allowed
SELECT p1,p2,c1,c2,c3 FROM test WHERE p1=1 AND p2 IN (2,3) AND c1=1 AND c2=2 AND c3 IN (3,4); SELECT * FROM test WHERE p1=1 AND p2 IN (2,3) AND c1=1 AND c2=2 AND c3 = 4; SELECT * FROM test WHERE p1=1 AND p2 IN (2,3) AND c1=1 AND c2=2 AND c3 > 4;
Following queries are not allowed
SELECT p1,p2,c1,c2,c3 FROM test WHERE p1=1 AND p2 IN (2,3) AND c1=1 AND c2 > 2 AND c3 IN (3,4); SELECT * FROM test WHERE p1=1 AND p2 IN (2,3) AND c1=1 AND c2=2 AND c3 IN (3,4);
Useful links on this topic
http://planetcassandra.org/blog/flite-breaking-down-the-cql-where-clause/