views

# knob-opt: sync-event-log

statement ok
SET CLUSTER SETTING sql.cross_db_views.enabled = TRUE

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

let $t_id
SELECT id FROM system.namespace WHERE name='t'

statement ok
INSERT INTO t VALUES (1, 99), (2, 98), (3, 97)

statement ok
CREATE VIEW v1 AS SELECT a, b FROM t

statement error pgcode 42P07 relation \"test.public.v1\" already exists
CREATE VIEW v1 AS SELECT a, b FROM t

statement error pgcode 42P07 relation \"test.public.t\" already exists
CREATE VIEW t AS SELECT a, b FROM t

# view statement ignored if other way around.
statement ok
CREATE VIEW IF NOT EXISTS v1 AS SELECT b, a FROM v1

statement ok
CREATE VIEW IF NOT EXISTS v2 (x, y) AS SELECT a, b FROM t

statement error pgcode 42601 CREATE VIEW specifies 1 column name, but data source has 2 columns
CREATE VIEW v3 (x) AS SELECT a, b FROM t

statement error pgcode 42601 CREATE VIEW specifies 3 column names, but data source has 2 columns
CREATE VIEW v4 (x, y, z) AS SELECT a, b FROM t

statement error pgcode 42P01 relation "dne" does not exist
CREATE VIEW v5 AS SELECT a, b FROM dne

statement ok
CREATE VIEW v6 (x, y) AS SELECT a, b FROM v1

statement ok
CREATE VIEW v7 (x, y) AS SELECT a, b FROM v1 ORDER BY a DESC LIMIT 2

statement error pgcode 42703 column \"j\" does not exist
CREATE VIEW err AS SELECT j FROM t

statement error pgcode 42703 column \"j\" does not exist
CREATE VIEW err AS SELECT a FROM t WHERE a = j

query II colnames,rowsort
SELECT * FROM v1
----
a b
1 99
2 98
3 97

query II colnames,rowsort
SELECT * FROM v2
----
x y
1 99
2 98
3 97

query II colnames,rowsort
SELECT * FROM v6
----
x y
1 99
2 98
3 97

query II colnames
SELECT * FROM v7
----
x y
3 97
2 98

query II colnames
SELECT * FROM v7 ORDER BY x LIMIT 1
----
x y
2 98

query II
SELECT * FROM v2 ORDER BY x DESC LIMIT 1
----
3 97

query I rowsort
SELECT x FROM v2
----
1
2
3

query I rowsort
SELECT y FROM v2
----
99
98
97

query I
SELECT x FROM v7
----
3
2

query I
SELECT x FROM v7 ORDER BY x LIMIT 1
----
2

query I
SELECT y FROM v7
----
97
98

query I
SELECT y FROM v7 ORDER BY x LIMIT 1
----
98

query IIII rowsort
SELECT * FROM v1 AS v1 INNER JOIN v2 AS v2 ON v1.a = v2.x
----
1 99 1 99
2 98 2 98
3 97 3 97

statement ok
CREATE DATABASE test2

statement ok
SET DATABASE = test2

statement ok
CREATE VIEW t1 AS SELECT ARRAY[]:::STRING[];
CREATE VIEW t2 AS SELECT ARRAY[1,2,3]:::INT[];


query TT colnames
SHOW CREATE VIEW t1
----
table_name  create_statement
t1          CREATE VIEW public.t1 (
              "array"
            ) AS SELECT ARRAY[]::STRING[]:::STRING[]

query TT colnames
SHOW CREATE VIEW t2
----
table_name  create_statement
t2          CREATE VIEW public.t2 (
              "array"
            ) AS SELECT ARRAY[1:::INT8, 2:::INT8, 3:::INT8]::INT8[]:::INT8[]

query II colnames,rowsort
SELECT * FROM test.v1
----
a b
1 99
2 98
3 97

query II colnames,rowsort
SELECT * FROM test.v2
----
x y
1 99
2 98
3 97

query II colnames,rowsort
SELECT * FROM test.v6
----
x y
1 99
2 98
3 97

query II colnames
SELECT * FROM test.v7
----
x y
3 97
2 98

query II colnames
SELECT * FROM test.v7 ORDER BY x LIMIT 1
----
x y
2 98

statement ok
CREATE VIEW v1 AS SELECT x, y FROM test.v2

statement ok
SET DATABASE = test

query II colnames,rowsort
SELECT * FROM test2.v1
----
x y
1 99
2 98
3 97

query TT
SHOW CREATE VIEW v1
----
v1  CREATE VIEW public.v1 (
      a,
      b
    ) AS SELECT a, b FROM test.public.t

query TT
SHOW CREATE VIEW v2
----
v2  CREATE VIEW public.v2 (
      x,
      y
    ) AS SELECT a, b FROM test.public.t

query TT
SHOW CREATE VIEW v6
----
v6  CREATE VIEW public.v6 (
      x,
      y
    ) AS SELECT a, b FROM test.public.v1

query TT
SHOW CREATE VIEW v7
----
v7  CREATE VIEW public.v7 (
      x,
      y
    ) AS SELECT a, b FROM test.public.v1 ORDER BY a DESC LIMIT 2

