test/autoindex4.test

   1 # 2014-10-24
   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 #
  12 # This file implements regression tests for SQLite library.  The
  13 # focus of this script is testing automatic index creation logic,
  14 # and specifically creation of automatic partial indexes.
  15 #
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19
  20 do_execsql_test autoindex4-1.0 {
  21   CREATE TABLE t1(a,b);
  22   INSERT INTO t1 VALUES(123,'abc'),(234,'def'),(234,'ghi'),(345,'jkl');
  23   CREATE TABLE t2(x,y);
  24   INSERT INTO t2 VALUES(987,'zyx'),(654,'wvu'),(987,'rqp');
  25
  26   SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=987 ORDER BY +b;
  27 } {234 def 987 rqp | 234 def 987 zyx | 234 ghi 987 rqp | 234 ghi 987 zyx |}
  28 do_execsql_test autoindex4-1.1 {
  29   SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=555;
  30 } {}
  31
  32 do_execsql_test autoindex4-1.2 {
  33   SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555;
  34 } {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
  35 do_execsql_test autoindex4-1.2-rj {
  36   SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON a=234 AND x=555;
  37 } {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
  38 do_execsql_test autoindex4-1.3 {
  39   SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234;
  40 } {234 def {} {} | 234 ghi {} {} |}
  41 do_execsql_test autoindex4-1.3-rj {
  42   SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON x=555 WHERE a=234;
  43 } {234 def {} {} | 234 ghi {} {} |}
  44 do_execsql_test autoindex4-1.4 {
  45   SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555;
  46 } {}
  47 do_execsql_test autoindex4-1.4-rj {
  48   SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 WHERE a=234 AND x=555;
  49 } {}
  50
  51
  52 do_execsql_test autoindex4-2.0 {
  53   CREATE TABLE t3(e,f);
  54   INSERT INTO t3 VALUES(123,654),(555,444),(234,987);
  55
  56   SELECT (SELECT count(*) FROM t1, t2 WHERE a=e AND x=f), e, f, '|'
  57     FROM t3
  58    ORDER BY rowid;
  59 } {1 123 654 | 0 555 444 | 4 234 987 |}
  60
  61 # Ticket [2326c258d02ead33d]
  62 # Two joins, one with and the other without an ORDER BY clause.
  63 # The one without ORDER BY correctly returns two rows of result.
  64 # The one with ORDER BY returns no rows.
  65 #
  66 do_execsql_test autoindex4-3.0 {
  67   CREATE TABLE A(Name text);
  68   CREATE TABLE Items(ItemName text , Name text);
  69   INSERT INTO Items VALUES('Item1','Parent');
  70   INSERT INTO Items VALUES('Item2','Parent');
  71   CREATE TABLE B(Name text);
  72
  73   SELECT Items.ItemName
  74     FROM Items
  75       LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
  76       LEFT JOIN B ON (B.Name = Items.ItemName)
  77     WHERE Items.Name = 'Parent'
  78     ORDER BY Items.ItemName;
  79 } {Item1 Item2}
  80 do_execsql_test autoindex4-3.1 {
  81   SELECT Items.ItemName
  82     FROM A
  83       RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
  84       LEFT JOIN B ON (B.Name = Items.ItemName)
  85     WHERE Items.Name = 'Parent'
  86     ORDER BY Items.ItemName;
  87 } {Item1 Item2}
  88 do_execsql_test autoindex4-3.10 {
  89   CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
  90
  91   SELECT Items.ItemName
  92     FROM Items
  93       LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
  94       LEFT JOIN B ON (B.Name = Items.ItemName)
  95     WHERE Items.Name = 'Parent'
  96     ORDER BY Items.ItemName;
  97 } {Item1 Item2}
  98 do_execsql_test autoindex4-3.11 {
  99   SELECT Items.ItemName
 100     FROM A
 101       RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
 102       LEFT JOIN B ON (B.Name = Items.ItemName)
 103     WHERE Items.Name = 'Parent'
 104     ORDER BY Items.ItemName;
 105 } {Item1 Item2}
 106
 107 # 2021-11-30 - Enhancement to help the automatic index mechanism to
 108 # create a partial index more often.
 109 #
 110 unset -nocomplain id data1 data2 jointype onclause whereclause answer
 111 foreach {id data1 data2 jointype onclause whereclause answer} {
 112   1
 113   VALUES(1,2),(3,4)
 114   VALUES(1,2),(3,4)
 115   {LEFT JOIN}
 116   a=x
 117   {y=4 OR y IS NULL}
 118   {3 4 3 4}
 119
 120   2
 121   VALUES(1,2),(3,4)
 122   VALUES(1,2),(3,4)
 123   {LEFT JOIN}
 124   {a=x AND y=4}
 125   {coalesce(y,4)==4}
 126   {1 2 {} {} 3 4 3 4}
 127
 128   3
 129   VALUES(1,2),(3,4)
 130   VALUES(1,2),(3,4)
 131   {JOIN}
 132   {a=x}
 133   {y=4 OR y IS NULL}
 134   {3 4 3 4}
 135
 136   4
 137   VALUES(1,2),(3,4)
 138   VALUES(1,2),(3,4)
 139   {JOIN}
 140   {a=x AND y=4}
 141   {coalesce(y,4)==4}
 142   {3 4 3 4}
 143
 144   5.1
 145   VALUES(1,2),(3,4),(NULL,4)
 146   VALUES(1,2),(3,4)
 147   {LEFT JOIN}
 148   a=x
 149   {y=4 OR y IS NULL}
 150   {3 4 3 4 {} 4 {} {}}
 151
 152   5.2
 153   VALUES(1,2),(3,4),(NULL,4)
 154   VALUES(1,2),(3,4)
 155   {LEFT JOIN}
 156   a=x
 157   {y NOT IN ()}
 158   {1 2 1 2 3 4 3 4 {} 4 {} {}}
 159
 160   5.3
 161   VALUES(1,2),(3,4),(NULL,4)
 162   VALUES(1,2),(3,4)
 163   {LEFT JOIN}
 164   a=x
 165   {y NOT IN (SELECT 1 WHERE false)}
 166   {1 2 1 2 3 4 3 4 {} 4 {} {}}
 167
 168   6
 169   VALUES(1,2),(3,4)
 170   VALUES(1,2),(3,4),(NULL,4)
 171   {LEFT JOIN}
 172   {a=x AND y=4}
 173   {coalesce(y,4)==4}
 174   {1 2 {} {} 3 4 3 4}
 175
 176   7
 177   VALUES(1,2),(3,4),(NULL,4)
 178   VALUES(1,2),(3,4),(NULL,4)
 179   {JOIN}
 180   {a=x}
 181   {y=4 OR y IS NULL}
 182   {3 4 3 4}
 183
 184   8
 185   VALUES(1,2),(3,4)
 186   VALUES(1,2),(3,4)
 187   {JOIN}
 188   {a=x AND y=4}
 189   {coalesce(y,4)==4}
 190   {3 4 3 4}
 191 } {
 192   do_test autoindex4-4.$id.0 {
 193     db eval {
 194        DROP TABLE IF EXISTS t1;
 195        CREATE TABLE t1(a INT, b INT);
 196        DROP TABLE IF EXISTS t2;
 197        CREATE TABLE t2(x INT, y INT);
 198     }
 199     db eval "INSERT INTO t1(a,b) $data1;"
 200     db eval "INSERT INTO t2(x,y) $data2;"
 201   } {}
 202   set sql "SELECT * FROM t1 $jointype t2 ON $onclause WHERE $whereclause"
 203   # puts "sql = $sql"
 204   do_test autoindex4-4.$id.1 {
 205     db eval {PRAGMA automatic_index=ON;}
 206     db eval $sql
 207   } $answer
 208   do_test autoindex4-4.$id.2 {
 209     db eval {PRAGMA automatic_index=OFF;}
 210     db eval $sql
 211   } $answer
 212   do_test autoindex4-4.$id.3 {
 213     db eval {PRAGMA automatic_index=ON;}
 214     optimization_control db all 0
 215     db eval $sql
 216   } $answer
 217   optimization_control db all 1
 218 }
 219
 220
 221
 222
 223 finish_test