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
178
179
180
181
182
183
|
# name: test/optimizer/deliminator.test
# description: Test the Deliminator optimizer
# group: [optimizer]
statement ok
SET default_null_order='nulls_first';
require tpch
statement ok
CALL dbgen(sf=0.0001);
statement ok
PRAGMA explain_output = OPTIMIZED_ONLY
# Q 02: join with JoinType::SINGLE (created when pushing down dependent joins) is converted to LEFT
query II
explain SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment FROM part, supplier, partsupp, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND p_size = 15 AND p_type LIKE '%BRASS' AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE' AND ps_supplycost = ( SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE') ORDER BY s_acctbal DESC, n_name, s_name, p_partkey LIMIT 100;
----
logical_opt <!REGEX>:.*SINGLE.*
# Q 17: join with JoinType::SINGLE (created when pushing down dependent joins) is converted to LEFT
query II
explain SELECT sum(l_extendedprice) / 7.0 AS avg_yearly FROM lineitem, part WHERE p_partkey = l_partkey AND p_brand = 'Brand#23' AND p_container = 'MED BOX' AND l_quantity < ( SELECT 0.2 * avg(l_quantity) FROM lineitem WHERE l_partkey = p_partkey);
----
logical_opt <!REGEX>:.*SINGLE.*
# Q 17: if we remove the filters """ p_brand = 'Brand#23' AND p_container = 'MED BOX' """ we can remove the whole DELIM join
query II
explain SELECT sum(l_extendedprice) / 7.0 AS avg_yearly FROM lineitem, part WHERE p_partkey = l_partkey AND l_quantity < (SELECT 0.2 * avg(l_quantity) FROM lineitem WHERE l_partkey = p_partkey);
----
logical_opt <!REGEX>:.*DELIM_JOIN.*
# Q 20: join with JoinType::SINGLE (created when pushing down dependent joins) is converted to LEFT
query II
explain SELECT s_name, s_address FROM supplier, nation WHERE s_suppkey IN ( SELECT ps_suppkey FROM partsupp WHERE ps_partkey IN ( SELECT p_partkey FROM part WHERE p_name LIKE 'forest%') AND ps_availqty > ( SELECT 0.5 * sum(l_quantity) FROM lineitem WHERE l_partkey = ps_partkey AND l_suppkey = ps_suppkey AND l_shipdate >= CAST('1994-01-01' AS date) AND l_shipdate < CAST('1995-01-01' AS date))) AND s_nationkey = n_nationkey AND n_name = 'CANADA' ORDER BY s_name;
----
logical_opt <!REGEX>:.*SINGLE.*
statement ok
CREATE TABLE integers(i INTEGER, j integer)
statement ok
INSERT INTO integers VALUES (NULL,1)
query II
SELECT i, (select SUM(i) is not null from integers where j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL False
query II
SELECT i, (select SUM(i) AND false from integers where j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL False
query II
SELECT i, (select SUM(i) OR true from integers where j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL True
query II
SELECT i, (select SUM(i) IS DISTINCT FROM NULL from integers where j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL False
query II
SELECT i, (select SUM(i) IS NOT DISTINCT FROM NULL from integers where j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL TRUE
query II
SELECT i, (select CONCAT(SUM(i), 'hello') from integers where j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL hello
query II
SELECT i, (select [SUM(i)] from integers where j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL [NULL]
query II
SELECT i, (select {'a': SUM(i)} from integers where j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL {'a': NULL}
statement ok
INSERT INTO integers VALUES (1,2)
query II
SELECT i, (select sum(i) from integers where j is null and j = i1.j) FROM integers i1 ORDER BY 1;
----
NULL NULL
1 NULL
statement ok
DROP TABLE integers;
statement ok
CREATE TABLE integers(i INTEGER);
statement ok
INSERT INTO integers VALUES (1), (2), (3), (NULL);
query II
SELECT i, (SELECT SUM(i) IS NOT NULL FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;
----
NULL False
1 True
2 True
3 False
query II
SELECT i, (SELECT sum IS NULL FROM (SELECT SUM(i) sum FROM integers i2 WHERE i2.i>i1.i)) t1 FROM integers i1 ORDER BY i;
----
NULL True
1 False
2 False
3 True
query II
SELECT i, (SELECT sum IS NOT NULL FROM (SELECT SUM(i) sum FROM integers i2 WHERE i2.i>i1.i)) t1 FROM integers i1 ORDER BY i;
----
NULL False
1 True
2 True
3 False
query II
SELECT i, (SELECT [sum] FROM (SELECT SUM(i) sum FROM integers i2 WHERE i2.i>i1.i)) t1 FROM integers i1 ORDER BY i;
----
NULL [NULL]
1 [5]
2 [3]
3 [NULL]
query II
SELECT i, (SELECT (SELECT SUM(i) IS NOT NULL) FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;
----
NULL False
1 True
2 True
3 False
query II
SELECT i, (SELECT (SELECT [SUM(i)]) FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;
----
NULL [NULL]
1 [5]
2 [3]
3 [NULL]
query II
SELECT i, (SELECT SUM(i) AND false FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;
----
NULL 0
1 0
2 0
3 0
query II
SELECT i, (SELECT SUM(i) OR true FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;
----
NULL 1
1 1
2 1
3 1
query II
SELECT i, (SELECT COUNT(*) FROM (SELECT SUM(i) FROM integers i2 WHERE i2.i>i1.i)) t1 FROM integers i1 ORDER BY i;
----
NULL 1
1 1
2 1
3 1
query II
SELECT i, (SELECT COUNT(sum) FROM (SELECT SUM(i) sum FROM integers i2 WHERE i2.i>i1.i) t2) t1 FROM integers i1 ORDER BY i;
----
NULL 0
1 1
2 1
3 0
|
