alter_table

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 at or near "\*": syntax error
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 pq: multiple primary keys for table "t" are not allowed
ALTER TABLE t ADD CONSTRAINT bar PRIMARY KEY (b)

query TTBITTBB colnames
SHOW INDEXES FROM 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' OR job_type = 'SCHEMA CHANGE GC'
ORDER BY created DESC
LIMIT 2
----
SCHEMA CHANGE GC  GC for ROLLBACK of ALTER TABLE test.public.t ADD CONSTRAINT bar UNIQUE (c)  root  running   NULL  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' OR job_type = 'SCHEMA CHANGE GC'
ORDER BY created DESC
LIMIT 2
----
SCHEMA CHANGE GC  GC for DROP INDEX test.public.t@foo CASCADE  root  running    NULL  0  ·
SCHEMA CHANGE     DROP INDEX test.public.t@foo CASCADE         root  succeeded  NULL  1  ·

query TTBITTBB colnames
SHOW INDEXES FROM 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' OR job_type = 'SCHEMA CHANGE GC'
ORDER BY created DESC
LIMIT 2
----
SCHEMA CHANGE GC  GC for DROP INDEX test.public.t@t_f_idx   root  running    NULL  0  ·
SCHEMA CHANGE     DROP INDEX test.public.t@t_f_idx          root  succeeded  NULL  1  ·

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 #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)

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 (#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

# query TTBTTTB colnames
# SHOW COLUMNS FROM add_default
# ----
# column_name  data_type  is_nullable  column_default  generation_expression  indices                      is_hidden

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 pgcode 0A000 cannot evaluate scalar expressions containing sequence operations in this context
ALTER TABLE add_default ADD g INT DEFAULT nextval('initial_seq')

statement error pgcode 22C01 cannot evaluate scalar expressions using table lookups in this context
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

query T rowsort
SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name = 'sql.schema.change_in_explicit_txn'
----
sql.schema.change_in_explicit_txn

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)

statement error pgcode 42P01 relation "nonexistent" does not exist
ALTER TABLE nonexistent UNSPLIT ALL

statement error pgcode 42P01 relation "nonexistent" does not exist
ALTER INDEX nonexistent@noindex UNSPLIT ALL

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)

statement error pgcode 42809 "privsview" is not a table
ALTER TABLE privsview UNSPLIT ALL

# Verify that impure defaults are evaluated separately on each row
# (#14352)
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, FAMILY "primary" (a, b))

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;

query T
SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name = 'sql.schema.set_audit_mode.read_write'
----
sql.schema.set_audit_mode.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

statement ok
ALTER TABLE audit EXPERIMENTAL_AUDIT SET OFF

query T
SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name = 'sql.schema.set_audit_mode.off'
----
sql.schema.set_audit_mode.off

# 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 #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)

# Test SET NOT NULL
statement ok
CREATE TABLE t (a INT)

statement ok
INSERT INTO t VALUES (1), (NULL)

statement error validation of NOT NULL constraint failed: validation of CHECK "a IS NOT NULL" failed
ALTER TABLE t ALTER COLUMN a SET NOT NULL

statement ok
DELETE FROM t WHERE a IS NULL

statement ok
ALTER TABLE t ALTER COLUMN a SET NOT NULL

statement error null value in column "a" violates not-null constraint
INSERT INTO t VALUES (NULL)

query TTTTB
SHOW CONSTRAINTS FROM t
----

statement ok
ALTER TABLE t ALTER COLUMN a DROP NOT NULL

statement ok
INSERT INTO t VALUES (NULL)

statement ok
DROP TABLE t

# Test interaction of SET NOT NULL with other constraints
statement ok
CREATE TABLE t (a INT)

statement ok
INSERT INTO t VALUES (1)

# Check for name collisions with the auto-generated NOT NULL check constraint name
statement ok
ALTER TABLE t ADD CONSTRAINT a_auto_not_null CHECK (a IS NOT NULL)

statement ok
ALTER TABLE t ADD CONSTRAINT a_auto_not_null1 CHECK (a IS NOT NULL), ALTER COLUMN a SET NOT NULL

statement error null value in column "a" violates not-null constraint
INSERT INTO t VALUES (NULL)

query TTTTB
SHOW CONSTRAINTS FROM t
----
t  a_auto_not_null   CHECK  CHECK ((a IS NOT NULL))  true
t  a_auto_not_null1  CHECK  CHECK ((a IS NOT NULL))  true

statement ok
DROP TABLE t

# Check for adding constraints NOT VALID
statement ok
CREATE TABLE t (a int);

statement ok
INSERT INTO t VALUES (10), (15), (17)

statement error pq: validation of CHECK "a < 16" failed on row: a=17
ALTER TABLE t ADD CHECK (a < 16)

statement ok
ALTER TABLE t ADD CHECK (a < 100)

statement ok
ALTER TABLE t ADD CHECK (a < 16) NOT VALID

