Cassandra datetime multi clustering columns filtering

Question

I'm using Cassandra 3.0, I created one table as following:

CREATE TABLE site (
    site_id bigint,    
   end_date timestamp,
    start_date timestamp,  
   promotion_id uuid,

   PRIMARY KEY (site_id, end_date,start_date, promotion_id)
) WITH CLUSTERING ORDER BY (end_date DESC,start_date DESC, promotion_id ASC);

And I insert one record into this table

insert into site (site_id, start_date,end_date,promotion_id) values (10000,'2017-05-11T16:17:48','2017-05-21T16:17:48',8999f91c-a787-4604-9479-f8ead4955f6c);

May I ask why the following cql returns one record? I'm expecting it returns nothing:

select * from site
where site_id = 10000 and ( end_date, start_date ) <= ( '2117-05-12', '2017-04-12' );

undefined_variable undefined_variable · Accepted Answer · 2017-05-17T10:35:03

It is also possible to “group” CLUSTERING COLUMNS together in a relation using the tuple notation. For instance:
SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) > ('John''s Blog', '2012-01-01')
will request all rows that sorts after the one having “John’s Blog” as blog_tile and ‘2012-01-01’ for posted_at in the clustering order. In particular, rows having a posted_at <= '2012-01-01' will be returned as long as their blog_title > 'John''s Blog', which would not be the case for:
SELECT * FROM posts
 WHERE userid = 'john doe'
   AND blog_title > 'John''s Blog'
   AND posted_at > '2012-01-01'

this is from the cassandra documentation.

So in your case it will return all rows which has end_date<=211705-12

Cassandra datetime multi clustering columns filtering

1 Answers