query TT
SHOW CREATE VIEW test2.v1
----
test2.public.v1  CREATE VIEW public.v1 (
                   x,
                   y
                 ) AS SELECT x, y FROM test.public.v2

statement ok
GRANT SELECT ON t TO testuser

user testuser

query II rowsort
SELECT * FROM t
----
1 99
2 98
3 97

query error user testuser does not have SELECT privilege on relation v1
SELECT * FROM v1

query error user testuser does not have SELECT privilege on relation v6
SELECT * FROM v6

query error user testuser has no privileges on relation v1
SHOW CREATE VIEW v1

user root

statement ok
REVOKE SELECT ON t FROM testuser

statement ok
GRANT SELECT ON v1 TO testuser

user testuser

query error user testuser does not have SELECT privilege on relation t
SELECT * FROM t

query II rowsort
SELECT * FROM v1
----
1 99
2 98
3 97

query error user testuser does not have SELECT privilege on relation v6
SELECT * FROM v6

query TT
SHOW CREATE VIEW v1
----
v1  CREATE VIEW public.v1 (
      a,
      b
    ) AS SELECT a, b FROM test.public.t

user root

statement ok
REVOKE SELECT ON v1 FROM testuser

statement ok
GRANT SELECT ON v6 TO testuser

user testuser

query error user testuser does not have SELECT privilege on relation t
SELECT * FROM t

query error user testuser does not have SELECT privilege on relation v1
SELECT * FROM v1

query II rowsort
SELECT * FROM v6
----
1 99
2 98
3 97

user root

# Ensure that views work over tables identified by numeric reference.
statement ok
CREATE VIEW num_ref_view AS SELECT a, b FROM [$t_id AS t]

statement ok
GRANT SELECT ON num_ref_view TO testuser

user testuser

query II rowsort
SELECT * FROM num_ref_view
----
1 99
2 98
3 97

user root

statement ok
DROP VIEW num_ref_view

statement error pgcode 42809 "v1" is not a table
DROP TABLE v1

statement error pgcode 42809 "t" is not a view
DROP VIEW t

skipif config local-legacy-schema-changer
statement error cannot drop relation "v1" because view "v7" depends on it
DROP VIEW v1

# Legacy schema changer orders dependent views differently, but
# semantically the same error.
onlyif config local-legacy-schema-changer
statement error cannot drop relation "v1" because view "v6" depends on it
DROP VIEW v1

statement error cannot drop relation "v2" because view "test2.public.v1" depends on it
DROP VIEW v2

statement ok
DROP VIEW test2.v1

statement ok
DROP VIEW v7

statement ok
DROP VIEW v6

statement ok
DROP VIEW v2

statement ok
DROP VIEW v1

statement error pgcode 42P01 relation "v1" does not exist
DROP VIEW v1

# Verify that we can depend on virtual tables.
statement ok
CREATE VIEW virt1 AS SELECT table_schema FROM information_schema.columns

statement ok
DROP VIEW virt1

# Verify that we can depend on virtual views.
statement ok
CREATE VIEW virt2 AS SELECT range_id, lease_holder FROM crdb_internal.ranges

statement ok
DROP VIEW virt2

# Verify correct rejection of star expressions
# TODO(a-robinson): Support star expressions as soon as we can (#10028)

statement error views do not currently support \* expressions
create view s1 AS SELECT * FROM t

statement error views do not currently support \* expressions
create view s1 AS SELECT t.* FROM t

statement error views do not currently support \* expressions
create view s1 AS SELECT a FROM t ORDER BY t.*

statement error views do not currently support \* expressions
create view s1 AS SELECT count(1) FROM t GROUP BY t.*

statement error views do not currently support \* expressions
create view s1 AS SELECT alias.* FROM t AS alias

statement error views do not currently support \* expressions
create view s1 AS TABLE t

statement error views do not currently support \* expressions
create view s1 AS SELECT a FROM (SELECT * FROM t)

statement error views do not currently support \* expressions
create view s1 AS SELECT a FROM t WHERE NOT a IN (SELECT a FROM (SELECT * FROM t))

statement error views do not currently support \* expressions
create view s1 AS SELECT a FROM t GROUP BY a HAVING a IN (SELECT a FROM (SELECT * FROM t))

statement error views do not currently support \* expressions
create view s1 AS SELECT t1.*, t2.a FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a

statement error views do not currently support \* expressions
create view s1 AS SELECT t1.a, t2.* FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a

statement error views do not currently support \* expressions
create view s1 AS SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a

statement error views do not currently support \* expressions
create view s1 AS SELECT * FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a

statement error views do not currently support \* expressions
create view s1 AS SELECT t1.a, t2.a FROM (SELECT * FROM t) AS t1 JOIN t AS t2 ON t1.a = t2.a

statement error views do not currently support \* expressions
create view s1 AS SELECT t1.a, t2.a FROM t AS t1 JOIN (SELECT * FROM t) AS t2 ON t1.a = t2.a