query TTTTB
SHOW CONSTRAINTS FROM t
----
t  check_a   CHECK  CHECK  ((a < 100))  true
t  check_a1  CHECK  CHECK  ((a < 16))  false

query error pq: failed to satisfy CHECK constraint \(a < 16\)
INSERT INTO t VALUES (20)

statement error pq: validation of CHECK "a < 16" failed on row: a=17
ALTER TABLE t VALIDATE CONSTRAINT check_a1

statement ok
DELETE FROM t WHERE a = 17

statement ok
ALTER TABLE t VALIDATE CONSTRAINT check_a1

query TTTTB
SHOW CONSTRAINTS FROM t
----
t  check_a   CHECK  CHECK  ((a < 100))  true
t  check_a1  CHECK  CHECK  ((a < 16))   true

subtest regression_42858

statement ok
CREATE TABLE TEST2 (COL1 SERIAL PRIMARY KEY, COL2 INT8)

statement ok
CREATE TABLE TEST1 (COL1 SERIAL PRIMARY KEY, COL2 INT8, COL3 INT8)

statement ok
ALTER TABLE TEST1 ADD CONSTRAINT duplicate_name FOREIGN KEY (COL2) REFERENCES TEST2 (COL1)

statement error pq: duplicate constraint name: "duplicate_name"
ALTER TABLE TEST1 ADD CONSTRAINT duplicate_name FOREIGN KEY (COL3) REFERENCES TEST2 (COL1)

statement ok
DROP TABLE test1; DROP TABLE test2

subtest multiple_set_drop_not_null_inside_txn

statement ok
CREATE TABLE t1(x INT, y INT);

statement ok
BEGIN;
ALTER TABLE t1 ALTER COLUMN x SET NOT NULL;
ALTER TABLE t1 ALTER COLUMN y SET NOT NULL;
COMMIT

statement ok
BEGIN;
ALTER TABLE t1 ALTER COLUMN x DROP NOT NULL;
ALTER TABLE t1 ALTER COLUMN y DROP NOT NULL;
COMMIT

statement ok
DROP TABLE t1

subtest regression_43092

statement ok
CREATE TABLE t43092(x INT PRIMARY KEY)

statement error pgcode 42P16 column "x" is in a primary index
ALTER TABLE t43092 ALTER COLUMN x DROP NOT NULL

statement ok
DROP TABLE t43092

subtest regression_qualification_feature_counts

statement ok
CREATE TABLE telemetry_test (d int);
ALTER TABLE telemetry_test
  ADD COLUMN a int DEFAULT 1,
  ADD COLUMN b int UNIQUE CHECK(b > 1),
  ADD COLUMN c int AS (a + b) STORED

query T rowsort
SELECT feature_name FROM crdb_internal.feature_usage
WHERE feature_name IN (
  'sql.schema.new_column.qualification.computed',
  'sql.schema.new_column.qualification.default_expr',
  'sql.schema.new_column.qualification.unique'
)
----
sql.schema.new_column.qualification.unique
sql.schema.new_column.qualification.computed
sql.schema.new_column.qualification.default_expr

statement ok
DROP TABLE telemetry_test

# Disable stats injection in explicit transactions.
statement ok
BEGIN

statement ok
CREATE TABLE inject_stats (k CHAR PRIMARY KEY, v TIMESTAMPTZ)

statement error pq: cannot inject statistics in an explicit transaction
ALTER TABLE inject_stats INJECT STATISTICS '[]'

statement ok
ROLLBACK

subtest regression_47141

statement ok
CREATE TABLE regression_47141(a time(3), b bytea)

statement error type conversion from TIME\(3\) to TIME\(2\) requires overwriting existing values which is not yet implemented
ALTER TABLE regression_47141 ALTER a SET DATA TYPE time(2)

# Regression for #26045.
statement ok
CREATE TABLE t25045 (x INT, y INT AS (x+1) STORED)

statement error pq: column \"x\" is referenced by computed column \"y\"
ALTER TABLE t25045 DROP COLUMN x

subtest add_col_references

statement ok
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (x INT PRIMARY KEY);
CREATE TABLE t2 (y INT)

statement ok
ALTER TABLE t2 ADD COLUMN x INT REFERENCES t1 (x)

statement ok
INSERT INTO t1 VALUES (1)

statement error pq: insert on table "t2" violates foreign key constraint "fk_x_ref_t1"
INSERT INTO t2 VALUES (2, 2)

statement ok
INSERT INTO t2 VALUES (1, 1)

# Error out trying to add a column with a foreign key on a non-empty table.
statement error pq: foreign key requires an existing index on columns \("z"\)
ALTER TABLE t2 ADD COLUMN z INT REFERENCES t1 (x)

# Check that the foreign key was indeed added.
query TT
SHOW CREATE t2
----
t2  CREATE TABLE t2 (
    y INT8 NULL,
    x INT8 NULL,
    CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x),
    INDEX t2_auto_index_fk_x_ref_t1 (x ASC),
    FAMILY "primary" (y, rowid, x)
)

