insert.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/insert
#
# 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 error pgcode 42P01 relation "kv" does not exist
INSERT INTO kv VALUES ('a', 'b')

statement ok
CREATE TABLE kv (
  k VARCHAR PRIMARY KEY,
  v VARCHAR,
  UNIQUE INDEX a (v),
  FAMILY (k),
  FAMILY (v)
)

query TT
SELECT * FROM kv
----

statement ok
INSERT INTO kv VALUES ('A')

statement error missing "k" primary key column
INSERT INTO kv (v) VALUES ('a')

statement ok
INSERT INTO kv (k) VALUES ('nil1')

statement ok
INSERT INTO kv (k) VALUES ('nil2')

statement ok
INSERT INTO kv VALUES ('nil3', NULL)

statement ok
INSERT INTO kv VALUES ('nil4', NULL)

statement ok
INSERT INTO kv (k,v) VALUES ('a', 'b'), ('c', 'd')

query T
SELECT v || 'hello' FROM [INSERT INTO kv VALUES ('e', 'f'), ('g', '') RETURNING v]
----
fhello
hello

statement error pgcode 23505 duplicate key value \(v\)=\('f'\) violates unique constraint "a"
INSERT INTO kv VALUES ('h', 'f')

statement ok
INSERT INTO kv VALUES ('f', 'g')

statement error duplicate key value \(v\)=\('g'\) violates unique constraint "a"
INSERT INTO kv VALUES ('h', 'g')

query TT
SELECT * FROM kv ORDER BY k
----
A     NULL
a     b
c     d
e     f
f     g
g     ·
nil1  NULL
nil2  NULL
nil3  NULL
nil4  NULL

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

statement ok
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

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

statement error null value in column "v" violates not-null constraint
INSERT INTO kv3 VALUES ('a')

statement error null value in column "v" violates not-null constraint
INSERT INTO kv3 VALUES ('a', NULL)

statement error null value in column "v" violates not-null constraint
INSERT INTO kv3 (k) VALUES ('a')

query TT
SELECT * FROM kv3
----

statement ok
CREATE TABLE kv4 (
  int INT PRIMARY KEY,
  bit BIT,
  bool BOOLEAN,
  char CHAR,
  float FLOAT
)

statement error could not parse "a" as type int
INSERT INTO kv4 (int) VALUES ('a')

statement ok
INSERT INTO kv4 (int) VALUES (1)

statement error could not parse string as bit array: "a" is not a valid binary digit
INSERT INTO kv4 (int, bit) VALUES (2, 'a')

statement ok
INSERT INTO kv4 (int, bit) VALUES (2, B'1')

statement error could not parse "a" as type bool
INSERT INTO kv4 (int, bool) VALUES (3, 'a')

statement ok
INSERT INTO kv4 (int, bool) VALUES (3, true)

statement error value type int doesn't match type char of column "char"
INSERT INTO kv4 (int, char) VALUES (4, 1)

statement ok
INSERT INTO kv4 (int, char) VALUES (4, 'a')

statement error value type int doesn't match type float of column "float"
INSERT INTO kv4 (int, float) VALUES (5, 1::INT)

statement ok
INSERT INTO kv4 (int, float) VALUES (5, 2.3)

query ITBTR rowsort
SELECT * from kv4
----
1  NULL  NULL  NULL  NULL
2  1     NULL  NULL  NULL
3  NULL  true  NULL  NULL
4  NULL  NULL  a     NULL
5  NULL  NULL  NULL  2.3

statement ok
CREATE TABLE kv5 (
  k CHAR PRIMARY KEY,
  v CHAR,
  UNIQUE INDEX a (v, k)
)

statement ok
INSERT INTO kv5 VALUES ('a', NULL)

statement error VALUES lists must all be the same length, expected 1 columns, found 2
INSERT INTO kv5 VALUES ('b'), ('c', DEFAULT)

query TT
SELECT v, k FROM kv5@a
----
NULL a

statement error INSERT has more expressions than target columns, 3 expressions for 2 targets
INSERT INTO kv SELECT 'a', 'b', 'c'

statement error INSERT has more expressions than target columns, 2 expressions for 1 targets
INSERT INTO kv (k) SELECT 'a', 'b'

