modules/sql-engine/src/integrationTest/sql/basic_queries/rename_columns_in_from.test

# name: sql/basic_queries/rename_columns_in_from.test
# description: SQL feature E051-9 (Basic query specification. Rename columns in the FROM clause)
# feature: E051-09
# group: [basic_queries]

statement ok
CREATE TABLE tab ( A INT PRIMARY KEY, B INT )

statement ok
INSERT INTO tab VALUES(1,13), (2,12), (3,11)

# use alias with ALL
query II
SELECT ALL alias . X , Y FROM tab AS alias (X, Y) order by x
----
1	13
2	12
3	11

# use alias with DISTINCT
query II
SELECT DISTINCT alias.Y, X FROM tab AS alias (X, Y) order by y
----
11	3
12	2
13	1

# use alias for second field
query II
SELECT alias.X, Y FROM tab AS alias (X, Y) order by alias.x
----
1	13
2	12
3	11

# use alias for second field
query II
SELECT X, alias.Y FROM tab AS alias (X, Y) order by x
----
1	13
2	12
3	11

# inversion of column name by aliases
query II
SELECT alias.a, b FROM tab AS alias (b, a) order by b
----
13	1
12	2
11	3


# no alias in projection
query II
SELECT X, Y FROM tab AS alias (X, Y) order by x
----
1	13
2	12
3	11

# no AS keyword
query II
SELECT X, Y FROM tab alias (X, Y) order by x -- no AS
----
1	13
2	12
3	11

# duplicate alias name
statement error
SELECT alias.b, b FROM tab AS alias (b, b)

# number of aliases is less than number of columns
statement error
SELECT alias.X FROM tab AS alias (X)

# number of aliases is bigger than number of columns
statement error
SELECT alias.X FROM tab AS alias (X, Y, Z)