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
184
|
# name: test/optimizer/statistics/statistics_join.test
# description: Statistics propagation test with joins
# group: [statistics]
statement ok
CREATE TABLE integers AS SELECT * FROM (VALUES (1), (2), (3)) tbl(i);
statement ok
CREATE TABLE integers2 AS SELECT * FROM (VALUES (4), (5), (6)) tbl(i);
statement ok
PRAGMA explain_output = OPTIMIZED_ONLY;
# inner join
# join cannot match
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i=i2.i ORDER BY 1;
----
logical_opt <REGEX>:.*EMPTY_RESULT.*
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i>i2.i ORDER BY 1;
----
logical_opt <REGEX>:.*EMPTY_RESULT.*
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i>=i2.i ORDER BY 1;
----
logical_opt <REGEX>:.*EMPTY_RESULT.*
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i2.i<i1.i ORDER BY 1;
----
logical_opt <REGEX>:.*EMPTY_RESULT.*
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i2.i<=i1.i ORDER BY 1;
----
logical_opt <REGEX>:.*EMPTY_RESULT.*
# join is guaranteed to match
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i<i2.i ORDER BY 1;
----
logical_opt <REGEX>:.*CROSS_PRODUCT.*
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i<=i2.i ORDER BY 1;
----
logical_opt <REGEX>:.*CROSS_PRODUCT.*
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i2.i>i1.i ORDER BY 1;
----
logical_opt <REGEX>:.*CROSS_PRODUCT.*
query II
EXPLAIN SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i2.i>=i1.i ORDER BY 1;
----
logical_opt <REGEX>:.*CROSS_PRODUCT.*
# left join
# join cannot match
# note that the result is not actually empty here; only the RHS is set to the empty result
query II
EXPLAIN SELECT i1.i FROM integers i1 LEFT JOIN integers2 i2 ON i1.i=i2.i ORDER BY 1;
----
logical_opt <REGEX>:.*EMPTY_RESULT.*
# semi join
# join cannot match: replaced with empty result
query II
explain select * from integers where i > any(select * from integers2) order by 1;
----
logical_opt <REGEX>:.*EMPTY_RESULT.*
# join is guaranteed to match: entire join is stripped away
query II
explain select * from integers where i < any(select * from integers2) order by 1;
----
logical_opt <!REGEX>:.*JOIN.*
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i=i2.i ORDER BY 1;
----
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i>i2.i ORDER BY 1;
----
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i>=i2.i ORDER BY 1;
----
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i2.i<i1.i ORDER BY 1;
----
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i2.i<=i1.i ORDER BY 1;
----
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i<i2.i ORDER BY 1;
----
1
1
1
2
2
2
3
3
3
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i1.i<=i2.i ORDER BY 1;
----
1
1
1
2
2
2
3
3
3
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i2.i>i1.i ORDER BY 1;
----
1
1
1
2
2
2
3
3
3
query I
SELECT i1.i FROM integers i1 JOIN integers2 i2 ON i2.i>=i1.i ORDER BY 1;
----
1
1
1
2
2
2
3
3
3
query I
SELECT i1.i FROM integers i1 LEFT JOIN integers2 i2 ON i1.i=i2.i ORDER BY 1;
----
1
2
3
query I
SELECT i1.i FROM integers i1 LEFT JOIN integers2 i2 ON i1.i<i2.i ORDER BY 1;
----
1
1
1
2
2
2
3
3
3
query I
select * from integers where i > any(select * from integers2) order by 1;
----
query I
select * from integers where i < any(select * from integers2) order by 1;
----
1
2
3
|
