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
|
# name: test/sql/optimizer/expression/test_conjunction_optimization.test
# description: Test conjunction statements that can be simplified
# group: [expression]
statement ok
SET default_null_order='nulls_first';
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE integers(i INTEGER);
statement ok
INSERT INTO integers VALUES (1), (2), (3), (NULL)
# test conjunctions in FILTER clause
query I
SELECT i FROM integers WHERE (i=1 AND i>0) OR (i=1 AND i<3) ORDER BY i
----
1
query I
SELECT i FROM integers WHERE (i=1) OR (i=1) ORDER BY i
----
1
query I
SELECT i FROM integers WHERE (i=1) OR (i=1) OR (i=1) OR (i=1) OR (i=1) ORDER BY i
----
1
query I
SELECT i FROM integers WHERE (i IS NULL AND i=1) OR (i IS NULL AND i<10) ORDER BY i
----
query I
SELECT i FROM integers WHERE (i IS NOT NULL AND i>1) OR (i IS NOT NULL AND i<10) ORDER BY i
----
1
2
3
query I
SELECT i FROM integers WHERE (i IS NULL AND (i+1) IS NULL) OR (i IS NULL AND (i+2) IS NULL) ORDER BY i
----
NULL
query I
SELECT i FROM integers WHERE i=1 OR 1=1 ORDER BY i
----
NULL
1
2
3
query I
SELECT i FROM integers WHERE i=1 OR 1=0 OR 1=1 ORDER BY i
----
NULL
1
2
3
query I
SELECT i FROM integers WHERE (i=1 OR 1=0 OR i=1) AND (0=1 OR 1=0 OR 1=1) ORDER BY i
----
1
# test conjunctions in SELECT clause
query T
SELECT (i=1 AND i>0) OR (i=1 AND i<3) FROM integers ORDER BY i
----
NULL
1
0
0
query T
SELECT (i=1) OR (i=1) FROM integers ORDER BY i
----
NULL
1
0
0
query T
SELECT (i=1) OR (i=1) OR (i=1) OR (i=1) OR (i=1) FROM integers ORDER BY i
----
NULL
1
0
0
query T
SELECT (i IS NULL AND i=1) OR (i IS NULL AND i<10) FROM integers ORDER BY i
----
NULL
0
0
0
query T
SELECT (i IS NOT NULL AND i>1) OR (i IS NOT NULL AND i<10) FROM integers ORDER BY i
----
0
1
1
1
query T
SELECT (i IS NULL AND (i+1) IS NULL) OR (i IS NULL AND (i+2) IS NULL) FROM integers ORDER BY i
----
1
0
0
0
|
