test/pushdown.test

   1 # 2017 April 29
   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 set testdir [file dirname $argv0]
  13 source $testdir/tester.tcl
  14 set testprefix pushdown
  15
  16 do_execsql_test 1.0 {
  17   CREATE TABLE t1(a, b, c);
  18   INSERT INTO t1 VALUES(1, 'b1', 'c1');
  19   INSERT INTO t1 VALUES(2, 'b2', 'c2');
  20   INSERT INTO t1 VALUES(3, 'b3', 'c3');
  21   INSERT INTO t1 VALUES(4, 'b4', 'c4');
  22   CREATE INDEX i1 ON t1(a, c);
  23 }
  24
  25 proc f {val} {
  26   lappend ::L $val
  27   return 0
  28 }
  29 db func f f
  30
  31 do_test 1.1 {
  32   set L [list]
  33   execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
  34   set L
  35 } {c2}
  36
  37 do_test 1.2 {
  38   set L [list]
  39   execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
  40   set L
  41 } {c3}
  42
  43 do_execsql_test 1.3 {
  44   DROP INDEX i1;
  45   CREATE INDEX i1 ON t1(a, b);
  46 }
  47 do_test 1.4 {
  48   set L [list]
  49   execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
  50   set L
  51 } {b2}
  52
  53 do_test 1.5 {
  54   set L [list]
  55   execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
  56   set L
  57 } {b3}
  58
  59 #-----------------------------------------------
  60
  61 do_execsql_test 2.0 {
  62   CREATE TABLE u1(a, b, c);
  63   CREATE TABLE u2(x, y, z);
  64
  65   INSERT INTO u1 VALUES('a1', 'b1', 'c1');
  66   INSERT INTO u2 VALUES('a1', 'b1', 'c1');
  67 }
  68
  69 do_test 2.1 {
  70   set L [list]
  71   execsql {
  72     SELECT * FROM u1 WHERE f('one')=123 AND 123=(
  73       SELECT x FROM u2 WHERE x=a AND f('two')
  74     )
  75   }
  76   set L
  77 } {one}
  78
  79 do_test 2.2 {
  80   set L [list]
  81   execsql {
  82     SELECT * FROM u1 WHERE 123=(
  83       SELECT x FROM u2 WHERE x=a AND f('two')
  84     ) AND f('three')=123
  85   }
  86   set L
  87 } {three}
  88
  89 # 2022-11-25 dbsqlfuzz crash-3a548de406a50e896c1bf7142692d35d339d697f
  90 # Disable the push-down optimization for compound subqueries if any
  91 # arm of the compound has an incompatible affinity.
  92 #
  93 reset_db
  94 do_execsql_test 3.1 {
  95   CREATE TABLE t0(c0 INT);
  96   INSERT INTO t0 VALUES(0);
  97   CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
  98   INSERT INTO t1_a VALUES(1,'one');
  99   CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);
 100   INSERT INTO t1_b VALUES(2,'two');
 101   CREATE VIEW v0 AS SELECT CAST(t0.c0 AS INTEGER) AS c0 FROM t0;
 102   CREATE VIEW v1(a,b) AS SELECT a, b FROM t1_a UNION ALL SELECT c, 0 FROM t1_b;
 103   SELECT v1.a, quote(v1.b), t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0,v1;
 104 } {
 105   1 'one' 0
 106   2 0     0
 107 }
 108 do_execsql_test 3.2 {
 109   SELECT a, quote(b), cd FROM (
 110     SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1
 111   ) WHERE a=2 AND b='0' AND cd=0;
 112 } {}
 113 do_execsql_test 3.3 {
 114   SELECT a, quote(b), cd FROM (
 115     SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1
 116   ) WHERE a=1 AND b='one' AND cd=0;
 117 } {1 'one' 0}
 118 do_execsql_test 3.4 {
 119   SELECT a, quote(b), cd FROM (
 120     SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1
 121   ) WHERE a=2 AND b=0 AND cd=0;
 122 } {
 123   2 0     0
 124 }
 125
 126 # 2023-02-22 https://sqlite.org/forum/forumpost/bcc4375032
 127 # Performance regression caused by check-in [1ad41840c5e0fa70] from 2022-11-25.
 128 # That check-in added a new restriction on push-down.  The new restriction is
 129 # no longer necessary after check-in [27655c9353620aa5] from 2022-12-14.
 130 #
 131 do_execsql_test 3.5 {
 132   DROP TABLE IF EXISTS t1;
 133   CREATE TABLE t1(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
 134   INSERT INTO t1(a,b,c) VALUES
 135     (1,100,'abc'),
 136     (2,200,'def'),
 137     (3,300,'abc');
 138   DROP TABLE IF EXISTS t2;
 139   CREATE TABLE t2(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
 140   INSERT INTO t2(a,b,c) VALUES
 141     (1,110,'efg'),
 142     (2,200,'hij'),
 143     (3,330,'klm');
 144   CREATE VIEW v3 AS
 145     SELECT a, b, c FROM t1
 146     UNION ALL
 147     SELECT a, b, 'xyz' FROM t2;
 148   SELECT * FROM v3 WHERE a=2 AND b=200;
 149 } {2 200 def 2 200 xyz}
 150 do_eqp_test 3.6 {
 151   SELECT * FROM v3 WHERE a=2 AND b=200;
 152 } {
 153   QUERY PLAN
 154   |--CO-ROUTINE v3
 155   |  `--COMPOUND QUERY
 156   |     |--LEFT-MOST SUBQUERY
 157   |     |  `--SEARCH t1 USING PRIMARY KEY (a=? AND b=?)
 158   |     `--UNION ALL
 159   |        `--SEARCH t2 USING PRIMARY KEY (a=? AND b=?)
 160   `--SCAN v3
 161 }
 162 #                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 163 # We want both arms of the compound subquery to use the
 164 # primary key.
 165
 166 # The following is a test of the count-of-view optimization.  This does
 167 # not have anything to do with push-down.  It is here because this is a
 168 # convenient place to put the test.
 169 #
 170 do_execsql_test 3.7 {
 171   SELECT count(*) FROM v3;
 172 } 6
 173 do_eqp_test 3.8 {
 174   SELECT count(*) FROM v3;
 175 } {
 176   QUERY PLAN
 177   |--SCAN CONSTANT ROW
 178   |--SCALAR SUBQUERY xxxxxx
 179   |  `--SCAN t1
 180   `--SCALAR SUBQUERY xxxxxx
 181      `--SCAN t2
 182 }
 183 # ^^^^^^^^^^^^^^^^^^^^
 184 # The query should be converted into:
 185 #   SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)
 186
 187 # 2023-05-09 https://sqlite.org/forum/forumpost/a7d4be7fb6
 188 # Restriction (9) on the push-down optimization.
 189 #
 190 reset_db
 191 db null -
 192 do_execsql_test 4.1 {
 193   CREATE TABLE t1(a INT);
 194   CREATE TABLE t2(b INT);
 195   CREATE TABLE t3(c INT);
 196   INSERT INTO t3(c) VALUES(3);
 197   CREATE TABLE t4(d INT);
 198   CREATE TABLE t5(e INT);
 199   INSERT INTO t5(e) VALUES(5);
 200   CREATE VIEW v6(f,g) AS SELECT d, e FROM t4 RIGHT JOIN t5 ON true;
 201   SELECT * FROM  t1 JOIN t2 ON false RIGHT JOIN t3 ON true CROSS JOIN v6;
 202 } {- - 3 - 5}
 203 do_execsql_test 4.2 {
 204   SELECT * FROM v6 JOIN t5 ON false RIGHT JOIN t3 ON true;
 205 } {- - - 3}
 206 do_execsql_test 4.3 {
 207   SELECT * FROM t1 JOIN t2 ON false JOIN v6 ON true RIGHT JOIN t3 ON true;
 208 } {- - - - 3}
 209
 210 # 2023-05-15 https://sqlite.org/forum/forumpost/f3f546025a
 211 # This is restriction (6) on sqlite3ExprIsSingleTableConstraint().
 212 # That restriction (now) used to implement restriction (9) on push-down.
 213 # It is used for other things too, so it is not purely a push-down
 214 # restriction.  But it seems convenient to put it here.
 215 #
 216 reset_db
 217 db null -
 218 do_execsql_test 5.0 {
 219   CREATE TABLE t1(a INT);  INSERT INTO t1 VALUES(1);
 220   CREATE TABLE t2(b INT);  INSERT INTO t2 VALUES(2);
 221   CREATE TABLE t3(c INT);  INSERT INTO t3 VALUES(3);
 222   CREATE TABLE t4(d INT);  INSERT INTO t4 VALUES(4);
 223   CREATE TABLE t5(e INT);  INSERT INTO t5 VALUES(5);
 224   SELECT *
 225     FROM t1 JOIN t2 ON null RIGHT JOIN t3 ON true
 226           LEFT JOIN (t4 JOIN t5 ON d+1=e) ON d=4
 227    WHERE e>0;
 228 } {- - 3 4 5}
 229
 230 finish_test