Cassandra get lease page does not return sorted results without clustering column
Doing queries in the Cassandra database with '>' or '<' operands and 'LIMIT', but without 'ORDER BY' will retrieve some elements from the database, but not necessarily in order.
Values in the database table 'test':
value
9 3 7 8 2 5
query 'select value from test where value > 3 limit 2' will retrieve 9 and 7. The next query which will be run for the next page 'select value from test where value > 9 limit 2' will miss values like 8 and 5...
To be able to 'ORDER BY', a clustering column must be created. If the primary key is a compound key (partition key + clustering key), the data will be sorted on each partition. For example if creating a dummy partition key: CREATE TABLE test ( dummy int, value int, PRIMARY KEY (dummy, value));
and inserting all values with dummy = 0 will guarantee that retrieved values will be ordered, but all the data in the table will be stored on a partition.
Another implementation could retrieve all data from respective table and sort it in memory and split it by page size, but this would make the purpose of using pages useless.