# Test that only one index gets created when adding a column
# with references and unique.
statement ok
CREATE TABLE t3 (y INT)

statement ok
ALTER TABLE t3 ADD COLUMN x INT UNIQUE REFERENCES t1 (x)

query TT
SHOW CREATE t3
----
t3  CREATE TABLE t3 (
    y INT8 NULL,
    x INT8 NULL,
    CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x),
    UNIQUE INDEX t3_x_key (x ASC),
    FAMILY "primary" (y, rowid, x)
)

# We allowed the foreign key validation code to look into the mutations
# list to validate what columns / indexes can be used for foreign keys.
# Ensure that we still have the correct restrictions.
statement ok
DROP TABLE t1, t2 CASCADE;
CREATE TABLE t1 (x INT PRIMARY KEY);
CREATE TABLE t2 (x INT, y INT, INDEX i (x))

statement error pq: column \"x\" does not exist
BEGIN;
ALTER TABLE t2 DROP COLUMN x;
ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1(x);

statement ok
ROLLBACK

statement ok
INSERT INTO t2 VALUES (1, 2)

statement error pq: foreign key requires an existing index on columns \("x"\)
BEGIN;
DROP INDEX t2@i;
ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1(x)

statement ok
ROLLBACK

# Test using ADD COL REFERENCES in a self referencing constraint.
statement ok
DROP TABLE t1 CASCADE;
CREATE TABLE t1 (x INT PRIMARY KEY);
ALTER TABLE t1 ADD COLUMN x2 INT REFERENCES t1 (x)

query TT
SHOW CREATE t1
----
t1  CREATE TABLE t1 (
    x INT8 NOT NULL,
    x2 INT8 NULL,
    CONSTRAINT "primary" PRIMARY KEY (x ASC),
    CONSTRAINT fk_x2_ref_t1 FOREIGN KEY (x2) REFERENCES t1(x),
    INDEX t1_auto_index_fk_x2_ref_t1 (x2 ASC),
    FAMILY "primary" (x, x2)
)

statement error pq: insert on table "t1" violates foreign key constraint "fk_x2_ref_t1"
INSERT INTO t1 VALUES (1, 2)

# Test ADD COL REFERENCES on a new table in the same txn.
statement ok
DROP TABLE t1, t2 CASCADE

statement ok
BEGIN;
CREATE TABLE t1 (x INT PRIMARY KEY);
CREATE TABLE t2 (y INT);
ALTER TABLE t2 ADD COLUMN x INT REFERENCES t1 (x);
COMMIT

query TT
SHOW CREATE t2
----
t2  CREATE TABLE t2 (
    y INT8 NULL,
    x INT8 NULL,
    CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x),
    INDEX t2_auto_index_fk_x_ref_t1 (x ASC),
    FAMILY "primary" (y, rowid, x)
)

# Test that we can also add a column and then an FK in the same txn.
statement ok
DROP TABLE t1, t2 CASCADE

statement ok
BEGIN;
CREATE TABLE t1 (x INT PRIMARY KEY);
CREATE TABLE t2 (y INT);
ALTER TABLE t2 ADD COLUMN x INT;
ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1 (x);
COMMIT

query TT
SHOW CREATE t2
----
t2  CREATE TABLE t2 (
    y INT8 NULL,
    x INT8 NULL,
    CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x),
    INDEX t2_auto_index_fk_x_ref_t1 (x ASC),
    FAMILY "primary" (y, rowid, x)
)

# Test that we can add a column and an index to an FK in the same txn.
statement ok
DROP TABLE t1, t2 CASCADE

statement ok
BEGIN;
CREATE TABLE t1 (x INT PRIMARY KEY);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (y INT);
INSERT INTO t2 VALUES (2);
ALTER TABLE t2 ADD COLUMN x INT;
CREATE INDEX ON t2 (x);
ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1 (x);
COMMIT

query TT
SHOW CREATE t2
----
t2  CREATE TABLE t2 (
    y INT8 NULL,
    x INT8 NULL,
    CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x),
    INDEX t2_x_idx (x ASC),
    FAMILY "primary" (y, rowid, x)
)

# Test when default column value leads to an FK violation.
statement ok
DROP TABLE t1, t2 CASCADE

statement ok
CREATE TABLE t1 (x INT PRIMARY KEY);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (y INT);
INSERT INTO t2 VALUES (2)

statement error pq: foreign key violation
ALTER TABLE t2 ADD COLUMN x INT DEFAULT 2 UNIQUE REFERENCES t1 (x)

# Test that it works with an appropriate default.
statement ok
ALTER TABLE t2 ADD COLUMN x INT DEFAULT 1 UNIQUE REFERENCES t1 (x)

query TT
SHOW CREATE t2
----
t2  CREATE TABLE t2 (
    y INT8 NULL,
    x INT8 NULL DEFAULT 1:::INT8,
    CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x),
    UNIQUE INDEX t2_x_key (x ASC),
    FAMILY "primary" (y, rowid, x)
)