Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Correctly initialize the iSelectId of FROM clause terms that are a self join of a reused materialized subquery. Without this, the EXPLAIN QUERY PLAN output for the query will identify the subquery using the uninitialized (and arbitrary) iSelectId. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
43c9ae371f6250fee98a7c4011726eff |
| User & Date: | drh 2017-05-29 13:09:24 |
Context
|
2017-05-29
| ||
| 14:26 | Optimizations to the Walker object and its methods to make the code a little smaller and to help it run a little faster. (check-in: 6854a34e user: drh tags: trunk) | |
| 13:09 | Correctly initialize the iSelectId of FROM clause terms that are a self join of a reused materialized subquery. Without this, the EXPLAIN QUERY PLAN output for the query will identify the subquery using the uninitialized (and arbitrary) iSelectId. (check-in: 43c9ae37 user: drh tags: trunk) | |
| 03:48 | Improve shell help text for the '.open' command. (check-in: 7cc940a9 user: mistachkin tags: trunk) | |
Changes
Changes to src/select.c.
5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 5276 5277 5278 5279 |
VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
}else{
VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
}
pPrior = isSelfJoinView(pTabList, pItem);
if( pPrior ){
sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor);
}else{
sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
sqlite3Select(pParse, pSub, &dest);
}
pItem->pTab->nRowLogEst = pSub->nSelectRow;
if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
|
> |
5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 5276 5277 5278 5279 5280 |
VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
}else{
VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
}
pPrior = isSelfJoinView(pTabList, pItem);
if( pPrior ){
sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor);
explainSetInteger(pItem->iSelectId, pPrior->iSelectId);
}else{
sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
sqlite3Select(pParse, pSub, &dest);
}
pItem->pTab->nRowLogEst = pSub->nSelectRow;
if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
|
Changes to test/with1.test.
985 986 987 988 989 990 991 992 993 994 |
WITH xyz(x) AS (
SELECT printf('%d', 5) * NULL
UNION SELECT round(1<1+x)
FROM xyz ORDER BY 1
)
SELECT 1 FROM xyz;
} 1
finish_test
|
> > > > > > > > > > > > > > |
985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 |
WITH xyz(x) AS (
SELECT printf('%d', 5) * NULL
UNION SELECT round(1<1+x)
FROM xyz ORDER BY 1
)
SELECT 1 FROM xyz;
} 1
# EXPLAIN QUERY PLAN on a self-join of a CTE
#
do_execsql_test 19.1 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(x);
EXPLAIN QUERY PLAN
WITH
x1(a) AS (values(100))
INSERT INTO t1(x)
SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
SELECT * FROM t1;
} {0 0 0 {SCAN SUBQUERY 1} 0 1 1 {SCAN SUBQUERY 1}}
finish_test
|