statement error INSERT has more target columns than expressions, 1 expressions for 2 targets
INSERT INTO kv5 (k, v) SELECT 'a'

# INSERT ... VALUES take a separate code path from INSERT ... SELECT.

statement error INSERT has more expressions than target columns, 3 expressions for 2 targets
INSERT INTO kv VALUES ('a', 'b', 'c')

statement error INSERT has more expressions than target columns, 2 expressions for 1 targets
INSERT INTO kv (k) VALUES ('a', 'b')

statement error INSERT has more target columns than expressions, 1 expressions for 2 targets
INSERT INTO kv5 (k, v) VALUES ('a')

statement ok
CREATE TABLE return (a INT DEFAULT 3, b INT)

query III
INSERT INTO return (a) VALUES (default), (8) RETURNING a, 2, a+4
----
3 2 7
8 2 12

query III
INSERT INTO return (b) VALUES (default), (8) RETURNING a, a+4, b
----
3 7 NULL
3 7 8

# All columns returned if none specified.
query II
INSERT INTO return VALUES (default) RETURNING a, b
----
3 NULL

# Test column names
query III colnames
INSERT INTO return VALUES (default) RETURNING a, b AS c, 4 AS d
----
a  c     d
3  NULL  4

# Return a qualified name
query I
INSERT INTO return VALUES (default) RETURNING return.a
----
3

# Can fetch rowid
statement ok
INSERT INTO return VALUES (default) RETURNING rowid != unique_rowid()

query I colnames
INSERT INTO return (a) VALUES (default) RETURNING b
----
b
NULL

query III
INSERT INTO return (b) VALUES (1) RETURNING *, a+1
----
3 1 4

query II colnames
INSERT INTO return VALUES (default) RETURNING *
----
a b
3 NULL

query II colnames
INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.a, b
----
a b
1 2
3 4

query II colnames
INSERT INTO return VALUES (1, 2), (3, 4) RETURNING *
----
a b
1 2
3 4

# Verify we return all columns even if we don't provide a value for all of them.
query II colnames
INSERT INTO return VALUES (1) RETURNING *
----
a b
1 NULL

query II colnames
INSERT INTO return (a) VALUES (1) RETURNING *
----
a b
1 NULL

statement error pq: "return.*" cannot be aliased
INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.* as x

query III colnames
INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.*, a + b AS c
----
a  b  c
1  2  3
3  4  7

# Table alias
statement ok
INSERT INTO return AS r VALUES (5, 6)

# TODO(knz) after #6092 is fixed
# statement ok
# INSERT INTO return AS r VALUES (5, 6) RETURNING r.a

# #17008: allow fully-qualified table names in RETURNING clauses
statement ok
INSERT INTO return VALUES (5, 6) RETURNING test.return.a

statement error no data source matches pattern: x.\*
INSERT INTO return VALUES (1, 2) RETURNING x.*[1]

statement error column "x" does not exist
INSERT INTO return VALUES (1, 2) RETURNING x[1]

statement ok
CREATE VIEW kview AS VALUES ('a', 'b'), ('c', 'd')

query TT
SELECT * FROM kview
----
a b
c d

statement error "kview" is not a table
INSERT INTO kview VALUES ('e', 'f')

query TT
SELECT * FROM kview
----
a b
c d

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

statement ok
INSERT INTO abc VALUES (1, 2, 10)

# Verify we get the correct message, even though internally the ConditionalPut
# for the index key will also fail.
statement error pgcode 23505 duplicate key value \(a,b\)=\(1,2\) violates unique constraint "primary"
INSERT INTO abc VALUES (1, 2, 20)

statement ok
CREATE TABLE decimal (
  a DECIMAL PRIMARY KEY
)

statement ok
INSERT INTO decimal VALUES (4)

# Verify that the "blind" ConditionalPut optimization correctly handles a batch
# with two CPuts of the same key.
statement ok
CREATE TABLE blindcput (
  x INT,
  v INT,
  PRIMARY KEY (x)
)

# The optimization thresholds at 10 k/v operations, so we need at least that
# many in one batch to trigger it.
statement error duplicate key value \(x\)=\(1\) violates unique constraint "primary"
INSERT INTO blindcput values (1, 1), (2, 2), (3, 3), (4, 4), (1, 5)

statement ok
CREATE TABLE nocols()

