[QUEUED scylla next] Merge 'Additional cql-pytest tests related to WHERE expressions' from Nadav Har'El

[Commit Bot]

From: Piotr Sarna <sa...@scylladb.com>
Committer: Piotr Sarna <sa...@scylladb.com>
Branch: next

Merge 'Additional cql-pytest tests related to WHERE expressions' from Nadav Har'El

This small series includes a few more CQL tests in the cql-pytest
framework.

The main patch is a translation of a unit test from Cassandra that
checks the behavior of restrictions (WHERE expressions, filtering) in
different cases. It turns out that Cassandra didn't implement some
cases - for example filtering on unfrozen UDTs - but Scylla does
implement them. So in the translated test, the
checks-that-these-features-generate-an-error from Cassandra are
commented out, and this series also includes separate tests for these
Scylla-unique features to check that they actually work correctly and
not just that they exist.

Closes #10611

* github.com:scylladb/scylla:
cql-pytest: translate Cassandra's tests for relations
test/cql-pytest: add test for filtering UDTs
test/cql-pytest: tests for IN restrictions and filtering
test/cql-pytest: test more cases of overlapping restrictions

---
diff --git a/test/cql-pytest/cassandra_tests/validation/operations/select_single_column_relation_test.py b/test/cql-pytest/cassandra_tests/validation/operations/select_single_column_relation_test.py
--- a/test/cql-pytest/cassandra_tests/validation/operations/select_single_column_relation_test.py
+++ b/test/cql-pytest/cassandra_tests/validation/operations/select_single_column_relation_test.py
@@ -0,0 +1,607 @@
+# This file was translated from the original Java test from the Apache
+# Cassandra source repository, as of commit a87055d56a33a9b17606f14535f48eb461965b82
+#
+# The original Apache Cassandra license:
+#
+# SPDX-License-Identifier: Apache-2.0
+
+from cassandra_tests.porting import *
+from cassandra.query import UNSET_VALUE
+
+...@pytest.mark.xfail(reason="#2962 - we don't support index on collection column")
+def testInvalidCollectionEqualityRelation(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a int PRIMARY KEY, b set<int>, c list<int>, d map<int, int>)") as table:
+ execute(cql, table, "CREATE INDEX ON %s (b)")
+ execute(cql, table, "CREATE INDEX ON %s (c)")
+ execute(cql, table, "CREATE INDEX ON %s (d)")
+ assert_invalid_message(cql, table, "Collection column 'b' (set<int>) cannot be restricted by a '=' relation",
+ "SELECT * FROM %s WHERE a = 0 AND b=?", {0})
+ assert_invalid_message(cql, table, "Collection column 'c' (list<int>) cannot be restricted by a '=' relation",
+ "SELECT * FROM %s WHERE a = 0 AND c=?", [0])
+ assert_invalid_message(cql, table, "Collection column 'd' (map<int, int>) cannot be restricted by a '=' relation",
+ "SELECT * FROM %s WHERE a = 0 AND d=?", {0: 0})
+
+...@pytest.mark.xfail(reason="#10631 - wrong error message")
+def testInvalidCollectionNonEQRelation(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a int PRIMARY KEY, b set<int>, c int)") as table:
+ execute(cql, table, "CREATE INDEX ON %s (c)")
+ execute(cql, table, "INSERT INTO %s (a, b, c) VALUES (0, {0}, 0)")
+
+ # non-EQ operators
+ assert_invalid_message(cql, table, "Collection column 'b' (set<int>) cannot be restricted by a '>' relation",
+ "SELECT * FROM %s WHERE c = 0 AND b > ?", {0})
+ assert_invalid_message(cql, table, "Collection column 'b' (set<int>) cannot be restricted by a '>=' relation",
+ "SELECT * FROM %s WHERE c = 0 AND b >= ?", {0})
+ assert_invalid_message(cql, table, "Collection column 'b' (set<int>) cannot be restricted by a '<' relation",
+ "SELECT * FROM %s WHERE c = 0 AND b < ?", {0})
+ assert_invalid_message(cql, table, "Collection column 'b' (set<int>) cannot be restricted by a '<=' relation",
+ "SELECT * FROM %s WHERE c = 0 AND b <= ?", {0})
+ # Reproduces #10631:
+ assert_invalid_message(cql, table, "Collection column 'b' (set<int>) cannot be restricted by a 'IN' relation",
+ "SELECT * FROM %s WHERE c = 0 AND b IN (?)", {0})
+ assert_invalid_message(cql, table, "Unsupported \"!=\" relation: b != 5",
+ "SELECT * FROM %s WHERE c = 0 AND b != 5")
+ # different error message in Scylla and Cassandra. Note that in the
+ # future, Scylla may want to support this restriction so the erorr
+ # message will change again.
+ assert_invalid_message(cql, table, "IS NOT",
+ "SELECT * FROM %s WHERE c = 0 AND b IS NOT NULL")
+
+def testClusteringColumnRelations(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a text, b int, c int, d int, primary key (a, b, c))") as table:
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 1, 5, 1)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 2, 6, 2)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 3, 7, 3)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 4, 8, 4)
+
+ assert_rows(execute(cql, table, "select * from %s where a in (?, ?)", "first", "second"),
+ ["first", 1, 5, 1],
+ ["first", 2, 6, 2],
+ ["first", 3, 7, 3],
+ ["second", 4, 8, 4])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and b = ? and c in (?, ?)", "first", 2, 6, 7),
+ ["first", 2, 6, 2])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and b in (?, ?) and c in (?, ?)", "first", 2, 3, 6, 7),
+ ["first", 2, 6, 2],
+ ["first", 3, 7, 3])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and b in (?, ?) and c in (?, ?)", "first", 3, 2, 7, 6),
+ ["first", 2, 6, 2],
+ ["first", 3, 7, 3])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c in (?, ?) and b in (?, ?)", "first", 7, 6, 3, 2),
+ ["first", 2, 6, 2],
+ ["first", 3, 7, 3])
+
+ assert_rows(execute(cql, table, "select c, d from %s where a = ? and c in (?, ?) and b in (?, ?)", "first", 7, 6, 3, 2),
+ [6, 2],
+ [7, 3])
+
+ assert_rows(execute(cql, table, "select c, d from %s where a = ? and c in (?, ?) and b in (?, ?, ?)", "first", 7, 6, 3, 2, 3),
+ [6, 2],
+ [7, 3])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and b in (?, ?) and c = ?", "first", 3, 2, 7),
+ ["first", 3, 7, 3])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and b in ? and c in ?",
+ "first", [3, 2], [7, 6]),
+ ["first", 2, 6, 2],
+ ["first", 3, 7, 3])
+
+ assert_invalid_message(cql, table, "Invalid null value for column b",
+ "select * from %s where a = ? and b in ? and c in ?", "first", None, [7, 6])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c >= ? and b in (?, ?)", "first", 6, 3, 2),
+ ["first", 2, 6, 2],
+ ["first", 3, 7, 3])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c > ? and b in (?, ?)", "first", 6, 3, 2),
+ ["first", 3, 7, 3])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c <= ? and b in (?, ?)", "first", 6, 3, 2),
+ ["first", 2, 6, 2])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c < ? and b in (?, ?)", "first", 7, 3, 2),
+ ["first", 2, 6, 2])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c >= ? and c <= ? and b in (?, ?)", "first", 6, 7, 3, 2),
+ ["first", 2, 6, 2],
+ ["first", 3, 7, 3])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c > ? and c <= ? and b in (?, ?)", "first", 6, 7, 3, 2),
+ ["first", 3, 7, 3])
+
+ assert_empty(execute(cql, table, "select * from %s where a = ? and c > ? and c < ? and b in (?, ?)", "first", 6, 7, 3, 2))
+
+ # Scylla does allow such queries, and their correctness is tested in
+ # test_filtering.py::test_multiple_restrictions_on_same_column
+ #assert_invalid_message(cql, table, "Column \"c\" cannot be restricted by both an equality and an inequality relation",
+ # "select * from %s where a = ? and c > ? and c = ? and b in (?, ?)", "first", 6, 7, 3, 2)
+
+ #assert_invalid_message(cql, table, "c cannot be restricted by more than one relation if it includes an Equal",
+ # "select * from %s where a = ? and c = ? and c > ? and b in (?, ?)", "first", 6, 7, 3, 2)
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c in (?, ?) and b in (?, ?) order by b DESC",
+ "first", 7, 6, 3, 2),
+ ["first", 3, 7, 3],
+ ["first", 2, 6, 2])
+
+ # Scylla does allow such queries, and their correctness is tested in
+ # test_filtering.py::test_multiple_restrictions_on_same_column
+ #assert_invalid_message(cql, table, "More than one restriction was found for the start bound on b",
+ # "select * from %s where a = ? and b > ? and b > ?", "first", 6, 3, 2)
+
+ #assert_invalid_message(cql, table, "More than one restriction was found for the end bound on b",
+ # "select * from %s where a = ? and b < ? and b <= ?", "first", 6, 3, 2)
+
+REQUIRES_ALLOW_FILTERING_MESSAGE = "Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
+
+def testPartitionKeyColumnRelations(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a text, b int, c int, d int, primary key ((a, b), c))") as table:
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 1, 1, 1)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 2, 2, 2)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 3, 3, 3)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 4, 4, 4)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 1, 1, 1)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 4, 4, 4)
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and b = ?", "first", 2),
+ ["first", 2, 2, 2])
+
+ assert_rows(execute(cql, table, "select * from %s where a in (?, ?) and b in (?, ?)", "first", "second", 2, 3),
+ ["first", 2, 2, 2],
+ ["first", 3, 3, 3])
+
+ assert_rows(execute(cql, table, "select * from %s where a in (?, ?) and b = ?", "first", "second", 4),
+ ["first", 4, 4, 4],
+ ["second", 4, 4, 4])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and b in (?, ?)", "first", 3, 4),
+ ["first", 3, 3, 3],
+ ["first", 4, 4, 4])
+
+ assert_rows(execute(cql, table, "select * from %s where a in (?, ?) and b in (?, ?)", "first", "second", 1, 4),
+ ["first", 1, 1, 1],
+ ["first", 4, 4, 4],
+ ["second", 1, 1, 1],
+ ["second", 4, 4, 4])
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "select * from %s where a in (?, ?)", "first", "second")
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "select * from %s where a = ?", "first")
+ # Scylla does allow such queries, and their correctness is tested in
+ # test_filtering.py::test_multiple_restrictions_on_same_column
+ #assert_invalid_message(cql, table, "b cannot be restricted by more than one relation if it includes a IN",
+ # "select * from %s where a = ? AND b IN (?, ?) AND b = ?", "first", 2, 2, 3)
+ #assert_invalid_message(cql, table, "b cannot be restricted by more than one relation if it includes an Equal",
+ # "select * from %s where a = ? AND b = ? AND b IN (?, ?)", "first", 2, 2, 3)
+ #assert_invalid_message(cql, table, "a cannot be restricted by more than one relation if it includes a IN",
+ # "select * from %s where a IN (?, ?) AND a = ? AND b = ?", "first", "second", "first", 3)
+ #assert_invalid_message(cql, table, "a cannot be restricted by more than one relation if it includes an Equal",
+ # "select * from %s where a = ? AND a IN (?, ?) AND b IN (?, ?)", "first", "second", "first", 2, 3)
+
+def testClusteringColumnRelationsWithClusteringOrder(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a text, b int, c int, d int, primary key (a, b, c)) WITH CLUSTERING ORDER BY (b DESC, c ASC)") as table:
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 1, 5, 1)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 2, 6, 2)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 3, 7, 3)
+ execute(cql, table, "insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 4, 8, 4)
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c in (?, ?) and b in (?, ?) order by b DESC",
+ "first", 7, 6, 3, 2),
+ ["first", 3, 7, 3],
+ ["first", 2, 6, 2])
+
+ assert_rows(execute(cql, table, "select * from %s where a = ? and c in (?, ?) and b in (?, ?) order by b ASC",
+ "first", 7, 6, 3, 2),
+ ["first", 2, 6, 2],
+ ["first", 3, 7, 3])
+
+def testAllowFilteringWithClusteringColumn(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(k int, c int, v int, primary key (k, c))") as table:
+ execute(cql, table, "INSERT INTO %s (k, c, v) VALUES(?, ?, ?)", 1, 2, 1)
+ execute(cql, table, "INSERT INTO %s (k, c, v) VALUES(?, ?, ?)", 1, 3, 2)
+ execute(cql, table, "INSERT INTO %s (k, c, v) VALUES(?, ?, ?)", 2, 2, 3)
+
+ # Don't require filtering, always allowed
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k = ?", 1),
+ [1, 2, 1],
+ [1, 3, 2])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k = ? AND c > ?", 1, 2), [1, 3, 2])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k = ? AND c = ?", 1, 2), [1, 2, 1])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k = ? ALLOW FILTERING", 1),
+ [1, 2, 1],
+ [1, 3, 2])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k = ? AND c > ? ALLOW FILTERING", 1, 2), [1, 3, 2])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k = ? AND c = ? ALLOW FILTERING", 1, 2), [1, 2, 1])
+
+ # Require filtering, allowed only with ALLOW FILTERING
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE c = ?", 2)
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE c > ? AND c <= ?", 2, 4)
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE c = ? ALLOW FILTERING", 2),
+ [1, 2, 1],
+ [2, 2, 3])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE c > ? AND c <= ? ALLOW FILTERING", 2, 4), [1, 3, 2])
+
+def testAllowFilteringWithIndexedColumn(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(k int PRIMARY KEY, a int, b int)") as table:
+ execute(cql, table, "CREATE INDEX ON %s(a)")
+
+ execute(cql, table, "INSERT INTO %s(k, a, b) VALUES(?, ?, ?)", 1, 10, 100)
+ execute(cql, table, "INSERT INTO %s(k, a, b) VALUES(?, ?, ?)", 2, 20, 200)
+ execute(cql, table, "INSERT INTO %s(k, a, b) VALUES(?, ?, ?)", 3, 30, 300)
+ execute(cql, table, "INSERT INTO %s(k, a, b) VALUES(?, ?, ?)", 4, 40, 400)
+
+ # Don't require filtering, always allowed
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k = ?", 1), [1, 10, 100])
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ?", 20), [2, 20, 200])
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k = ? ALLOW FILTERING", 1), [1, 10, 100])
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? ALLOW FILTERING", 20), [2, 20, 200])
+
+ assert_invalid(cql, table, "SELECT * FROM %s WHERE a = ? AND b = ?")
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND b = ? ALLOW FILTERING", 20, 200), [2, 20, 200])
+
+def testAllowFilteringWithIndexedColumnAndStaticColumns(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a int, b int, c int, s int static, PRIMARY KEY (a, b))") as table:
+ execute(cql, table, "CREATE INDEX ON %s(c)")
+
+ execute(cql, table, "INSERT INTO %s(a, b, c, s) VALUES(?, ?, ?, ?)", 1, 1, 1, 1)
+ execute(cql, table, "INSERT INTO %s(a, b, c) VALUES(?, ?, ?)", 1, 2, 1)
+ execute(cql, table, "INSERT INTO %s(a, s) VALUES(?, ?)", 3, 3)
+ execute(cql, table, "INSERT INTO %s(a, b, c, s) VALUES(?, ?, ?, ?)", 2, 1, 1, 2)
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE c = ? AND s > ? ALLOW FILTERING", 1, 1),
+ [2, 1, 2, 1])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE c = ? AND s < ? ALLOW FILTERING", 1, 2),
+ [1, 1, 1, 1],
+ [1, 2, 1, 1])
+
+def testIndexQueriesOnComplexPrimaryKey(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(pk0 int, pk1 int, ck0 int, ck1 int, ck2 int, value int, PRIMARY KEY ((pk0, pk1), ck0, ck1, ck2))") as table:
+ execute(cql, table, "CREATE INDEX ON %s(ck1)")
+ execute(cql, table, "CREATE INDEX ON %s(ck2)")
+ execute(cql, table, "CREATE INDEX ON %s(pk0)")
+ execute(cql, table, "CREATE INDEX ON %s(ck0)")
+
+ execute(cql, table, "INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 0, 1, 2, 3, 4, 5)
+ execute(cql, table, "INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 1, 2, 3, 4, 5, 0)
+ execute(cql, table, "INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 2, 3, 4, 5, 0, 1)
+ execute(cql, table, "INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 3, 4, 5, 0, 1, 2)
+ execute(cql, table, "INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 4, 5, 0, 1, 2, 3)
+ execute(cql, table, "INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 5, 0, 1, 2, 3, 4)
+
+ assert_rows(execute(cql, table, "SELECT value FROM %s WHERE pk0 = 2"), [1])
+ assert_rows(execute(cql, table, "SELECT value FROM %s WHERE ck0 = 0"), [3])
+ assert_rows(execute(cql, table, "SELECT value FROM %s WHERE pk0 = 3 AND pk1 = 4 AND ck1 = 0"), [2])
+ assert_rows(execute(cql, table, "SELECT value FROM %s WHERE pk0 = 5 AND pk1 = 0 AND ck0 = 1 AND ck2 = 3 ALLOW FILTERING"), [4])
+
+def testIndexOnClusteringColumns(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(id1 int, id2 int, author text, time bigint, v1 text, v2 text, PRIMARY KEY ((id1, id2), author, time))") as table:
+ execute(cql, table, "CREATE INDEX ON %s(time)")
+ execute(cql, table, "CREATE INDEX ON %s(id2)")
+
+ execute(cql, table, "INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 0, 'bob', 0, 'A', 'A')")
+ execute(cql, table, "INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 0, 'bob', 1, 'B', 'B')")
+ execute(cql, table, "INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 1, 'bob', 2, 'C', 'C')")
+ execute(cql, table, "INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 0, 'tom', 0, 'D', 'D')")
+ execute(cql, table, "INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 1, 'tom', 1, 'E', 'E')")
+
+ assert_rows(execute(cql, table, "SELECT v1 FROM %s WHERE time = 1"), ["B"], ["E"])
+
+ assert_rows(execute(cql, table, "SELECT v1 FROM %s WHERE id2 = 1"), ["C"], ["E"])
+
+ assert_rows(execute(cql, table, "SELECT v1 FROM %s WHERE id1 = 0 AND id2 = 0 AND author = 'bob' AND time = 0"), ["A"])
+
+ # Test for CASSANDRA-8206
+ execute(cql, table, "UPDATE %s SET v2 = null WHERE id1 = 0 AND id2 = 0 AND author = 'bob' AND time = 1")
+
+ assert_rows(execute(cql, table, "SELECT v1 FROM %s WHERE id2 = 0"), ["A"], ["B"], ["D"])
+
+ assert_rows(execute(cql, table, "SELECT v1 FROM %s WHERE time = 1"), ["B"], ["E"])
+
+ # Scylla does support IN restrictions of any column when ALLOW
+ # FILTERING is used, so the following does work. See
+ # test_filtering.py::test_filter_in_restriction for a test that
+ # this support is correct.
+ #assert_invalid_message(cql, table, "IN restrictions are not supported on indexed columns",
+ # "SELECT v1 FROM %s WHERE id2 = 0 and time IN (1, 2) ALLOW FILTERING")
+
+ assert_rows(execute(cql, table, "SELECT v1 FROM %s WHERE author > 'ted' AND time = 1 ALLOW FILTERING"), ["E"])
+ assert_rows(execute(cql, table, "SELECT v1 FROM %s WHERE author > 'amy' AND author < 'zoe' AND time = 0 ALLOW FILTERING"),
+ ["A"], ["D"])
+
+def testCompositeIndexWithPrimaryKey(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(blog_id int, time1 int, time2 int, author text, content text, PRIMARY KEY (blog_id, time1, time2))") as table:
+ execute(cql, table, "CREATE INDEX ON %s(author)")
+ req = "INSERT INTO %s (blog_id, time1, time2, author, content) VALUES (?, ?, ?, ?, ?)"
+ execute(cql, table, req, 1, 0, 0, "foo", "bar1")
+ execute(cql, table, req, 1, 0, 1, "foo", "bar2")
+ execute(cql, table, req, 2, 1, 0, "foo", "baz")
+ execute(cql, table, req, 3, 0, 1, "gux", "qux")
+
+ assert_rows(execute(cql, table, "SELECT blog_id, content FROM %s WHERE author='foo'"),
+ [1, "bar1"],
+ [1, "bar2"],
+ [2, "baz"])
+ assert_rows(execute(cql, table, "SELECT blog_id, content FROM %s WHERE time1 > 0 AND author='foo' ALLOW FILTERING"), [2, "baz"])
+ assert_rows(execute(cql, table, "SELECT blog_id, content FROM %s WHERE time1 = 1 AND author='foo' ALLOW FILTERING"), [2, "baz"])
+ assert_rows(execute(cql, table, "SELECT blog_id, content FROM %s WHERE time1 = 1 AND time2 = 0 AND author='foo' ALLOW FILTERING"),
+ [2, "baz"])
+ assert_empty(execute(cql, table, "SELECT content FROM %s WHERE time1 = 1 AND time2 = 1 AND author='foo' ALLOW FILTERING"))
+ assert_empty(execute(cql, table, "SELECT content FROM %s WHERE time1 = 1 AND time2 > 0 AND author='foo' ALLOW FILTERING"))
+
+ # Scylla and Cassandra chose to print different erros in this case -
+ # Cassandra says that ALLOW FILTERING would have made this query
+ # work, while Scylla says that time1 should have also been
+ # restricted.
+ assert_invalid(cql, table,
+ "SELECT content FROM %s WHERE time2 >= 0 AND author='foo'")
+
+def testRangeQueryOnIndex(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(id int primary key, row int, setid int)") as table:
+ execute(cql, table, "CREATE INDEX ON %s(setid)")
+
+ q = "INSERT INTO %s (id, row, setid) VALUES (?, ?, ?);"
+ execute(cql, table, q, 0, 0, 0)
+ execute(cql, table, q, 1, 1, 0)
+ execute(cql, table, q, 2, 2, 0)
+ execute(cql, table, q, 3, 3, 0)
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE setid = 0 AND row < 1;")
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE setid = 0 AND row < 1 ALLOW FILTERING;"), [0, 0, 0])
+
+def testEmptyIN(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(k1 int, k2 int, v int, PRIMARY KEY (k1, k2))") as table:
+ for i in range(3):
+ for j in range(3):
+ execute(cql, table, "INSERT INTO %s (k1, k2, v) VALUES (?, ?, ?)", i, j, i + j)
+
+ assert_empty(execute(cql, table, "SELECT v FROM %s WHERE k1 IN ()"))
+ assert_empty(execute(cql, table, "SELECT v FROM %s WHERE k1 = 0 AND k2 IN ()"))
+
+def testINWithDuplicateValue(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(k1 int, k2 int, v int, PRIMARY KEY (k1, k2))") as table:
+ execute(cql, table, "INSERT INTO %s (k1, k2, v) VALUES (?, ?, ?)", 1, 1, 1)
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k1 IN (?, ?)", 1, 1),
+ [1, 1, 1])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k1 IN (?, ?) AND k2 IN (?, ?)", 1, 1, 1, 1),
+ [1, 1, 1])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k1 = ? AND k2 IN (?, ?)", 1, 1, 1),
+ [1, 1, 1])
+
+...@pytest.mark.xfail(reason="#10577 - max-clustering-key-restrictions-per-query is too low for this test")
+def testLargeClusteringINValues(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(k int, c int, v int, PRIMARY KEY (k, c))") as table:
+ execute(cql, table, "INSERT INTO %s (k, c, v) VALUES (0, 0, 0)")
+ inValues = list(range(10000))
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE k=? AND c IN ?", 0, inValues),
+ [0, 0, 0])
+
+def testMultiplePartitionKeyWithIndex(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a int, b int, c int, d int, e int, f int, PRIMARY KEY ((a, b), c, d, e))") as table:
+ execute(cql, table, "CREATE INDEX ON %s (c)")
+ execute(cql, table, "CREATE INDEX ON %s (f)")
+
+ execute(cql, table, "INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 0, 0, 0, 0)
+ execute(cql, table, "INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 0, 1, 0, 1)
+ execute(cql, table, "INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 0, 1, 1, 2)
+
+ execute(cql, table, "INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 1, 0, 0, 3)
+ execute(cql, table, "INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 1, 1, 0, 4)
+ execute(cql, table, "INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 1, 1, 1, 5)
+
+ execute(cql, table, "INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 2, 0, 0, 5)
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE a = ? AND c = ?", 0, 1)
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c = ? ALLOW FILTERING", 0, 1),
+ [0, 0, 1, 0, 0, 3],
+ [0, 0, 1, 1, 0, 4],
+ [0, 0, 1, 1, 1, 5])
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE a = ? AND c = ? AND d = ?", 0, 1, 1)
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c = ? AND d = ? ALLOW FILTERING", 0, 1, 1),
+ [0, 0, 1, 1, 0, 4],
+ [0, 0, 1, 1, 1, 5])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c IN (?) AND d IN (?) ALLOW FILTERING", 0, 1, 1),
+ [0, 0, 1, 1, 0, 4],
+ [0, 0, 1, 1, 1, 5])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND (c, d) >= (?, ?) ALLOW FILTERING", 0, 1, 1),
+ [0, 0, 1, 1, 0, 4],
+ [0, 0, 1, 1, 1, 5],
+ [0, 0, 2, 0, 0, 5])
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND f = ?", 0, 0, 1, 5)
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND f = ? ALLOW FILTERING", 0, 1, 3, 5),
+ [0, 0, 1, 1, 1, 5])
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND f = ?", 0, 1, 2, 5)
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND f = ? ALLOW FILTERING", 0, 1, 2, 5),
+ [0, 0, 1, 1, 1, 5],
+ [0, 0, 2, 0, 0, 5])
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND d IN (?) AND f = ?", 0, 1, 3, 0, 3)
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND d IN (?) AND f = ? ALLOW FILTERING", 0, 1, 3, 0, 3),
+ [0, 0, 1, 0, 0, 3])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c >= ? ALLOW FILTERING", 0, 1),
+ [0, 0, 1, 0, 0, 3],
+ [0, 0, 1, 1, 0, 4],
+ [0, 0, 1, 1, 1, 5],
+ [0, 0, 2, 0, 0, 5])
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE a = ? AND c >= ? AND f = ?", 0, 1, 5)
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND b = ? AND c >= ? AND f = ?", 0, 0, 1, 5),
+ [0, 0, 1, 1, 1, 5],
+ [0, 0, 2, 0, 0, 5])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c >= ? AND f = ? ALLOW FILTERING", 0, 1, 5),
+ [0, 0, 1, 1, 1, 5],
+ [0, 0, 2, 0, 0, 5])
+
+ assert_invalid_message(cql, table, REQUIRES_ALLOW_FILTERING_MESSAGE,
+ "SELECT * FROM %s WHERE a = ? AND c = ? AND d >= ? AND f = ?", 0, 1, 1, 5)
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND b = ? AND c = ? AND d >= ? AND f = ?", 0, 0, 1, 1, 5),
+ [0, 0, 1, 1, 1, 5])
+
+ assert_rows(execute(cql, table, "SELECT * FROM %s WHERE a = ? AND c = ? AND d >= ? AND f = ? ALLOW FILTERING", 0, 1, 1, 5),
+ [0, 0, 1, 1, 1, 5])
+
+def testFunctionCallWithUnset(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(k int PRIMARY KEY, s text, i int)") as table:
+ # The error messages in Scylla and Cassandra here are slightly
+ # different.
+ assert_invalid_message(cql, table, "unset value for argument",
+ "SELECT * FROM %s WHERE token(k) >= token(?)", UNSET_VALUE)
+ assert_invalid_message(cql, table, "unset value for argument",
+ "SELECT * FROM %s WHERE k = blobAsInt(?)", UNSET_VALUE)
+
+def testLimitWithUnset(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(k int PRIMARY KEY, i int)") as table:
+ execute(cql, table, "INSERT INTO %s (k, i) VALUES (1, 1)")
+ execute(cql, table, "INSERT INTO %s (k, i) VALUES (2, 1)")
+ assert_rows(execute(cql, table, "SELECT k FROM %s LIMIT ?", UNSET_VALUE), # treat as 'unlimited'
+ [1],
+ [2]
+ )
+
+...@pytest.mark.xfail(reason="#10358 - comparison with unset doesn't generate error")
+def testWithUnsetValues(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(k int, i int, j int, s text, PRIMARY KEY (k,i,j))") as table:
+ execute(cql, table, "CREATE INDEX ON %s (s)")
+ # partition key
+ # Test commented out because the Python driver can't send an
+ # UNSET_VALUE for the partition key (it is needed to decide
+ # which coordinator to send the request to!)
+ #assert_invalid_message(cql, table, "Invalid unset value for column k", "SELECT * from %s WHERE k = ?", UNSET_VALUE)
+ assert_invalid_message(cql, table, "Invalid unset value for column k", "SELECT * from %s WHERE k IN ?", UNSET_VALUE)
+ # Test commented out because the Python driver can't send an
+ # UNSET_VALUE for the partition key (it is needed to decide
+ # which coordinator to send the request to!)
+ #assert_invalid_message(cql, table, "Invalid unset value for column k", "SELECT * from %s WHERE k IN(?)", UNSET_VALUE)
+ #assert_invalid_message(cql, table, "Invalid unset value for column k", "SELECT * from %s WHERE k IN(?,?)", 1, UNSET_VALUE)
+ # clustering column
+ # Reproduces #10358:
+ assert_invalid_message(cql, table, "Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i = ?", UNSET_VALUE)
+ assert_invalid_message(cql, table, "Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i IN ?", UNSET_VALUE)
+ assert_invalid_message(cql, table, "Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i IN(?)", UNSET_VALUE)
+ assert_invalid_message(cql, table, "Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i IN(?,?)", 1, UNSET_VALUE)
+ assert_invalid_message(cql, table, "Invalid unset value for column i", "SELECT * from %s WHERE i = ? ALLOW FILTERING", UNSET_VALUE)
+ # indexed column
+ assert_invalid_message(cql, table, "Unsupported unset value for column s", "SELECT * from %s WHERE s = ?", UNSET_VALUE)
+ # range
+ assert_invalid_message(cql, table, "Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i > ?", UNSET_VALUE)
+
+def testInvalidSliceRestrictionOnPartitionKey(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a int PRIMARY KEY, b int, c text)") as table:
+ # Scylla and Cassandra choose to print a different error message
+ # here: Cassandra tells you this query would have worked with
+ # ALLOW FILTERING, while Scylla *also* tells you that this
+ # query would have worked with EQ or IN relations or with token().
+ # The word "filtering" is common to both messages.
+ assert_invalid_message(cql, table, 'filtering',
+ "SELECT * FROM %s WHERE a >= 1 and a < 4")
+ # Again, different error messages. Cassandra says "Multi-column
+ # relations can only be applied to clustering columns but was
+ # applied to: a", Scylla says "Only EQ and IN relation are supported
+ # on the partition key (unless you use the token() function or allow
+ # filtering)". There is no word in common :-(
+ assert_invalid(cql, table,
+ "SELECT * FROM %s WHERE (a) >= (1) and (a) < (4)")
+
+def testInvalidMulticolumnSliceRestrictionOnPartitionKey(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a int, b int, c text, PRIMARY KEY ((a, b)))") as table:
+ assert_invalid_message(cql, table, "Multi-column relations can only be applied to clustering columns but was applied to: a",
+ "SELECT * FROM %s WHERE (a, b) >= (1, 1) and (a, b) < (4, 1)")
+ # Again, different error messages. Cassandra says "Multi-column
+ # relations can only be applied to clustering columns but was
+ # applied to: a", Scylla says "Only EQ and IN relation are supported
+ # on the partition key (unless you use the token() function or allow
+ # filtering)". There is no word in common :-(
+ assert_invalid(cql, table,
+ "SELECT * FROM %s WHERE a >= 1 and (a, b) < (4, 1)")
+ assert_invalid(cql, table,
+ "SELECT * FROM %s WHERE b >= 1 and (a, b) < (4, 1)")
+ assert_invalid_message(cql, table, "Multi-column relations can only be applied to clustering columns but was applied to: a",
+ "SELECT * FROM %s WHERE (a, b) >= (1, 1) and (b) < (4)")
+ assert_invalid_message(cql, table, "Multi-column relations can only be applied to clustering columns but was applied to: b",
+ "SELECT * FROM %s WHERE (b) < (4) and (a, b) >= (1, 1)")
+ assert_invalid_message(cql, table, "Multi-column relations can only be applied to clustering columns but was applied to: a",
+ "SELECT * FROM %s WHERE (a, b) >= (1, 1) and a = 1")
+
+def testInvalidColumnNames(cql, test_keyspace):
+ with create_table(cql, test_keyspace, "(a int, b int, c map<int, int>, PRIMARY KEY (a, b))") as table:
+ # Slightly different error messages in Scylla and Cassandra. Both
+ # include the string "name d".
+ assert_invalid_message(cql, table, "name d", "SELECT * FROM %s WHERE d = 0")
+ assert_invalid_message(cql, table, "name d", "SELECT * FROM %s WHERE d IN (0, 1)")
+ assert_invalid_message(cql, table, "name d", "SELECT * FROM %s WHERE d > 0 and d <= 2")
+ assert_invalid_message(cql, table, "name d", "SELECT * FROM %s WHERE d CONTAINS 0")
+ assert_invalid_message(cql, table, "name d", "SELECT * FROM %s WHERE d CONTAINS KEY 0")
+ # Here, Cassandra says "Undefined column name d" but Scylla gives
+ # a clearer error message about the real cuase: "Aliases aren't
+ # allowed in the where clause ('d = 0')".
+ assert_invalid(cql, table, "SELECT a AS d FROM %s WHERE d = 0")
+ assert_invalid(cql, table, "SELECT b AS d FROM %s WHERE d IN (0, 1)")
+ assert_invalid(cql, table, "SELECT b AS d FROM %s WHERE d > 0 and d <= 2")
+ assert_invalid(cql, table, "SELECT c AS d FROM %s WHERE d CONTAINS 0")
+ assert_invalid(cql, table, "SELECT c AS d FROM %s WHERE d CONTAINS KEY 0")
+ assert_invalid_message(cql, table, "name d", "SELECT d FROM %s WHERE a = 0")
+
+...@pytest.mark.xfail(reason="#10632 - strange error message")
+def testInvalidNonFrozenUDTRelation(cql, test_keyspace):
+ with create_type(cql, test_keyspace, "(a int)") as type:
+ with create_table(cql, test_keyspace, f"(a int PRIMARY KEY, b {type})") as table:
+ udt = user_type("a", 1)
+ ks, t = type.split('.')
+
+ # All operators
+ # As decided https://issues.apache.org/jira/browse/CASSANDRA-13247,
+ # Cassandra does not allow restrictions on non-frozen UDTs.
+ # Scylla does implement them, so the commented out tests below
+ # are not relevant (Scylla will complain that ALLOW FILTERING
+ # is missing, not about the non-frozen UDT).
+ msg = "Non-frozen UDT column 'b' (" + t + ") cannot be restricted by any relation"
+ #assert_invalid_message(cql, table, msg, "SELECT * FROM %s WHERE b = ?", udt)
+ #assert_invalid_message(cql, table, msg, "SELECT * FROM %s WHERE b > ?", udt)
+ #assert_invalid_message(cql, table, msg, "SELECT * FROM %s WHERE b < ?", udt)
+ #assert_invalid_message(cql, table, msg, "SELECT * FROM %s WHERE b >= ?", udt)
+ #assert_invalid_message(cql, table, msg, "SELECT * FROM %s WHERE b <= ?", udt)
+ #assert_invalid_message(cql, table, msg, "SELECT * FROM %s WHERE b IN (?)", udt)
+ # Scylla and Cassandra print different errors here - Scylla
+ # says that b is not a string, Cassandra says it is a non-frozen
+ # UDT.
+ assert_invalid(cql, table, "SELECT * FROM %s WHERE b LIKE ?", udt)
+ assert_invalid_message(cql, table, "Unsupported \"!=\" relation",
+ "SELECT * FROM %s WHERE b != {a: 0}", udt)
+ # Reproduces #10632:
+ assert_invalid_message(cql, table, "b IS NOT",
+ "SELECT * FROM %s WHERE b IS NOT NULL", udt)
+ assert_invalid_message(cql, table, "Cannot use CONTAINS on non-collection column",
+ "SELECT * FROM %s WHERE b CONTAINS ?", udt)
diff --git a/test/cql-pytest/test_filtering.py b/test/cql-pytest/test_filtering.py
--- a/test/cql-pytest/test_filtering.py
+++ b/test/cql-pytest/test_filtering.py
@@ -12,7 +12,7 @@

import pytest
import re
-from util import new_test_table
+from util import new_test_table, new_type, user_type
from cassandra.protocol import InvalidRequest
from cassandra.connection import DRIVER_NAME, DRIVER_VERSION
from cassandra.query import UNSET_VALUE
@@ -256,7 +256,8 @@ def test_filtering_null_map_with_subscript(cql, test_keyspace):
# Cassandra does *not* allow such expressions, giving errors such as
# "Column "c" cannot be restricted by both an equality and an inequality
# relation", "More than one restriction was found for the start bound on
-# c", and so on.
+# c", "c cannot be restricted by more than one relation if it includes a
+# IN", and so on.
#
# Scylla chose to *allow* such expressions. In that case, we need to verify
# that it at least gives the correct results - if the two restrictions
@@ -284,3 +285,67 @@ def test_multiple_restrictions_on_same_column(cql, test_keyspace, scylla_only):
assert list(cql.execute(f"SELECT c FROM {table} WHERE p = {p} and c > 1 and c < 1")) == []
assert list(cql.execute(f"SELECT c FROM {table} WHERE p = {p} and c >= 1 and c <= 1")) == [(1,)]
assert list(cql.execute(f"SELECT c FROM {table} WHERE p = {p} and c >= 1 and c <= 2")) == [(1,),(2,)]
+ assert list(cql.execute(f"SELECT c FROM {table} WHERE p = {p} and c = 1 and c in (2, 3)")) == []
+ assert list(cql.execute(f"SELECT c FROM {table} WHERE p = {p} and c = 2 and c in (2, 3)")) == [(2,)]
+
+# Cassandra does not allow IN restrictions on non-primary-key columns,
+# even when doing filtering (ALLOW FILTERING). Scylla does support this
+# case, but in this test let's check that it returns correct results.
+# Cassandra consider this a bug (the message say "not yet supported")
+# so we mark this test cassandra_bug (maybe one day it will start working
+# on Cassandra).
+def test_filter_in_restriction(cql, test_keyspace, cassandra_bug):
+ schema = 'pk int, ck int, x int, PRIMARY KEY (pk, ck)'
+ with new_test_table(cql, test_keyspace, schema) as table:
+ stmt = cql.prepare(f'INSERT INTO {table} (pk, ck, x) VALUES (?, ?, ?)')
+ for i in range(3):
+ cql.execute(stmt, [1, i, i*2])
+ assert [(1,), (2,)] == list(cql.execute(f'SELECT ck FROM {table} WHERE x IN (2, 4) ALLOW FILTERING'))
+ assert [(1,)] == list(cql.execute(f'SELECT ck FROM {table} WHERE x IN (2, 7) ALLOW FILTERING'))
+ assert [] == list(cql.execute(f'SELECT ck FROM {table} WHERE x IN (3, 7) ALLOW FILTERING'))
+
+
+# Both Cassandra and Scylla allow filtering restrictions on frozen UDTs,
+# and the "frozen=True" case of the following test verifies their behavior
+# is the same in this case.
+# Non-frozen UDTs could also theoretically behave the same - but they are
+# currently not allowed in Cassandra (this was decided in CASSANDRA-13247).
+# Scylla, however, does allow filtering on non-frozen UDTs so the
+# "frozen=False" case of the following test verifies that they behave just
+# like frozen ones.
+# The non-frozen ("frozen=False") case is expected to fail on Cassandra
+# but the frozen case is expected to pass.
+def test_filter_UDT_restriction_frozen(cql, test_keyspace):
+ do_test_filter_UDT_restriction(cql, test_keyspace, frozen=True)
+def test_filter_UDT_restriction_nonfrozen(cql, test_keyspace, cassandra_bug):
+ do_test_filter_UDT_restriction(cql, test_keyspace, frozen=False)
+def do_test_filter_UDT_restriction(cql, test_keyspace, frozen):
+ # Single-integer UDT, should be comparable like a normal integer:
+ with new_type(cql, test_keyspace, "(a int)") as typ:
+ ftyp = f"frozen<{typ}>" if frozen else typ
+ schema = f"pk int, ck int, x {ftyp}, PRIMARY KEY (pk, ck)"
+ with new_test_table(cql, test_keyspace, schema) as table:
+ stmt = cql.prepare(f"INSERT INTO {table} (pk, ck, x) VALUES (?, ?, ?)")
+ for i in range(5):
+ cql.execute(stmt, [1, i, user_type("a", i*2)])
+ stmt = cql.prepare(f"SELECT ck FROM {table} WHERE x = ? ALLOW FILTERING")
+ assert [(2,)] == list(cql.execute(stmt, [user_type("a", 4)]))
+ assert [] == list(cql.execute(stmt, [user_type("a", 3)]))
+ stmt = cql.prepare(f"SELECT ck FROM {table} WHERE x < ? ALLOW FILTERING")
+ assert [(0,), (1,)] == list(cql.execute(stmt, [user_type("a", 4)]))
+ assert [] == list(cql.execute(stmt, [user_type("a", -1)]))
+ # UDT with two integers. EQ operator is obvious, LT is lexicographical
+ with new_type(cql, test_keyspace, "(a int, b int)") as typ:
+ ftyp = f"frozen<{typ}>" if frozen else typ
+ schema = f"pk int, ck int, x {ftyp}, PRIMARY KEY (pk, ck)"
+ with new_test_table(cql, test_keyspace, schema) as table:
+ stmt = cql.prepare(f"INSERT INTO {table} (pk, ck, x) VALUES (?, ?, ?)")
+ for i in range(5):
+ cql.execute(stmt, [1, i, user_type("a", i*2, "b", i*3)])
+ stmt = cql.prepare(f"SELECT ck FROM {table} WHERE x = ? ALLOW FILTERING")
+ assert [(2,)] == list(cql.execute(stmt, [user_type("a", 4, "b", 6)]))
+ assert [] == list(cql.execute(stmt, [user_type("a", 4, "b", 5)]))
+ stmt = cql.prepare(f"SELECT ck FROM {table} WHERE x < ? ALLOW FILTERING")
+ assert [(0,), (1,)] == list(cql.execute(stmt, [user_type("a", 4, "b", 6)]))
+ assert [(0,), (1,), (2,)] == list(cql.execute(stmt, [user_type("a", 4, "b", 7)]))
+ assert [] == list(cql.execute(stmt, [user_type("a", -1, "b", 7)]))
diff --git a/test/cql-pytest/test_secondary_index.py b/test/cql-pytest/test_secondary_index.py
--- a/test/cql-pytest/test_secondary_index.py
+++ b/test/cql-pytest/test_secondary_index.py
@@ -344,3 +344,23 @@ def test_index_weird_chars_in_col_name(cql, test_keyspace):
iswordchar = lambda x: str.isalnum(x) or x == '_'
cleaned_up_column_name = ''.join(filter(iswordchar, magic_path))
assert index_name == cf + '_' + cleaned_up_column_name + '_idx'
+
+# Cassandra does not allow IN restrictions on non-primary-key columns,
+# and Scylla does (see test_filtering.py::test_filter_in_restriction).
+# However Scylla currently allows this only with ALLOW FILTERING.
+# In theory, on an index column we could allow it also without filtering,
+# just like we allow it on the partition key. But currently we don't
+# so the following test is marked xfail. It's also cassandra_bug because
+# Cassandra doesn't support it either (it gives the message "not yet
+# supported" suggesting it may be fixed in the future).
+...@pytest.mark.xfail
+def test_index_in_restriction(cql, test_keyspace, cassandra_bug):
+ schema = 'pk int, ck int, x int, PRIMARY KEY (pk, ck)'
+ with new_test_table(cql, test_keyspace, schema) as table:
+ cql.execute(f'CREATE INDEX ON {table}(x)')
+ stmt = cql.prepare(f'INSERT INTO {table} (pk, ck, x) VALUES (?, ?, ?)')
+ for i in range(3):
+ cql.execute(stmt, [1, i, i*2])
+ assert [(1,), (2,)] == list(cql.execute(f'SELECT ck FROM {table} WHERE x IN (2, 4)'))
+ assert [(1,)] == list(cql.execute(f'SELECT ck FROM {table} WHERE x IN (2, 7)'))
+ assert [] == list(cql.execute(f'SELECT ck FROM {table} WHERE x IN (3, 7)'))
diff --git a/test/cql-pytest/util.py b/test/cql-pytest/util.py
--- a/test/cql-pytest/util.py
+++ b/test/cql-pytest/util.py
@@ -11,6 +11,7 @@
import time
import socket
import os
+import collections
from contextlib import contextmanager

def random_string(length=10, chars=string.ascii_uppercase + string.digits):
@@ -74,6 +75,15 @@ def new_test_table(cql, keyspace, schema, extra=""):
finally:
cql.execute("DROP TABLE " + table)

+# A utility function for creating a new temporary user-defined type.
+@contextmanager
+def new_type(cql, keyspace, cmd):
+ type_name = keyspace + "." + unique_name()
+ cql.execute("CREATE TYPE " + type_name + " " + cmd)
+ try:
+ yield type_name
+ finally:
+ cql.execute("DROP TYPE " + type_name)

# A utility function for creating a new temporary user-defined function.
@contextmanager
@@ -170,3 +180,9 @@ def local_process_id(cql):
# Ignore errors. We can't check processes we don't own.
pass
return None
+
+# user_type("a", 1, "b", 2) creates a named tuple with component names "a", "b"
+# and values 1, 2. The return of this function can be used to bind to a UDT.
+# The number of arguments is assumed to be even.
+def user_type(*args):
+ return collections.namedtuple('user_type', args[::2])(*args[1::2])

[Commit Bot]

From: Piotr Sarna <sa...@scylladb.com>
Committer: Piotr Sarna <sa...@scylladb.com>

Branch: master