# 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. mode cockroach reset-server query error db error: ERROR: invalid table definition for "t1" CREATE TABLE t1 (a int, b text VERSION ADDED 0); statement ok CREATE TABLE t2 (a int); # TODO(alter_table): Manually specifying a VERSION when referencing an item (maybe?) shouldn't be allowed. statement ok CREATE VIEW v1 AS SELECT * FROM [u1 AS "materialize"."public"."t2" VERSION 0]; statement ok CREATE VIEW v2 AS SELECT * FROM t2; # TODO(alter_table): Sort out what is returned from SHOW CREATE. query TT SHOW CREATE VIEW v1 ---- materialize.public.v1 CREATE␠VIEW␠materialize.public.v1␠AS␠SELECT␠*␠FROM␠materialize.public.t2; # Note: When the feature is off we should not record versions. query TT SHOW CREATE VIEW v2 ---- materialize.public.v2 CREATE␠VIEW␠materialize.public.v2␠AS␠SELECT␠*␠FROM␠materialize.public.t2; statement ok DROP VIEW v1 CASCADE; statement ok DROP VIEW v2 CASCADE; simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_alter_table_add_column = true ---- COMPLETE 0 statement ok CREATE TABLE t1 (a int NOT NULL) statement error column "a" of relation "t1" already exists ALTER TABLE t1 ADD COLUMN a int; statement ok ALTER TABLE t1 ADD COLUMN IF NOT EXISTS a int; query error db error: ERROR: invalid version "1000" for "t2" CREATE VIEW v1 AS SELECT * FROM [u1 AS "materialize"."public"."t2" VERSION 1000]; query TTTT SHOW COLUMNS FROM t1; ---- a false integer (empty) statement ok INSERT INTO t1 VALUES (1), (2), (3); statement ok CREATE VIEW v1 AS SELECT * FROM t1; statement ok ALTER TABLE t1 ADD COLUMN b text; query TTTT SHOW COLUMNS FROM t1; ---- a false integer (empty) b true text (empty) query TT SHOW CREATE TABLE t1; ---- materialize.public.t1 CREATE␠TABLE␠materialize.public.t1␠(a␠pg_catalog.int4␠NOT␠NULL,␠b␠pg_catalog.text␠VERSION␠ADDED␠1); query TT SHOW CREATE VIEW v1; ---- materialize.public.v1 CREATE␠VIEW␠materialize.public.v1␠AS␠SELECT␠*␠FROM␠materialize.public.t1; statement ok CREATE VIEW v2 AS SELECT * FROM t1; query TTTT SHOW COLUMNS FROM v2; ---- a false integer (empty) b true text (empty) statement ok CREATE INDEX v2_idx ON v2 (a); statement ok CREATE MATERIALIZED VIEW mv1 AS SELECT SUM(a) FROM t1 WHERE b IS NOT NULL; statement ok INSERT INTO t1 VALUES (4, 'hello'), (5, 'world'); query I SELECT * FROM v1; ---- 1 2 3 4 5 query I SELECT * FROM mv1; ---- 9 query IT SELECT * FROM t1; ---- 1 NULL 2 NULL 3 NULL 4 hello 5 world query IT SELECT * FROM v2; ---- 1 NULL 2 NULL 3 NULL 4 hello 5 world statement ok ALTER TABLE t1 ADD COLUMN c timestamp; query TT SHOW CREATE TABLE t1; ---- materialize.public.t1 CREATE␠TABLE␠materialize.public.t1␠(a␠pg_catalog.int4␠NOT␠NULL,␠b␠pg_catalog.text␠VERSION␠ADDED␠1,␠c␠pg_catalog.timestamp␠VERSION␠ADDED␠2); query TTTT SHOW COLUMNS FROM t1; ---- a false integer (empty) b true text (empty) c true timestamp␠without␠time␠zone (empty) statement ok CREATE VIEW v3 AS SELECT * FROM t1; query TTTT SHOW COLUMNS FROM v3; ---- a false integer (empty) b true text (empty) c true timestamp␠without␠time␠zone (empty) statement ok CREATE VIEW v4 AS SELECT a, c FROM t1; query TTTT SHOW COLUMNS FROM v4; ---- a false integer (empty) c true timestamp␠without␠time␠zone (empty) statement ok DROP VIEW v1; statement ok INSERT INTO t1 VALUES (6, 'foo', '2020-01-01'::timestamp), (7, 'bar', '1000-01-01'::timestamp); query I SELECT * FROM mv1; ---- 22 statement ok UPDATE t1 SET b = 'later' WHERE a % 2 = 0; query I SELECT * FROM mv1; ---- 24 query ITT SELECT * FROM t1 ORDER BY a ASC; ---- 1 NULL NULL 2 later NULL 3 NULL NULL 4 later NULL 5 world NULL 6 later 2020-01-01␠00:00:00 7 bar 1000-01-01␠00:00:00 query IT SELECT * FROM v2 ORDER BY a ASC; ---- 1 NULL 2 later 3 NULL 4 later 5 world 6 later 7 bar query ITT SELECT * FROM v3 ORDER BY a ASC; ---- 1 NULL NULL 2 later NULL 3 NULL NULL 4 later NULL 5 world NULL 6 later 2020-01-01␠00:00:00 7 bar 1000-01-01␠00:00:00 query IT SELECT * FROM v4 ORDER BY a ASC; ---- 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL 6 2020-01-01␠00:00:00 7 1000-01-01␠00:00:00 statement ok DELETE FROM t1 WHERE b IS NULL; query I SELECT * FROM mv1; ---- 24 query ITT SELECT * FROM t1 ORDER BY a ASC; ---- 2 later NULL 4 later NULL 5 world NULL 6 later 2020-01-01␠00:00:00 7 bar 1000-01-01␠00:00:00 query IT SELECT * FROM v2 ORDER BY a ASC; ---- 2 later 4 later 5 world 6 later 7 bar query ITT SELECT * FROM v3 ORDER BY a ASC; ---- 2 later NULL 4 later NULL 5 world NULL 6 later 2020-01-01␠00:00:00 7 bar 1000-01-01␠00:00:00 query IT SELECT * FROM v4 ORDER BY a ASC; ---- 2 NULL 4 NULL 5 NULL 6 2020-01-01␠00:00:00 7 1000-01-01␠00:00:00 statement ok CREATE INDEX v3_idx_full_scan ON v3 (b); query IIITTT SELECT v2.a, v3.a, v4.a, v2.b, v3.b, v4.c FROM v2 JOIN v3 ON v2.a = v3.a JOIN v4 ON v2.a = v4.a; ---- 2 2 2 later later NULL 4 4 4 later later NULL 5 5 5 world world NULL 6 6 6 later later 2020-01-01␠00:00:00 7 7 7 bar bar 1000-01-01␠00:00:00 query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT v2.a, v3.a, v4.a, v2.b, v3.b, v4.c FROM v2 JOIN v3 ON v2.a = v3.a JOIN v4 ON v2.a = v4.a; ---- Explained Query: Project (#0{a}, #0{a}, #0{a}, #1{b}, #3{b}, #5{c}) Join on=(#0{a} = #2{a} = #4{a}) type=delta ArrangeBy keys=[[#0{a}]] ReadIndex on=v2 v2_idx=[delta join 1st input (full scan)] ArrangeBy keys=[[#0{a}]] Project (#0{a}, #1{b}) ReadIndex on=v3 v3_idx_full_scan=[*** full scan ***] ArrangeBy keys=[[#0{a}]] Project (#0{a}, #2{c}) ReadStorage materialize.public.t1 Source materialize.public.t1 Used Indexes: - materialize.public.v2_idx (delta join 1st input (full scan)) - materialize.public.v3_idx_full_scan (*** full scan ***) Target cluster: quickstart EOF # Should be the same results as above. query IIITTT SELECT v2.a, v3.a, v4.a, v2.b, v3.b, v4.c FROM v2 JOIN v3 ON v2.a = v3.a JOIN v4 ON v2.a = v4.a; ---- 2 2 2 later later NULL 4 4 4 later later NULL 5 5 5 world world NULL 6 6 6 later later 2020-01-01␠00:00:00 7 7 7 bar bar 1000-01-01␠00:00:00 statement ok DROP INDEX v3_idx_full_scan; statement ok CREATE INDEX t1_idx_a ON t1 (a); query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT v2.a, v3.a, v4.a, v2.b, v3.b, v4.c FROM v2 JOIN v3 ON v2.a = v3.a JOIN v4 ON v2.a = v4.a; ---- Explained Query: With cte l0 = ArrangeBy keys=[[#0{a}]] ReadIndex on=t1 t1_idx_a=[delta join lookup] Return Project (#0{a}, #0{a}, #0{a}, #1{b}, #3{b}, #7{c}) Join on=(#0{a} = #2{a} = #5{a}) type=delta ArrangeBy keys=[[#0{a}]] ReadIndex on=v2 v2_idx=[delta join 1st input (full scan)] Get l0 Get l0 Used Indexes: - materialize.public.v2_idx (delta join 1st input (full scan)) - materialize.public.t1_idx_a (delta join lookup) Target cluster: quickstart EOF # Should be the same results as above. query IIITTT SELECT v2.a, v3.a, v4.a, v2.b, v3.b, v4.c FROM v2 JOIN v3 ON v2.a = v3.a JOIN v4 ON v2.a = v4.a; ---- 2 2 2 later later NULL 4 4 4 later later NULL 5 5 5 world world NULL 6 6 6 later later 2020-01-01␠00:00:00 7 7 7 bar bar 1000-01-01␠00:00:00 simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_alter_table_add_column = false; ---- COMPLETE 0 # We should be able to select from existing views even if the feature gets turned off. query IT SELECT * FROM v2 ORDER BY a ASC; ---- 2 later 4 later 5 world 6 later 7 bar statement ok INSERT INTO t1 VALUES (8, 'apple', '2024-09-20'::timestamp); query IT SELECT * FROM v2 ORDER BY a ASC; ---- 2 later 4 later 5 world 6 later 7 bar 8 apple simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_alter_table_add_column = true; ---- COMPLETE 0 statement ok CREATE VIEW v_on_t2 AS SELECT * FROM t2; statement ok ALTER TABLE t2 ADD COLUMN b text; statement ok DROP TABLE t1 CASCADE; query TT SELECT id, name FROM mz_tables WHERE id LIKE 'u%'; ---- u1 t2 statement ok COMMENT ON COLUMN t2.a IS 'this column existed originally'; query TTIT SELECT * FROM mz_internal.mz_comments WHERE id = 'u1'; ---- u1 table 1 this␠column␠existed␠originally statement ok ALTER TABLE t2 ADD COLUMN c timestamp; statement ok COMMENT ON COLUMN t2.c IS 'added later'; query TTIT rowsort SELECT * FROM mz_internal.mz_comments WHERE id = 'u1'; ---- u1 table 3 added␠later u1 table 1 this␠column␠existed␠originally statement ok DROP TABLE t2 CASCADE; query TTIT SELECT * FROM mz_internal.mz_comments WHERE id = 'u1'; ----