Materialize/test/sqllogictest/cockroach/alter_table.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/alter_table
#
# 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 other (b INT PRIMARY KEY)

statement ok
INSERT INTO other VALUES (9)

statement ok
CREATE TABLE t (a INT PRIMARY KEY CHECK(a > 0), f INT REFERENCES other, INDEX (f))

statement ok
INSERT INTO t VALUES (1, 9)

statement error syntax error at or near "*"
ALTER TABLE t RENAME TO t.*

statement ok
ALTER TABLE t ADD b INT

query TTBTTTB colnames
SHOW COLUMNS FROM t
----
column_name  data_type  is_nullable  column_default  generation_expression  indices            is_hidden
a            INT8       false        NULL            ·                      {primary,t_f_idx}  false
f            INT8       true         NULL            ·                      {t_f_idx}          false
b            INT8       true         NULL            ·                      {}                 false

statement ok
ALTER TABLE t ADD CONSTRAINT foo UNIQUE (b)

query TTTTRT
SELECT job_type, description, user_name, status, fraction_completed, error
FROM crdb_internal.jobs
WHERE job_type = 'SCHEMA CHANGE'
ORDER BY created DESC
LIMIT 1
----
SCHEMA CHANGE  ALTER TABLE test.public.t ADD CONSTRAINT foo UNIQUE (b)  root  succeeded  1  ·

statement error duplicate constraint name: "foo"
ALTER TABLE t ADD CONSTRAINT foo UNIQUE (b)

statement error multiple primary keys for table "t" are not allowed
ALTER TABLE t ADD CONSTRAINT bar PRIMARY KEY (b)

query TTBITTBB colnames
SHOW INDEXES ON t
----
table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
t           primary     false       1             a            ASC        false    false
t           t_f_idx     true        1             f            ASC        false    false
t           t_f_idx     true        2             a            ASC        false    true
t           foo         false       1             b            ASC        false    false
t           foo         false       2             a            ASC        false    true

query III
SELECT * FROM t
----
1 9 NULL

statement ok
ALTER TABLE t ADD c INT

statement ok
INSERT INTO t VALUES (2, 9, 1, 1), (3, 9, 2, 1)

statement error pgcode 23505 violates unique constraint "bar"
ALTER TABLE t ADD CONSTRAINT bar UNIQUE (c)

# Test that rollback was successful
query TTTTTR
SELECT job_type, regexp_replace(description, 'JOB \d+', 'JOB ...'), user_name, status, running_status, fraction_completed::decimal(10,2)
FROM crdb_internal.jobs
WHERE job_type = 'SCHEMA CHANGE'
ORDER BY created DESC
LIMIT 2
----
SCHEMA CHANGE  ROLL BACK JOB ...: ALTER TABLE test.public.t ADD CONSTRAINT bar UNIQUE (c)  root  running  waiting for GC TTL  0.00
SCHEMA CHANGE  ALTER TABLE test.public.t ADD CONSTRAINT bar UNIQUE (c)                     root  failed   NULL                0.00

query IIII colnames,rowsort
SELECT * FROM t
----
a f b    c
1 9 NULL NULL
2 9 1    1
3 9 2    1

query TTTTB colnames
SHOW CONSTRAINTS FROM t
----
table_name  constraint_name  constraint_type  details                               validated
t           check_a          CHECK            CHECK (a > 0)                         true
t           fk_f_ref_other   FOREIGN KEY      FOREIGN KEY (f) REFERENCES other (b)  true
t           foo              UNIQUE           UNIQUE (b ASC)                        true
t           primary          PRIMARY KEY      PRIMARY KEY (a ASC)                   true

statement error CHECK
INSERT INTO t (a, f) VALUES (-2, 9)

statement ok
ALTER TABLE t DROP CONSTRAINT check_a

statement ok
INSERT INTO t (a, f) VALUES (-2, 9)

statement error validation of CHECK "a > 0" failed on row: a=-2, f=9, b=NULL, c=NULL
ALTER TABLE t ADD CONSTRAINT check_a CHECK (a > 0)

statement ok
DELETE FROM t WHERE a = -2

statement ok
ALTER TABLE t ADD CONSTRAINT check_a CHECK (a > 0)

statement error CHECK
INSERT INTO t (a) VALUES (-3)

