1 # 2009 December 16 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. 12 # 13 # This file implements tests to verify that ticket [31338dca7e] has been 14 # fixed. Ticket [31338dca7e] demonstrates problems with the OR-clause 15 # optimization in joins where the WHERE clause is of the form 16 # 17 # (x AND y) OR z 18 # 19 # And the x and y subterms from from different tables of the join. 20 # 21 22 set testdir [file dirname $argv0] 23 source $testdir/tester.tcl 24 25 do_test tkt-31338-1.1 { 26 db eval { 27 CREATE TABLE t1(x); 28 CREATE TABLE t2(y); 29 INSERT INTO t1 VALUES(111); 30 INSERT INTO t1 VALUES(222); 31 INSERT INTO t2 VALUES(333); 32 INSERT INTO t2 VALUES(444); 33 SELECT * FROM t1, t2 34 WHERE (x=111 AND y!=444) OR x=222 35 ORDER BY x, y; 36 } 37 } {111 333 222 333 222 444} 38 39 do_test tkt-31338-1.2 { 40 db eval { 41 CREATE INDEX t1x ON t1(x); 42 SELECT * FROM t1, t2 43 WHERE (x=111 AND y!=444) OR x=222 44 ORDER BY x, y; 45 } 46 } {111 333 222 333 222 444} 47 48 do_test tkt-31338-2.1 { 49 db eval { 50 CREATE TABLE t3(v,w); 51 CREATE TABLE t4(x,y); 52 CREATE TABLE t5(z); 53 INSERT INTO t3 VALUES(111,222); 54 INSERT INTO t3 VALUES(333,444); 55 INSERT INTO t4 VALUES(222,333); 56 INSERT INTO t4 VALUES(444,555); 57 INSERT INTO t5 VALUES(888); 58 INSERT INTO t5 VALUES(999); 59 60 SELECT * FROM t3, t4, t5 61 WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444) 62 ORDER BY v, w, x, y, z; 63 } 64 } {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999} 65 66 do_test tkt-31338-2.2 { 67 db eval { 68 CREATE INDEX t3v ON t3(v); 69 CREATE INDEX t4x ON t4(x); 70 SELECT * FROM t3, t4, t5 71 WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444) 72 ORDER BY v, w, x, y, z; 73 } 74 } {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999} 75 76 77 finish_test 78
