Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive

Question

I am trying to create cluster column using the create query

CREATE TABLE videos_by_tag (
       tag text,
       video_id uuid,
       added_date timestamp,
       title text,
       PRIMARY KEY ((tag), added_date, video_id)
) WITH CLUSTERING ORDER BY(added_date DESC);

It's showing the following error. Am i missing something in the create table ?

InvalidRequest: Error from server: code=2200 [Invalid query] message="Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive"

It's working only if i include all the clustering columns mentioned in the primary key( in this case added_date, video_id). Is it mandatory to include all the columns from the primary key in clustering order by?

CREATE TABLE videos_by_tag (
       tag text,
       video_id uuid,
       added_date timestamp,
       title text,
       PRIMARY KEY ((tag), added_date, video_id)
) WITH CLUSTERING ORDER BY(added_date DESC,video_id ASC);

I am using the following version of cassandra on mac os

[cqlsh 5.0.1 | Cassandra 4.0.0.680 | CQL spec 3.4.5

It's working only if i include all the clustering columns mentioned in the primary key( in this case added_date, video_id). Is it mandatory to include all the columns from the primary key in clustering order by?

Answer 1

It looks like the question was also posted on https://community.datastax.com/questions/4118/ and I'm re-posting my answer here.

Yes, it is mandatory to specify all the clustering columns in the CLUSTERING ORDER BY directive in DataStax Enterprise 6.8.0. The logic for CREATE TABLE was changed in Apache Cassandra 4.0-alpha1 (CASSANDRA-13426) but has been reverted back in C* 4.0-alpha4 (CASSANDRA-15271). The previous [accepted] behaviour will be included in a future release of DSE 6.8. Cheers!