Materialize/test/sqllogictest/cockroach/update.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/update
#
# 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.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

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

# statement error value type tuple\{int, int\} doesn't match type int of column "v"
# UPDATE kv SET v = (SELECT (10, 11))

# statement error value type decimal doesn't match type int of column "v"
# UPDATE kv SET v = 3.2

# statement error value type decimal doesn't match type int of column "v"
# UPDATE kv SET (k, v) = (3, 3.2)

# statement error value type decimal doesn't match type int of column "v"
# UPDATE kv SET (k, v) = (SELECT 3, 3.2)

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

statement count 2
UPDATE kv SET v = 9 WHERE k IN (1, 3)

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

statement count 4
UPDATE kv SET v = k + v

query II rowsort
SELECT * FROM kv
----
1 10
3 12
5 11
7 15

statement error pgcode 42703 unknown column m
UPDATE kv SET m = 9 WHERE k IN (1, 3)

# statement error unimplemented at or near "k"
# UPDATE kv SET kv.k = 9

# statement error unimplemented at or near "*"
# UPDATE kv SET k.* = 9

# statement error unimplemented at or near "v"
# UPDATE kv SET k.v = 9

statement ok
CREATE VIEW kview as SELECT k,v from kv

query II rowsort
SELECT * FROM kview
----
1 10
3 12
5 11
7 15

# statement error "kview" is not a table
# UPDATE kview SET v = 99 WHERE k IN (1, 3)

query II rowsort
SELECT * FROM kview
----
1 10
3 12
5 11
7 15

statement ok
CREATE TABLE kv2 (
  k CHAR PRIMARY KEY,
  v CHAR,
  UNIQUE (v)
)

statement count 4
INSERT INTO kv2 VALUES ('a', 'b'), ('c', 'd'), ('e', 'f'), ('f', 'g')

query TT rowsort
SELECT * FROM kv2
----
a   b
c   d
e   f
f   g

# TODO: enforce primary keys.
# statement error duplicate key value violates unique constraint "kv2_v_key"
# UPDATE kv2 SET v = 'g' WHERE k IN ('a')

statement count 1
UPDATE kv2 SET v = 'i' WHERE k IN ('a')

query TT rowsort
SELECT * FROM kv2
----
a   i
c   d
e   f
f   g

statement count 1
UPDATE kv2 SET v = 'b' WHERE k IN ('a')

query TT rowsort
SELECT * FROM kv2
----
a   b
c   d
e   f
f   g

statement ok
CREATE TABLE kv3 (
  k CHAR PRIMARY KEY,
  v CHAR NOT NULL
)

statement count 1
INSERT INTO kv3 VALUES ('a', 'b')

statement error null value in column "v" violates not-null constraint
UPDATE kv3 SET v = NULL WHERE k = 'a'

query TT
SELECT * FROM kv3
----
a   b

statement error column "nonexistent" does not exist
UPDATE kv3 SET v = NULL WHERE nonexistent = 'a'

# TODO(benesch): support the rest of this file.
halt

statement ok
CREATE TABLE abc (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  UNIQUE INDEX d (c)
)

statement count 1
INSERT INTO abc VALUES (1, 2, 3)

statement error number of columns \(2\) does not match number of values \(1\)
UPDATE abc SET (b, c) = (4)

statement error DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET
UPDATE abc SET (b, c) = (SELECT (VALUES (DEFAULT, DEFAULT)))

statement count 1
UPDATE abc SET (b, c) = (4, 5)

query III
SELECT * FROM abc
----
1 4 5

statement count 1
UPDATE abc SET a = 1, (b, c) = (SELECT 1, 2)

query III colnames
UPDATE abc SET (b, c) = (8, 9) RETURNING abc.b, c, 4 AS d
----
b  c  d
8  9  4

query III colnames
UPDATE abc SET (b, c) = (8, 9) RETURNING b as col1, c as col2, 4 as col3
----
col1 col2 col3
8    9    4

query I colnames
UPDATE abc SET (b, c) = (8, 9) RETURNING a
----
a
1

query IIII colnames
UPDATE abc SET (b, c) = (5, 6) RETURNING a, b, c, 4 AS d
----
a  b  c  d
1  5  6  4

query III colnames
UPDATE abc SET (b, c) = (7, 8) RETURNING *
----
a b c
1 7 8

query IIII colnames
UPDATE abc SET (b, c) = (7, 8) RETURNING *, 4 AS d
----
a  b  c  d
1  7  8  4

