Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not allow a partial index scan on the left table of a RIGHT JOIN, because since the index is partial, some rows will be omitted from the scan, and those rows will subsequently be picked up by the no-match logic in the right-join post-processing loop. forum post c4676c4956. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
615c0026119f7870c3b6ef9dcb57ce4e |
| User & Date: | drh 2022-06-08 12:46:58 |
Context
|
2022-06-08
| ||
| 15:38 | Fix the query flattener so that it refuses a flattening that might leave both an inner-join and outer-join ON-clause constraint (or equivalent) on the same term of the FROM clause. (check-in: f6c4fb48 user: drh tags: trunk) | |
| 12:46 | Do not allow a partial index scan on the left table of a RIGHT JOIN, because since the index is partial, some rows will be omitted from the scan, and those rows will subsequently be picked up by the no-match logic in the right-join post-processing loop. forum post c4676c4956. (check-in: 615c0026 user: drh tags: trunk) | |
| 12:35 | Minor fix to test/index9.test so that it can be invoked with other index tests using a wildcard. (check-in: ccbd6e77 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
3212 3213 3214 3215 3216 3217 3218 |
int iTab, /* The table for which we want an index */
u8 jointype, /* The JT_* flags on the join */
WhereClause *pWC, /* The WHERE clause of the query */
Expr *pWhere /* The WHERE clause from the partial index */
){
int i;
WhereTerm *pTerm;
| > > > | | 3212 3213 3214 3215 3216 3217 3218 3219 3220 3221 3222 3223 3224 3225 3226 3227 3228 3229 |
int iTab, /* The table for which we want an index */
u8 jointype, /* The JT_* flags on the join */
WhereClause *pWC, /* The WHERE clause of the query */
Expr *pWhere /* The WHERE clause from the partial index */
){
int i;
WhereTerm *pTerm;
Parse *pParse;
if( jointype & JT_LTORJ ) return 0;
pParse = pWC->pWInfo->pParse;
while( pWhere->op==TK_AND ){
if( !whereUsablePartialIndex(iTab,jointype,pWC,pWhere->pLeft) ) return 0;
pWhere = pWhere->pRight;
}
if( pParse->db->flags & SQLITE_EnableQPSG ) pParse = 0;
for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
Expr *pExpr;
|
| ︙ | ︙ |
Changes to test/index6.test.
| ︙ | ︙ | |||
503 504 505 506 507 508 509 510 511 |
reset_db
do_execsql_test index6-18.1 {
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES(10,10);
CREATE UNIQUE INDEX t1b ON t1(b) WHERE a>NULL;
SELECT * FROM t1 WHERE a IS NOT NULL;
} {10 10}
finish_test
| > > > > > > > > > > > > > > > > > > > > | 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 |
reset_db
do_execsql_test index6-18.1 {
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES(10,10);
CREATE UNIQUE INDEX t1b ON t1(b) WHERE a>NULL;
SELECT * FROM t1 WHERE a IS NOT NULL;
} {10 10}
# 2022-06-09
# https://sqlite.org/forum/forumpost/c4676c4956
# Cannot do a scan of a partial index on the left table of a RIGHT JOIN
# since that will cause extra rows to appear in the output during the
# right-join no-match loop. The following testcase is verify using
# PostgreSQL 14.
#
reset_db
do_execsql_test index6-19.1 {
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1(a) VALUES(2);
CREATE TABLE t2(c INT);
CREATE INDEX i0 ON t2(c) WHERE c=3;
CREATE TABLE t3(d INT);
INSERT INTO t3 VALUES(1);
}
do_execsql_test index6-19.2 {
SELECT * FROM t2 RIGHT JOIN t3 ON d<>0 LEFT JOIN t1 ON c=3 WHERE t1.a<>0;
} {}
finish_test
|