Materialize/test/sqllogictest/cockroach/computed.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/computed
#
# 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 with_no_column_refs (
  a INT,
  b INT,
  c INT AS (3) STORED
)

query TT
SHOW CREATE TABLE with_no_column_refs
----
with_no_column_refs  CREATE TABLE with_no_column_refs (
                     a INT8 NULL,
                     b INT8 NULL,
                     c INT8 NULL AS (3) STORED,
                     FAMILY "primary" (a, b, c, rowid)
)

statement ok
CREATE TABLE extra_parens (
  a INT,
  b INT,
  c INT AS ((3)) STORED
)

query TT
SHOW CREATE TABLE extra_parens
----
extra_parens  CREATE TABLE extra_parens (
              a INT8 NULL,
              b INT8 NULL,
              c INT8 NULL AS ((3)) STORED,
              FAMILY "primary" (a, b, c, rowid)
)


statement error cannot write directly to computed column "c"
INSERT INTO with_no_column_refs VALUES (1, 2, 3)

statement error cannot write directly to computed column "c"
INSERT INTO with_no_column_refs (SELECT 1, 2, 3)

statement error cannot write directly to computed column "c"
INSERT INTO with_no_column_refs (a, c) (SELECT 1, 3)

statement error cannot write directly to computed column "c"
INSERT INTO with_no_column_refs (c) VALUES (1)

statement ok
INSERT INTO with_no_column_refs (a, b) VALUES (1, 2)

statement ok
INSERT INTO with_no_column_refs VALUES (1, 2)

statement error cannot write directly to computed column "c"
UPDATE with_no_column_refs SET c = 1

statement error cannot write directly to computed column "c"
UPDATE with_no_column_refs SET (a, b, c) = (1, 2, 3)

statement error cannot write directly to computed column "c"
UPDATE with_no_column_refs SET (a, b, c) = (SELECT 1, 2, 3)

query I
SELECT c FROM with_no_column_refs
----
3
3

statement ok
CREATE TABLE x (
  a INT DEFAULT 3,
  b INT DEFAULT 7,
  c INT AS (a) STORED,
  d INT AS (a + b) STORED
)

query TT
SHOW CREATE TABLE x
----
x  CREATE TABLE x (
   a INT8 NULL DEFAULT 3:::INT8,
   b INT8 NULL DEFAULT 7:::INT8,
   c INT8 NULL AS (a) STORED,
   d INT8 NULL AS (a + b) STORED,
   FAMILY "primary" (a, b, c, d, rowid)
)

query TTBTTTB colnames
SHOW COLUMNS FROM x
----
column_name  data_type  is_nullable  column_default  generation_expression  indices    is_hidden
a            INT8       true         3:::INT8        ·                      {}         false
b            INT8       true         7:::INT8        ·                      {}         false
c            INT8       true         NULL            a                      {}         false
d            INT8       true         NULL            a + b                  {}         false
rowid        INT8       false        unique_rowid()  ·                      {primary}  true

statement error cannot write directly to computed column "c"
INSERT INTO x (c) VALUES (1)

statement ok
INSERT INTO x (a, b) VALUES (1, 2)

query II
SELECT c, d FROM x
----
1 3

statement ok
DELETE FROM x

statement ok
DELETE FROM x

statement ok
DROP TABLE x

statement ok
CREATE TABLE x (
  a INT NOT NULL,
  b INT,
  c INT AS (a) STORED,
  d INT AS (a + b) STORED
)

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

statement error null value in column "a" violates not-null constraint
INSERT INTO x (b) VALUES (1)

query II
SELECT c, d FROM x
----
1  NULL

statement ok
DROP TABLE x

# Check with upserts
statement ok
CREATE TABLE x (
  a INT PRIMARY KEY,
  b INT,
  c INT AS (b + 1) STORED,
  d INT AS (b - 1) STORED
)

statement ok
INSERT INTO x (a, b) VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = excluded.b + 1

query II
SELECT c, d FROM x
----
2 0

statement ok
INSERT INTO x (a, b) VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = excluded.b + 1

query IIII
SELECT a, b, c, d FROM x
----
1 2 3 1

statement ok
INSERT INTO x (a, b) VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = x.b + 1

query III
SELECT a, b, c FROM x
----
1 3 4

# Update.

statement ok
UPDATE x SET b = 3

query III
SELECT a, b, c FROM x
----
1 3 4

# Update/self-reference.

statement ok
UPDATE x SET b = c

query III
SELECT a, b, c FROM x
----
1 4 5

# Updating with default is not allowed.

statement error cannot write directly to computed column "c"
UPDATE x SET (b, c) = (1, DEFAULT)

# Upsert using the UPSERT shorthand.