statement error INSERT has more expressions than target columns, 2 expressions for 0 targets
INSERT INTO nocols VALUES (true, default)

statement error unimplemented at or near "k"
INSERT INTO kv (kv.k) VALUES ('hello')

statement error unimplemented at or near "*"
INSERT INTO kv (k.*) VALUES ('hello')

statement error unimplemented at or near "v"
INSERT INTO kv (k.v) VALUES ('hello')



statement ok
CREATE TABLE insert_t (x INT, v INT)

statement ok
CREATE TABLE select_t (x INT, v INT)

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

# Check that INSERT supports ORDER BY (MySQL extension)
query II rowsort
INSERT INTO insert_t TABLE select_t ORDER BY v DESC LIMIT 3 RETURNING x, v
----
1  9
3  7
6  4

# Check that INSERT supports LIMIT (MySQL extension)

statement ok
TRUNCATE TABLE insert_t

statement ok
INSERT INTO insert_t SELECT * FROM select_t LIMIT 1

query II
SELECT * FROM insert_t
----
1  9

statement ok
TRUNCATE TABLE insert_t

statement ok
INSERT INTO insert_t (SELECT * FROM select_t LIMIT 1)

query II
SELECT * FROM insert_t
----
1  9

statement error pq: multiple LIMIT clauses not allowed
INSERT INTO insert_t (VALUES (1,1), (2,2) LIMIT 1) LIMIT 1

statement error pq: multiple ORDER BY clauses not allowed
INSERT INTO insert_t (VALUES (1,1), (2,2) ORDER BY 2) ORDER BY 2

statement error DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET
INSERT INTO insert_t (VALUES (1, DEFAULT), (2,'BBB') LIMIT 1)

statement error DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET
INSERT INTO insert_t (VALUES (1, DEFAULT), (2,'BBB')) LIMIT 1

subtest string_bytes_conflicts

statement ok
CREATE TABLE bytes_t (
  b BYTES PRIMARY KEY
)

statement ok
INSERT INTO bytes_t VALUES ('byte')

statement ok
CREATE TABLE string_t (
  s STRING PRIMARY KEY
)

statement ok
INSERT INTO string_t VALUES ('str')

query error value type text doesn't match type bytes of column "b"
INSERT INTO bytes_t SELECT * FROM string_t

query error value type bytes doesn't match type text of column "s"
INSERT INTO string_t SELECT * FROM bytes_t

subtest string_width_check

statement ok
CREATE TABLE sw (
 a CHAR,
 b CHAR(3),
 c VARCHAR,
 d VARCHAR(3),
 e STRING,
 f STRING(3),
 g "char",
 ac CHAR COLLATE en,
 bc CHAR(3) COLLATE en,
 cc VARCHAR COLLATE en,
 dc VARCHAR(3) COLLATE en,
 ec STRING COLLATE en,
 fc STRING(3) COLLATE en
)

query T
SELECT create_statement FROM [SHOW CREATE TABLE sw]
----
CREATE TABLE sw (
   a CHAR NULL,
   b CHAR(3) NULL,
   c VARCHAR NULL,
   d VARCHAR(3) NULL,
   e STRING NULL,
   f STRING(3) NULL,
   g "char" NULL,
   ac CHAR COLLATE en NULL,
   bc CHAR(3) COLLATE en NULL,
   cc VARCHAR COLLATE en NULL,
   dc VARCHAR(3) COLLATE en NULL,
   ec STRING COLLATE en NULL,
   fc STRING(3) COLLATE en NULL,
   FAMILY "primary" (a, b, c, d, e, f, g, ac, bc, cc, dc, ec, fc, rowid)
)

statement ok
INSERT INTO sw VALUES (
   'a', 'b', 'c', 'd', 'e', 'f', 'g',
   'A' COLLATE en, 'B' COLLATE en, 'C' COLLATE en, 'D' COLLATE en, 'E' COLLATE en, 'F' COLLATE en)

