Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix the Bloom filter pull-down optimization so that it jumps to the correct place if it encounters a NULL key. Fix for the bug described by forum thread 2482b32700384a0f. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
6eda9b1a7784cf6d58c8876551f67ab9 |
| User & Date: | drh 2022-05-03 14:01:48.589 |
Context
|
2022-05-03
| ||
| 21:58 | Typos cleared from README.md (check-in: be3a1879c8 user: larrybr tags: trunk) | |
| 16:26 | Merge Bloom filter bug fix from trunk into the right-join branch. (Closed-Leaf check-in: 72131ad1bd user: drh tags: right-join) | |
| 14:25 | Fix the Bloom filter pull-down optimization so that it jumps to the correct place if it encounters a NULL key. Fix for the bug described by forum thread 2482b32700384a0f. (check-in: 3dc9fc2f2d user: drh tags: branch-3.38) | |
| 14:01 | Fix the Bloom filter pull-down optimization so that it jumps to the correct place if it encounters a NULL key. Fix for the bug described by forum thread 2482b32700384a0f. (check-in: 6eda9b1a77 user: drh tags: trunk) | |
| 12:11 | Add assert()s to show that jumps always land an an instruction that is between 1 and Vdbe.nOp-1. Had these assert()s been in place before, they would have caused an assertion fault for the byte-code error reported by forum post 2482b32700. (check-in: 8f8a58feb7 user: drh tags: trunk) | |
Changes
Changes to src/wherecode.c.
| ︙ | ︙ | |||
1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 |
WhereLevel *pLevel = &pWInfo->a[iLevel];
WhereLoop *pLoop = pLevel->pWLoop;
if( pLevel->regFilter==0 ) continue;
if( pLevel->pWLoop->nSkip ) continue;
/* ,--- Because sqlite3ConstructBloomFilter() has will not have set
** vvvvv--' pLevel->regFilter if this were true. */
if( NEVER(pLoop->prereq & notReady) ) continue;
if( pLoop->wsFlags & WHERE_IPK ){
WhereTerm *pTerm = pLoop->aLTerm[0];
int regRowid;
assert( pTerm!=0 );
assert( pTerm->pExpr!=0 );
testcase( pTerm->wtFlags & TERM_VIRTUAL );
regRowid = sqlite3GetTempReg(pParse);
| > > | 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 |
WhereLevel *pLevel = &pWInfo->a[iLevel];
WhereLoop *pLoop = pLevel->pWLoop;
if( pLevel->regFilter==0 ) continue;
if( pLevel->pWLoop->nSkip ) continue;
/* ,--- Because sqlite3ConstructBloomFilter() has will not have set
** vvvvv--' pLevel->regFilter if this were true. */
if( NEVER(pLoop->prereq & notReady) ) continue;
assert( pLevel->addrBrk==0 );
pLevel->addrBrk = addrNxt;
if( pLoop->wsFlags & WHERE_IPK ){
WhereTerm *pTerm = pLoop->aLTerm[0];
int regRowid;
assert( pTerm!=0 );
assert( pTerm->pExpr!=0 );
testcase( pTerm->wtFlags & TERM_VIRTUAL );
regRowid = sqlite3GetTempReg(pParse);
|
| ︙ | ︙ | |||
1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 |
codeApplyAffinity(pParse, r1, nEq, zStartAff);
sqlite3DbFree(pParse->db, zStartAff);
sqlite3VdbeAddOp4Int(pParse->pVdbe, OP_Filter, pLevel->regFilter,
addrNxt, r1, nEq);
VdbeCoverage(pParse->pVdbe);
}
pLevel->regFilter = 0;
}
}
/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
| > | 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 |
codeApplyAffinity(pParse, r1, nEq, zStartAff);
sqlite3DbFree(pParse->db, zStartAff);
sqlite3VdbeAddOp4Int(pParse->pVdbe, OP_Filter, pLevel->regFilter,
addrNxt, r1, nEq);
VdbeCoverage(pParse->pVdbe);
}
pLevel->regFilter = 0;
pLevel->addrBrk = 0;
}
}
/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
|
| ︙ | ︙ |
Changes to test/join5.test.
| ︙ | ︙ | |||
388 389 390 391 392 393 394 395 396 397 398 |
} {4}
do_execsql_test 11.3 {
SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d=100;
} {1}
do_execsql_test 11.4 {
SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d>=300;
} {2}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 |
} {4}
do_execsql_test 11.3 {
SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d=100;
} {1}
do_execsql_test 11.4 {
SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d>=300;
} {2}
# 2022-05-03 https://sqlite.org/forum/forumpost/2482b32700384a0f
# Bloom-filter pull-down does not handle NOT NULL constraints correctly.
#
reset_db
do_execsql_test 12.1 {
CREATE TABLE t1(a INT, b INT, c INT);
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
INSERT INTO t1(a,b,c) SELECT x, x*1000, x*1000000 FROM c;
CREATE TABLE t2(b INT, x INT);
INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%3==0;
CREATE INDEX t2b ON t2(b);
CREATE TABLE t3(c INT, y INT);
INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%4==0;
CREATE INDEX t3c ON t3(c);
INSERT INTO t1(a,b,c) VALUES(200, 200000, NULL);
ANALYZE;
} {}
do_execsql_test 12.2 {
SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 WHERE x>0 AND y>0
ORDER BY +a;
} {
12 12000 12000000 12 12
24 24000 24000000 24 24
36 36000 36000000 36 36
48 48000 48000000 48 48
60 60000 60000000 60 60
72 72000 72000000 72 72
84 84000 84000000 84 84
96 96000 96000000 96 96
}
finish_test
|