query III colnames
UPDATE abc SET (b, c) = (8, 9) RETURNING abc.*
----
a b c
1 8 9

statement error pq: "abc.*" cannot be aliased
UPDATE abc SET (b, c) = (8, 9) RETURNING abc.* as x

query III
SELECT * FROM abc
----
1 8 9

statement count 1
INSERT INTO abc VALUES (4, 5, 6)

statement error duplicate key value \(a\)=\(4\) violates unique constraint "primary"
UPDATE abc SET a = 4, b = 3

statement error duplicate key value \(c\)=\(6\) violates unique constraint "d"
UPDATE abc SET a = 2, c = 6

query III
UPDATE abc SET a = 2, b = 3 WHERE a = 1 RETURNING *
----
2 3 9

query III rowsort
SELECT * FROM abc
----
2 3 9
4 5 6

query III
SELECT * FROM abc@d WHERE c = 9
----
2 3 9

statement error multiple assignments to the same column "b"
UPDATE abc SET b = 10, b = 11

statement error multiple assignments to the same column "b"
UPDATE abc SET (b, b) = (10, 11)

statement error multiple assignments to the same column "b"
UPDATE abc SET (b, c) = (10, 11), b = 12

statement ok
CREATE TABLE xyz (
  x INT PRIMARY KEY,
  y INT,
  z INT
)

statement count 1
INSERT INTO xyz VALUES (111, 222, 333)


# TODO(jordan): re-enable post cockroach#28716
# statement count 1
# UPDATE xyz SET (z, y) = (SELECT 666, 777), x = (SELECT 2)
#
# query III
# SELECT * from xyz
# ----
# 2 777 666

statement ok
CREATE TABLE lots (
  k1 INT,
  k2 INT,
  k3 INT,
  k4 INT,
  k5 INT
)

statement count 1
INSERT INTO lots VALUES (1, 2, 3, 4, 5)

statement count 1
UPDATE lots SET (k1, k2) = (6, 7), k3 = 8, (k4, k5) = (9, 10)

query IIIII
SELECT * FROM lots
----
6  7  8  9  10

statement count 1
UPDATE lots SET (k5, k4, k3, k2, k1) = (SELECT * FROM lots)

query IIIII
SELECT * FROM lots
----
10  9  8  7  6

statement ok
CREATE TABLE pks (
  k1 INT,
  k2 INT,
  v INT,
  PRIMARY KEY (k1, k2),
  UNIQUE INDEX i (k2, v),
  FAMILY (k1, k2),
  FAMILY (v)
)

statement count 2
INSERT INTO pks VALUES (1, 2, 3), (4, 5, 3)

statement error duplicate key value \(k2,v\)=\(5,3\) violates unique constraint "i"
UPDATE pks SET k2 = 5 where k1 = 1

# Test updating only one of the columns of a multi-column primary key.

statement count 1
UPDATE pks SET k1 = 2 WHERE k1 = 1

query III rowsort
SELECT * FROM pks
----
2  2  3
4  5  3

# Check that UPDATE properly supports ORDER BY (MySQL extension)

statement count 0
TRUNCATE kv

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

query II
UPDATE kv SET v = v + 1 ORDER BY v DESC LIMIT 3 RETURNING k,v
----
1  10
3  8
6  5

# Check that UPDATE properly supports LIMIT (MySQL extension)

statement count 3
TRUNCATE kv; INSERT INTO kv VALUES (1, 2), (2, 3), (3, 4)

query II
UPDATE kv SET v = v - 1 WHERE k < 10 ORDER BY k LIMIT 1 RETURNING k, v
----
1  1

query II rowsort
SELECT * FROM kv
----
1  1
2  3
3  4

# Check that updates on tables with multiple column families behave as
# they should.

statement ok
CREATE TABLE tu (a INT PRIMARY KEY, b INT, c INT, d INT, FAMILY (a), FAMILY (b), FAMILY (c,d));
  INSERT INTO tu VALUES (1, 2, 3, 4)

statement ok
UPDATE tu SET b = NULL, c = NULL, d = NULL

query IIII rowsort
SELECT * FROM tu
----
1 NULL NULL NULL

subtest contraint_check_validation_ordering

# Verification of column constraints vs CHECK handling. The column
# constraint verification must take place first.
#
# This test requires that the error message for a CHECK constraint
# validation error be different than a column validation error. So we
# test the former first, as a sanity check.
statement ok
CREATE TABLE tn(x INT NULL CHECK(x IS NOT NULL), y CHAR(4) CHECK(length(y) < 4));
  INSERT INTO tn(x, y) VALUES (123, 'abc');

