CODEWITHSUNDEEP
cassandranosql
rajpy
rajpy

Reputation: 2476

IN operator in Cassandra doesn't work for table having a column with type-collection(Map or List)

I'm working on Cassandra, trying to get to know how it works. Encountered something strange while using IN operator. Example:

Table:

CREATE TABLE test_time (
  name text,
  age int,
  time timeuuid,
  "timestamp" timestamp,
  PRIMARY KEY ((name, age), time)
)

I have inserted few dummy data. Used IN operator as follows:

SELECT * from test_time
where name="9" and age=81
and time IN (c7c88000-190e-11e4-8000-000000000000, c7c88000-190e-11e4-7000-000000000000);

It worked properly.

Then, added a column of type Map. Table will look like:

CREATE TABLE test_time (
  name text,
  age int,
  time timeuuid,
  name_age map<text, int>,
  "timestamp" timestamp,
  PRIMARY KEY ((name, age), time)
)

On executing same query, I got following error:

Bad Request: Cannot restrict PRIMARY KEY part time by IN relation as a collection is selected by the query

From the above examples, we can say, IN operator doesn't work if there are any column of type collection(Map or List) in the table.

I don't understand why it behaves like this. Please let me know If I'm missing anything here. Thanks in advance.

Upvotes: 4

Views: 1462

Answers (2)

Mykola Dolgalov
Mykola Dolgalov

Reputation: 43

You can either include your column as a part of partitioning expression in the primary key 

CREATE TABLE test_time (
  name text,
  age int,
  time timeuuid,
  "timestamp" timestamp,
  PRIMARY KEY ((name, time), age)
);

or create a separate Materialized View to satisfy your query requirements:

CREATE MATERIALIZED VIEW test_time_mv AS
SELECT * FROM test_time
WHERE name IS NOT NULL AND time IS NOT NULL AND age IS NOT NULL
PRIMARY KEY ((name, time), age);

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

    SELECT * from test_time_mv 
    where name='9' 
      and age=81 
      and time IN (c7c88000-190e-11e4-8000-000000000000, 
        c7c88000-190e-11e4-7000-000000000000);

Upvotes: 0

ashic
ashic

Reputation: 6495

Yup...that is a limitation. You can do the following:

  1. select * from ...where name='9' and age=81 and time > x and time < y
  2. select [columns except collection] from ...where name='9' and age=81 and time in (...)

You can then filter client side, or do another query.

Upvotes: 4

Related Questions