Repository: cassandra Updated Branches: refs/heads/cassandra-3.0 41731b84f -> 5a4253b6a
http://git-wip-us.apache.org/repos/asf/cassandra/blob/5a4253b6/test/unit/org/apache/cassandra/cql3/ViewFilteringTest.java ---------------------------------------------------------------------- diff --git a/test/unit/org/apache/cassandra/cql3/ViewFilteringTest.java b/test/unit/org/apache/cassandra/cql3/ViewFilteringTest.java new file mode 100644 index 0000000..2d789c3 --- /dev/null +++ b/test/unit/org/apache/cassandra/cql3/ViewFilteringTest.java @@ -0,0 +1,1292 @@ +/* + * 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; + +import java.util.*; + +import org.junit.After; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; + +import com.datastax.driver.core.exceptions.InvalidQueryException; +import junit.framework.Assert; + +import org.apache.cassandra.db.SystemKeyspace; + +public class ViewFilteringTest extends CQLTester +{ + int protocolVersion = 4; + private final List<String> views = new ArrayList<>(); + + @BeforeClass + public static void startup() + { + requireNetwork(); + } + @Before + public void begin() + { + views.clear(); + } + + @After + public void end() throws Throwable + { + for (String viewName : views) + executeNet(protocolVersion, "DROP MATERIALIZED VIEW " + viewName); + } + + private void createView(String name, String query) throws Throwable + { + executeNet(protocolVersion, String.format(query, name)); + // If exception is thrown, the view will not be added to the list; since it shouldn't have been created, this is + // the desired behavior + views.add(name); + } + + private void dropView(String name) throws Throwable + { + executeNet(protocolVersion, "DROP MATERIALIZED VIEW " + name); + views.remove(name); + } + + @Test + public void testMVCreationSelectRestrictions() throws Throwable + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, PRIMARY KEY((a, b), c, d))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + // IS NOT NULL is required on all PK statements that are not otherwise restricted + List<String> badStatements = Arrays.asList( + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE b IS NOT NULL AND c IS NOT NULL AND d is NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND c IS NOT NULL AND d is NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND b IS NOT NULL AND d is NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND b IS NOT NULL AND c is NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = ? AND b IS NOT NULL AND c is NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = blobAsInt(?) AND b IS NOT NULL AND c is NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s PRIMARY KEY (a, b, c, d)" + ); + + for (String badStatement : badStatements) + { + try + { + createView("mv1_test", badStatement); + Assert.fail("Create MV statement should have failed due to missing IS NOT NULL restriction: " + badStatement); + } + catch (InvalidQueryException exc) {} + } + + List<String> goodStatements = Arrays.asList( + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b = 1 AND c IS NOT NULL AND d is NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND b IS NOT NULL AND c = 1 AND d IS NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND b IS NOT NULL AND c = 1 AND d = 1 PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b = 1 AND c = 1 AND d = 1 PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b = 1 AND c > 1 AND d IS NOT NULL PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b = 1 AND c = 1 AND d IN (1, 2, 3) PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b = 1 AND (c, d) = (1, 1) PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b = 1 AND (c, d) > (1, 1) PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b = 1 AND (c, d) IN ((1, 1), (2, 2)) PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = (int) 1 AND b = 1 AND c = 1 AND d = 1 PRIMARY KEY ((a, b), c, d)", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = blobAsInt(intAsBlob(1)) AND b = 1 AND c = 1 AND d = 1 PRIMARY KEY ((a, b), c, d)" + ); + + for (int i = 0; i < goodStatements.size(); i++) + { + try + { + createView("mv" + i + "_test", goodStatements.get(i)); + } + catch (Exception e) + { + throw new RuntimeException("MV creation failed: " + goodStatements.get(i), e); + } + + try + { + executeNet(protocolVersion, "ALTER MATERIALIZED VIEW mv" + i + "_test WITH compaction = { 'class' : 'LeveledCompactionStrategy' }"); + } + catch (Exception e) + { + throw new RuntimeException("MV alter failed: " + goodStatements.get(i), e); + } + } + + try + { + createView("mv_foo", "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b IS NOT NULL AND c IS NOT NULL AND d is NOT NULL PRIMARY KEY ((a, b), c, d)"); + Assert.fail("Partial partition key restriction should not be allowed"); + } + catch (InvalidQueryException exc) {} + } + + @Test + public void testCaseSensitivity() throws Throwable + { + createTable("CREATE TABLE %s (\"theKey\" int, \"theClustering\" int, \"the\"\"Value\" int, PRIMARY KEY (\"theKey\", \"theClustering\"))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (\"theKey\", \"theClustering\", \"the\"\"Value\") VALUES (?, ?, ?)", 0, 0, 0); + execute("INSERT INTO %s (\"theKey\", \"theClustering\", \"the\"\"Value\") VALUES (?, ?, ?)", 0, 1, 0); + execute("INSERT INTO %s (\"theKey\", \"theClustering\", \"the\"\"Value\") VALUES (?, ?, ?)", 1, 0, 0); + execute("INSERT INTO %s (\"theKey\", \"theClustering\", \"the\"\"Value\") VALUES (?, ?, ?)", 1, 1, 0); + + createView("mv_test", "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s " + + "WHERE \"theKey\" = 1 AND \"theClustering\" = 1 AND \"the\"\"Value\" IS NOT NULL " + + "PRIMARY KEY (\"theKey\", \"theClustering\")"); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test")) + Thread.sleep(10); + createView("mv_test2", "CREATE MATERIALIZED VIEW %s AS SELECT \"theKey\", \"theClustering\", \"the\"\"Value\" FROM %%s " + + "WHERE \"theKey\" = 1 AND \"theClustering\" = 1 AND \"the\"\"Value\" IS NOT NULL " + + "PRIMARY KEY (\"theKey\", \"theClustering\")"); + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test2")) + Thread.sleep(10); + + for (String mvname : Arrays.asList("mv_test", "mv_test2")) + { + assertRowsIgnoringOrder(execute("SELECT \"theKey\", \"theClustering\", \"the\"\"Value\" FROM " + mvname), + row(1, 1, 0) + ); + } + + executeNet(protocolVersion, "ALTER TABLE %s RENAME \"theClustering\" TO \"Col\""); + + for (String mvname : Arrays.asList("mv_test", "mv_test2")) + { + assertRowsIgnoringOrder(execute("SELECT \"theKey\", \"Col\", \"the\"\"Value\" FROM " + mvname), + row(1, 1, 0) + ); + } + } + + @Test + public void testFilterWithFunction() throws Throwable + { + createTable("CREATE TABLE %s (a int, b int, c int, PRIMARY KEY (a, b))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 0, 0); + execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 1, 1); + execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 1, 0, 2); + execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 1, 1, 3); + + createView("mv_test", "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s " + + "WHERE a = blobAsInt(intAsBlob(1)) AND b IS NOT NULL " + + "PRIMARY KEY (a, b)"); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test")) + Thread.sleep(10); + + assertRows(execute("SELECT a, b, c FROM mv_test"), + row(1, 0, 2), + row(1, 1, 3) + ); + + executeNet(protocolVersion, "ALTER TABLE %s RENAME a TO foo"); + + assertRows(execute("SELECT foo, b, c FROM mv_test"), + row(1, 0, 2), + row(1, 1, 3) + ); + } + + @Test + public void testFilterWithTypecast() throws Throwable + { + createTable("CREATE TABLE %s (a int, b int, c int, PRIMARY KEY (a, b))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 0, 0); + execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 1, 1); + execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 1, 0, 2); + execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 1, 1, 3); + + createView("mv_test", "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s " + + "WHERE a = (int) 1 AND b IS NOT NULL " + + "PRIMARY KEY (a, b)"); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test")) + Thread.sleep(10); + + assertRows(execute("SELECT a, b, c FROM mv_test"), + row(1, 0, 2), + row(1, 1, 3) + ); + + executeNet(protocolVersion, "ALTER TABLE %s RENAME a TO foo"); + + assertRows(execute("SELECT foo, b, c FROM mv_test"), + row(1, 0, 2), + row(1, 1, 3) + ); + } + + @Test + public void testPartitionKeyRestrictions() throws Throwable + { + List<String> mvPrimaryKeys = Arrays.asList("((a, b), c)", "((b, a), c)", "(a, b, c)", "(c, b, a)", "((c, a), b)"); + for (int i = 0; i < mvPrimaryKeys.size(); i++) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + + logger.info("Testing MV primary key: {}", mvPrimaryKeys.get(i)); + + // only accept rows where a = 1 + createView("mv_test" + i, "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b IS NOT NULL AND c IS NOT NULL PRIMARY KEY " + mvPrimaryKeys.get(i)); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test" + i)) + Thread.sleep(10); + + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 0, 0), + row(1, 0, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 1, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 0, 0), + row(1, 0, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 2, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 0, 0), + row(1, 0, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 0, 0, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 0, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 0, 0), + row(1, 0, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 0, 0), + row(1, 0, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 0, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 0, 1, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ?", 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 0, 0), + row(1, 0, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 0, 0), + row(1, 0, 1, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ?", 1); + assertEmpty(execute("SELECT * FROM mv_test" + i)); + } + } + + @Test + public void testCompoundPartitionKeyRestrictions() throws Throwable + { + List<String> mvPrimaryKeys = Arrays.asList("((a, b), c)", "((b, a), c)", "(a, b, c)", "(c, b, a)", "((c, a), b)"); + for (int i = 0; i < mvPrimaryKeys.size(); i++) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY ((a, b), c))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + + logger.info("Testing MV primary key: {}", mvPrimaryKeys.get(i)); + + // only accept rows where a = 1 and b = 1 + createView("mv_test" + i, "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b = 1 AND c IS NOT NULL PRIMARY KEY " + mvPrimaryKeys.get(i)); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test" + i)) + Thread.sleep(10); + + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 2, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 2, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 0, 0, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 0, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 0, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 0, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 0, 1, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ?", 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ? AND b = ?", 1, 1); + assertEmpty(execute("SELECT * FROM mv_test" + i)); + } + } + + @Test + public void testCompoundPartitionKeyRestrictionsNotIncludeAll() throws Throwable + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY ((a, b), c))"); + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + + // only accept rows where a = 1 and b = 1, don't include column d in the selection + createView("mv_test", "CREATE MATERIALIZED VIEW %s AS SELECT a, b, c FROM %%s WHERE a = 1 AND b = 1 AND c IS NOT NULL PRIMARY KEY ((a, b), c)"); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test")) + Thread.sleep(10); + + assertRows(execute("SELECT * FROM mv_test"), + row(1, 1, 0), + row(1, 1, 1) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 2, 0, 0); + assertRows(execute("SELECT * FROM mv_test"), + row(1, 1, 0), + row(1, 1, 1) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 2, 0); + assertRows(execute("SELECT * FROM mv_test"), + row(1, 1, 0), + row(1, 1, 1), + row(1, 1, 2) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 0, 0, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 0, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 0, 1, 0); + assertRows(execute("SELECT * FROM mv_test"), + row(1, 1, 0), + row(1, 1, 1), + row(1, 1, 2) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 1, 0); + assertRows(execute("SELECT * FROM mv_test"), + row(1, 1, 0), + row(1, 1, 1), + row(1, 1, 2) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 0, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 0, 1, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ?", 0, 0); + assertRows(execute("SELECT * FROM mv_test"), + row(1, 1, 0), + row(1, 1, 1), + row(1, 1, 2) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 0); + assertRows(execute("SELECT * FROM mv_test"), + row(1, 1, 1), + row(1, 1, 2) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ? AND b = ?", 1, 1); + assertEmpty(execute("SELECT * FROM mv_test")); + } + + @Test + public void testClusteringKeyEQRestrictions() throws Throwable + { + List<String> mvPrimaryKeys = Arrays.asList("((a, b), c)", "((b, a), c)", "(a, b, c)", "(c, b, a)", "((c, a), b)"); + for (int i = 0; i < mvPrimaryKeys.size(); i++) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + + logger.info("Testing MV primary key: {}", mvPrimaryKeys.get(i)); + + // only accept rows where b = 1 + createView("mv_test" + i, "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND b = 1 AND c IS NOT NULL PRIMARY KEY " + mvPrimaryKeys.get(i)); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test" + i)) + Thread.sleep(10); + + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 2, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 2, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, 0, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, 2, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, 2, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ?", 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ?", 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0) + ); + + dropView("mv_test" + i); + dropTable("DROP TABLE %s"); + } + } + + @Test + public void testClusteringKeySliceRestrictions() throws Throwable + { + List<String> mvPrimaryKeys = Arrays.asList("((a, b), c)", "((b, a), c)", "(a, b, c)", "(c, b, a)", "((c, a), b)"); + for (int i = 0; i < mvPrimaryKeys.size(); i++) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + + logger.info("Testing MV primary key: {}", mvPrimaryKeys.get(i)); + + createView("mv_test" + i, "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND b >= 1 AND c IS NOT NULL PRIMARY KEY " + mvPrimaryKeys.get(i)); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test" + i)) + Thread.sleep(10); + + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, -1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 0, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 2, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, -1, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, -1, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ?", 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ?", 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0) + ); + + dropView("mv_test" + i); + dropTable("DROP TABLE %s"); + } + } + + @Test + public void testClusteringKeyINRestrictions() throws Throwable + { + List<String> mvPrimaryKeys = Arrays.asList("((a, b), c)", "((b, a), c)", "(a, b, c)", "(c, b, a)", "((c, a), b)"); + for (int i = 0; i < mvPrimaryKeys.size(); i++) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 2, 1, 0); + + logger.info("Testing MV primary key: {}", mvPrimaryKeys.get(i)); + + // only accept rows where b = 1 + createView("mv_test" + i, "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND b IN (1, 2) AND c IS NOT NULL PRIMARY KEY " + mvPrimaryKeys.get(i)); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test" + i)) + Thread.sleep(10); + + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 2, 1, 0) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, -1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 0, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 2, 1, 0) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 2, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0), + row(1, 2, 1, 0) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, -1, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0), + row(1, 2, 1, 0) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0), + row(1, 2, 1, 0) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, -1, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ?", 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0), + row(1, 2, 1, 0) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0), + row(1, 2, 1, 0) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ?", 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0) + ); + + dropView("mv_test" + i); + dropTable("DROP TABLE %s"); + } + } + + @Test + public void testClusteringKeyMultiColumnRestrictions() throws Throwable + { + List<String> mvPrimaryKeys = Arrays.asList("((a, b), c)", "((b, a), c)", "(a, b, c)", "(c, b, a)", "((c, a), b)"); + for (int i = 0; i < mvPrimaryKeys.size(); i++) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, -1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + + logger.info("Testing MV primary key: {}", mvPrimaryKeys.get(i)); + + // only accept rows where b = 1 + createView("mv_test" + i, "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND (b, c) >= (1, 0) PRIMARY KEY " + mvPrimaryKeys.get(i)); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test" + i)) + Thread.sleep(10); + + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, -1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 1, -1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 2, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, -1, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, -1, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, -1); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, -1, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ?", 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 0, 1), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0), + row(1, 1, 1, 0), + row(1, 1, 2, 0) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ?", 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 1, 0, 0), + row(0, 1, 1, 0) + ); + + dropView("mv_test" + i); + dropTable("DROP TABLE %s"); + } + } + + @Test + public void testClusteringKeyFilteringRestrictions() throws Throwable + { + List<String> mvPrimaryKeys = Arrays.asList("((a, b), c)", "((b, a), c)", "(a, b, c)", "(c, b, a)", "((c, a), b)"); + for (int i = 0; i < mvPrimaryKeys.size(); i++) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, -1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + + logger.info("Testing MV primary key: {}", mvPrimaryKeys.get(i)); + + // only accept rows where b = 1 + createView("mv_test" + i, "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a IS NOT NULL AND b IS NOT NULL AND c = 1 PRIMARY KEY " + mvPrimaryKeys.get(i)); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test" + i)) + Thread.sleep(10); + + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 0, 1, 0), + row(0, 1, 1, 0), + row(1, 0, 1, 0), + row(1, 1, 1, 0) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 2, 1, -1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 0, 1, 0), + row(0, 1, 1, 0), + row(1, 0, 1, 0), + row(1, 1, 1, 0) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 2, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 0, 1, 0), + row(0, 1, 1, 0), + row(1, 0, 1, 0), + row(1, 1, 1, 0), + row(1, 2, 1, 0) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, -1, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 2, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 0, 1, 0), + row(0, 1, 1, 0), + row(1, 0, 1, 0), + row(1, 1, 1, 0), + row(1, 2, 1, 0) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 2, 1, 1, 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 0, 1, 0), + row(0, 1, 1, 0), + row(1, 0, 1, 0), + row(1, 1, 1, 2), + row(1, 2, 1, 0) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, -1); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, -1, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, 0, 0); + execute("DELETE FROM %s WHERE a = ? AND b = ?", 0, -1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 0, 1, 0), + row(0, 1, 1, 0), + row(1, 0, 1, 0), + row(1, 1, 1, 2), + row(1, 2, 1, 0) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 0, 1, 0), + row(0, 1, 1, 0), + row(1, 0, 1, 0), + row(1, 2, 1, 0) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ?", 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(0, 0, 1, 0), + row(0, 1, 1, 0) + ); + + dropView("mv_test" + i); + dropTable("DROP TABLE %s"); + } + } + + @Test + public void testPartitionKeyAndClusteringKeyFilteringRestrictions() throws Throwable + { + List<String> mvPrimaryKeys = Arrays.asList("((a, b), c)", "((b, a), c)", "(a, b, c)", "(c, b, a)", "((c, a), b)"); + for (int i = 0; i < mvPrimaryKeys.size(); i++) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, -1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 0); + + logger.info("Testing MV primary key: {}", mvPrimaryKeys.get(i)); + + // only accept rows where b = 1 + createView("mv_test" + i, "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE a = 1 AND b IS NOT NULL AND c = 1 PRIMARY KEY " + mvPrimaryKeys.get(i)); + + while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test" + i)) + Thread.sleep(10); + + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 1, 0), + row(1, 1, 1, 0) + ); + + // insert new rows that do not match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 0); + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 0, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 1, 0), + row(1, 1, 1, 0) + ); + + // insert new row that does match the filter + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 2, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 1, 0), + row(1, 1, 1, 0), + row(1, 2, 1, 0) + ); + + // update rows that don't match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 1, 1, -1, 0); + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 0, 1, 1, 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 1, 0), + row(1, 1, 1, 0), + row(1, 2, 1, 0) + ); + + // update a row that does match the filter + execute("UPDATE %s SET d = ? WHERE a = ? AND b = ? AND c = ?", 2, 1, 1, 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 1, 0), + row(1, 1, 1, 2), + row(1, 2, 1, 0) + ); + + // delete rows that don't match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, -1); + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 2, 0, 1); + execute("DELETE FROM %s WHERE a = ?", 0); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 1, 0), + row(1, 1, 1, 2), + row(1, 2, 1, 0) + ); + + // delete a row that does match the filter + execute("DELETE FROM %s WHERE a = ? AND b = ? AND c = ?", 1, 1, 1); + assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), + row(1, 0, 1, 0), + row(1, 2, 1, 0) + ); + + // delete a partition that matches the filter + execute("DELETE FROM %s WHERE a = ?", 1); + assertEmpty(execute("SELECT a, b, c, d FROM mv_test" + i)); + + dropView("mv_test" + i); + dropTable("DROP TABLE %s"); + } + } + + @Test + public void testAllTypes() throws Throwable + { + String myType = createType("CREATE TYPE %s (a int, b uuid, c set<text>)"); + String columnNames = "asciival, " + + "bigintval, " + + "blobval, " + + "booleanval, " + + "dateval, " + + "decimalval, " + + "doubleval, " + + "floatval, " + + "inetval, " + + "intval, " + + "textval, " + + "timeval, " + + "timestampval, " + + "timeuuidval, " + + "uuidval," + + "varcharval, " + + "varintval, " + + "frozenlistval, " + + "frozensetval, " + + "frozenmapval, " + + "tupleval, " + + "udtval"; + + createTable( + "CREATE TABLE %s (" + + "asciival ascii, " + + "bigintval bigint, " + + "blobval blob, " + + "booleanval boolean, " + + "dateval date, " + + "decimalval decimal, " + + "doubleval double, " + + "floatval float, " + + "inetval inet, " + + "intval int, " + + "textval text, " + + "timeval time, " + + "timestampval timestamp, " + + "timeuuidval timeuuid, " + + "uuidval uuid," + + "varcharval varchar, " + + "varintval varint, " + + "frozenlistval frozen<list<int>>, " + + "frozensetval frozen<set<uuid>>, " + + "frozenmapval frozen<map<ascii, int>>," + + "tupleval frozen<tuple<int, ascii, uuid>>," + + "udtval frozen<" + myType + ">, " + + "PRIMARY KEY (" + columnNames + "))"); + + execute("USE " + keyspace()); + executeNet(protocolVersion, "USE " + keyspace()); + + + createView( + "mv_test", + "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE " + + "asciival = 'abc' AND " + + "bigintval = 123 AND " + + "blobval = 0xfeed AND " + + "booleanval = true AND " + + "dateval = '1987-03-23' AND " + + "decimalval = 123.123 AND " + + "doubleval = 123.123 AND " + + "floatval = 123.123 AND " + + "inetval = '127.0.0.1' AND " + + "intval = 123 AND " + + "textval = 'abc' AND " + + "timeval = '07:35:07.000111222' AND " + + "timestampval = 123123123 AND " + + "timeuuidval = 6BDDC89A-5644-11E4-97FC-56847AFE9799 AND " + + "uuidval = 6BDDC89A-5644-11E4-97FC-56847AFE9799 AND " + + "varcharval = 'abc' AND " + + "varintval = 123123123 AND " + + "frozenlistval = [1, 2, 3] AND " + + "frozensetval = {6BDDC89A-5644-11E4-97FC-56847AFE9799} AND " + + "frozenmapval = {'a': 1, 'b': 2} AND " + + "tupleval = (1, 'foobar', 6BDDC89A-5644-11E4-97FC-56847AFE9799) AND " + + "udtval = {a: 1, b: 6BDDC89A-5644-11E4-97FC-56847AFE9799, c: {'foo', 'bar'}} " + + "PRIMARY KEY (" + columnNames + ")"); + + execute("INSERT INTO %s (" + columnNames + ") VALUES (" + + "'abc'," + + "123," + + "0xfeed," + + "true," + + "'1987-03-23'," + + "123.123," + + "123.123," + + "123.123," + + "'127.0.0.1'," + + "123," + + "'abc'," + + "'07:35:07.000111222'," + + "123123123," + + "6BDDC89A-5644-11E4-97FC-56847AFE9799," + + "6BDDC89A-5644-11E4-97FC-56847AFE9799," + + "'abc'," + + "123123123," + + "[1, 2, 3]," + + "{6BDDC89A-5644-11E4-97FC-56847AFE9799}," + + "{'a': 1, 'b': 2}," + + "(1, 'foobar', 6BDDC89A-5644-11E4-97FC-56847AFE9799)," + + "{a: 1, b: 6BDDC89A-5644-11E4-97FC-56847AFE9799, c: {'foo', 'bar'}})"); + + assert !execute("SELECT * FROM mv_test").isEmpty(); + + executeNet(protocolVersion, "ALTER TABLE %s RENAME inetval TO foo"); + assert !execute("SELECT * FROM mv_test").isEmpty(); + } +} http://git-wip-us.apache.org/repos/asf/cassandra/blob/5a4253b6/test/unit/org/apache/cassandra/cql3/ViewTest.java ---------------------------------------------------------------------- diff --git a/test/unit/org/apache/cassandra/cql3/ViewTest.java b/test/unit/org/apache/cassandra/cql3/ViewTest.java index 43f7747..5d65115 100644 --- a/test/unit/org/apache/cassandra/cql3/ViewTest.java +++ b/test/unit/org/apache/cassandra/cql3/ViewTest.java @@ -558,14 +558,14 @@ public class ViewTest extends CQLTester createView("mv_test2", "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE textval2 IS NOT NULL AND k IS NOT NULL AND asciival IS NOT NULL AND bigintval IS NOT NULL AND textval1 IS NOT NULL PRIMARY KEY ((textval2, k), asciival, bigintval, textval1)"); while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test2")) - Thread.sleep(1000); + Thread.sleep(10); Assert.assertEquals(100, execute("select * from mv_test2").size()); createView("mv_test3", "CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s WHERE textval2 IS NOT NULL AND k IS NOT NULL AND asciival IS NOT NULL AND bigintval IS NOT NULL AND textval1 IS NOT NULL PRIMARY KEY ((textval2, k), bigintval, textval1, asciival)"); while (!SystemKeyspace.isViewBuilt(keyspace(), "mv_test3")) - Thread.sleep(1000); + Thread.sleep(10); Assert.assertEquals(100, execute("select * from mv_test3").size()); Assert.assertEquals(100, execute("select asciival from mv_test3 where textval2 = ? and k = ?", "baz", 0).size()); http://git-wip-us.apache.org/repos/asf/cassandra/blob/5a4253b6/test/unit/org/apache/cassandra/cql3/validation/operations/SelectSingleColumnRelationTest.java ---------------------------------------------------------------------- diff --git a/test/unit/org/apache/cassandra/cql3/validation/operations/SelectSingleColumnRelationTest.java b/test/unit/org/apache/cassandra/cql3/validation/operations/SelectSingleColumnRelationTest.java index f80e489..fef34ca 100644 --- a/test/unit/org/apache/cassandra/cql3/validation/operations/SelectSingleColumnRelationTest.java +++ b/test/unit/org/apache/cassandra/cql3/validation/operations/SelectSingleColumnRelationTest.java @@ -62,6 +62,10 @@ public class SelectSingleColumnRelationTest extends CQLTester "SELECT * FROM %s WHERE c = 0 AND b <= ?", set(0)); assertInvalidMessage("Collection column 'b' (set<int>) cannot be restricted by a 'IN' relation", "SELECT * FROM %s WHERE c = 0 AND b IN (?)", set(0)); + assertInvalidMessage("Unsupported \"!=\" relation: b != 5", + "SELECT * FROM %s WHERE c = 0 AND b != 5"); + assertInvalidMessage("Unsupported restriction: b IS NOT NULL", + "SELECT * FROM %s WHERE c = 0 AND b IS NOT NULL"); } @Test
