/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.cassandra.cql3.validation.operations;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.cassandra.cql3.CQLTester;
import org.apache.cassandra.cql3.restrictions.StatementRestrictions;
import org.junit.Test;
public class SelectSingleColumnRelationTest extends CQLTester
{
@Test
public void testInvalidCollectionEqualityRelation() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b set<int>, c list<int>, d map<int, int>)");
createIndex("CREATE INDEX ON %s (b)");
createIndex("CREATE INDEX ON %s (c)");
createIndex("CREATE INDEX ON %s (d)");
assertInvalidMessage("Collection column 'b' (set<int>) cannot be restricted by a '=' relation",
"SELECT * FROM %s WHERE a = 0 AND b=?", set(0));
assertInvalidMessage("Collection column 'c' (list<int>) cannot be restricted by a '=' relation",
"SELECT * FROM %s WHERE a = 0 AND c=?", list(0));
assertInvalidMessage("Collection column 'd' (map<int, int>) cannot be restricted by a '=' relation",
"SELECT * FROM %s WHERE a = 0 AND d=?", map(0, 0));
}
@Test
public void testInvalidCollectionNonEQRelation() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b set<int>, c int)");
createIndex("CREATE INDEX ON %s (c)");
execute("INSERT INTO %s (a, b, c) VALUES (0, {0}, 0)");
// non-EQ operators
assertInvalidMessage("Collection column 'b' (set<int>) cannot be restricted by a '>' relation",
"SELECT * FROM %s WHERE c = 0 AND b > ?", set(0));
assertInvalidMessage("Collection column 'b' (set<int>) cannot be restricted by a '>=' relation",
"SELECT * FROM %s WHERE c = 0 AND b >= ?", set(0));
assertInvalidMessage("Collection column 'b' (set<int>) cannot be restricted by a '<' relation",
"SELECT * FROM %s WHERE c = 0 AND b < ?", set(0));
assertInvalidMessage("Collection column 'b' (set<int>) cannot be restricted by a '<=' relation",
"SELECT * FROM %s WHERE c = 0 AND b <= ?", set(0));
assertInvalidMessage("Collection column 'b' (set<int>) cannot be restricted by a 'IN' relation",
"SELECT * FROM %s WHERE c = 0 AND b IN (?)", set(0));
assertInvalidMessage("Unsupported \"!=\" relation: b != 5",
"SELECT * FROM %s WHERE c = 0 AND b != 5");
assertInvalidMessage("Unsupported restriction: b IS NOT NULL",
"SELECT * FROM %s WHERE c = 0 AND b IS NOT NULL");
}
@Test
public void testClusteringColumnRelations() throws Throwable
{
createTable("CREATE TABLE %s (a text, b int, c int, d int, primary key(a, b, c))");
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 1, 5, 1);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 2, 6, 2);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 3, 7, 3);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 4, 8, 4);
testSelectQueriesWithClusteringColumnRelations();
}
@Test
public void testClusteringColumnRelationsWithCompactStorage() throws Throwable
{
createTable("CREATE TABLE %s (a text, b int, c int, d int, primary key(a, b, c)) WITH COMPACT STORAGE;");
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 1, 5, 1);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 2, 6, 2);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 3, 7, 3);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 4, 8, 4);
testSelectQueriesWithClusteringColumnRelations();
}
private void testSelectQueriesWithClusteringColumnRelations() throws Throwable
{
assertRows(execute("select * from %s where a in (?, ?)", "first", "second"),
row("first", 1, 5, 1),
row("first", 2, 6, 2),
row("first", 3, 7, 3),
row("second", 4, 8, 4));
assertRows(execute("select * from %s where a = ? and b = ? and c in (?, ?)", "first", 2, 6, 7),
row("first", 2, 6, 2));
assertRows(execute("select * from %s where a = ? and b in (?, ?) and c in (?, ?)", "first", 2, 3, 6, 7),
row("first", 2, 6, 2),
row("first", 3, 7, 3));
assertRows(execute("select * from %s where a = ? and b in (?, ?) and c in (?, ?)", "first", 3, 2, 7, 6),
row("first", 2, 6, 2),
row("first", 3, 7, 3));
assertRows(execute("select * from %s where a = ? and c in (?, ?) and b in (?, ?)", "first", 7, 6, 3, 2),
row("first", 2, 6, 2),
row("first", 3, 7, 3));
assertRows(execute("select c, d from %s where a = ? and c in (?, ?) and b in (?, ?)", "first", 7, 6, 3, 2),
row(6, 2),
row(7, 3));
assertRows(execute("select c, d from %s where a = ? and c in (?, ?) and b in (?, ?, ?)", "first", 7, 6, 3, 2, 3),
row(6, 2),
row(7, 3));
assertRows(execute("select * from %s where a = ? and b in (?, ?) and c = ?", "first", 3, 2, 7),
row("first", 3, 7, 3));
assertRows(execute("select * from %s where a = ? and b in ? and c in ?",
"first", Arrays.asList(3, 2), Arrays.asList(7, 6)),
row("first", 2, 6, 2),
row("first", 3, 7, 3));
assertInvalidMessage("Invalid null value for column b",
"select * from %s where a = ? and b in ? and c in ?", "first", null, Arrays.asList(7, 6));
assertRows(execute("select * from %s where a = ? and c >= ? and b in (?, ?)", "first", 6, 3, 2),
row("first", 2, 6, 2),
row("first", 3, 7, 3));
assertRows(execute("select * from %s where a = ? and c > ? and b in (?, ?)", "first", 6, 3, 2),
row("first", 3, 7, 3));
assertRows(execute("select * from %s where a = ? and c <= ? and b in (?, ?)", "first", 6, 3, 2),
row("first", 2, 6, 2));
assertRows(execute("select * from %s where a = ? and c < ? and b in (?, ?)", "first", 7, 3, 2),
row("first", 2, 6, 2));
assertRows(execute("select * from %s where a = ? and c >= ? and c <= ? and b in (?, ?)", "first", 6, 7, 3, 2),
row("first", 2, 6, 2),
row("first", 3, 7, 3));
assertRows(execute("select * from %s where a = ? and c > ? and c <= ? and b in (?, ?)", "first", 6, 7, 3, 2),
row("first", 3, 7, 3));
assertEmpty(execute("select * from %s where a = ? and c > ? and c < ? and b in (?, ?)", "first", 6, 7, 3, 2));
assertInvalidMessage("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);
assertInvalidMessage("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);
assertRows(execute("select * from %s where a = ? and c in (?, ?) and b in (?, ?) order by b DESC",
"first", 7, 6, 3, 2),
row("first", 3, 7, 3),
row("first", 2, 6, 2));
assertInvalidMessage("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);
assertInvalidMessage("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);
}
@Test
public void testPartitionKeyColumnRelations() throws Throwable
{
createTable("CREATE TABLE %s (a text, b int, c int, d int, primary key((a, b), c))");
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 1, 1, 1);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 2, 2, 2);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 3, 3, 3);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 4, 4, 4);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 1, 1, 1);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 4, 4, 4);
assertRows(execute("select * from %s where a = ? and b = ?", "first", 2),
row("first", 2, 2, 2));
assertRows(execute("select * from %s where a in (?, ?) and b in (?, ?)", "first", "second", 2, 3),
row("first", 2, 2, 2),
row("first", 3, 3, 3));
assertRows(execute("select * from %s where a in (?, ?) and b = ?", "first", "second", 4),
row("first", 4, 4, 4),
row("second", 4, 4, 4));
assertRows(execute("select * from %s where a = ? and b in (?, ?)", "first", 3, 4),
row("first", 3, 3, 3),
row("first", 4, 4, 4));
assertRows(execute("select * from %s where a in (?, ?) and b in (?, ?)", "first", "second", 1, 4),
row("first", 1, 1, 1),
row("first", 4, 4, 4),
row("second", 1, 1, 1),
row("second", 4, 4, 4));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"select * from %s where a in (?, ?)", "first", "second");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"select * from %s where a = ?", "first");
assertInvalidMessage("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);
assertInvalidMessage("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);
assertInvalidMessage("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);
assertInvalidMessage("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);
}
@Test
public void testClusteringColumnRelationsWithClusteringOrder() throws Throwable
{
createTable("CREATE TABLE %s (a text, b int, c int, d int, primary key(a, b, c)) WITH CLUSTERING ORDER BY (b DESC);");
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 1, 5, 1);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 2, 6, 2);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "first", 3, 7, 3);
execute("insert into %s (a, b, c, d) values (?, ?, ?, ?)", "second", 4, 8, 4);
assertRows(execute("select * from %s where a = ? and c in (?, ?) and b in (?, ?) order by b DESC",
"first", 7, 6, 3, 2),
row("first", 3, 7, 3),
row("first", 2, 6, 2));
assertRows(execute("select * from %s where a = ? and c in (?, ?) and b in (?, ?) order by b ASC",
"first", 7, 6, 3, 2),
row("first", 2, 6, 2),
row("first", 3, 7, 3));
}
@Test
public void testAllowFilteringWithClusteringColumn() throws Throwable
{
createTable("CREATE TABLE %s (k int, c int, v int, PRIMARY KEY (k, c))");
execute("INSERT INTO %s (k, c, v) VALUES(?, ?, ?)", 1, 2, 1);
execute("INSERT INTO %s (k, c, v) VALUES(?, ?, ?)", 1, 3, 2);
execute("INSERT INTO %s (k, c, v) VALUES(?, ?, ?)", 2, 2, 3);
// Don't require filtering, always allowed
assertRows(execute("SELECT * FROM %s WHERE k = ?", 1),
row(1, 2, 1),
row(1, 3, 2));
assertRows(execute("SELECT * FROM %s WHERE k = ? AND c > ?", 1, 2), row(1, 3, 2));
assertRows(execute("SELECT * FROM %s WHERE k = ? AND c = ?", 1, 2), row(1, 2, 1));
assertRows(execute("SELECT * FROM %s WHERE k = ? ALLOW FILTERING", 1),
row(1, 2, 1),
row(1, 3, 2));
assertRows(execute("SELECT * FROM %s WHERE k = ? AND c > ? ALLOW FILTERING", 1, 2), row(1, 3, 2));
assertRows(execute("SELECT * FROM %s WHERE k = ? AND c = ? ALLOW FILTERING", 1, 2), row(1, 2, 1));
// Require filtering, allowed only with ALLOW FILTERING
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = ?", 2);
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > ? AND c <= ?", 2, 4);
assertRows(execute("SELECT * FROM %s WHERE c = ? ALLOW FILTERING", 2),
row(1, 2, 1),
row(2, 2, 3));
assertRows(execute("SELECT * FROM %s WHERE c > ? AND c <= ? ALLOW FILTERING", 2, 4), row(1, 3, 2));
}
@Test
public void testAllowFilteringWithIndexedColumn() throws Throwable
{
createTable("CREATE TABLE %s (k int PRIMARY KEY, a int, b int)");
createIndex("CREATE INDEX ON %s(a)");
execute("INSERT INTO %s(k, a, b) VALUES(?, ?, ?)", 1, 10, 100);
execute("INSERT INTO %s(k, a, b) VALUES(?, ?, ?)", 2, 20, 200);
execute("INSERT INTO %s(k, a, b) VALUES(?, ?, ?)", 3, 30, 300);
execute("INSERT INTO %s(k, a, b) VALUES(?, ?, ?)", 4, 40, 400);
// Don't require filtering, always allowed
assertRows(execute("SELECT * FROM %s WHERE k = ?", 1), row(1, 10, 100));
assertRows(execute("SELECT * FROM %s WHERE a = ?", 20), row(2, 20, 200));
assertRows(execute("SELECT * FROM %s WHERE k = ? ALLOW FILTERING", 1), row(1, 10, 100));
assertRows(execute("SELECT * FROM %s WHERE a = ? ALLOW FILTERING", 20), row(2, 20, 200));
assertInvalid("SELECT * FROM %s WHERE a = ? AND b = ?");
assertRows(execute("SELECT * FROM %s WHERE a = ? AND b = ? ALLOW FILTERING", 20, 200), row(2, 20, 200));
}
@Test
public void testAllowFilteringWithIndexedColumnAndStaticColumns() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c int, s int static, PRIMARY KEY(a, b))");
createIndex("CREATE INDEX ON %s(c)");
execute("INSERT INTO %s(a, b, c, s) VALUES(?, ?, ?, ?)", 1, 1, 1, 1);
execute("INSERT INTO %s(a, b, c) VALUES(?, ?, ?)", 1, 2, 1);
execute("INSERT INTO %s(a, s) VALUES(?, ?)", 3, 3);
execute("INSERT INTO %s(a, b, c, s) VALUES(?, ?, ?, ?)", 2, 1, 1, 2);
assertRows(execute("SELECT * FROM %s WHERE c = ? AND s > ? ALLOW FILTERING", 1, 1),
row(2, 1, 2, 1));
assertRows(execute("SELECT * FROM %s WHERE c = ? AND s < ? ALLOW FILTERING", 1, 2),
row(1, 1, 1, 1),
row(1, 2, 1, 1));
}
@Test
public void testIndexQueriesOnComplexPrimaryKey() throws Throwable
{
createTable("CREATE TABLE %s (pk0 int, pk1 int, ck0 int, ck1 int, ck2 int, value int, PRIMARY KEY ((pk0, pk1), ck0, ck1, ck2))");
createIndex("CREATE INDEX ON %s (ck1)");
createIndex("CREATE INDEX ON %s (ck2)");
createIndex("CREATE INDEX ON %s (pk0)");
createIndex("CREATE INDEX ON %s (ck0)");
execute("INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 0, 1, 2, 3, 4, 5);
execute("INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 1, 2, 3, 4, 5, 0);
execute("INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 2, 3, 4, 5, 0, 1);
execute("INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 3, 4, 5, 0, 1, 2);
execute("INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 4, 5, 0, 1, 2, 3);
execute("INSERT INTO %s (pk0, pk1, ck0, ck1, ck2, value) VALUES (?, ?, ?, ?, ?, ?)", 5, 0, 1, 2, 3, 4);
assertRows(execute("SELECT value FROM %s WHERE pk0 = 2"), row(1));
assertRows(execute("SELECT value FROM %s WHERE ck0 = 0"), row(3));
assertRows(execute("SELECT value FROM %s WHERE pk0 = 3 AND pk1 = 4 AND ck1 = 0"), row(2));
assertRows(execute("SELECT value FROM %s WHERE pk0 = 5 AND pk1 = 0 AND ck0 = 1 AND ck2 = 3 ALLOW FILTERING"), row(4));
}
@Test
public void testIndexOnClusteringColumns() throws Throwable
{
createTable("CREATE TABLE %s (id1 int, id2 int, author text, time bigint, v1 text, v2 text, PRIMARY KEY ((id1, id2), author, time))");
createIndex("CREATE INDEX ON %s(time)");
createIndex("CREATE INDEX ON %s(id2)");
execute("INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 0, 'bob', 0, 'A', 'A')");
execute("INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 0, 'bob', 1, 'B', 'B')");
execute("INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 1, 'bob', 2, 'C', 'C')");
execute("INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 0, 'tom', 0, 'D', 'D')");
execute("INSERT INTO %s(id1, id2, author, time, v1, v2) VALUES(0, 1, 'tom', 1, 'E', 'E')");
assertRows(execute("SELECT v1 FROM %s WHERE time = 1"), row("B"), row("E"));
assertRows(execute("SELECT v1 FROM %s WHERE id2 = 1"), row("C"), row("E"));
assertRows(execute("SELECT v1 FROM %s WHERE id1 = 0 AND id2 = 0 AND author = 'bob' AND time = 0"), row("A"));
// Test for CASSANDRA-8206
execute("UPDATE %s SET v2 = null WHERE id1 = 0 AND id2 = 0 AND author = 'bob' AND time = 1");
assertRows(execute("SELECT v1 FROM %s WHERE id2 = 0"), row("A"), row("B"), row("D"));
assertRows(execute("SELECT v1 FROM %s WHERE time = 1"), row("B"), row("E"));
assertInvalidMessage("IN restrictions are not supported on indexed columns",
"SELECT v1 FROM %s WHERE id2 = 0 and time IN (1, 2) ALLOW FILTERING");
assertRows(execute("SELECT v1 FROM %s WHERE author > 'ted' AND time = 1 ALLOW FILTERING"), row("E"));
assertRows(execute("SELECT v1 FROM %s WHERE author > 'amy' AND author < 'zoe' AND time = 0 ALLOW FILTERING"),
row("A"), row("D"));
}
@Test
public void testCompositeIndexWithPrimaryKey() throws Throwable
{
createTable("CREATE TABLE %s (blog_id int, time1 int, time2 int, author text, content text, PRIMARY KEY (blog_id, time1, time2))");
createIndex("CREATE INDEX ON %s(author)");
String req = "INSERT INTO %s (blog_id, time1, time2, author, content) VALUES (?, ?, ?, ?, ?)";
execute(req, 1, 0, 0, "foo", "bar1");
execute(req, 1, 0, 1, "foo", "bar2");
execute(req, 2, 1, 0, "foo", "baz");
execute(req, 3, 0, 1, "gux", "qux");
assertRows(execute("SELECT blog_id, content FROM %s WHERE author='foo'"),
row(1, "bar1"),
row(1, "bar2"),
row(2, "baz"));
assertRows(execute("SELECT blog_id, content FROM %s WHERE time1 > 0 AND author='foo' ALLOW FILTERING"), row(2, "baz"));
assertRows(execute("SELECT blog_id, content FROM %s WHERE time1 = 1 AND author='foo' ALLOW FILTERING"), row(2, "baz"));
assertRows(execute("SELECT blog_id, content FROM %s WHERE time1 = 1 AND time2 = 0 AND author='foo' ALLOW FILTERING"),
row(2, "baz"));
assertEmpty(execute("SELECT content FROM %s WHERE time1 = 1 AND time2 = 1 AND author='foo' ALLOW FILTERING"));
assertEmpty(execute("SELECT content FROM %s WHERE time1 = 1 AND time2 > 0 AND author='foo' ALLOW FILTERING"));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT content FROM %s WHERE time2 >= 0 AND author='foo'");
}
@Test
public void testRangeQueryOnIndex() throws Throwable
{
createTable("CREATE TABLE %s (id int primary key, row int, setid int);");
createIndex("CREATE INDEX ON %s (setid)");
String q = "INSERT INTO %s (id, row, setid) VALUES (?, ?, ?);";
execute(q, 0, 0, 0);
execute(q, 1, 1, 0);
execute(q, 2, 2, 0);
execute(q, 3, 3, 0);
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE setid = 0 AND row < 1;");
assertRows(execute("SELECT * FROM %s WHERE setid = 0 AND row < 1 ALLOW FILTERING;"), row(0, 0, 0));
}
@Test
public void testEmptyIN() throws Throwable
{
for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
{
createTable("CREATE TABLE %s (k1 int, k2 int, v int, PRIMARY KEY (k1, k2))" + compactOption);
for (int i = 0; i <= 2; i++)
for (int j = 0; j <= 2; j++)
execute("INSERT INTO %s (k1, k2, v) VALUES (?, ?, ?)", i, j, i + j);
assertEmpty(execute("SELECT v FROM %s WHERE k1 IN ()"));
assertEmpty(execute("SELECT v FROM %s WHERE k1 = 0 AND k2 IN ()"));
}
}
@Test
public void testINWithDuplicateValue() throws Throwable
{
for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
{
createTable("CREATE TABLE %s (k1 int, k2 int, v int, PRIMARY KEY (k1, k2))" + compactOption);
execute("INSERT INTO %s (k1, k2, v) VALUES (?, ?, ?)", 1, 1, 1);
assertRows(execute("SELECT * FROM %s WHERE k1 IN (?, ?)", 1, 1),
row(1, 1, 1));
assertRows(execute("SELECT * FROM %s WHERE k1 IN (?, ?) AND k2 IN (?, ?)", 1, 1, 1, 1),
row(1, 1, 1));
assertRows(execute("SELECT * FROM %s WHERE k1 = ? AND k2 IN (?, ?)", 1, 1, 1),
row(1, 1, 1));
}
}
@Test
public void testLargeClusteringINValues() throws Throwable
{
createTable("CREATE TABLE %s (k int, c int, v int, PRIMARY KEY (k, c))");
execute("INSERT INTO %s (k, c, v) VALUES (0, 0, 0)");
List<Integer> inValues = new ArrayList<>(10000);
for (int i = 0; i < 10000; i++)
inValues.add(i);
assertRows(execute("SELECT * FROM %s WHERE k=? AND c IN ?", 0, inValues),
row(0, 0, 0));
}
@Test
public void testMultiplePartitionKeyWithIndex() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, f int, PRIMARY KEY ((a, b), c, d, e))");
createIndex("CREATE INDEX ON %s (c)");
createIndex("CREATE INDEX ON %s (f)");
execute("INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 0, 0, 0, 0);
execute("INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 0, 1, 0, 1);
execute("INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 0, 1, 1, 2);
execute("INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 1, 0, 0, 3);
execute("INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 1, 1, 0, 4);
execute("INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 1, 1, 1, 5);
execute("INSERT INTO %s (a, b, c, d, e, f) VALUES (?, ?, ?, ?, ?, ?)", 0, 0, 2, 0, 0, 5);
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = ? AND c = ?", 0, 1);
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c = ? ALLOW FILTERING", 0, 1),
row(0, 0, 1, 0, 0, 3),
row(0, 0, 1, 1, 0, 4),
row(0, 0, 1, 1, 1, 5));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = ? AND c = ? AND d = ?", 0, 1, 1);
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c = ? AND d = ? ALLOW FILTERING", 0, 1, 1),
row(0, 0, 1, 1, 0, 4),
row(0, 0, 1, 1, 1, 5));
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c IN (?) AND d IN (?) ALLOW FILTERING", 0, 1, 1),
row(0, 0, 1, 1, 0, 4),
row(0, 0, 1, 1, 1, 5));
assertRows(execute("SELECT * FROM %s WHERE a = ? AND (c, d) >= (?, ?) ALLOW FILTERING", 0, 1, 1),
row(0, 0, 1, 1, 0, 4),
row(0, 0, 1, 1, 1, 5),
row(0, 0, 2, 0, 0, 5));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND f = ?", 0, 0, 1, 5);
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND f = ? ALLOW FILTERING", 0, 1, 3, 5),
row(0, 0, 1, 1, 1, 5));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND f = ?", 0, 1, 2, 5);
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND f = ? ALLOW FILTERING", 0, 1, 2, 5),
row(0, 0, 1, 1, 1, 5),
row(0, 0, 2, 0, 0, 5));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND d IN (?) AND f = ?", 0, 1, 3, 0, 3);
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c IN (?, ?) AND d IN (?) AND f = ? ALLOW FILTERING", 0, 1, 3, 0, 3),
row(0, 0, 1, 0, 0, 3));
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c >= ? ALLOW FILTERING", 0, 1),
row(0, 0, 1, 0, 0, 3),
row(0, 0, 1, 1, 0, 4),
row(0, 0, 1, 1, 1, 5),
row(0, 0, 2, 0, 0, 5));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = ? AND c >= ? AND f = ?", 0, 1, 5);
assertRows(execute("SELECT * FROM %s WHERE a = ? AND b = ? AND c >= ? AND f = ?", 0, 0, 1, 5),
row(0, 0, 1, 1, 1, 5),
row(0, 0, 2, 0, 0, 5));
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c >= ? AND f = ? ALLOW FILTERING", 0, 1, 5),
row(0, 0, 1, 1, 1, 5),
row(0, 0, 2, 0, 0, 5));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = ? AND c = ? AND d >= ? AND f = ?", 0, 1, 1, 5);
assertRows(execute("SELECT * FROM %s WHERE a = ? AND b = ? AND c = ? AND d >= ? AND f = ?", 0, 0, 1, 1, 5),
row(0, 0, 1, 1, 1, 5));
assertRows(execute("SELECT * FROM %s WHERE a = ? AND c = ? AND d >= ? AND f = ? ALLOW FILTERING", 0, 1, 1, 5),
row(0, 0, 1, 1, 1, 5));
}
@Test
public void testFunctionCallWithUnset() throws Throwable
{
createTable("CREATE TABLE %s (k int PRIMARY KEY, s text, i int)");
assertInvalidMessage("Invalid unset value for argument in call to function token",
"SELECT * FROM %s WHERE token(k) >= token(?)", unset());
assertInvalidMessage("Invalid unset value for argument in call to function blobasint",
"SELECT * FROM %s WHERE k = blobAsInt(?)", unset());
}
@Test
public void testLimitWithUnset() throws Throwable
{
createTable("CREATE TABLE %s (k int PRIMARY KEY, i int)");
execute("INSERT INTO %s (k, i) VALUES (1, 1)");
execute("INSERT INTO %s (k, i) VALUES (2, 1)");
assertRows(execute("SELECT k FROM %s LIMIT ?", unset()), // treat as 'unlimited'
row(1),
row(2)
);
}
@Test
public void testWithUnsetValues() throws Throwable
{
createTable("CREATE TABLE %s (k int, i int, j int, s text, PRIMARY KEY(k,i,j))");
createIndex("CREATE INDEX s_index ON %s (s)");
// partition key
assertInvalidMessage("Invalid unset value for column k", "SELECT * from %s WHERE k = ?", unset());
assertInvalidMessage("Invalid unset value for column k", "SELECT * from %s WHERE k IN ?", unset());
assertInvalidMessage("Invalid unset value for column k", "SELECT * from %s WHERE k IN(?)", unset());
assertInvalidMessage("Invalid unset value for column k", "SELECT * from %s WHERE k IN(?,?)", 1, unset());
// clustering column
assertInvalidMessage("Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i = ?", unset());
assertInvalidMessage("Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i IN ?", unset());
assertInvalidMessage("Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i IN(?)", unset());
assertInvalidMessage("Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i IN(?,?)", 1, unset());
assertInvalidMessage("Invalid unset value for column i", "SELECT * from %s WHERE i = ? ALLOW FILTERING", unset());
// indexed column
assertInvalidMessage("Unsupported unset value for column s", "SELECT * from %s WHERE s = ?", unset());
// range
assertInvalidMessage("Invalid unset value for column i", "SELECT * from %s WHERE k = 1 AND i > ?", unset());
}
@Test
public void testInvalidSliceRestrictionOnPartitionKey() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c text)");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 and a < 4");
assertInvalidMessage("Multi-column relations can only be applied to clustering columns but was applied to: a",
"SELECT * FROM %s WHERE (a) >= (1) and (a) < (4)");
}
@Test
public void testInvalidMulticolumnSliceRestrictionOnPartitionKey() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c text, PRIMARY KEY ((a, b)))");
assertInvalidMessage("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)");
assertInvalidMessage("Multi-column relations can only be applied to clustering columns but was applied to: a",
"SELECT * FROM %s WHERE a >= 1 and (a, b) < (4, 1)");
assertInvalidMessage("Multi-column relations can only be applied to clustering columns but was applied to: a",
"SELECT * FROM %s WHERE b >= 1 and (a, b) < (4, 1)");
assertInvalidMessage("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)");
assertInvalidMessage("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)");
assertInvalidMessage("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");
}
@Test
public void testInvalidColumnNames() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c map<int, int>, PRIMARY KEY (a, b))");
assertInvalidMessage("Undefined column name d", "SELECT * FROM %s WHERE d = 0");
assertInvalidMessage("Undefined column name d", "SELECT * FROM %s WHERE d IN (0, 1)");
assertInvalidMessage("Undefined column name d", "SELECT * FROM %s WHERE d > 0 and d <= 2");
assertInvalidMessage("Undefined column name d", "SELECT * FROM %s WHERE d CONTAINS 0");
assertInvalidMessage("Undefined column name d", "SELECT * FROM %s WHERE d CONTAINS KEY 0");
assertInvalidMessage("Undefined column name d", "SELECT a AS d FROM %s WHERE d = 0");
assertInvalidMessage("Undefined column name d", "SELECT b AS d FROM %s WHERE d IN (0, 1)");
assertInvalidMessage("Undefined column name d", "SELECT b AS d FROM %s WHERE d > 0 and d <= 2");
assertInvalidMessage("Undefined column name d", "SELECT c AS d FROM %s WHERE d CONTAINS 0");
assertInvalidMessage("Undefined column name d", "SELECT c AS d FROM %s WHERE d CONTAINS KEY 0");
assertInvalidMessage("Undefined column name d", "SELECT d FROM %s WHERE a = 0");
}
@Test
public void testInvalidNonFrozenUDTRelation() throws Throwable
{
String type = createType("CREATE TYPE %s (a int)");
createTable("CREATE TABLE %s (a int PRIMARY KEY, b " + type + ")");
Object udt = userType("a", 1);
// All operators
String msg = "Non-frozen UDT column 'b' (" + type + ") cannot be restricted by any relation";
assertInvalidMessage(msg, "SELECT * FROM %s WHERE b = ?", udt);
assertInvalidMessage(msg, "SELECT * FROM %s WHERE b > ?", udt);
assertInvalidMessage(msg, "SELECT * FROM %s WHERE b < ?", udt);
assertInvalidMessage(msg, "SELECT * FROM %s WHERE b >= ?", udt);
assertInvalidMessage(msg, "SELECT * FROM %s WHERE b <= ?", udt);
assertInvalidMessage(msg, "SELECT * FROM %s WHERE b IN (?)", udt);
assertInvalidMessage(msg, "SELECT * FROM %s WHERE b LIKE ?", udt);
assertInvalidMessage("Unsupported \"!=\" relation: b != {a: 0}",
"SELECT * FROM %s WHERE b != {a: 0}", udt);
assertInvalidMessage("Unsupported restriction: b IS NOT NULL",
"SELECT * FROM %s WHERE b IS NOT NULL", udt);
assertInvalidMessage("Cannot use CONTAINS on non-collection column b",
"SELECT * FROM %s WHERE b CONTAINS ?", udt);
}
}