test/where6.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