query TTTTB
SHOW CONSTRAINTS FROM t
----
t  check_a         CHECK        CHECK (a > 0)                         true
t  fk_f_ref_other  FOREIGN KEY  FOREIGN KEY (f) REFERENCES other (b)  true
t  foo             UNIQUE       UNIQUE (b ASC)                        true
t  primary         PRIMARY KEY  PRIMARY KEY (a ASC)                   true

statement error duplicate constraint name
ALTER TABLE t ADD CONSTRAINT check_a CHECK (a > 0)

statement error duplicate constraint name
ALTER TABLE t ADD CONSTRAINT fk_f_ref_other FOREIGN KEY (a) REFERENCES other (b)

# added constraints with generated names avoid name collisions.
statement ok
ALTER TABLE t ADD CHECK (a > 0)

query TTTTB
SHOW CONSTRAINTS FROM t
----
t  check_a         CHECK        CHECK (a > 0)                         true
t  check_a1        CHECK        CHECK (a > 0)                         true
t  fk_f_ref_other  FOREIGN KEY  FOREIGN KEY (f) REFERENCES other (b)  true
t  foo             UNIQUE       UNIQUE (b ASC)                        true
t  primary         PRIMARY KEY  PRIMARY KEY (a ASC)                   true

statement error constraint "typo" does not exist
ALTER TABLE t VALIDATE CONSTRAINT typo

# TODO(erik): re-enable test when unvalidated checks can be added
#statement error validation of CHECK "a > 0" failed on row: a=-2, f=9, b=NULL, c=NULL
#ALTER TABLE t VALIDATE CONSTRAINT check_a

#statement ok
#DELETE FROM t WHERE a = -2

statement ok
ALTER TABLE t VALIDATE CONSTRAINT check_a

query TTTTB
SHOW CONSTRAINTS FROM t
----
t  check_a         CHECK        CHECK (a > 0)                         true
t  check_a1        CHECK        CHECK (a > 0)                         true
t  fk_f_ref_other  FOREIGN KEY  FOREIGN KEY (f) REFERENCES other (b)  true
t  foo             UNIQUE       UNIQUE (b ASC)                        true
t  primary         PRIMARY KEY  PRIMARY KEY (a ASC)                   true

statement ok
ALTER TABLE t DROP CONSTRAINT check_a, DROP CONSTRAINT check_a1

statement error pgcode 42703 column "d" does not exist
ALTER TABLE t DROP d

statement ok
ALTER TABLE t DROP IF EXISTS d

statement error column "a" is referenced by the primary key
ALTER TABLE t DROP a

statement error constraint "bar" does not exist
ALTER TABLE t DROP CONSTRAINT bar

statement ok
ALTER TABLE t DROP CONSTRAINT IF EXISTS bar

statement error cannot drop UNIQUE constraint \"foo\" using ALTER TABLE DROP CONSTRAINT, use DROP INDEX CASCADE instead
ALTER TABLE t DROP CONSTRAINT foo

statement ok
DROP INDEX foo CASCADE

query TTTTTRT
SELECT job_type, description, user_name, status, running_status, fraction_completed, error
FROM crdb_internal.jobs
WHERE job_type = 'SCHEMA CHANGE'
ORDER BY created DESC
LIMIT 1
----
SCHEMA CHANGE  DROP INDEX test.public.t@foo CASCADE  root  running  waiting for GC TTL  0  ·

query TTBITTBB colnames
SHOW INDEXES ON t
----
table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
t           primary     false       1             a            ASC        false    false
t           t_f_idx     true        1             f            ASC        false    false
t           t_f_idx     true        2             a            ASC        false    true

statement ok
ALTER TABLE t DROP b, DROP c

query II rowsort
SELECT * FROM t
----
1  9
2  9
3  9

statement ok
ALTER TABLE t ADD d INT UNIQUE

statement ok
INSERT INTO t VALUES (4, 9, 1)

statement error duplicate key value \(d\)=\(1\) violates unique constraint \"t_d_key\"
INSERT INTO t VALUES (5, 9, 1)

# Add a column with no default value
statement ok
ALTER TABLE t ADD COLUMN x DECIMAL

