Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Ensure that the WhereInfo.revMask bitmap is adjusted when tables are removed from the FROM clause by the Omit-Noop-Join optimization of [0cd82ee9a8413cf1]. Fix for the issue described by format post 8a1e467e905b8d27. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
22ca5a2ffb89ccb5f337993b5a95e27c |
| User & Date: | drh 2024-09-05 23:22:55 |
| Original Comment: | Ensure that the WhereInfo.revMask bitmap is adjusted when tables are removed from the FROM clause by the Omit-Noop-Join optimization of [0cd82ee9a8413cf1]. Fix for the issue described by format post 8a1e467e905b8d27. |
Context
|
2024-09-05
| ||
| 23:44 | Ensure that the WhereInfo.revMask bitmap is adjusted when tables are removed from the FROM clause by the Omit-Noop-Join optimization. (Leaf check-in: 2b543fbc user: drh tags: branch-3.46) | |
| 23:40 | Fix a testrunner status reporting problem introduced by [aa5f10f21dbfb24e]. (check-in: d20c65c3 user: drh tags: trunk) | |
| 23:22 | Ensure that the WhereInfo.revMask bitmap is adjusted when tables are removed from the FROM clause by the Omit-Noop-Join optimization of [0cd82ee9a8413cf1]. Fix for the issue described by format post 8a1e467e905b8d27. (check-in: 22ca5a2f user: drh tags: trunk) | |
| 15:55 | Update notes on build procedures in the README.md file. (check-in: 1d12744f user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
6195 6196 6197 6198 6199 6200 6201 6202 6203 6204 6205 6206 6207 6208 |
tabUsed |= sqlite3WhereExprListUsage(&pWInfo->sMaskSet, pWInfo->pOrderBy);
}
hasRightJoin = (pWInfo->pTabList->a[0].fg.jointype & JT_LTORJ)!=0;
for(i=pWInfo->nLevel-1; i>=1; i--){
WhereTerm *pTerm, *pEnd;
SrcItem *pItem;
WhereLoop *pLoop;
pLoop = pWInfo->a[i].pWLoop;
pItem = &pWInfo->pTabList->a[pLoop->iTab];
if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))!=JT_LEFT ) continue;
if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)==0
&& (pLoop->wsFlags & WHERE_ONEROW)==0
){
continue;
| > | 6195 6196 6197 6198 6199 6200 6201 6202 6203 6204 6205 6206 6207 6208 6209 |
tabUsed |= sqlite3WhereExprListUsage(&pWInfo->sMaskSet, pWInfo->pOrderBy);
}
hasRightJoin = (pWInfo->pTabList->a[0].fg.jointype & JT_LTORJ)!=0;
for(i=pWInfo->nLevel-1; i>=1; i--){
WhereTerm *pTerm, *pEnd;
SrcItem *pItem;
WhereLoop *pLoop;
Bitmask m1;
pLoop = pWInfo->a[i].pWLoop;
pItem = &pWInfo->pTabList->a[pLoop->iTab];
if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))!=JT_LEFT ) continue;
if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)==0
&& (pLoop->wsFlags & WHERE_ONEROW)==0
){
continue;
|
| ︙ | ︙ | |||
6222 6223 6224 6225 6226 6227 6228 6229 6230 6231 6232 6233 6234 6235 |
&& pTerm->pExpr->w.iJoin==pItem->iCursor
){
break; /* restriction (5) */
}
}
if( pTerm<pEnd ) continue;
WHERETRACE(0xffffffff, ("-> drop loop %c not used\n", pLoop->cId));
notReady &= ~pLoop->maskSelf;
for(pTerm=pWInfo->sWC.a; pTerm<pEnd; pTerm++){
if( (pTerm->prereqAll & pLoop->maskSelf)!=0 ){
pTerm->wtFlags |= TERM_CODED;
}
}
if( i!=pWInfo->nLevel-1 ){
| > > | 6223 6224 6225 6226 6227 6228 6229 6230 6231 6232 6233 6234 6235 6236 6237 6238 |
&& pTerm->pExpr->w.iJoin==pItem->iCursor
){
break; /* restriction (5) */
}
}
if( pTerm<pEnd ) continue;
WHERETRACE(0xffffffff, ("-> drop loop %c not used\n", pLoop->cId));
m1 = MASKBIT(i)-1;
pWInfo->revMask = (m1 & pWInfo->revMask) | ((pWInfo->revMask>>1) & ~m1);
notReady &= ~pLoop->maskSelf;
for(pTerm=pWInfo->sWC.a; pTerm<pEnd; pTerm++){
if( (pTerm->prereqAll & pLoop->maskSelf)!=0 ){
pTerm->wtFlags |= TERM_CODED;
}
}
if( i!=pWInfo->nLevel-1 ){
|
| ︙ | ︙ |
Changes to test/join2.test.
| ︙ | ︙ | |||
423 424 425 426 427 428 429 430 431 |
do_eqp_test 12.3 {
SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;
} {
QUERY PLAN
|--SCAN t1
`--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
finish_test
| > > > > > > > > > > > > > > > > > > > > > | 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 |
do_eqp_test 12.3 {
SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;
} {
QUERY PLAN
|--SCAN t1
`--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}
# 2024-09-05 https://sqlite.org/forum/forumpost/8a1e467e905b8d27
# When performing the Omit-Noop-Join optimization, if FROM clause terms
# to the right of the omitted join have the reverse-order bit set in the
# WhereInfo.revMask bitmask, those bits need to be shifted to account
# for the omitted join.
#
reset_db
do_execsql_test 13.0 {
CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1 INT);
CREATE TABLE t2(c2 INT, d2 INTEGER PRIMARY KEY);
CREATE TABLE t3(e3 INTEGER PRIMARY KEY);
INSERT INTO t1 VALUES(33,0);
INSERT INTO t2 VALUES(33,1),(33,2);
}
do_execsql_test 13.1 {
SELECT t1.a1, t2.d2
FROM (t1 LEFT JOIN t3 ON t3.e3=t1.b1) JOIN t2 ON t2.c2=t1.a1
WHERE t1.a1=33
ORDER BY t2.d2 DESC;
} {33 2 33 1}
finish_test
|