Cannot restrict clustering columns by in relations when a collection is selected by the query

6
votes

I am reading this article which shows how to write queries with IN clause in cassandra

https://www.datastax.com/dev/blog/a-deep-look-to-the-cql-where-clause

I created the following table 

create table foo2(id bigint, bid bigint, data set<bigint>, primary key (id, bid));

insert into foo2 (id, bid, data) values (1, 1, {1, 2});
insert into foo2 (id, bid, data) values (1, 2, {3, 4});
insert into foo2 (id, bid, data) values (1, 3, {5, 6});

Now I write the query

select * from foo2 where id = 1 and bid IN (1, 2, 3);

Cannot restrict clustering columns by in relations when a collection is selected by the query.

I googled on this error and found this

https://issues.apache.org/jira/browse/CASSANDRA-12654

and it says that the issue is resolved in Cassandra 4.0 but I a using

[cqlsh 5.0.1 | Cassandra 3.10 | CQL spec 3.4.4 | Native protocol v4]

Is there a workaround for this (apart from the mother of all answers for any Cassandra question change your schema)

Some people are pointing here : Cassandra IN query not working if table has SET type column

But this does not question does not have a clearly marked answer.

2
cassandra
Possible duplicate of Cassandra IN query not working if table has SET type column – Ashraful Islam
Ashraful.. that question does not have a marked answer. So you are pointing me to an unanswered question. – Knows Not Much
This is the cassandra's limitation, If you use set, list, or map in your table you can't use in clause with clustering key – Ashraful Islam

2 Answers

6
votes

You may not like this answer but you will have to change the schema a bit to work around this issue. By using frozen the issue will disappear. Note that this issue is also affecting UDT's and that for them it also get's fixed by using frozen.

4
votes

Old answer:
Using column names instead of * will solve the problem. In your case, the query should be:
select id,bid,data from foo2 where id = 1 and bid IN (1, 2, 3);

Update:
I was wrong with previous answer, It should be a limitation of Cassandra which we can't use IN query for the cluster key when we query a collection column(data column). If you really need an IN query, you can change the schema of the table:

create table foo3(id bigint, bid bigint, data set<bigint>, primary key ((id, bid)));
(New parentheses is introduced in the key of table)

In this new schema, the partition key is group of columns id and bid which we can have IN query for partition key. Now, below query should work:
select * from foo3 where id = 1 and bid IN (1, 2, 3);