# Add a non NULL column with a default value
statement ok
ALTER TABLE t ADD COLUMN y DECIMAL NOT NULL DEFAULT (DECIMAL '1.3')

statement error could not parse "1-3" as type decimal
ALTER TABLE t ADD COLUMN p DECIMAL NOT NULL DEFAULT (DECIMAL '1-3')

# Add a non NULL column with no default value
statement error pgcode 23502 null value in column \"q\" violates not-null constraint
ALTER TABLE t ADD COLUMN q DECIMAL NOT NULL

statement ok
ALTER TABLE t ADD COLUMN z DECIMAL DEFAULT (DECIMAL '1.4')

statement ok
INSERT INTO t VALUES (11, 9, 12, DECIMAL '1.0')

statement ok
INSERT INTO t (a, d) VALUES (13, 14)

statement ok
INSERT INTO t (a, d, y) VALUES (21, 22, DECIMAL '1.0')

statement ok
INSERT INTO t (a, d) VALUES (23, 24)

statement error foreign key
INSERT INTO t VALUES (31, 7, 32)

statement error in use as a foreign key constraint
DROP INDEX t@t_f_idx

statement ok
ALTER TABLE t DROP CONSTRAINT fk_f_ref_other

statement  ok
INSERT INTO t VALUES (31, 7, 32)

statement ok
INSERT INTO t (a, d, x, y, z) VALUES (33, 34, DECIMAL '2.0', DECIMAL '2.1', DECIMAL '2.2')

statement ok
DROP INDEX t@t_f_idx

query TTTTTRT
SELECT job_type, description, user_name, status, running_status, fraction_completed, error
FROM crdb_internal.jobs
WHERE job_type = 'SCHEMA CHANGE'
ORDER BY created DESC
LIMIT 1
----
SCHEMA CHANGE  DROP INDEX test.public.t@t_f_idx  root  running  waiting for GC TTL  0  ·

statement ok
ALTER TABLE t DROP COLUMN f

query IITTT colnames,rowsort
SELECT * FROM t
----
a   d     x     y     z
1   NULL  NULL  1.3   1.4
2   NULL  NULL  1.3   1.4
3   NULL  NULL  1.3   1.4
4   1     NULL  1.3   1.4
11  12    1.0   1.3   1.4
13  14    NULL  1.3   1.4
21  22    NULL  1.0   1.4
23  24    NULL  1.3   1.4
31  32    NULL  1.3   1.4
33  34    2.0   2.1   2.2

statement ok
ALTER TABLE t DROP COLUMN d

statement ok
ALTER TABLE t ADD COLUMN e INT; ALTER TABLE t ADD COLUMN d INT

statement ok
CREATE VIEW v AS SELECT x, y FROM t WHERE e > 5

statement error cannot drop column "x" because view "v" depends on it
ALTER TABLE t DROP COLUMN x

statement error cannot drop column "y" because view "v" depends on it
ALTER TABLE t DROP COLUMN y

statement error cannot drop column "e" because view "v" depends on it
ALTER TABLE t DROP COLUMN e

# TODO(knz): this statement should succeed after cockroach#17269 is fixed.
statement error cannot drop column "d" because view "v" depends on it
ALTER TABLE t DROP COLUMN d

# TODO(knz): remove the following once the test above succeeds.
statement ok
ALTER TABLE t DROP COLUMN d CASCADE

statement ok
ALTER TABLE t DROP COLUMN e CASCADE

statement ok
ALTER TABLE t ADD COLUMN e INT

statement ok
CREATE VIEW v AS SELECT x, y FROM t WHERE e > 5

statement ok
ALTER TABLE t DROP COLUMN IF EXISTS q

statement error cannot drop column "e" because view "v" depends on it
ALTER TABLE t DROP COLUMN IF EXISTS e

statement ok
ALTER TABLE t DROP COLUMN IF EXISTS e CASCADE

statement ok
ALTER TABLE t ADD COLUMN g INT UNIQUE

statement ok
CREATE TABLE o (gf INT REFERENCES t (g), h INT, i INT, INDEX ii (i) STORING(h))

statement error "t_g_key" is referenced by foreign key from table "o"
ALTER TABLE t DROP COLUMN g

statement ok
ALTER TABLE t DROP COLUMN g CASCADE

