1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
# name: test/sql/binder/alias_qualification_select_projection.test
# description: Test alias.name resolution in SELECT projection
# group: [binder]
query II
SELECT
a + 1 AS x,
alias.x + 2 AS y
FROM (VALUES (42)) t(a);
----
43 45
# chained alias references within SELECT projection
query IIII
SELECT
a AS a,
a + 1 AS x,
alias.x + 2 AS y,
alias.y * 2 AS z
FROM (VALUES (10)) t(a);
----
10 11 13 26
# multiple references to the same alias
query III
SELECT
a + 1 AS x,
alias.x AS x2,
alias.x + alias.x AS xsum
FROM (VALUES (5)) t(a);
----
6 6 12
# quoted alias names with mixed case
query II
SELECT
a + 1 AS "MiXeD",
alias."MiXeD" + 1 AS y
FROM (VALUES (1)) t(a);
----
2 3
# alias usage with window function in earlier alias
query III
SELECT
a,
row_number() OVER (ORDER BY a) AS rn,
alias.rn + 1 AS rn2
FROM (VALUES (3),(1),(2)) t(a)
ORDER BY a;
----
1 1 2
2 2 3
3 3 4
# alias with DISTINCT and deterministic ordering
query II
SELECT DISTINCT
a AS x,
alias.x + 0 AS y
FROM (VALUES (1),(1),(2)) t(a)
ORDER BY x;
----
1 1
2 2
# alias inside CASE expression across multiple rows
query IT
SELECT
a AS x,
CASE WHEN alias.x % 2 = 0 THEN 'even' ELSE 'odd' END AS parity
FROM (VALUES (1),(2),(3)) t(a)
ORDER BY a;
----
1 odd
2 even
3 odd
# string alias usage
query TT
SELECT
'hi' AS s,
alias.s || '!' AS ex
FROM (VALUES (1)) t(a);
----
hi hi!
# making sure that SELECT on a table under the name of `alias` works correctly
statement ok
CREATE TABLE alias (col1 INT, col2 INT);
statement ok
INSERT INTO alias VALUES (11, 22);
query II
SELECT alias.col1, alias.col2 FROM alias;
----
11 22
# even if we have an alias reference in the projection, it should not conflict with the table named `alias`,
# but the table columns should be prioritised
query II
SELECT col1 * 10 AS col2, alias.col2 AS projected_col2 FROM alias;
----
110 22
# mmethod call on alias reference
query II
SELECT 'AbC' AS s, alias.s.lower();
----
AbC abc
|