statement error views do not currently support \* expressions
create view s1 AS SELECT t1.a, t2.a FROM t AS t1 JOIN t AS t2 ON t1.a IN (SELECT a FROM (SELECT * FROM t))

statement ok
create view s1 AS SELECT count(*) FROM t

statement ok
create view s2 AS SELECT a FROM t WHERE a IN (SELECT count(*) FROM t)

statement ok
create view s3 AS SELECT a, count(*) FROM t GROUP BY a

statement ok
create view s4 AS SELECT a, count(*) FROM t GROUP BY a HAVING a > (SELECT count(*) FROM t)

statement ok
DROP VIEW s4

statement ok
DROP VIEW s3

statement ok
DROP VIEW s2

statement ok
DROP VIEW s1

statement ok
DROP TABLE t

# Check for memory leak (#10466)
statement ok
CREATE VIEW foo AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata

statement error pq: relation "test.public.foo" already exists
CREATE VIEW foo AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata

# Ensure views work with dates/timestamps (#12420)
statement ok
CREATE TABLE t (d DATE, t TIMESTAMP)

statement ok
CREATE VIEW dt AS SELECT d, t FROM t WHERE d > DATE '1988-11-12' AND t < TIMESTAMP '2017-01-01'

statement ok
SELECT * FROM dt

statement ok
CREATE VIEW dt2 AS SELECT d, t FROM t WHERE d > d + INTERVAL '10h'

statement ok
SELECT * FROM dt2

# Ensure that creating a view doesn't leak any session-level settings that
# could affect subsequent AS OF SYSTEM TIME queries (#13547).
statement ok
CREATE VIEW v AS SELECT d, t FROM t

statement error pq: AS OF SYSTEM TIME must be provided on a top-level statement
CREATE TABLE t2 AS SELECT d, t FROM t AS OF SYSTEM TIME '2017-02-13 21:30:00'

statement ok
DROP TABLE t CASCADE

statement ok
CREATE TABLE t (a INT[])

statement ok
INSERT INTO t VALUES (array[1,2,3])

statement ok
CREATE VIEW b AS SELECT a[1] FROM t

query I
SELECT * FROM b
----
1

statement ok
DROP TABLE t CASCADE

statement ok
CREATE VIEW arr(a) AS SELECT ARRAY[3]

query TI
SELECT *, a[1] FROM arr
----
{3}  3

# Regression for #15951

statement ok
CREATE TABLE t15951 (a int, b int)

statement ok
CREATE VIEW Caps15951 AS SELECT a, b FROM t15951

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

query R
SELECT sum (Caps15951. a) FROM Caps15951 GROUP BY b ORDER BY b
----
1
3
6

query R
SELECT sum ("caps15951". a) FROM "caps15951" GROUP BY b ORDER BY b
----
1
3
6

statement ok
CREATE VIEW "QuotedCaps15951" AS SELECT a, b FROM t15951

query R
SELECT sum ("QuotedCaps15951". a) FROM "QuotedCaps15951" GROUP BY b ORDER BY b
----
1
3
6

# Regression tests for #23833

statement ok
CREATE VIEW w AS WITH a AS (SELECT 1 AS x) SELECT x FROM a

query T
SELECT create_statement FROM [SHOW CREATE w]
----
CREATE VIEW public.w (
  x
) AS WITH a AS (SELECT 1 AS x) SELECT x FROM a

statement ok
CREATE VIEW w2 AS WITH t AS (SELECT x FROM w) SELECT x FROM t

query T
SELECT create_statement FROM [SHOW CREATE w2]
----
CREATE VIEW public.w2 (
  x
) AS WITH t AS (SELECT x FROM test.public.w) SELECT x FROM t

statement ok
CREATE VIEW w3 AS (WITH t AS (SELECT x FROM w) SELECT x FROM t)

query T
SELECT create_statement FROM [SHOW CREATE w3]
----
CREATE VIEW public.w3 (
  x
) AS (WITH t AS (SELECT x FROM test.public.w) SELECT x FROM t)

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

statement error INSERT cannot be used inside a view definition
CREATE VIEW crud_view AS SELECT a, b FROM [INSERT INTO ab VALUES (100, 100) RETURNING a, b]

statement ok
CREATE TABLE cd (c INT PRIMARY KEY, b INT)

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

statement ok
INSERT INTO cd VALUES (2, 2), (3, 3), (4, 4)

# View containing a correlated subquery.
statement ok
CREATE VIEW v1 AS SELECT a, b, EXISTS(SELECT c FROM cd WHERE cd.c=ab.a) FROM ab;

query IIB rowsort
SELECT * FROM v1
----
1  1  false
2  2  true
3  3  true

# Regression test for #47704: the columns inside PARITION BY and ORDER BY were
# losing their qualification.
statement ok
CREATE TABLE a47704 (foo UUID);
CREATE TABLE b47704 (foo UUID)

statement ok
CREATE VIEW v47704 AS
  SELECT first_value(a47704.foo) OVER (PARTITION BY a47704.foo ORDER BY a47704.foo)
  FROM a47704 JOIN b47704 ON a47704.foo = b47704.foo

