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.