AryaWu/sqlite
0
1# 2010 December 62#3# The author disclaims copyright to this source code. In place of4# 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] is13# resolved. That ticket is about an incorrect result that appears when14# an index is added. The root cause is that a constant is being used15# without initialization when the OR optimization applies in the WHERE clause.16#17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20set ::testprefix tkt-80ba20107921 22do_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, t231 WHERE (a='A' AND b='X')32 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));33 }34} {A B}35do_test tkt-80ba2-101 {36 db eval {37 CREATE INDEX i1 ON t1(a);38 SELECT * FROM t1, t239 WHERE (a='A' AND b='X')40 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));41 }42} {A B}43do_test tkt-80ba2-102 {44 optimization_control db factor-constants 045 db cache flush46 db eval {47 SELECT * FROM t1, t248 WHERE (a='A' AND b='X')49 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));50 }51} {A B}52optimization_control db all 153 54# Verify that the optimization_control command is actually working55#56do_test tkt-80ba2-150 {57 optimization_control db factor-constants 158 db cache flush59 set x1 [db eval {EXPLAIN 60 SELECT * FROM t1, t261 WHERE (a='A' AND b='X')62 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]63 optimization_control db factor-constants 064 db cache flush65 set x2 [db eval {EXPLAIN 66 SELECT * FROM t1, t267 WHERE (a='A' AND b='X')68 OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]69 70 expr {$x1==$x2}71} {0}72 73do_test tkt-80ba2-200 {74 db eval {75 CREATE TABLE entry_types (76 id integer primary key,77 name text78 );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 int88 );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 int97 );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_id103 FROM timeline JOIN entry_types ON entry_type = entry_types.id104 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)105 OR (entry_types.name = 'object_change'106 AND entry_id IN (SELECT change_id107 FROM object_changes108 WHERE obj_context = 'exported_pools'));109 }110} {300 object_change 2048}111do_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_id117 FROM timeline JOIN entry_types ON entry_type = entry_types.id118 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)119 OR (entry_types.name = 'object_change'120 AND entry_id IN (SELECT change_id121 FROM object_changes122 WHERE obj_context = 'exported_pools'));123 }124} {300 object_change 2048}125do_test tkt-80ba2-202 {126 optimization_control db factor-constants 0127 db cache flush128 db eval {129 SELECT entry_type,130 entry_types.name,131 entry_id132 FROM timeline JOIN entry_types ON entry_type = entry_types.id133 WHERE (entry_types.name = 'cli_command' AND entry_id=2114)134 OR (entry_types.name = 'object_change'135 AND entry_id IN (SELECT change_id136 FROM object_changes137 WHERE obj_context = 'exported_pools'));138 }139} {300 object_change 2048}140 141#-------------------------------------------------------------------------142#143 144drop_all_tables145do_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);150 151 INSERT INTO t1 VALUES('A', 'B', 'C');152 INSERT INTO t2 VALUES('D', 'E');153}154 155do_execsql_test 302 {156 SELECT * FROM t1, t2 WHERE157 (a='A' AND d='E') OR158 (b='B' AND c IN ('C', 'D', 'E'))159} {A B C D E}160 161do_execsql_test 303 {162 SELECT * FROM t1, t2 WHERE163 (a='A' AND d='E') OR164 (b='B' AND c IN (SELECT c FROM t1))165} {A B C D E}166 167ifcapable compound {168 do_execsql_test 304 {169 SELECT * FROM t1, t2 WHERE170 (a='A' AND d='E') OR171 (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'))172 } {A B C D E}173}174 175do_execsql_test 305 {176 SELECT * FROM t1, t2 WHERE177 (b='B' AND c IN ('C', 'D', 'E')) OR178 (a='A' AND d='E')179} {A B C D E}180 181do_execsql_test 306 {182 SELECT * FROM t1, t2 WHERE183 (b='B' AND c IN (SELECT c FROM t1)) OR184 (a='A' AND d='E')185} {A B C D E}186 187ifcapable compound {188 do_execsql_test 307 {189 SELECT * FROM t1, t2 WHERE190 (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D')) OR191 (a='A' AND d='E')192 } {A B C D E}193}194 195finish_test196