statement error column "h" is referenced by existing index "ii"
ALTER TABLE o DROP COLUMN h

statement ok
ALTER TABLE o DROP COLUMN h CASCADE

statement ok
ALTER TABLE t ADD f INT CHECK (f > 1)

statement ok
ALTER TABLE t ADD g INT DEFAULT 1 CHECK (g > 0)

statement ok
ALTER TABLE t ADD h INT CHECK (h > 0) CHECK (h < 10) UNIQUE

statement error pq: validation of CHECK "i < 0" failed on row:.* i=1
ALTER TABLE t ADD i INT DEFAULT 1 CHECK (i < 0)

statement error pq: validation of CHECK "i < g" failed on row:.* g=1.* i=1
ALTER TABLE t ADD i INT DEFAULT 1 CHECK (i < g)

statement error pq: validation of CHECK "i > 0" failed on row:.* g=1.* i=0
ALTER TABLE t ADD i INT AS (g - 1) STORED CHECK (i > 0)

statement error adding a REFERENCES constraint while also adding a column via ALTER not supported
ALTER TABLE t ADD f INT UNIQUE REFERENCES other

query TTTTB
SHOW CONSTRAINTS FROM t
----
t  check_f   CHECK        CHECK (f > 1)        true
t  check_g   CHECK        CHECK (g > 0)        true
t  check_h   CHECK        CHECK (h > 0)        true
t  check_h1  CHECK        CHECK (h < 10)       true
t  primary   PRIMARY KEY  PRIMARY KEY (a ASC)  true
t  t_h_key   UNIQUE       UNIQUE (h ASC)       true

statement ok
DROP TABLE t

# Test that more than one column with constraints can be added in the same
# statement. The constraints added here are on columns that are new and both
# columns and constraints run through the schema change process together.

statement ok
CREATE TABLE t (a INT PRIMARY KEY)

statement ok
INSERT INTO t VALUES (1)

# Check references column added in same statement
statement ok
ALTER TABLE t ADD b INT DEFAULT 1, ADD c INT DEFAULT 2 CHECK (c > b)

statement ok
ALTER TABLE t ADD d INT UNIQUE, ADD e INT UNIQUE, ADD f INT

# Check references column added in same statement
statement error pq: validation of CHECK "g = h" failed on row:.* g=3.* h=2
ALTER TABLE t ADD g INT DEFAULT 3, ADD h INT DEFAULT 2 CHECK (g = h)

# Multiple unique columns can be added, followed by other commands (cockroach#35011)
statement ok
ALTER TABLE t ADD COLUMN u INT UNIQUE, ADD COLUMN v INT UNIQUE, ADD CONSTRAINT ck CHECK (a > 0);

query TTTTB
SHOW CONSTRAINTS FROM t
----
t  check_c_b  CHECK        CHECK (c > b)        true
t  ck         CHECK        CHECK (a > 0)        true
t  primary    PRIMARY KEY  PRIMARY KEY (a ASC)  true
t  t_d_key    UNIQUE       UNIQUE (d ASC)       true
t  t_e_key    UNIQUE       UNIQUE (e ASC)       true
t  t_u_key    UNIQUE       UNIQUE (u ASC)       true
t  t_v_key    UNIQUE       UNIQUE (v ASC)       true

statement ok
DROP TABLE t

# Subsequent operations succeed because the table is empty
statement ok
CREATE TABLE tt (a INT PRIMARY KEY)

statement ok
ALTER TABLE tt ADD COLUMN q DECIMAL NOT NULL

statement ok
ALTER table tt ADD COLUMN r DECIMAL

# Ensure that a UNIQUE NOT NULL COLUMN can be added when there is no data in
# the table.
statement ok
ALTER TABLE tt ADD COLUMN s DECIMAL UNIQUE NOT NULL

statement ok
ALTER TABLE tt ADD t DECIMAL UNIQUE DEFAULT 4.0

query TTBTTTB colnames
SHOW COLUMNS FROM tt
----
column_name  data_type  is_nullable  column_default  generation_expression  indices                      is_hidden
a            INT8       false        NULL            ·                      {primary,tt_s_key,tt_t_key}  false
q            DECIMAL    false        NULL            ·                      {}                           false
r            DECIMAL    true         NULL            ·                      {}                           false
s            DECIMAL    false        NULL            ·                      {tt_s_key}                   false
t            DECIMAL    true         4.0:::DECIMAL   ·                      {tt_t_key}                   false