# Verify that the descriptor did not "lose" the column qualification inside
# PARITION BY and ORDER BY.
query T
SELECT create_statement FROM [ SHOW CREATE VIEW v47704 ]
----
CREATE VIEW public.v47704 (
  first_value
) AS SELECT
    first_value(a47704.foo) OVER (PARTITION BY a47704.foo ORDER BY a47704.foo)
  FROM
    test.public.a47704 JOIN test.public.b47704 ON a47704.foo = b47704.foo

statement ok
SELECT * FROM v47704

subtest create_or_replace

user root

statement ok
DROP TABLE IF EXISTS t, t2;
CREATE TABLE t (x INT);
INSERT INTO t VALUES (1), (2);
CREATE TABLE t2 (x INT);
INSERT INTO t2 VALUES (3), (4);

# Test some error cases.

statement error pq: \"t\" is not a view
CREATE OR REPLACE VIEW t AS VALUES (1)

statement ok
CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2 FROM t

# Test cases where new columns don't line up.

statement error pq: cannot drop columns from view
CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1 FROM t

statement error pq: cannot change name of view column \"x\" to \"xy\"
CREATE OR REPLACE VIEW tview AS SELECT x AS xy, x+1 AS x1, x+2 AS x2 FROM t

statement error pq: cannot change type of view column "x1" from int to string
CREATE OR REPLACE VIEW tview AS SELECT x AS x, (x+1)::STRING AS x1, x+2 AS x2 FROM t

statement ok
CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t

query IIII rowsort
SELECT * FROM tview
----
1 2 3 4
2 3 4 5

# Test cases where back references get updated.
statement ok
CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2

query IIII rowsort
SELECT * FROM tview
----
3 4 5 6
4 5 6 7

# After remaking tview, it no longer depends on t.
statement ok
DROP TABLE t

# However, we now depend on t2.
statement error cannot drop relation "t2" because view "tview" depends on it
DROP TABLE t2

# Test that if we add a reference to something in t2 and use it when replacing
# the view that we now reference that object as well.
statement ok
CREATE INDEX i ON t2 (x);
CREATE INDEX i2 ON t2 (x);

statement ok
CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2@i

statement error pq: cannot drop index \"i\" because view \"tview\" depends on it
DROP INDEX t2@i

# However, if we change the view, we should be able to drop i.
statement ok
CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2@i2;
DROP INDEX t2@i

# ... and not i2.
statement error pq: cannot drop index \"i2\" because view \"tview\" depends on it
DROP INDEX t2@i2

# Ensure that users can't replace views they don't have privilege to.
statement ok
GRANT CREATE ON DATABASE test TO testuser;
GRANT CREATE, SELECT ON TABLE tview, t2 TO testuser

user testuser

statement error pq: user testuser does not have DROP privilege on relation tview
CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2

# Give privilege now.
user root

statement ok
GRANT DROP ON TABLE tview TO testuser

user testuser

statement ok
CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2

user root

# Ensure a view that contains a table that is referenced multiple times with
# different column sets depends on the correct columns.
# Depended on columns should not be droppable.

# Only column a should be depended on in this case.
statement ok
DROP TABLE ab CASCADE;

statement ok
CREATE TABLE ab (a INT, b INT);
CREATE VIEW vab (x) AS SELECT ab.a FROM ab, ab AS ab2

statement ok
ALTER TABLE ab DROP COLUMN b

statement error pq: cannot drop column "a" because view "vab" depends on it
ALTER TABLE ab DROP COLUMN a

statement ok
CREATE TABLE abc (a INT, b INT, c INT);
CREATE VIEW vabc AS SELECT abc.a, abc2.b, abc3.c FROM abc, abc AS abc2, abc AS abc3

# All three columns a,b,c should not be droppable.
statement error pq: cannot drop column "a" because view "vabc" depends on it
ALTER TABLE abc DROP COLUMN a

statement error pq: cannot drop column "b" because view "vabc" depends on it
ALTER TABLE abc DROP COLUMN b

statement error pq: cannot drop column "c" because view "vabc" depends on it
ALTER TABLE abc DROP COLUMN c

# RegClass objects used in expressions should be added to view dependencies.
statement ok
CREATE TABLE toreg();
CREATE VIEW vregclass AS SELECT 'toreg'::regclass

statement error pq: cannot drop relation "toreg" because view "vregclass" depends on it
DROP TABLE toreg;

statement ok
DROP VIEW vregclass;

statement ok
CREATE VIEW vregclass AS SELECT x FROM (SELECT CAST('toreg' AS regclass) AS x)

statement error pq: cannot drop relation "toreg" because view "vregclass" depends on it
DROP TABLE toreg;

statement ok
DROP VIEW vregclass;

statement ok
CREATE SEQUENCE s_reg;
CREATE VIEW vregclass AS SELECT x FROM [SELECT 's_reg'::REGCLASS AS x]

statement error pq: cannot drop sequence s_reg because other objects depend on it
DROP SEQUENCE s_reg

# RegClass dependencies should not be added if the RegClass expression contains
# a variable.
statement ok
DROP VIEW vregclass;

