/* * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.apache.cassandra.cql3.validation.operations; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.List; import org.junit.Test; import org.apache.cassandra.cql3.CQLTester; import static org.apache.commons.lang3.StringUtils.isEmpty; import static org.junit.Assert.assertEquals; public class DeleteTest extends CQLTester { /** Test for cassandra 8558 */ @Test public void testRangeDeletion() throws Throwable { createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 1, 1, 1, 1); flush(); execute("DELETE FROM %s WHERE a=? AND b=?", 1, 1); flush(); assertEmpty(execute("SELECT * FROM %s WHERE a=? AND b=? AND c=?", 1, 1, 1)); } /** * Test simple deletion and in particular check for #4193 bug * migrated from cql_tests.py:TestCQL.deletion_test() */ @Test public void testDeletion() throws Throwable { createTable("CREATE TABLE %s (username varchar, id int, name varchar, stuff varchar, PRIMARY KEY(username, id))"); execute("INSERT INTO %s (username, id, name, stuff) VALUES (?, ?, ?, ?)", "abc", 2, "rst", "some value"); execute("INSERT INTO %s (username, id, name, stuff) VALUES (?, ?, ?, ?)", "abc", 4, "xyz", "some other value"); assertRows(execute("SELECT * FROM %s"), row("abc", 2, "rst", "some value"), row("abc", 4, "xyz", "some other value")); execute("DELETE FROM %s WHERE username='abc' AND id=2"); assertRows(execute("SELECT * FROM %s"), row("abc", 4, "xyz", "some other value")); createTable("CREATE TABLE %s (username varchar, id int, name varchar, stuff varchar, PRIMARY KEY(username, id, name)) WITH COMPACT STORAGE"); execute("INSERT INTO %s (username, id, name, stuff) VALUES (?, ?, ?, ?)", "abc", 2, "rst", "some value"); execute("INSERT INTO %s (username, id, name, stuff) VALUES (?, ?, ?, ?)", "abc", 4, "xyz", "some other value"); assertRows(execute("SELECT * FROM %s"), row("abc", 2, "rst", "some value"), row("abc", 4, "xyz", "some other value")); execute("DELETE FROM %s WHERE username='abc' AND id=2"); assertRows(execute("SELECT * FROM %s"), row("abc", 4, "xyz", "some other value")); } /** * Test deletion by 'composite prefix' (range tombstones) * migrated from cql_tests.py:TestCQL.range_tombstones_test() */ @Test public void testDeleteByCompositePrefix() throws Throwable { // This test used 3 nodes just to make sure RowMutation are correctly serialized createTable("CREATE TABLE %s ( k int, c1 int, c2 int, v1 int, v2 int, PRIMARY KEY (k, c1, c2))"); int numRows = 5; int col1 = 2; int col2 = 2; int cpr = col1 * col2; for (int i = 0; i < numRows; i++) for (int j = 0; j < col1; j++) for (int k = 0; k < col2; k++) { int n = (i * cpr) + (j * col2) + k; execute("INSERT INTO %s (k, c1, c2, v1, v2) VALUES (?, ?, ?, ?, ?)", i, j, k, n, n); } for (int i = 0; i < numRows; i++) { Object[][] rows = getRows(execute("SELECT v1, v2 FROM %s where k = ?", i)); for (int x = i * cpr; x < (i + 1) * cpr; x++) { assertEquals(x, rows[x - i * cpr][0]); assertEquals(x, rows[x - i * cpr][1]); } } for (int i = 0; i < numRows; i++) execute("DELETE FROM %s WHERE k = ? AND c1 = 0", i); for (int i = 0; i < numRows; i++) { Object[][] rows = getRows(execute("SELECT v1, v2 FROM %s WHERE k = ?", i)); for (int x = i * cpr + col1; x < (i + 1) * cpr; x++) { assertEquals(x, rows[x - i * cpr - col1][0]); assertEquals(x, rows[x - i * cpr - col1][1]); } } for (int i = 0; i < numRows; i++) { Object[][] rows = getRows(execute("SELECT v1, v2 FROM %s WHERE k = ?", i)); for (int x = i * cpr + col1; x < (i + 1) * cpr; x++) { assertEquals(x, rows[x - i * cpr - col1][0]); assertEquals(x, rows[x - i * cpr - col1][1]); } } } /** * Test deletion by 'composite prefix' (range tombstones) with compaction * migrated from cql_tests.py:TestCQL.range_tombstones_compaction_test() */ @Test public void testDeleteByCompositePrefixWithCompaction() throws Throwable { createTable("CREATE TABLE %s (k int, c1 int, c2 int, v1 text, PRIMARY KEY (k, c1, c2))"); for (int c1 = 0; c1 < 4; c1++) for (int c2 = 0; c2 < 2; c2++) execute("INSERT INTO %s (k, c1, c2, v1) VALUES (0, ?, ?, ?)", c1, c2, String.format("%d%d", c1, c2)); flush(); execute("DELETE FROM %s WHERE k = 0 AND c1 = 1"); flush(); compact(); Object[][] rows = getRows(execute("SELECT v1 FROM %s WHERE k = 0")); int idx = 0; for (int c1 = 0; c1 < 4; c1++) for (int c2 = 0; c2 < 2; c2++) if (c1 != 1) assertEquals(String.format("%d%d", c1, c2), rows[idx++][0]); } /** * Test deletion of rows * migrated from cql_tests.py:TestCQL.delete_row_test() */ @Test public void testRowDeletion() throws Throwable { createTable("CREATE TABLE %s (k int, c1 int, c2 int, v1 int, v2 int, PRIMARY KEY (k, c1, c2))"); execute("INSERT INTO %s (k, c1, c2, v1, v2) VALUES (?, ?, ?, ?, ?)", 0, 0, 0, 0, 0); execute("INSERT INTO %s (k, c1, c2, v1, v2) VALUES (?, ?, ?, ?, ?)", 0, 0, 1, 1, 1); execute("INSERT INTO %s (k, c1, c2, v1, v2) VALUES (?, ?, ?, ?, ?)", 0, 0, 2, 2, 2); execute("INSERT INTO %s (k, c1, c2, v1, v2) VALUES (?, ?, ?, ?, ?)", 0, 1, 0, 3, 3); execute("DELETE FROM %s WHERE k = 0 AND c1 = 0 AND c2 = 0"); assertRowCount(execute("SELECT * FROM %s"), 3); } /** * Check the semantic of CQL row existence (part of #4361), * migrated from cql_tests.py:TestCQL.row_existence_test() */ @Test public void testRowExistence() throws Throwable { createTable("CREATE TABLE %s (k int, c int, v1 int, v2 int, PRIMARY KEY (k, c))"); execute("INSERT INTO %s (k, c, v1, v2) VALUES (1, 1, 1, 1)"); assertRows(execute("SELECT * FROM %s"), row(1, 1, 1, 1)); assertInvalid("DELETE c FROM %s WHERE k = 1 AND c = 1"); execute("DELETE v2 FROM %s WHERE k = 1 AND c = 1"); assertRows(execute("SELECT * FROM %s"), row(1, 1, 1, null)); execute("DELETE v1 FROM %s WHERE k = 1 AND c = 1"); assertRows(execute("SELECT * FROM %s"), row(1, 1, null, null)); execute("DELETE FROM %s WHERE k = 1 AND c = 1"); assertEmpty(execute("SELECT * FROM %s")); execute("INSERT INTO %s (k, c) VALUES (2, 2)"); assertRows(execute("SELECT * FROM %s"), row(2, 2, null, null)); } /** * Migrated from cql_tests.py:TestCQL.remove_range_slice_test() */ @Test public void testRemoveRangeSlice() throws Throwable { createTable("CREATE TABLE %s (k int PRIMARY KEY, v int)"); for (int i = 0; i < 3; i++) execute("INSERT INTO %s (k, v) VALUES (?, ?)", i, i); execute("DELETE FROM %s WHERE k = 1"); assertRows(execute("SELECT * FROM %s"), row(0, 0), row(2, 2)); } /** * Test deletions * migrated from cql_tests.py:TestCQL.no_range_ghost_test() */ @Test public void testNoRangeGhost() throws Throwable { createTable("CREATE TABLE %s ( k int PRIMARY KEY, v int ) "); for (int k = 0; k < 5; k++) execute("INSERT INTO %s (k, v) VALUES (?, 0)", k); Object[][] rows = getRows(execute("SELECT k FROM %s")); int[] ordered = sortIntRows(rows); for (int k = 0; k < 5; k++) assertEquals(k, ordered[k]); execute("DELETE FROM %s WHERE k=2"); rows = getRows(execute("SELECT k FROM %s")); ordered = sortIntRows(rows); int idx = 0; for (int k = 0; k < 5; k++) if (k != 2) assertEquals(k, ordered[idx++]); // Example from #3505 createTable("CREATE TABLE %s ( KEY varchar PRIMARY KEY, password varchar, gender varchar, birth_year bigint)"); execute("INSERT INTO %s (KEY, password) VALUES ('user1', 'ch@ngem3a')"); execute("UPDATE %s SET gender = 'm', birth_year = 1980 WHERE KEY = 'user1'"); assertRows(execute("SELECT * FROM %s WHERE KEY='user1'"), row("user1", 1980L, "m", "ch@ngem3a")); execute("TRUNCATE %s"); assertEmpty(execute("SELECT * FROM %s")); assertEmpty(execute("SELECT * FROM %s WHERE KEY='user1'")); } private int[] sortIntRows(Object[][] rows) { int[] ret = new int[rows.length]; for (int i = 0; i < ret.length; i++) ret[i] = rows[i][0] == null ? Integer.MIN_VALUE : (Integer) rows[i][0]; Arrays.sort(ret); return ret; } /** * Migrated from cql_tests.py:TestCQL.range_with_deletes_test() */ @Test public void testRandomDeletions() throws Throwable { createTable("CREATE TABLE %s (k int PRIMARY KEY, v int,)"); int nb_keys = 30; int nb_deletes = 5; List<Integer> deletions = new ArrayList<>(nb_keys); for (int i = 0; i < nb_keys; i++) { execute("INSERT INTO %s (k, v) VALUES (?, ?)", i, i); deletions.add(i); } Collections.shuffle(deletions); for (int i = 0; i < nb_deletes; i++) execute("DELETE FROM %s WHERE k = ?", deletions.get(i)); assertRowCount(execute("SELECT * FROM %s LIMIT ?", (nb_keys / 2)), nb_keys / 2); } /** * Test for CASSANDRA-8558, deleted row still can be selected out * migrated from cql_tests.py:TestCQL.bug_8558_test() */ @Test public void testDeletedRowCannotBeSelected() throws Throwable { createTable("CREATE TABLE %s (a int, b int, c text,primary key(a,b))"); execute("INSERT INTO %s (a,b,c) VALUES(1,1,'1')"); flush(); execute("DELETE FROM %s where a=1 and b=1"); flush(); assertEmpty(execute("select * from %s where a=1 and b=1")); } @Test public void testDeleteWithNoClusteringColumns() throws Throwable { testDeleteWithNoClusteringColumns(false); testDeleteWithNoClusteringColumns(true); } private void testDeleteWithNoClusteringColumns(boolean forceFlush) throws Throwable { for (String compactOption : new String[] {"", " WITH COMPACT STORAGE" }) { createTable("CREATE TABLE %s (partitionKey int PRIMARY KEY," + "value int)" + compactOption); execute("INSERT INTO %s (partitionKey, value) VALUES (0, 0)"); execute("INSERT INTO %s (partitionKey, value) VALUES (1, 1)"); execute("INSERT INTO %s (partitionKey, value) VALUES (2, 2)"); execute("INSERT INTO %s (partitionKey, value) VALUES (3, 3)"); flush(forceFlush); execute("DELETE value FROM %s WHERE partitionKey = ?", 0); flush(forceFlush); if (isEmpty(compactOption)) { assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 0), row(0, null)); } else { assertEmpty(execute("SELECT * FROM %s WHERE partitionKey = ?", 0)); } execute("DELETE FROM %s WHERE partitionKey IN (?, ?)", 0, 1); flush(forceFlush); assertRows(execute("SELECT * FROM %s"), row(2, 2), row(3, 3)); // test invalid queries // token function assertInvalidMessage("The token function cannot be used in WHERE clauses for DELETE statements", "DELETE FROM %s WHERE token(partitionKey) = token(?)", 0); // multiple time same primary key element in WHERE clause assertInvalidMessage("partitionkey cannot be restricted by more than one relation if it includes an Equal", "DELETE FROM %s WHERE partitionKey = ? AND partitionKey = ?", 0, 1); // unknown identifiers assertInvalidMessage("Unknown identifier unknown", "DELETE unknown FROM %s WHERE partitionKey = ?", 0); assertInvalidMessage("Undefined name partitionkey1 in where clause ('partitionkey1 = ?')", "DELETE FROM %s WHERE partitionKey1 = ?", 0); // Invalid operator in the where clause assertInvalidMessage("Only EQ and IN relation are supported on the partition key (unless you use the token() function)", "DELETE FROM %s WHERE partitionKey > ? ", 0); assertInvalidMessage("Cannot use CONTAINS on non-collection column partitionkey", "DELETE FROM %s WHERE partitionKey CONTAINS ?", 0); // Non primary key in the where clause assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value", "DELETE FROM %s WHERE partitionKey = ? AND value = ?", 0, 1); } } @Test public void testDeleteWithOneClusteringColumns() throws Throwable { testDeleteWithOneClusteringColumns(false); testDeleteWithOneClusteringColumns(true); } private void testDeleteWithOneClusteringColumns(boolean forceFlush) throws Throwable { for (String compactOption : new String[] {"", " WITH COMPACT STORAGE" }) { createTable("CREATE TABLE %s (partitionKey int," + "clustering int," + "value int," + " PRIMARY KEY (partitionKey, clustering))" + compactOption); execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 0, 0)"); execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 1, 1)"); execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 2, 2)"); execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 3, 3)"); execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 4, 4)"); execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (0, 5, 5)"); execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (1, 0, 6)"); flush(forceFlush); execute("DELETE value FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1); flush(forceFlush); if (isEmpty(compactOption)) { assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1), row(0, 1, null)); } else { assertEmpty(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1)); } execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering) = (?)", 0, 1); flush(forceFlush); assertEmpty(execute("SELECT value FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1)); execute("DELETE FROM %s WHERE partitionKey IN (?, ?) AND clustering = ?", 0, 1, 0); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1), row(0, 2, 2), row(0, 3, 3), row(0, 4, 4), row(0, 5, 5)); execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering) IN ((?), (?))", 0, 4, 5); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1), row(0, 2, 2), row(0, 3, 3)); // test invalid queries // missing primary key element assertInvalidMessage("Some partition key parts are missing: partitionkey", "DELETE FROM %s WHERE clustering = ?", 1); // token function assertInvalidMessage("The token function cannot be used in WHERE clauses for DELETE statements", "DELETE FROM %s WHERE token(partitionKey) = token(?) AND clustering = ? ", 0, 1); // multiple time same primary key element in WHERE clause assertInvalidMessage("clustering cannot be restricted by more than one relation if it includes an Equal", "DELETE FROM %s WHERE partitionKey = ? AND clustering = ? AND clustering = ?", 0, 1, 1); // unknown identifiers assertInvalidMessage("Unknown identifier value1", "DELETE value1 FROM %s WHERE partitionKey = ? AND clustering = ?", 0, 1); assertInvalidMessage("Undefined name partitionkey1 in where clause ('partitionkey1 = ?')", "DELETE FROM %s WHERE partitionKey1 = ? AND clustering = ?", 0, 1); assertInvalidMessage("Undefined name clustering_3 in where clause ('clustering_3 = ?')", "DELETE FROM %s WHERE partitionKey = ? AND clustering_3 = ?", 0, 1); // Invalid operator in the where clause assertInvalidMessage("Only EQ and IN relation are supported on the partition key (unless you use the token() function)", "DELETE FROM %s WHERE partitionKey > ? AND clustering = ?", 0, 1); assertInvalidMessage("Cannot use CONTAINS on non-collection column partitionkey", "DELETE FROM %s WHERE partitionKey CONTAINS ? AND clustering = ?", 0, 1); // Non primary key in the where clause assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value", "DELETE FROM %s WHERE partitionKey = ? AND clustering = ? AND value = ?", 0, 1, 3); } } @Test public void testDeleteWithTwoClusteringColumns() throws Throwable { testDeleteWithTwoClusteringColumns(false); testDeleteWithTwoClusteringColumns(true); } private void testDeleteWithTwoClusteringColumns(boolean forceFlush) throws Throwable { for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" }) { createTable("CREATE TABLE %s (partitionKey int," + "clustering_1 int," + "clustering_2 int," + "value int," + " PRIMARY KEY (partitionKey, clustering_1, clustering_2))" + compactOption); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 0, 0)"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 1, 1)"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 2, 2)"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 3, 3)"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 1, 1, 4)"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 1, 2, 5)"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (1, 0, 0, 6)"); flush(forceFlush); execute("DELETE value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1); flush(forceFlush); if (isEmpty(compactOption)) { assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1), row(0, 1, 1, null)); } else { assertEmpty(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1)); } execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) = (?, ?)", 0, 1, 1); flush(forceFlush); assertEmpty(execute("SELECT value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1)); execute("DELETE FROM %s WHERE partitionKey IN (?, ?) AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 0, 0); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1), row(0, 0, 1, 1), row(0, 0, 2, 2), row(0, 0, 3, 3), row(0, 1, 2, 5)); Object[][] rows; if (isEmpty(compactOption)) { rows = new Object[][]{row(0, 0, 1, 1), row(0, 0, 2, null), row(0, 0, 3, null), row(0, 1, 2, 5)}; } else { rows = new Object[][]{row(0, 0, 1, 1), row(0, 1, 2, 5)}; } execute("DELETE value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 IN (?, ?)", 0, 0, 2, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1), rows); if (isEmpty(compactOption)) { rows = new Object[][]{row(0, 0, 1, 1), row(0, 0, 3, null)}; } else { rows = new Object[][]{row(0, 0, 1, 1)}; } execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) IN ((?, ?), (?, ?))", 0, 0, 2, 1, 2); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1), rows); execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1) IN ((?), (?)) AND clustering_2 = ?", 0, 0, 2, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey IN (?, ?)", 0, 1), row(0, 0, 1, 1)); // test invalid queries // missing primary key element assertInvalidMessage("Some partition key parts are missing: partitionkey", "DELETE FROM %s WHERE clustering_1 = ? AND clustering_2 = ?", 1, 1); assertInvalidMessage("PRIMARY KEY column \"clustering_2\" cannot be restricted as preceding column \"clustering_1\" is not restricted", "DELETE FROM %s WHERE partitionKey = ? AND clustering_2 = ?", 0, 1); // token function assertInvalidMessage("The token function cannot be used in WHERE clauses for DELETE statements", "DELETE FROM %s WHERE token(partitionKey) = token(?) AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1); // multiple time same primary key element in WHERE clause assertInvalidMessage("clustering_1 cannot be restricted by more than one relation if it includes an Equal", "DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ? AND clustering_1 = ?", 0, 1, 1, 1); // unknown identifiers assertInvalidMessage("Unknown identifier value1", "DELETE value1 FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1); assertInvalidMessage("Undefined name partitionkey1 in where clause ('partitionkey1 = ?')", "DELETE FROM %s WHERE partitionKey1 = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1); assertInvalidMessage("Undefined name clustering_3 in where clause ('clustering_3 = ?')", "DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_3 = ?", 0, 1, 1); // Invalid operator in the where clause assertInvalidMessage("Only EQ and IN relation are supported on the partition key (unless you use the token() function)", "DELETE FROM %s WHERE partitionKey > ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1); assertInvalidMessage("Cannot use CONTAINS on non-collection column partitionkey", "DELETE FROM %s WHERE partitionKey CONTAINS ? AND clustering_1 = ? AND clustering_2 = ?", 0, 1, 1); // Non primary key in the where clause assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value", "DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ? AND value = ?", 0, 1, 1, 3); } } @Test public void testDeleteWithRangeAndOneClusteringColumn() throws Throwable { testDeleteWithRangeAndOneClusteringColumn(false); testDeleteWithRangeAndOneClusteringColumn(true); } private void testDeleteWithRangeAndOneClusteringColumn(boolean forceFlush) throws Throwable { for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" }) { createTable("CREATE TABLE %s (partitionKey int," + "clustering int," + "value int," + " PRIMARY KEY (partitionKey, clustering))" + compactOption); int value = 0; for (int partitionKey = 0; partitionKey < 5; partitionKey++) for (int clustering1 = 0; clustering1 < 5; clustering1++) execute("INSERT INTO %s (partitionKey, clustering, value) VALUES (?, ?, ?)", partitionKey, clustering1, value++); flush(forceFlush); // test delete partition execute("DELETE FROM %s WHERE partitionKey = ?", 1); flush(forceFlush); assertEmpty(execute("SELECT * FROM %s WHERE partitionKey = ?", 1)); // test slices on the first clustering column execute("DELETE FROM %s WHERE partitionKey = ? AND clustering >= ?", 0, 4); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 0), row(0, 0, 0), row(0, 1, 1), row(0, 2, 2), row(0, 3, 3)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering > ?", 0, 2); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 0), row(0, 0, 0), row(0, 1, 1), row(0, 2, 2)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering <= ?", 0, 0); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 0), row(0, 1, 1), row(0, 2, 2)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering < ?", 0, 2); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 0), row(0, 2, 2)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering >= ? AND clustering < ?", 2, 0, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 13), row(2, 4, 14)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering > ? AND clustering <= ?", 2, 3, 5); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 13)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering < ? AND clustering > ?", 2, 3, 5); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 13)); // test multi-column slices execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering) > (?)", 3, 2); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 3), row(3, 0, 15), row(3, 1, 16), row(3, 2, 17)); execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering) < (?)", 3, 1); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 3), row(3, 1, 16), row(3, 2, 17)); execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering) >= (?) AND (clustering) <= (?)", 3, 0, 1); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 3), row(3, 2, 17)); // Test invalid queries assertInvalidMessage("Range deletions are not supported for specific columns", "DELETE value FROM %s WHERE partitionKey = ? AND clustering >= ?", 2, 1); assertInvalidMessage("Range deletions are not supported for specific columns", "DELETE value FROM %s WHERE partitionKey = ?", 2); } } @Test public void testDeleteWithRangeAndTwoClusteringColumns() throws Throwable { testDeleteWithRangeAndTwoClusteringColumns(false); testDeleteWithRangeAndTwoClusteringColumns(true); } private void testDeleteWithRangeAndTwoClusteringColumns(boolean forceFlush) throws Throwable { for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" }) { createTable("CREATE TABLE %s (partitionKey int," + "clustering_1 int," + "clustering_2 int," + "value int," + " PRIMARY KEY (partitionKey, clustering_1, clustering_2))" + compactOption); int value = 0; for (int partitionKey = 0; partitionKey < 5; partitionKey++) for (int clustering1 = 0; clustering1 < 5; clustering1++) for (int clustering2 = 0; clustering2 < 5; clustering2++) { execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (?, ?, ?, ?)", partitionKey, clustering1, clustering2, value++);} flush(forceFlush); // test unspecified second clustering column execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ?", 0, 1); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 < ?", 0, 2), row(0, 0, 0, 0), row(0, 0, 1, 1), row(0, 0, 2, 2), row(0, 0, 3, 3), row(0, 0, 4, 4)); // test delete partition execute("DELETE FROM %s WHERE partitionKey = ?", 1); flush(forceFlush); assertEmpty(execute("SELECT * FROM %s WHERE partitionKey = ?", 1)); // test slices on the second clustering column execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 < ?", 0, 0, 2); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 < ?", 0, 2), row(0, 0, 2, 2), row(0, 0, 3, 3), row(0, 0, 4, 4)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 <= ?", 0, 0, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 < ?", 0, 2), row(0, 0, 4, 4)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 > ? ", 0, 2, 2); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ?", 0, 2), row(0, 2, 0, 10), row(0, 2, 1, 11), row(0, 2, 2, 12)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 >= ? ", 0, 2, 1); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ?", 0, 2), row(0, 2, 0, 10)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 > ? AND clustering_2 < ? ", 0, 3, 1, 4); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ?", 0, 3), row(0, 3, 0, 15), row(0, 3, 1, 16), row(0, 3, 4, 19)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 > ? AND clustering_2 < ? ", 0, 3, 4, 1); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ?", 0, 3), row(0, 3, 0, 15), row(0, 3, 1, 16), row(0, 3, 4, 19)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 >= ? AND clustering_2 <= ? ", 0, 3, 1, 4); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ?", 0, 3), row(0, 3, 0, 15)); // test slices on the first clustering column execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 >= ?", 0, 4); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 0), row(0, 0, 4, 4), row(0, 2, 0, 10), row(0, 3, 0, 15)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 > ?", 0, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 0), row(0, 0, 4, 4), row(0, 2, 0, 10), row(0, 3, 0, 15)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 < ?", 0, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 0), row(0, 3, 0, 15)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 >= ? AND clustering_1 < ?", 2, 0, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 0, 65), row(2, 3, 1, 66), row(2, 3, 2, 67), row(2, 3, 3, 68), row(2, 3, 4, 69), row(2, 4, 0, 70), row(2, 4, 1, 71), row(2, 4, 2, 72), row(2, 4, 3, 73), row(2, 4, 4, 74)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 > ? AND clustering_1 <= ?", 2, 3, 5); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 0, 65), row(2, 3, 1, 66), row(2, 3, 2, 67), row(2, 3, 3, 68), row(2, 3, 4, 69)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 < ? AND clustering_1 > ?", 2, 3, 5); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 0, 65), row(2, 3, 1, 66), row(2, 3, 2, 67), row(2, 3, 3, 68), row(2, 3, 4, 69)); // test multi-column slices execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) > (?, ?)", 2, 3, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 0, 65), row(2, 3, 1, 66), row(2, 3, 2, 67), row(2, 3, 3, 68)); execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) < (?, ?)", 2, 3, 1); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 1, 66), row(2, 3, 2, 67), row(2, 3, 3, 68)); execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) >= (?, ?) AND (clustering_1) <= (?)", 2, 3, 2, 4); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ?", 2), row(2, 3, 1, 66)); // Test with a mix of single column and multi-column restrictions execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND (clustering_2) < (?)", 3, 0, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 = ?", 3, 0), row(3, 0, 3, 78), row(3, 0, 4, 79)); execute("DELETE FROM %s WHERE partitionKey = ? AND clustering_1 IN (?, ?) AND (clustering_2) >= (?)", 3, 0, 1, 3); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 IN (?, ?)", 3, 0, 1), row(3, 1, 0, 80), row(3, 1, 1, 81), row(3, 1, 2, 82)); execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1) IN ((?), (?)) AND clustering_2 < ?", 3, 0, 1, 1); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 IN (?, ?)", 3, 0, 1), row(3, 1, 1, 81), row(3, 1, 2, 82)); execute("DELETE FROM %s WHERE partitionKey = ? AND (clustering_1) = (?) AND clustering_2 >= ?", 3, 1, 2); flush(forceFlush); assertRows(execute("SELECT * FROM %s WHERE partitionKey = ? AND clustering_1 IN (?, ?)", 3, 0, 1), row(3, 1, 1, 81)); // Test invalid queries assertInvalidMessage("Range deletions are not supported for specific columns", "DELETE value FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) >= (?, ?)", 2, 3, 1); assertInvalidMessage("Range deletions are not supported for specific columns", "DELETE value FROM %s WHERE partitionKey = ? AND clustering_1 >= ?", 2, 3); assertInvalidMessage("Range deletions are not supported for specific columns", "DELETE value FROM %s WHERE partitionKey = ? AND clustering_1 = ?", 2, 3); assertInvalidMessage("Range deletions are not supported for specific columns", "DELETE value FROM %s WHERE partitionKey = ?", 2); } } @Test public void testDeleteWithAStaticColumn() throws Throwable { testDeleteWithAStaticColumn(false); testDeleteWithAStaticColumn(true); } private void testDeleteWithAStaticColumn(boolean forceFlush) throws Throwable { createTable("CREATE TABLE %s (partitionKey int," + "clustering_1 int," + "clustering_2 int," + "value int," + "staticValue text static," + " PRIMARY KEY (partitionKey, clustering_1, clustering_2))"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value, staticValue) VALUES (0, 0, 0, 0, 'A')"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value) VALUES (0, 0, 1, 1)"); execute("INSERT INTO %s (partitionKey, clustering_1, clustering_2, value, staticValue) VALUES (1, 0, 0, 6, 'B')"); flush(forceFlush); execute("DELETE staticValue FROM %s WHERE partitionKey = ?", 0); flush(forceFlush); assertRows(execute("SELECT DISTINCT staticValue FROM %s WHERE partitionKey IN (?, ?)", 0, 1), row(new Object[1]), row("B")); execute("DELETE staticValue, value FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 1, 0, 0); flush(forceFlush); assertRows(execute("SELECT * FROM %s"), row(1, 0, 0, null, null), row(0, 0, 0, null, 0), row(0, 0, 1, null, 1)); assertInvalidMessage("Invalid restrictions on clustering columns since the DELETE statement modifies only static columns", "DELETE staticValue FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND clustering_2 = ?", 0, 0, 1); assertInvalidMessage("Invalid restrictions on clustering columns since the DELETE statement modifies only static columns", "DELETE staticValue FROM %s WHERE partitionKey = ? AND (clustering_1, clustering_2) >= (?, ?)", 0, 0, 1); } @Test public void testDeleteWithSecondaryIndices() throws Throwable { testDeleteWithSecondaryIndices(false); testDeleteWithSecondaryIndices(true); } private void testDeleteWithSecondaryIndices(boolean forceFlush) throws Throwable { createTable("CREATE TABLE %s (partitionKey int," + "clustering_1 int," + "value int," + "values set<int>," + " PRIMARY KEY (partitionKey, clustering_1))"); createIndex("CREATE INDEX ON %s (value)"); createIndex("CREATE INDEX ON %s (clustering_1)"); createIndex("CREATE INDEX ON %s (values)"); execute("INSERT INTO %s (partitionKey, clustering_1, value, values) VALUES (0, 0, 0, {0})"); execute("INSERT INTO %s (partitionKey, clustering_1, value, values) VALUES (0, 1, 1, {0, 1})"); execute("INSERT INTO %s (partitionKey, clustering_1, value, values) VALUES (0, 2, 2, {0, 1, 2})"); execute("INSERT INTO %s (partitionKey, clustering_1, value, values) VALUES (0, 3, 3, {0, 1, 2, 3})"); execute("INSERT INTO %s (partitionKey, clustering_1, value, values) VALUES (1, 0, 4, {0, 1, 2, 3, 4})"); flush(forceFlush); assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value", "DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND value = ?", 3, 3, 3); assertInvalidMessage("Non PRIMARY KEY columns found in where clause: values", "DELETE FROM %s WHERE partitionKey = ? AND clustering_1 = ? AND values CONTAINS ?", 3, 3, 3); assertInvalidMessage("Non PRIMARY KEY columns found in where clause: value", "DELETE FROM %s WHERE partitionKey = ? AND value = ?", 3, 3); assertInvalidMessage("Non PRIMARY KEY columns found in where clause: values", "DELETE FROM %s WHERE partitionKey = ? AND values CONTAINS ?", 3, 3); assertInvalidMessage("Some partition key parts are missing: partitionkey", "DELETE FROM %s WHERE clustering_1 = ?", 3); assertInvalidMessage("Some partition key parts are missing: partitionkey", "DELETE FROM %s WHERE value = ?", 3); assertInvalidMessage("Some partition key parts are missing: partitionkey", "DELETE FROM %s WHERE values CONTAINS ?", 3); } @Test public void testDeleteWithOnlyPK() throws Throwable { // This is a regression test for CASSANDRA-11102 createTable("CREATE TABLE %s (k int, v int, PRIMARY KEY (k, v)) WITH gc_grace_seconds=1"); execute("INSERT INTO %s(k, v) VALUES (?, ?)", 1, 2); execute("DELETE FROM %s WHERE k = ? AND v = ?", 1, 2); execute("INSERT INTO %s(k, v) VALUES (?, ?)", 2, 3); Thread.sleep(500); execute("DELETE FROM %s WHERE k = ? AND v = ?", 2, 3); execute("INSERT INTO %s(k, v) VALUES (?, ?)", 1, 2); Thread.sleep(500); flush(); assertRows(execute("SELECT * FROM %s"), row(1, 2)); Thread.sleep(1000); compact(); assertRows(execute("SELECT * FROM %s"), row(1, 2)); } @Test public void testDeleteColumnNoClustering() throws Throwable { // This is a regression test for CASSANDRA-11068 (and ultimately another test for CASSANDRA-11102) // Creates a table without clustering, insert a row (with a column) and only remove the column. // We should still have a row (with a null column value) even post-compaction. createTable("CREATE TABLE %s (k int PRIMARY KEY, v int) WITH gc_grace_seconds=0"); execute("INSERT INTO %s(k, v) VALUES (?, ?)", 0, 0); execute("DELETE v FROM %s WHERE k=?", 0); assertRows(execute("SELECT * FROM %s"), row(0, null)); flush(); assertRows(execute("SELECT * FROM %s"), row(0, null)); compact(); assertRows(execute("SELECT * FROM %s"), row(0, null)); } private void flush(boolean forceFlush) { if (forceFlush) flush(); } @Test public void testDeleteAndReverseQueries() throws Throwable { // This test insert rows in one sstable and a range tombstone covering some of those rows in another, and it // validates we correctly get only the non-removed rows when doing reverse queries. createTable("CREATE TABLE %s (k text, i int, PRIMARY KEY (k, i))"); for (int i = 0; i < 10; i++) execute("INSERT INTO %s(k, i) values (?, ?)", "a", i); flush(); execute("DELETE FROM %s WHERE k = ? AND i >= ? AND i <= ?", "a", 2, 7); assertRows(execute("SELECT i FROM %s WHERE k = ? ORDER BY i DESC", "a"), row(9), row(8), row(1), row(0) ); flush(); assertRows(execute("SELECT i FROM %s WHERE k = ? ORDER BY i DESC", "a"), row(9), row(8), row(1), row(0) ); } }