Home | History | Annotate | Download | only in test 
      1 # 2007 June 8
      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.  The
     12 # focus of this file is testing that terms in the ON clause of
     13 # a LEFT OUTER JOIN are not used with indices.  See ticket #3015.
     14 #
     15 # $Id: where6.test,v 1.2 2008/04/17 19:14:02 drh Exp $
     16 
     17 set testdir [file dirname $argv0]
     18 source $testdir/tester.tcl
     19 
     20 # Build some test data
     21 #
     22 do_test where6-1.1 {
     23   execsql {
     24     CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c);
     25     INSERT INTO t1 VALUES(1,3,1);
     26     INSERT INTO t1 VALUES(2,4,2);
     27     CREATE TABLE t2(x INTEGER PRIMARY KEY);
     28     INSERT INTO t2 VALUES(3);
     29 
     30     SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1;
     31   }
     32 } {1 3 1 3 2 4 2 {}}
     33 do_test where6-1.2 {
     34   execsql {
     35     SELECT * FROM t1 LEFT JOIN t2 ON x=b AND c=1;
     36   }
     37 } {1 3 1 3 2 4 2 {}}
     38 do_test where6-1.3 {
     39   execsql {
     40     SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c;
     41   }
     42 } {1 3 1 3 2 4 2 {}}
     43 do_test where6-1.4 {
     44   execsql {
     45     SELECT * FROM t1 LEFT JOIN t2 ON b=x AND 1=c;
     46   }
     47 } {1 3 1 3 2 4 2 {}}
     48 
     49 ifcapable explain {
     50   do_test where6-1.5 {
     51      explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c}
     52   } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1}]
     53   do_test where6-1.6 {
     54      explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c}
     55   } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1}]
     56 }
     57 
     58 do_test where6-1.11 {
     59   execsql {
     60     SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1;
     61   }
     62 } {1 3 1 3}
     63 do_test where6-1.12 {
     64   execsql {
     65     SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE c=1;
     66   }
     67 } {1 3 1 3}
     68 do_test where6-1.13 {
     69   execsql {
     70     SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE 1=c;
     71   }
     72 } {1 3 1 3}
     73 
     74 
     75 
     76 do_test where6-2.1 {
     77   execsql {
     78     CREATE INDEX i1 ON t1(c);
     79 
     80     SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1;
     81   }
     82 } {1 3 1 3 2 4 2 {}}
     83 do_test where6-2.2 {
     84   execsql {
     85     SELECT * FROM t1 LEFT JOIN t2 ON x=b AND c=1;
     86   }
     87 } {1 3 1 3 2 4 2 {}}
     88 do_test where6-2.3 {
     89   execsql {
     90     SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c;
     91   }
     92 } {1 3 1 3 2 4 2 {}}
     93 do_test where6-2.4 {
     94   execsql {
     95     SELECT * FROM t1 LEFT JOIN t2 ON b=x AND 1=c;
     96   }
     97 } {1 3 1 3 2 4 2 {}}
     98 
     99 ifcapable explain {
    100   do_test where6-2.5 {
    101      explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c}
    102   } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1}]
    103   do_test where6-2.6 {
    104      explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c}
    105   } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1}]
    106 }
    107 
    108 
    109 do_test where6-2.11 {
    110   execsql {
    111     SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1;
    112   }
    113 } {1 3 1 3}
    114 do_test where6-2.12 {
    115   execsql {
    116     SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE c=1;
    117   }
    118 } {1 3 1 3}
    119 do_test where6-2.13 {
    120   execsql {
    121     SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c;
    122   }
    123 } {1 3 1 3}
    124 do_test where6-2.14 {
    125   execsql {
    126     SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE 1=c;
    127   }
    128 } {1 3 1 3}
    129 
    130 # Ticket [ebdbadade5b]:
    131 # If the ON close on a LEFT JOIN is of the form x=y where both x and y
    132 # are indexed columns on tables to left of the join, then do not use that 
    133 # term with indices to either table.
    134 #
    135 do_test where6-3.1 {
    136   db eval {
    137     CREATE TABLE t4(x UNIQUE);
    138     INSERT INTO t4 VALUES('abc');
    139     INSERT INTO t4 VALUES('def');
    140     INSERT INTO t4 VALUES('ghi');
    141     CREATE TABLE t5(a, b, c, PRIMARY KEY(a,b));
    142     INSERT INTO t5 VALUES('abc','def',123);
    143     INSERT INTO t5 VALUES('def','ghi',456);
    144 
    145     SELECT t4a.x, t4b.x, t5.c, t6.v
    146       FROM t4 AS t4a
    147            INNER JOIN t4 AS t4b
    148            LEFT JOIN t5 ON t5.a=t4a.x AND t5.b=t4b.x
    149            LEFT JOIN (SELECT 1 AS v) AS t6 ON t4a.x=t4b.x
    150      ORDER BY 1, 2, 3;
    151   }
    152 } {abc abc {} 1 abc def 123 {} abc ghi {} {} def abc {} {} def def {} 1 def ghi 456 {} ghi abc {} {} ghi def {} {} ghi ghi {} 1}
    153 
    154 finish_test
    155