statement ok
CREATE VIEW vregclass AS SELECT x::regclass FROM (SELECT 's_reg' AS x);
DROP SEQUENCE s_reg;

statement ok
DROP VIEW vregclass;

statement ok
CREATE VIEW vregclass AS SELECT x::regclass FROM (SELECT 'does_not_exist' AS x);

statement error pq: relation "does_not_exist" does not exist
SELECT * FROM vregclass

statement ok
DROP VIEW vregclass;

statement ok
CREATE table tregclass();

statement ok
CREATE VIEW vregclass AS SELECT 1 FROM (SELECT 1) AS foo WHERE 'tregclass'::regclass = 'tregclass'::regclass;

statement error pq: cannot drop relation "tregclass" because view "vregclass" depends on it
DROP TABLE tregclass

# Test that we disallow cross-database references from views, depending on the
# cluster setting.
subtest cross_db_views

statement ok
SET CLUSTER SETTING sql.cross_db_views.enabled = FALSE

statement ok
CREATE DATABASE db1

statement ok
CREATE DATABASE db2

statement ok
USE db1

statement ok
CREATE TABLE ab (a INT, b INT)

statement ok
CREATE VIEW v1 AS SELECT a+b FROM ab

statement ok
CREATE VIEW db1.public.v2 AS SELECT a+b FROM db1.public.ab

statement error the view cannot refer to other databases
CREATE VIEW db2.v AS SELECT a+b FROM db1.public.ab

statement ok
CREATE VIEW db2.replace AS SELECT 1

statement error the view cannot refer to other databases
CREATE OR REPLACE VIEW db2.replace AS SELECT a+b FROM db1.public.ab

statement ok
CREATE SEQUENCE db2.seq

statement error the view cannot refer to other databases
CREATE VIEW v2 AS SELECT last_value FROM db2.seq

# Verify that cross-schema views are allowed.
statement ok
CREATE SCHEMA sc2

statement ok
CREATE VIEW db1.sc2.v AS SELECT a+b FROM db1.public.ab

statement ok
CREATE TABLE db1.sc2.cd (c INT, d INT)

statement ok
CREATE VIEW db1.public.v3 AS SELECT a+b+c+d FROM db1.public.ab, db1.sc2.cd

# Verify that views involving system tables are allowed.

statement ok
CREATE VIEW sys AS SELECT id FROM system.descriptor

statement ok
CREATE VIEW sys2 AS SELECT id, a+b FROM system.descriptor, ab

statement ok
USE db2

statement ok
CREATE TABLE cd (c INT, d INT)

statement error the view cannot refer to other databases
CREATE VIEW v AS SELECT a+b+c+d FROM cd, db1.public.ab

statement ok
SET CLUSTER SETTING sql.cross_db_views.enabled = TRUE

statement ok
CREATE VIEW db2.v1 AS SELECT a+b FROM db1.public.ab

statement ok
CREATE VIEW db2.v2 AS SELECT a+b+c+d FROM cd, db1.public.ab

# Validate that cross DB views are detected by internal tables

statement ok
USE DB1;

statement ok
CREATE SEQUENCE SQ1;

statement ok
CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');

statement ok
CREATE TABLE tval (val int primary key);

statement ok
CREATE VIEW rv as select val from tval;

statement ok;
USE DB2;

# Cross-database type references are not allowed.
statement error cross database type references are not supported: db1.public.status
CREATE VIEW vm as (select cast('open' as db1.status) from db1.tval as t)

# If the view is being created in the same database as the type, that is allowed.
statement ok
CREATE VIEW db1.vm as (select cast('open' as db1.status) from db1.tval as t)

statement ok
CREATE TYPE db2.status AS ENUM ('open', 'closed', 'inactive');

statement ok
CREATE VIEW vm as (select s.last_value, t.val as a, v.val as b, cast('open' as db2.status) from db1.tval as t, db1.sq1 as s, db1.rv as v)

query TTTTTTT
select * from "".crdb_internal.cross_db_references order by object_database, object_schema, object_name, referenced_object_database, referenced_object_schema, referenced_object_name desc;
----
db1  public  sys   system  public  descriptor  view references table
db1  public  sys2  system  public  descriptor  view references table
db2  public  v1    db1     public  ab          view references table
db2  public  v2    db1     public  ab          view references table
db2  public  vm    db1     public  tval        view references table
db2  public  vm    db1     public  sq1         view references table
db2  public  vm    db1     public  rv          view references table


# Test that user defined types used in views are tracked.
subtest view_user_defined_types

statement ok
CREATE TYPE typ AS ENUM('a')

statement ok
CREATE VIEW v AS (SELECT 'a'::typ::string AS k)

statement error cannot drop type "typ" because other objects \(\[db2.public.v\]\) still depend on it
DROP TYPE typ

statement ok
DROP VIEW v

statement ok
CREATE VIEW v AS (WITH r AS (SELECT 'a'::typ < 'a'::typ AS k) SELECT k FROM r)

statement error cannot drop type "typ" because other objects \(\[db2.public.v\]\) still depend on it
DROP TYPE typ

statement ok
DROP VIEW v

