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
|
# name: test/optimizer/statistics/statistics_setop.test
# description: Statistics propagation test with set operations
# 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
CREATE TABLE integers3 AS SELECT * FROM (VALUES (4), (5), (NULL)) tbl(i);
statement ok
PRAGMA explain_output = OPTIMIZED_ONLY;
# union all
# total min/max after union all is [1, 6]: 7 is out of bounds, so i=7 can be optimized away
query II
EXPLAIN SELECT i=7 FROM (SELECT * FROM integers UNION ALL SELECT * FROM integers2) tbl(i);
----
logical_opt <!REGEX>:.*\(i = 7\).*
# 5 is in bounds, so this cannot be optimized away
query II
EXPLAIN SELECT i=5 FROM (SELECT * FROM integers UNION ALL SELECT * FROM integers2) tbl(i);
----
logical_opt <REGEX>:.*\(i = 5\).*
# neither integers nor integers2 has null values, so this is false
query II
EXPLAIN SELECT i IS NULL FROM (SELECT * FROM integers UNION ALL SELECT * FROM integers2) tbl(i);
----
logical_opt <!REGEX>:.*IS NULL.*
# integers3 has null values, so once we include integers3 we need to execute the IS_NULL predicate
query II
EXPLAIN SELECT i IS NULL FROM (SELECT * FROM integers UNION ALL SELECT * FROM integers2 UNION ALL SELECT * FROM integers3) tbl(i);
----
logical_opt <REGEX>:.*IS NULL.*
# except
# except has the same stats as the LHS (as in the worst case, nothing is filtered out)
# in this case the LHS stats are [4, 6] without null values
query II
EXPLAIN SELECT i=7 FROM (SELECT * FROM integers2 EXCEPT SELECT * FROM integers3) tbl(i);
----
logical_opt <!REGEX>:.*\(i = 7\).*
query II
EXPLAIN SELECT i=5 FROM (SELECT * FROM integers2 EXCEPT SELECT * FROM integers3) tbl(i);
----
logical_opt <REGEX>:.*\(i = 5\).*
query II
EXPLAIN SELECT i IS NULL FROM (SELECT * FROM integers2 EXCEPT SELECT * FROM integers3) tbl(i);
----
logical_opt <!REGEX>:.*IS NULL.*
# intersect
# intersect has as stats the intersection of the LHS with the RHS
# in this case that would be [4, 5] with no null values
# however, we don't actually intersect the stats yet, so limit our testing to the stats of the LHS
query II
EXPLAIN SELECT i=7 FROM (SELECT * FROM integers2 INTERSECT SELECT * FROM integers3) tbl(i);
----
logical_opt <!REGEX>:.*\(i = 7\).*
query II
EXPLAIN SELECT i=5 FROM (SELECT * FROM integers2 INTERSECT SELECT * FROM integers3) tbl(i);
----
logical_opt <REGEX>:.*\(i = 5\).*
# now check the results of all these queries
query I
SELECT i=7 FROM (SELECT * FROM integers UNION ALL SELECT * FROM integers2) tbl(i);
----
0
0
0
0
0
0
query I
SELECT i=5 FROM (SELECT * FROM integers UNION ALL SELECT * FROM integers2) tbl(i);
----
0
0
0
0
1
0
query I
SELECT i IS NULL FROM (SELECT * FROM integers UNION ALL SELECT * FROM integers2) tbl(i);
----
0
0
0
0
0
0
query I
SELECT i IS NULL FROM (SELECT * FROM integers UNION ALL SELECT * FROM integers2 UNION ALL SELECT * FROM integers3) tbl(i);
----
0
0
0
0
0
0
0
0
1
query I
SELECT i=7 FROM (SELECT * FROM integers2 EXCEPT SELECT * FROM integers3) tbl(i);
----
0
query I
SELECT i=5 FROM (SELECT * FROM integers2 EXCEPT SELECT * FROM integers3) tbl(i);
----
0
query I
SELECT i IS NULL FROM (SELECT * FROM integers2 EXCEPT SELECT * FROM integers3) tbl(i);
----
0
query I
SELECT i=7 FROM (SELECT * FROM integers2 INTERSECT SELECT * FROM integers3) tbl(i);
----
0
0
query I
SELECT i=5 FROM (SELECT * FROM integers2 INTERSECT SELECT * FROM integers3) tbl(i) ORDER BY i;
----
0
1
|