statement error failed to satisfy CHECK constraint
UPDATE tn SET x = NULL

statement error failed to satisfy CHECK constraint
UPDATE tn SET y = 'abcd'

# Now we test that the column validation occurs before the CHECK constraint.
statement ok
CREATE TABLE tn2(x INT NOT NULL CHECK(x IS NOT NULL), y CHAR(3) CHECK(length(y) < 4));
  INSERT INTO tn2(x, y) VALUES (123, 'abc');

statement error null value in column "x" violates not-null constraint
UPDATE tn2 SET x = NULL

statement error value too long for type CHAR\(3\)
UPDATE tn2 SET y = 'abcd'

subtest fk_contraint_check_validation_ordering

# Verify that column constraints and CHECK handling occur before
# foreign key validation.
statement ok
CREATE TABLE src(x VARCHAR PRIMARY KEY);
  INSERT INTO src(x) VALUES ('abc');
  CREATE TABLE derived(x CHAR(3) REFERENCES src(x),
                       y VARCHAR CHECK(length(y) < 4) REFERENCES src(x));
  INSERT INTO derived(x, y) VALUES ('abc', 'abc')

# Sanity check that the FK constraints gets actually validated
statement error foreign key violation
UPDATE derived SET x = 'xxx'

statement error value too long for type CHAR\(3\)
UPDATE derived SET x = 'abcd'

statement error failed to satisfy CHECK constraint
UPDATE derived SET y = 'abcd'

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
UPDATE t29494 SET x = 123 RETURNING y

# Check the new column is not assignable. We need to restart
# the txn because the error above trashed it.
statement ok
ROLLBACK; BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123

# Returning * doesn't return y
query I
UPDATE t29494 SET x = 124 WHERE x = 12 RETURNING *
----
124

statement error column "y" does not exist
UPDATE t29494 SET y = 123

# Check the new column is not usable in assignments. We need to
# restart the txn because the error above trashed it.
statement ok
ROLLBACK; BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123

statement error column "y" is being backfilled
UPDATE t29494 SET x = y

statement ok
COMMIT

# Use delete-only mutation columns with default and computed expressions.
statement ok
CREATE TABLE mutation (m INT PRIMARY KEY, n INT)

statement ok
INSERT INTO mutation VALUES (1, 1)

statement ok
BEGIN; ALTER TABLE mutation add COLUMN o INT DEFAULT(10), ADD COLUMN p INT AS (o + n) STORED

statement ok
UPDATE mutation SET m=2 WHERE n=1

statement ok
COMMIT TRANSACTION

query IIII colnames
SELECT * FROM mutation
----
m  n  o   p
2  1  10  11

#regression test for cockroach#32477
subtest reject_special_funcs_inset

statement ok
CREATE TABLE t32477(x) AS SELECT 1

statement error aggregate functions are not allowed in UPDATE SET
UPDATE t32477 SET x = count(x)

statement error window functions are not allowed in UPDATE SET
UPDATE t32477 SET x = rank() OVER ()

statement error generator functions are not allowed in UPDATE SET
UPDATE t32477 SET x = generate_series(1,2)

#regression test for cockroach#32054
subtest empty_update_subquery

statement ok
CREATE TABLE t32054(x,y) AS SELECT 1,2

statement ok
CREATE TABLE t32054_empty(x INT, y INT)

statement ok
UPDATE t32054 SET (x,y) = (SELECT x,y FROM t32054_empty)

query II
SELECT * FROM t32054
----
NULL  NULL

# ------------------------------------------------------------------------------
# Regression for cockroach#35364.
# ------------------------------------------------------------------------------
subtest regression_35364

statement ok
CREATE TABLE t35364(x DECIMAL(1,0) CHECK (x >= 1))

statement ok
INSERT INTO t35364 VALUES (2)

statement ok
UPDATE t35364 SET x=0.5

query T
SELECT x FROM t35364
----
1

# ------------------------------------------------------------------------------
# Regression for cockroach#35970.
# ------------------------------------------------------------------------------
statement ok
CREATE TABLE table35970 (
    a INT PRIMARY KEY,
    b INT,
    c INT8[],
    FAMILY fam0 (a, b),
    FAMILY fam1 (c)
)

statement ok
INSERT INTO table35970 VALUES (1, 1, NULL);

query I
UPDATE table35970
SET c = c
RETURNING b
----
1