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
|
# name: test/sql/subquery/scalar/test_scalar_subquery.test
# description: Test subqueries
# group: [scalar]
statement ok
PRAGMA enable_verification
query I
SELECT 1+(SELECT 1)
----
2
query T
SELECT 1=(SELECT 1)
----
1
query T
SELECT 1<>(SELECT 1)
----
0
query T
SELECT 1=(SELECT NULL)
----
NULL
query T
SELECT NULL=(SELECT 1)
----
NULL
# scalar subquery
query I
SELECT (SELECT 42)
----
42
# nested subquery
query I
SELECT (SELECT (SELECT 42))
----
42
# test aliasing of subquery
query I
SELECT * FROM (SELECT 42) v1(a);
----
42
# not enough aliases: defaults to using names for missing columns
query II
SELECT * FROM (SELECT 42, 41 AS x) v1(a);
----
42 41
# too many aliases: fails
statement error
SELECT * FROM (SELECT 42, 41 AS x) v1(a, b, c);
----
statement ok
CREATE TABLE test (a INTEGER, b INTEGER);
statement ok
INSERT INTO test VALUES (11, 22)
statement ok
INSERT INTO test VALUES (12, 21)
statement ok
INSERT INTO test VALUES (13, 22)
# select single tuple only in scalar subquery
statement error
SELECT (SELECT a * 42 FROM test)
----
More than one row returned by a subquery used as an expression
statement ok
SET scalar_subquery_error_on_multiple_rows=false
query I
SELECT (SELECT a * 42 FROM test)
----
462
# operations on subquery
query I
SELECT a*(SELECT 42) FROM test
----
462
504
546
statement ok
CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER)
statement ok
INSERT INTO t1(e,c,b,d,a) VALUES(103,102,100,101,104)
statement ok
INSERT INTO t1(a,c,d,e,b) VALUES(107,106,108,109,105)
query R
SELECT c-(SELECT sum(c) FROM t1) FROM t1
----
-106.000000
-102.000000
query I
SELECT CASE WHEN c>(SELECT sum(c)/count(*) FROM t1) THEN a*2 ELSE b*10 END FROM t1
----
1000
214
# correlated subqueries
query IR
SELECT a, (SELECT SUM(b) FROM test tsub WHERE test.a=tsub.a) FROM test
----
11 22.000000
12 21.000000
13 22.000000
query II
SELECT a, (SELECT CASE WHEN test.a=11 THEN 22 ELSE NULL END) FROM test ORDER BY a
----
11 22
12 NULL
13 NULL
query II
SELECT a, (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub) FROM test ORDER BY a
----
11 22
12 NULL
13 NULL
query II
SELECT a, (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub LIMIT 1) FROM test ORDER BY a
----
11 22
12 NULL
13 NULL
query II
SELECT * from test where a=(SELECT MIN(a) FROM test t WHERE t.b=test.b) ORDER BY a
----
11 22
12 21
# exists / in / any subqueries
query II
SELECT * FROM test WHERE EXISTS (SELECT a FROM test ts WHERE ts.a = test.a AND b>21)
----
11 22
13 22
# duplicate name in subquery
query II
SELECT * FROM (SELECT 42 AS a, 44 AS a) tbl1
----
42 44
|