statement ok
UPSERT INTO x (a, b) VALUES (1, 2)

query IIII
SELECT a, b, c, d FROM x
----
1 2 3 1

statement ok
TRUNCATE x

# statement ok
# INSERT INTO x VALUES (2, 3) ON CONFLICT (a) DO UPDATE SET a = 2, b = 3

statement ok
UPSERT INTO x VALUES (2, 3)

query IIII
SELECT a, b, c, d FROM x
----
2 3 4 2

statement ok
TRUNCATE x

statement error cannot write directly to computed column "c"
UPSERT INTO x VALUES (2, 3, 12)

statement ok
UPSERT INTO x (a, b) VALUES (2, 3)

query IIII
SELECT a, b, c, d FROM x
----
2 3 4 2

statement ok
DROP TABLE x

# TODO(justin): cockroach#22434
# statement ok
# CREATE TABLE x (
#   b INT AS a STORED,
#   a INT
# )
#
# statement ok
# INSERT INTO x VALUES (DEFAULT, 1)
#
# statement ok
# INSERT INTO x VALUES (DEFAULT, '2')
#
# query I
# SELECT b FROM x ORDER BY b
# ----
# 1
# 2
#
# statement ok
# DROP TABLE x

statement error use AS \( <expr> \) STORED
CREATE TABLE y (
  a INT AS 3 STORED
)

statement error use AS \( <expr> \) STORED
CREATE TABLE y (
  a INT AS (3)
)

statement error unimplemented at or near "virtual"
CREATE TABLE y (
  a INT AS (3) VIRTUAL
)

statement error expected computed column expression to have type int, but .* has type text
CREATE TABLE y (
  a INT AS ('not an integer!'::STRING) STORED
)

# We utilize the types from other columns.

statement error expected computed column expression to have type int, but 'a' has type text
CREATE TABLE y (
  a STRING,
  b INT AS (a) STORED
)

statement error impure functions are not allowed in computed column
CREATE TABLE y (
  a TIMESTAMP AS (now()) STORED
)

statement error impure functions are not allowed in computed column
CREATE TABLE y (
  a STRING AS (concat(now()::STRING, uuid_v4()::STRING)) STORED
)

statement error computed columns cannot reference other computed columns
CREATE TABLE y (
  a INT AS (3) STORED,
  b INT AS (a) STORED
)

statement error column "a" does not exist
CREATE TABLE y (
  b INT AS (a) STORED
)

statement error aggregate functions are not allowed in computed column
CREATE TABLE y (
  b INT AS (count(1)) STORED
)

statement error computed columns cannot have default values
CREATE TABLE y (
  a INT AS (3) STORED DEFAULT 4
)

# TODO(justin,bram): this should be allowed.
statement ok
CREATE TABLE x (a INT PRIMARY KEY)

statement error computed columns cannot reference non-restricted FK columns
CREATE TABLE y (
  q INT REFERENCES x (a) ON UPDATE CASCADE,
  r INT AS (q) STORED
)

statement error computed columns cannot reference non-restricted FK columns
CREATE TABLE y (
  q INT REFERENCES x (a) ON DELETE CASCADE,
  r INT AS (q) STORED
)

statement error computed column "r" cannot be a foreign key reference
CREATE TABLE y (
  r INT AS (1) STORED REFERENCES x (a)
)

statement error computed column "r" cannot be a foreign key reference
CREATE TABLE y (
  r INT AS (1) STORED REFERENCES x
)

statement error computed column "r" cannot be a foreign key reference
CREATE TABLE y (
  a INT,
  r INT AS (1) STORED REFERENCES x
)

# Regression test for cockroach#36036.
statement ok
CREATE TABLE tt (i INT8 AS (1) STORED)

statement error variable sub-expressions are not allowed in computed column
ALTER TABLE tt ADD COLUMN c STRING AS ((SELECT NULL)) STORED

statement error computed columns cannot reference other computed columns
ALTER TABLE tt ADD COLUMN c INT8 AS (i) STORED

# Composite FK.

statement ok
CREATE TABLE xx (
  a INT,
  b INT,
  UNIQUE (a, b)
)

statement error computed column "y" cannot be a foreign key reference
CREATE TABLE yy (
  x INT,
  y INT AS (3) STORED,
  FOREIGN KEY (x, y) REFERENCES xx (a, b)
)

statement error computed column "y" cannot be a foreign key reference
CREATE TABLE yy (
  x INT,
  y INT AS (3) STORED,
  FOREIGN KEY (y, x) REFERENCES xx (a, b)
)

statement ok
DROP TABLE xx

statement ok
CREATE TABLE y (
  r INT AS (1) STORED,
  INDEX (r)
)