statement ok
CREATE TABLE t (i INT, k STRING AS ('a'::typ::string) STORED)

statement ok
CREATE VIEW v AS (SELECT i FROM t)

# Note that v does not depend on typ since it does not use column k.
statement error cannot drop type "typ" because other objects \(\[db2.public.t\]\) still depend on it
DROP TYPE typ

statement ok
CREATE VIEW v_dep AS (SELECT k FROM t)

# Since v_dep depends on t.k which uses type typ, v_dep has a dependency to typ.
statement error cannot drop type "typ" because other objects \(\[db2.public.t db2.public.v_dep\]\) still depend on it
DROP TYPE typ

statement ok
CREATE TYPE typ2 AS ENUM('a')

statement ok
CREATE VIEW v3 AS (SELECT 'a'::typ2::string AS k)

statement error cannot drop type "typ2" because other objects \(\[db2.public.v3\]\) still depend on it
DROP TYPE typ2

statement ok
CREATE OR REPLACE VIEW v3 AS (SELECT 'a' AS k)

statement ok
DROP TYPE typ2

statement ok
CREATE TYPE typ2 AS ENUM('a')

statement ok
CREATE OR REPLACE VIEW v3 AS (SELECT 'a'::typ2::string AS k)

statement error cannot drop type "typ2" because other objects \(\[db2.public.v3\]\) still depend on it
DROP TYPE typ2

statement ok
ALTER TYPE typ2 RENAME TO typ3

statement error cannot drop type "typ3" because other objects \(\[db2.public.v3\]\) still depend on it
DROP TYPE typ3

statement ok
CREATE TYPE typ4 AS ENUM('a')

statement ok
CREATE TABLE t4 (i INT, j typ4)

statement ok
CREATE VIEW v4 AS (SELECT i FROM t4)

# Note that v4 does not depend on typ4.
statement error cannot drop type "typ4" because other objects \(\[db2.public.t4\]\) still depend on it
DROP TYPE typ4

statement ok
ALTER TABLE t4 DROP COLUMN j

statement ok
DROP TYPE typ4

statement ok
CREATE TYPE typ4 AS ENUM('a')

statement ok
ALTER TABLE t4 ADD COLUMN j typ4

statement ok
CREATE VIEW v4_dep AS (SELECT j FROM t4)

# Since v4_dep depends on t4.j which is of type typ4, v4_dep has a dependency to typ4.
statement error cannot drop type "typ4" because other objects \(\[db2.public.t4 db2.public.v4_dep\]\) still depend on it
DROP type typ4

statement ok
CREATE TYPE typ5 AS ENUM('a')

statement ok
CREATE TABLE t5 (i INT, j STRING DEFAULT 'a'::typ5::string)

# Note that v5 does not depend on typ5.
statement ok
CREATE VIEW v5 AS (SELECT i FROM t5)

statement error cannot drop type "typ5" because other objects \(\[db2.public.t5\]\) still depend on it
DROP TYPE typ5

statement ok
CREATE VIEW v5_dep AS (SELECT j FROM t5)

# Since v5_dep depends on t5.j which uses type typ5, v5_dep has a dependency to typ5.
statement error cannot drop type "typ5" because other objects \(\[db2.public.t5 db2.public.v5_dep\]\) still depend on it
DROP TYPE typ5

statement ok
CREATE VIEW v6 AS (SELECT j FROM v4_dep)

# v6 depends on v4_dep.j, which depends on t4.j, which depends on typ4, so v6 also depends on typ4.
statement error cannot drop type "typ4" because other objects \(\[db2.public.t4 db2.public.v4_dep db2.public.v6\]\) still depend on it
DROP TYPE typ4

statement ok
CREATE TYPE typ6 AS ENUM('a');
CREATE TABLE t6 (i INT, k typ6);
CREATE INDEX idx ON t6 (i) WHERE k < 'a'::typ6

statement ok
CREATE VIEW v7 AS (SELECT i FROM t6)

# Note that v7 does not depend on t6.
statement error cannot drop type "typ6" because other objects \(\[db2.public.t6\]\) still depend on it
DROP TYPE typ6

statement ok
CREATE VIEW v7_dep AS (SELECT i FROM t6@idx WHERE k < 'a'::typ6)

# v7_dep depends on typ6 now.
statement error cannot drop type "typ6" because other objects \(\[db2.public.t6 db2.public.v7_dep\]\) still depend on it
DROP TYPE typ6

# Test we can create views from various data sources.
statement ok
CREATE SEQUENCE s

statement ok
CREATE VIEW v8 AS (SELECT last_value FROM s)

statement ok
CREATE VIEW v9 AS (SELECT sequence_name FROM information_schema.sequences)


# Test that user defined types used in views can be renamed without corrupting the view.
subtest view_user_defined_types_renames

statement ok
CREATE TYPE view_typ AS ENUM('a', 'b')

statement ok
CREATE VIEW v10 AS SELECT 'a'::view_typ

statement ok
ALTER TYPE view_typ RENAME TO view_typ_new

query TT
SHOW CREATE VIEW v10
----
v10  CREATE VIEW public.v10 (
       view_typ
     ) AS SELECT 'a':::db2.public.view_typ_new

