Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a problem causing the planner to generate sub-optimal plans for some queries that use recursive WITH sub-queries with LIMIT clauses. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
053a149cc8244a7f85137129cfcb8622 |
User & Date: | dan 2016-12-16 15:05:40.539 |
Context
2016-12-16
| ||
18:14 | Built-in PRAGMA statements without side-effects can be invoked as table-valued functions by prefixing their name with "pragma_". (check-in: d66ec5cfb6 user: drh tags: trunk) | |
15:57 | Merge recent trunk enhancements. (check-in: 74a0ca1f15 user: drh tags: est_count_pragma) | |
15:05 | Fix a problem causing the planner to generate sub-optimal plans for some queries that use recursive WITH sub-queries with LIMIT clauses. (check-in: 053a149cc8 user: dan tags: trunk) | |
01:00 | All temp.sqlite_master to be used as an alias for sqlite_temp_master. (check-in: 8d646905b8 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 | int regLimit, regOffset; /* Registers used by LIMIT and OFFSET */ /* Obtain authorization to do a recursive query */ if( sqlite3AuthCheck(pParse, SQLITE_RECURSIVE, 0, 0, 0) ) return; /* Process the LIMIT and OFFSET clauses, if they exist */ addrBreak = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, addrBreak); pLimit = p->pLimit; pOffset = p->pOffset; regLimit = p->iLimit; regOffset = p->iOffset; p->pLimit = p->pOffset = 0; p->iLimit = p->iOffset = 0; | > | 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 | int regLimit, regOffset; /* Registers used by LIMIT and OFFSET */ /* Obtain authorization to do a recursive query */ if( sqlite3AuthCheck(pParse, SQLITE_RECURSIVE, 0, 0, 0) ) return; /* Process the LIMIT and OFFSET clauses, if they exist */ addrBreak = sqlite3VdbeMakeLabel(v); p->nSelectRow = 320; /* 4 billion rows */ computeLimitRegisters(pParse, p, addrBreak); pLimit = p->pLimit; pOffset = p->pOffset; regLimit = p->iLimit; regOffset = p->iOffset; p->pLimit = p->pOffset = 0; p->iLimit = p->iOffset = 0; |
︙ | ︙ | |||
5166 5167 5168 5169 5170 5171 5172 | if( pDest->eDest==SRT_EphemTab ){ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr); } /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); | > | > | 5167 5168 5169 5170 5171 5172 5173 5174 5175 5176 5177 5178 5179 5180 5181 5182 5183 | if( pDest->eDest==SRT_EphemTab ){ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr); } /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); if( (p->selFlags & SF_FixedLimit)==0 ){ p->nSelectRow = 320; /* 4 billion rows */ } computeLimitRegisters(pParse, p, iEnd); if( p->iLimit==0 && sSort.addrSortIndex>=0 ){ sqlite3VdbeChangeOpcode(v, sSort.addrSortIndex, OP_SorterOpen); sSort.sortFlags |= SORTFLAG_UseSorter; } /* Open an ephemeral index to use for the distinct set. |
︙ | ︙ |
Changes to test/with3.test.
︙ | ︙ | |||
56 57 58 59 60 61 62 63 64 | CREATE TABLE t1(x); 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; } {200} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | CREATE TABLE t1(x); 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; } {200} #------------------------------------------------------------------------- # Test that the planner notices LIMIT clauses on recursive WITH queries. # ifcapable analyze { do_execsql_test 3.1.1 { CREATE TABLE y1(a, b); CREATE INDEX y1a ON y1(a); WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000) INSERT INTO y1 SELECT i%10, i FROM cnt; ANALYZE; } do_eqp_test 3.1.2 { WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1) SELECT * FROM cnt, y1 WHERE i=a } { 3 0 0 {SCAN TABLE cnt} 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} 0 0 0 {SCAN SUBQUERY 1} 0 1 1 {SEARCH TABLE y1 USING INDEX y1a (a=?)} } do_eqp_test 3.1.3 { WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000) SELECT * FROM cnt, y1 WHERE i=a } { 3 0 0 {SCAN TABLE cnt} 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} 0 0 1 {SCAN TABLE y1} 0 1 0 {SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (i=?)} } } do_execsql_test 3.2.1 { CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER); CREATE TABLE w2(pk INTEGER PRIMARY KEY); } do_eqp_test 3.2.2 { WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1) UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1) SELECT * FROM c, w2, w1 WHERE c.id=w2.pk AND c.id=w1.pk; } { 2 0 0 {EXECUTE SCALAR SUBQUERY 3} 3 0 0 {SCAN TABLE w2} 4 0 0 {SCAN TABLE w1} 4 1 1 {SCAN TABLE c} 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} 0 0 0 {SCAN SUBQUERY 1} 0 1 1 {SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)} 0 2 2 {SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)} } finish_test |