statement error computed column "r" cannot be a foreign key reference
ALTER TABLE y ADD FOREIGN KEY (r) REFERENCES x (a)

statement ok
DROP TABLE y

statement error variable sub-expressions are not allowed in computed column
CREATE TABLE y (
  r INT AS ((SELECT 1)) STORED
)

statement error no data source matches prefix: x
CREATE TABLE y (
  r INT AS (x.a) STORED
)

statement error no data source matches prefix: x
CREATE TABLE y (
  q INT,
  r INT AS (x.q) STORED
)

statement ok
CREATE TABLE y (
  q INT,
  r INT AS (y.q) STORED
)

statement ok
DROP TABLE y

# It's ok if they exist and we don't reference them.
statement ok
CREATE TABLE y (
  q INT REFERENCES x (a) ON UPDATE CASCADE,
  r INT AS (3) STORED
)

statement ok
DROP TABLE y

statement ok
DROP TABLE x

# Indexes on computed columns
statement ok
CREATE TABLE x (
  k INT PRIMARY KEY,
  a JSON,
  b TEXT AS (a->>'q') STORED,
  INDEX (b)
)

statement error cannot write directly to computed column
INSERT INTO x (k, a, b) VALUES (1, '{"q":"xyz"}', 'not allowed!'), (2, '{"q":"abc"}', 'also not allowed')

statement error cannot write directly to computed column
UPDATE x SET (k, a, b) = (1, '{"q":"xyz"}', 'not allowed!')

statement ok
INSERT INTO x (k, a) VALUES (1, '{"q":"xyz"}'), (2, '{"q":"abc"}')

query IT
SELECT k, b FROM x ORDER BY b
----
2 abc
1 xyz

statement ok
DROP TABLE x

statement ok
CREATE TABLE x (
  k INT AS ((data->>'id')::INT) STORED PRIMARY KEY,
  data JSON
)

statement ok
INSERT INTO x (data) VALUES
 ('{"id": 1, "name": "lucky"}'),
 ('{"id": 2, "name": "rascal"}'),
 ('{"id": 3, "name": "captain"}'),
 ('{"id": 4, "name": "lola"}')

# ON CONFLICT that modifies a PK.
statement ok
INSERT INTO x (data) VALUES ('{"id": 1, "name": "ernie"}')
ON CONFLICT (k) DO UPDATE SET data = '{"id": 5, "name": "ernie"}'

# ON CONFLICT that modifies a PK which then also conflicts.
statement error duplicate key value
INSERT INTO x (data) VALUES ('{"id": 5, "name": "oliver"}')
ON CONFLICT (k) DO UPDATE SET data = '{"id": 2, "name": "rascal"}'

# Updating a non-PK column.
statement ok
UPDATE x SET data = data || '{"name": "carl"}' WHERE k = 2

query T
SELECT data->>'name' FROM x WHERE k = 2
----
carl

query T
SELECT data->>'name' FROM x WHERE k = 5
----
ernie

# Referencing a computed column.
statement ok
create table y (
  a INT REFERENCES x (k)
)

statement ok
INSERT INTO y VALUES (5)

statement error foreign key violation
INSERT INTO y VALUES (100)

statement ok
DROP TABLE x CASCADE

statement ok
CREATE TABLE x (
  a INT,
  b INT,
  c INT,
  d INT[] AS (ARRAY[a, b, c]) STORED
)

statement ok
INSERT INTO x (a, b, c) VALUES (1, 2, 3)

query T
SELECT d FROM x
----
{1,2,3}

statement ok
TRUNCATE x

# Make sure we get the permutation on the inserts correct.

statement ok
INSERT INTO x (b, a, c) VALUES (1, 2, 3)

query T
SELECT d FROM x
----
{2,1,3}

# Make sure we get the permutation on the updates correct.
statement ok
UPDATE x SET (c, a, b) = (1, 2, 3)

query T
SELECT d FROM x
----
{2,3,1}

statement ok
UPDATE x SET (a, c) = (1, 2)

query T
SELECT d FROM x
----
{1,3,2}

statement ok
UPDATE x SET c = 2, a = 3, b = 1

query T
SELECT d FROM x
----
{3,1,2}

# Make sure we get the permutation on upserts correct.
statement ok
INSERT INTO x (rowid) VALUES ((SELECT rowid FROM x)) ON CONFLICT(rowid) DO UPDATE SET (a, b, c) = (1, 2, 3)

query T
SELECT d FROM x
----
{1,2,3}

statement ok
INSERT INTO x (rowid) VALUES ((SELECT rowid FROM x)) ON CONFLICT(rowid) DO UPDATE SET (c, a, b) = (1, 2, 3)

query T
SELECT d FROM x
----
{2,3,1}

