| blob | ea0799b5683c12275bf2484f8acb5349a29cbd00 |
1 # 2010 December 6
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. Specifically,
12 # it tests that ticket [80ba201079ea608071d22a57856b940ea3ac53ce] is
13 # resolved. That ticket is about an incorrect result that appears when
14 # an index is added. The root cause is that a constant is being used
15 # without initialization when the OR optimization applies in the WHERE clause.
16 #
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set ::testprefix tkt-80ba201079
22 do_test tkt-80ba2-100 {
23 db eval {
24 CREATE TABLE t1(a);
25 INSERT INTO t1 VALUES('A');
26 CREATE TABLE t2(b);
27 INSERT INTO t2 VALUES('B');
28 CREATE TABLE t3(c);
29 INSERT INTO t3 VALUES('C');
30 SELECT * FROM t1, t2
31 WHERE (a='A' AND b='X')
32 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
33 }
34 } {A B}
35 do_test tkt-80ba2-101 {
36 db eval {
37 CREATE INDEX i1 ON t1(a);
38 SELECT * FROM t1, t2
39 WHERE (a='A' AND b='X')
40 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
41 }
42 } {A B}
43 do_test tkt-80ba2-102 {
44 optimization_control db factor-constants 0
45 db cache flush
46 db eval {
47 SELECT * FROM t1, t2
48 WHERE (a='A' AND b='X')
49 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
50 }
51 } {A B}
52 optimization_control db all 1
54 # Verify that the optimization_control command is actually working
55 #
56 do_test tkt-80ba2-150 {
57 optimization_control db factor-constants 1
58 db cache flush
59 set x1 [db eval {EXPLAIN
60 SELECT * FROM t1, t2
61 WHERE (a='A' AND b='X')
62 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]
63 optimization_control db factor-constants 0
64 db cache flush
65 set x2 [db eval {EXPLAIN
66 SELECT * FROM t1, t2
67 WHERE (a='A' AND b='X')
68 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]
70 expr {$x1==$x2}
71 } {0}
73 do_test tkt-80ba2-200 {
74 db eval {
75 CREATE TABLE entry_types (
76 id integer primary key,
77 name text
78 );
79 INSERT INTO "entry_types" VALUES(100,'cli_command');
80 INSERT INTO "entry_types" VALUES(300,'object_change');
81 CREATE TABLE object_changes (
82 change_id integer primary key,
83 system_id int,
84 obj_id int,
85 obj_context text,
86 change_type int,
87 command_id int
88 );
89 INSERT INTO "object_changes" VALUES(1551,1,114608,'exported_pools',1,2114);
90 INSERT INTO "object_changes" VALUES(2048,1,114608,'exported_pools',2,2319);
91 CREATE TABLE timeline (
92 rowid integer primary key,
93 timestamp text,
94 system_id int,
95 entry_type int,
96 entry_id int
97 );
98 INSERT INTO "timeline" VALUES(6735,'2010-11-21 17:08:27.000',1,300,2048);
99 INSERT INTO "timeline" VALUES(6825,'2010-11-21 17:09:21.000',1,300,2114);
100 SELECT entry_type,
101 entry_types.name,
102 entry_id
103 FROM timeline JOIN entry_types ON entry_type = entry_types.id
104 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
105 OR (entry_types.name = 'object_change'
106 AND entry_id IN (SELECT change_id
107 FROM object_changes
108 WHERE obj_context = 'exported_pools'));
109 }
110 } {300 object_change 2048}
111 do_test tkt-80ba2-201 {
112 db eval {
113 CREATE INDEX timeline_entry_id_idx on timeline(entry_id);
114 SELECT entry_type,
115 entry_types.name,
116 entry_id
117 FROM timeline JOIN entry_types ON entry_type = entry_types.id
118 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
119 OR (entry_types.name = 'object_change'
120 AND entry_id IN (SELECT change_id
121 FROM object_changes
122 WHERE obj_context = 'exported_pools'));
123 }
124 } {300 object_change 2048}
125 do_test tkt-80ba2-202 {
126 optimization_control db factor-constants 0
127 db cache flush
128 db eval {
129 SELECT entry_type,
130 entry_types.name,
131 entry_id
132 FROM timeline JOIN entry_types ON entry_type = entry_types.id
133 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
134 OR (entry_types.name = 'object_change'
135 AND entry_id IN (SELECT change_id
136 FROM object_changes
137 WHERE obj_context = 'exported_pools'));
138 }
139 } {300 object_change 2048}
141 #-------------------------------------------------------------------------
142 #
144 drop_all_tables
145 do_execsql_test 301 {
146 CREATE TABLE t1(a, b, c);
147 CREATE INDEX i1 ON t1(a);
148 CREATE INDEX i2 ON t1(b);
149 CREATE TABLE t2(d, e);
151 INSERT INTO t1 VALUES('A', 'B', 'C');
152 INSERT INTO t2 VALUES('D', 'E');
153 }
155 do_execsql_test 302 {
156 SELECT * FROM t1, t2 WHERE
157 (a='A' AND d='E') OR
158 (b='B' AND c IN ('C', 'D', 'E'))
159 } {A B C D E}
161 do_execsql_test 303 {
162 SELECT * FROM t1, t2 WHERE
163 (a='A' AND d='E') OR
164 (b='B' AND c IN (SELECT c FROM t1))
165 } {A B C D E}
167 ifcapable compound {
168 do_execsql_test 304 {
169 SELECT * FROM t1, t2 WHERE
170 (a='A' AND d='E') OR
171 (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'))
172 } {A B C D E}
173 }
175 do_execsql_test 305 {
176 SELECT * FROM t1, t2 WHERE
177 (b='B' AND c IN ('C', 'D', 'E')) OR
178 (a='A' AND d='E')
179 } {A B C D E}
181 do_execsql_test 306 {
182 SELECT * FROM t1, t2 WHERE
183 (b='B' AND c IN (SELECT c FROM t1)) OR
184 (a='A' AND d='E')
185 } {A B C D E}
187 ifcapable compound {
188 do_execsql_test 307 {
189 SELECT * FROM t1, t2 WHERE
190 (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D')) OR
191 (a='A' AND d='E')
192 } {A B C D E}
193 }
195 finish_test