Cassandra IN clause – rules and limitations

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/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s