Why are the returned rows in an unbounded query not sorted by the clustering column?

Question

question

Thiru_DS8487 asked Dec 17, '21 Erick Ramirez answered Dec 21, '21

Why are the returned rows in an unbounded query not sorted by the clustering column?

Was testing a Simple clustering column with an example, but i was not able to see the default ASC order for the Clustering column

Here is the below example

CREATE TABLE pk_ck_clust_test2 (
    id INT,
    name text,
    age INT,
    status text,
    primary key((age, id), name)
)

insert into pk_ck_CLUST_test2 (id,name,age,status) values (25,'thiru1',30,'New30test');
insert into pk_ck_CLUST_test2 (id,name,age,status) values (24,'thiru1',30,'New30test');
insert into pk_ck_CLUST_test2 (id,name,age,status) values (26,'thiru1',30,'New30test');
insert into pk_ck_CLUST_test2 (id,name,age,status) values (15,'thiru1',30,'New30test');
insert into pk_ck_CLUST_test2 (id,name,age,status) values (24,'thiru0',30,'New30test');
insert into pk_ck_CLUST_test2 (id,name,age,status) values (24,'athiru0',30,'New80test');

select * from pk_ck_CLUST_test2;
 age | id | name.   | status
-----+----+---------+-----------
  30 | 15 | thiru1  | New30test
  30 | 25 | thiru1  | New30test
  30 | 24 | athiru0 | New80test <======== (name : athiru0) Supposed to be the clsutering column and it should be ASC by Default , why is it showing up in the middle
  30 | 24 | thiru0  | New70test
  30 | 24 | thiru1  | New30test
  30 | 26 | thiru1  | New30test

===> by the definition of a Clustering column the (name : athiru0) Supposed to be the clustering column and it should be ASC by

(name : athiru0) should be the fist value showing up based on name ASC , but i see its in the middle , just wondering if this has any more to it

Thanks

clustering key

1

10 |1000

Attachments: Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Thiru_DS8487 commented · Dec 17, 2021 at 09:44 AM

when i run a select , with Part key and one of the Cluster column , its showing correct data

But why would a select * from table does not show the Clusterting order by name also by definition of the table.

select * from pk_ck_CLUST_test2 where age=30 and id=24 ;

age | id | name | status

-----+----+---------+-----------

30 | 24 | athiru0 | New80test

30 | 24 | thiru0 | New70test

30 | 24 | thiru1 | New30test

0 ·

DataStax Enterprise is powered by the best distribution of Apache Cassandra ™

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries.

Privacy Policy Terms of Use

Answer 1 · 2021-12-21T06:02:52Z

Erick Ramirez answered Dec 21, '21

The clustering order only applies to the order of the clustering column (rows) within the partition -- the ordering does not apply to rows across all partitions in the table.

In the example you posted, age = 30 AND id = 15 is one partition which has a single row:

 age | id | name    | status
-----+----+---------+-----------
  30 | 15 | thiru1  | New30test

age = 30 AND id = 25 is another partition which also has just one row:

 age | id | name    | status
-----+----+---------+-----------
  30 | 25 | thiru1  | New30test

The partition age = 30 AND id = 24 has 3 rows:

 age | id | name    | status
-----+----+---------+-----------
  30 | 24 | athiru0 | New30test
  30 | 24 | thiru0  | New30test
  30 | 24 | thiru1  | New30test

You'll notice that they are sorted by name in ascending order. The rows are sorted as expected because they all belong in the same partition. As I stated above, the clustering order (sorting) applies to the rows within a partition. Cheers!

Share

10 |1000

Attachments: Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

question