# Default values can be added and changed after table creation.
statement ok
CREATE TABLE add_default (a int primary key, b int not null)

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

statement ok
ALTER TABLE add_default ALTER COLUMN b SET DEFAULT 42

statement ok
INSERT INTO add_default (a) VALUES (2)

statement ok
ALTER TABLE add_default ALTER COLUMN b SET DEFAULT 10

statement ok
INSERT INTO add_default (a) VALUES (3)

statement error could not parse "foo" as type int
ALTER TABLE add_default ALTER COLUMN b SET DEFAULT 'foo'

statement error variable sub-expressions are not allowed in DEFAULT
ALTER TABLE add_default ALTER COLUMN b SET DEFAULT c

statement error variable sub-expressions are not allowed in DEFAULT
ALTER TABLE add_default ALTER COLUMN b SET DEFAULT (SELECT 1)

statement ok
ALTER TABLE add_default ALTER COLUMN b DROP DEFAULT

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

statement ok
ALTER TABLE add_default ALTER COLUMN b SET DEFAULT NULL

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

# Each row gets the default value from the time it was inserted.
query II rowsort
SELECT * FROM add_default
----
2 42
3 10

statement ok
ALTER TABLE add_default ALTER b DROP NOT NULL

statement ok
INSERT INTO add_default (a) VALUES (5)

query II
SELECT * from add_default WHERE a=5
----
5 NULL

# Add a column with a default current_timestamp()
statement ok
ALTER TABLE add_default ADD COLUMN c TIMESTAMP DEFAULT current_timestamp()

query II rowsort
SELECT a,b FROM add_default WHERE current_timestamp > c AND current_timestamp() - c < interval '10s'
----
2 42
3 10
5 NULL

# Add a column with a default transaction_timestamp()
statement ok
ALTER TABLE add_default ADD COLUMN d TIMESTAMP DEFAULT transaction_timestamp()

query II rowsort
SELECT a,b FROM add_default WHERE d > c AND d - c < interval '10s'
----
2 42
3 10
5 NULL

# Add a column with a default statement_timestamp()
statement ok
ALTER TABLE add_default ADD COLUMN e TIMESTAMP DEFAULT statement_timestamp()

query II rowsort
SELECT a,b FROM add_default WHERE e > d AND e - d < interval '10s'
----
2 42
3 10
5 NULL

# Add a column with a null-default statement_timestamp()
statement ok
ALTER TABLE add_default ADD COLUMN f TIMESTAMP DEFAULT NULL

query IIS rowsort
SELECT a,b,f FROM add_default
----
2 42   NULL
3 10   NULL
5 NULL NULL

# Adding a unique column to an existing table with data with a default value
# is illegal
statement error pgcode 23505 violates unique constraint \"add_default_g_key\"
ALTER TABLE add_default ADD g INT UNIQUE DEFAULT 1

# various default evaluation errors

statement ok
CREATE SEQUENCE initial_seq

statement error cannot backfill such sequence operation
ALTER TABLE add_default ADD g INT DEFAULT nextval('initial_seq')

statement error cannot backfill such evaluated expression
ALTER TABLE add_default ADD g OID DEFAULT 'foo'::regclass::oid

statement error cannot access virtual schema in anonymous database
ALTER TABLE add_default ADD g INT DEFAULT 'foo'::regtype::INT

subtest 26422

statement ok
BEGIN

statement ok
ALTER TABLE add_default ADD fee FLOAT NOT NULL DEFAULT 2.99

statement ok
ALTER TABLE add_default ALTER COLUMN fee DROP DEFAULT

statement error pgcode XXA00 null value in column "fee" violates not-null constraint
COMMIT

statement error pgcode 42703 column "fee" does not exist
ALTER TABLE add_default DROP fee

# Multiple columns can be added at once with heterogeneous DEFAULT usage
statement ok
CREATE TABLE d (a INT PRIMARY KEY)

statement ok
INSERT INTO d VALUES (1), (2)

statement ok
ALTER TABLE d ADD COLUMN c INT, ADD COLUMN b INT DEFAULT 7

