| 1 |
# 2008 September 1 |
| 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 |
# $Id: in4.test,v 1.4 2009/06/05 17:09:12 drh Exp $ |
| 13 |
|
| 14 |
set testdir [file dirname $argv0] |
| 15 |
source $testdir/tester.tcl |
| 16 |
set testprefix in4 |
| 17 |
|
| 18 |
do_test in4-1.1 { |
| 19 |
execsql { |
| 20 |
CREATE TABLE t1(a, b); |
| 21 |
CREATE INDEX i1 ON t1(a); |
| 22 |
} |
| 23 |
} {} |
| 24 |
do_test in4-1.2 { |
| 25 |
execsql { |
| 26 |
SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); |
| 27 |
} |
| 28 |
} {} |
| 29 |
do_test in4-1.3 { |
| 30 |
execsql { |
| 31 |
INSERT INTO t1 VALUES('aaa', 1); |
| 32 |
INSERT INTO t1 VALUES('ddd', 2); |
| 33 |
INSERT INTO t1 VALUES('ccc', 3); |
| 34 |
INSERT INTO t1 VALUES('eee', 4); |
| 35 |
SELECT b FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); |
| 36 |
} |
| 37 |
} {1 3} |
| 38 |
do_test in4-1.4 { |
| 39 |
execsql { |
| 40 |
SELECT a FROM t1 WHERE rowid IN (1, 3); |
| 41 |
} |
| 42 |
} {aaa ccc} |
| 43 |
do_test in4-1.5 { |
| 44 |
execsql { |
| 45 |
SELECT a FROM t1 WHERE rowid IN (); |
| 46 |
} |
| 47 |
} {} |
| 48 |
do_test in4-1.6 { |
| 49 |
execsql { |
| 50 |
SELECT a FROM t1 WHERE a IN ('ddd'); |
| 51 |
} |
| 52 |
} {ddd} |
| 53 |
|
| 54 |
do_test in4-2.1 { |
| 55 |
execsql { |
| 56 |
CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); |
| 57 |
INSERT INTO t2 VALUES(-1, '-one'); |
| 58 |
INSERT INTO t2 VALUES(0, 'zero'); |
| 59 |
INSERT INTO t2 VALUES(1, 'one'); |
| 60 |
INSERT INTO t2 VALUES(2, 'two'); |
| 61 |
INSERT INTO t2 VALUES(3, 'three'); |
| 62 |
} |
| 63 |
} {} |
| 64 |
|
| 65 |
do_test in4-2.2 { |
| 66 |
execsql { SELECT b FROM t2 WHERE a IN (0, 2) } |
| 67 |
} {zero two} |
| 68 |
|
| 69 |
do_test in4-2.3 { |
| 70 |
execsql { SELECT b FROM t2 WHERE a IN (2, 0) } |
| 71 |
} {zero two} |
| 72 |
|
| 73 |
do_test in4-2.4 { |
| 74 |
execsql { SELECT b FROM t2 WHERE a IN (2, -1) } |
| 75 |
} {-one two} |
| 76 |
|
| 77 |
do_test in4-2.5 { |
| 78 |
execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) } |
| 79 |
} {three} |
| 80 |
|
| 81 |
do_test in4-2.6 { |
| 82 |
execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) } |
| 83 |
} {one} |
| 84 |
|
| 85 |
do_test in4-2.7 { |
| 86 |
execsql { SELECT b FROM t2 WHERE a IN ('1', '2') } |
| 87 |
} {one two} |
| 88 |
|
| 89 |
do_test in4-2.8 { |
| 90 |
execsql { SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') } |
| 91 |
} {two} |
| 92 |
|
| 93 |
# The following block of tests test expressions of the form: |
| 94 |
# |
| 95 |
# <expr> IN () |
| 96 |
# |
| 97 |
# i.e. IN expressions with a literal empty set. |
| 98 |
# |
| 99 |
# This has led to crashes on more than one occasion. Test case in4-3.2 |
| 100 |
# was added in reponse to a bug reported on the mailing list on 11/7/2008. |
| 101 |
# See also tickets #3602 and #185. |
| 102 |
# |
| 103 |
do_test in4-3.1 { |
| 104 |
execsql { |
| 105 |
DROP TABLE IF EXISTS t1; |
| 106 |
DROP TABLE IF EXISTS t2; |
| 107 |
CREATE TABLE t1(x, id); |
| 108 |
CREATE TABLE t2(x, id); |
| 109 |
INSERT INTO t1 VALUES(NULL, NULL); |
| 110 |
INSERT INTO t1 VALUES(0, NULL); |
| 111 |
INSERT INTO t1 VALUES(1, 3); |
| 112 |
INSERT INTO t1 VALUES(2, 4); |
| 113 |
INSERT INTO t1 VALUES(3, 5); |
| 114 |
INSERT INTO t1 VALUES(4, 6); |
| 115 |
INSERT INTO t2 VALUES(0, NULL); |
| 116 |
INSERT INTO t2 VALUES(4, 1); |
| 117 |
INSERT INTO t2 VALUES(NULL, 1); |
| 118 |
INSERT INTO t2 VALUES(NULL, NULL); |
| 119 |
} |
| 120 |
} {} |
| 121 |
do_test in4-3.2 { |
| 122 |
execsql { |
| 123 |
SELECT x FROM t1 WHERE id IN () AND x IN (SELECT x FROM t2 WHERE id=1) |
| 124 |
} |
| 125 |
} {} |
| 126 |
do_test in4-3.3 { |
| 127 |
execsql { |
| 128 |
CREATE TABLE t3(x, y, z); |
| 129 |
CREATE INDEX t3i1 ON t3(x, y); |
| 130 |
INSERT INTO t3 VALUES(1, 1, 1); |
| 131 |
INSERT INTO t3 VALUES(10, 10, 10); |
| 132 |
} |
| 133 |
execsql { SELECT * FROM t3 WHERE x IN () } |
| 134 |
} {} |
| 135 |
do_test in4-3.4 { |
| 136 |
execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () } |
| 137 |
} {} |
| 138 |
do_test in4-3.5 { |
| 139 |
execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 } |
| 140 |
} {} |
| 141 |
do_test in4-3.6 { |
| 142 |
execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 } |
| 143 |
} {10 10 10} |
| 144 |
do_test in4-3.7 { |
| 145 |
execsql { SELECT * FROM t3 WHERE y IN () } |
| 146 |
} {} |
| 147 |
do_test in4-3.8 { |
| 148 |
execsql { SELECT x IN() AS a FROM t3 WHERE a } |
| 149 |
} {} |
| 150 |
do_test in4-3.9 { |
| 151 |
execsql { SELECT x IN() AS a FROM t3 WHERE NOT a } |
| 152 |
} {0 0} |
| 153 |
do_test in4-3.10 { |
| 154 |
execsql { SELECT * FROM t3 WHERE oid IN () } |
| 155 |
} {} |
| 156 |
do_test in4-3.11 { |
| 157 |
execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()} |
| 158 |
} {1 1 1} |
| 159 |
do_test in4-3.12 { |
| 160 |
execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()} |
| 161 |
} {} |
| 162 |
|
| 163 |
# Tests for "... IN (?)" and "... NOT IN (?)". In other words, tests |
| 164 |
# for when the RHS of IN is a single expression. This should work the |
| 165 |
# same as the == and <> operators. |
| 166 |
# |
| 167 |
do_execsql_test in4-3.21 { |
| 168 |
SELECT * FROM t3 WHERE x=10 AND y IN (10); |
| 169 |
} {10 10 10} |
| 170 |
do_execsql_test in4-3.22 { |
| 171 |
SELECT * FROM t3 WHERE x IN (10) AND y=10; |
| 172 |
} {10 10 10} |
| 173 |
do_execsql_test in4-3.23 { |
| 174 |
SELECT * FROM t3 WHERE x IN (10) AND y IN (10); |
| 175 |
} {10 10 10} |
| 176 |
do_execsql_test in4-3.24 { |
| 177 |
SELECT * FROM t3 WHERE x=1 AND y NOT IN (10); |
| 178 |
} {1 1 1} |
| 179 |
do_execsql_test in4-3.25 { |
| 180 |
SELECT * FROM t3 WHERE x NOT IN (10) AND y=1; |
| 181 |
} {1 1 1} |
| 182 |
do_execsql_test in4-3.26 { |
| 183 |
SELECT * FROM t3 WHERE x NOT IN (10) AND y NOT IN (10); |
| 184 |
} {1 1 1} |
| 185 |
|
| 186 |
# The query planner recognizes that "x IN (?)" only generates a |
| 187 |
# single match and can use this information to optimize-out ORDER BY |
| 188 |
# clauses. |
| 189 |
# |
| 190 |
do_execsql_test in4-3.31 { |
| 191 |
DROP INDEX t3i1; |
| 192 |
CREATE UNIQUE INDEX t3xy ON t3(x,y); |
| 193 |
|
| 194 |
SELECT *, '|' FROM t3 A, t3 B |
| 195 |
WHERE A.x=10 AND A.y IN (10) |
| 196 |
AND B.x=1 AND B.y IN (1); |
| 197 |
} {10 10 10 1 1 1 |} |
| 198 |
do_execsql_test in4-3.32 { |
| 199 |
EXPLAIN QUERY PLAN |
| 200 |
SELECT *, '|' FROM t3 A, t3 B |
| 201 |
WHERE A.x=10 AND A.y IN (10) |
| 202 |
AND B.x=1 AND B.y IN (1); |
| 203 |
} {~/B-TREE/} ;# No separate sorting pass |
| 204 |
do_execsql_test in4-3.33 { |
| 205 |
SELECT *, '|' FROM t3 A, t3 B |
| 206 |
WHERE A.x IN (10) AND A.y=10 |
| 207 |
AND B.x IN (1) AND B.y=1; |
| 208 |
} {10 10 10 1 1 1 |} |
| 209 |
do_execsql_test in4-3.34 { |
| 210 |
EXPLAIN QUERY PLAN |
| 211 |
SELECT *, '|' FROM t3 A, t3 B |
| 212 |
WHERE A.x IN (10) AND A.y=10 |
| 213 |
AND B.x IN (1) AND B.y=1; |
| 214 |
} {~/B-TREE/} ;# No separate sorting pass |
| 215 |
|
| 216 |
# An expression of the form "x IN (?,?)" creates an ephemeral table to |
| 217 |
# hold the list of values on the RHS. But "x IN (?)" does not create |
| 218 |
# an ephemeral table. |
| 219 |
# |
| 220 |
do_execsql_test in4-3.41 { |
| 221 |
SELECT * FROM t3 WHERE x IN (10,11); |
| 222 |
} {10 10 10} |
| 223 |
do_execsql_test in4-3.42 { |
| 224 |
EXPLAIN |
| 225 |
SELECT * FROM t3 WHERE x IN (10,11); |
| 226 |
} {/OpenEphemeral/} |
| 227 |
do_execsql_test in4-3.43 { |
| 228 |
SELECT * FROM t3 WHERE x IN (10); |
| 229 |
} {10 10 10} |
| 230 |
|
| 231 |
# This test would verify that the "X IN (Y)" -> "X==Y" optimization |
| 232 |
# was working. But we have now taken that optimization out. |
| 233 |
#do_execsql_test in4-3.44 { |
| 234 |
# EXPLAIN |
| 235 |
# SELECT * FROM t3 WHERE x IN (10); |
| 236 |
#} {~/OpenEphemeral/} |
| 237 |
do_execsql_test in4-3.45 { |
| 238 |
SELECT * FROM t3 WHERE x NOT IN (10,11,99999); |
| 239 |
} {1 1 1} |
| 240 |
do_execsql_test in4-3.46 { |
| 241 |
EXPLAIN |
| 242 |
SELECT * FROM t3 WHERE x NOT IN (10,11,99999); |
| 243 |
} {/OpenEphemeral/} |
| 244 |
do_execsql_test in4-3.47 { |
| 245 |
SELECT * FROM t3 WHERE x NOT IN (10); |
| 246 |
} {1 1 1} |
| 247 |
do_execsql_test in4-3.48 { |
| 248 |
EXPLAIN |
| 249 |
SELECT * FROM t3 WHERE x NOT IN (10); |
| 250 |
} {~/OpenEphemeral/} |
| 251 |
|
| 252 |
# Make sure that when "x IN (?)" is converted into "x==?" that collating |
| 253 |
# sequence and affinity computations do not get messed up. |
| 254 |
# |
| 255 |
do_execsql_test in4-4.1 { |
| 256 |
CREATE TABLE t4a(a TEXT, b TEXT COLLATE nocase, c); |
| 257 |
INSERT INTO t4a VALUES('ABC','abc',1); |
| 258 |
INSERT INTO t4a VALUES('def','xyz',2); |
| 259 |
INSERT INTO t4a VALUES('ghi','ghi',3); |
| 260 |
SELECT c FROM t4a WHERE a=b ORDER BY c; |
| 261 |
} {3} |
| 262 |
do_execsql_test in4-4.2 { |
| 263 |
SELECT c FROM t4a WHERE b=a ORDER BY c; |
| 264 |
} {1 3} |
| 265 |
do_execsql_test in4-4.3 { |
| 266 |
SELECT c FROM t4a WHERE (a||'')=b ORDER BY c; |
| 267 |
} {1 3} |
| 268 |
do_execsql_test in4-4.4 { |
| 269 |
SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c; |
| 270 |
} {3} |
| 271 |
do_execsql_test in4-4.5 { |
| 272 |
SELECT c FROM t4a WHERE a IN (b) ORDER BY c; |
| 273 |
} {3} |
| 274 |
do_execsql_test in4-4.6 { |
| 275 |
SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c; |
| 276 |
} {3} |
| 277 |
|
| 278 |
|
| 279 |
do_execsql_test in4-4.11 { |
| 280 |
CREATE TABLE t4b(a TEXT, b NUMERIC, c); |
| 281 |
INSERT INTO t4b VALUES('1.0',1,4); |
| 282 |
SELECT c FROM t4b WHERE a=b; |
| 283 |
} {4} |
| 284 |
do_execsql_test in4-4.12 { |
| 285 |
SELECT c FROM t4b WHERE b=a; |
| 286 |
} {4} |
| 287 |
do_execsql_test in4-4.13 { |
| 288 |
SELECT c FROM t4b WHERE +a=b; |
| 289 |
} {4} |
| 290 |
do_execsql_test in4-4.14 { |
| 291 |
SELECT c FROM t4b WHERE a=+b; |
| 292 |
} {} |
| 293 |
do_execsql_test in4-4.15 { |
| 294 |
SELECT c FROM t4b WHERE +b=a; |
| 295 |
} {} |
| 296 |
do_execsql_test in4-4.16 { |
| 297 |
SELECT c FROM t4b WHERE b=+a; |
| 298 |
} {4} |
| 299 |
do_execsql_test in4-4.17 { |
| 300 |
SELECT c FROM t4b WHERE a IN (b); |
| 301 |
} {} |
| 302 |
do_execsql_test in4-4.18 { |
| 303 |
SELECT c FROM t4b WHERE b IN (a); |
| 304 |
} {4} |
| 305 |
do_execsql_test in4-4.19 { |
| 306 |
SELECT c FROM t4b WHERE +b IN (a); |
| 307 |
} {} |
| 308 |
|
| 309 |
do_execsql_test in4-5.1 { |
| 310 |
CREATE TABLE t5(c INTEGER PRIMARY KEY, d TEXT COLLATE nocase); |
| 311 |
INSERT INTO t5 VALUES(17, 'fuzz'); |
| 312 |
SELECT 1 FROM t5 WHERE 'fuzz' IN (d); -- match |
| 313 |
SELECT 2 FROM t5 WHERE 'FUZZ' IN (d); -- no match |
| 314 |
SELECT 3 FROM t5 WHERE d IN ('fuzz'); -- match |
| 315 |
SELECT 4 FROM t5 WHERE d IN ('FUZZ'); -- match |
| 316 |
} {1 3 4} |
| 317 |
|
| 318 |
# An expression of the form "x IN (y)" can be used as "x=y" by the |
| 319 |
# query planner when computing transitive constraints or to run the |
| 320 |
# query using an index on y. |
| 321 |
# |
| 322 |
do_execsql_test in4-6.1 { |
| 323 |
CREATE TABLE t6a(a INTEGER PRIMARY KEY, b); |
| 324 |
INSERT INTO t6a VALUES(1,2),(3,4),(5,6); |
| 325 |
CREATE TABLE t6b(c INTEGER PRIMARY KEY, d); |
| 326 |
INSERT INTO t6b VALUES(4,44),(5,55),(6,66); |
| 327 |
|
| 328 |
SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c); |
| 329 |
} {3 4 4 44} |
| 330 |
do_execsql_test in4-6.1-eqp { |
| 331 |
EXPLAIN QUERY PLAN |
| 332 |
SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c); |
| 333 |
} {~/SCAN t6a/} |
| 334 |
do_execsql_test in4-6.2 { |
| 335 |
SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b); |
| 336 |
} {3 4 4 44} |
| 337 |
do_execsql_test in4-6.2-eqp { |
| 338 |
EXPLAIN QUERY PLAN |
| 339 |
SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b); |
| 340 |
} {~/SCAN/} |
| 341 |
|
| 342 |
reset_db |
| 343 |
do_execsql_test 7.0 { |
| 344 |
CREATE TABLE t1(a, b, c); |
| 345 |
CREATE TABLE t2(d, e); |
| 346 |
CREATE INDEX t1bc ON t1(c, b); |
| 347 |
INSERT INTO t2(e) VALUES(1); |
| 348 |
INSERT INTO t1 VALUES(NULL, NULL, NULL); |
| 349 |
} |
| 350 |
|
| 351 |
do_execsql_test 7.1 { |
| 352 |
SELECT * FROM t2 LEFT JOIN t1 ON c = d AND b IN (10,10,10); |
| 353 |
} {{} 1 {} {} {}} |
| 354 |
|
| 355 |
ifcapable rtree { |
| 356 |
reset_db |
| 357 |
do_execsql_test 7.2 { |
| 358 |
CREATE VIRTUAL TABLE t1 USING rtree(a, b, c); |
| 359 |
CREATE TABLE t2(d INTEGER, e INT); |
| 360 |
INSERT INTO t2(e) VALUES(1); |
| 361 |
} |
| 362 |
|
| 363 |
do_execsql_test 7.3 { |
| 364 |
SELECT * FROM t2 LEFT JOIN t1 ON c IN (d) AND b IN (10,10,10); |
| 365 |
} {{} 1 {} {} {}} |
| 366 |
} |
| 367 |
|
| 368 |
#------------------------------------------------------------------------- |
| 369 |
reset_db |
| 370 |
do_execsql_test 8.0 { |
| 371 |
CREATE TABLE t1(x INTEGER PRIMARY KEY, y); |
| 372 |
CREATE UNIQUE INDEX t1y ON t1(y); |
| 373 |
INSERT INTO t1 VALUES(111, 'AAA'),(222, 'BBB'),(333, 'CCC'); |
| 374 |
CREATE TABLE t2(z); |
| 375 |
INSERT INTO t2 VALUES('BBB'),('AAA'); |
| 376 |
ANALYZE sqlite_schema; |
| 377 |
INSERT INTO sqlite_stat1 VALUES('t1', 't1y','100 1'); |
| 378 |
} |
| 379 |
|
| 380 |
db close |
| 381 |
sqlite3 db test.db |
| 382 |
|
| 383 |
do_execsql_test 8.1 { |
| 384 |
SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y; |
| 385 |
} {222 111} |
| 386 |
|
| 387 |
do_execsql_test 8.2 { |
| 388 |
SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y AND +t1.x IN (111, 222); |
| 389 |
} {222 111} |
| 390 |
|
| 391 |
do_execsql_test 8.3 { |
| 392 |
SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y AND t1.x IN (111, 222); |
| 393 |
} {222 111} |
| 394 |
|
| 395 |
# 2021-06-02 forum post https://sqlite.org/forum/forumpost/b4fcb8a598 |
| 396 |
# OP_SeekScan changes from check-in 4a43430fd23f8835 on 2020-09-30 causes |
| 397 |
# performance regression. |
| 398 |
# |
| 399 |
reset_db |
| 400 |
do_execsql_test 9.0 { |
| 401 |
CREATE TABLE node(node_id INTEGER PRIMARY KEY); |
| 402 |
CREATE TABLE edge(node_from INT, node_to INT); |
| 403 |
CREATE TABLE sub_nodes(node_id INTEGER PRIMARY KEY); |
| 404 |
CREATE INDEX edge_from_to ON edge(node_from,node_to); |
| 405 |
CREATE INDEX edge_to_from ON edge(node_to,node_from); |
| 406 |
ANALYZE; |
| 407 |
DELETE FROM sqlite_stat1; |
| 408 |
INSERT INTO sqlite_stat1 VALUES |
| 409 |
('sub_nodes',NULL,'1000000'), |
| 410 |
('edge','edge_to_from','20000000 2 2'), |
| 411 |
('edge','edge_from_to','20000000 2 2'), |
| 412 |
('node',NULL,'10000000'); |
| 413 |
ANALYZE sqlite_schema; |
| 414 |
} {} |
| 415 |
do_eqp_test 9.1 { |
| 416 |
SELECT count(*) FROM edge |
| 417 |
WHERE node_from IN sub_nodes AND node_to IN sub_nodes; |
| 418 |
} { |
| 419 |
QUERY PLAN |
| 420 |
|--SEARCH edge USING COVERING INDEX edge_to_from (node_to=?) |
| 421 |
|--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR |
| 422 |
`--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR |
| 423 |
} |
| 424 |
# ^^^^^ the key to the above is that the index should only use a single |
| 425 |
# term (node_to=?), not two terms (node_to=? AND node_from=). |
| 426 |
|
| 427 |
# dbsqlfuzz case |
| 428 |
# |
| 429 |
reset_db |
| 430 |
do_execsql_test 10.0 { |
| 431 |
CREATE TABLE t1(a,b,c,d,PRIMARY KEY(a,b,c)) WITHOUT ROWID; |
| 432 |
INSERT INTO t1(a,b,c,d) VALUES |
| 433 |
(0,-2,2,3), |
| 434 |
(0,2,3,4), |
| 435 |
(0,5,8,10), |
| 436 |
(1,7,11,13); |
| 437 |
ANALYZE sqlite_schema; |
| 438 |
INSERT INTO sqlite_stat1 VALUES('t1','t1','10 3 2 1'); |
| 439 |
ANALYZE sqlite_schema; |
| 440 |
PRAGMA reverse_unordered_selects(1); |
| 441 |
SELECT d FROM t1 WHERE 0=a AND b IN (-17,-4,-3,1,5,25,7798); |
| 442 |
} {10} |
| 443 |
|
| 444 |
# 2021-06-13 dbsqlfuzz e41762333a4d6e90a49e628f488d0873b2dba4c5 |
| 445 |
# The opcode that precedes OP_SeekScan is usually OP_IdxGT, but can |
| 446 |
# sometimes be OP_IdxGE |
| 447 |
# |
| 448 |
reset_db |
| 449 |
do_execsql_test 11.0 { |
| 450 |
CREATE TABLE t1(a TEXT, b INT, c INT, d INT); |
| 451 |
INSERT INTO t1 VALUES('abc',123,4,5); |
| 452 |
INSERT INTO t1 VALUES('xyz',1,'abcdefxyz',99); |
| 453 |
CREATE INDEX t1abc ON t1(b,b,c); |
| 454 |
ANALYZE sqlite_schema; |
| 455 |
INSERT INTO sqlite_stat1 VALUES('t1','t1abc','10000 5 00 2003 10'); |
| 456 |
ANALYZE sqlite_schema; |
| 457 |
} {} |
| 458 |
do_execsql_test 11.1 { |
| 459 |
SELECT * FROM t1 |
| 460 |
WHERE b IN (345, (SELECT 1 FROM t1 |
| 461 |
WHERE b IN (coalesce(1,random())) |
| 462 |
AND c GLOB 'abc*xyz')) |
| 463 |
AND c BETWEEN 'abc' AND 'xyz'; |
| 464 |
} {xyz 1 abcdefxyz 99} |
| 465 |
do_execsql_test 11.2 { |
| 466 |
EXPLAIN SELECT * FROM t1 |
| 467 |
WHERE b IN (345, (SELECT 1 FROM t1 |
| 468 |
WHERE b IN (coalesce(1,random())) |
| 469 |
AND c GLOB 'abc*xyz')) |
| 470 |
AND c BETWEEN 'abc' AND 'xyz'; |
| 471 |
} {/ SeekScan /} |
| 472 |
|
| 473 |
# 2021-06-25 ticket 6dcbfd11cf666e21 |
| 474 |
# Another problem with OP_SeekScan |
| 475 |
# |
| 476 |
reset_db |
| 477 |
do_execsql_test 12.0 { |
| 478 |
CREATE TABLE t1(a,b,c); |
| 479 |
CREATE INDEX t1abc ON t1(a,b,c); |
| 480 |
CREATE INDEX t1bca on t1(b,c,a); |
| 481 |
INSERT INTO t1 VALUES(56,1119,1115); |
| 482 |
INSERT INTO t1 VALUES(57,1147,1137); |
| 483 |
INSERT INTO t1 VALUES(100,1050,1023); |
| 484 |
INSERT INTO t1 VALUES(101,1050,1023); |
| 485 |
ANALYZE sqlite_schema; |
| 486 |
INSERT INTO sqlite_stat1 VALUES('t1','t1abc','358677 2 2 1'); |
| 487 |
INSERT INTO sqlite_stat1 VALUES('t1','t1bca','358677 4 2 1'); |
| 488 |
ANALYZE sqlite_schema; |
| 489 |
SELECT * FROM t1 NOT INDEXED |
| 490 |
WHERE (b = 1137 AND c IN (97, 98)) |
| 491 |
OR (b = 1119 AND c IN (1115, 1023)); |
| 492 |
} {56 1119 1115} |
| 493 |
do_execsql_test 12.1 { |
| 494 |
SELECT * FROM t1 |
| 495 |
WHERE (b = 1137 AND c IN (97, 98)) |
| 496 |
OR (b = 1119 AND c IN (1115, 1023)); |
| 497 |
} {56 1119 1115} |
| 498 |
|
| 499 |
# 2021-11-02 ticket 5981a8c041a3c2f3 |
| 500 |
# Another OP_SeekScan problem. |
| 501 |
# |
| 502 |
reset_db |
| 503 |
do_execsql_test 13.0 { |
| 504 |
CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT, b INT, c INT); |
| 505 |
INSERT INTO t1 VALUES(10,1,2,5); |
| 506 |
INSERT INTO t1 VALUES(20,1,3,5); |
| 507 |
INSERT INTO t1 VALUES(30,1,2,4); |
| 508 |
INSERT INTO t1 VALUES(40,1,3,4); |
| 509 |
ANALYZE sqlite_master; |
| 510 |
INSERT INTO sqlite_stat1 VALUES('t1','t1x','84000 3 2 1'); |
| 511 |
CREATE INDEX t1x ON t1(a,b,c); |
| 512 |
PRAGMA writable_schema=RESET; |
| 513 |
SELECT * FROM t1 |
| 514 |
WHERE a=1 |
| 515 |
AND b IN (2,3) |
| 516 |
AND c BETWEEN 4 AND 5 |
| 517 |
ORDER BY +id; |
| 518 |
} {10 1 2 5 20 1 3 5 30 1 2 4 40 1 3 4} |
| 519 |
|
| 520 |
finish_test |