AryaWu/sqlite

1# 2009 December 162#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.12#13# This file implements tests to verify that ticket [31338dca7e] has been14# fixed.  Ticket [31338dca7e] demonstrates problems with the OR-clause15# optimization in joins where the WHERE clause is of the form16#17#     (x AND y) OR z18#19# And the x and y subterms from from different tables of the join.20#21 22set testdir [file dirname $argv0]23source $testdir/tester.tcl24 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, t234     WHERE (x=111 AND y!=444) OR x=22235     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, t243     WHERE (x=111 AND y!=444) OR x=22244     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, t561     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, t571     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=h96     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 e100            ));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=h106     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 e110            ));111  }    112} {4 1 2 3 4 {}}113do_test tkt-31338-3.3 {114  db eval {    115    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h116     WHERE (+a=1 AND h=4)117         OR (b IN (118               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)119               GROUP BY e120            ));121  }    122} {4 1 2 3 4 {}}123do_test tkt-31338-3.4 {124  db eval {    125    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h126     WHERE (a=1 AND h=4)127         OR (+b IN (128               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)129               GROUP BY e130            ));131  }    132} {4 1 2 3 4 {}}133 134do_test tkt-31338-3.5 {135  db eval {136    CREATE TABLE t5(a,b,c,d,e,f);137    CREATE TABLE t6(g,h);138    CREATE TRIGGER t6r AFTER INSERT ON t6 BEGIN139      INSERT INTO t5    140        SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h141         WHERE (a=1 AND h=4)142            OR (b IN (143               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)144               GROUP BY e145            ));146    END;147    INSERT INTO t6 VALUES(88,99);148    SELECT * FROM t5;149  }    150} {4 1 2 3 4 {}}151 152do_test tkt-31338-3.6 {153  db eval {    154    INSERT INTO t1 VALUES(2,4,3,4);155    INSERT INTO t1 VALUES(99,101,3,4);156    INSERT INTO t1 VALUES(98,97,3,4);157    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h158     WHERE (a=1 AND h=4)159         OR (b IN (160               SELECT x+a FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)161               GROUP BY e162            ));163  }    164} {4 2 4 3 4 {} 4 99 101 3 4 {}}165 166do_test tkt-31338-3.7 {167  db eval {168    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h169     WHERE (a=1 AND h=4)170         OR (b IN (171               SELECT x FROM (SELECT e+f+a AS x, e FROM t2 ORDER BY 1 LIMIT 2)172               GROUP BY e173            ));174  }    175} {4 2 4 3 4 {} 4 99 101 3 4 {}}176 177 178finish_test179