Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not attempt to use terms from the WHERE clause to drive indexes on the right table of a LEFT JOIN. Fix for ticket [4ba5abf65c5b0f9a96a7a40cd18b] |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
aeb694e3f787f1f8b55650c17f90c197 |
User & Date: | drh 2018-04-24 17:34:03.981 |
Context
2018-04-24
| ||
18:53 | Fix a problem with processing "LEFT JOIN tbl ON tbl.a = ? AND (tbl.b=? OR tbl.c=?)" in cases where there are indexes on both tbl(a, b) and tbl(a, c). (check-in: ce35e39c5c user: dan tags: trunk) | |
17:34 | Do not attempt to use terms from the WHERE clause to drive indexes on the right table of a LEFT JOIN. Fix for ticket [4ba5abf65c5b0f9a96a7a40cd18b] (check-in: aeb694e3f7 user: drh tags: trunk) | |
16:51 | Update the expert extension test cases to account for the fact that EXPLAIN QUERY PLAN now shows the start of each trigger in its output. (check-in: 8acb42f489 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | |||
5539 5540 5541 5542 5543 5544 5545 | 5539 5540 5541 5542 5543 5544 5545 5546 5547 5548 5549 5550 5551 5552 5553 | - + | /* Sometimes the code for a subquery will be generated more than ** once, if the subquery is part of the WHERE clause in a LEFT JOIN, ** for example. In that case, do not regenerate the code to manifest ** a view or the co-routine to implement a view. The first instance ** is sufficient, though the subroutine to manifest the view does need ** to be invoked again. */ |
︙ |
Changes to src/where.c.
︙ | |||
2416 2417 2418 2419 2420 2421 2422 | 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 | - + - - - | } if( pTerm->prereqRight & pNew->maskSelf ) continue; /* Do not allow the upper bound of a LIKE optimization range constraint ** to mix with a lower range bound from some other source */ if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue; |
︙ |
Changes to test/where.test.
︙ | |||
1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 | 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 | + + + + + + + + + + + + | CREATE INDEX t191a ON t1(a); CREATE INDEX t191b ON t1(b); CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT); EXPLAIN QUERY PLAN SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1; } {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/} # 2018-04-24 ticket [https://www.sqlite.org/src/info/4ba5abf65c5b0f9a] # Index on expressions leads to an incorrect answer for a LEFT JOIN # do_execsql_test where-20.0 { CREATE TABLE t201(x); CREATE TABLE t202(y, z); INSERT INTO t201 VALUES('key'); INSERT INTO t202 VALUES('key', -1); CREATE INDEX t202i ON t202(y, ifnull(z, 0)); SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0; } {0} finish_test |