test/whereL.test

   1 # 2018-07-26
   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 the WHERE-clause constant propagation
  13 # optimization.
  14 #
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17 set ::testprefix whereL
  18
  19 do_execsql_test 100 {
  20   CREATE TABLE t1(a INT PRIMARY KEY, b, c, d, e);
  21   CREATE TABLE t2(a INT PRIMARY KEY, f, g, h, i);
  22   CREATE TABLE t3(a INT PRIMARY KEY, j, k, l, m);
  23   CREATE VIEW v4 AS SELECT * FROM t2 UNION ALL SELECT * FROM t3;
  24 }
  25 do_eqp_test 110 {
  26   SELECT * FROM t1, v4 WHERE t1.a=?1 AND v4.a=t1.a;
  27 } {
  28   QUERY PLAN
  29   |--MATERIALIZE xxxxxx
  30   |  `--COMPOUND QUERY
  31   |     |--LEFT-MOST SUBQUERY
  32   |     |  `--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
  33   |     `--UNION ALL
  34   |        `--SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?)
  35   |--SCAN SUBQUERY xxxxxx
  36   `--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
  37 }
  38
  39 # The scan of the t1 table goes first since that enables the ORDER BY
  40 # sort to be omitted.  This would not be possible without constant
  41 # propagation because without it the t1 table would depend on t3.
  42 #
  43 do_eqp_test 120 {
  44   SELECT * FROM t1, t2, t3
  45    WHERE t1.a=t2.a AND t2.a=t3.j AND t3.j=5
  46   ORDER BY t1.a;
  47 } {
  48   QUERY PLAN
  49   |--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
  50   |--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
  51   `--SCAN TABLE t3
  52 }
  53
  54 # Constant propagation in the face of collating sequences:
  55 #
  56 do_execsql_test 200 {
  57   CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary);
  58   CREATE INDEX c3x ON c3(x);
  59   INSERT INTO c3 VALUES('ABC', 'ABC', 'abc');
  60   SELECT * FROM c3 WHERE x=y AND y=z AND z='abc';
  61 } {ABC ABC abc}
  62
  63 # If the constants are blindly propagated, as shown in the following
  64 # query, the wrong answer results:
  65 #
  66 do_execsql_test 201 {
  67   SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc';
  68 } {}
  69
  70 # Constant propagation caused an incorrect answer in the following
  71 # query.  (Reported by Bentley system on 2018-08-09.)
  72 #
  73 do_execsql_test 300 {
  74   CREATE TABLE A(id INTEGER PRIMARY KEY, label TEXT);
  75   CREATE TABLE B(id INTEGER PRIMARY KEY, label TEXT, Aid INTEGER);
  76   CREATE TABLE C(
  77     id INTEGER PRIMARY KEY,
  78     xx INTEGER NOT NULL,
  79     yy INTEGER,
  80     zz INTEGER
  81   );
  82   CREATE UNIQUE INDEX x2 ON C(yy);
  83   CREATE UNIQUE INDEX x4 ON C(yy, zz);
  84   INSERT INTO A(id) VALUES(1);
  85   INSERT INTO B(id) VALUES(2);
  86   INSERT INTO C(id,xx,yy,zz) VALUES(99,50,1,2);
  87   SELECT 1
  88     FROM A,
  89          (SELECT id,xx,yy,zz FROM C) subq,
  90          B
  91    WHERE A.id='1'
  92      AND A.id=subq.yy
  93      AND B.id=subq.zz;
  94 } {1}
  95 do_execsql_test 301 {
  96   SELECT 1
  97     FROM A,
  98          (SELECT id,xx,yy,zz FROM C) subq,
  99          B
 100    WHERE A.id=1
 101      AND A.id=subq.yy
 102      AND B.id=subq.zz;
 103 } {1}
 104 do_execsql_test 302 {
 105   SELECT 1
 106     FROM A,
 107          (SELECT id,yy,zz FROM C) subq,
 108          B
 109    WHERE A.id='1'
 110      AND A.id=subq.yy
 111      AND B.id=subq.zz;
 112 } {1}
 113
 114 # 2018-10-25: Ticket [cf5ed20f]
 115 # Incorrect join result with duplicate WHERE clause constraint.
 116 #
 117 do_execsql_test 400 {
 118   CREATE TABLE x(a, b, c);
 119   CREATE TABLE y(a, b);
 120   INSERT INTO x VALUES (1, 0, 1);
 121   INSERT INTO y VALUES (1, 2);
 122   SELECT x.a FROM x JOIN y ON x.c = y.a WHERE x.b = 1 AND x.b = 1;
 123 } {}
 124
 125 finish_test