statement ok
INSERT INTO d (a, c) VALUES (3, 4)

query III rowsort
SELECT * FROM d
----
1 NULL 7
2 NULL 7
3 4    7

# Test privileges.

statement ok
CREATE TABLE privs (a INT PRIMARY KEY, b INT)

statement ok
INSERT INTO privs VALUES (1)

user testuser

query T
SHOW DATABASE
----
test

statement error user testuser does not have CREATE privilege on relation privs
ALTER TABLE privs ADD c INT

statement error user testuser does not have CREATE privilege on relation privs
ALTER TABLE privs ADD CONSTRAINT foo UNIQUE (b)

user root

query TTBTTTB colnames
SHOW COLUMNS FROM privs
----
column_name  data_type  is_nullable  column_default  generation_expression  indices    is_hidden
a            INT8       false        NULL            ·                      {primary}  false
b            INT8       true         NULL            ·                      {}         false

statement ok
GRANT CREATE ON privs TO testuser

user testuser

statement ok
ALTER TABLE privs ADD c INT

statement ok
ALTER TABLE privs ADD CONSTRAINT foo UNIQUE (b)

query TTBTTTB colnames
SHOW COLUMNS FROM privs
----
column_name  data_type  is_nullable  column_default  generation_expression  indices        is_hidden
a            INT8       false        NULL            ·                      {primary,foo}  false
b            INT8       true         NULL            ·                      {foo}          false
c            INT8       true         NULL            ·                      {}             false

statement error pgcode 42P01 relation "nonexistent" does not exist
ALTER TABLE nonexistent SPLIT AT VALUES (42)

statement error pgcode 42P01 relation "nonexistent" does not exist
ALTER INDEX nonexistent@noindex SPLIT AT VALUES (42)

statement error pgcode 42P01 relation "nonexistent" does not exist
ALTER TABLE nonexistent UNSPLIT AT VALUES (42)

statement error pgcode 42P01 relation "nonexistent" does not exist
ALTER INDEX nonexistent@noindex UNSPLIT AT VALUES (42)

user root

statement ok
CREATE VIEW privsview AS SELECT a,b,c FROM privs

statement error pgcode 42809 "privsview" is not a table
ALTER TABLE privsview ADD d INT

statement error pgcode 42809 "privsview" is not a table
ALTER TABLE privsview SPLIT AT VALUES (42)

statement error pgcode 42809 "privsview" is not a table
ALTER TABLE privsview UNSPLIT AT VALUES (42)

# Verify that impure defaults are evaluated separately on each row
# (database-issues#4105)
statement ok
CREATE TABLE impure (x INT); INSERT INTO impure(x) VALUES (1), (2), (3);

statement ok
ALTER TABLE impure ADD COLUMN a INT DEFAULT unique_rowid();

query I
SELECT count(distinct a) FROM impure
----
3

# No orphaned schema change jobs.
query I
SELECT count(*) FROM crdb_internal.jobs
WHERE job_type = 'SCHEMA CHANGE' AND status = 'pending' OR status = 'started'
----
0

# Verify that ALTER TABLE statements are rolled back properly when a DEFAULT expression returns
# an error.

statement ok
CREATE TABLE default_err_test (foo text)

statement ok
INSERT INTO default_err_test VALUES ('foo'), ('bar'), ('baz')

statement error some_msg
ALTER TABLE default_err_test ADD COLUMN id int DEFAULT crdb_internal.force_error('foo', 'some_msg')

query T
SELECT * from default_err_test ORDER BY foo
----
bar
baz
foo

# Create a table with a computed column that we'll de-compute
statement ok
CREATE TABLE decomputed_column (a INT PRIMARY KEY, b INT AS ( a + 1 ) STORED)

statement ok
INSERT INTO decomputed_column VALUES (1), (2)

statement error cannot write directly to computed column
INSERT INTO decomputed_column VALUES (3, NULL), (4, 99)

statement ok
ALTER TABLE decomputed_column ALTER COLUMN b DROP STORED

statement error pq: column "a" is not a computed column
ALTER TABLE decomputed_column ALTER COLUMN a DROP STORED

statement error pq: column "b" is not a computed column
ALTER TABLE decomputed_column ALTER COLUMN b DROP STORED

