test/pushdown.test

   1 # 2017-04-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 # Test cases for the push-down optimizations.
  13 #
  14 #
  15 # There are two different meanings for "push-down optimization".
  16 #
  17 #   (1)  "MySQL push-down" means that WHERE clause terms that can be
  18 #        evaluated using only the index and without reference to the
  19 #        table are run first, so that if they are false, unnecessary table
  20 #        seeks are avoided.  See https://sqlite.org/src/info/d7bb79ed3a40419d
  21 #        from 2017-04-29.
  22 #
  23 #   (2)  "WHERE-clause pushdown" means to push WHERE clause terms in
  24 #        outer queries down into subqueries.  See
  25 #        https://sqlite.org/src/info/6df18e949d367629 from 2015-06-02.
  26 #
  27 # This module started out as tests for MySQL push-down only.  But because
  28 # of naming ambiguity, it has picked up test cases for WHERE-clause push-down
  29 # over the years.
  30 #
  31
  32 set testdir [file dirname $argv0]
  33 source $testdir/tester.tcl
  34 set testprefix pushdown
  35
  36 do_execsql_test 1.0 {
  37   CREATE TABLE t1(a, b, c);
  38   INSERT INTO t1 VALUES(1, 'b1', 'c1');
  39   INSERT INTO t1 VALUES(2, 'b2', 'c2');
  40   INSERT INTO t1 VALUES(3, 'b3', 'c3');
  41   INSERT INTO t1 VALUES(4, 'b4', 'c4');
  42   CREATE INDEX i1 ON t1(a, c);
  43 }
  44
  45 proc f {val} {
  46   lappend ::L $val
  47   return 0
  48 }
  49 db func f f
  50
  51 do_test 1.1 {
  52   set L [list]
  53   execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
  54   set L
  55 } {c2}
  56
  57 do_test 1.2 {
  58   set L [list]
  59   execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
  60   set L
  61 } {c3}
  62
  63 do_execsql_test 1.3 {
  64   DROP INDEX i1;
  65   CREATE INDEX i1 ON t1(a, b);
  66 }
  67 do_test 1.4 {
  68   set L [list]
  69   execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
  70   set L
  71 } {b2}
  72
  73 do_test 1.5 {
  74   set L [list]
  75   execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
  76   set L
  77 } {b3}
  78
  79 #-----------------------------------------------
  80
  81 do_execsql_test 2.0 {
  82   CREATE TABLE u1(a, b, c);
  83   CREATE TABLE u2(x, y, z);
  84
  85   INSERT INTO u1 VALUES('a1', 'b1', 'c1');
  86   INSERT INTO u2 VALUES('a1', 'b1', 'c1');
  87 }
  88
  89 do_test 2.1 {
  90   set L [list]
  91   execsql {
  92     SELECT * FROM u1 WHERE f('one')=123 AND 123=(
  93       SELECT x FROM u2 WHERE x=a AND f('two')
  94     )
  95   }
  96   set L
  97 } {one}
  98
  99 do_test 2.2 {
 100   set L [list]
 101   execsql {
 102     SELECT * FROM u1 WHERE 123=(
 103       SELECT x FROM u2 WHERE x=a AND f('two')
 104     ) AND f('three')=123
 105   }
 106   set L
 107 } {three}
 108
 109 # 2022-11-25 dbsqlfuzz crash-3a548de406a50e896c1bf7142692d35d339d697f
 110 # Disable the WHERE-clause push-down optimization for compound subqueries
 111 # if any arm of the compound has an incompatible affinity.
 112 #
 113 reset_db
 114 do_execsql_test 3.1 {
 115   CREATE TABLE t0(c0 INT);
 116   INSERT INTO t0 VALUES(0);
 117   CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
 118   INSERT INTO t1_a VALUES(1,'one');
 119   CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);
 120   INSERT INTO t1_b VALUES(2,'two');
 121   CREATE VIEW v0 AS SELECT CAST(t0.c0 AS INTEGER) AS c0 FROM t0;
 122   CREATE VIEW v1(a,b) AS SELECT a, b FROM t1_a UNION ALL SELECT c, 0 FROM t1_b;
 123   SELECT v1.a, quote(v1.b), t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0,v1;
 124 } {
 125   1 'one' 0
 126   2 0     0
 127 }
 128 do_execsql_test 3.2 {
 129   SELECT a, quote(b), cd FROM (
 130     SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1
 131   ) WHERE a=2 AND b='0' AND cd=0;
 132 } {}
 133 do_execsql_test 3.3 {
 134   SELECT a, quote(b), cd FROM (
 135     SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1
 136   ) WHERE a=1 AND b='one' AND cd=0;
 137 } {1 'one' 0}
 138 do_execsql_test 3.4 {
 139   SELECT a, quote(b), cd FROM (
 140     SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1
 141   ) WHERE a=2 AND b=0 AND cd=0;
 142 } {
 143   2 0     0
 144 }
 145
 146 # 2023-02-22 https://sqlite.org/forum/forumpost/bcc4375032
 147 # Performance regression caused by check-in [1ad41840c5e0fa70] from 2022-11-25.
 148 # That check-in added a new restriction on push-down.  The new restriction is
 149 # no longer necessary after check-in [27655c9353620aa5] from 2022-12-14.
 150 #
 151 do_execsql_test 3.5 {
 152   DROP TABLE IF EXISTS t1;
 153   CREATE TABLE t1(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
 154   INSERT INTO t1(a,b,c) VALUES
 155     (1,100,'abc'),
 156     (2,200,'def'),
 157     (3,300,'abc');
 158   DROP TABLE IF EXISTS t2;
 159   CREATE TABLE t2(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
 160   INSERT INTO t2(a,b,c) VALUES
 161     (1,110,'efg'),
 162     (2,200,'hij'),
 163     (3,330,'klm');
 164   CREATE VIEW v3 AS
 165     SELECT a, b, c FROM t1
 166     UNION ALL
 167     SELECT a, b, 'xyz' FROM t2;
 168   SELECT * FROM v3 WHERE a=2 AND b=200;
 169 } {2 200 def 2 200 xyz}
 170 do_eqp_test 3.6 {
 171   SELECT * FROM v3 WHERE a=2 AND b=200;
 172 } {
 173   QUERY PLAN
 174   |--CO-ROUTINE v3
 175   |  `--COMPOUND QUERY
 176   |     |--LEFT-MOST SUBQUERY
 177   |     |  `--SEARCH t1 USING PRIMARY KEY (a=? AND b=?)
 178   |     `--UNION ALL
 179   |        `--SEARCH t2 USING PRIMARY KEY (a=? AND b=?)
 180   `--SCAN v3
 181 }
 182 #                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 183 # We want both arms of the compound subquery to use the
 184 # primary key.
 185
 186 # The following is a test of the count-of-view optimization.  This does
 187 # not have anything to do with push-down.  It is here because this is a
 188 # convenient place to put the test.
 189 #
 190 do_execsql_test 3.7 {
 191   SELECT count(*) FROM v3;
 192 } 6
 193 do_eqp_test 3.8 {
 194   SELECT count(*) FROM v3;
 195 } {
 196   QUERY PLAN
 197   |--SCAN CONSTANT ROW
 198   |--SCALAR SUBQUERY xxxxxx
 199   |  `--SCAN t1
 200   `--SCALAR SUBQUERY xxxxxx
 201      `--SCAN t2
 202 }
 203 # ^^^^^^^^^^^^^^^^^^^^
 204 # The query should be converted into:
 205 #   SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)
 206
 207 # 2023-05-09 https://sqlite.org/forum/forumpost/a7d4be7fb6
 208 # Restriction (9) on the WHERE-clause push-down optimization.
 209 #
 210 reset_db
 211 db null -
 212 do_execsql_test 4.1 {
 213   CREATE TABLE t1(a INT);
 214   CREATE TABLE t2(b INT);
 215   CREATE TABLE t3(c INT);
 216   INSERT INTO t3(c) VALUES(3);
 217   CREATE TABLE t4(d INT);
 218   CREATE TABLE t5(e INT);
 219   INSERT INTO t5(e) VALUES(5);
 220   CREATE VIEW v6(f,g) AS SELECT d, e FROM t4 RIGHT JOIN t5 ON true;
 221   SELECT * FROM  t1 JOIN t2 ON false RIGHT JOIN t3 ON true CROSS JOIN v6;
 222 } {- - 3 - 5}
 223 do_execsql_test 4.2 {
 224   SELECT * FROM v6 JOIN t5 ON false RIGHT JOIN t3 ON true;
 225 } {- - - 3}
 226 do_execsql_test 4.3 {
 227   SELECT * FROM t1 JOIN t2 ON false JOIN v6 ON true RIGHT JOIN t3 ON true;
 228 } {- - - - 3}
 229
 230 # 2023-05-15 https://sqlite.org/forum/forumpost/f3f546025a
 231 # This is restriction (6) on sqlite3ExprIsSingleTableConstraint().
 232 # That restriction (now) used to implement restriction (9) on push-down.
 233 # It is used for other things too, so it is not purely a push-down
 234 # restriction.  But it seems convenient to put it here.
 235 #
 236 reset_db
 237 db null -
 238 do_execsql_test 5.0 {
 239   CREATE TABLE t1(a INT);  INSERT INTO t1 VALUES(1);
 240   CREATE TABLE t2(b INT);  INSERT INTO t2 VALUES(2);
 241   CREATE TABLE t3(c INT);  INSERT INTO t3 VALUES(3);
 242   CREATE TABLE t4(d INT);  INSERT INTO t4 VALUES(4);
 243   CREATE TABLE t5(e INT);  INSERT INTO t5 VALUES(5);
 244   SELECT *
 245     FROM t1 JOIN t2 ON null RIGHT JOIN t3 ON true
 246           LEFT JOIN (t4 JOIN t5 ON d+1=e) ON d=4
 247    WHERE e>0;
 248 } {- - 3 4 5}
 249
 250
 251 # 2024-04-05
 252 # Allow push-down of operators of the form "expr IN table".
 253 #
 254 reset_db
 255 do_execsql_test 6.0 {
 256   CREATE TABLE t01(w,x,y,z);
 257   CREATE TABLE t02(w,x,y,z);
 258   CREATE VIEW t0(w,x,y,z) AS
 259     SELECT w,x,y,z FROM t01 UNION ALL SELECT w,x,y,z FROM t02;
 260   CREATE INDEX t01x ON t01(w,x,y);
 261   CREATE INDEX t02x ON t02(w,x,y);
 262   CREATE VIEW v1(k) AS VALUES(77),(88),(99);
 263   CREATE TABLE k1(k);
 264   INSERT INTO k1 SELECT * FROM v1;
 265 }
 266 do_eqp_test 6.1 {
 267   WITH k(n) AS (VALUES(77),(88),(99))
 268   SELECT max(z) FROM t0 WHERE w=123 AND x IN k AND y BETWEEN 44 AND 55;
 269 } {
 270   QUERY PLAN
 271   |--CO-ROUTINE t0
 272   |  `--COMPOUND QUERY
 273   |     |--LEFT-MOST SUBQUERY
 274   |     |  |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
 275   |     |  `--LIST SUBQUERY xxxxxx
 276   |     |     |--MATERIALIZE k
 277   |     |     |  `--SCAN 3 CONSTANT ROWS
 278   |     |     |--SCAN k
 279   |     |     `--CREATE BLOOM FILTER
 280   |     `--UNION ALL
 281   |        |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
 282   |        `--REUSE LIST SUBQUERY xxxxxx
 283   |--SEARCH t0
 284   `--REUSE LIST SUBQUERY xxxxxx
 285 }
 286 # ^^^^--- The key feature above is that the SEARCH for each subquery
 287 # uses all three fields of the index w, x, and y.  Prior to the push-down
 288 # of "expr IN table", only the w term of the index would be used.  Similar
 289 # for the following tests:
 290 #
 291 do_eqp_test 6.2 {
 292   SELECT max(z) FROM t0 WHERE w=123 AND x IN v1 AND y BETWEEN 44 AND 55;
 293 } {
 294   QUERY PLAN
 295   |--CO-ROUTINE t0
 296   |  `--COMPOUND QUERY
 297   |     |--LEFT-MOST SUBQUERY
 298   |     |  |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
 299   |     |  `--LIST SUBQUERY xxxxxx
 300   |     |     |--CO-ROUTINE v1
 301   |     |     |  `--SCAN 3 CONSTANT ROWS
 302   |     |     |--SCAN v1
 303   |     |     `--CREATE BLOOM FILTER
 304   |     `--UNION ALL
 305   |        |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
 306   |        `--REUSE LIST SUBQUERY xxxxxx
 307   |--SEARCH t0
 308   `--REUSE LIST SUBQUERY xxxxxx
 309 }
 310 do_eqp_test 6.3 {
 311   SELECT max(z) FROM t0 WHERE w=123 AND x IN k1 AND y BETWEEN 44 AND 55;
 312 } {
 313   QUERY PLAN
 314   |--CO-ROUTINE t0
 315   |  `--COMPOUND QUERY
 316   |     |--LEFT-MOST SUBQUERY
 317   |     |  |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
 318   |     |  `--LIST SUBQUERY xxxxxx
 319   |     |     |--SCAN k1
 320   |     |     `--CREATE BLOOM FILTER
 321   |     `--UNION ALL
 322   |        |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
 323   |        `--REUSE LIST SUBQUERY xxxxxx
 324   |--SEARCH t0
 325   `--REUSE LIST SUBQUERY xxxxxx
 326 }
 327
 328 finish_test