CODEWITHSUNDEEP
cassandracql
Navrattan Yadav
Navrattan Yadav

Reputation: 2113

Cassandra IN query not working if table has SET type column

I am new to Cassandra. I have an issue when using IN in cassandra query.

If table has no column of SET type it works.

CREATE TABLE test (
    test_date bigint, 
    test_id bigint, 
    caption text,
    PRIMARY KEY(test_date,test_id)
);

select * from test where test_date = 2022015 and test_id IN (1,2);

But if I add a column of SET type f.e. tags set in the above table and rerun the select query, it gives error.

CREATE TABLE test1 (
    test_date bigint, 
    test_id bigint, 
    tags set<text>,
    caption text,
    PRIMARY KEY(test_date,test_id)
);

select * from test1 where test_date = 2022015 and test_id IN (1,2);

code=2200 [Invalid query] message="Cannot restrict column "test_id" by IN relation as a collection is selected by the query"

Upvotes: 9

Views: 3835

Answers (3)

Mykola Dolgalov
Mykola Dolgalov

Reputation: 43

You can use a Materialized View with test_id as a part of partitioning expression to satisfy your requirement if changing the PK on your base table is not an option:

CREATE MATERIALIZED VIEW test1_mv AS
SELECT * FROM test1
WHERE test_date IS NOT NULL AND test_id IS NOT NULL
PRIMARY KEY((test_date,test_id));

Then use the Materialized View instead of the base table in your query:

select * from test1_mv where test_date = 2022015 and test_id IN (1,2);

Upvotes: 0

Aaron
Aaron

Reputation: 57808

I think you are seeing this error due to Cassandra's underlying storage model. When I query your test1 table within CQLSH (with my own test data), this is what I see:

aploetz@cqlsh:stackoverflow> SELECT * FROM test1;

 test_date | test_id | caption   | tags
-----------+---------+-----------+-------------------------
   2022015 |       1 | blah blah | {'one', 'three', 'two'}
   2022015 |       2 | blah blah | {'one', 'three', 'two'}

(2 rows)

This view gives a misleading interpretation of how the data is actually stored. This is what it looks like when I query the same table from within cassandra-cli:

[default@stackoverflow] list test1;
Using default limit of 100
Using default cell limit of 100
-------------------
RowKey: 2022015
=> (name=1:, value=, timestamp=1422895168730184)
=> (name=1:caption, value=626c616820626c6168, timestamp=1422895168730184)
=> (name=1:tags:6f6e65, value=, timestamp=1422895168730184)
=> (name=1:tags:7468726565, value=, timestamp=1422895168730184)
=> (name=1:tags:74776f, value=, timestamp=1422895168730184)
=> (name=2:, value=, timestamp=1422895161891116)
=> (name=2:caption, value=626c616820626c6168, timestamp=1422895161891116)
=> (name=2:tags:6f6e65, value=, timestamp=1422895161891116)
=> (name=2:tags:7468726565, value=, timestamp=1422895161891116)
=> (name=2:tags:74776f, value=, timestamp=1422895161891116)

1 Row Returned.

This suggests that collection (set) values are stored as additional column keys. A restriction on using the IN relation, is that it must operate on the last key (partitioning or clustering) of a primary key. So I would guess that this is a limitation based on how Cassandra stores the collection data "under the hood."

And just a warning, but using IN for production-level queries is not recommended. Some have even gone as far as to put it on the list of Cassandra anti-patterns. My answer to this question (Is the IN relation in Cassandra bad for queries?) explains why IN queries are not optimal.

EDIT

Just to see, I tried your schema with a list instead of a set to see if that made any difference. It still didn't work, but from within the cassandra-cli it appeared to add an additional UUID identifier to the key, and stored the actual value as the column value. Which is different from how a set was treated...this must be how sets are restricted to unique values.

Upvotes: 3

Stefan Podkowinski
Stefan Podkowinski

Reputation: 5249

I'm not sure why this restriction should apply particulary for collections. But in your case you can get around this issue by making the test_id part of your partition key:

PRIMARY KEY((test_date,test_id))

This will allow you to do IN queries as long as you specify the first part of the composite key (test_date).

Upvotes: 3

Related Questions