github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/delete

# LogicTest: !3node-tenant
statement ok
CREATE TABLE kv (
  k INT PRIMARY KEY,
  v INT,
  UNIQUE INDEX foo (v),
  INDEX bar (k, v)
)

statement ok
CREATE TABLE unindexed (
  k INT PRIMARY KEY,
  v INT
)

statement count 4
INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8)

statement count 4
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

query II rowsort
SELECT * FROM kv
----
1 2
3 4
5 6
7 8

statement ok
CREATE VIEW kview AS SELECT k,v FROM kv

query II rowsort
SELECT * FROM kview
----
1 2
3 4
5 6
7 8

statement error "kview" is not a table
DELETE FROM kview

query II rowsort
SELECT * FROM kview
----
1 2
3 4
5 6
7 8

statement count 2
DELETE FROM kv WHERE k=3 OR v=6

query II rowsort
SELECT * FROM kv
----
1 2
7 8

# delete a non-existent value.
statement count 0
DELETE FROM kv WHERE k=5

query II
DELETE FROM kv RETURNING k, v
----
1 2
7 8

query II
SELECT * FROM kv
----

statement error column "nonexistent" does not exist
DELETE FROM kv WHERE nonexistent = 1

statement count 2
DELETE FROM unindexed WHERE k=3 OR v=6

query II rowsort
SELECT * FROM unindexed
----
1 2
7 8

query II
DELETE FROM unindexed RETURNING k, v
----
1 2
7 8

query II
SELECT * FROM unindexed
----

statement count 4
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

query II colnames
DELETE FROM unindexed WHERE k=3 or v=6 RETURNING *
----
k v
3 4
5 6

query II colnames
DELETE FROM unindexed RETURNING unindexed.*
----
k v
1 2
7 8

statement count 4
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

query II colnames,rowsort
SELECT k, v FROM unindexed
----
k v
1 2
3 4
5 6
7 8

statement count 4
DELETE FROM unindexed

# Delete of range with limit.
statement count 4
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

statement count 1
DELETE FROM unindexed WHERE k >= 4 ORDER BY k LIMIT 1

query II colnames,rowsort
SELECT k, v FROM unindexed
----
k v
1 2
3 4
7 8

statement count 3
DELETE FROM unindexed

query II colnames
SELECT k, v FROM unindexed
----
k v

statement ok
CREATE TABLE indexed (id int primary key, value int, other int, index (value))

statement count 0
DELETE FROM indexed WHERE value = 5

# Check DELETE with ORDER BY clause (MySQL extension)

statement ok
INSERT INTO unindexed VALUES (1, 9), (8, 2), (3, 7), (6, 4)

query II
DELETE FROM unindexed WHERE k > 1 AND v < 7 ORDER BY v DESC LIMIT 2 RETURNING v,k
----
4  6
2  8

query II
DELETE FROM unindexed ORDER BY v LIMIT 2 RETURNING k,v
----
3  7
1  9

# Check DELETE with LIMIT clause (MySQL extension)

statement count 4
INSERT INTO unindexed VALUES (1, 2), (3, 4), (5, 6), (7, 8)

query I
SELECT count(*) FROM [DELETE FROM unindexed LIMIT 2 RETURNING v]
----
2

query I
SELECT count(*) FROM [DELETE FROM unindexed LIMIT 1 RETURNING v]
----
1

query I
SELECT count(*) FROM [DELETE FROM unindexed LIMIT 5 RETURNING v]
----
1

subtest regression_29494

statement ok
CREATE TABLE t29494(x INT PRIMARY KEY); INSERT INTO t29494 VALUES (12)

statement ok
BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123

# Check that the new column is not visible
query T
SELECT create_statement FROM [SHOW CREATE t29494]
----
CREATE TABLE t29494 (
   x INT8 NOT NULL,
   CONSTRAINT "primary" PRIMARY KEY (x ASC),
   FAMILY "primary" (x)
)

# Check that the new column is not usable in RETURNING
statement error column "y" does not exist
DELETE FROM t29494 RETURNING y

statement ok
ROLLBACK

statement ok
BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123

query I
DELETE FROM t29494 RETURNING *
----
12

statement ok
COMMIT

subtest regression_33361

# Disable automatic stats to avoid flakiness (sometimes causes retry errors).
statement ok
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false

statement ok
CREATE TABLE t33361(x INT PRIMARY KEY, y INT UNIQUE, z INT); INSERT INTO t33361 VALUES (1, 2, 3)

statement ok
BEGIN; ALTER TABLE t33361 DROP COLUMN y

statement error column "y" does not exist
DELETE FROM t33361 RETURNING y

statement ok
ROLLBACK

statement ok
BEGIN; ALTER TABLE t33361 DROP COLUMN y

query II
DELETE FROM t33361 RETURNING *; COMMIT
----
1 3

# Test that delete works with column families (no indexes, so fast path).
statement ok
CREATE TABLE family (
    x INT PRIMARY KEY,
    y INT,
    FAMILY (x),
    FAMILY (y)
);
INSERT INTO family VALUES (1, 1), (2, 2), (3, 3)

statement ok
BEGIN; ALTER TABLE family ADD COLUMN z INT CREATE FAMILY

statement ok
DELETE FROM family WHERE x=2

statement ok
COMMIT

query III rowsort
SELECT x, y, z FROM family
----
1  1  NULL
3  3  NULL

statement error at or near "where": syntax error: unimplemented: this syntax
DELETE FROM family USING family, other_table WHERE x=2

# Verify that the fast path does its deletes at the expected timestamp.
statement ok
CREATE TABLE a (a INT PRIMARY KEY)

statement ok
INSERT INTO a SELECT generate_series(1,5)

let $ts
SELECT cluster_logical_timestamp()

statement ok
DELETE FROM a WHERE a <= 3

query I rowsort
SELECT * FROM a
----
4
5

query I rowsort
SELECT * FROM a AS OF SYSTEM TIME $ts
----
1
2
3
4
5

# Similar test with interleaved tables.
statement ok
INSERT INTO a SELECT generate_series(1,3)

statement ok
CREATE TABLE ab (
  a INT, b INT, PRIMARY KEY (a, b), FOREIGN KEY (a) REFERENCES a(a) ON DELETE CASCADE
) INTERLEAVE IN PARENT a(a)

statement ok
INSERT INTO ab SELECT x, x*10 FROM generate_series(1,5) AS g(x)

let $ts2
SELECT cluster_logical_timestamp()

statement ok
DELETE FROM a WHERE a <= 3

query I rowsort
SELECT * FROM a
----
4
5

query II rowsort
SELECT * FROM ab
----
4  40
5  50

query I rowsort
SELECT * FROM a AS OF SYSTEM TIME $ts2
----
1
2
3
4
5

query II rowsort
SELECT * FROM ab AS OF SYSTEM TIME $ts2
----
1  10
2  20
3  30
4  40
5  50