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 77# Ticket [2c2de252666662f5459904fc33a9f2956cbff23c] 78# 79do_test tkt-31338-3.1 { 80 foreach x [db eval {SELECT name FROM sqlite_master WHERE type='table'}] { 81 db eval "DROP TABLE $x" 82 } 83 db eval { 84 CREATE TABLE t1(a,b,c,d); 85 CREATE TABLE t2(e,f); 86 INSERT INTO t1 VALUES(1,2,3,4); 87 INSERT INTO t2 VALUES(10,-8); 88 CREATE INDEX t1a ON t1(a); 89 CREATE INDEX t1b ON t1(b); 90 CREATE TABLE t3(g); 91 INSERT INTO t3 VALUES(4); 92 CREATE TABLE t4(h); 93 INSERT INTO t4 VALUES(5); 94 95 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 96 WHERE (a=1 AND h=4) 97 OR (b IN ( 98 SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) 99 GROUP BY e 100 )); 101 } 102} {4 1 2 3 4 {}} 103do_test tkt-31338-3.2 { 104 db eval { 105 SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h 106 WHERE (a=1 AND h=4) 107 OR (b=2 AND b NOT IN ( 108 SELECT x+1 FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) 109 GROUP BY e 110 )); 111 } 112} {4 1 2 3 4 {}} 113 114 115finish_test 116