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

statement ok
CREATE TABLE t1 (a int)

statement ok
CREATE TABLE t2 (a int)

statement ok
CREATE TABLE t3 (a int)

# These queries work in MySQL but not PostgreSQL. We used to support them,
# back before we'd fully committed to PostgreSQL compatibility.
query error column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
SELECT t1.a FROM t1 JOIN t2 ON t1.a = t2.a GROUP BY t2.a

query error column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
SELECT t1.a FROM t1 JOIN t2 ON t1.a = t2.a LEFT JOIN t3 ON t2.a = t3.a GROUP BY t2.a

query error column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
SELECT t1.a FROM t1 JOIN (t2 JOIN t3 ON t2.a = t3.a) ON t1.a = t2.a GROUP BY t3.a

# Ensure that reflexive equality expressions do not cause the specified column
# to become unnameable. See database-issues#1778.
query I
SELECT t1.a FROM t1 JOIN t2 ON t1.a = t1.a GROUP BY t1.a
----

# This works in PostgreSQL.
query I
SELECT t1.a FROM t1 NATURAL JOIN t2
----

# This works in PostgreSQL too.
query I
SELECT t2.a FROM t1 NATURAL JOIN t2
----

# Regression tests for database-issues#4887.
query I
SELECT t.a FROM (t1 NATURAL JOIN t2) t
----

query I
SELECT t.a FROM (t1 JOIN t2 USING (a)) t
----

# Test sources with unnamed columns.

statement ok
CREATE VIEW v1 AS SELECT 1

query I
SELECT * FROM v1
----
1

query I
SELECT v1."?column?" FROM v1
----
1

query I
SELECT v1.* FROM v1
----
1

# Test wildcards in SELECT list.

query I
SELECT v1.* FROM v1
----
1

query I
SELECT v.* FROM v1 v
----
1

query error no table named 'v1' in scope
SELECT v1.* FROM v1 v

query error no table named 'totalgarbage' in scope
SELECT totalgarbage.* FROM v1

query error no table named 'totalgarbage' in scope
SELECT totalgarbage.*

query error SELECT \* with no tables specified is not valid
SELECT *

query error column reference "k" is ambiguous
SELECT k FROM (SELECT 1 AS k, 2 AS k)

# Wildcards on a zero-arity table are ok, though.

statement ok
CREATE TABLE nullary ()

query
SELECT * FROM nullary
----

# Check that column names propagate through several layers of subqueries.
query T colnames
SELECT (SELECT * FROM (SELECT 1 AS a) _)
----
a
1

# Check that the EXISTS operator names its output column as such.
query T colnames
SELECT EXISTS (SELECT 1)
----
exists
true

# Check that duplicated columns with different names retain their different
# names.
query TT colnames
SELECT column1, column1 as column2 FROM (VALUES (1))
----
column1  column2
1  1

# Ensure that table references only expand to columns in the innermost scope.
# Past versions of Materialize had a bug that would cause the following query
# to produce (1,2,3,4).
query T
SELECT (SELECT v FROM (VALUES (1, 2)) v) FROM (VALUES (3, 4)) v
----
(1,2)

query error column reference "f1" is ambiguous
SELECT * FROM (SELECT 1 f1) s1 CROSS JOIN ((SELECT 2 f1) s2 CROSS JOIN LATERAL (SELECT f1) s3)

query error column reference "a" is ambiguous
SELECT * FROM (SELECT 1 a) t1 CROSS JOIN ((SELECT 1 a) t1 CROSS JOIN LATERAL (SELECT a) t2) t3;

query error column reference "a" is ambiguous
WITH cte1 AS (SELECT 1 AS a, 2 AS a) SELECT * FROM cte1 WHERE cte1.a = 1

query error table reference "t1" is ambiguous
SELECT * FROM (SELECT 1 a) t1 CROSS JOIN ((SELECT 1 a) t1 CROSS JOIN LATERAL (SELECT t1.a) t2) t3;

query error table reference "t1" is ambiguous
SELECT * FROM (SELECT 1 a) t1 CROSS JOIN ((SELECT 1 a) t1 CROSS JOIN LATERAL (SELECT t1) t2) t3;

# Test column name inference corner cases.

query T colnames
SELECT 1::int
----
int4
1

query T colnames
SELECT 1::text::int
----
int4
1

query T colnames
SELECT CASE WHEN TRUE THEN 1 END
----
case
1

query T colnames
SELECT CASE WHEN TRUE THEN 1 ELSE column1 END FROM (VALUES (1))
----
column1
1

query T colnames
SELECT CASE WHEN TRUE THEN 1 ELSE 1::int END
----
case
1

statement ok
CREATE TYPE scoping_composite AS (i int);

query T colnames
SELECT ROW(1)::scoping_composite
----
row
(1)