[SOLVED] Cannot query with IN() on indexed column

Cannot query with IN() on indexed column

I have simple table. Here is how it looks:

create table trip_stops
(
    cid      uuid,
    stop_id  uuid,
    rate     float,
    date     date,
    primary key ((cid), stop_id)
);
CREATE INDEX ON trip_stops ((cid), date); // local secondary index

Then I am doing query as follows:

select sum(rate) from trip_stops where
      cid = aa9e4310-e439-423c-86e5-53ddd7c74609
            and date in ('2019-01-01', '2019-01-02');

It fails and asks for allow filtering. Shouldn't the result of this query must be in a single partition and no need for internode data exchange? Thanks in advance.

I run the query and it fails, expect it to work

Solution

This looks like a ScyllaDB bug, tracked (for the similar case of ordinary, non-local, secondary index) in https://github.com/scylladb/scylladb/issues/13533:

An "IN" together with a secondary index is not supported; In Cassandra it gives an error ("IN predicates on non-primary-key columns (x) is not yet supported"), in Scylla instead it is wrongly done using slow filtering (with either an error or a warning that ALLOW FILTERING is needed).

Unfortunately, as a workaround today, you will need to split the IN request into multiple requests.