statement ok
INSERT INTO x (rowid) VALUES ((SELECT rowid FROM x)) ON CONFLICT(rowid) DO UPDATE SET (c, a) = (1, 2)

query T
SELECT d FROM x
----
{2,3,1}

statement ok
DROP TABLE x

statement ok
CREATE TABLE x (
  a INT,
  b INT as (x.a) STORED
)

query TT
SHOW CREATE TABLE x
----
x  CREATE TABLE x (
   a INT8 NULL,
   b INT8 NULL AS (a) STORED,
   FAMILY "primary" (a, b, rowid)
)

statement ok
DROP TABLE x

# Check that computed columns are resilient to column renames.
statement ok
CREATE TABLE x (
  a INT,
  b INT AS (a) STORED
)

statement ok
ALTER TABLE x RENAME COLUMN a TO c

query TT
SHOW CREATE TABLE x
----
x  CREATE TABLE x (
   c INT8 NULL,
   b INT8 NULL AS (c) STORED,
   FAMILY "primary" (c, b, rowid)
)

statement ok
DROP TABLE x

statement ok
CREATE TABLE x (
  a INT,
  b INT AS (a * 2) STORED
)

query T colnames
SELECT generation_expression FROM information_schema.columns
WHERE table_name = 'x' and column_name = 'b'
----
generation_expression
a * 2

query I
SELECT count(*) FROM information_schema.columns
WHERE table_name = 'x' and generation_expression = ''
----
2

statement ok
INSERT INTO x VALUES (3)

# Verify computed columns work.
statement ok
ALTER TABLE x ADD COLUMN c INT NOT NULL AS (a + 4) STORED

query TT
SHOW CREATE TABLE x
----
x  CREATE TABLE x (
   a INT8 NULL,
   b INT8 NULL AS (a * 2) STORED,
   c INT8 NOT NULL AS (a + 4) STORED,
   FAMILY "primary" (a, b, rowid, c)
)

statement ok
INSERT INTO x VALUES (6)

query III
SELECT * FROM x ORDER BY a
----
3 6 7
6 12 10

# Verify a bad statement fails.
statement error unsupported binary operator: <int> \+ <string> \(desired <int>\)
ALTER TABLE x ADD COLUMN d INT AS (a + 'a') STORED

statement error could not parse "a" as type int
ALTER TABLE x ADD COLUMN d INT AS ('a') STORED

statement error unsupported binary operator
ALTER TABLE x ADD COLUMN d INT AS (a / 0) STORED

# Verify an error during computation fails.
statement error division by zero
ALTER TABLE x ADD COLUMN d INT AS (a // 0) STORED

statement ok
DROP TABLE x

# Regression test for materialize#23109
statement ok
CREATE TABLE x (
  a INT DEFAULT 1,
  b INT AS (2) STORED
)

statement ok
INSERT INTO x (a) SELECT 1

statement ok
DROP TABLE x

statement ok
CREATE TABLE x (
  b INT AS (2) STORED,
  a INT DEFAULT 1
)

statement ok
INSERT INTO x (a) SELECT 1

statement ok
DROP TABLE x

# Verify errors emitted from computed columns contain the column name
statement ok
CREATE TABLE error_check (k INT PRIMARY KEY, s STRING, i INT AS (s::INT) STORED)

statement ok
INSERT INTO error_check VALUES(1, '1')

statement error could not parse "foo" as type int: strconv.ParseInt
INSERT INTO error_check VALUES(2, 'foo')

statement error could not parse "foo" as type int: strconv.ParseInt: parsing "foo": invalid syntax
UPDATE error_check SET s = 'foo' WHERE k = 1

# Upsert -> update
# NOTE: The CBO cannot show the name of the computed column in the error message
# because the computation is part of an overall SQL statement.
statement error could not parse "foo" as type int: strconv.ParseInt: parsing "foo": invalid syntax
UPSERT INTO error_check VALUES (1, 'foo')

# Upsert -> insert
statement error could not parse "foo" as type int: strconv.ParseInt: parsing "foo": invalid syntax
UPSERT INTO error_check VALUES (3, 'foo')

statement ok
CREATE TABLE x (
  a INT PRIMARY KEY,
  b INT AS (a+1) STORED
)

query error value type decimal doesn't match type int of column "a"
INSERT INTO x VALUES(1.4)

# Regression test for cockroach#34901: verify that builtins can be used in computed
# column expressions without a "memory budget exceeded" error while backfilling
statement ok
CREATE TABLE t34901 (x STRING)

statement ok
INSERT INTO t34901 VALUES ('a')

statement ok
ALTER TABLE t34901 ADD COLUMN y STRING AS (concat(x, 'b')) STORED

query TT
SELECT * FROM t34901
----
a  ab