Materialize/test/sqllogictest/alter-table.slt

# 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';
----