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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
# name: test/sql/subquery/lateral/test_lateral_join.test
# description: Test lateral join
# group: [lateral]
statement ok
SET default_null_order='nulls_first';
statement ok
PRAGMA enable_verification
query I
select (select MIN(val) from unnest((select a)) t(val)) from (select ARRAY[1, 2, 3, NULL]) t(a);
----
1
query I
select (select MIN(val) from unnest((select (select a))) t(val)) from (select ARRAY[1, 2, 3, NULL]) t(a);
----
1
query II rowsort
select * from (select array[1, 2, 3] a), unnest((select (select (select a))))
----
[1, 2, 3] 1
[1, 2, 3] 2
[1, 2, 3] 3
query I
select (select MIN(val) from unnest(a) t(val)) from (select ARRAY[1, 2, 3, NULL]) t(a);
----
1
# simplest lateral joins
query II
select * from (select 42) t(a), (select t.a + 1);
----
42 43
query II
select * from (select 42) t(a) cross join lateral (select t.a + 1);
----
42 43
# more rows
query II
select * from (select 42 union all select 84) t(a), (select t.a + 1) ORDER BY ALL;
----
42 43
84 85
query I
select (select MIN(val) from unnest(a) t(val)) from (select ARRAY[1, 2, 3, NULL]) t(a);
----
1
# unnest
query II
select * from (select [42, 43, 44]) t(a), (select unnest(t.a)) order by all;
----
[42, 43, 44] 42
[42, 43, 44] 43
[42, 43, 44] 44
query II
select * from (select [42, 43, 44]) t(a), (select unnest(t.a)) t2(b) where b=43;
----
[42, 43, 44] 43
query II
select * from (select [42, 43, 44] union all select [45, NULL, 46]) t(a), (select unnest(t.a)) t2(b) order by all;
----
[42, 43, 44] 42
[42, 43, 44] 43
[42, 43, 44] 44
[45, NULL, 46] NULL
[45, NULL, 46] 45
[45, NULL, 46] 46
query I
select sum(b) from (select [42, 43, 44] union all select [45, NULL, 46]) t(a), (select unnest(t.a)) t2(b);
----
220
query II
select a, sum(b) from (select [42, 43, 44] union all select [45, NULL, 46]) t(a), (select unnest(t.a)) t2(b) group by a order by a;
----
[42, 43, 44] 129
[45, NULL, 46] 91
# join syntax
query II
select * from (select array[42, 43, 44]) t(a) join (select unnest(t.a)) t2(b) on (true) order by all;
----
[42, 43, 44] 42
[42, 43, 44] 43
[42, 43, 44] 44
# explicit LATERAL (Postgres syntax)
query II
select * from (select array[42, 43, 44]) t(a) join lateral (select unnest(t.a)) t2(b) on (true) order by all;
----
[42, 43, 44] 42
[42, 43, 44] 43
[42, 43, 44] 44
query II
select * from (select array[42, 43, 44]) t(a) join lateral (select unnest(t.a)) t2(b) on (a[1]=b);
----
[42, 43, 44] 42
query II
select * from (select array[42, 43, 44]) t(a) join lateral (select unnest(t.a)) t2(b) on (a[1]<b) order by all;
----
[42, 43, 44] 43
[42, 43, 44] 44
query II
select * from (select array[42, 43, 44]) t(a) join lateral (select unnest(t.a)) t2(b) on (false) order by all;
----
# postgres syntax
query II
select * from (select 42) t(a) join lateral (select t.a + 1) t2(b) on (true);
----
42 43
# we can use random expressions in join conditions of inner joins
query II
select * from (select 42) t(a) join lateral (select t.a + 1) t2(b) on (a+b>=80);
----
42 43
query II
select * from (select ARRAY[42, 43, 44]) t(a) join lateral (select * from unnest(t.a)) t2(b) on (true) ORDER BY b;
----
[42, 43, 44] 42
[42, 43, 44] 43
[42, 43, 44] 44
statement ok
CREATE TABLE students(id INTEGER, name VARCHAR, major VARCHAR, year INTEGER)
statement ok
CREATE TABLE exams(sid INTEGER, course VARCHAR, curriculum VARCHAR, grade INTEGER, year INTEGER)
statement ok
INSERT INTO students VALUES (1, 'Mark', 'CS', 2017)
statement ok
INSERT INTO students VALUES (2, 'Dirk', 'CS', 2017)
statement ok
INSERT INTO exams VALUES (1, 'Database Systems', 'CS', 10, 2015)
statement ok
INSERT INTO exams VALUES (1, 'Graphics', 'CS', 9, 2016)
statement ok
INSERT INTO exams VALUES (2, 'Database Systems', 'CS', 7, 2015)
statement ok
INSERT INTO exams VALUES (2, 'Graphics', 'CS', 7, 2016)
# lateral join with explicit LATERAL added
query II
SELECT name, total FROM students JOIN LATERAL (SELECT SUM(grade) AS total FROM exams WHERE exams.sid=students.id) grades ON true ORDER BY total DESC;
----
Mark 19
Dirk 14
# lateral join without explicit LATERAL
query II
SELECT name, total FROM students, (SELECT SUM(grade) AS total FROM exams WHERE exams.sid=students.id) grades ORDER BY total DESC;
----
Mark 19
Dirk 14
|
