Materialize/test/sqllogictest/id.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

# Start from a pristine server
reset-server

statement ok
CREATE TABLE x (a int)

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

query T
SELECT id FROM mz_catalog.mz_tables WHERE name = 'x'
----
u1

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

query I rowsort
SELECT a FROM [u1 AS materialize.public.y]
----
1
2
3

# Renaming the table to something different.

# Referring to it by its "true" name should not work.
statement error column "x.a" does not exist
SELECT x.a FROM [u1 AS materialize.public.y]

# Referring to it by its assigned name should work.
query I rowsort
SELECT y.a FROM [u1 AS materialize.public.y]
----
1
2
3

statement error invalid id
SELECT y.a FROM [u6 AS materialize.public.y]

statement error couldn't parse id
SELECT y.a FROM [xx AS materialize.public.y]

statement error invalid digit
SELECT y.a FROM [ux AS materialize.public.y]

statement ok
CREATE VIEW foo AS SELECT * FROM x

mode standard

# If the name in the catalog matches that which is specified in the view
# definition, we should output it as its (fully qualified) name.

query TT
SHOW CREATE VIEW foo
----
materialize.public.foo
CREATE VIEW materialize.public.foo AS SELECT * FROM materialize.public.x;

statement ok
DROP VIEW foo;

statement ok
CREATE VIEW foo AS SELECT * FROM [u1 AS materialize.public.x]

query TT
SHOW CREATE VIEW foo
----
materialize.public.foo
CREATE VIEW materialize.public.foo AS SELECT * FROM materialize.public.x;

# If the name *differs*, fall back to the id version.

statement ok
DROP VIEW foo;

statement ok
CREATE VIEW foo AS SELECT * FROM [u1 AS materialize.public.y]

query TT
SHOW CREATE VIEW foo
----
materialize.public.foo
CREATE VIEW materialize.public.foo AS SELECT * FROM [u1 AS materialize.public.y];