Google Git
Sign in
apache / cassandra / 821e7ab7bd7605842d9a898aa4022e15b60dd44d / . / test / unit / org / apache / cassandra / cql3 / validation / operations / CompactStorageTest.java
blob: b02c6ffed6956e9930d1e88d989b9e467020a2e4 [file] [log] [blame]
/*
* 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.nio.ByteBuffer;
import java.util.Arrays;
import java.util.EnumSet;
import java.util.UUID;
import org.junit.Assert;
import org.junit.Test;
import org.apache.cassandra.SchemaLoader;
import org.apache.cassandra.cql3.CQLTester;
import org.apache.cassandra.cql3.UntypedResultSet;
import org.apache.cassandra.cql3.restrictions.StatementRestrictions;
import org.apache.cassandra.cql3.validation.entities.SecondaryIndexTest;
import org.apache.cassandra.db.ColumnFamilyStore;
import org.apache.cassandra.db.Keyspace;
import org.apache.cassandra.db.RowUpdateBuilder;
import org.apache.cassandra.db.SchemaCQLHelper;
import org.apache.cassandra.db.marshal.AsciiType;
import org.apache.cassandra.db.marshal.CounterColumnType;
import org.apache.cassandra.db.marshal.IntegerType;
import org.apache.cassandra.db.marshal.ReversedType;
import org.apache.cassandra.db.partitions.PartitionUpdate;
import org.apache.cassandra.schema.KeyspaceParams;
import org.apache.cassandra.schema.TableMetadata;
import org.apache.cassandra.utils.ByteBufferUtil;
import org.apache.cassandra.utils.FBUtilities;
import static junit.framework.TestCase.assertTrue;
import static org.apache.cassandra.utils.ByteBufferUtil.EMPTY_BYTE_BUFFER;
import static org.apache.cassandra.utils.ByteBufferUtil.bytes;
import static org.junit.Assert.assertArrayEquals;
import static org.junit.Assert.fail;
import static org.reflections.util.Utils.isEmpty;
public class CompactStorageTest extends CQLTester
{
private static final String compactOption = " WITH COMPACT STORAGE";
@Test
public void testSparseCompactTableIndex() throws Throwable
{
createTable("CREATE TABLE %s (key ascii PRIMARY KEY, val ascii) WITH COMPACT STORAGE");
// Indexes are allowed only on the sparse compact tables
createIndex("CREATE INDEX ON %s(val)");
for (int i = 0; i < 10; i++)
execute("INSERT INTO %s (key, val) VALUES (?, ?)", Integer.toString(i), Integer.toString(i * 10));
alterTable("ALTER TABLE %s DROP COMPACT STORAGE");
assertRows(execute("SELECT * FROM %s WHERE val = '50'"),
row("5", null, "50", null));
assertRows(execute("SELECT * FROM %s WHERE key = '5'"),
row("5", null, "50", null));
}
@Test
public void before() throws Throwable
{
createTable("CREATE TABLE %s (key TEXT, column TEXT, value BLOB, PRIMARY KEY (key, column)) WITH COMPACT STORAGE");
ByteBuffer largeBytes = ByteBuffer.wrap(new byte[100000]);
execute("INSERT INTO %s (key, column, value) VALUES (?, ?, ?)", "test", "a", largeBytes);
ByteBuffer smallBytes = ByteBuffer.wrap(new byte[10]);
execute("INSERT INTO %s (key, column, value) VALUES (?, ?, ?)", "test", "c", smallBytes);
flush();
assertRows(execute("SELECT column FROM %s WHERE key = ? AND column IN (?, ?, ?)", "test", "c", "a", "b"),
row("a"),
row("c"));
}
@Test
public void testStaticCompactTables() throws Throwable
{
createTable("CREATE TABLE %s (k text PRIMARY KEY, v1 int, v2 text) WITH COMPACT STORAGE");
execute("INSERT INTO %s (k, v1, v2) values (?, ?, ?)", "first", 1, "value1");
execute("INSERT INTO %s (k, v1, v2) values (?, ?, ?)", "second", 2, "value2");
execute("INSERT INTO %s (k, v1, v2) values (?, ?, ?)", "third", 3, "value3");
assertRows(execute("SELECT * FROM %s WHERE k = ?", "first"),
row("first", 1, "value1")
);
assertRows(execute("SELECT v2 FROM %s WHERE k = ?", "second"),
row("value2")
);
// Murmur3 order
assertRows(execute("SELECT * FROM %s"),
row("third", 3, "value3"),
row("second", 2, "value2"),
row("first", 1, "value1")
);
}
@Test
public void testCompactStorageUpdateWithNull() throws Throwable
{
createTable("CREATE TABLE %s (partitionKey int," +
"clustering_1 int," +
"value int," +
" PRIMARY KEY (partitionKey, clustering_1)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (partitionKey, clustering_1, value) VALUES (0, 0, 0)");
execute("INSERT INTO %s (partitionKey, clustering_1, value) VALUES (0, 1, 1)");
flush();
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ?", null, 0, 0);
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND (clustering_1) IN ((?), (?))", 0, 0, 1),
row(0, 1, 1)
);
}
/**
* Migrated from cql_tests.py:TestCQL.collection_compact_test()
*/
@Test
public void testCompactCollections() throws Throwable
{
String tableName = KEYSPACE + "." + createTableName();
assertInvalid(String.format("CREATE TABLE %s (user ascii PRIMARY KEY, mails list < text >) WITH COMPACT STORAGE;", tableName));
}
/**
* Check for a table with counters,
* migrated from cql_tests.py:TestCQL.counters_test()
*/
@Test
public void testCounters() throws Throwable
{
createTable("CREATE TABLE %s (userid int, url text, total counter, PRIMARY KEY (userid, url)) WITH COMPACT STORAGE");
execute("UPDATE %s SET total = total + 1 WHERE userid = 1 AND url = 'http://foo.com'");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(1L));
execute("UPDATE %s SET total = total - 4 WHERE userid = 1 AND url = 'http://foo.com'");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(-3L));
execute("UPDATE %s SET total = total+1 WHERE userid = 1 AND url = 'http://foo.com'");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(-2L));
execute("UPDATE %s SET total = total -2 WHERE userid = 1 AND url = 'http://foo.com'");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(-4L));
execute("UPDATE %s SET total += 6 WHERE userid = 1 AND url = 'http://foo.com'");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(2L));
execute("UPDATE %s SET total -= 1 WHERE userid = 1 AND url = 'http://foo.com'");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(1L));
execute("UPDATE %s SET total += -2 WHERE userid = 1 AND url = 'http://foo.com'");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(-1L));
execute("UPDATE %s SET total -= -2 WHERE userid = 1 AND url = 'http://foo.com'");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(1L));
}
@Test
public void testCounterFiltering() throws Throwable
{
createTable("CREATE TABLE %s (k int PRIMARY KEY, a counter) WITH COMPACT STORAGE");
for (int i = 0; i < 10; i++)
execute("UPDATE %s SET a = a + ? WHERE k = ?", (long) i, i);
execute("UPDATE %s SET a = a + ? WHERE k = ?", 6L, 10);
// GT
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a > ? ALLOW FILTERING", 5L),
row(6, 6L),
row(7, 7L),
row(8, 8L),
row(9, 9L),
row(10, 6L));
// GTE
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a >= ? ALLOW FILTERING", 6L),
row(6, 6L),
row(7, 7L),
row(8, 8L),
row(9, 9L),
row(10, 6L));
// LT
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a < ? ALLOW FILTERING", 3L),
row(0, 0L),
row(1, 1L),
row(2, 2L));
// LTE
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a <= ? ALLOW FILTERING", 3L),
row(0, 0L),
row(1, 1L),
row(2, 2L),
row(3, 3L));
// EQ
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a = ? ALLOW FILTERING", 6L),
row(6, 6L),
row(10, 6L));
}
/**
* Test for the bug of #11726.
*/
@Test
public void testCounterAndColumnSelection() throws Throwable
{
for (String compactStorageClause : new String[]{ "", " WITH COMPACT STORAGE" })
{
createTable("CREATE TABLE %s (k int PRIMARY KEY, c counter)" + compactStorageClause);
// Flush 2 updates in different sstable so that the following select does a merge, which is what triggers
// the problem from #11726
execute("UPDATE %s SET c = c + ? WHERE k = ?", 1L, 0);
flush();
execute("UPDATE %s SET c = c + ? WHERE k = ?", 1L, 0);
flush();
// Querying, but not including the counter. Pre-CASSANDRA-11726, this made us query the counter but include
// it's value, which broke at merge (post-CASSANDRA-11726 are special cases to never skip values).
assertRows(execute("SELECT k FROM %s"), row(0));
}
}
/*
* Check that a counter batch works as intended
*/
@Test
public void testCounterBatch() throws Throwable
{
createTable("CREATE TABLE %s (userid int, url text, total counter, PRIMARY KEY (userid, url)) WITH COMPACT STORAGE");
// Ensure we handle updates to the same CQL row in the same partition properly
execute("BEGIN UNLOGGED BATCH " +
"UPDATE %1$s SET total = total + 1 WHERE userid = 1 AND url = 'http://foo.com'; " +
"UPDATE %1$s SET total = total + 1 WHERE userid = 1 AND url = 'http://foo.com'; " +
"UPDATE %1$s SET total = total + 1 WHERE userid = 1 AND url = 'http://foo.com'; " +
"APPLY BATCH; ");
assertRows(execute("SELECT total FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(3L));
// Ensure we handle different CQL rows in the same partition properly
execute("BEGIN UNLOGGED BATCH " +
"UPDATE %1$s SET total = total + 1 WHERE userid = 1 AND url = 'http://bar.com'; " +
"UPDATE %1$s SET total = total + 1 WHERE userid = 1 AND url = 'http://baz.com'; " +
"UPDATE %1$s SET total = total + 1 WHERE userid = 1 AND url = 'http://bad.com'; " +
"APPLY BATCH; ");
assertRows(execute("SELECT url, total FROM %s WHERE userid = 1"),
row("http://bad.com", 1L),
row("http://bar.com", 1L),
row("http://baz.com", 1L),
row("http://foo.com", 3L)); // from previous batch
// Different counters in the same CQL Row
createTable("CREATE TABLE %s (userid int, url text, first counter, second counter, third counter, PRIMARY KEY (userid, url))");
execute("BEGIN UNLOGGED BATCH " +
"UPDATE %1$s SET first = first + 1 WHERE userid = 1 AND url = 'http://foo.com'; " +
"UPDATE %1$s SET first = first + 1 WHERE userid = 1 AND url = 'http://foo.com'; " +
"UPDATE %1$s SET second = second + 1 WHERE userid = 1 AND url = 'http://foo.com'; " +
"APPLY BATCH; ");
assertRows(execute("SELECT first, second, third FROM %s WHERE userid = 1 AND url = 'http://foo.com'"),
row(2L, 1L, null));
// Different counters in different CQL Rows
execute("BEGIN UNLOGGED BATCH " +
"UPDATE %1$s SET first = first + 1 WHERE userid = 1 AND url = 'http://bad.com'; " +
"UPDATE %1$s SET first = first + 1, second = second + 1 WHERE userid = 1 AND url = 'http://bar.com'; " +
"UPDATE %1$s SET first = first - 1, second = second - 1 WHERE userid = 1 AND url = 'http://bar.com'; " +
"UPDATE %1$s SET second = second + 1 WHERE userid = 1 AND url = 'http://baz.com'; " +
"APPLY BATCH; ");
assertRows(execute("SELECT url, first, second, third FROM %s WHERE userid = 1"),
row("http://bad.com", 1L, null, null),
row("http://bar.com", 0L, 0L, null),
row("http://baz.com", null, 1L, null),
row("http://foo.com", 2L, 1L, null)); // from previous batch
// Different counters in different partitions
execute("BEGIN UNLOGGED BATCH " +
"UPDATE %1$s SET first = first + 1 WHERE userid = 2 AND url = 'http://bad.com'; " +
"UPDATE %1$s SET first = first + 1, second = second + 1 WHERE userid = 3 AND url = 'http://bar.com'; " +
"UPDATE %1$s SET first = first - 1, second = second - 1 WHERE userid = 4 AND url = 'http://bar.com'; " +
"UPDATE %1$s SET second = second + 1 WHERE userid = 5 AND url = 'http://baz.com'; " +
"APPLY BATCH; ");
assertRowsIgnoringOrder(execute("SELECT userid, url, first, second, third FROM %s WHERE userid IN (2, 3, 4, 5)"),
row(2, "http://bad.com", 1L, null, null),
row(3, "http://bar.com", 1L, 1L, null),
row(4, "http://bar.com", -1L, -1L, null),
row(5, "http://baz.com", null, 1L, null));
}
/**
* from FrozenCollectionsTest
*/
@Test
public void testClusteringKeyUsageSet() throws Throwable
{
testClusteringKeyUsage("set<int>",
set(),
set(1, 2, 3),
set(4, 5, 6),
set(7, 8, 9));
}
@Test
public void testClusteringKeyUsageList() throws Throwable
{
testClusteringKeyUsage("list<int>",
list(),
list(1, 2, 3),
list(4, 5, 6),
list(7, 8, 9));
}
@Test
public void testClusteringKeyUsageMap() throws Throwable
{
testClusteringKeyUsage("map<int, int>",
map(),
map(1, 10, 2, 20, 3, 30),
map(4, 40, 5, 50, 6, 60),
map(7, 70, 8, 80, 9, 90));
}
private void testClusteringKeyUsage(String type, Object v1, Object v2, Object v3, Object v4) throws Throwable
{
createTable(String.format("CREATE TABLE %%s (a int, b frozen<%s>, c int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE",
type));
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, v1, 1);
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, v2, 1);
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, v3, 0);
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, v4, 0);
// overwrite with an update
execute("UPDATE %s SET c=? WHERE a=? AND b=?", 0, 0, v1);
execute("UPDATE %s SET c=? WHERE a=? AND b=?", 0, 0, v2);
assertRows(execute("SELECT * FROM %s"),
row(0, v1, 0),
row(0, v2, 0),
row(0, v3, 0),
row(0, v4, 0)
);
assertRows(execute("SELECT b FROM %s"),
row(v1),
row(v2),
row(v3),
row(v4)
);
assertRows(execute("SELECT * FROM %s LIMIT 2"),
row(0, v1, 0),
row(0, v2, 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=?", 0, v3),
row(0, v3, 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=?", 0, v1),
row(0, v1, 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b IN ?", 0, list(v3, v1)),
row(0, v1, 0),
row(0, v3, 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b > ?", 0, v3),
row(0, v4, 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b >= ?", 0, v3),
row(0, v3, 0),
row(0, v4, 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b < ?", 0, v3),
row(0, v1, 0),
row(0, v2, 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b <= ?", 0, v3),
row(0, v1, 0),
row(0, v2, 0),
row(0, v3, 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b > ? AND b <= ?", 0, v2, v3),
row(0, v3, 0)
);
execute("DELETE FROM %s WHERE a=? AND b=?", 0, v1);
execute("DELETE FROM %s WHERE a=? AND b=?", 0, v3);
assertRows(execute("SELECT * FROM %s"),
row(0, v2, 0),
row(0, v4, 0)
);
}
@Test
public void testNestedClusteringKeyUsage() throws Throwable
{
createTable("CREATE TABLE %s (a int, b frozen<map<set<int>, list<int>>>, c frozen<set<int>>, d int, PRIMARY KEY (a, b, c)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(), set(), 0);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(set(), list(1, 2, 3)), set(), 0);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0);
assertRows(execute("SELECT * FROM %s"),
row(0, map(), set(), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT b FROM %s"),
row(map()),
row(map(set(), list(1, 2, 3))),
row(map(set(1, 2, 3), list(1, 2, 3))),
row(map(set(4, 5, 6), list(1, 2, 3))),
row(map(set(7, 8, 9), list(1, 2, 3)))
);
assertRows(execute("SELECT c FROM %s"),
row(set()),
row(set()),
row(set(1, 2, 3)),
row(set(1, 2, 3)),
row(set(1, 2, 3))
);
assertRows(execute("SELECT * FROM %s LIMIT 3"),
row(0, map(), set(), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=0 ORDER BY b DESC LIMIT 4"),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=?", 0, map()),
row(0, map(), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=?", 0, map(set(), list(1, 2, 3))),
row(0, map(set(), list(1, 2, 3)), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=?", 0, map(set(1, 2, 3), list(1, 2, 3))),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(), list(1, 2, 3)), set()),
row(0, map(set(), list(1, 2, 3)), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND (b, c) IN ?", 0, list(tuple(map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3)),
tuple(map(), set()))),
row(0, map(), set(), 0),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b > ?", 0, map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b >= ?", 0, map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b < ?", 0, map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(), set(), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b <= ?", 0, map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(), set(), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b > ? AND b <= ?", 0, map(set(1, 2, 3), list(1, 2, 3)), map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0)
);
execute("DELETE FROM %s WHERE a=? AND b=? AND c=?", 0, map(), set());
assertEmpty(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 0, map(), set()));
execute("DELETE FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(), list(1, 2, 3)), set());
assertEmpty(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(), list(1, 2, 3)), set()));
execute("DELETE FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3));
assertEmpty(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3)));
assertRows(execute("SELECT * FROM %s"),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0)
);
}
@Test
public void testNestedClusteringKeyUsageWithReverseOrder() throws Throwable
{
createTable("CREATE TABLE %s (a int, b frozen<map<set<int>, list<int>>>, c frozen<set<int>>, d int, " +
"PRIMARY KEY (a, b, c)) WITH COMPACT STORAGE AND CLUSTERING ORDER BY (b DESC)");
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(), set(), 0);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(set(), list(1, 2, 3)), set(), 0);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0);
assertRows(execute("SELECT * FROM %s"),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0),
row(0, map(), set(), 0)
);
assertRows(execute("SELECT b FROM %s"),
row(map(set(7, 8, 9), list(1, 2, 3))),
row(map(set(4, 5, 6), list(1, 2, 3))),
row(map(set(1, 2, 3), list(1, 2, 3))),
row(map(set(), list(1, 2, 3))),
row(map())
);
assertRows(execute("SELECT c FROM %s"),
row(set(1, 2, 3)),
row(set(1, 2, 3)),
row(set(1, 2, 3)),
row(set()),
row(set())
);
assertRows(execute("SELECT * FROM %s LIMIT 3"),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=0 ORDER BY b DESC LIMIT 4"),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=?", 0, map()),
row(0, map(), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=?", 0, map(set(), list(1, 2, 3))),
row(0, map(set(), list(1, 2, 3)), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=?", 0, map(set(1, 2, 3), list(1, 2, 3))),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(), list(1, 2, 3)), set()),
row(0, map(set(), list(1, 2, 3)), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND (b, c) IN ?", 0, list(tuple(map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3)),
tuple(map(), set()))),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b > ?", 0, map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b >= ?", 0, map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b < ?", 0, map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0),
row(0, map(), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b <= ?", 0, map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(), list(1, 2, 3)), set(), 0),
row(0, map(), set(), 0)
);
assertRows(execute("SELECT * FROM %s WHERE a=? AND b > ? AND b <= ?", 0, map(set(1, 2, 3), list(1, 2, 3)), map(set(4, 5, 6), list(1, 2, 3))),
row(0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3), 0)
);
execute("DELETE FROM %s WHERE a=? AND b=? AND c=?", 0, map(), set());
assertEmpty(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 0, map(), set()));
execute("DELETE FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(), list(1, 2, 3)), set());
assertEmpty(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(), list(1, 2, 3)), set()));
execute("DELETE FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3));
assertEmpty(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 0, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3)));
assertRows(execute("SELECT * FROM %s"),
row(0, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3), 0),
row(0, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3), 0)
);
}
@Test
public void testNormalColumnUsage() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b frozen<map<set<int>, list<int>>>, c frozen<set<int>>)" + compactOption);
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, map(), set());
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 1, map(set(), list(99999, 999999, 99999)), set());
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 2, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3));
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 3, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3));
execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 4, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3));
// overwrite with update
execute("UPDATE %s SET b=? WHERE a=?", map(set(), list(1, 2, 3)), 1);
assertRowsIgnoringOrder(execute("SELECT * FROM %s"),
row(0, map(), set()),
row(1, map(set(), list(1, 2, 3)), set()),
row(2, map(set(1, 2, 3), list(1, 2, 3)), set(1, 2, 3)),
row(3, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3)),
row(4, map(set(7, 8, 9), list(1, 2, 3)), set(1, 2, 3))
);
assertRowsIgnoringOrder(execute("SELECT b FROM %s"),
row(map()),
row(map(set(), list(1, 2, 3))),
row(map(set(1, 2, 3), list(1, 2, 3))),
row(map(set(4, 5, 6), list(1, 2, 3))),
row(map(set(7, 8, 9), list(1, 2, 3)))
);
assertRowsIgnoringOrder(execute("SELECT c FROM %s"),
row(set()),
row(set()),
row(set(1, 2, 3)),
row(set(1, 2, 3)),
row(set(1, 2, 3))
);
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a=?", 3),
row(3, map(set(4, 5, 6), list(1, 2, 3)), set(1, 2, 3))
);
execute("UPDATE %s SET b=? WHERE a=?", null, 1);
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a=?", 1),
row(1, null, set())
);
execute("UPDATE %s SET b=? WHERE a=?", map(), 1);
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a=?", 1),
row(1, map(), set())
);
execute("UPDATE %s SET c=? WHERE a=?", null, 2);
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a=?", 2),
row(2, map(set(1, 2, 3), list(1, 2, 3)), null)
);
execute("UPDATE %s SET c=? WHERE a=?", set(), 2);
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a=?", 2),
row(2, map(set(1, 2, 3), list(1, 2, 3)), set())
);
execute("DELETE b FROM %s WHERE a=?", 3);
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a=?", 3),
row(3, null, set(1, 2, 3))
);
execute("DELETE c FROM %s WHERE a=?", 4);
assertRowsIgnoringOrder(execute("SELECT * FROM %s WHERE a=?", 4),
row(4, map(set(7, 8, 9), list(1, 2, 3)), null)
);
}
/**
* from SecondaryIndexTest
*/
@Test
public void testCompactTableWithValueOver64k() throws Throwable
{
createTable("CREATE TABLE %s(a int, b blob, PRIMARY KEY (a)) WITH COMPACT STORAGE");
createIndex("CREATE INDEX ON %s(b)");
failInsert("INSERT INTO %s (a, b) VALUES (0, ?)", ByteBuffer.allocate(SecondaryIndexTest.TOO_BIG));
failInsert("INSERT INTO %s (a, b) VALUES (0, ?) IF NOT EXISTS", ByteBuffer.allocate(SecondaryIndexTest.TOO_BIG));
failInsert("BEGIN BATCH\n" +
"INSERT INTO %s (a, b) VALUES (0, ?);\n" +
"APPLY BATCH",
ByteBuffer.allocate(SecondaryIndexTest.TOO_BIG));
failInsert("BEGIN BATCH\n" +
"INSERT INTO %s (a, b) VALUES (0, ?) IF NOT EXISTS;\n" +
"APPLY BATCH",
ByteBuffer.allocate(SecondaryIndexTest.TOO_BIG));
}
public void failInsert(String insertCQL, Object... args) throws Throwable
{
try
{
execute(insertCQL, args);
fail("Expected statement to fail validation");
}
catch (Exception e)
{
// as expected
}
}
/**
* Migrated from cql_tests.py:TestCQL.invalid_clustering_indexing_test()
*/
@Test
public void testIndexesOnClusteringInvalid() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY ((a, b))) WITH COMPACT STORAGE");
assertInvalid("CREATE INDEX ON %s (a)");
assertInvalid("CREATE INDEX ON %s (b)");
createTable("CREATE TABLE %s (a int, b int, c int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
assertInvalid("CREATE INDEX ON %s (a)");
assertInvalid("CREATE INDEX ON %s (b)");
assertInvalid("CREATE INDEX ON %s (c)");
}
@Test
public void testEmptyRestrictionValueWithSecondaryIndexAndCompactTables() throws Throwable
{
createTable("CREATE TABLE %s (pk blob, c blob, v blob, PRIMARY KEY ((pk), c)) WITH COMPACT STORAGE");
assertInvalidMessage("Secondary indexes are not supported on PRIMARY KEY columns in COMPACT STORAGE tables",
"CREATE INDEX on %s(c)");
createTable("CREATE TABLE %s (pk blob PRIMARY KEY, v blob) WITH COMPACT STORAGE");
createIndex("CREATE INDEX on %s(v)");
execute("INSERT INTO %s (pk, v) VALUES (?, ?)", bytes("foo123"), bytes("1"));
// Test restrictions on non-primary key value
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('');"));
execute("INSERT INTO %s (pk, v) VALUES (?, ?)", bytes("foo124"), EMPTY_BYTE_BUFFER);
assertRows(execute("SELECT * FROM %s WHERE v = textAsBlob('');"),
row(bytes("foo124"), EMPTY_BYTE_BUFFER));
}
@Test
public void testIndicesOnCompactTable() throws Throwable
{
assertInvalidMessage("COMPACT STORAGE with composite PRIMARY KEY allows no more than one column not part of the PRIMARY KEY (got: v1, v2)",
"CREATE TABLE " + KEYSPACE + ".test (pk int, c int, v1 int, v2 int, PRIMARY KEY(pk, c)) WITH COMPACT STORAGE");
createTable("CREATE TABLE %s (pk int, c int, v int, PRIMARY KEY(pk, c)) WITH COMPACT STORAGE");
assertInvalidMessage("Secondary indexes are not supported on compact value column of COMPACT STORAGE tables",
"CREATE INDEX ON %s(v)");
createTable("CREATE TABLE %s (pk int PRIMARY KEY, v int) WITH COMPACT STORAGE");
createIndex("CREATE INDEX ON %s(v)");
execute("INSERT INTO %s (pk, v) VALUES (?, ?)", 1, 1);
execute("INSERT INTO %s (pk, v) VALUES (?, ?)", 2, 1);
execute("INSERT INTO %s (pk, v) VALUES (?, ?)", 3, 3);
assertRows(execute("SELECT pk, v FROM %s WHERE v = 1"),
row(1, 1),
row(2, 1));
assertRows(execute("SELECT pk, v FROM %s WHERE v = 3"),
row(3, 3));
assertEmpty(execute("SELECT pk, v FROM %s WHERE v = 5"));
createTable("CREATE TABLE %s (pk int PRIMARY KEY, v1 int, v2 int) WITH COMPACT STORAGE");
createIndex("CREATE INDEX ON %s(v1)");
execute("INSERT INTO %s (pk, v1, v2) VALUES (?, ?, ?)", 1, 1, 1);
execute("INSERT INTO %s (pk, v1, v2) VALUES (?, ?, ?)", 2, 1, 2);
execute("INSERT INTO %s (pk, v1, v2) VALUES (?, ?, ?)", 3, 3, 3);
assertRows(execute("SELECT pk, v2 FROM %s WHERE v1 = 1"),
row(1, 1),
row(2, 2));
assertRows(execute("SELECT pk, v2 FROM %s WHERE v1 = 3"),
row(3, 3));
assertEmpty(execute("SELECT pk, v2 FROM %s WHERE v1 = 5"));
}
/**
* OverflowTest
*/
/**
* Test regression from #5189,
* migrated from cql_tests.py:TestCQL.compact_metadata_test()
*/
@Test
public void testCompactMetadata() throws Throwable
{
createTable("CREATE TABLE %s (id int primary key, i int ) WITH COMPACT STORAGE");
execute("INSERT INTO %s (id, i) VALUES (1, 2)");
assertRows(execute("SELECT * FROM %s"),
row(1, 2));
}
@Test
public void testEmpty() throws Throwable
{
// Same test, but for compact
createTable("CREATE TABLE %s (k1 int, k2 int, v int, PRIMARY KEY (k1, k2)) WITH COMPACT STORAGE");
// Inserts a few rows to make sure we don 't actually query something
Object[][] rows = fill();
assertEmpty(execute("SELECT v FROM %s WHERE k1 IN ()"));
assertEmpty(execute("SELECT v FROM %s WHERE k1 = 0 AND k2 IN ()"));
// Test empty IN() in DELETE
execute("DELETE FROM %s WHERE k1 IN ()");
assertArrayEquals(rows, getRows(execute("SELECT * FROM %s")));
// Test empty IN() in UPDATE
execute("UPDATE %s SET v = 3 WHERE k1 IN () AND k2 = 2");
assertArrayEquals(rows, getRows(execute("SELECT * FROM %s")));
}
private Object[][] fill() throws Throwable
{
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);
return getRows(execute("SELECT * FROM %s"));
}
/**
* AggregationTest
*/
@Test
public void testFunctionsWithCompactStorage() throws Throwable
{
createTable("CREATE TABLE %s (a int , b int, c double, primary key(a, b) ) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 1, 11.5)");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, 9.5)");
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, 9.0)");
assertRows(execute("SELECT max(b), min(b), sum(b), avg(b) , max(c), sum(c), avg(c) FROM %s"),
row(3, 1, 6, 2, 11.5, 30.0, 10.0));
assertRows(execute("SELECT COUNT(*) FROM %s"), row(3L));
assertRows(execute("SELECT COUNT(1) FROM %s"), row(3L));
assertRows(execute("SELECT COUNT(*) FROM %s WHERE a = 1 AND b > 1"), row(2L));
assertRows(execute("SELECT COUNT(1) FROM %s WHERE a = 1 AND b > 1"), row(2L));
assertRows(execute("SELECT max(b), min(b), sum(b), avg(b) , max(c), sum(c), avg(c) FROM %s WHERE a = 1 AND b > 1"),
row(3, 2, 5, 2, 9.5, 18.5, 9.25));
}
/**
* BatchTest
*/
@Test
public void testBatchRangeDelete() throws Throwable
{
createTable("CREATE TABLE %s (partitionKey int," +
"clustering int," +
"value int," +
" PRIMARY KEY (partitionKey, clustering)) WITH COMPACT STORAGE");
int value = 0;
for (int partitionKey = 0; partitionKey < 4; partitionKey++)
for (int clustering1 = 0; clustering1 < 5; clustering1++)
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (?, ?, ?)",
partitionKey, clustering1, value++);
execute("BEGIN BATCH " +
"DELETE FROM %1$s WHERE partitionKey = 1;" +
"DELETE FROM %1$s WHERE partitionKey = 0 AND clustering >= 4;" +
"DELETE FROM %1$s WHERE partitionKey = 0 AND clustering <= 0;" +
"DELETE FROM %1$s WHERE partitionKey = 2 AND clustering >= 0 AND clustering <= 3;" +
"DELETE FROM %1$s WHERE partitionKey = 2 AND clustering <= 3 AND clustering >= 4;" +
"DELETE FROM %1$s WHERE partitionKey = 3 AND (clustering) >= (3) AND (clustering) <= (6);" +
"APPLY BATCH;");
assertRows(execute("SELECT * FROM %s"),
row(0, 1, 1),
row(0, 2, 2),
row(0, 3, 3),
row(2, 4, 14),
row(3, 0, 15),
row(3, 1, 16),
row(3, 2, 17));
}
/**
* CreateTest
*/
/**
* /**
* Creation and basic operations on a static table with compact storage,
* migrated from cql_tests.py:TestCQL.noncomposite_static_cf_test()
*/
@Test
public void testDenseStaticTable() throws Throwable
{
createTable("CREATE TABLE %s (userid uuid PRIMARY KEY, firstname text, lastname text, age int) WITH COMPACT STORAGE");
UUID id1 = UUID.fromString("550e8400-e29b-41d4-a716-446655440000");
UUID id2 = UUID.fromString("f47ac10b-58cc-4372-a567-0e02b2c3d479");
execute("INSERT INTO %s (userid, firstname, lastname, age) VALUES (?, ?, ?, ?)", id1, "Frodo", "Baggins", 32);
execute("UPDATE %s SET firstname = ?, lastname = ?, age = ? WHERE userid = ?", "Samwise", "Gamgee", 33, id2);
assertRows(execute("SELECT firstname, lastname FROM %s WHERE userid = ?", id1),
row("Frodo", "Baggins"));
assertRows(execute("SELECT * FROM %s WHERE userid = ?", id1),
row(id1, 32, "Frodo", "Baggins"));
assertRows(execute("SELECT * FROM %s"),
row(id2, 33, "Samwise", "Gamgee"),
row(id1, 32, "Frodo", "Baggins")
);
String batch = "BEGIN BATCH "
+ "INSERT INTO %1$s (userid, age) VALUES (?, ?) "
+ "UPDATE %1$s SET age = ? WHERE userid = ? "
+ "DELETE firstname, lastname FROM %1$s WHERE userid = ? "
+ "DELETE firstname, lastname FROM %1$s WHERE userid = ? "
+ "APPLY BATCH";
execute(batch, id1, 36, 37, id2, id1, id2);
assertRows(execute("SELECT * FROM %s"),
row(id2, 37, null, null),
row(id1, 36, null, null));
}
/**
* Creation and basic operations on a non-composite table with compact storage,
* migrated from cql_tests.py:TestCQL.dynamic_cf_test()
*/
@Test
public void testDenseNonCompositeTable() throws Throwable
{
createTable("CREATE TABLE %s (userid uuid, url text, time bigint, PRIMARY KEY (userid, url)) WITH COMPACT STORAGE");
UUID id1 = UUID.fromString("550e8400-e29b-41d4-a716-446655440000");
UUID id2 = UUID.fromString("f47ac10b-58cc-4372-a567-0e02b2c3d479");
UUID id3 = UUID.fromString("810e8500-e29b-41d4-a716-446655440000");
execute("INSERT INTO %s (userid, url, time) VALUES (?, ?, ?)", id1, "http://foo.bar", 42L);
execute("INSERT INTO %s (userid, url, time) VALUES (?, ?, ?)", id1, "http://foo-2.bar", 24L);
execute("INSERT INTO %s (userid, url, time) VALUES (?, ?, ?)", id1, "http://bar.bar", 128L);
execute("UPDATE %s SET time = 24 WHERE userid = ? and url = 'http://bar.foo'", id2);
execute("UPDATE %s SET time = 12 WHERE userid IN (?, ?) and url = 'http://foo-3'", id2, id1);
assertRows(execute("SELECT url, time FROM %s WHERE userid = ?", id1),
row("http://bar.bar", 128L),
row("http://foo-2.bar", 24L),
row("http://foo-3", 12L),
row("http://foo.bar", 42L));
assertRows(execute("SELECT * FROM %s WHERE userid = ?", id2),
row(id2, "http://bar.foo", 24L),
row(id2, "http://foo-3", 12L));
assertRows(execute("SELECT time FROM %s"),
row(24L), // id2
row(12L),
row(128L), // id1
row(24L),
row(12L),
row(42L)
);
// Check we don't allow empty values for url since this is the full underlying cell name (#6152)
assertInvalid("INSERT INTO %s (userid, url, time) VALUES (?, '', 42)", id3);
}
/**
* Creation and basic operations on a composite table with compact storage,
* migrated from cql_tests.py:TestCQL.dense_cf_test()
*/
@Test
public void testDenseCompositeTable() throws Throwable
{
createTable("CREATE TABLE %s (userid uuid, ip text, port int, time bigint, PRIMARY KEY (userid, ip, port)) WITH COMPACT STORAGE");
UUID id1 = UUID.fromString("550e8400-e29b-41d4-a716-446655440000");
UUID id2 = UUID.fromString("f47ac10b-58cc-4372-a567-0e02b2c3d479");
execute("INSERT INTO %s (userid, ip, port, time) VALUES (?, '192.168.0.1', 80, 42)", id1);
execute("INSERT INTO %s (userid, ip, port, time) VALUES (?, '192.168.0.2', 80, 24)", id1);
execute("INSERT INTO %s (userid, ip, port, time) VALUES (?, '192.168.0.2', 90, 42)", id1);
execute("UPDATE %s SET time = 24 WHERE userid = ? AND ip = '192.168.0.2' AND port = 80", id2);
// we don't have to include all of the clustering columns (see CASSANDRA-7990)
execute("INSERT INTO %s (userid, ip, time) VALUES (?, '192.168.0.3', 42)", id2);
execute("UPDATE %s SET time = 42 WHERE userid = ? AND ip = '192.168.0.4'", id2);
assertRows(execute("SELECT ip, port, time FROM %s WHERE userid = ?", id1),
row("192.168.0.1", 80, 42L),
row("192.168.0.2", 80, 24L),
row("192.168.0.2", 90, 42L));
assertRows(execute("SELECT ip, port, time FROM %s WHERE userid = ? and ip >= '192.168.0.2'", id1),
row("192.168.0.2", 80, 24L),
row("192.168.0.2", 90, 42L));
assertRows(execute("SELECT ip, port, time FROM %s WHERE userid = ? and ip = '192.168.0.2'", id1),
row("192.168.0.2", 80, 24L),
row("192.168.0.2", 90, 42L));
assertEmpty(execute("SELECT ip, port, time FROM %s WHERE userid = ? and ip > '192.168.0.2'", id1));
assertRows(execute("SELECT ip, port, time FROM %s WHERE userid = ? AND ip = '192.168.0.3'", id2),
row("192.168.0.3", null, 42L));
assertRows(execute("SELECT ip, port, time FROM %s WHERE userid = ? AND ip = '192.168.0.4'", id2),
row("192.168.0.4", null, 42L));
execute("DELETE time FROM %s WHERE userid = ? AND ip = '192.168.0.2' AND port = 80", id1);
assertRowCount(execute("SELECT * FROM %s WHERE userid = ?", id1), 2);
execute("DELETE FROM %s WHERE userid = ?", id1);
assertEmpty(execute("SELECT * FROM %s WHERE userid = ?", id1));
execute("DELETE FROM %s WHERE userid = ? AND ip = '192.168.0.3'", id2);
assertEmpty(execute("SELECT * FROM %s WHERE userid = ? AND ip = '192.168.0.3'", id2));
}
@Test
public void testCreateIndexOnCompactTableWithClusteringColumns() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int , c int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE;");
assertInvalidMessage("Secondary indexes are not supported on PRIMARY KEY columns in COMPACT STORAGE tables",
"CREATE INDEX ON %s (a);");
assertInvalidMessage("Secondary indexes are not supported on PRIMARY KEY columns in COMPACT STORAGE tables",
"CREATE INDEX ON %s (b);");
assertInvalidMessage("Secondary indexes are not supported on compact value column of COMPACT STORAGE tables",
"CREATE INDEX ON %s (c);");
}
@Test
public void testCreateIndexOnCompactTableWithoutClusteringColumns() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int) WITH COMPACT STORAGE;");
assertInvalidMessage("Secondary indexes are not supported on PRIMARY KEY columns in COMPACT STORAGE tables",
"CREATE INDEX ON %s (a);");
createIndex("CREATE INDEX ON %s (b);");
execute("INSERT INTO %s (a, b) values (1, 1)");
execute("INSERT INTO %s (a, b) values (2, 4)");
execute("INSERT INTO %s (a, b) values (3, 6)");
assertRows(execute("SELECT * FROM %s WHERE b = ?", 4), row(2, 4));
}
/**
* DeleteTest
*/
@Test
public void testDeleteWithNoClusteringColumns() throws Throwable
{
testDeleteWithNoClusteringColumns(false);
testDeleteWithNoClusteringColumns(true);
}
private void testDeleteWithNoClusteringColumns(boolean forceFlush) throws Throwable
{
createTable("CREATE TABLE %s (partitionKey int PRIMARY KEY," +
"value int)" + compactOption);
execute("INSERT INTO %s (partitionKey, value) VALUES (0, 0)");
execute("INSERT INTO %s (partitionKey, value) VALUES (1, 1)");
execute("INSERT INTO %s (partitionKey, value) VALUES (2, 2)");
execute("INSERT INTO %s (partitionKey, value) VALUES (3, 3)");
flush(forceFlush);
execute("DELETE value FROM %s WHERE partitionKey = ?", 0);
flush(forceFlush);
assertEmpty(execute("SELECT * FROM %s WHERE partitionKey = ?", 0));
execute("DELETE FROM %s WHERE partitionKey IN (?, ?)", 0, 1);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s"),
row(2, 2),
row(3, 3));
// test invalid queries
// token function
assertInvalidMessage("The token function cannot be used in WHERE clauses for DELETE statements",
"DELETE FROM %s WHERE token(partitionKey) = token(?)", 0);
// multiple time same primary key element in WHERE clause
assertInvalidMessage("partitionkey cannot be restricted by more than one relation if it includes an Equal",
"DELETE FROM %s WHERE partitionKey = ? AND partitionKey = ?", 0, 1);
// Undefined column names
assertInvalidMessage("Undefined column name unknown",
"DELETE unknown FROM %s WHERE partitionKey = ?", 0);
assertInvalidMessage("Undefined column name partitionkey1",
"DELETE FROM %s WHERE partitionKey1 = ?", 0);
// Invalid operator in the where clause
assertInvalidMessage("Only EQ and IN relation are supported on the partition key (unless you use the token() function)",
"DELETE FROM %s WHERE partitionKey > ? ", 0);
assertInvalidMessage("Cannot use DELETE with CONTAINS",
"DELETE FROM %s WHERE partitionKey CONTAINS ?", 0);
// Non primary key in the where clause
assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value",
"DELETE FROM %s WHERE partitionKey = ? AND value = ?", 0, 1);
}
@Test
public void testDeleteWithOneClusteringColumns() throws Throwable
{
testDeleteWithOneClusteringColumns(false);
testDeleteWithOneClusteringColumns(true);
}
private void testDeleteWithOneClusteringColumns(boolean forceFlush) throws Throwable
{
String compactOption = " WITH COMPACT STORAGE";
createTable("CREATE TABLE %s (partitionKey int," +
"clustering int," +
"value int," +
" PRIMARY KEY (partitionKey, clustering))" + compactOption);
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 0, 0)");
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 1, 1)");
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 2, 2)");
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 3, 3)");
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 4, 4)");
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 5, 5)");
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (1, 0, 6)");
flush(forceFlush);
execute("DELETE value FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1);
flush(forceFlush);
assertEmpty(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1));
execute("DELETE FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1);
flush(forceFlush);
assertEmpty(execute("SELECT value FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1));
execute("DELETE FROM %s WHERE partitionKey IN (?, ?) AND clustering = ?", 0, 1, 0);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1),
row(0, 2, 2),
row(0, 3, 3),
row(0, 4, 4),
row(0, 5, 5));
execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering) IN ((?), (?))", 0, 4, 5);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1),
row(0, 2, 2),
row(0, 3, 3));
// test invalid queries
// missing primary key element
assertInvalidMessage("Some partition key parts are missing: partitionkey",
"DELETE FROM %s WHERE clustering = ?", 1);
// token function
assertInvalidMessage("The token function cannot be used in WHERE clauses for DELETE statements",
"DELETE FROM %s WHERE token(partitionKey) = token(?) AND clustering = ? ", 0, 1);
// multiple time same primary key element in WHERE clause
assertInvalidMessage("clustering cannot be restricted by more than one relation if it includes an Equal",
"DELETE FROM %s WHERE partitionKey = ? AND clustering = ? AND clustering = ?", 0, 1, 1);
// Undefined column names
assertInvalidMessage("Undefined column name value1",
"DELETE value1 FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1);
assertInvalidMessage("Undefined column name partitionkey1",
"DELETE FROM %s WHERE partitionKey1 = ? AND clustering = ?", 0, 1);
assertInvalidMessage("Undefined column name clustering_3",
"DELETE FROM %s WHERE partitionKey = ? AND clustering_3 = ?", 0, 1);
// Invalid operator in the where clause
assertInvalidMessage("Only EQ and IN relation are supported on the partition key (unless you use the token() function)",
"DELETE FROM %s WHERE partitionKey > ? AND clustering = ?", 0, 1);
assertInvalidMessage("Cannot use DELETE with CONTAINS",
"DELETE FROM %s WHERE partitionKey CONTAINS ? AND clustering = ?", 0, 1);
// Non primary key in the where clause
assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value",
"DELETE FROM %s WHERE partitionKey = ? AND clustering = ? AND value = ?", 0, 1, 3);
}
@Test
public void testDeleteWithTwoClusteringColumns() throws Throwable
{
testDeleteWithTwoClusteringColumns(false);
testDeleteWithTwoClusteringColumns(true);
}
private void testDeleteWithTwoClusteringColumns(boolean forceFlush) throws Throwable
{
createTable("CREATE TABLE %s (partitionKey int," +
"clustering_1 int," +
"clustering_2 int," +
"value int," +
" PRIMARY KEY (partitionKey, clustering_1, clustering_2))" + compactOption);
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 0, 0)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 1, 1)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 2, 2)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 3, 3)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 1, 1, 4)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 1, 2, 5)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (1, 0, 0, 6)");
flush(forceFlush);
execute("DELETE value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1);
flush(forceFlush);
assertEmpty(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?",
0, 1, 1));
execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) = (?, ?)", 0, 1, 1);
flush(forceFlush);
assertEmpty(execute("SELECT value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?",
0, 1, 1));
execute("DELETE FROM %s WHERE partitionKey IN (?, ?) AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 0, 0);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1),
row(0, 0, 1, 1),
row(0, 0, 2, 2),
row(0, 0, 3, 3),
row(0, 1, 2, 5));
Object[][] rows = new Object[][]{ row(0, 0, 1, 1), row(0, 1, 2, 5) };
execute("DELETE value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 IN (?, ?)", 0, 0, 2, 3);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1), rows);
rows = new Object[][]{ row(0, 0, 1, 1) };
execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) IN ((?, ?), (?, ?))", 0, 0, 2, 1, 2);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1), rows);
execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1) IN ((?), (?)) AND clustering_2 = ?", 0, 0, 2, 3);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1),
row(0, 0, 1, 1));
// test invalid queries
// missing primary key element
assertInvalidMessage("Some partition key parts are missing: partitionkey",
"DELETE FROM %s WHERE clustering_1 = ? AND clustering_2 = ?", 1, 1);
assertInvalidMessage("PRIMARY KEY column \"clustering_2\" cannot be restricted as preceding column \"clustering_1\" is not restricted",
"DELETE FROM %s WHERE partitionKey = ? AND clustering_2 = ?", 0, 1);
// token function
assertInvalidMessage("The token function cannot be used in WHERE clauses for DELETE statements",
"DELETE FROM %s WHERE token(partitionKey) = token(?) AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1);
// multiple time same primary key element in WHERE clause
assertInvalidMessage("clustering_1 cannot be restricted by more than one relation if it includes an Equal",
"DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ? AND clustering_1 = ?", 0, 1, 1, 1);
// Undefined column names
assertInvalidMessage("Undefined column name value1",
"DELETE value1 FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1);
assertInvalidMessage("Undefined column name partitionkey1",
"DELETE FROM %s WHERE partitionKey1 = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1);
assertInvalidMessage("Undefined column name clustering_3",
"DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_3 = ?", 0, 1, 1);
// Invalid operator in the where clause
assertInvalidMessage("Only EQ and IN relation are supported on the partition key (unless you use the token() function)",
"DELETE FROM %s WHERE partitionKey > ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1);
assertInvalidMessage("Cannot use DELETE with CONTAINS",
"DELETE FROM %s WHERE partitionKey CONTAINS ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1);
// Non primary key in the where clause
assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value",
"DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ? AND value = ?", 0, 1, 1, 3);
}
/**
* InsertTest
*/
@Test
public void testInsertWithCompactFormat() throws Throwable
{
testInsertWithCompactFormat(false);
testInsertWithCompactFormat(true);
}
private void testInsertWithCompactFormat(boolean forceFlush) throws Throwable
{
createTable("CREATE TABLE %s (partitionKey int," +
"clustering int," +
"value int," +
" PRIMARY KEY (partitionKey, clustering)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 0, 0)");
execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 1, 1)");
flush(forceFlush);
assertRows(execute("SELECT * FROM %s"),
row(0, 0, 0),
row(0, 1, 1));
// Invalid Null values for the clustering key or the regular column
assertInvalidMessage("Some clustering keys are missing: clustering",
"INSERT INTO %s (partitionKey, value) VALUES (0, 0)");
assertInvalidMessage("Column value is mandatory for this COMPACT STORAGE table",
"INSERT INTO %s (partitionKey, clustering) VALUES (0, 0)");
// Missing primary key columns
assertInvalidMessage("Some partition key parts are missing: partitionkey",
"INSERT INTO %s (clustering, value) VALUES (0, 1)");
// multiple time the same value
assertInvalidMessage("The column names contains duplicates",
"INSERT INTO %s (partitionKey, clustering, value, value) VALUES (0, 0, 2, 2)");
// multiple time same primary key element in WHERE clause
assertInvalidMessage("The column names contains duplicates",
"INSERT INTO %s (partitionKey, clustering, clustering, value) VALUES (0, 0, 0, 2)");
// Undefined column names
assertInvalidMessage("Undefined column name clusteringx",
"INSERT INTO %s (partitionKey, clusteringx, value) VALUES (0, 0, 2)");
assertInvalidMessage("Undefined column name valuex",
"INSERT INTO %s (partitionKey, clustering, valuex) VALUES (0, 0, 2)");
}
@Test
public void testInsertWithCompactStorageAndTwoClusteringColumns() throws Throwable
{
testInsertWithCompactStorageAndTwoClusteringColumns(false);
testInsertWithCompactStorageAndTwoClusteringColumns(true);
}
private void testInsertWithCompactStorageAndTwoClusteringColumns(boolean forceFlush) throws Throwable
{
createTable("CREATE TABLE %s (partitionKey int," +
"clustering_1 int," +
"clustering_2 int," +
"value int," +
" PRIMARY KEY (partitionKey, clustering_1, clustering_2)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (partitionKey, clustering_1, value) VALUES (0, 0, 0)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 0, 0)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 1, 1)");
flush(forceFlush);
assertRows(execute("SELECT * FROM %s"),
row(0, 0, null, 0),
row(0, 0, 0, 0),
row(0, 0, 1, 1));
// Invalid Null values for the clustering key or the regular column
assertInvalidMessage("PRIMARY KEY column \"clustering_2\" cannot be restricted as preceding column \"clustering_1\" is not restricted",
"INSERT INTO %s (partitionKey, clustering_2, value) VALUES (0, 0, 0)");
assertInvalidMessage("Column value is mandatory for this COMPACT STORAGE table",
"INSERT INTO %s (partitionKey, clustering_1, clustering_2) VALUES (0, 0, 0)");
// Missing primary key columns
assertInvalidMessage("Some partition key parts are missing: partitionkey",
"INSERT INTO %s (clustering_1, clustering_2, value) VALUES (0, 0, 1)");
assertInvalidMessage("PRIMARY KEY column \"clustering_2\" cannot be restricted as preceding column \"clustering_1\" is not restricted",
"INSERT INTO %s (partitionKey, clustering_2, value) VALUES (0, 0, 2)");
// multiple time the same value
assertInvalidMessage("The column names contains duplicates",
"INSERT INTO %s (partitionKey, clustering_1, value, clustering_2, value) VALUES (0, 0, 2, 0, 2)");
// multiple time same primary key element in WHERE clause
assertInvalidMessage("The column names contains duplicates",
"INSERT INTO %s (partitionKey, clustering_1, clustering_1, clustering_2, value) VALUES (0, 0, 0, 0, 2)");
// Undefined column names
assertInvalidMessage("Undefined column name clustering_1x",
"INSERT INTO %s (partitionKey, clustering_1x, clustering_2, value) VALUES (0, 0, 0, 2)");
assertInvalidMessage("Undefined column name valuex",
"INSERT INTO %s (partitionKey, clustering_1, clustering_2, valuex) VALUES (0, 0, 0, 2)");
}
/**
* InsertUpdateIfConditionTest
*/
/**
* Test for CAS with compact storage table, and #6813 in particular,
* migrated from cql_tests.py:TestCQL.cas_and_compact_test()
*/
@Test
public void testCompactStorage() throws Throwable
{
createTable("CREATE TABLE %s (partition text, key text, owner text, PRIMARY KEY (partition, key) ) WITH COMPACT STORAGE");
execute("INSERT INTO %s (partition, key, owner) VALUES ('a', 'b', null)");
assertRows(execute("UPDATE %s SET owner='z' WHERE partition='a' AND key='b' IF owner=null"), row(true));
assertRows(execute("UPDATE %s SET owner='b' WHERE partition='a' AND key='b' IF owner='a'"), row(false, "z"));
assertRows(execute("UPDATE %s SET owner='b' WHERE partition='a' AND key='b' IF owner='z'"), row(true));
assertRows(execute("INSERT INTO %s (partition, key, owner) VALUES ('a', 'c', 'x') IF NOT EXISTS"), row(true));
}
/**
* SelectGroupByTest
*/
@Test
public void testGroupByWithoutPaging() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key (a, b, c, d))"
+ compactOption);
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 3, 6)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 6, 12)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 3, 2, 12, 24)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 4, 2, 12, 24)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 4, 2, 6, 12)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (2, 2, 3, 3, 6)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (2, 4, 3, 6, 12)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (3, 3, 2, 12, 24)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (4, 8, 2, 12, 24)");
// Makes sure that we have some tombstones
execute("DELETE FROM %s WHERE a = 1 AND b = 3 AND c = 2 AND d = 12");
execute("DELETE FROM %s WHERE a = 3");
// Range queries
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a"),
row(1, 2, 6, 4L, 24),
row(2, 2, 6, 2L, 12),
row(4, 8, 24, 1L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b"),
row(1, 2, 6, 2L, 12),
row(1, 4, 12, 2L, 24),
row(2, 2, 6, 1L, 6),
row(2, 4, 12, 1L, 12),
row(4, 8, 24, 1L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE b = 2 GROUP BY a, b ALLOW FILTERING"),
row(1, 2, 6, 2L, 12),
row(2, 2, 6, 1L, 6));
assertEmpty(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE b IN () GROUP BY a, b ALLOW FILTERING"));
// Range queries without aggregates
assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b, c"),
row(1, 2, 1, 3),
row(1, 2, 2, 6),
row(1, 4, 2, 6),
row(2, 2, 3, 3),
row(2, 4, 3, 6),
row(4, 8, 2, 12));
assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b"),
row(1, 2, 1, 3),
row(1, 4, 2, 6),
row(2, 2, 3, 3),
row(2, 4, 3, 6),
row(4, 8, 2, 12));
// Range queries with wildcard
assertRows(execute("SELECT * FROM %s GROUP BY a, b, c"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12),
row(1, 4, 2, 6, 12),
row(2, 2, 3, 3, 6),
row(2, 4, 3, 6, 12),
row(4, 8, 2, 12, 24));
assertRows(execute("SELECT * FROM %s GROUP BY a, b"),
row(1, 2, 1, 3, 6),
row(1, 4, 2, 6, 12),
row(2, 2, 3, 3, 6),
row(2, 4, 3, 6, 12),
row(4, 8, 2, 12, 24));
// Range query with LIMIT
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b LIMIT 2"),
row(1, 2, 6, 2L, 12),
row(1, 4, 12, 2L, 24));
// Range queries with PER PARTITION LIMIT
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b PER PARTITION LIMIT 1"),
row(1, 2, 6, 2L, 12),
row(2, 2, 6, 1L, 6),
row(4, 8, 24, 1L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a PER PARTITION LIMIT 2"),
row(1, 2, 6, 4L, 24),
row(2, 2, 6, 2L, 12),
row(4, 8, 24, 1L, 24));
// Range query with PER PARTITION LIMIT and LIMIT
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2"),
row(1, 2, 6, 2L, 12),
row(2, 2, 6, 1L, 6));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a PER PARTITION LIMIT 2"),
row(1, 2, 6, 4L, 24),
row(2, 2, 6, 2L, 12),
row(4, 8, 24, 1L, 24));
// Range queries without aggregates and with LIMIT
assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b, c LIMIT 3"),
row(1, 2, 1, 3),
row(1, 2, 2, 6),
row(1, 4, 2, 6));
assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b LIMIT 3"),
row(1, 2, 1, 3),
row(1, 4, 2, 6),
row(2, 2, 3, 3));
// Range queries with wildcard and with LIMIT
assertRows(execute("SELECT * FROM %s GROUP BY a, b, c LIMIT 3"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12),
row(1, 4, 2, 6, 12));
assertRows(execute("SELECT * FROM %s GROUP BY a, b LIMIT 3"),
row(1, 2, 1, 3, 6),
row(1, 4, 2, 6, 12),
row(2, 2, 3, 3, 6));
// Range queries without aggregates and with PER PARTITION LIMIT
assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2"),
row(1, 2, 1, 3),
row(1, 2, 2, 6),
row(2, 2, 3, 3),
row(2, 4, 3, 6),
row(4, 8, 2, 12));
assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b PER PARTITION LIMIT 1"),
row(1, 2, 1, 3),
row(2, 2, 3, 3),
row(4, 8, 2, 12));
// Range queries with wildcard and with PER PARTITION LIMIT
assertRows(execute("SELECT * FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12),
row(2, 2, 3, 3, 6),
row(2, 4, 3, 6, 12),
row(4, 8, 2, 12, 24));
assertRows(execute("SELECT * FROM %s GROUP BY a, b PER PARTITION LIMIT 1"),
row(1, 2, 1, 3, 6),
row(2, 2, 3, 3, 6),
row(4, 8, 2, 12, 24));
// Range queries without aggregates, with PER PARTITION LIMIT and LIMIT
assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2 LIMIT 3"),
row(1, 2, 1, 3),
row(1, 2, 2, 6),
row(2, 2, 3, 3));
// Range queries with wildcard, with PER PARTITION LIMIT and LIMIT
assertRows(execute("SELECT * FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2 LIMIT 3"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12),
row(2, 2, 3, 3, 6));
// Range query with DISTINCT
assertRows(execute("SELECT DISTINCT a, count(a)FROM %s GROUP BY a"),
row(1, 1L),
row(2, 1L),
row(4, 1L));
assertInvalidMessage("Grouping on clustering columns is not allowed for SELECT DISTINCT queries",
"SELECT DISTINCT a, count(a)FROM %s GROUP BY a, b");
// Range query with DISTINCT and LIMIT
assertRows(execute("SELECT DISTINCT a, count(a)FROM %s GROUP BY a LIMIT 2"),
row(1, 1L),
row(2, 1L));
assertInvalidMessage("Grouping on clustering columns is not allowed for SELECT DISTINCT queries",
"SELECT DISTINCT a, count(a)FROM %s GROUP BY a, b LIMIT 2");
// Range query with ORDER BY
assertInvalidMessage("ORDER BY is only supported when the partition key is restricted by an EQ or an IN",
"SELECT a, b, c, count(b), max(e) FROM %s GROUP BY a, b ORDER BY b DESC, c DESC");
// Single partition queries
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12),
row(1, 4, 12, 2L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY b, c"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12),
row(1, 4, 12, 2L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 AND b = 2 GROUP BY a, b, c"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 AND b = 2 GROUP BY a, c"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 AND b = 2 GROUP BY c"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12));
// Single partition queries without aggregates
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b"),
row(1, 2, 1, 3),
row(1, 4, 2, 6));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b, c"),
row(1, 2, 1, 3),
row(1, 2, 2, 6),
row(1, 4, 2, 6));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY b, c"),
row(1, 2, 1, 3),
row(1, 2, 2, 6),
row(1, 4, 2, 6));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 and token(a) = token(1) GROUP BY b, c"),
row(1, 2, 1, 3),
row(1, 2, 2, 6),
row(1, 4, 2, 6));
// Single partition queries with wildcard
assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12),
row(1, 4, 2, 6, 12));
assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b"),
row(1, 2, 1, 3, 6),
row(1, 4, 2, 6, 12));
// Single partition queries with DISTINCT
assertRows(execute("SELECT DISTINCT a, count(a)FROM %s WHERE a = 1 GROUP BY a"),
row(1, 1L));
assertInvalidMessage("Grouping on clustering columns is not allowed for SELECT DISTINCT queries",
"SELECT DISTINCT a, count(a)FROM %s WHERE a = 1 GROUP BY a, b");
// Single partition queries with LIMIT
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 10"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12),
row(1, 4, 12, 2L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12));
assertRows(execute("SELECT count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 1"),
row(1L, 6));
// Single partition queries with PER PARTITION LIMIT
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 10"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12),
row(1, 4, 12, 2L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12));
assertRows(execute("SELECT count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 1"),
row(1L, 6));
// Single partition queries without aggregates and with LIMIT
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b LIMIT 2"),
row(1, 2, 1, 3),
row(1, 4, 2, 6));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b LIMIT 1"),
row(1, 2, 1, 3));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2"),
row(1, 2, 1, 3),
row(1, 2, 2, 6));
// Single partition queries with wildcard and with LIMIT
assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12));
assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b LIMIT 1"),
row(1, 2, 1, 3, 6));
// Single partition queries without aggregates and with PER PARTITION LIMIT
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 2"),
row(1, 2, 1, 3),
row(1, 4, 2, 6));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 1"),
row(1, 2, 1, 3));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2"),
row(1, 2, 1, 3),
row(1, 2, 2, 6));
// Single partition queries with wildcard and with PER PARTITION LIMIT
assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12));
assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 1"),
row(1, 2, 1, 3, 6));
// Single partition queries with ORDER BY
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC"),
row(1, 4, 24, 2L, 24),
row(1, 2, 12, 1L, 12),
row(1, 2, 6, 1L, 6));
// Single partition queries with ORDER BY and PER PARTITION LIMIT
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC PER PARTITION LIMIT 1"),
row(1, 4, 24, 2L, 24));
// Single partition queries with ORDER BY and LIMIT
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC LIMIT 2"),
row(1, 4, 24, 2L, 24),
row(1, 2, 12, 1L, 12));
// Multi-partitions queries
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12),
row(1, 4, 12, 2L, 24),
row(2, 2, 6, 1L, 6),
row(2, 4, 12, 1L, 12),
row(4, 8, 24, 1L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) AND b = 2 GROUP BY a, b, c"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12),
row(2, 2, 6, 1L, 6));
// Multi-partitions queries without aggregates
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b"),
row(1, 2, 1, 3),
row(1, 4, 2, 6),
row(2, 2, 3, 3),
row(2, 4, 3, 6),
row(4, 8, 2, 12));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c"),
row(1, 2, 1, 3),
row(1, 2, 2, 6),
row(1, 4, 2, 6),
row(2, 2, 3, 3),
row(2, 4, 3, 6),
row(4, 8, 2, 12));
// Multi-partitions with wildcard
assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12),
row(1, 4, 2, 6, 12),
row(2, 2, 3, 3, 6),
row(2, 4, 3, 6, 12),
row(4, 8, 2, 12, 24));
assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b"),
row(1, 2, 1, 3, 6),
row(1, 4, 2, 6, 12),
row(2, 2, 3, 3, 6),
row(2, 4, 3, 6, 12),
row(4, 8, 2, 12, 24));
// Multi-partitions query with DISTINCT
assertRows(execute("SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) GROUP BY a"),
row(1, 1L),
row(2, 1L),
row(4, 1L));
assertInvalidMessage("Grouping on clustering columns is not allowed for SELECT DISTINCT queries",
"SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b");
// Multi-partitions query with DISTINCT and LIMIT
assertRows(execute("SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) GROUP BY a LIMIT 2"),
row(1, 1L),
row(2, 1L));
// Multi-partitions queries with PER PARTITION LIMIT
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c PER PARTITION LIMIT 1"),
row(1, 2, 6, 1L, 6),
row(2, 2, 6, 1L, 6),
row(4, 8, 24, 1L, 24));
assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c PER PARTITION LIMIT 2"),
row(1, 2, 6, 1L, 6),
row(1, 2, 12, 1L, 12),
row(2, 2, 6, 1L, 6),
row(2, 4, 12, 1L, 12),
row(4, 8, 24, 1L, 24));
// Multi-partitions with wildcard and PER PARTITION LIMIT
assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c PER PARTITION LIMIT 2"),
row(1, 2, 1, 3, 6),
row(1, 2, 2, 6, 12),
row(2, 2, 3, 3, 6),
row(2, 4, 3, 6, 12),
row(4, 8, 2, 12, 24));
assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b PER PARTITION LIMIT 1"),
row(1, 2, 1, 3, 6),
row(2, 2, 3, 3, 6),
row(4, 8, 2, 12, 24));
// Multi-partitions queries with ORDER BY
assertRows(execute("SELECT a, b, c, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b ORDER BY b DESC, c DESC"),
row(4, 8, 2, 1L, 24),
row(2, 4, 3, 1L, 12),
row(1, 4, 2, 2L, 24),
row(2, 2, 3, 1L, 6),
row(1, 2, 2, 2L, 12));
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c ORDER BY b DESC, c DESC"),
row(4, 8, 2, 12),
row(2, 4, 3, 6),
row(1, 4, 2, 12),
row(2, 2, 3, 3),
row(1, 2, 2, 6),
row(1, 2, 1, 3));
// Multi-partitions queries with ORDER BY and LIMIT
assertRows(execute("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b ORDER BY b DESC, c DESC LIMIT 3"),
row(4, 8, 2, 12),
row(2, 4, 3, 6),
row(1, 4, 2, 12));
// Multi-partitions with wildcard, ORDER BY and LIMIT
assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c ORDER BY b DESC, c DESC LIMIT 3"),
row(4, 8, 2, 12, 24),
row(2, 4, 3, 6, 12),
row(1, 4, 2, 12, 24));
// Invalid queries
assertInvalidMessage("Group by is currently only supported on the columns of the PRIMARY KEY, got e",
"SELECT a, b, d, count(b), max(c) FROM %s WHERE a = 1 GROUP BY a, e");
assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY",
"SELECT a, b, d, count(b), max(c) FROM %s WHERE a = 1 GROUP BY c");
assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY",
"SELECT a, b, d, count(b), max(c) FROM %s WHERE a = 1 GROUP BY a, c, b");
assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY",
"SELECT a, b, d, count(b), max(c) FROM %s WHERE a = 1 GROUP BY a, a");
assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY",
"SELECT a, b, c, d FROM %s WHERE token(a) = token(1) GROUP BY b, c");
assertInvalidMessage("Undefined column name clustering1",
"SELECT a, b as clustering1, max(c) FROM %s WHERE a = 1 GROUP BY a, clustering1");
assertInvalidMessage("Undefined column name z",
"SELECT a, b, max(c) FROM %s WHERE a = 1 GROUP BY a, b, z");
// Test with composite partition key
createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key ((a, b), c, d))" + compactOption);
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 1, 1, 3, 6)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 1, 2, 6, 12)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 1, 3, 12, 24)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 12, 24)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 6, 12)");
assertInvalidMessage("Group by is not supported on only a part of the partition key",
"SELECT a, b, max(d) FROM %s GROUP BY a");
assertRows(execute("SELECT a, b, max(d) FROM %s GROUP BY a, b"),
row(1, 2, 12),
row(1, 1, 12));
assertRows(execute("SELECT a, b, max(d) FROM %s WHERE a = 1 AND b = 1 GROUP BY b"),
row(1, 1, 12));
// Test with table without clustering key
createTable("CREATE TABLE %s (a int primary key, b int, c int)" + compactOption);
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, 6)");
execute("INSERT INTO %s (a, b, c) VALUES (2, 6, 12)");
execute("INSERT INTO %s (a, b, c) VALUES (3, 12, 24)");
assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY",
"SELECT a, max(c) FROM %s WHERE a = 1 GROUP BY a, a");
}
@Test
public void testGroupByWithoutPagingWithDeletions() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key (a, b, c, d))"
+ compactOption);
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 3, 6)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 6, 12)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 9, 18)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 12, 24)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 3, 6)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 6, 12)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 9, 18)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 12, 24)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 3, 3, 6)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 3, 6, 12)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 3, 9, 18)");
execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 3, 12, 24)");
execute("DELETE FROM %s WHERE a = 1 AND b = 2 AND c = 1 AND d = 12");
execute("DELETE FROM %s WHERE a = 1 AND b = 2 AND c = 2 AND d = 9");
assertRows(execute("SELECT a, b, c, count(b), max(d) FROM %s GROUP BY a, b, c"),
row(1, 2, 1, 3L, 9),
row(1, 2, 2, 3L, 12),
row(1, 2, 3, 4L, 12));
}
@Test
public void testGroupByWithRangeNamesQueryWithoutPaging() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c int, d int, primary key (a, b, c))"
+ compactOption);
for (int i = 1; i < 5; i++)
for (int j = 1; j < 5; j++)
for (int k = 1; k < 5; k++)
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", i, j, k, i + j);
// Makes sure that we have some tombstones
execute("DELETE FROM %s WHERE a = 3");
// Range queries
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(2, 1, 3, 2L, 3),
row(4, 1, 5, 2L, 5));
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(2, 1, 3, 2L, 3),
row(4, 1, 5, 2L, 5));
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(1, 2, 3, 2L, 3),
row(2, 1, 3, 2L, 3),
row(2, 2, 4, 2L, 4),
row(4, 1, 5, 2L, 5),
row(4, 2, 6, 2L, 6));
// Range queries with LIMIT
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a LIMIT 5 ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(2, 1, 3, 2L, 3),
row(4, 1, 5, 2L, 5));
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b LIMIT 3 ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(2, 1, 3, 2L, 3),
row(4, 1, 5, 2L, 5));
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b LIMIT 3 ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(1, 2, 3, 2L, 3),
row(2, 1, 3, 2L, 3));
// Range queries with PER PARTITION LIMIT
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 2 ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(2, 1, 3, 2L, 3),
row(4, 1, 5, 2L, 5));
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 1 ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(2, 1, 3, 2L, 3),
row(4, 1, 5, 2L, 5));
// Range queries with PER PARTITION LIMIT and LIMIT
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 5 ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(2, 1, 3, 2L, 3),
row(4, 1, 5, 2L, 5));
assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2 ALLOW FILTERING"),
row(1, 1, 2, 2L, 2),
row(2, 1, 3, 2L, 3));
}
/**
* SelectSingleColumn
*/
@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);
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);
}
/**
* SelectTest
*/
/**
* Check query with KEY IN clause for wide row tables
* migrated from cql_tests.py:TestCQL.in_clause_wide_rows_test()
*/
@Test
public void testSelectKeyInForWideRows() throws Throwable
{
createTable("CREATE TABLE %s (k int, c int, v int, PRIMARY KEY (k, c)) WITH COMPACT STORAGE");
for (int i = 0; i < 10; i++)
execute("INSERT INTO %s (k, c, v) VALUES (0, ?, ?)", i, i);
assertRows(execute("SELECT v FROM %s WHERE k = 0 AND c IN (5, 2, 8)"),
row(2), row(5), row(8));
createTable("CREATE TABLE %s (k int, c1 int, c2 int, v int, PRIMARY KEY (k, c1, c2)) WITH COMPACT STORAGE");
for (int i = 0; i < 10; i++)
execute("INSERT INTO %s (k, c1, c2, v) VALUES (0, 0, ?, ?)", i, i);
assertEmpty(execute("SELECT v FROM %s WHERE k = 0 AND c1 IN (5, 2, 8) AND c2 = 3"));
assertRows(execute("SELECT v FROM %s WHERE k = 0 AND c1 = 0 AND c2 IN (5, 2, 8)"),
row(2), row(5), row(8));
}
/**
* Check SELECT respects inclusive and exclusive bounds
* migrated from cql_tests.py:TestCQL.exclusive_slice_test()
*/
@Test
public void testSelectBounds() throws Throwable
{
createTable("CREATE TABLE %s (k int, c int, v int, PRIMARY KEY (k, c)) WITH COMPACT STORAGE");
for (int i = 0; i < 10; i++)
execute("INSERT INTO %s (k, c, v) VALUES (0, ?, ?)", i, i);
assertRowCount(execute("SELECT v FROM %s WHERE k = 0"), 10);
assertRows(execute("SELECT v FROM %s WHERE k = 0 AND c >= 2 AND c <= 6"),
row(2), row(3), row(4), row(5), row(6));
assertRows(execute("SELECT v FROM %s WHERE k = 0 AND c > 2 AND c <= 6"),
row(3), row(4), row(5), row(6));
assertRows(execute("SELECT v FROM %s WHERE k = 0 AND c >= 2 AND c < 6"),
row(2), row(3), row(4), row(5));
assertRows(execute("SELECT v FROM %s WHERE k = 0 AND c > 2 AND c < 6"),
row(3), row(4), row(5));
assertRows(execute("SELECT v FROM %s WHERE k = 0 AND c > 2 AND c <= 6 LIMIT 2"),
row(3), row(4));
assertRows(execute("SELECT v FROM %s WHERE k = 0 AND c >= 2 AND c < 6 ORDER BY c DESC LIMIT 2"),
row(5), row(4));
}
/**
* Test for #4716 bug and more generally for good behavior of ordering,
* migrated from cql_tests.py:TestCQL.reversed_compact_test()
*/
@Test
public void testReverseCompact() throws Throwable
{
createTable("CREATE TABLE %s ( k text, c int, v int, PRIMARY KEY (k, c) ) WITH COMPACT STORAGE AND CLUSTERING ORDER BY (c DESC)");
for (int i = 0; i < 10; i++)
execute("INSERT INTO %s (k, c, v) VALUES ('foo', ?, ?)", i, i);
assertRows(execute("SELECT c FROM %s WHERE c > 2 AND c < 6 AND k = 'foo'"),
row(5), row(4), row(3));
assertRows(execute("SELECT c FROM %s WHERE c >= 2 AND c <= 6 AND k = 'foo'"),
row(6), row(5), row(4), row(3), row(2));
assertRows(execute("SELECT c FROM %s WHERE c > 2 AND c < 6 AND k = 'foo' ORDER BY c ASC"),
row(3), row(4), row(5));
assertRows(execute("SELECT c FROM %s WHERE c >= 2 AND c <= 6 AND k = 'foo' ORDER BY c ASC"),
row(2), row(3), row(4), row(5), row(6));
assertRows(execute("SELECT c FROM %s WHERE c > 2 AND c < 6 AND k = 'foo' ORDER BY c DESC"),
row(5), row(4), row(3));
assertRows(execute("SELECT c FROM %s WHERE c >= 2 AND c <= 6 AND k = 'foo' ORDER BY c DESC"),
row(6), row(5), row(4), row(3), row(2));
createTable("CREATE TABLE %s ( k text, c int, v int, PRIMARY KEY (k, c) ) WITH COMPACT STORAGE");
for (int i = 0; i < 10; i++)
execute("INSERT INTO %s(k, c, v) VALUES ('foo', ?, ?)", i, i);
assertRows(execute("SELECT c FROM %s WHERE c > 2 AND c < 6 AND k = 'foo'"),
row(3), row(4), row(5));
assertRows(execute("SELECT c FROM %s WHERE c >= 2 AND c <= 6 AND k = 'foo'"),
row(2), row(3), row(4), row(5), row(6));
assertRows(execute("SELECT c FROM %s WHERE c > 2 AND c < 6 AND k = 'foo' ORDER BY c ASC"),
row(3), row(4), row(5));
assertRows(execute("SELECT c FROM %s WHERE c >= 2 AND c <= 6 AND k = 'foo' ORDER BY c ASC"),
row(2), row(3), row(4), row(5), row(6));
assertRows(execute("SELECT c FROM %s WHERE c > 2 AND c < 6 AND k = 'foo' ORDER BY c DESC"),
row(5), row(4), row(3));
assertRows(execute("SELECT c FROM %s WHERE c >= 2 AND c <= 6 AND k = 'foo' ORDER BY c DESC"),
row(6), row(5), row(4), row(3), row(2));
}
/**
* Test for the bug from #4760 and #4759,
* migrated from cql_tests.py:TestCQL.reversed_compact_multikey_test()
*/
@Test
public void testReversedCompactMultikey() throws Throwable
{
createTable("CREATE TABLE %s (key text, c1 int, c2 int, value text, PRIMARY KEY(key, c1, c2) ) WITH COMPACT STORAGE AND CLUSTERING ORDER BY(c1 DESC, c2 DESC)");
for (int i = 0; i < 3; i++)
for (int j = 0; j < 3; j++)
execute("INSERT INTO %s (key, c1, c2, value) VALUES ('foo', ?, ?, 'bar')", i, j);
// Equalities
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 = 1"),
row(1, 2), row(1, 1), row(1, 0));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 = 1 ORDER BY c1 ASC, c2 ASC"),
row(1, 0), row(1, 1), row(1, 2));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 = 1 ORDER BY c1 DESC, c2 DESC"),
row(1, 2), row(1, 1), row(1, 0));
// GT
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 > 1"),
row(2, 2), row(2, 1), row(2, 0));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 > 1 ORDER BY c1 ASC, c2 ASC"),
row(2, 0), row(2, 1), row(2, 2));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 > 1 ORDER BY c1 DESC, c2 DESC"),
row(2, 2), row(2, 1), row(2, 0));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 >= 1"),
row(2, 2), row(2, 1), row(2, 0), row(1, 2), row(1, 1), row(1, 0));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 >= 1 ORDER BY c1 ASC, c2 ASC"),
row(1, 0), row(1, 1), row(1, 2), row(2, 0), row(2, 1), row(2, 2));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 >= 1 ORDER BY c1 ASC"),
row(1, 0), row(1, 1), row(1, 2), row(2, 0), row(2, 1), row(2, 2));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 >= 1 ORDER BY c1 DESC, c2 DESC"),
row(2, 2), row(2, 1), row(2, 0), row(1, 2), row(1, 1), row(1, 0));
// LT
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 < 1"),
row(0, 2), row(0, 1), row(0, 0));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 < 1 ORDER BY c1 ASC, c2 ASC"),
row(0, 0), row(0, 1), row(0, 2));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 < 1 ORDER BY c1 DESC, c2 DESC"),
row(0, 2), row(0, 1), row(0, 0));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 <= 1"),
row(1, 2), row(1, 1), row(1, 0), row(0, 2), row(0, 1), row(0, 0));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 <= 1 ORDER BY c1 ASC, c2 ASC"),
row(0, 0), row(0, 1), row(0, 2), row(1, 0), row(1, 1), row(1, 2));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 <= 1 ORDER BY c1 ASC"),
row(0, 0), row(0, 1), row(0, 2), row(1, 0), row(1, 1), row(1, 2));
assertRows(execute("SELECT c1, c2 FROM %s WHERE key='foo' AND c1 <= 1 ORDER BY c1 DESC, c2 DESC"),
row(1, 2), row(1, 1), row(1, 0), row(0, 2), row(0, 1), row(0, 0));
}
/**
* Migrated from cql_tests.py:TestCQL.multi_in_compact_non_composite_test()
*/
@Test
public void testMultiSelectsNonCompositeCompactStorage() throws Throwable
{
createTable("CREATE TABLE %s (key int, c int, v int, PRIMARY KEY (key, c)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (key, c, v) VALUES (0, 0, 0)");
execute("INSERT INTO %s (key, c, v) VALUES (0, 1, 1)");
execute("INSERT INTO %s (key, c, v) VALUES (0, 2, 2)");
assertRows(execute("SELECT * FROM %s WHERE key=0 AND c IN (0, 2)"),
row(0, 0, 0), row(0, 2, 2));
}
@Test
public void testSelectDistinct() throws Throwable
{
//Test a 'compact storage' table.
createTable("CREATE TABLE %s (pk0 int, pk1 int, val int, PRIMARY KEY((pk0, pk1))) WITH COMPACT STORAGE");
for (int i = 0; i < 3; i++)
execute("INSERT INTO %s (pk0, pk1, val) VALUES (?, ?, ?)", i, i, i);
assertRows(execute("SELECT DISTINCT pk0, pk1 FROM %s LIMIT 1"),
row(0, 0));
assertRows(execute("SELECT DISTINCT pk0, pk1 FROM %s LIMIT 3"),
row(0, 0),
row(2, 2),
row(1, 1));
// Test a 'wide row' thrift table.
createTable("CREATE TABLE %s (pk int, name text, val int, PRIMARY KEY(pk, name)) WITH COMPACT STORAGE");
for (int i = 0; i < 3; i++)
{
execute("INSERT INTO %s (pk, name, val) VALUES (?, 'name0', 0)", i);
execute("INSERT INTO %s (pk, name, val) VALUES (?, 'name1', 1)", i);
}
assertRows(execute("SELECT DISTINCT pk FROM %s LIMIT 1"),
row(1));
assertRows(execute("SELECT DISTINCT pk FROM %s LIMIT 3"),
row(1),
row(0),
row(2));
}
@Test
public void testFilteringOnCompactTablesWithoutIndices() throws Throwable
{
//----------------------------------------------
// Test COMPACT table with clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int, b int, c int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, 6)");
execute("INSERT INTO %s (a, b, c) VALUES (1, 4, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (2, 3, 7)");
// Adds tomstones
execute("INSERT INTO %s (a, b, c) VALUES (1, 1, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (2, 2, 7)");
execute("DELETE FROM %s WHERE a = 1 AND b = 1");
execute("DELETE FROM %s WHERE a = 2 AND b = 2");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = 4");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = 4 ALLOW FILTERING"),
row(1, 4, 4));
assertInvalidMessage("IN predicates on non-primary-key columns (c) is not yet supported",
"SELECT * FROM %s WHERE a IN (1, 2) AND c IN (6, 7)");
assertInvalidMessage("IN predicates on non-primary-key columns (c) is not yet supported",
"SELECT * FROM %s WHERE a IN (1, 2) AND c IN (6, 7) ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > 4");
assertRows(execute("SELECT * FROM %s WHERE c > 4 ALLOW FILTERING"),
row(1, 3, 6),
row(2, 3, 7));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE b < 3 AND c <= 4");
assertRows(execute("SELECT * FROM %s WHERE b < 3 AND c <= 4 ALLOW FILTERING"),
row(1, 2, 4));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= 3 AND c <= 6");
assertRows(execute("SELECT * FROM %s WHERE c >= 3 AND c <= 6 ALLOW FILTERING"),
row(1, 2, 4),
row(1, 3, 6),
row(1, 4, 4));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c > null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c > ? ALLOW FILTERING",
unset());
//----------------------------------------------
// Test COMPACT table without clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, 6)");
execute("INSERT INTO %s (a, b, c) VALUES (3, 2, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (4, 1, 7)");
// Adds tomstones
execute("INSERT INTO %s (a, b, c) VALUES (0, 1, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (5, 2, 7)");
execute("DELETE FROM %s WHERE a = 0");
execute("DELETE FROM %s WHERE a = 5");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = 4");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 2 AND c = 4 ALLOW FILTERING"),
row(1, 2, 4));
assertInvalidMessage("IN predicates on non-primary-key columns (c) is not yet supported",
"SELECT * FROM %s WHERE a IN (1, 2) AND c IN (6, 7)");
assertInvalidMessage("IN predicates on non-primary-key columns (c) is not yet supported",
"SELECT * FROM %s WHERE a IN (1, 2) AND c IN (6, 7) ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > 4");
assertRows(execute("SELECT * FROM %s WHERE c > 4 ALLOW FILTERING"),
row(2, 1, 6),
row(4, 1, 7));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE b < 3 AND c <= 4");
assertRows(execute("SELECT * FROM %s WHERE b < 3 AND c <= 4 ALLOW FILTERING"),
row(1, 2, 4),
row(3, 2, 4));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= 3 AND c <= 6");
assertRows(execute("SELECT * FROM %s WHERE c >= 3 AND c <= 6 ALLOW FILTERING"),
row(1, 2, 4),
row(2, 1, 6),
row(3, 2, 4));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c > null ALLOW FILTERING");
// // Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c > ? ALLOW FILTERING",
unset());
}
@Test
public void testFilteringOnCompactTablesWithoutIndicesAndWithLists() throws Throwable
{
//----------------------------------------------
// Test COMPACT table with clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int, b int, c frozen<list<int>>, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, [4, 2])");
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, [6, 2])");
execute("INSERT INTO %s (a, b, c) VALUES (1, 4, [4, 1])");
execute("INSERT INTO %s (a, b, c) VALUES (2, 3, [7, 1])");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = [4, 1]");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = [4, 1] ALLOW FILTERING"),
row(1, 4, list(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > [4, 2]");
assertRows(execute("SELECT * FROM %s WHERE c > [4, 2] ALLOW FILTERING"),
row(1, 3, list(6, 2)),
row(2, 3, list(7, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE b <= 3 AND c < [6, 2]");
assertRows(execute("SELECT * FROM %s WHERE b <= 3 AND c < [6, 2] ALLOW FILTERING"),
row(1, 2, list(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= [4, 2] AND c <= [6, 4]");
assertRows(execute("SELECT * FROM %s WHERE c >= [4, 2] AND c <= [6, 4] ALLOW FILTERING"),
row(1, 2, list(4, 2)),
row(1, 3, list(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, list(4, 2)),
row(1, 3, list(6, 2)));
assertInvalidMessage("Cannot use CONTAINS KEY on non-map column c",
"SELECT * FROM %s WHERE c CONTAINS KEY 2 ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 AND c CONTAINS 6 ALLOW FILTERING"),
row(1, 3, list(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c CONTAINS null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c CONTAINS ? ALLOW FILTERING",
unset());
//----------------------------------------------
// Test COMPACT table without clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c frozen<list<int>>) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, [4, 2])");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, [6, 2])");
execute("INSERT INTO %s (a, b, c) VALUES (3, 2, [4, 1])");
execute("INSERT INTO %s (a, b, c) VALUES (4, 1, [7, 1])");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 2 AND c = [4, 2]");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 2 AND c = [4, 2] ALLOW FILTERING"),
row(1, 2, list(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > [4, 2]");
assertRows(execute("SELECT * FROM %s WHERE c > [4, 2] ALLOW FILTERING"),
row(2, 1, list(6, 2)),
row(4, 1, list(7, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE b < 3 AND c <= [4, 2]");
assertRows(execute("SELECT * FROM %s WHERE b < 3 AND c <= [4, 2] ALLOW FILTERING"),
row(1, 2, list(4, 2)),
row(3, 2, list(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= [4, 3] AND c <= [7]");
assertRows(execute("SELECT * FROM %s WHERE c >= [4, 3] AND c <= [7] ALLOW FILTERING"),
row(2, 1, list(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, list(4, 2)),
row(2, 1, list(6, 2)));
assertInvalidMessage("Cannot use CONTAINS KEY on non-map column c",
"SELECT * FROM %s WHERE c CONTAINS KEY 2 ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 AND c CONTAINS 6 ALLOW FILTERING"),
row(2, 1, list(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c CONTAINS null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c CONTAINS ? ALLOW FILTERING",
unset());
}
@Test
public void testFilteringOnCompactTablesWithoutIndicesAndWithSets() throws Throwable
{
//----------------------------------------------
// Test COMPACT table with clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int, b int, c frozen<set<int>>, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, {4, 2})");
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, {6, 2})");
execute("INSERT INTO %s (a, b, c) VALUES (1, 4, {4, 1})");
execute("INSERT INTO %s (a, b, c) VALUES (2, 3, {7, 1})");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = {4, 1}");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = {4, 1} ALLOW FILTERING"),
row(1, 4, set(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > {4, 2}");
assertRows(execute("SELECT * FROM %s WHERE c > {4, 2} ALLOW FILTERING"),
row(1, 3, set(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE b <= 3 AND c < {6, 2}");
assertRows(execute("SELECT * FROM %s WHERE b <= 3 AND c < {6, 2} ALLOW FILTERING"),
row(1, 2, set(2, 4)),
row(2, 3, set(1, 7)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= {4, 2} AND c <= {6, 4}");
assertRows(execute("SELECT * FROM %s WHERE c >= {4, 2} AND c <= {6, 4} ALLOW FILTERING"),
row(1, 2, set(4, 2)),
row(1, 3, set(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, set(4, 2)),
row(1, 3, set(6, 2)));
assertInvalidMessage("Cannot use CONTAINS KEY on non-map column c",
"SELECT * FROM %s WHERE c CONTAINS KEY 2 ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 AND c CONTAINS 6 ALLOW FILTERING"),
row(1, 3, set(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c CONTAINS null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c CONTAINS ? ALLOW FILTERING",
unset());
//----------------------------------------------
// Test COMPACT table without clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c frozen<set<int>>) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, {4, 2})");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, {6, 2})");
execute("INSERT INTO %s (a, b, c) VALUES (3, 2, {4, 1})");
execute("INSERT INTO %s (a, b, c) VALUES (4, 1, {7, 1})");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 2 AND c = {4, 2}");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 2 AND c = {4, 2} ALLOW FILTERING"),
row(1, 2, set(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > {4, 2}");
assertRows(execute("SELECT * FROM %s WHERE c > {4, 2} ALLOW FILTERING"),
row(2, 1, set(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE b < 3 AND c <= {4, 2}");
assertRows(execute("SELECT * FROM %s WHERE b < 3 AND c <= {4, 2} ALLOW FILTERING"),
row(1, 2, set(4, 2)),
row(4, 1, set(1, 7)),
row(3, 2, set(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= {4, 3} AND c <= {7}");
assertRows(execute("SELECT * FROM %s WHERE c >= {5, 2} AND c <= {7} ALLOW FILTERING"),
row(2, 1, set(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, set(4, 2)),
row(2, 1, set(6, 2)));
assertInvalidMessage("Cannot use CONTAINS KEY on non-map column c",
"SELECT * FROM %s WHERE c CONTAINS KEY 2 ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 AND c CONTAINS 6 ALLOW FILTERING"),
row(2, 1, set(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c CONTAINS null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c CONTAINS ? ALLOW FILTERING",
unset());
}
@Test
public void testAllowFilteringOnPartitionKeyWithDistinct() throws Throwable
{
// Test a 'compact storage' table.
createTable("CREATE TABLE %s (pk0 int, pk1 int, val int, PRIMARY KEY((pk0, pk1))) WITH COMPACT STORAGE");
for (int i = 0; i < 3; i++)
execute("INSERT INTO %s (pk0, pk1, val) VALUES (?, ?, ?)", i, i, i);
beforeAndAfterFlush(() -> {
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT DISTINCT pk0, pk1 FROM %s WHERE pk1 = 1 LIMIT 3");
assertRows(execute("SELECT DISTINCT pk0, pk1 FROM %s WHERE pk0 < 2 AND pk1 = 1 LIMIT 1 ALLOW FILTERING"),
row(1, 1));
assertRows(execute("SELECT DISTINCT pk0, pk1 FROM %s WHERE pk1 > 1 LIMIT 3 ALLOW FILTERING"),
row(2, 2));
});
// Test a 'wide row' thrift table.
createTable("CREATE TABLE %s (pk int, name text, val int, PRIMARY KEY(pk, name)) WITH COMPACT STORAGE");
for (int i = 0; i < 3; i++)
{
execute("INSERT INTO %s (pk, name, val) VALUES (?, 'name0', 0)", i);
execute("INSERT INTO %s (pk, name, val) VALUES (?, 'name1', 1)", i);
}
beforeAndAfterFlush(() -> {
assertRows(execute("SELECT DISTINCT pk FROM %s WHERE pk > 1 LIMIT 1 ALLOW FILTERING"),
row(2));
assertRows(execute("SELECT DISTINCT pk FROM %s WHERE pk > 0 LIMIT 3 ALLOW FILTERING"),
row(1),
row(2));
});
}
@Test
public void testAllowFilteringOnPartitionKeyWithCounters() throws Throwable
{
for (String compactStorageClause : new String[]{ "", " WITH COMPACT STORAGE" })
{
createTable("CREATE TABLE %s (a int, b int, c int, cnt counter, PRIMARY KEY ((a, b), c))"
+ compactStorageClause);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 14L, 11, 12, 13);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 24L, 21, 22, 23);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 27L, 21, 22, 26);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 34L, 31, 32, 33);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 24L, 41, 42, 43);
beforeAndAfterFlush(() -> {
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE cnt = 24"),
row(41, 42, 43, 24L),
row(21, 22, 23, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE b > 22 AND cnt = 24"),
row(41, 42, 43, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE b > 10 AND b < 25 AND cnt = 24"),
row(21, 22, 23, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE b > 10 AND c < 25 AND cnt = 24"),
row(21, 22, 23, 24L));
assertInvalidMessage(
"ORDER BY is only supported when the partition key is restricted by an EQ or an IN.",
"SELECT * FROM %s WHERE a = 21 AND b > 10 AND cnt > 23 ORDER BY c DESC ALLOW FILTERING");
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE a = 21 AND b = 22 AND cnt > 23 ORDER BY c DESC"),
row(21, 22, 26, 27L),
row(21, 22, 23, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE cnt > 20 AND cnt < 30"),
row(41, 42, 43, 24L),
row(21, 22, 23, 24L),
row(21, 22, 26, 27L));
});
}
}
@Test
public void testAllowFilteringOnPartitionKeyOnCompactTablesWithoutIndicesAndWithLists() throws Throwable
{
// ----------------------------------------------
// Test COMPACT table with clustering columns
// ----------------------------------------------
createTable("CREATE TABLE %s (a int, b int, c frozen<list<int>>, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, [4, 2])");
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, [6, 2])");
execute("INSERT INTO %s (a, b, c) VALUES (1, 4, [4, 1])");
execute("INSERT INTO %s (a, b, c) VALUES (2, 3, [7, 1])");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b = 4 AND c = [4, 1]");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND b >= 4 AND c = [4, 1] ALLOW FILTERING"),
row(1, 4, list(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 0 AND c > [4, 2]");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND c > [4, 2] ALLOW FILTERING"),
row(1, 3, list(6, 2)),
row(2, 3, list(7, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND b <= 3 AND c < [6, 2]");
assertRows(execute("SELECT * FROM %s WHERE a <= 1 AND b <= 3 AND c < [6, 2] ALLOW FILTERING"),
row(1, 2, list(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a <= 1 AND c >= [4, 2] AND c <= [6, 4]");
assertRows(execute("SELECT * FROM %s WHERE a > 0 AND b <= 3 AND c >= [4, 2] AND c <= [6, 4] ALLOW FILTERING"),
row(1, 2, list(4, 2)),
row(1, 3, list(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE a > 0 AND c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, list(4, 2)),
row(1, 3, list(6, 2)));
assertInvalidMessage("Cannot use CONTAINS KEY on non-map column c",
"SELECT * FROM %s WHERE a > 1 AND c CONTAINS KEY 2 ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE a < 2 AND c CONTAINS 2 AND c CONTAINS 6 ALLOW FILTERING"),
row(1, 3, list(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a > 1 AND c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a > 1 AND c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a > 1 AND c CONTAINS null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a > 1 AND c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a > 1 AND c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a > 1 AND c CONTAINS ? ALLOW FILTERING",
unset());
// ----------------------------------------------
// Test COMPACT table without clustering columns
// ----------------------------------------------
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c frozen<list<int>>) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, [4, 2])");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, [6, 2])");
execute("INSERT INTO %s (a, b, c) VALUES (3, 2, [4, 1])");
execute("INSERT INTO %s (a, b, c) VALUES (4, 1, [7, 1])");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b = 2 AND c = [4, 2]");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND b = 2 AND c = [4, 2] ALLOW FILTERING"),
row(1, 2, list(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c > [4, 2]");
assertRows(execute("SELECT * FROM %s WHERE a > 3 AND c > [4, 2] ALLOW FILTERING"),
row(4, 1, list(7, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a < 1 AND b < 3 AND c <= [4, 2]");
assertRows(execute("SELECT * FROM %s WHERE a < 3 AND b < 3 AND c <= [4, 2] ALLOW FILTERING"),
row(1, 2, list(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c >= [4, 3] AND c <= [7]");
assertRows(execute("SELECT * FROM %s WHERE a >= 2 AND c >= [4, 3] AND c <= [7] ALLOW FILTERING"),
row(2, 1, list(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 3 AND c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, list(4, 2)),
row(2, 1, list(6, 2)));
assertInvalidMessage("Cannot use CONTAINS KEY on non-map column c",
"SELECT * FROM %s WHERE a >=1 AND c CONTAINS KEY 2 ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE a < 3 AND c CONTAINS 2 AND c CONTAINS 6 ALLOW FILTERING"),
row(2, 1, list(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a > 1 AND c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a > 1 AND c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a > 1 AND c CONTAINS null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a > 1 AND c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a > 1 AND c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a > 1 AND c CONTAINS ? ALLOW FILTERING",
unset());
}
@Test
public void testAllowFilteringOnPartitionKeyOnCompactTablesWithoutIndicesAndWithMaps() throws Throwable
{
//----------------------------------------------
// Test COMPACT table with clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int, b int, c frozen<map<int, int>>, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, {4 : 2})");
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, {6 : 2})");
execute("INSERT INTO %s (a, b, c) VALUES (1, 4, {4 : 1})");
execute("INSERT INTO %s (a, b, c) VALUES (2, 3, {7 : 1})");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b = 4 AND c = {4 : 1}");
assertRows(execute("SELECT * FROM %s WHERE a <= 1 AND b = 4 AND c = {4 : 1} ALLOW FILTERING"),
row(1, 4, map(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c > {4 : 2}");
assertRows(execute("SELECT * FROM %s WHERE a > 1 AND c > {4 : 2} ALLOW FILTERING"),
row(2, 3, map(7, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND b <= 3 AND c < {6 : 2}");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND b <= 3 AND c < {6 : 2} ALLOW FILTERING"),
row(1, 2, map(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 1 AND c >= {4 : 2} AND c <= {6 : 4}");
assertRows(execute("SELECT * FROM %s WHERE a > 0 AND c >= {4 : 2} AND c <= {6 : 4} ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(1, 3, map(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a > 10 AND c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE a > 0 AND c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(1, 3, map(6, 2)));
assertRows(execute("SELECT * FROM %s WHERE a < 2 AND c CONTAINS KEY 6 ALLOW FILTERING"),
row(1, 3, map(6, 2)));
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND c CONTAINS 2 AND c CONTAINS KEY 6 ALLOW FILTERING"),
row(1, 3, map(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS null ALLOW FILTERING");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS KEY null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS KEY ? ALLOW FILTERING",
unset());
//----------------------------------------------
// Test COMPACT table without clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c frozen<map<int, int>>) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, {4 : 2})");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, {6 : 2})");
execute("INSERT INTO %s (a, b, c) VALUES (3, 2, {4 : 1})");
execute("INSERT INTO %s (a, b, c) VALUES (4, 1, {7 : 1})");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b = 2 AND c = {4 : 2}");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND b = 2 AND c = {4 : 2} ALLOW FILTERING"),
row(1, 2, map(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c > {4 : 2}");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND c > {4 : 2} ALLOW FILTERING"),
row(2, 1, map(6, 2)),
row(4, 1, map(7, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b < 3 AND c <= {4 : 2}");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND b < 3 AND c <= {4 : 2} ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(3, 2, map(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c >= {4 : 3} AND c <= {7 : 1}");
assertRows(execute("SELECT * FROM %s WHERE a >= 2 AND c >= {5 : 2} AND c <= {7 : 0} ALLOW FILTERING"),
row(2, 1, map(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(2, 1, map(6, 2)));
assertRows(execute("SELECT * FROM %s WHERE a > 0 AND c CONTAINS KEY 4 ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(3, 2, map(4, 1)));
assertRows(execute("SELECT * FROM %s WHERE a >= 2 AND c CONTAINS 2 AND c CONTAINS KEY 6 ALLOW FILTERING"),
row(2, 1, map(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS KEY null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS KEY null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS KEY ? ALLOW FILTERING",
unset());
}
@Test
public void testAllowFilteringOnPartitionKeyOnCompactTablesWithoutIndicesAndWithSets() throws Throwable
{
//----------------------------------------------
// Test COMPACT table with clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int, b int, c frozen<set<int>>, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, {4, 2})");
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, {6, 2})");
execute("INSERT INTO %s (a, b, c) VALUES (1, 4, {4, 1})");
execute("INSERT INTO %s (a, b, c) VALUES (2, 3, {7, 1})");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b = 4 AND c = {4, 1}");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND b = 4 AND c = {4, 1} ALLOW FILTERING"),
row(1, 4, set(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c > {4, 2}");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND c > {4, 2} ALLOW FILTERING"),
row(1, 3, set(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b <= 3 AND c < {6, 2}");
assertRows(execute("SELECT * FROM %s WHERE a > 0 AND b <= 3 AND c < {6, 2} ALLOW FILTERING"),
row(1, 2, set(2, 4)),
row(2, 3, set(1, 7)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c >= {4, 2} AND c <= {6, 4}");
assertRows(execute("SELECT * FROM %s WHERE a >= 0 AND c >= {4, 2} AND c <= {6, 4} ALLOW FILTERING"),
row(1, 2, set(4, 2)),
row(1, 3, set(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE a < 2 AND c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, set(4, 2)),
row(1, 3, set(6, 2)));
assertInvalidMessage("Cannot use CONTAINS KEY on non-map column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS KEY 2 ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND c CONTAINS 2 AND c CONTAINS 6 ALLOW FILTERING"),
row(1, 3, set(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS ? ALLOW FILTERING",
unset());
//----------------------------------------------
// Test COMPACT table without clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c frozen<set<int>>) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, {4, 2})");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, {6, 2})");
execute("INSERT INTO %s (a, b, c) VALUES (3, 2, {4, 1})");
execute("INSERT INTO %s (a, b, c) VALUES (4, 1, {7, 1})");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b = 2 AND c = {4, 2}");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND b = 2 AND c = {4, 2} ALLOW FILTERING"),
row(1, 2, set(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c > {4, 2}");
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND c > {4, 2} ALLOW FILTERING"),
row(2, 1, set(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND b < 3 AND c <= {4, 2}");
assertRows(execute("SELECT * FROM %s WHERE a <= 4 AND b < 3 AND c <= {4, 2} ALLOW FILTERING"),
row(1, 2, set(4, 2)),
row(4, 1, set(1, 7)),
row(3, 2, set(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c >= {4, 3} AND c <= {7}");
assertRows(execute("SELECT * FROM %s WHERE a < 3 AND c >= {5, 2} AND c <= {7} ALLOW FILTERING"),
row(2, 1, set(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE a >= 0 AND c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, set(4, 2)),
row(2, 1, set(6, 2)));
assertInvalidMessage("Cannot use CONTAINS KEY on non-map column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS KEY 2 ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE a >= 2 AND c CONTAINS 2 AND c CONTAINS 6 ALLOW FILTERING"),
row(2, 1, set(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE a >= 1 AND c CONTAINS ? ALLOW FILTERING",
unset());
}
@Test
public void testAllowFilteringOnPartitionKeyOnCompactTablesWithoutIndices() throws Throwable
{
// ----------------------------------------------
// Test COMPACT table with clustering columns
// ----------------------------------------------
createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY ((a, b), c)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, d) VALUES (1, 2, 4, 5)");
execute("INSERT INTO %s (a, b, c, d) VALUES (1, 3, 6, 7)");
execute("INSERT INTO %s (a, b, c, d) VALUES (1, 4, 4, 5)");
execute("INSERT INTO %s (a, b, c, d) VALUES (2, 3, 7, 8)");
// Adds tomstones
execute("INSERT INTO %s (a, b, c, d) VALUES (1, 1, 4, 5)");
execute("INSERT INTO %s (a, b, c, d) VALUES (2, 2, 7, 8)");
execute("DELETE FROM %s WHERE a = 1 AND b = 1 AND c = 4");
execute("DELETE FROM %s WHERE a = 2 AND b = 2 AND c = 7");
beforeAndAfterFlush(() -> {
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = 4"),
row(1, 4, 4, 5));
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = 4 AND d = 5");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = 4 ALLOW FILTERING"),
row(1, 4, 4, 5));
assertInvalidMessage("IN predicates on non-primary-key columns (d) is not yet supported",
"SELECT * FROM %s WHERE a IN (1, 2) AND b = 3 AND d IN (6, 7)");
assertInvalidMessage("IN predicates on non-primary-key columns (d) is not yet supported",
"SELECT * FROM %s WHERE a IN (1, 2) AND b = 3 AND d IN (6, 7) ALLOW FILTERING");
assertRows(execute("SELECT * FROM %s WHERE a < 2 AND c > 4 AND c <= 6 ALLOW FILTERING"),
row(1, 3, 6, 7));
assertRows(execute("SELECT * FROM %s WHERE a <= 1 AND b >= 2 AND c >= 4 AND d <= 8 ALLOW FILTERING"),
row(1, 3, 6, 7),
row(1, 4, 4, 5),
row(1, 2, 4, 5));
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND c >= 4 AND d <= 8 ALLOW FILTERING"),
row(1, 3, 6, 7),
row(1, 4, 4, 5),
row(1, 2, 4, 5));
assertRows(execute("SELECT * FROM %s WHERE a >= 2 AND c >= 4 AND d <= 8 ALLOW FILTERING"),
row(2, 3, 7, 8));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE d = null");
assertInvalidMessage("Unsupported null value for column a",
"SELECT * FROM %s WHERE a = null ALLOW FILTERING");
assertInvalidMessage("Unsupported null value for column a",
"SELECT * FROM %s WHERE a > null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column a",
"SELECT * FROM %s WHERE a = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column a",
"SELECT * FROM %s WHERE a > ? ALLOW FILTERING",
unset());
//----------------------------------------------
// Test COMPACT table without clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int primary key, b int, c int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, 6)");
execute("INSERT INTO %s (a, b, c) VALUES (3, 2, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (4, 1, 7)");
// Adds tomstones
execute("INSERT INTO %s (a, b, c) VALUES (0, 1, 4)");
execute("INSERT INTO %s (a, b, c) VALUES (5, 2, 7)");
execute("DELETE FROM %s WHERE a = 0");
execute("DELETE FROM %s WHERE a = 5");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = 4");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 2 AND c = 4 ALLOW FILTERING"),
row(1, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 2 ALLOW FILTERING"),
row(1, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE b >= 2 AND c <= 4 ALLOW FILTERING"),
row(1, 2, 4),
row(3, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE a = 1 ALLOW FILTERING"),
row(1, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE b >= 2 ALLOW FILTERING"),
row(1, 2, 4),
row(3, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE a >= 2 AND b <=1 ALLOW FILTERING"),
row(2, 1, 6),
row(4, 1, 7));
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND c >= 4 ALLOW FILTERING"),
row(1, 2, 4));
assertInvalidMessage("IN predicates on non-primary-key columns (b) is not yet supported",
"SELECT * FROM %s WHERE a = 1 AND b IN (1, 2) AND c IN (6, 7)");
assertInvalidMessage("IN predicates on non-primary-key columns (c) is not yet supported",
"SELECT * FROM %s WHERE a IN (1, 2) AND c IN (6, 7) ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > 4");
assertRows(execute("SELECT * FROM %s WHERE c > 4 ALLOW FILTERING"),
row(2, 1, 6),
row(4, 1, 7));
assertRows(execute("SELECT * FROM %s WHERE a >= 1 AND b >= 2 AND c <= 4 ALLOW FILTERING"),
row(1, 2, 4),
row(3, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE a < 3 AND c <= 4 ALLOW FILTERING"),
row(1, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE a < 3 AND b >= 2 AND c <= 4 ALLOW FILTERING"),
row(1, 2, 4));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= 3 AND c <= 6");
assertRows(execute("SELECT * FROM %s WHERE c <=6 ALLOW FILTERING"),
row(1, 2, 4),
row(2, 1, 6),
row(3, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE token(a) >= token(2)"),
row(2, 1, 6),
row(4, 1, 7),
row(3, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE token(a) >= token(2) ALLOW FILTERING"),
row(2, 1, 6),
row(4, 1, 7),
row(3, 2, 4));
assertRows(execute("SELECT * FROM %s WHERE token(a) >= token(2) AND b = 1 ALLOW FILTERING"),
row(2, 1, 6),
row(4, 1, 7));
});
}
@Test
public void testFilteringOnCompactTablesWithoutIndicesAndWithMaps() throws Throwable
{
//----------------------------------------------
// Test COMPACT table with clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int, b int, c frozen<map<int, int>>, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, {4 : 2})");
execute("INSERT INTO %s (a, b, c) VALUES (1, 3, {6 : 2})");
execute("INSERT INTO %s (a, b, c) VALUES (1, 4, {4 : 1})");
execute("INSERT INTO %s (a, b, c) VALUES (2, 3, {7 : 1})");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = {4 : 1}");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 4 AND c = {4 : 1} ALLOW FILTERING"),
row(1, 4, map(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > {4 : 2}");
assertRows(execute("SELECT * FROM %s WHERE c > {4 : 2} ALLOW FILTERING"),
row(1, 3, map(6, 2)),
row(2, 3, map(7, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE b <= 3 AND c < {6 : 2}");
assertRows(execute("SELECT * FROM %s WHERE b <= 3 AND c < {6 : 2} ALLOW FILTERING"),
row(1, 2, map(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= {4 : 2} AND c <= {6 : 4}");
assertRows(execute("SELECT * FROM %s WHERE c >= {4 : 2} AND c <= {6 : 4} ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(1, 3, map(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(1, 3, map(6, 2)));
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS KEY 6 ALLOW FILTERING"),
row(1, 3, map(6, 2)));
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 AND c CONTAINS KEY 6 ALLOW FILTERING"),
row(1, 3, map(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c CONTAINS null ALLOW FILTERING");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c CONTAINS KEY null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c CONTAINS ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c CONTAINS KEY ? ALLOW FILTERING",
unset());
//----------------------------------------------
// Test COMPACT table without clustering columns
//----------------------------------------------
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c frozen<map<int, int>>) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 2, {4 : 2})");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, {6 : 2})");
execute("INSERT INTO %s (a, b, c) VALUES (3, 2, {4 : 1})");
execute("INSERT INTO %s (a, b, c) VALUES (4, 1, {7 : 1})");
beforeAndAfterFlush(() -> {
// Checks filtering
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE a = 1 AND b = 2 AND c = {4 : 2}");
assertRows(execute("SELECT * FROM %s WHERE a = 1 AND b = 2 AND c = {4 : 2} ALLOW FILTERING"),
row(1, 2, map(4, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > {4 : 2}");
assertRows(execute("SELECT * FROM %s WHERE c > {4 : 2} ALLOW FILTERING"),
row(2, 1, map(6, 2)),
row(4, 1, map(7, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE b < 3 AND c <= {4 : 2}");
assertRows(execute("SELECT * FROM %s WHERE b < 3 AND c <= {4 : 2} ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(3, 2, map(4, 1)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c >= {4 : 3} AND c <= {7 : 1}");
assertRows(execute("SELECT * FROM %s WHERE c >= {5 : 2} AND c <= {7 : 0} ALLOW FILTERING"),
row(2, 1, map(6, 2)));
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS 2");
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(2, 1, map(6, 2)));
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS KEY 4 ALLOW FILTERING"),
row(1, 2, map(4, 2)),
row(3, 2, map(4, 1)));
assertRows(execute("SELECT * FROM %s WHERE c CONTAINS 2 AND c CONTAINS KEY 6 ALLOW FILTERING"),
row(2, 1, map(6, 2)));
});
// Checks filtering with null
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c = null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c = null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c > null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c > null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c CONTAINS null ALLOW FILTERING");
assertInvalidMessage(StatementRestrictions.REQUIRES_ALLOW_FILTERING_MESSAGE,
"SELECT * FROM %s WHERE c CONTAINS KEY null");
assertInvalidMessage("Unsupported null value for column c",
"SELECT * FROM %s WHERE c CONTAINS KEY null ALLOW FILTERING");
// Checks filtering with unset
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c = ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c > ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c CONTAINS ? ALLOW FILTERING",
unset());
assertInvalidMessage("Unsupported unset value for column c",
"SELECT * FROM %s WHERE c CONTAINS KEY ? ALLOW FILTERING",
unset());
}
@Test
public void filteringOnCompactTable() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 11, 12, 13, 14);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 21, 22, 23, 24);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 21, 25, 26, 27);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 31, 32, 33, 34);
beforeAndAfterFlush(() -> {
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE c > 13"),
row(21, 22, 23, 24),
row(21, 25, 26, 27),
row(31, 32, 33, 34));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE c > 13 AND c < 33"),
row(21, 22, 23, 24),
row(21, 25, 26, 27));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE c > 13 AND b < 32"),
row(21, 22, 23, 24),
row(21, 25, 26, 27));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE a = 21 AND c > 13 AND b < 32 ORDER BY b DESC"),
row(21, 25, 26, 27),
row(21, 22, 23, 24));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE a IN (21, 31) AND c > 13 ORDER BY b DESC"),
row(31, 32, 33, 34),
row(21, 25, 26, 27),
row(21, 22, 23, 24));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE c > 13 AND d < 34"),
row(21, 22, 23, 24),
row(21, 25, 26, 27));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE c > 13"),
row(21, 22, 23, 24),
row(21, 25, 26, 27),
row(31, 32, 33, 34));
});
// with frozen in clustering key
createTable("CREATE TABLE %s (a int, b int, c frozen<list<int>>, d int, PRIMARY KEY (a, b, c)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 11, 12, list(1, 3), 14);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 21, 22, list(2, 3), 24);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 21, 25, list(2, 6), 27);
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 31, 32, list(3, 3), 34);
beforeAndAfterFlush(() -> {
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE c CONTAINS 2"),
row(21, 22, list(2, 3), 24),
row(21, 25, list(2, 6), 27));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE c CONTAINS 2 AND b < 25"),
row(21, 22, list(2, 3), 24));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE c CONTAINS 2 AND c CONTAINS 3"),
row(21, 22, list(2, 3), 24));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE b > 12 AND c CONTAINS 2 AND d < 27"),
row(21, 22, list(2, 3), 24));
});
// with frozen in value
createTable("CREATE TABLE %s (a int, b int, c int, d frozen<list<int>>, PRIMARY KEY (a, b, c)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 11, 12, 13, list(1, 4));
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 21, 22, 23, list(2, 4));
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 21, 25, 25, list(2, 6));
execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 31, 32, 34, list(3, 4));
beforeAndAfterFlush(() -> {
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE d CONTAINS 2"),
row(21, 22, 23, list(2, 4)),
row(21, 25, 25, list(2, 6)));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE d CONTAINS 2 AND b < 25"),
row(21, 22, 23, list(2, 4)));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE d CONTAINS 2 AND d CONTAINS 4"),
row(21, 22, 23, list(2, 4)));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE b > 12 AND c < 25 AND d CONTAINS 2"),
row(21, 22, 23, list(2, 4)));
});
}
private UntypedResultSet executeFilteringOnly(String statement) throws Throwable
{
assertInvalid(statement);
return execute(statement + " ALLOW FILTERING");
}
@Test
public void testFilteringWithCounters() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, c int, cnt counter, PRIMARY KEY (a, b, c))" + compactOption);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 14L, 11, 12, 13);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 24L, 21, 22, 23);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 27L, 21, 25, 26);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 34L, 31, 32, 33);
execute("UPDATE %s SET cnt = cnt + ? WHERE a = ? AND b = ? AND c = ?", 24L, 41, 42, 43);
beforeAndAfterFlush(() -> {
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE cnt = 24"),
row(21, 22, 23, 24L),
row(41, 42, 43, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE b > 22 AND cnt = 24"),
row(41, 42, 43, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE b > 10 AND b < 25 AND cnt = 24"),
row(21, 22, 23, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE b > 10 AND c < 25 AND cnt = 24"),
row(21, 22, 23, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE a = 21 AND b > 10 AND cnt > 23 ORDER BY b DESC"),
row(21, 25, 26, 27L),
row(21, 22, 23, 24L));
assertRows(executeFilteringOnly("SELECT * FROM %s WHERE cnt > 20 AND cnt < 30"),
row(21, 22, 23, 24L),
row(21, 25, 26, 27L),
row(41, 42, 43, 24L));
});
}
/**
* Check select with and without compact storage, with different column
* order. See CASSANDRA-10988
*/
@Test
public void testClusteringOrderWithSlice() throws Throwable
{
final String compactOption = " WITH COMPACT STORAGE AND";
// non-compound, ASC order
createTable("CREATE TABLE %s (a text, b int, PRIMARY KEY (a, b)) " +
compactOption +
" CLUSTERING ORDER BY (b ASC)");
execute("INSERT INTO %s (a, b) VALUES ('a', 2)");
execute("INSERT INTO %s (a, b) VALUES ('a', 3)");
assertRows(execute("SELECT * FROM %s WHERE a = 'a' AND b > 0"),
row("a", 2),
row("a", 3));
assertRows(execute("SELECT * FROM %s WHERE a = 'a' AND b > 0 ORDER BY b DESC"),
row("a", 3),
row("a", 2));
// non-compound, DESC order
createTable("CREATE TABLE %s (a text, b int, PRIMARY KEY (a, b))" +
compactOption +
" CLUSTERING ORDER BY (b DESC)");
execute("INSERT INTO %s (a, b) VALUES ('a', 2)");
execute("INSERT INTO %s (a, b) VALUES ('a', 3)");
assertRows(execute("SELECT * FROM %s WHERE a = 'a' AND b > 0"),
row("a", 3),
row("a", 2));
assertRows(execute("SELECT * FROM %s WHERE a = 'a' AND b > 0 ORDER BY b ASC"),
row("a", 2),
row("a", 3));
// compound, first column DESC order
createTable("CREATE TABLE %s (a text, b int, c int, PRIMARY KEY (a, b, c)) " +
compactOption +
" CLUSTERING ORDER BY (b DESC)"
);
execute("INSERT INTO %s (a, b, c) VALUES ('a', 2, 4)");
execute("INSERT INTO %s (a, b, c) VALUES ('a', 3, 5)");
assertRows(execute("SELECT * FROM %s WHERE a = 'a' AND b > 0"),
row("a", 3, 5),
row("a", 2, 4));
assertRows(execute("SELECT * FROM %s WHERE a = 'a' AND b > 0 ORDER BY b ASC"),
row("a", 2, 4),
row("a", 3, 5));
// compound, mixed order
createTable("CREATE TABLE %s (a text, b int, c int, PRIMARY KEY (a, b, c)) " +
compactOption +
" CLUSTERING ORDER BY (b ASC, c DESC)"
);
execute("INSERT INTO %s (a, b, c) VALUES ('a', 2, 4)");
execute("INSERT INTO %s (a, b, c) VALUES ('a', 3, 5)");
assertRows(execute("SELECT * FROM %s WHERE a = 'a' AND b > 0"),
row("a", 2, 4),
row("a", 3, 5));
assertRows(execute("SELECT * FROM %s WHERE a = 'a' AND b > 0 ORDER BY b ASC"),
row("a", 2, 4),
row("a", 3, 5));
}
@Test
public void testEmptyRestrictionValue() throws Throwable
{
for (String options : new String[]{ "", " WITH COMPACT STORAGE" })
{
createTable("CREATE TABLE %s (pk blob, c blob, v blob, PRIMARY KEY ((pk), c))" + options);
execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
bytes("foo123"), bytes("1"), bytes("1"));
execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
bytes("foo123"), bytes("2"), bytes("2"));
beforeAndAfterFlush(() -> {
assertInvalidMessage("Key may not be empty", "SELECT * FROM %s WHERE pk = textAsBlob('');");
assertInvalidMessage("Key may not be empty", "SELECT * FROM %s WHERE pk IN (textAsBlob(''), textAsBlob('1'));");
assertInvalidMessage("Key may not be empty",
"INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
EMPTY_BYTE_BUFFER, bytes("2"), bytes("2"));
// Test clustering columns restrictions
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c = textAsBlob('');"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) = (textAsBlob(''));"));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c IN (textAsBlob(''), textAsBlob('1'));"),
row(bytes("foo123"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) IN ((textAsBlob('')), (textAsBlob('1')));"),
row(bytes("foo123"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('');"),
row(bytes("foo123"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) > (textAsBlob(''));"),
row(bytes("foo123"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('');"),
row(bytes("foo123"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) >= (textAsBlob(''));"),
row(bytes("foo123"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("2"), bytes("2")));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('');"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) <= (textAsBlob(''));"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('');"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) < (textAsBlob(''));"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('') AND c < textAsBlob('');"));
});
if (options.contains("COMPACT"))
{
assertInvalidMessage("Invalid empty or null value for column c",
"INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"));
}
else
{
execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"));
beforeAndAfterFlush(() -> {
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c = textAsBlob('');"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) = (textAsBlob(''));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c IN (textAsBlob(''), textAsBlob('1'));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")),
row(bytes("foo123"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) IN ((textAsBlob('')), (textAsBlob('1')));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")),
row(bytes("foo123"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('');"),
row(bytes("foo123"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) > (textAsBlob(''));"),
row(bytes("foo123"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('');"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")),
row(bytes("foo123"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) >= (textAsBlob(''));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")),
row(bytes("foo123"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('');"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) <= (textAsBlob(''));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('');"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) < (textAsBlob(''));"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('') AND c < textAsBlob('');"));
});
}
// Test restrictions on non-primary key value
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('') ALLOW FILTERING;"));
execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
bytes("foo123"), bytes("3"), EMPTY_BYTE_BUFFER);
beforeAndAfterFlush(() -> {
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('') ALLOW FILTERING;"),
row(bytes("foo123"), bytes("3"), EMPTY_BYTE_BUFFER));
});
}
}
@Test
public void testEmptyRestrictionValueWithMultipleClusteringColumns() throws Throwable
{
createTable("CREATE TABLE %s (pk blob, c1 blob, c2 blob, v blob, PRIMARY KEY (pk, c1, c2))" + compactOption);
execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", bytes("foo123"), bytes("1"), bytes("1"), bytes("1"));
execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", bytes("foo123"), bytes("1"), bytes("2"), bytes("2"));
beforeAndAfterFlush(() -> {
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('');"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 = textAsBlob('');"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) = (textAsBlob('1'), textAsBlob(''));"));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 IN (textAsBlob(''), textAsBlob('1')) AND c2 = textAsBlob('1');"),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 IN (textAsBlob(''), textAsBlob('1'));"),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) IN ((textAsBlob(''), textAsBlob('1')), (textAsBlob('1'), textAsBlob('1')));"),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 > textAsBlob('');"),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 > textAsBlob('');"),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) > (textAsBlob(''), textAsBlob('1'));"),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 >= textAsBlob('');"),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 <= textAsBlob('');"));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) <= (textAsBlob('1'), textAsBlob(''));"));
});
execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)",
bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"));
beforeAndAfterFlush(() -> {
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('');"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('') AND c2 = textAsBlob('1');"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) = (textAsBlob(''), textAsBlob('1'));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 IN (textAsBlob(''), textAsBlob('1')) AND c2 = textAsBlob('1');"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) IN ((textAsBlob(''), textAsBlob('1')), (textAsBlob('1'), textAsBlob('1')));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) > (textAsBlob(''), textAsBlob('1'));"),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) >= (textAsBlob(''), textAsBlob('1'));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) <= (textAsBlob(''), textAsBlob('1'));"),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) < (textAsBlob(''), textAsBlob('1'));"));
});
}
@Test
public void testEmptyRestrictionValueWithOrderBy() throws Throwable
{
for (String options : new String[]{ " WITH COMPACT STORAGE",
" WITH COMPACT STORAGE AND CLUSTERING ORDER BY (c DESC)" })
{
String orderingClause = options.contains("ORDER") ? "" : "ORDER BY c DESC";
createTable("CREATE TABLE %s (pk blob, c blob, v blob, PRIMARY KEY ((pk), c))" + options);
execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
bytes("foo123"),
bytes("1"),
bytes("1"));
execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
bytes("foo123"),
bytes("2"),
bytes("2"));
beforeAndAfterFlush(() -> {
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('')" + orderingClause),
row(bytes("foo123"), bytes("2"), bytes("2")),
row(bytes("foo123"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('')" + orderingClause),
row(bytes("foo123"), bytes("2"), bytes("2")),
row(bytes("foo123"), bytes("1"), bytes("1")));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('')" + orderingClause));
assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('')" + orderingClause));
});
assertInvalidMessage("Invalid empty or null value for column c",
"INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)",
bytes("foo123"),
EMPTY_BYTE_BUFFER,
bytes("4"));
}
}
@Test
public void testEmptyRestrictionValueWithMultipleClusteringColumnsAndOrderBy() throws Throwable
{
for (String options : new String[]{ " WITH COMPACT STORAGE",
" WITH COMPACT STORAGE AND CLUSTERING ORDER BY (c1 DESC, c2 DESC)" })
{
String orderingClause = options.contains("ORDER") ? "" : "ORDER BY c1 DESC, c2 DESC";
createTable("CREATE TABLE %s (pk blob, c1 blob, c2 blob, v blob, PRIMARY KEY (pk, c1, c2))" + options);
execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", bytes("foo123"), bytes("1"), bytes("1"), bytes("1"));
execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", bytes("foo123"), bytes("1"), bytes("2"), bytes("2"));
beforeAndAfterFlush(() -> {
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 > textAsBlob('')" + orderingClause),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 > textAsBlob('')" + orderingClause),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) > (textAsBlob(''), textAsBlob('1'))" + orderingClause),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 >= textAsBlob('')" + orderingClause),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
});
execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)",
bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"));
beforeAndAfterFlush(() -> {
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 IN (textAsBlob(''), textAsBlob('1')) AND c2 = textAsBlob('1')" + orderingClause),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) IN ((textAsBlob(''), textAsBlob('1')), (textAsBlob('1'), textAsBlob('1')))" + orderingClause),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) > (textAsBlob(''), textAsBlob('1'))" + orderingClause),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")));
assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) >= (textAsBlob(''), textAsBlob('1'))" + orderingClause),
row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")),
row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")),
row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")));
});
}
}
/**
* UpdateTest
*/
@Test
public void testUpdate() throws Throwable
{
testUpdate(false);
testUpdate(true);
}
private void testUpdate(boolean forceFlush) throws Throwable
{
createTable("CREATE TABLE %s (partitionKey int," +
"clustering_1 int," +
"value int," +
" PRIMARY KEY (partitionKey, clustering_1))" + compactOption);
execute("INSERT INTO %s (partitionKey, clustering_1, value) VALUES (0, 0, 0)");
execute("INSERT INTO %s (partitionKey, clustering_1, value) VALUES (0, 1, 1)");
execute("INSERT INTO %s (partitionKey, clustering_1, value) VALUES (0, 2, 2)");
execute("INSERT INTO %s (partitionKey, clustering_1, value) VALUES (0, 3, 3)");
execute("INSERT INTO %s (partitionKey, clustering_1, value) VALUES (1, 0, 4)");
flush(forceFlush);
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ?", 7, 0, 1);
flush(forceFlush);
assertRows(execute("SELECT value FROM %s WHERE partitionKey = ? AND clustering_1 = ?",
0, 1),
row(7));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND (clustering_1) = (?)", 8, 0, 2);
flush(forceFlush);
assertRows(execute("SELECT value FROM %s WHERE partitionKey = ? AND clustering_1 = ?",
0, 2),
row(8));
execute("UPDATE %s SET value = ? WHERE partitionKey IN (?, ?) AND clustering_1 = ?", 9, 0, 1, 0);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?) AND clustering_1 = ?",
0, 1, 0),
row(0, 0, 9),
row(1, 0, 9));
execute("UPDATE %s SET value = ? WHERE partitionKey IN ? AND clustering_1 = ?", 19, Arrays.asList(0, 1), 0);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN ? AND clustering_1 = ?",
Arrays.asList(0, 1), 0),
row(0, 0, 19),
row(1, 0, 19));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 IN (?, ?)", 10, 0, 1, 0);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 IN (?, ?)",
0, 1, 0),
row(0, 0, 10),
row(0, 1, 10));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND (clustering_1) IN ((?), (?))", 20, 0, 0, 1);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND (clustering_1) IN ((?), (?))",
0, 0, 1),
row(0, 0, 20),
row(0, 1, 20));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ?", null, 0, 0);
flush(forceFlush);
if (isEmpty(compactOption))
{
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND (clustering_1) IN ((?), (?))",
0, 0, 1),
row(0, 0, null),
row(0, 1, 20));
}
else
{
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND (clustering_1) IN ((?), (?))",
0, 0, 1),
row(0, 1, 20));
}
// test invalid queries
// missing primary key element
assertInvalidMessage("Some partition key parts are missing: partitionkey",
"UPDATE %s SET value = ? WHERE clustering_1 = ? ", 7, 1);
assertInvalidMessage("Some clustering keys are missing: clustering_1",
"UPDATE %s SET value = ? WHERE partitionKey = ?", 7, 0);
assertInvalidMessage("Some clustering keys are missing: clustering_1",
"UPDATE %s SET value = ? WHERE partitionKey = ?", 7, 0);
// token function
assertInvalidMessage("The token function cannot be used in WHERE clauses for UPDATE statements",
"UPDATE %s SET value = ? WHERE token(partitionKey) = token(?) AND clustering_1 = ?",
7, 0, 1);
// multiple time the same value
assertInvalidSyntax("UPDATE %s SET value = ?, value = ? WHERE partitionKey = ? AND clustering_1 = ?", 7, 0, 1);
// multiple time same primary key element in WHERE clause
assertInvalidMessage("clustering_1 cannot be restricted by more than one relation if it includes an Equal",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_1 = ?", 7, 0, 1, 1);
// Undefined column names
assertInvalidMessage("Undefined column name value1",
"UPDATE %s SET value1 = ? WHERE partitionKey = ? AND clustering_1 = ?", 7, 0, 1);
assertInvalidMessage("Undefined column name partitionkey1",
"UPDATE %s SET value = ? WHERE partitionKey1 = ? AND clustering_1 = ?", 7, 0, 1);
assertInvalidMessage("Undefined column name clustering_3",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_3 = ?", 7, 0, 1);
// Invalid operator in the where clause
assertInvalidMessage("Only EQ and IN relation are supported on the partition key (unless you use the token() function)",
"UPDATE %s SET value = ? WHERE partitionKey > ? AND clustering_1 = ?", 7, 0, 1);
assertInvalidMessage("Cannot use UPDATE with CONTAINS",
"UPDATE %s SET value = ? WHERE partitionKey CONTAINS ? AND clustering_1 = ?", 7, 0, 1);
assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ? AND value = ?", 7, 0, 1, 3);
assertInvalidMessage("Slice restrictions are not supported on the clustering columns in UPDATE statements",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 > ?", 7, 0, 1);
}
@Test
public void testUpdateWithTwoClusteringColumns() throws Throwable
{
testUpdateWithTwoClusteringColumns(false);
testUpdateWithTwoClusteringColumns(true);
}
private void testUpdateWithTwoClusteringColumns(boolean forceFlush) throws Throwable
{
createTable("CREATE TABLE %s (partitionKey int," +
"clustering_1 int," +
"clustering_2 int," +
"value int," +
" PRIMARY KEY (partitionKey, clustering_1, clustering_2))" + compactOption);
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 0, 0)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 1, 1)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 2, 2)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 3, 3)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 1, 1, 4)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 1, 2, 5)");
execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (1, 0, 0, 6)");
flush(forceFlush);
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 7, 0, 1, 1);
flush(forceFlush);
assertRows(execute("SELECT value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?",
0, 1, 1),
row(7));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND (clustering_1, clustering_2) = (?, ?)", 8, 0, 1, 2);
flush(forceFlush);
assertRows(execute("SELECT value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?",
0, 1, 2),
row(8));
execute("UPDATE %s SET value = ? WHERE partitionKey IN (?, ?) AND clustering_1 = ? AND clustering_2 = ?", 9, 0, 1, 0, 0);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?) AND clustering_1 = ? AND clustering_2 = ?",
0, 1, 0, 0),
row(0, 0, 0, 9),
row(1, 0, 0, 9));
execute("UPDATE %s SET value = ? WHERE partitionKey IN ? AND clustering_1 = ? AND clustering_2 = ?", 9, Arrays.asList(0, 1), 0, 0);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey IN ? AND clustering_1 = ? AND clustering_2 = ?",
Arrays.asList(0, 1), 0, 0),
row(0, 0, 0, 9),
row(1, 0, 0, 9));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 IN (?, ?)", 12, 0, 1, 1, 2);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 IN (?, ?)",
0, 1, 1, 2),
row(0, 1, 1, 12),
row(0, 1, 2, 12));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 IN (?, ?) AND clustering_2 IN (?, ?)", 10, 0, 1, 0, 1, 2);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 IN (?, ?) AND clustering_2 IN (?, ?)",
0, 1, 0, 1, 2),
row(0, 0, 1, 10),
row(0, 0, 2, 10),
row(0, 1, 1, 10),
row(0, 1, 2, 10));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND (clustering_1, clustering_2) IN ((?, ?), (?, ?))", 20, 0, 0, 2, 1, 2);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) IN ((?, ?), (?, ?))",
0, 0, 2, 1, 2),
row(0, 0, 2, 20),
row(0, 1, 2, 20));
execute("UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", null, 0, 0, 2);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) IN ((?, ?), (?, ?))",
0, 0, 2, 1, 2),
row(0, 1, 2, 20));
// test invalid queries
// missing primary key element
assertInvalidMessage("Some partition key parts are missing: partitionkey",
"UPDATE %s SET value = ? WHERE clustering_1 = ? AND clustering_2 = ?", 7, 1, 1);
String errorMsg = "PRIMARY KEY column \"clustering_2\" cannot be restricted as preceding column \"clustering_1\" is not restricted";
assertInvalidMessage(errorMsg,
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_2 = ?", 7, 0, 1);
assertInvalidMessage("Some clustering keys are missing: clustering_1, clustering_2",
"UPDATE %s SET value = ? WHERE partitionKey = ?", 7, 0);
// token function
assertInvalidMessage("The token function cannot be used in WHERE clauses for UPDATE statements",
"UPDATE %s SET value = ? WHERE token(partitionKey) = token(?) AND clustering_1 = ? AND clustering_2 = ?",
7, 0, 1, 1);
// multiple time the same value
assertInvalidSyntax("UPDATE %s SET value = ?, value = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 7, 0, 1, 1);
// multiple time same primary key element in WHERE clause
assertInvalidMessage("clustering_1 cannot be restricted by more than one relation if it includes an Equal",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ? AND clustering_1 = ?", 7, 0, 1, 1, 1);
// Undefined column names
assertInvalidMessage("Undefined column name value1",
"UPDATE %s SET value1 = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 7, 0, 1, 1);
assertInvalidMessage("Undefined column name partitionkey1",
"UPDATE %s SET value = ? WHERE partitionKey1 = ? AND clustering_1 = ? AND clustering_2 = ?", 7, 0, 1, 1);
assertInvalidMessage("Undefined column name clustering_3",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_3 = ?", 7, 0, 1, 1);
// Invalid operator in the where clause
assertInvalidMessage("Only EQ and IN relation are supported on the partition key (unless you use the token() function)",
"UPDATE %s SET value = ? WHERE partitionKey > ? AND clustering_1 = ? AND clustering_2 = ?", 7, 0, 1, 1);
assertInvalidMessage("Cannot use UPDATE with CONTAINS",
"UPDATE %s SET value = ? WHERE partitionKey CONTAINS ? AND clustering_1 = ? AND clustering_2 = ?", 7, 0, 1, 1);
assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ? AND value = ?", 7, 0, 1, 1, 3);
assertInvalidMessage("Slice restrictions are not supported on the clustering columns in UPDATE statements",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND clustering_1 > ?", 7, 0, 1);
assertInvalidMessage("Slice restrictions are not supported on the clustering columns in UPDATE statements",
"UPDATE %s SET value = ? WHERE partitionKey = ? AND (clustering_1, clustering_2) > (?, ?)", 7, 0, 1, 1);
}
@Test
public void testUpdateWithMultiplePartitionKeyComponents() throws Throwable
{
testUpdateWithMultiplePartitionKeyComponents(false);
testUpdateWithMultiplePartitionKeyComponents(true);
}
public void testUpdateWithMultiplePartitionKeyComponents(boolean forceFlush) throws Throwable
{
createTable("CREATE TABLE %s (partitionKey_1 int," +
"partitionKey_2 int," +
"clustering_1 int," +
"clustering_2 int," +
"value int," +
" PRIMARY KEY ((partitionKey_1, partitionKey_2), clustering_1, clustering_2))" + compactOption);
execute("INSERT INTO %s (partitionKey_1, partitionKey_2, clustering_1, clustering_2, value) VALUES (0, 0, 0, 0, 0)");
execute("INSERT INTO %s (partitionKey_1, partitionKey_2, clustering_1, clustering_2, value) VALUES (0, 1, 0, 1, 1)");
execute("INSERT INTO %s (partitionKey_1, partitionKey_2, clustering_1, clustering_2, value) VALUES (0, 1, 1, 1, 2)");
execute("INSERT INTO %s (partitionKey_1, partitionKey_2, clustering_1, clustering_2, value) VALUES (1, 0, 0, 1, 3)");
execute("INSERT INTO %s (partitionKey_1, partitionKey_2, clustering_1, clustering_2, value) VALUES (1, 1, 0, 1, 3)");
flush(forceFlush);
execute("UPDATE %s SET value = ? WHERE partitionKey_1 = ? AND partitionKey_2 = ? AND clustering_1 = ? AND clustering_2 = ?", 7, 0, 0, 0, 0);
flush(forceFlush);
assertRows(execute("SELECT value FROM %s WHERE partitionKey_1 = ? AND partitionKey_2 = ? AND clustering_1 = ? AND clustering_2 = ?",
0, 0, 0, 0),
row(7));
execute("UPDATE %s SET value = ? WHERE partitionKey_1 IN (?, ?) AND partitionKey_2 = ? AND clustering_1 = ? AND clustering_2 = ?", 9, 0, 1, 1, 0, 1);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s WHERE partitionKey_1 IN (?, ?) AND partitionKey_2 = ? AND clustering_1 = ? AND clustering_2 = ?",
0, 1, 1, 0, 1),
row(0, 1, 0, 1, 9),
row(1, 1, 0, 1, 9));
execute("UPDATE %s SET value = ? WHERE partitionKey_1 IN (?, ?) AND partitionKey_2 IN (?, ?) AND clustering_1 = ? AND clustering_2 = ?", 10, 0, 1, 0, 1, 0, 1);
flush(forceFlush);
assertRows(execute("SELECT * FROM %s"),
row(0, 0, 0, 0, 7),
row(0, 0, 0, 1, 10),
row(0, 1, 0, 1, 10),
row(0, 1, 1, 1, 2),
row(1, 0, 0, 1, 10),
row(1, 1, 0, 1, 10));
// missing primary key element
assertInvalidMessage("Some partition key parts are missing: partitionkey_2",
"UPDATE %s SET value = ? WHERE partitionKey_1 = ? AND clustering_1 = ? AND clustering_2 = ?", 7, 1, 1);
}
@Test
public void testCfmCompactStorageCQL()
{
String keyspace = "cql_test_keyspace_compact";
String table = "test_table_compact";
TableMetadata.Builder metadata =
TableMetadata.builder(keyspace, table)
.flags(EnumSet.of(TableMetadata.Flag.DENSE))
.addPartitionKeyColumn("pk1", IntegerType.instance)
.addPartitionKeyColumn("pk2", AsciiType.instance)
.addClusteringColumn("ck1", ReversedType.getInstance(IntegerType.instance))
.addClusteringColumn("ck2", IntegerType.instance)
.addRegularColumn("reg", IntegerType.instance);
SchemaLoader.createKeyspace(keyspace, KeyspaceParams.simple(1), metadata);
ColumnFamilyStore cfs = Keyspace.open(keyspace).getColumnFamilyStore(table);
String actual = SchemaCQLHelper.getTableMetadataAsCQL(cfs.metadata(), true, true, true);
String expected = "CREATE TABLE IF NOT EXISTS cql_test_keyspace_compact.test_table_compact (\n" +
" pk1 varint,\n" +
" pk2 ascii,\n" +
" ck1 varint,\n" +
" ck2 varint,\n" +
" reg varint,\n" +
" PRIMARY KEY ((pk1, pk2), ck1, ck2)\n" +
") WITH COMPACT STORAGE\n" +
" AND ID = " + cfs.metadata.id + "\n" +
" AND CLUSTERING ORDER BY (ck1 DESC, ck2 ASC)";
assertTrue(String.format("Expected\n%s\nto contain\n%s", actual, expected),
actual.contains(expected));
}
@Test
public void testCfmCounterCQL()
{
String keyspace = "cql_test_keyspace_counter";
String table = "test_table_counter";
TableMetadata.Builder metadata;
metadata = TableMetadata.builder(keyspace, table)
.flags(EnumSet.of(TableMetadata.Flag.DENSE,
TableMetadata.Flag.COUNTER))
.isCounter(true)
.addPartitionKeyColumn("pk1", IntegerType.instance)
.addPartitionKeyColumn("pk2", AsciiType.instance)
.addClusteringColumn("ck1", ReversedType.getInstance(IntegerType.instance))
.addClusteringColumn("ck2", IntegerType.instance)
.addRegularColumn("cnt", CounterColumnType.instance);
SchemaLoader.createKeyspace(keyspace, KeyspaceParams.simple(1), metadata);
ColumnFamilyStore cfs = Keyspace.open(keyspace).getColumnFamilyStore(table);
String actual = SchemaCQLHelper.getTableMetadataAsCQL(cfs.metadata(), true, true, true);
String expected = "CREATE TABLE IF NOT EXISTS cql_test_keyspace_counter.test_table_counter (\n" +
" pk1 varint,\n" +
" pk2 ascii,\n" +
" ck1 varint,\n" +
" ck2 varint,\n" +
" cnt counter,\n" +
" PRIMARY KEY ((pk1, pk2), ck1, ck2)\n" +
") WITH COMPACT STORAGE\n" +
" AND ID = " + cfs.metadata.id + "\n" +
" AND CLUSTERING ORDER BY (ck1 DESC, ck2 ASC)";
assertTrue(String.format("Expected\n%s\nto contain\n%s", actual, expected),
actual.contains(expected));
}
@Test
public void testDenseTable() throws Throwable
{
String tableName = createTable("CREATE TABLE IF NOT EXISTS %s (" +
"pk1 varint PRIMARY KEY," +
"reg1 int)" +
" WITH COMPACT STORAGE");
ColumnFamilyStore cfs = Keyspace.open(keyspace()).getColumnFamilyStore(tableName);
String actual = SchemaCQLHelper.getTableMetadataAsCQL(cfs.metadata(), true, true, true);
String expected = "CREATE TABLE IF NOT EXISTS " + keyspace() + "." + tableName + " (\n" +
" pk1 varint,\n" +
" reg1 int,\n" +
" PRIMARY KEY (pk1)\n" +
") WITH COMPACT STORAGE\n" +
" AND ID = " + cfs.metadata.id + "\n";
assertTrue(String.format("Expected\n%s\nto contain\n%s", actual, expected),
actual.contains(expected));
}
@Test
public void testStaticCompactTable()
{
String tableName = createTable("CREATE TABLE IF NOT EXISTS %s (" +
"pk1 varint PRIMARY KEY," +
"reg1 int," +
"reg2 int)" +
" WITH COMPACT STORAGE");
ColumnFamilyStore cfs = Keyspace.open(keyspace()).getColumnFamilyStore(tableName);
assertTrue(SchemaCQLHelper.getTableMetadataAsCQL(cfs.metadata(), true, true, true).contains(
"CREATE TABLE IF NOT EXISTS " + keyspace() + "." + tableName + " (\n" +
" pk1 varint,\n" +
" reg1 int,\n" +
" reg2 int,\n" +
" PRIMARY KEY (pk1)\n" +
") WITH COMPACT STORAGE\n" +
" AND ID = " + cfs.metadata.id));
}
@Test
public void testStaticCompactWithCounters()
{
String tableName = createTable("CREATE TABLE IF NOT EXISTS %s (" +
"pk1 varint PRIMARY KEY," +
"reg1 counter," +
"reg2 counter)" +
" WITH COMPACT STORAGE");
ColumnFamilyStore cfs = Keyspace.open(keyspace()).getColumnFamilyStore(tableName);
String actual = SchemaCQLHelper.getTableMetadataAsCQL(cfs.metadata(), true, true, true);
String expected = "CREATE TABLE IF NOT EXISTS " + keyspace() + "." + tableName + " (\n" +
" pk1 varint,\n" +
" reg1 counter,\n" +
" reg2 counter,\n" +
" PRIMARY KEY (pk1)\n" +
") WITH COMPACT STORAGE\n" +
" AND ID = " + cfs.metadata.id + "\n";
assertTrue(String.format("Expected\n%s\nto contain\n%s", actual, expected),
actual.contains(expected));
}
@Test
public void testDenseCompactTableWithoutRegulars() throws Throwable
{
String tableName = createTable("CREATE TABLE IF NOT EXISTS %s (" +
"pk1 varint," +
"ck1 int," +
"PRIMARY KEY (pk1, ck1))" +
" WITH COMPACT STORAGE");
ColumnFamilyStore cfs = Keyspace.open(keyspace()).getColumnFamilyStore(tableName);
String actual = SchemaCQLHelper.getTableMetadataAsCQL(cfs.metadata(), true, true, true);
String expected = "CREATE TABLE IF NOT EXISTS " + keyspace() + "." + tableName + " (\n" +
" pk1 varint,\n" +
" ck1 int,\n" +
" PRIMARY KEY (pk1, ck1)\n" +
") WITH COMPACT STORAGE\n" +
" AND ID = " + cfs.metadata.id;
assertTrue(String.format("Expected\n%s\nto contain\n%s", actual, expected),
actual.contains(expected));
}
@Test
public void testCompactDynamic() throws Throwable
{
String tableName = createTable("CREATE TABLE IF NOT EXISTS %s (" +
"pk1 varint," +
"ck1 int," +
"reg int," +
"PRIMARY KEY (pk1, ck1))" +
" WITH COMPACT STORAGE");
ColumnFamilyStore cfs = Keyspace.open(keyspace()).getColumnFamilyStore(tableName);
String actual = SchemaCQLHelper.getTableMetadataAsCQL(cfs.metadata(), true, true, true);
String expected = "CREATE TABLE IF NOT EXISTS " + keyspace() + "." + tableName + " (\n" +
" pk1 varint,\n" +
" ck1 int,\n" +
" reg int,\n" +
" PRIMARY KEY (pk1, ck1)\n" +
") WITH COMPACT STORAGE\n" +
" AND ID = " + cfs.metadata.id;
assertTrue(String.format("Expected\n%s\nto contain\n%s", actual, expected),
actual.contains(expected));
}
/**
* PartitionUpdateTest
*/
@Test
public void testOperationCountWithCompactTable()
{
createTable("CREATE TABLE %s (key text PRIMARY KEY, a int) WITH COMPACT STORAGE");
TableMetadata cfm = currentTableMetadata();
PartitionUpdate update = new RowUpdateBuilder(cfm, FBUtilities.timestampMicros(), "key0").add("a", 1)
.buildUpdate();
Assert.assertEquals(1, update.operationCount());
update = new RowUpdateBuilder(cfm, FBUtilities.timestampMicros(), "key0").buildUpdate();
Assert.assertEquals(0, update.operationCount());
}
/**
* AlterTest
*/
/**
* Test for CASSANDRA-13917
*/
@Test
public void testAlterWithCompactStaticFormat() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int) WITH COMPACT STORAGE");
assertInvalidMessage("Undefined column name column1 in table",
"ALTER TABLE %s RENAME column1 TO column2");
}
/**
* Test for CASSANDRA-13917
*/
@Test
public void testAlterWithCompactNonStaticFormat() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
assertInvalidMessage("Undefined column name column1 in table",
"ALTER TABLE %s RENAME column1 TO column2");
createTable("CREATE TABLE %s (a int, b int, v int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
assertInvalidMessage("Undefined column name column1 in table",
"ALTER TABLE %s RENAME column1 TO column2");
}
/**
* CreateTest
*/
/**
* Test for CASSANDRA-13917
*/
@Test
public void testCreateIndextWithCompactStaticFormat() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int) WITH COMPACT STORAGE");
assertInvalidMessage("Undefined column name column1 in table",
"CREATE INDEX column1_index on %s (column1)");
assertInvalidMessage("Undefined column name value in table",
"CREATE INDEX value_index on %s (value)");
}
/**
* DeleteTest
*/
/**
* Test for CASSANDRA-13917
*/
@Test
public void testDeleteWithCompactStaticFormat() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int) WITH COMPACT STORAGE");
testDeleteWithCompactFormat();
// if column1 is present, hidden column is called column2
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int, column1 int) WITH COMPACT STORAGE");
assertInvalidMessage("Undefined column name column2 in table",
"DELETE FROM %s WHERE a = 1 AND column2= 1");
assertInvalidMessage("Undefined column name column2 in table",
"DELETE FROM %s WHERE a = 1 AND column2 = 1 AND value1 = 1");
assertInvalidMessage("Undefined column name column2",
"DELETE column2 FROM %s WHERE a = 1");
// if value is present, hidden column is called value1
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int, value int) WITH COMPACT STORAGE");
assertInvalidMessage("Undefined column name value1 in table",
"DELETE FROM %s WHERE a = 1 AND value1 = 1");
assertInvalidMessage("Undefined column name value1 in table",
"DELETE FROM %s WHERE a = 1 AND value1 = 1 AND column1 = 1");
assertInvalidMessage("Undefined column name value1",
"DELETE value1 FROM %s WHERE a = 1");
}
/**
* Test for CASSANDRA-13917
*/
@Test
public void testDeleteWithCompactNonStaticFormat() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b) VALUES (1, 1)");
execute("INSERT INTO %s (a, b) VALUES (2, 1)");
assertRows(execute("SELECT a, b FROM %s"),
row(1, 1),
row(2, 1));
testDeleteWithCompactFormat();
createTable("CREATE TABLE %s (a int, b int, v int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, v) VALUES (1, 1, 3)");
execute("INSERT INTO %s (a, b, v) VALUES (2, 1, 4)");
assertRows(execute("SELECT a, b, v FROM %s"),
row(1, 1, 3),
row(2, 1, 4));
testDeleteWithCompactFormat();
}
private void testDeleteWithCompactFormat() throws Throwable
{
assertInvalidMessage("Undefined column name value in table",
"DELETE FROM %s WHERE a = 1 AND value = 1");
assertInvalidMessage("Undefined column name column1 in table",
"DELETE FROM %s WHERE a = 1 AND column1= 1");
assertInvalidMessage("Undefined column name value in table",
"DELETE FROM %s WHERE a = 1 AND value = 1 AND column1 = 1");
assertInvalidMessage("Undefined column name value",
"DELETE value FROM %s WHERE a = 1");
assertInvalidMessage("Undefined column name column1",
"DELETE column1 FROM %s WHERE a = 1");
}
/**
* InsertTest
*/
/**
* Test for CASSANDRA-13917
*/
@Test
public void testInsertWithCompactStaticFormat() throws Throwable
{
testInsertWithCompactTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int) WITH COMPACT STORAGE");
// if column1 is present, hidden column is called column2
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int, column1 int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, column1) VALUES (1, 1, 1, 1)");
assertInvalidMessage("Undefined column name column2",
"INSERT INTO %s (a, b, c, column2) VALUES (1, 1, 1, 1)");
// if value is present, hidden column is called value1
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int, value int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, value) VALUES (1, 1, 1, 1)");
assertInvalidMessage("Undefined column name value1",
"INSERT INTO %s (a, b, c, value1) VALUES (1, 1, 1, 1)");
}
/**
* Test for CASSANDRA-13917
*/
@Test
public void testInsertWithCompactNonStaticFormat() throws Throwable
{
testInsertWithCompactTable("CREATE TABLE %s (a int, b int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
testInsertWithCompactTable("CREATE TABLE %s (a int, b int, v int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
}
private void testInsertWithCompactTable(String tableQuery) throws Throwable
{
createTable(tableQuery);
// pass correct types to the hidden columns
assertInvalidMessage("Undefined column name column1",
"INSERT INTO %s (a, b, column1) VALUES (?, ?, ?)",
1, 1, 1, ByteBufferUtil.bytes('a'));
assertInvalidMessage("Undefined column name value",
"INSERT INTO %s (a, b, value) VALUES (?, ?, ?)",
1, 1, 1, ByteBufferUtil.bytes('a'));
assertInvalidMessage("Undefined column name column1",
"INSERT INTO %s (a, b, column1, value) VALUES (?, ?, ?, ?)",
1, 1, 1, ByteBufferUtil.bytes('a'), ByteBufferUtil.bytes('b'));
assertInvalidMessage("Undefined column name value",
"INSERT INTO %s (a, b, value, column1) VALUES (?, ?, ?, ?)",
1, 1, 1, ByteBufferUtil.bytes('a'), ByteBufferUtil.bytes('b'));
// pass incorrect types to the hidden columns
assertInvalidMessage("Undefined column name value",
"INSERT INTO %s (a, b, value) VALUES (?, ?, ?)",
1, 1, 1, 1);
assertInvalidMessage("Undefined column name column1",
"INSERT INTO %s (a, b, column1) VALUES (?, ?, ?)",
1, 1, 1, 1);
assertEmpty(execute("SELECT * FROM %s"));
// pass null to the hidden columns
assertInvalidMessage("Undefined column name value",
"INSERT INTO %s (a, b, value) VALUES (?, ?, ?)",
1, 1, null);
assertInvalidMessage("Undefined column name column1",
"INSERT INTO %s (a, b, column1) VALUES (?, ?, ?)",
1, 1, null);
}
/**
* SelectTest
*/
/**
* Test for CASSANDRA-13917
*/
@Test
public void testSelectWithCompactStaticFormat() throws Throwable
{
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c) VALUES (1, 1, 1)");
execute("INSERT INTO %s (a, b, c) VALUES (2, 1, 1)");
assertRows(execute("SELECT a, b, c FROM %s"),
row(1, 1, 1),
row(2, 1, 1));
testSelectWithCompactFormat();
// if column column1 is present, hidden column is called column2
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int, column1 int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, column1) VALUES (1, 1, 1, 1)");
execute("INSERT INTO %s (a, b, c, column1) VALUES (2, 1, 1, 2)");
assertRows(execute("SELECT a, b, c, column1 FROM %s"),
row(1, 1, 1, 1),
row(2, 1, 1, 2));
assertInvalidMessage("Undefined column name column2 in table",
"SELECT a, column2, value FROM %s");
// if column value is present, hidden column is called value1
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int, value int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, value) VALUES (1, 1, 1, 1)");
execute("INSERT INTO %s (a, b, c, value) VALUES (2, 1, 1, 2)");
assertRows(execute("SELECT a, b, c, value FROM %s"),
row(1, 1, 1, 1),
row(2, 1, 1, 2));
assertInvalidMessage("Undefined column name value1 in table",
"SELECT a, value1, value FROM %s");
}
/**
* Test for CASSANDRA-13917
*/
@Test
public void testSelectWithCompactNonStaticFormat() throws Throwable
{
createTable("CREATE TABLE %s (a int, b int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b) VALUES (1, 1)");
execute("INSERT INTO %s (a, b) VALUES (2, 1)");
assertRows(execute("SELECT a, b FROM %s"),
row(1, 1),
row(2, 1));
testSelectWithCompactFormat();
createTable("CREATE TABLE %s (a int, b int, v int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, v) VALUES (1, 1, 3)");
execute("INSERT INTO %s (a, b, v) VALUES (2, 1, 4)");
assertRows(execute("SELECT a, b, v FROM %s"),
row(1, 1, 3),
row(2, 1, 4));
testSelectWithCompactFormat();
}
private void testSelectWithCompactFormat() throws Throwable
{
assertInvalidMessage("Undefined column name column1 in table",
"SELECT column1 FROM %s");
assertInvalidMessage("Undefined column name value in table",
"SELECT value FROM %s");
assertInvalidMessage("Undefined column name value in table",
"SELECT value, column1 FROM %s");
assertInvalid("Undefined column name column1 in table ('column1 = NULL')",
"SELECT * FROM %s WHERE column1 = null ALLOW FILTERING");
assertInvalid("Undefined column name value in table ('value = NULL')",
"SELECT * FROM %s WHERE value = null ALLOW FILTERING");
assertInvalidMessage("Undefined column name column1 in table",
"SELECT WRITETIME(column1) FROM %s");
assertInvalidMessage("Undefined column name value in table",
"SELECT WRITETIME(value) FROM %s");
}
/**
* UpdateTest
*/
/**
* Test for CASSANDRA-13917
*/
@Test
public void testUpdateWithCompactStaticFormat() throws Throwable
{
testUpdateWithCompactFormat("CREATE TABLE %s (a int PRIMARY KEY, b int, c int) WITH COMPACT STORAGE");
assertInvalidMessage("Undefined column name column1 in table",
"UPDATE %s SET b = 1 WHERE column1 = ?",
ByteBufferUtil.bytes('a'));
assertInvalidMessage("Undefined column name value in table",
"UPDATE %s SET b = 1 WHERE value = ?",
ByteBufferUtil.bytes('a'));
// if column1 is present, hidden column is called column2
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int, column1 int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, column1) VALUES (1, 1, 1, 1)");
execute("UPDATE %s SET column1 = 6 WHERE a = 1");
assertInvalidMessage("Undefined column name column2", "UPDATE %s SET column2 = 6 WHERE a = 0");
assertInvalidMessage("Undefined column name value", "UPDATE %s SET value = 6 WHERE a = 0");
// if value is present, hidden column is called value1
createTable("CREATE TABLE %s (a int PRIMARY KEY, b int, c int, value int) WITH COMPACT STORAGE");
execute("INSERT INTO %s (a, b, c, value) VALUES (1, 1, 1, 1)");
execute("UPDATE %s SET value = 6 WHERE a = 1");
assertInvalidMessage("Undefined column name column1", "UPDATE %s SET column1 = 6 WHERE a = 1");
assertInvalidMessage("Undefined column name value1", "UPDATE %s SET value1 = 6 WHERE a = 1");
}
/**
* Test for CASSANDRA-13917
*/
@Test
public void testUpdateWithCompactNonStaticFormat() throws Throwable
{
testUpdateWithCompactFormat("CREATE TABLE %s (a int, b int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
testUpdateWithCompactFormat("CREATE TABLE %s (a int, b int, v int, PRIMARY KEY (a, b)) WITH COMPACT STORAGE");
}
private void testUpdateWithCompactFormat(String tableQuery) throws Throwable
{
createTable(tableQuery);
// pass correct types to hidden columns
assertInvalidMessage("Undefined column name column1",
"UPDATE %s SET column1 = ? WHERE a = 0",
ByteBufferUtil.bytes('a'));
assertInvalidMessage("Undefined column name value",
"UPDATE %s SET value = ? WHERE a = 0",
ByteBufferUtil.bytes('a'));
// pass incorrect types to hidden columns
assertInvalidMessage("Undefined column name column1", "UPDATE %s SET column1 = 6 WHERE a = 0");
assertInvalidMessage("Undefined column name value", "UPDATE %s SET value = 6 WHERE a = 0");
}
}