delete.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/delete
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

# not supported yet
halt

mode cockroach

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