query T
SELECT * FROM v10
----
a

statement ok
CREATE VIEW v11 AS (SELECT 'a'::view_typ_new < 'a'::view_typ_new AS k)

statement ok
ALTER TYPE view_typ_new RENAME TO view_typ

query TT
SHOW CREATE VIEW v11
----
v11  CREATE VIEW public.v11 (
       k
     ) AS (SELECT 'a':::db2.public.view_typ < 'a':::db2.public.view_typ AS k)

query B
SELECT * FROM v11
----
false

statement ok
CREATE VIEW v12 AS (SELECT k FROM (SELECT 'a'::view_typ AS k))

statement ok
ALTER TYPE view_typ RENAME TO view_type_new

query TT
SHOW CREATE VIEW v12
----
v12  CREATE VIEW public.v12 (
       k
     ) AS (SELECT k FROM (SELECT 'a':::db2.public.view_type_new AS k))

query T
SELECT * FROM v12
----
a

statement ok
CREATE VIEW v13 AS (SELECT 'a'::view_type_new AS k UNION SELECT 'b'::view_type_new)

statement ok
ALTER TYPE view_type_new RENAME TO view_type

query TT
SHOW CREATE VIEW v13
----
v13  CREATE VIEW public.v13 (
       k
     ) AS (SELECT 'a':::db2.public.view_type AS k UNION SELECT 'b':::db2.public.view_type)

query T
SELECT * FROM v13 ORDER BY k
----
a
b

statement ok
CREATE VIEW v14 AS (SELECT ARRAY['a'::view_type, 'b'::view_type])

statement ok
ALTER TYPE view_type RENAME TO view_type_new

query TT
SHOW CREATE VIEW v14
----
v14  CREATE VIEW public.v14 (
       "array"
     ) AS (
         SELECT
           ARRAY[
             'a':::db2.public.view_type_new,
             'b':::db2.public.view_type_new
           ]::db2.public.view_type_new[]
       )

query T
SELECT * FROM v14
----
{a,b}

statement ok
CREATE VIEW v15 AS (SELECT ('{a, b}'::view_type_new[])[2] AS view_type_new)

statement ok
ALTER TYPE view_type_new RENAME TO view_type

query TT
SHOW CREATE VIEW v15
----
v15  CREATE VIEW public.v15 (
       view_type_new
     ) AS (
         SELECT
           ARRAY['a':::db2.public.view_type, 'b':::db2.public.view_type][2:::INT8] AS view_type_new
       )

query T
SELECT * FROM v15
----
b

subtest view-cascade-nesting

statement ok
CREATE TABLE t1nest (id INT PRIMARY KEY, name varchar(2$t_id))

statement ok
CREATE VIEW v1nest AS (SELECT name FROM t1nest)

statement ok
CREATE VIEW v2nest AS (SELECT name AS n1, name AS n2 FROM v1nest)

statement ok
CREATE VIEW v3nest AS (SELECT name, n1 FROM v1nest, v2nest);

statement ok
DROP table t1nest CASCADE

# Validate the objects being dropped
query IT
SELECT "reportingID",
       (info::JSONB - 'Timestamp' - 'DescriptorID')
       || (
			SELECT json_build_object(
					'CascadeDroppedViews',
					json_agg(value ORDER BY value)
			       )
			  FROM ROWS FROM (
					json_array_elements((info::JSONB)->'CascadeDroppedViews')
			       )
		)
  FROM system.eventlog
 WHERE "eventType" IN ('drop_view', 'drop_table')
 ORDER BY "timestamp" DESC
 LIMIT 1;
----
1  {"CascadeDroppedViews": ["db2.public.v1nest", "db2.public.v2nest", "db2.public.v3nest"], "EventType": "drop_table", "Statement": "DROP TABLE db2.public.t1nest CASCADE", "TableName": "db2.public.t1nest", "Tag": "DROP TABLE", "User": "root"}


statement ok
CREATE TABLE test_t1(a int);

statement ok
CREATE MATERIALIZED VIEW li AS (SELECT 1 AS test_t1);

query TT
SHOW CREATE VIEW li
----
li  CREATE MATERIALIZED VIEW public.li (
      test_t1,
      rowid
    ) AS (SELECT 1 AS test_t1)

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

statement ok
INSERT INTO test_view VALUES (1, 99), (2, 98), (3, 97)

statement ok
CREATE MATERIALIZED VIEW mv1 AS SELECT a, b FROM test_view

query II colnames,rowsort
SELECT * FROM mv1
----
a b
1 99
2 98
3 97

statement ok
CREATE MATERIALIZED VIEW mv2 AS SELECT a, b FROM test_view WHERE b > 98 WITH NO DATA

query error  pq: materialized view "mv2" has not been populated\nHINT: use the REFRESH MATERIALIZED VIEW command.
SELECT * FROM mv2

statement ok
REFRESH MATERIALIZED VIEW mv2

query II colnames,rowsort
SELECT * from mv2
----
a b
1 99

statement ok
DROP MATERIALIZED VIEW mv1

