| /* |
| * 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) |
| ); |
| |
| // insert a partition with one matching and one non-matching row using a batch (CASSANDRA-10614) |
| String tableName = KEYSPACE + "." + currentTable(); |
| execute("BEGIN BATCH " + |
| "INSERT INTO " + tableName + " (a, b, c, d) VALUES (?, ?, ?, ?); " + |
| "INSERT INTO " + tableName + " (a, b, c, d) VALUES (?, ?, ?, ?); " + |
| "APPLY BATCH", |
| 4, 4, 0, 0, |
| 4, 4, 1, 1); |
| assertRowsIgnoringOrder(execute("SELECT a, b, c, d FROM mv_test" + i), |
| row(0, 0, 1, 0), |
| row(0, 1, 1, 0), |
| row(4, 4, 1, 1) |
| ); |
| |
| 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(); |
| } |
| } |
