# 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/upsert # # 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 subtest strict statement ok CREATE TABLE ex( foo INT PRIMARY KEY, bar INT UNIQUE, baz INT ) statement count 1 INSERT INTO ex(foo,bar,baz) VALUES (1,1,1) statement count 0 INSERT INTO ex(foo,bar,baz) VALUES (1,1,1) ON CONFLICT DO NOTHING statement count 0 INSERT INTO ex(foo,bar,baz) VALUES (2,1,1) ON CONFLICT DO NOTHING # Do not insert conflicting first and last rows. statement count 2 INSERT INTO ex(foo,bar,baz) VALUES (1,2,1), (3,2,2), (6,6,2), (2,1,1) ON CONFLICT DO NOTHING query III colnames SELECT * from ex ORDER BY foo ---- foo bar baz 1 1 1 3 2 2 6 6 2 query III colnames INSERT INTO ex(foo,bar,baz) VALUES (4,3,1), (5,2,1) ON CONFLICT DO NOTHING RETURNING * ---- foo bar baz 4 3 1 statement ok CREATE TABLE ex2( a INT PRIMARY KEY, b INT UNIQUE, c INT, d INT, e INT, UNIQUE (c,d) ) statement count 1 INSERT INTO ex2(a,b,c,d,e) VALUES (0,0,0,0,0) statement count 0 INSERT INTO ex2(a,b,c,d,e) VALUES (1,0,1,1,0), (2,4,0,0,5) ON CONFLICT DO NOTHING statement count 3 INSERT INTO ex2(a,b,c,d,e) VALUES (3,4,5,6,7), (8,9,10,11,12), (13,14,15,16,17) ON CONFLICT DO NOTHING statement count 0 INSERT INTO ex2(a,b,c,d,e) VALUES (3,4,5,6,7), (8,9,10,11,12) ON CONFLICT DO NOTHING statement ok CREATE TABLE no_unique( a INT, b INT ) statement count 1 INSERT INTO no_unique(a,b) VALUES (1,2) statement count 1 INSERT INTO no_unique(a,b) VALUES (1,2) ON CONFLICT DO NOTHING statement count 3 INSERT INTO no_unique(a,b) VALUES (1,2), (1,3), (3,2) ON CONFLICT DO NOTHING query II colnames SELECT * from no_unique ORDER BY a, b ---- a b 1 2 1 2 1 2 1 3 3 2 statement count 3 INSERT INTO no_unique(a,b) VALUES (1,2), (1,2), (1,2) ON CONFLICT DO NOTHING subtest notstrict statement ok CREATE TABLE kv ( k INT PRIMARY KEY, v INT ) statement count 3 INSERT INTO kv VALUES (1, 1), (2, 2), (3, 3) ON CONFLICT (k) DO UPDATE SET v = excluded.v query II SELECT * FROM kv ORDER BY (k, v) ---- 1 1 2 2 3 3 statement error multiple assignments to the same column INSERT INTO kv VALUES (4, 4), (2, 5), (6, 6) ON CONFLICT (k) DO UPDATE SET v = 1, v = 1 statement count 3 INSERT INTO kv VALUES (4, 4), (2, 5), (6, 6) ON CONFLICT (k) DO UPDATE SET v = excluded.v statement count 3 UPSERT INTO kv VALUES (7, 7), (3, 8), (9, 9) statement count 1 INSERT INTO kv VALUES (1, 10) ON CONFLICT (k) DO UPDATE SET v = (SELECT CAST(sum(k) AS INT) FROM kv) statement error column reference "v" is ambiguous \(candidates: excluded.v, kv.v\) INSERT INTO kv VALUES (4, 10) ON CONFLICT (k) DO UPDATE SET v = v + 1 statement count 1 INSERT INTO kv VALUES (4, 10) ON CONFLICT (k) DO UPDATE SET v = kv.v + 20 statement error there is no unique or exclusion constraint matching the ON CONFLICT specification INSERT INTO kv VALUES (4, 10) ON CONFLICT DO UPDATE SET v = kv.v + 20 statement error duplicate key value \(k\)=\(3\) violates unique constraint "primary" INSERT INTO kv VALUES (2, 10) ON CONFLICT (k) DO UPDATE SET k = 3, v = 10 statement count 1 INSERT INTO kv VALUES (9, 9) ON CONFLICT (k) DO UPDATE SET (k, v) = (excluded.k + 2, excluded.v + 3) statement count 1 UPSERT INTO kv VALUES (10, 10) statement count 2 UPSERT INTO kv VALUES (10, 11), (10, 12) query II rowsort UPSERT INTO kv VALUES (11, 11), (10, 13) RETURNING k, v ---- 11 11 10 13 query I UPSERT INTO kv VALUES (11) RETURNING k ---- 11 query I UPSERT INTO kv VALUES (11, 12) RETURNING v ---- 12 statement count 1 INSERT INTO kv VALUES (13, 13), (7, 8) ON CONFLICT (k) DO NOTHING RETURNING * statement count 0 INSERT INTO kv VALUES (13, 13), (7, 8) ON CONFLICT DO NOTHING statement count 2 INSERT INTO kv VALUES (14, 14), (13, 15) ON CONFLICT (k) DO UPDATE SET v = excluded.v + 1 statement count 2 INSERT INTO kv VALUES (15, 15), (14, 16) ON CONFLICT (k) DO UPDATE SET k = excluded.k * 10 statement count 2 INSERT INTO kv VALUES (16, 16), (15, 17) ON CONFLICT (k) DO UPDATE SET k = excluded.k * 10, v = excluded.v query II SELECT * FROM kv ORDER BY (k, v) ---- 1 32 2 5 3 8 4 24 6 6 7 7 10 13 11 12 13 16 16 16 140 14 150 17 # TODO(knz): Enable the 1st statement and remove the 2nd once cockroach#33313 is fixed. #query II rowsort #UPSERT INTO kv(k) VALUES (6), (8) RETURNING k,v #---- #6 6 #8 NULL query II rowsort UPSERT INTO kv(k) VALUES (8) RETURNING k,v ---- 8 NULL query II rowsort INSERT INTO kv VALUES (10, 10), (11, 11) ON CONFLICT (k) DO UPDATE SET v = excluded.v RETURNING * ---- 10 10 11 11 query II rowsort INSERT INTO kv VALUES (10, 2), (11, 3) ON CONFLICT (k) DO UPDATE SET v = excluded.v + kv.v RETURNING * ---- 10 12 11 14 query II rowsort INSERT INTO kv VALUES (10, 14), (15, 15) ON CONFLICT (k) DO NOTHING RETURNING * ---- 15 15 statement ok CREATE TABLE abc ( a INT, b INT, c INT DEFAULT 7, PRIMARY KEY (a, b), INDEX y (b), UNIQUE INDEX z (c) ) statement error missing "b" primary key column UPSERT INTO abc (a, c) VALUES (1, 1) statement error missing "a" primary key column UPSERT INTO abc (b, c) VALUES (1, 1) statement count 1 INSERT INTO abc VALUES (1, 2, 3) statement count 1 INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT (c) DO UPDATE SET a = 4 query III SELECT * FROM abc ---- 4 2 3 statement count 1 INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT (c) DO UPDATE SET b = 5 statement count 1 INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT (c) DO UPDATE SET c = 6 query III SELECT * FROM abc ---- 4 5 6 statement count 1 INSERT INTO abc (a, b) VALUES (1, 2) ON CONFLICT (a, b) DO UPDATE SET a = 1, b = 2 statement count 1 INSERT INTO abc (a, b) VALUES (4, 5) ON CONFLICT (a, b) DO UPDATE SET a = 7, b = 8 query III SELECT * FROM abc ORDER BY (a, b, c) ---- 1 2 7 7 8 6 statement count 1 DELETE FROM abc where a = 1 statement count 1 UPSERT INTO abc VALUES (1, 2) query III SELECT * FROM abc ORDER BY (a, b, c) ---- 1 2 7 7 8 6 statement count 1 UPSERT INTO abc VALUES (1, 2, 5) query III SELECT * FROM abc ORDER BY (a, b, c) ---- 1 2 5 7 8 6 statement count 1 UPSERT INTO abc VALUES (1, 2) query III SELECT * FROM abc ORDER BY (a, b, c) ---- 1 2 7 7 8 6 statement count 1 DELETE FROM abc where a = 1 statement count 1 INSERT INTO abc VALUES (7, 8, 9) ON CONFLICT (a, b) DO UPDATE SET c = DEFAULT query III SELECT * FROM abc ORDER BY (a, b, c) ---- 7 8 7 statement ok CREATE TABLE excluded (a INT PRIMARY KEY, b INT) statement error ambiguous source name: "excluded" INSERT INTO excluded VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = excluded.b # Tests for upsert/on conflict returning statement ok CREATE TABLE upsert_returning (a INT PRIMARY KEY, b INT, c INT, d INT DEFAULT -1) statement count 1 INSERT INTO upsert_returning VALUES (1, 1, NULL) # Handle INSERT ... ON CONFLICT ... RETURNING query IIII rowsort INSERT INTO upsert_returning (a, c) VALUES (1, 1), (2, 2) ON CONFLICT (a) DO UPDATE SET c = excluded.c RETURNING * ---- 1 1 1 -1 2 NULL 2 -1 # Handle INSERT ... ON CONFLICT DO NOTHING ... RETURNING query IIII INSERT INTO upsert_returning (a, c) VALUES (1, 1), (3, 3) ON CONFLICT (a) DO NOTHING RETURNING * ---- 3 NULL 3 -1 # Handle UPSERT ... RETURNING query IIII rowsort UPSERT INTO upsert_returning (a, c) VALUES (1, 10), (3, 30) RETURNING * ---- 1 1 10 -1 3 NULL 30 -1 # Ensure returned values are inserted values after conflict resolution query I SELECT b FROM upsert_returning WHERE a = 1 ---- 1 query I INSERT INTO upsert_returning (a, b) VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = excluded.b + upsert_returning.b + 1 RETURNING b ---- 3 # Handle expressions within returning clause query I rowsort UPSERT INTO upsert_returning (a, b) VALUES (1, 2), (2, 3), (4, 3) RETURNING a+b+d ---- 2 4 6 # Handle upsert fast path with autocommit query IIII rowsort UPSERT INTO upsert_returning VALUES (1, 2, 3, 4), (5, 6, 7, 8) RETURNING * ---- 1 2 3 4 5 6 7 8 # Handle upsert fast path without autocommit statement ok BEGIN query IIII rowsort upsert INTO upsert_returning VALUES (1, 5, 4, 3), (6, 5, 4, 3) RETURNING * ---- 1 5 4 3 6 5 4 3 statement ok COMMIT # For materialize#22300. Test UPSERT ... RETURNING with UNION. query I rowsort SELECT a FROM [UPSERT INTO upsert_returning VALUES (7) RETURNING a] UNION VALUES (8) ---- 7 8 # For materialize#6710. Add an unused column to disable the fast path which doesn't have this bug. statement ok CREATE TABLE issue_6710 (a INT PRIMARY KEY, b STRING, c INT) statement count 2 INSERT INTO issue_6710 (a, b) VALUES (1, 'foo'), (2, 'bar') statement count 2 UPSERT INTO issue_6710 (a, b) VALUES (1, 'test1'), (2, 'test2') query IT rowsort SELECT a, b from issue_6710 ---- 1 test1 2 test2 statement ok CREATE TABLE issue_13962 (a INT PRIMARY KEY, b INT, c INT) statement count 1 INSERT INTO issue_13962 VALUES (1, 1, 1) statement count 1 INSERT INTO issue_13962 VALUES (1, 2, 2) ON CONFLICT (a) DO UPDATE SET b = excluded.b query III SELECT * FROM issue_13962 ---- 1 2 1 statement ok CREATE TABLE issue_14052 (a INT PRIMARY KEY, b INT, c INT) statement count 2 INSERT INTO issue_14052 (a, b) VALUES (1, 1), (2, 2) statement count 2 UPSERT INTO issue_14052 (a, c) (SELECT a, b from issue_14052) statement ok CREATE TABLE issue_14052_2 ( id SERIAL PRIMARY KEY, name VARCHAR(255), createdAt INT, updatedAt INT ) statement count 1 INSERT INTO issue_14052_2 (id, name, createdAt, updatedAt) VALUES (1, 'original', 1, 1) # Make sure the fast path isn't taken (createdAt is not in the ON CONFLICT clause) statement count 1 INSERT INTO issue_14052_2 (id, name, createdAt, updatedAt) VALUES (1, 'UPDATED', 2, 2) ON CONFLICT (id) DO UPDATE SET id = excluded.id, name = excluded.name, updatedAt = excluded.updatedAt query ITII SELECT * FROM issue_14052_2; ---- 1 UPDATED 1 2 statement error multiple assignments to the same column INSERT INTO issue_14052_2 (id, name, createdAt, updatedAt) VALUES (1, 'FOO', 3, 3) ON CONFLICT (id) DO UPDATE SET id = excluded.id, name = excluded.name, name = excluded.name, name = excluded.name # Make sure the fast path isn't taken (all clauses in the set must be of the form x = excluded.x) statement count 1 INSERT INTO issue_14052_2 (id, name, createdAt, updatedAt) VALUES (1, 'BAR', 4, 5) ON CONFLICT (id) DO UPDATE SET name = excluded.name, createdAt = excluded.updatedAt, updatedAt = excluded.updatedAt query ITII SELECT * FROM issue_14052_2; ---- 1 BAR 5 5 # Make sure the column types are propagated when type checking the ON CONFLICT # expressions. See materialize#16873. statement ok CREATE TABLE issue_16873 (col int PRIMARY KEY, date TIMESTAMP); # n.b. the fully-qualified names below are required, as there are two providers of # the column named `col` here, the original table and the `excluded` pseudo-table. statement count 1 INSERT INTO issue_16873 VALUES (1,clock_timestamp()) ON CONFLICT (col) DO UPDATE SET date = clock_timestamp() WHERE issue_16873.col = 1; statement count 1 INSERT INTO issue_16873 VALUES (1,clock_timestamp()) ON CONFLICT (col) DO UPDATE SET date = clock_timestamp() WHERE issue_16873.col = 1; # For materialize#17339. Support WHERE clause in ON CONFLICT handling. statement ok CREATE TABLE issue_17339 (a int primary key, b int); statement count 2 INSERT INTO issue_17339 VALUES (1, 1), (2, 0); statement count 1 INSERT INTO issue_17339 VALUES (1, 0), (2, 2) ON CONFLICT (a) DO UPDATE SET b = excluded.b WHERE excluded.b > issue_17339.b; query II SELECT * FROM issue_17339 ORDER BY a; ---- 1 1 2 2 statement count 2 INSERT INTO issue_17339 VALUES (1, 0), (2, 1) ON CONFLICT (a) DO UPDATE SET b = excluded.b WHERE TRUE; query II SELECT * FROM issue_17339 ORDER BY a; ---- 1 0 2 1 # Regression test for materialize#25726. # UPSERT over tables with column families, on the fast path, use the # INSERT logic. This has special casing for column families of 1 # column, and another special casing for column families of 2+ # columns. The special casing is only for families that do not include # the primary key. So we need a table with 3 families: 1 for the PK, 1 # with just 1 col, and 1 with 2+ cols. 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 UPSERT INTO tu VALUES (1, NULL, NULL, NULL) query IIII rowsort SELECT * FROM tu ---- 1 NULL NULL NULL subtest check statement ok CREATE TABLE ab( a INT PRIMARY KEY, b INT, CHECK (b < 1) ) statement count 1 INSERT INTO ab(a, b) VALUES (1, 0); statement error pq: failed to satisfy CHECK constraint \(b < 1\) INSERT INTO ab(a, b) VALUES (1, 0) ON CONFLICT(a) DO UPDATE SET b=12312313; statement count 1 INSERT INTO ab(a, b) VALUES (1, 0) ON CONFLICT(a) DO UPDATE SET b=-1; statement ok CREATE TABLE abc_check( a INT PRIMARY KEY, b INT, c INT, CHECK (b < 1), CHECK (c > 1) ) statement count 1 INSERT INTO abc_check(a, b, c) VALUES (1, 0, 2); statement error pq: failed to satisfy CHECK constraint \(b < 1\) INSERT INTO abc_check(a, b, c) VALUES (1, 0, 2) ON CONFLICT(a) DO UPDATE SET b=12312313; statement error pq: failed to satisfy CHECK constraint \(b < 1\) INSERT INTO abc_check(a, b, c) VALUES (1, 0, 2) ON CONFLICT(a) DO UPDATE SET (b, c) = (1, 1); statement error pq: failed to satisfy CHECK constraint \(c > 1\) INSERT INTO abc_check(a, b, c) VALUES (1, 0, 2) ON CONFLICT(a) DO UPDATE SET (b, c) = (-1, 1); statement count 1 INSERT INTO abc_check(a, b, c) VALUES (2, 0, 3); statement error pq: failed to satisfy CHECK constraint \(b < 1\) INSERT INTO abc_check(c, a, b) VALUES (3, 2, 0) ON CONFLICT(a) DO UPDATE SET b=12312313; statement error pq: failed to satisfy CHECK constraint \(b < 1\) INSERT INTO abc_check(a, c) VALUES (2, 3) ON CONFLICT(a) DO UPDATE SET b=12312313; statement error pq: failed to satisfy CHECK constraint \(c > 1\) INSERT INTO abc_check(a, c) VALUES (2, 3) ON CONFLICT(a) DO UPDATE SET c=1; statement error pq: failed to satisfy CHECK constraint \(c > 1\) INSERT INTO abc_check(c, a) VALUES (3, 2) ON CONFLICT(a) DO UPDATE SET c=1; statement error pq: failed to satisfy CHECK constraint \(b < 1\) INSERT INTO abc_check(c, a) VALUES (3, 2) ON CONFLICT(a) DO UPDATE SET b=123123123; statement error pq: failed to satisfy CHECK constraint \(b < 1\) INSERT INTO abc_check(c, a) VALUES (3, 2) ON CONFLICT(a) DO UPDATE SET b=123123123; subtest 29495 statement ok CREATE TABLE IF NOT EXISTS example ( id SERIAL PRIMARY KEY ,value string NOT NULL ); query B UPSERT INTO example (value) VALUES ('foo') RETURNING id > 0 ---- true statement ok DROP TABLE example 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)); statement error failed to satisfy CHECK constraint UPSERT INTO tn(x) VALUES (NULL) statement error failed to satisfy CHECK constraint UPSERT INTO tn(y) VALUES ('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)); statement error null value in column "x" violates not-null constraint UPSERT INTO tn2(x) VALUES (NULL) statement error value too long for type CHAR\(3\) UPSERT INTO tn2(x, y) VALUES (123, 'abcd') subtest regression_29494 statement ok CREATE TABLE t29494(x INT); 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 NULL, FAMILY "primary" (x, rowid) ) # Check that the new column is not usable in RETURNING statement error column "y" does not exist UPSERT INTO t29494(x) VALUES (123) RETURNING y # Ditto for INSERT ON CONFLICT statement ok ROLLBACK; BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 statement error column "y" does not exist INSERT INTO t29494(x) VALUES (123) ON CONFLICT(rowid) DO UPDATE SET x = 400 RETURNING y statement ok ROLLBACK statement ok BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 query I UPSERT INTO t29494(x) VALUES (12) RETURNING * ---- 12 query I UPSERT INTO t29494(x) VALUES (123) RETURNING * ---- 123 query I INSERT INTO t29494(x) VALUES (123) ON CONFLICT(rowid) DO UPDATE SET x = 400 RETURNING * ---- 123 statement ok COMMIT subtest regression_31255 statement ok CREATE TABLE tc(x INT PRIMARY KEY, y INT AS (x+1) STORED) statement error cannot write directly to computed column "y" INSERT INTO tc(x) VALUES (1) ON CONFLICT(x) DO UPDATE SET y = 123 statement error cannot write directly to computed column "y" UPSERT INTO tc(x,y) VALUES (1,2) statement error cannot write directly to computed column "y" UPSERT INTO tc VALUES (1,2) subtest regression_29497 statement ok CREATE TABLE t29497(x INT PRIMARY KEY); BEGIN; ALTER TABLE t29497 ADD COLUMN y INT NOT NULL DEFAULT 123 statement error [UPSERT has more expressions than target columns | column "y" is being backfilled] UPSERT INTO t29497 VALUES (1, 2) statement ok ROLLBACK; BEGIN; ALTER TABLE t29497 ADD COLUMN y INT NOT NULL DEFAULT 123 statement error [column "y" does not exist | column "y" is being backfilled] INSERT INTO t29497(x) VALUES (1) ON CONFLICT (x) DO UPDATE SET y = 456 statement ok ROLLBACK subtest visible_returning_columns statement ok BEGIN; ALTER TABLE tc DROP COLUMN y query I colnames,rowsort UPSERT INTO tc VALUES (1), (2) RETURNING * ---- x 1 2 statement ok COMMIT subtest regression_32762 statement ok CREATE TABLE t32762(x INT, y INT, UNIQUE (x,y), CONSTRAINT y_not_null CHECK (y IS NOT NULL)) statement ok INSERT INTO t32762(x,y) VALUES (1,2) ON CONFLICT (x,y) DO UPDATE SET x = t32762.x; statement ok INSERT INTO t32762(x,y) VALUES (1,2) ON CONFLICT (x,y) DO UPDATE SET x = t32762.x subtest regression_33313 statement ok CREATE TABLE ex33313(foo INT PRIMARY KEY, bar INT UNIQUE, baz INT); INSERT INTO ex33313 VALUES (1,1,1); statement count 1 INSERT INTO ex33313(foo,bar,baz) VALUES (1,2,1), (3,2,2) ON CONFLICT DO NOTHING; query III colnames SELECT * FROM ex33313 ORDER BY foo ---- foo bar baz 1 1 1 3 2 2 # Use Upsert with indexed table, default columns, computed columns, and check # columns. statement ok CREATE TABLE indexed ( a DECIMAL PRIMARY KEY, b DECIMAL, c DECIMAL DEFAULT(10.0), d DECIMAL AS (a + c) STORED, UNIQUE INDEX secondary (d, b), CHECK (c > 0) ) statement ok INSERT INTO indexed VALUES (1, 1, 1); INSERT INTO indexed VALUES (2, 2, 2) # Use implicit target columns (should set default and computed values). statement ok UPSERT INTO indexed VALUES (1.0) query TTTT colnames SELECT * FROM indexed@secondary ORDER BY d, b ---- a b c d 2 2 2 4 1 NULL 10.0 11.0 # Explicitly specify all target columns. Ensure that primary key is not updated, # even though an alternate but equal decimal form is in use (1.0 vs. 1). statement ok UPSERT INTO indexed (a, b, c) VALUES (1.0, 1.0, 1.0) query TTTT colnames SELECT * FROM indexed@secondary ORDER BY d, b ---- a b c d 1 1.0 1.0 2.0 2 2 2 4 # Ensure that explicit target column does not disturb existing "b" value, but # does update the computed column. statement ok UPSERT INTO indexed (c, a) VALUES (2, 1) query TTTT colnames SELECT * FROM indexed@secondary ORDER BY d, b ---- a b c d 1 1.0 2 3 2 2 2 4 # Final check to ensure that primary index is correct. query TTTT colnames SELECT * FROM indexed@primary ORDER BY a ---- a b c d 1 1.0 2 3 2 2 2 4 # Drop the secondary index, allowing the "blind upsert" path to run. statement ok DROP INDEX indexed@secondary CASCADE # Use implicit target columns (should set default and computed values). statement ok UPSERT INTO indexed VALUES (1, 1) query TTTT colnames,rowsort SELECT * FROM indexed ---- a b c d 1 1 10.0 11.0 2 2 2 4 # Explicitly specify all target columns. statement ok UPSERT INTO indexed (a, b, c) SELECT 1, 2, 3 query TTTT colnames,rowsort SELECT * FROM indexed ---- a b c d 2 2 2 4 1 2 3 4 # Ensure that explicit target column does not disturb existing "b" value, but # does update the computed column. query TTTT UPSERT INTO indexed (c, a) VALUES (2.0, 1.0) RETURNING * ---- 1 2 2.0 3.0 query TTTT colnames,rowsort SELECT * FROM indexed ---- a b c d 1 2 2.0 3.0 2 2 2 4 statement ok DROP TABLE indexed subtest regression_35040 statement ok CREATE TABLE test35040(a INT PRIMARY KEY, b INT NOT NULL, c INT2) statement ok INSERT INTO test35040(a,b) VALUES(0,0) ON CONFLICT(a) DO UPDATE SET b = NULL statement error null value in column "b" violates not-null constraint INSERT INTO test35040(a,b) VALUES(0,0) ON CONFLICT(a) DO UPDATE SET b = NULL statement error integer out of range for type int2 INSERT INTO test35040(a,b) VALUES (0,1) ON CONFLICT(a) DO UPDATE SET c = 111111111; statement ok DROP TABLE test35040 # ------------------------------------------------------------------------------ # Regression for cockroach#35364. # ------------------------------------------------------------------------------ subtest regression_35364 statement ok CREATE TABLE t35364(x INT PRIMARY KEY, y DECIMAL(10,1) CHECK(y >= 8.0), UNIQUE INDEX (y)) statement ok INSERT INTO t35364(x, y) VALUES (1, 10.2) # 10.18 should be mapped to 10.2 before the left outer join so that the conflict # can be detected, and 7.95 should be mapped to 8.0 so that check constraint # will pass. statement ok INSERT INTO t35364(x, y) VALUES (2, 10.18) ON CONFLICT (y) DO UPDATE SET y=7.95 query IT SELECT * FROM t35364 ---- 1 8.0 statement ok DROP TABLE t35364 # Check UPSERT syntax. statement ok CREATE TABLE t35364( x DECIMAL(10,0) CHECK (x >= 0) PRIMARY KEY, y DECIMAL(10,0) CHECK (y >= 0) ) statement ok UPSERT INTO t35364 (x) VALUES (-0.1) query TT SELECT * FROM t35364 ---- -0 NULL statement ok UPSERT INTO t35364 (x, y) VALUES (-0.2, -0.3) query TT SELECT * FROM t35364 ---- -0 -0 statement ok UPSERT INTO t35364 (x, y) VALUES (1.5, 2.5) query TT rowsort SELECT * FROM t35364 ---- -0 -0 2 3 statement ok INSERT INTO t35364 (x) VALUES (1.5) ON CONFLICT (x) DO UPDATE SET x=2.5, y=3.5 query TT rowsort SELECT * FROM t35364 ---- -0 -0 3 4 # ------------------------------------------------------------------------------ # Regression for cockroach#35970. # ------------------------------------------------------------------------------ statement ok CREATE TABLE table35970 ( a DECIMAL(10,1) PRIMARY KEY, b DECIMAL(10,1), c DECIMAL(10,0), FAMILY fam0 (a, b), FAMILY fam1 (c) ) query I UPSERT INTO table35970 (a) VALUES (1.5) RETURNING b ---- NULL query I INSERT INTO table35970 VALUES (1.5, 1.5, NULL) ON CONFLICT (a) DO UPDATE SET c = table35970.a+1 RETURNING b ---- NULL