question

chandrasekar.b03_190734 avatar image
chandrasekar.b03_190734 asked Erick Ramirez edited

How do I read data using the inequality operator?

Hi,

In DS201, it has been told that we use equality operator on first clustering column and then inequality operator on next clustering column. I encountered an error while doing select statements. I did in both the ways.

create table test(
          one uuid,
          two text,
          three text,
          primary key(one, two, three)
          );
//I have already inserted records into table.
cqlsh> select * from test where one=08ab39df-68ad-487e-b489-4569693555fa and two='C' and three!='D';
Error: InvalidRequest: Error from server: code=2200 [Invalid query] message="Unsupported "!=" relation: three != 'D'"
cqlsh> select * from test where one=08ab39df-68ad-487e-b489-4569693555fa and two!='C' and three='D';
Error: InvalidRequest: Error from server: code=2200 [Invalid query] message="Unsupported "!=" relation: three != 'D'"

I could not find the right way of doing this. Could you please assist me with correct example of doing Inequality on Clustering Columns?

cql
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

bettina.swynnerton avatar image
bettina.swynnerton answered bettina.swynnerton commented

Hi @chandrasekar.b03_190734,

the not-equal operator is not supported on clustering keys, as it would not allow to select a contiguous set of rows. Here you can only use operators that allow to retrieve a contiguous set of rows: "<", ">", "<=" or ">=".

Quoting from this document:

https://cassandra.apache.org/doc/latest/cql/dml.html#select

Not all relations are allowed in a query. For instance, non-equal relations [..] on a partition key are not supported [..]. Moreover, for a given partition key, the clustering columns induce an ordering of rows and relations on them is restricted to the relations that allow to select a contiguous (for the ordering) set of rows.

Since you are mentioning DS201, which exercise/module uses the not-equal operator?

Thanks!

2 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

chandrasekar.b03_190734 avatar image chandrasekar.b03_190734 commented ·

Hi bettina,

Here is where I have found the use of inequality operator (fourth point on the slide) comparison given in one of the slides stating the rules for querying the clustering columns. i have attached a screenshot of DS201 tutorial for your reference and with your permission. Kindly let me know what does that line means.

Update: And for your information, the link Select which you have provided also has " !=" as one of its operators in use.

0 Likes 0 ·
screenshot-20200907-182629-chrome.jpg (207.6 KiB)
bettina.swynnerton avatar image bettina.swynnerton ♦♦ chandrasekar.b03_190734 commented ·

Hi @chandrasekar.b03_190734,

the slide from the course is referring to inequality comparisons, which are comparisons using "<", ">", "<=" or ">=". This not the same as using the non-equal operator "!=".

As it says on the slide, you can perform either equality (=) or range queries (<, >) on clustering columns, as range queries return a contiguous set of rows.

The link that I referred to states that not all relations are possible in a query, and "!=" is not a supported operator for clustering keys, or in fact for most queries.

I am not quite sure which query would allow the use of the "!=" operator, I cannot think of an example.

I hope this helps.

1 Like 1 ·