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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: | aeb694e3f787f1f8b55650c17f90c197 |
| User & Date: | drh 2018-04-24 17:34:03 |
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: ce35e39c 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: aeb694e3 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: 8acb42f4 user: drh tags: trunk | |
Changes
Changes to src/select.c.
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. */
if( pItem->addrFillSub ){
if( pItem->fg.viaCoroutine==0 ){
/* The subroutine that manifests the view might be a one-time routine,
** or it might need to be rerun on each iteration because it
** encodes a correlated subquery. */
testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once );
sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
}
|
| |
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. */
if( NEVER(pItem->addrFillSub) ){
if( pItem->fg.viaCoroutine==0 ){
/* The subroutine that manifests the view might be a one-time routine,
** or it might need to be rerun on each iteration because it
** encodes a correlated subquery. */
testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once );
sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
}
|
Changes to src/where.c.
2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 |
}
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;
/* Do not allow IS constraints from the WHERE clause to be used by the
** right table of a LEFT JOIN. Only constraints in the ON clause are
** allowed */
if( (pSrc->fg.jointype & JT_LEFT)!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
&& (eOp & (WO_IS|WO_ISNULL))!=0
){
testcase( eOp & WO_IS );
testcase( eOp & WO_ISNULL );
continue;
}
if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){
pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE;
}else{
pBuilder->bldFlags |= SQLITE_BLDF_INDEXED;
|
| < < < |
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;
/* Do not allow constraints from the WHERE clause to be used by the
** right table of a LEFT JOIN. Only constraints in the ON clause are
** allowed */
if( (pSrc->fg.jointype & JT_LEFT)!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
){
continue;
}
if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){
pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE;
}else{
pBuilder->bldFlags |= SQLITE_BLDF_INDEXED;
|
Changes to test/where.test.
1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 |
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 .*/}
finish_test
|
> > > > > > > > > > > > |
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
|