mz-resolve-object-name.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.

# We want stability of GlobalIDs
reset-server

statement ok
CREATE DATABASE d;

statement ok
CREATE SCHEMA s;

statement ok
CREATE SCHEMA d.s;

statement ok
CREATE TABLE t (a int);

statement ok
CREATE TABLE s.t (a int);

statement ok
CREATE TABLE d.s.t (a int);

# This goofy structure is because we can't just return the record from mz_resolve_object_name.
query T
SELECT concat_ws(' ', o.id, o.schema_id, o.name, o.type, o.owner_id, o.privileges::text) FROM (
    SELECT * FROM mz_internal.mz_resolve_object_name('t')
) AS o;
----
u1 u3 t table u1 {u1=arwd/u1}

query T
SELECT id FROM mz_internal.mz_resolve_object_name('t');
----
u1

query T
SELECT mz_internal.mz_global_id_to_name('u1');
----
materialize.public.t

# Roundtrips
query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('materialize.public.t');
----
materialize.public.t

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('t');
----
materialize.public.t

query T
SELECT id
FROM mz_internal.mz_resolve_object_name(
    mz_internal.mz_global_id_to_name('u1')
);
----
u1

query T
SELECT id FROM mz_internal.mz_resolve_object_name('s.t');
----
u2

query T
SELECT mz_internal.mz_global_id_to_name('u2');
----
materialize.s.t

query T
SELECT id FROM mz_internal.mz_resolve_object_name('d.s.t');
----
u3

query T
SELECT mz_internal.mz_global_id_to_name('u3');
----
d.s.t

query error db error: ERROR: improper relation name \(too many dotted names\): d\.s\.t\.c
SELECT mz_internal.mz_resolve_object_name('d.s.t.c');

query error db error: ERROR: improper relation name \(too many dotted names\): d\.s\.t\.c\.b
SELECT mz_internal.mz_resolve_object_name('d.s.t.c.b');

# Respects search path

query T
SHOW search_path;
----
public

statement ok
SET search_path = s

# Resolves to s.t
query T
SELECT id FROM mz_internal.mz_resolve_object_name('t');
----
u2

query T
SELECT id FROM mz_internal.mz_resolve_object_name('public.t');
----
u1

# Include public and s in search path, resolves public first.
statement ok
SET search_path = public, s

query T
SELECT id FROM mz_internal.mz_resolve_object_name('t');
----
u1

# Include public and s in search path, resolves s first.
statement ok
SET search_path = s, public

query T
SELECT id FROM mz_internal.mz_resolve_object_name('t');
----
u2

statement ok
SET search_path = public

# Check ambient schemas

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('int4');
----
pg_catalog.int4

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('pg_catalog.int4');
----
pg_catalog.int4

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('materialize.pg_catalog.int4');
----
pg_catalog.int4

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('mz_sources');
----
mz_catalog.mz_sources

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('mz_catalog.mz_sources');
----
mz_catalog.mz_sources

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('materialize.mz_catalog.mz_sources');
----
mz_catalog.mz_sources

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('d.mz_catalog.mz_sources');
----
mz_catalog.mz_sources

# Values that do not exist
query T
SELECT id FROM mz_internal.mz_resolve_object_name('dne')
----

query T
SELECT id FROM mz_internal.mz_resolve_object_name('dne.t')
----

query T
SELECT id FROM mz_internal.mz_resolve_object_name('public.dne')
----

query T
SELECT id FROM mz_internal.mz_resolve_object_name('dne.public.t')
----

# Ensure database qualification for catalog items behaves as expected.
query T
SELECT id FROM mz_internal.mz_resolve_object_name('dne.mz_catalog.mz_views')
----

query error global ID dne does not exist
SELECT mz_internal.mz_global_id_to_name('dne');

# Null input
query T
SELECT id FROM mz_internal.mz_resolve_object_name(null::text)
----

query T
SELECT mz_internal.mz_global_id_to_name(null)
----
NULL

# Check many objects with same GlobalId

query T
SELECT concat_ws(' ', id LIKE 's%', oid) FROM mz_internal.mz_resolve_object_name('abs');
----
t 1394
t 1395
t 1396
t 1397
t 1398
t 1705

query T
SELECT DISTINCT mz_internal.mz_global_id_to_name(id) AS name
FROM mz_internal.mz_resolve_object_name('abs');
----
pg_catalog.abs

statement ok
CREATE TABLE public.abs (a int);

# Ensure that competing names in items lower in the search path do not creep in.

query T
SELECT concat_ws(' ', id LIKE 's%', oid) FROM mz_internal.mz_resolve_object_name('abs');
----
t 1394
t 1395
t 1396
t 1397
t 1398
t 1705

statement ok
CREATE TABLE "#" ();

query T
SELECT mz_internal.mz_global_id_to_name(id)
FROM mz_internal.mz_resolve_object_name('"#"');
----
materialize.public."#"