Add support for mixing token, multi- and single-column restrictions

[psarna]

This is Scylla's bug tracker, to be used for reporting bugs only.
If you have a question about Scylla, and not a bug, please ask it in
our mailing-list at scylladb-dev@googlegroups.com or in our slack channel.

• I have read the disclaimer above, and I am reporting a suspected malfunction in Scylla.

Installation details
Scylla version (or git commit hash): master (e37e095)

Currently it's not possible to restrict columns by multi-column restrictions, token restrictions and single-column ones at the same time. We should allow that in order to make it possible to execute queries like:

select * from t1 where col1=k11 and token(col1) >= lower_range and token(col1) < upper_range allow filtering

SELECT * FROM t WHERE (ck1, ck2) IN ((1,2),(2,3)) AND ck3 < 3 ALLOW FILTERING

[psarna]

Refs #3574

[slivne]

@psarna - with regards to this one:

SELECT * FROM t WHERE (ck1, ck2) IN ((1,2),(2,3)) AND ck3 < 3

I think you are missing ALLOW FILTERING - right ?

[tarzanek]

Are we able to support queries like

SELECT * FROM cycling.last_3_days WHERE TOKEN(year) < TOKEN('2015-05-26') AND year IN ('2015-05-24','2015-05-25');
(or from https://docs.datastax.com/en/archived/cql/3.3/cql/cql_using/useToken.html ? )
?

[psarna]

@slivne yes, definitely, I'll fix it in the issue body for completeness.
@tarzanek nope, currently a token restriction always screams if it's used along with a non-token restriction in the same statement.

[dekimir]

For the record, Cassandra supports mixing single- and multi-column relations.

Cassandra:

cqlsh:ks> DESCRIBE TABLE tbl ;

CREATE TABLE ks.tbl (
    p1 int,
    p2 int,
    c1 int,
    c2 int,
    t text,
    PRIMARY KEY ((p1, p2), c1, c2)
) WITH CLUSTERING ORDER BY (c1 ASC, c2 ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'ALL'}
    AND comment = ''
    AND compaction = {'class': 'SizeTieredCompactionStrategy'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99.0PERCENTILE';

cqlsh:ks> SELECT * FROM tbl WHERE (c1,c2) > (0,1) and c1<10 ALLOW FILTERING;

 p1 | p2 | c1 | c2 | t
----+----+----+----+---

(0 rows)

Scylla:

cqlsh:ks> SELECT * FROM tbl WHERE (c1,c2) > (0,1) and c1<10 ALLOW FILTERING;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Mixing single column relations and multi column relations on clustering columns is not allowed"

[dekimir]

For multi-column restrictions, there are two failure modes:

1. When the query is rejected because the same column participates in multi- and single-column relations (eg, (c1,c2) > (0,1) and c1<10 as shown above).
2. When the query is accepted, but only the multi-column relation is evaluated. This is Other restrictions ignored when clustering key is constrained #6200.

[avikivity]

We imported this error message in 792f606, Dec 2014. Cassandra subsequently gained support for mixing single and multiple column constraints in apache/cassandra@90a012a (2.0.13/2.1.5) . Although that itself is marked as fixing a regression.

So we're compatible with some version of Cassandra, not randomly losing a feature.

[nyh]

A user reported that Apache Beam CassandraIO divides queries into sub-queries split into token ranges, which results in queries including both normal restrictions and token restrictions - which breaks on Scylla but works on Cassandra since 2015, as @avikivity noted above.

[nyh]

So we're compatible with some version of Cassandra, not randomly losing a feature.

At this point, the Cassandra feature is 6 years old and exists on Cassandra 2, 3 and 4 - so users with existing applications or Cassandra-oriented tools which need it will not be content knowing that Cassandra also didn't have this feature once.

[dekimir]

Ongoing work on #3815 will naturally fix this issue.

[nyh]

Two more translated Cassandra test exposes this issue (the multi/single case) -

• cassandra_tests/validation/operations/compact_storage_test.py::testDeleteWithTwoClusteringColumns
• cassandra_tests/validation/operations/delete_test.py::testDeleteWithTwoClusteringColumns

Both have the same command:

DELETE FROM tbl WHERE partitionKey = ? AND (clustering_1) IN ((?), (?)) AND clustering_2 = ?"

In this case one of the restrictions only looks like a multi-column restriction but it isn't really, which makes this error even more silly.

[ptrsmrn]

The issue is still valid (5.4 OSS):

cqlsh> CREATE TABLE ms.t (ck1 int PRIMARY KEY, ck2 int, ck3 int);
cqlsh> SELECT * FROM ms.t WHERE (ck1, ck2) IN ((1,2),(2,3)) AND ck3 < 3 ALLOW FILTERING;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Multi-column relations can only be applied to clustering columns but was applied to: ck1"

Queuing for Q3'24, but with low priority.

[nyh]

The error message you saw is really weird, and even worse than the situation before :-(
Please note above a list of tests for this feature and its Cassandra compatibility. By now this feature is 9 years old (!) in Cassandra and we still haven't implemented it.

[dekimir]

@nyh that message doesn't seem weird at all. Look at how the code above created the table -- ck1 and ck2 aren't clustering keys. Only clustering keys are allowed in multi-column relations.

[denesb]

You are right @dekimir (long time no see :D). Those are regular columns.

[ptrsmrn]

Thanks, correcting (the issue still present):

cqlsh> CREATE TABLE ms.tbl (
    p1 int,
    p2 int,
    c1 int,
    c2 int,
    t text,
    PRIMARY KEY ((p1, p2), c1, c2)
) WITH CLUSTERING ORDER BY (c1 ASC, c2 ASC);
cqlsh> SELECT * FROM ms.tbl WHERE (c1,c2) > (0,1) and c1<10 ALLOW FILTERING;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Mixing single column relations and multi column relations on clustering columns is not allowed"

[nyh]

@ptrsmrn please take a look at the cql-pytest tests mentioned above and learn how to run them quickly with test/cql-pytest/run - and then you won't need to invent new and incorrect way to reproduce such issues that already have reproducers :-)

[nyh]

Summary of cql-pytest tests that reproduce this issue. You can also grep the issue number (#4244) in the tests to find tests that reproduce it:

• test_scan.py::test_multi_column_restrictions_ck
• test_scan.py::test_multi_column_and_single_column_restriction_same_ck
• test_scan.py::test_restriction_token_and_nontoken
• cassandra_tests/validation/operations/select_test.py::testFilteringWithMultiColumnSlices
• cassandra_tests/validation/operations/compact_storage_test.py::testDeleteWithTwoClusteringColumns
• cassandra_tests/validation/operations/select_multi_column_relation_test.py::testMultiClusteringInvalidQueries
• cassandra_tests/validation/operations/compact_storage_test.py::testDeleteWithTwoClusteringColumns
• cassandra_tests/validation/operations/delete_test.py::testDeleteWithTwoClusteringColumns

These different tests exercises different subcases and variants of the same issue, so it's worth to check that we solve them all.