# 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