Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch nonworking-flatten-left-join Excluding Merge-Ins
This is equivalent to a diff from 836fa097 to dbe522b0
|
2022-07-25
| ||
| 16:06 | Fix an error in the aggregate query LEFT JOIN flattening optimization from [2cf373b10c9bc4cb] and further enhance that optimization so that it works even if there is a GROUP BY clause. (check-in: b52393ac user: drh tags: trunk) | |
| 14:05 | TK_IF_NULL_ROW expressions must be accumulated in the same way as TK_COLUMN expressions in an aggregate query. Proposed fix for the problem identifyed by dbsqlfuzz 8e17857db2c5a9294c975123ac807156a6559f13. (check-in: 40d08807 user: drh tags: flatten-left-join) | |
| 11:19 | If the LHS of a LEFT JOIN is flattened into an aggregate parent query, avoid executing OP_IfNullRow on an unopened cursor by disallowing the index-only optimization. (Later:) Does not work if automatic indexes are disabled. (Closed-Leaf check-in: dbe522b0 user: dan tags: nonworking-flatten-left-join) | |
| 11:04 | Reduce a timeout in walsetlk.test from 2000ms to 1100ms so that the test runs a bit faster. (check-in: 836fa097 user: dan tags: trunk) | |
|
2022-07-23
| ||
| 12:51 | Simplifications to sqlite3FinishCoding() for a small size reduction and performance increase. (check-in: a995614b user: drh tags: trunk) | |
Changes to src/select.c.
| ︙ | ︙ | |||
4563 4564 4565 4566 4567 4568 4569 4570 4571 4572 4573 4574 4575 4576 |
*pItem = pSubSrc->a[i];
pItem->fg.jointype |= ltorj;
iNewParent = pSubSrc->a[i].iCursor;
memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
}
pSrc->a[iFrom].fg.jointype &= JT_LTORJ;
pSrc->a[iFrom].fg.jointype |= jointype | ltorj;
/* Now begin substituting subquery result set expressions for
** references to the iParent in the outer query.
**
** Example:
**
** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
| > > > > | 4563 4564 4565 4566 4567 4568 4569 4570 4571 4572 4573 4574 4575 4576 4577 4578 4579 4580 |
*pItem = pSubSrc->a[i];
pItem->fg.jointype |= ltorj;
iNewParent = pSubSrc->a[i].iCursor;
memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
}
pSrc->a[iFrom].fg.jointype &= JT_LTORJ;
pSrc->a[iFrom].fg.jointype |= jointype | ltorj;
if( isAgg && (jointype & JT_LEFT) ){
pSrc->a[iFrom].fg.noIndexOnly = 1;
}
/* Now begin substituting subquery result set expressions for
** references to the iParent in the outer query.
**
** Example:
**
** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
|
| ︙ | ︙ |
Changes to src/sqliteInt.h.
| ︙ | ︙ | |||
3100 3101 3102 3103 3104 3105 3106 3107 3108 3109 3110 3111 3112 3113 |
unsigned fromDDL :1; /* Comes from sqlite_schema */
unsigned isCte :1; /* This is a CTE */
unsigned notCte :1; /* This item may not match a CTE */
unsigned isUsing :1; /* u3.pUsing is valid */
unsigned isOn :1; /* u3.pOn was once valid and non-NULL */
unsigned isSynthUsing :1; /* u3.pUsing is synthensized from NATURAL */
unsigned isNestedFrom :1; /* pSelect is a SF_NestedFrom subquery */
} fg;
int iCursor; /* The VDBE cursor number used to access this table */
union {
Expr *pOn; /* fg.isUsing==0 => The ON clause of a join */
IdList *pUsing; /* fg.isUsing==1 => The USING clause of a join */
} u3;
Bitmask colUsed; /* Bit N (1<<N) set if column N of pTab is used */
| > | 3100 3101 3102 3103 3104 3105 3106 3107 3108 3109 3110 3111 3112 3113 3114 |
unsigned fromDDL :1; /* Comes from sqlite_schema */
unsigned isCte :1; /* This is a CTE */
unsigned notCte :1; /* This item may not match a CTE */
unsigned isUsing :1; /* u3.pUsing is valid */
unsigned isOn :1; /* u3.pOn was once valid and non-NULL */
unsigned isSynthUsing :1; /* u3.pUsing is synthensized from NATURAL */
unsigned isNestedFrom :1; /* pSelect is a SF_NestedFrom subquery */
unsigned noIndexOnly :1; /* Do not use an index-only scan for this loop */
} fg;
int iCursor; /* The VDBE cursor number used to access this table */
union {
Expr *pOn; /* fg.isUsing==0 => The ON clause of a join */
IdList *pUsing; /* fg.isUsing==1 => The USING clause of a join */
} u3;
Bitmask colUsed; /* Bit N (1<<N) set if column N of pTab is used */
|
| ︙ | ︙ |
Changes to src/where.c.
| ︙ | ︙ | |||
3464 3465 3466 3467 3468 3469 3470 |
ApplyCostMultiplier(pNew->rRun, pTab->costMult);
whereLoopOutputAdjust(pWC, pNew, rSize);
rc = whereLoopInsert(pBuilder, pNew);
pNew->nOut = rSize;
if( rc ) break;
}else{
Bitmask m;
| > > | | 3464 3465 3466 3467 3468 3469 3470 3471 3472 3473 3474 3475 3476 3477 3478 3479 3480 |
ApplyCostMultiplier(pNew->rRun, pTab->costMult);
whereLoopOutputAdjust(pWC, pNew, rSize);
rc = whereLoopInsert(pBuilder, pNew);
pNew->nOut = rSize;
if( rc ) break;
}else{
Bitmask m;
if( pSrc->fg.noIndexOnly ){
pNew->wsFlags = WHERE_INDEXED;
}else if( pProbe->isCovering ){
pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED;
m = 0;
}else{
m = pSrc->colUsed & pProbe->colNotIdxed;
pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
}
|
| ︙ | ︙ |
Changes to test/select3.test.
| ︙ | ︙ | |||
325 326 327 328 329 330 331 332 333 |
do_execsql_test select3.10.100 {
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d);
SELECT max(t1.a),
(SELECT 'xyz' FROM (SELECT * FROM t2 WHERE 0) WHERE t1.b=1)
FROM t1;
} {{} {}}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 |
do_execsql_test select3.10.100 {
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d);
SELECT max(t1.a),
(SELECT 'xyz' FROM (SELECT * FROM t2 WHERE 0) WHERE t1.b=1)
FROM t1;
} {{} {}}
#-------------------------------------------------------------------------
# dbsqlfuzz crash-8e17857db2c5a9294c975123ac807156a6559f13.txt
#
reset_db
do_execsql_test select3-10.1 {
CREATE TABLE t1(a);
CREATE TABLE t2(x);
CREATE INDEX t2x ON t2(x);
INSERT INTO t1 VALUES('abc');
}
do_execsql_test select3.10.2 {
SELECT max(a), val FROM t1 LEFT JOIN (
SELECT 'constant' AS val FROM t2 WHERE x=1234
)
} {abc {}}
do_execsql_test select3.10.3 {
INSERT INTO t2 VALUES(123);
SELECT max(a), val FROM t1 LEFT JOIN (
SELECT 'constant' AS val FROM t2 WHERE x=1234
)
} {abc {}}
do_execsql_test select3.10.4 {
INSERT INTO t2 VALUES(1234);
SELECT max(a), val FROM t1 LEFT JOIN (
SELECT 'constant' AS val FROM t2 WHERE x=1234
)
} {abc constant}
do_execsql_test select3.10.5 {
DELETE FROM t2;
INSERT INTO t2 VALUES(1234);
SELECT max(a), val FROM t1 LEFT JOIN (
SELECT 'constant' AS val FROM t2 WHERE x=1234
)
} {abc constant}
finish_test
|