statement ok
DROP MATERIALIZED VIEW mv2

statement ok
CREATE VIEW view_with_null AS SELECT 1 AS a, null AS c

query TTBTTTB
SHOW COLUMNS FROM view_with_null
----
a  INT8    true  NULL  ·  {}  false
c  STRING  true  NULL  ·  {}  false


statement ok
CREATE MATERIALIZED VIEW materialized_view_with_null AS SELECT a, NULL AS b, b AS c FROM test_view

query TTBTTTB
SHOW COLUMNS FROM materialized_view_with_null
----
a      INT8    true   NULL            ·  {materialized_view_with_null_pkey}  false
b      STRING  true   NULL            ·  {materialized_view_with_null_pkey}  false
c      INT8    true   NULL            ·  {materialized_view_with_null_pkey}  false
rowid  INT8    false  unique_rowid()  ·  {materialized_view_with_null_pkey}  true

subtest seq_qualified_name

statement ok
CREATE SCHEMA sc_seq_qualified_name;
CREATE SEQUENCE sc_seq_qualified_name.sq;

statement error pq: relation "sc_seq_qualified_name.sq" does not exist
CREATE VIEW v_seq_rewrite_quoted AS SELECT nextval('"sc_seq_qualified_name.sq"');

statement ok
CREATE VIEW v_seq_rewrite AS SELECT nextval('sc_seq_qualified_name.sq');

query I
SELECT * FROM v_seq_rewrite
----
1

statement ok
CREATE VIEW v_seq_rewrite_quoted AS SELECT nextval('"sc_seq_qualified_name"."sq"');

query I
SELECT * FROM v_seq_rewrite_quoted
----
2

subtest rename_schema

statement ok
CREATE DATABASE rename_sc1;
SET DATABASE = rename_sc1;

statement ok
CREATE SCHEMA sc1;
CREATE SCHEMA sc2;
CREATE TYPE sc1.workday AS ENUM ('Mon');
CREATE TABLE sc1.tbl(a INT PRIMARY KEY);
CREATE SEQUENCE sc1.sq;

statement ok
CREATE VIEW sc1.v_tbl AS SELECT a FROM sc1.tbl;
CREATE VIEW sc1.v_type AS SELECT 'Mon'::sc1.workday;
CREATE VIEW sc1.v_seq AS SELECT nextval('sc1.sq');
CREATE VIEW sc2.v_tbl AS SELECT a FROM sc1.tbl;
CREATE VIEW sc2.v_type AS SELECT 'Mon'::sc1.workday;
CREATE VIEW sc2.v_seq AS SELECT nextval('sc1.sq');

query T
SELECT * FROM sc1.v_type
----
Mon

query I
SELECT * FROM sc1.v_seq
----
1

query T
SELECT * FROM sc2.v_type
----
Mon

query I
SELECT * FROM sc2.v_seq
----
2

statement error pq: cannot rename schema because relation "rename_sc1.sc1.v_tbl" depends on relation "rename_sc1.sc1.tbl"
ALTER SCHEMA sc1 RENAME TO sc1_new

statement ok
DROP VIEW sc1.v_tbl

statement error pq: cannot rename schema because relation "rename_sc1.sc2.v_tbl" depends on relation "rename_sc1.sc1.tbl"
ALTER SCHEMA sc1 RENAME TO sc1_new

statement ok
DROP VIEW sc2.v_tbl

statement ok
ALTER SCHEMA sc1 RENAME TO sc1_new

# Make sure that db renaming does not affect types and sequences in UDF.
query T
SELECT * FROM sc1_new.v_type
----
Mon

query I
SELECT * FROM sc1_new.v_seq
----
3

query T
SELECT * FROM sc2.v_type
----
Mon

query I
SELECT * FROM sc2.v_seq
----
4

statement ok
SET DATABASE = test

subtest select_from_seq_rename

statement ok
CREATE DATABASE tdb_seq_select;
SET DATABASE = tdb_seq_select;

statement ok
CREATE SCHEMA sc;
CREATE SEQUENCE sc.sq;
CREATE VIEW v AS SELECT last_value FROM sc.sq;

query I
SELECT * FROM v;
----
0

statement ok
ALTER SEQUENCE sc.sq RENAME TO sq_new;

statement error pq: relation "tdb_seq_select.sc.sq" does not exist
SELECT * FROM v;

statement ok
ALTER SEQUENCE sc.sq_new RENAME TO sq;
SELECT * FROM v;

statement ok
ALTER SCHEMA sc RENAME TO sc_new;

statement error pq: unknown schema "sc"
SELECT * FROM v;

statement ok
ALTER SCHEMA sc_new RENAME TO sc;
SELECT * FROM v;

statement ok
ALTER DATABASE tdb_seq_select RENAME TO tdb_seq_select_new;
SET DATABASE = tdb_seq_select_new;

statement error pq: database "tdb_seq_select" does not exist
SELECT * FROM v;

statement ok
ALTER DATABASE tdb_seq_select_new RENAME TO tdb_seq_select;
SET DATABASE = tdb_seq_select;
SELECT * FROM v;

statement ok
SET DATABASE = test;