statement ok
INSERT INTO sw VALUES (
   '', '', '', '', '', '', '',
   '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en)

statement error value too long for type CHAR \(column "a"\)
INSERT INTO sw(a) VALUES ('ab')

statement error value too long for type CHAR COLLATE en \(column "ac"\)
INSERT INTO sw(ac) VALUES ('ab' COLLATE en)

statement ok
INSERT INTO sw (b, c, d, e, f, g, bc, cc, dc, ec, fc) VALUES (
   'b22', 'c22', 'd22', 'e22', 'f22', 'g22',
   'B22' COLLATE en, 'C22' COLLATE en, 'D22' COLLATE en, 'E22' COLLATE en, 'F22' COLLATE en)

statement error value too long for type CHAR\(3\) \(column "b"\)
INSERT INTO sw(b) VALUES ('abcd')

statement error value too long for type CHAR\(3\) COLLATE en \(column "bc"\)
INSERT INTO sw(bc) VALUES ('abcd' COLLATE en)

statement error value too long for type VARCHAR\(3\) \(column "d"\)
INSERT INTO sw(d) VALUES ('abcd')

statement error value too long for type VARCHAR\(3\) COLLATE en \(column "dc"\)
INSERT INTO sw(dc) VALUES ('abcd' COLLATE en)

statement error value too long for type STRING\(3\) \(column "f"\)
INSERT INTO sw(f) VALUES ('abcd')

statement error value too long for type STRING\(3\) COLLATE en \(column "fc"\)
INSERT INTO sw(fc) VALUES ('abcd' COLLATE en)

subtest regression_26742

statement ok
CREATE TABLE ct(x INT, derived INT AS (x+1) STORED)

statement error value type varchar doesn't match type int of column "x"
INSERT INTO ct(x) SELECT c FROM sw

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
INSERT INTO tn(x) VALUES (NULL)

statement error failed to satisfy CHECK constraint
INSERT 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
INSERT INTO tn2(x) VALUES (NULL)

statement error value too long for type CHAR\(3\)
INSERT INTO tn2(x, y) VALUES (123, '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))

# Sanity check that the FK constraints gets actually validated
statement error foreign key violation
INSERT INTO derived(x) VALUES('xxx')

statement error value too long for type CHAR\(3\)
INSERT INTO derived(x) VALUES('abcd')

statement error failed to satisfy CHECK constraint
INSERT INTO derived(y) VALUES('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
INSERT INTO t29494(x) VALUES (123) RETURNING y

statement ok
ROLLBACK

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

query I
INSERT INTO t29494(x) VALUES (123) RETURNING *
----
123

statement ok
COMMIT

subtest regression_32759_33012

statement ok
CREATE TABLE t32759(x INT, y STRING NOT NULL DEFAULT 'b', z INT)

statement ok
BEGIN; ALTER TABLE t32759 DROP COLUMN y

# Check that the dropped column is not visible
query T
SELECT create_statement FROM [SHOW CREATE t32759]
----
CREATE TABLE t32759 (
   x INT8 NULL,
   z INT8 NULL,
   FAMILY "primary" (x, z, rowid)
)

# Check that values cannot be inserted into the dropped column.
# HP and CBO return different errors, so accept both.
statement error [column "y" does not exist | column "y" is being backfilled]
INSERT INTO t32759(x, y, z) VALUES (2, 'c', 2)

statement ok
ROLLBACK

statement ok
BEGIN; ALTER TABLE t32759 DROP COLUMN y

query II colnames
INSERT INTO t32759(x, z) VALUES (1, 4) RETURNING *
----
x  z
1  4

statement ok
COMMIT

# Test ORDER BY with computed ordering column (requires extra column).

statement ok
CREATE TABLE xy(x INT PRIMARY KEY, y INT);
CREATE TABLE ab(a INT PRIMARY KEY, b INT);
INSERT INTO ab VALUES (1, 1), (2, 2)

query II rowsort
INSERT INTO xy (x, y) SELECT a, b FROM ab ORDER BY -b LIMIT 10 RETURNING *;
----
2  2
1  1

statement ok
DROP TABLE xy; DROP TABLE ab

subtest regression_35611

statement ok
CREATE TABLE t35611(a INT PRIMARY KEY, CHECK (a > 0))

statement ok
BEGIN; ALTER TABLE t35611 ADD COLUMN b INT

statement ok
INSERT INTO t35611 (a) VALUES (1)

statement ok
COMMIT

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

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

statement ok
INSERT INTO t35364(x) VALUES (0.1)

query T
SELECT x FROM t35364
----
0

statement ok
DROP TABLE t35364