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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
# name: test/sql/binder/test_function_chaining_alias.test
# description: Test referencing an alias or a function chaining alias
# group: [binder]
statement ok
PRAGMA enable_verification
query II
SELECT 'test' || ' more testing' AS added, added.substr(5) AS my_substr
----
test more testing more testing
statement ok
CREATE TABLE varchars(v VARCHAR);
statement ok
INSERT INTO varchars VALUES ('>>%Test<<'), ('%FUNCTION%'), ('Chaining')
query I
SELECT v.lower() FROM varchars
----
>>%test<<
%function%
chaining
# Use only_alphabet before it is defined.
statement error
SELECT
v.trim('><') AS trim_inequality,
only_alphabet.lower() AS lower,
trim_inequality.replace('%', '') AS only_alphabet,
FROM varchars
----
<REGEX>:Binder Error.*column cannot be referenced before it is defined.*
query III
SELECT
v.trim('><') AS trim_inequality,
trim_inequality.replace('%', '') AS only_alphabet,
only_alphabet.lower() AS lower
FROM varchars
----
%Test Test test
%FUNCTION% FUNCTION function
Chaining Chaining chaining
# Test a column with a table name.
query III
SELECT
varchars.v.trim('><') AS trim_inequality,
trim_inequality.replace('%', '') AS only_alphabet,
only_alphabet.lower() AS lower
FROM varchars
----
%Test Test test
%FUNCTION% FUNCTION function
Chaining Chaining chaining
statement ok
DELETE FROM varchars
statement ok
INSERT INTO varchars VALUES ('Test Function Chaining Alias');
query III
SELECT
v.split(' ')::VARCHAR strings,
strings.lower() lower,
lower.upper() upper
FROM varchars
----
[Test, Function, Chaining, Alias] [test, function, chaining, alias] [TEST, FUNCTION, CHAINING, ALIAS]
query IIII
SELECT
v.split(' ') strings,
strings.apply(lambda x: x.lower()).filter(lambda x: x[1] == 't') lower,
strings.apply(lambda x: x.upper()).filter(lambda x: x[1] == 'T') upper,
lower + upper AS mix_case_srings
FROM varchars
----
[Test, Function, Chaining, Alias] [test] [TEST] [test, TEST]
# Test prepared statements.
statement ok
PREPARE v1 AS
SELECT
(?.split(' ')::VARCHAR).lower() lstrings,
(?.split(' ')::VARCHAR).upper() ustrings,
list_concat(lstrings::VARCHAR[], ustrings::VARCHAR[]) AS mix_case_srings
query III
EXECUTE v1('Hello World', 'test function chaining')
----
[hello, world] [TEST, FUNCTION, CHAINING] [hello, world, TEST, FUNCTION, CHAINING]
statement ok
INSERT INTO varchars VALUES ('Another longggggg String');
# Use an alias in a WHERE clause.
query IIII
SELECT
v.split(' ') strings,
strings.apply(lambda x: x.lower()).filter(lambda x: x[1] == 't' OR x[1] == 'a') lower,
strings.apply(lambda x: x.upper()).filter(lambda x: x[1] == 'T' OR x[1] == 'A') upper,
lower + upper AS mix_case_srings
FROM varchars
WHERE mix_case_srings[1] = 'test'
----
[Test, Function, Chaining, Alias] [test, alias] [TEST, ALIAS] [test, alias, TEST, ALIAS]
query IIII
SELECT
v.split(' ') strings,
strings.apply(lambda x: x.lower()).filter(lambda x: x[1] == 't' OR x[1] == 'a') lower,
strings.apply(lambda x: x.upper()).filter(lambda x: x[1] == 'T' OR x[1] == 'A') upper,
lower + upper AS mix_case_srings
FROM varchars
WHERE mix_case_srings[1] = 'another'
----
[Another, longggggg, String] [another] [ANOTHER] [another, ANOTHER]
# CTE with function chaining alias.
query II
WITH test AS (
SELECT 'woot' AS my_column
)
FROM test
SELECT
my_column.substr(2) AS partial_woot,
partial_woot.substr(2) AS more_partially_woot
WHERE
more_partially_woot = 'ot';
----
oot ot
|
