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
|
# name: test/optimizer/statistics/statistics_is_null.test
# description: Test filter propagation in IS NULL/IS NOT NULL operands
# group: [statistics]
statement ok
SET default_null_order='nulls_first';
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), (NULL)) tbl(i);
statement ok
PRAGMA explain_output = OPTIMIZED_ONLY;
# we can statically determine IS NULL/IS NOT NULL are false, if there are no null values for this column
query II
EXPLAIN SELECT i IS NULL FROM integers;
----
logical_opt <!REGEX>:.*IS NULL.*
query II
EXPLAIN SELECT i IS NOT NULL FROM integers;
----
logical_opt <!REGEX>:.*IS NOT NULL.*
# if there are null values, however, we have to execute the operator
query II
EXPLAIN SELECT i IS NULL FROM integers2;
----
logical_opt <REGEX>:.*IS NULL.*
query II
EXPLAIN SELECT i IS NOT NULL FROM integers2;
----
logical_opt <REGEX>:.*IS NOT NULL.*
# filters remove null values, so even if the base column contains null values, we don't need to check IS NULL here
query II
EXPLAIN SELECT i IS NULL FROM integers2 WHERE i>0;
----
logical_opt <!REGEX>:.*IS NULL.*
# left/right outer joins with false condition can convert to cross product with constant NULL value,
# so we don't need to check IS NULL here, since it's always NULL
query II
EXPLAIN SELECT i2.i IS NULL FROM integers i1 LEFT JOIN integers i2 ON (false);
----
logical_opt <!REGEX>:.*IS NULL.*
# full outer joins can introduce nulls, even if the base tables do not contain them
query II
EXPLAIN SELECT i1.i IS NULL FROM integers i1 FULL OUTER JOIN integers i2 ON (false);
----
logical_opt <REGEX>:.*IS NULL.*
# verify that all these queries produce the correct results
query I
SELECT i IS NULL FROM integers;
----
0
0
0
query I
SELECT i IS NOT NULL FROM integers;
----
1
1
1
query I
SELECT i IS NULL FROM integers2;
----
0
0
1
query I
SELECT i IS NOT NULL FROM integers2;
----
1
1
0
query I
SELECT i IS NULL FROM integers2 WHERE i>0;
----
0
0
query I
SELECT i2.i IS NULL FROM integers i1 LEFT JOIN integers i2 ON (false);
----
1
1
1
query I
SELECT i1.i IS NULL FROM integers i1 FULL OUTER JOIN integers i2 ON (false) ORDER BY i1.i;
----
1
1
1
0
0
0
# FIXME: we don't yet correctly track when columns don't contain valid values
mode skip
statement ok
CREATE TABLE integers3 AS SELECT * FROM (VALUES (NULL), (NULL), (NULL)) tbl(i);
# we can statically determine IS NULL/IS NOT NULL are true, if there are no valid values for this column
query II
EXPLAIN SELECT i IS NULL FROM integers3;
----
logical_opt <!REGEX>:.*IS NULL.*
query II
EXPLAIN SELECT i IS NOT NULL FROM integers3;
----
logical_opt <!REGEX>:.*IS NOT NULL.*
query I
SELECT i IS NULL FROM integers3;
----
true
true
true
query I
SELECT i IS NOT NULL FROM integers3;
----
false
false
false
|
