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/

Advertisements

Cassandra DBA – Tricks and tips

Change replication factor for an existing keyspace

ALTER KEYSPACE acme WITH REPLICATION =  { 'class' : 'SimpleStrategy', 'replication_factor' : 2 };

Drop a keyspace

DROP KEYSPACE acme;

Drop a column

ALTER TABLE person DROP mail;

Add a column

ALTER TABLE person ADD email text;

Counting keys (rows) in column. Here is a quick way to get an “estimated” count

nodetool cfstats acme.person

Command to check cluster / node status

nodetool status

Force restart of datastax agent

service datastax-agent force-reload

How to insert current date-time into a table using CQL?

This can be done using the now and dateof functions.

The now function takes no arguments and generates a new unique timeuuid (at the time where the statement using it is executed).

The dateof function takes a timeuuid argument and extracts the embedded timestamp.

INSERT INTO account (name, created) VALUES ('acme', dateof(now()));