Materialize/test/sqllogictest/cockroach/select_search_path.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/select_search_path
#
# 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

# Test that pg_catalog tables are accessible without qualifying table/view
# names.

query T
SHOW TABLES
----

# There's no table with default values in the default test database.
query I
SELECT count(DISTINCT(1)) FROM pg_attrdef
----
0

query I
SELECT count(DISTINCT(1)) FROM pg_attribute
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_class
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_namespace
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_tables
----
1


statement ok
CREATE DATABASE t1

statement ok
CREATE TABLE t1.numbers (n INTEGER)

statement ok
CREATE DATABASE t2

statement ok
CREATE TABLE t2.words (w TEXT)

# Test that we can query with unqualified table names from t1 and pg_catalog
# (but not t2) when t1 is the session database.

statement ok
SET DATABASE = t1

query I
SELECT count(*) FROM numbers
----
0

query error pq: relation "words" does not exist
SELECT count(*) FROM words

# There's no table with default values in t1.
query I
SELECT count(DISTINCT(1)) FROM pg_attrdef
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_attribute
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_class
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_namespace
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_tables
----
1

# Test that we can query with unqualified table names from t2 and pg_catalog
# (but not t1) when t2 is the session database.

statement ok
SET DATABASE = t2

query error pq: relation "numbers" does not exist
SELECT count(*) FROM numbers

query I
SELECT count(*) FROM words
----
0

# There's no table with default values in t2.
query I
SELECT count(DISTINCT(1)) FROM pg_attrdef
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_attribute
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_class
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_namespace
----
1

query I
SELECT count(DISTINCT(1)) FROM pg_tables
----
1

# Test that current_schema reports the first valid entry in search_path, or
# NULL if there is no such entry.

statement ok
SET search_path = nonexistent, public

query T
SELECT current_schema
----
public

statement ok
SET search_path = nonexistent

query T
SELECT current_schema
----
NULL

# Test that current_schemas only reports the valid entries in
# search_path.

statement ok
SET search_path = nonexistent, public

query T
SELECT current_schemas(false)
----
{public}

# Test that object creation targets the first valid entry in
# search_path, not just the first entry.

statement ok
CREATE TABLE sometable(x INT); SELECT * FROM public.sometable