| 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 |
#------------------------------------------------------------------------- |
| 329 |
reset_db |
| 330 |
do_execsql_test 7.0 { |
| 331 |
CREATE TABLE t0_1(a INT , b INT, c INT); |
| 332 |
CREATE TABLE t0_2(a INT , b INT, c INT); |
| 333 |
|
| 334 |
INSERT INTO t0_1 (a, b, c) VALUES (1, 0, 1); |
| 335 |
INSERT INTO t0_2 (a, b, c) VALUES (1, 0, 1); |
| 336 |
|
| 337 |
CREATE TABLE empty1(x); |
| 338 |
CREATE TABLE empty2(y); |
| 339 |
} |
| 340 |
|
| 341 |
do_execsql_test 7.1 { |
| 342 |
SELECT t0_2.c |
| 343 |
FROM (SELECT '0000' AS c0 FROM empty2 RIGHT JOIN t0_1 ON 1) AS v0 |
| 344 |
LEFT JOIN empty1 ON v0.c0, t0_2 |
| 345 |
RIGHT JOIN ( |
| 346 |
SELECT 5678 AS col0 FROM (SELECT 0) |
| 347 |
) AS sub1 ON 1; |
| 348 |
} {1} |
| 349 |
|
| 350 |
do_execsql_test 7.2 { |
| 351 |
SELECT t0_2.c |
| 352 |
FROM (SELECT '0000' AS c0 FROM empty2 RIGHT JOIN t0_1 ON 1) AS v0 |
| 353 |
LEFT JOIN empty1 ON v0.c0, t0_2 |
| 354 |
RIGHT JOIN ( |
| 355 |
SELECT 5678 AS col0 FROM (SELECT 0) |
| 356 |
) AS sub1 ON 1 WHERE +t0_2.c; |
| 357 |
} {1} |
| 358 |
|
| 359 |
finish_test |