Slicing over partition rows using tuple operation in CQL

Question

I am trying to understand the behavior of tuple operator with clustering keys. Here is what I was trying to do:

create table sampletable (a int,b int,c int, d int, e int, primary key((a,b,c),d,e));
insert into sampletable(a,b,c,d,e) values(1,1,1,1,1);
insert into sampletable(a,b,c,d,e) values(1,1,1,1,1);
insert into sampletable(a,b,c,d,e) values(1,1,1,1,2);
insert into sampletable(a,b,c,d,e) values(1,1,2,1,1);
insert into sampletable(a,b,c,d,e) values(1,1,2,1,2);
insert into sampletable(a,b,c,d,e) values(1,1,2,2,3);
insert into sampletable(a,b,c,d,e) values(1,1,2,1,2); 
insert into sampletable(a,b,c,d,e) values(1,1,1,2,3);

cqlsh:mapro> select * from sampletable;

 a | b | c | d | e
---+---+---+---+---
 1 | 1 | 1 | 1 | 1
 1 | 1 | 1 | 1 | 2
 1 | 1 | 1 | 2 | 3
 1 | 1 | 2 | 1 | 1
 1 | 1 | 2 | 1 | 2
 1 | 1 | 2 | 2 | 3

(6 rows)

-- Query1
cqlsh:mapro> select * from sampletable where a=1 and b=1 and c in (1,2) and (d,e)<(2,3);  
 a | b | c | d | e                                                                 
---+---+---+---+---                                                                
 1 | 1 | 1 | 1 | 1                                                                 
 1 | 1 | 1 | 1 | 2                                                                 
 1 | 1 | 2 | 1 | 1                                                                 
 1 | 1 | 2 | 1 | 2                                                                                                                                              
(4 rows)            


-- Query2
cqlsh:mapro> select * from sampletable where a=1 and b=1 and c in (1,2) and (d,e)<(2,9);                                                  
 a | b | c | d | e                                                                 
---+---+---+---+---                                                                
 1 | 1 | 1 | 1 | 1                                                                 
 1 | 1 | 1 | 1 | 2                                                                 
 1 | 1 | 1 | 2 | 3                                                                 
 1 | 1 | 2 | 1 | 1                                                                 
 1 | 1 | 2 | 1 | 2                                                                 
 1 | 1 | 2 | 2 | 3                                                                                                                                              
(6 rows)

I am not able to understand why Query 2 is returning different results compared to Query 1. My understanding is Cassandra will apply all the partition key filtering first and then it tries to apply the tuple ordering i.e. (d,e)<(2,3) will be applied as d<2 and on top of the results it will apply e<3. Am I wrong in my understanding? Please help.

Answer 1

In case of clustering columns, (a1, a2) < (b1, b2) can be true in any one of the following cases:

1) a1 < b1 
2) a1=b1 and a2 < b2

This is how Cassandra internally does the ordering based on clustering columns

Based on this, the results of Query 1 and 2 are as per the expectation.