# Verify that the computation is dropped and that we can mutate the column
statement ok
INSERT INTO decomputed_column VALUES (3, NULL), (4, 99)

query II
select a, b from decomputed_column order by a
----
1 2
2 3
3 NULL
4 99

query TT
show create table decomputed_column
----
decomputed_column  CREATE TABLE decomputed_column (
                   a INT8 NOT NULL,
                   b INT8 NULL,
                   CONSTRAINT "primary" PRIMARY KEY (a ASC),
                   FAMILY "primary" (a, b)
)

# Test for https://github.com/cockroachdb/cockroach/issues/26483
# We try to create a unique column on an un-indexable type.
statement ok
CREATE TABLE b26483()

statement error unimplemented: column c is of type int\[\] and thus is not indexable
ALTER TABLE b26483 ADD COLUMN c INT[] UNIQUE

# As above, but performed in a transaction
statement ok
BEGIN

statement ok
CREATE TABLE b26483_tx()

statement ok
ALTER TABLE b26483_tx ADD COLUMN c INT[]

statement error unimplemented: column c is of type int\[\] and thus is not indexable
CREATE INDEX on b26483_tx (c)

statement ok
ROLLBACK

# Verify that auditing can be enabled by root, and cannot be disabled by non-root.

statement ok
CREATE TABLE audit(x INT); ALTER TABLE audit EXPERIMENTAL_AUDIT SET READ WRITE;

# The user must be able to issue ALTER for this test to be meaningful.
statement ok
GRANT CREATE ON audit TO testuser

user testuser

# Check the user can indeed change the table
statement ok
ALTER TABLE audit ADD COLUMN y INT

# But not the audit settings.
statement error change auditing settings on a table
ALTER TABLE audit EXPERIMENTAL_AUDIT SET OFF;

user root

# Check column backfill in the presence of fks
subtest 27402

statement ok
CREATE TABLE users (
    id INT NOT NULL,
    city STRING NOT NULL,
    name STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
)

statement ok
CREATE TABLE vehicles (
    id INT NOT NULL,
    city STRING NOT NULL,
    type STRING NULL,
    owner_id INT NULL,
    mycol STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
    INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC)
)

statement ok
CREATE TABLE rides (
    id INT NOT NULL,
    city STRING NOT NULL,
    vehicle_city STRING NULL,
    rider_id INT NULL,
    vehicle_id INT NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
    INDEX rides_auto_index_fk_city_ref_users (city ASC, rider_id ASC),
    INDEX rides_auto_index_fk_vehicle_city_ref_vehicles (vehicle_city ASC, vehicle_id ASC),
    CONSTRAINT check_vehicle_city_city CHECK (vehicle_city = city)
)

statement ok
ALTER TABLE vehicles ADD CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users (city, id)

statement ok
ALTER TABLE rides ADD CONSTRAINT fk_city_ref_users FOREIGN KEY (city, rider_id) REFERENCES users (city, id)

statement ok
ALTER TABLE rides ADD CONSTRAINT fk_vehicle_city_ref_vehicles FOREIGN KEY (vehicle_city, vehicle_id) REFERENCES vehicles (city, id)


statement ok
INSERT INTO users VALUES (10, 'lagos', 'chimamanda')

statement ok
INSERT INTO vehicles VALUES (100, 'lagos', 'toyota', 10, 'mycol')

statement ok
INSERT INTO rides VALUES (567, 'lagos', 'lagos', 10, 100)

statement ok
ALTER TABLE vehicles DROP COLUMN mycol;

# check that adding a reference on a column still being backfilled fails.
# fix through cockroach#32917

statement ok
CREATE TABLE t32917 (a INT PRIMARY KEY)

statement ok
INSERT INTO t32917 VALUES (1), (2), (3)

statement ok
CREATE TABLE t32917_2 (b INT PRIMARY KEY)

statement ok
INSERT INTO t32917_2 VALUES (1), (2), (3)

statement ok
BEGIN

statement ok
ALTER TABLE t32917_2 ADD c INT UNIQUE DEFAULT 4

statement error adding a REFERENCES constraint while the column is being added not supported
ALTER TABLE t32917_2 ADD CONSTRAINT fk_c_a FOREIGN KEY (c) references t32917 (a)

statement ok
ROLLBACK