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 22set testdir [file dirname $argv0] 23source $testdir/tester.tcl 24 25do_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 39do_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 48do_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 66do_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 77finish_test 78