Materialize/test/sqllogictest/cockroach/namespace.slt

# 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/namespace
#
# 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 a(a INT)

statement ok
CREATE DATABASE public; CREATE TABLE public.public.t(a INT)

# "public" with the current database designates the public schema
query T
SHOW TABLES FROM public
----
a

# To access all tables in database "public", one must specify
# its public schema explicitly.
query T
SHOW TABLES FROM public.public
----
t

# Of course one can also list the tables in "public" by making it the
# current database.
statement ok
SET database = public

query T
SHOW TABLES
----
t

statement ok
SET database = test; DROP DATABASE public

# Unqualified pg_type resolves from pg_catalog.
query T
SELECT typname FROM pg_type WHERE typname = 'date'
----
date

# Override table and check name resolves properly.
statement ok
SET search_path=public,pg_catalog

statement ok
CREATE TABLE pg_type(x INT); INSERT INTO pg_type VALUES(42)

query I
SELECT x FROM pg_type
----
42

# Leave database, check name resolves to default.
# The expected error can only occur on the virtual pg_type, not the physical one.
query error cannot access virtual schema in anonymous database
SET database = ''; SELECT * FROM pg_type

# Go to different database, check name still resolves to default.
query T
CREATE DATABASE foo; SET database = foo; SELECT typname FROM pg_type WHERE typname = 'date'
----
date

# Verify that pg_catalog at the beginning of the search path takes precedence.
query T
SET database = test; SET search_path = pg_catalog,public; SELECT typname FROM pg_type WHERE typname = 'date'
----
date

# Now set the search path to the testdb, placing pg_catalog explicitly
# at the end.
query I
SET search_path = public,pg_catalog; SELECT x FROM pg_type
----
42

statement ok
DROP TABLE pg_type; RESET search_path; SET database = test

# Unqualified index name resolution.
statement ok
ALTER INDEX "primary" RENAME TO a_pk

# Schema-qualified index name resolution.
statement ok
ALTER INDEX public.a_pk RENAME TO a_pk2

# DB-qualified index name resolution (CRDB 1.x compat).
statement ok
ALTER INDEX test.a_pk2 RENAME TO a_pk3

statement ok
CREATE DATABASE public; CREATE TABLE public.public.t(a INT)

# We can't see the DB "public" with DB-qualified index name resolution.
statement error index "primary" does not exist
ALTER INDEX public."primary" RENAME TO t_pk

# But we can see it with sufficient qualification.
statement ok
ALTER INDEX public.public."primary" RENAME TO t_pk

# If the search path is invalid, we get a special error.
statement ok
SET search_path = invalid

statement error schema or database was not found while searching index: "a_pk3"
ALTER INDEX a_pk3 RENAME TO a_pk4

# But qualification resolves the problem.
statement ok
ALTER INDEX public.a_pk3 RENAME TO a_pk4