select_table_alias.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_table_alias
#
# 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.

mode cockroach

# Tests for SELECT with table aliasing.

statement ok
CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT)

statement ok
INSERT INTO abc VALUES (1, 2, 3), (4, 5, 6)


# Verify output column naming with *.

query III colnames,rowsort
SELECT * FROM abc
----
a b c
1 2 3
4 5 6

query III colnames,rowsort
SELECT * FROM abc AS foo
----
a b c
1 2 3
4 5 6

query III colnames,rowsort
SELECT * FROM abc AS foo (foo1)
----
foo1 b c
1 2 3
4 5 6

query III colnames,rowsort
SELECT * FROM abc AS foo (foo1, foo2)
----
foo1 foo2 c
1 2 3
4 5 6

query III colnames,rowsort
SELECT * FROM abc AS foo (foo1, foo2, foo3)
----
foo1 foo2 foo3
1 2 3
4 5 6


# Verify qualified name resolution.

query IIII colnames,rowsort
SELECT foo1, foo.foo1, b, foo.c FROM abc AS foo (foo1)
----
foo1 foo1 b c
1 1 2 3
4 4 5 6

query III colnames,rowsort
SELECT * FROM abc AS foo (foo1, foo2) WHERE foo.foo1 = 1
----
foo1 foo2 c
1 2 3

query III colnames,rowsort
SELECT * FROM abc AS foo (foo1, foo2) WHERE foo.foo2 = 2
----
foo1 foo2 c
1 2 3

query III colnames,rowsort
SELECT * FROM abc AS foo (foo1, foo2) WHERE foo.c = 6
----
foo1 foo2 c
4 5 6


# Verify we can't resolve columns using overridden table or colum names.

query error column "abc.foo1" does not exist
SELECT abc.foo1 FROM abc AS foo (foo1)

query error column "abc.b" does not exist
SELECT abc.b FROM abc AS foo (foo1)

query error column "foo.a" does not exist
SELECT foo.a FROM abc AS foo (foo1)


# Verify error for too many column aliases.

query error pgcode 42P10 foo has 3 columns available but 4 columns specified
SELECT * FROM abc AS foo (foo1, foo2, foo3, foo4)


# Verify that implicit columns don't interfere with aliasing.

statement ok
CREATE TABLE ab (a INT, b INT)

statement ok
INSERT INTO ab VALUES (1, 2), (1, 3), (2, 5)

query II colnames,rowsort
SELECT * FROM ab AS foo (foo1, foo2)
----
foo1 foo2
1 2
1 3
2 5

# NOTE(benesch): rowid is a CockroachDB-ism that we are unlikely to support.
#
# statement ok
# SELECT rowid, foo.rowid FROM ab AS foo (foo1, foo2)
#
# query error no data source matches prefix: ab
# SELECT ab.rowid FROM ab AS foo (foo1)

query error foo has 2 columns available but 3 columns specified
SELECT * FROM ab AS foo (foo1, foo2, foo3)

# TODO(benesch): support scalar functions in table position.
#
# query T colnames
# SELECT * FROM length('abc') AS x
# ----
# x
# 3
#
# query T colnames
# TABLE ROWS FROM length('abc') AS x
# ----
# x
# 3