Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Disable the flattening optimization if the sub-query is a recursive CTE. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | common-table-expr |
Files: | files | file ages | folders |
SHA1: |
9472f6d820a7fb233936d9b8f7a39c9d |
User & Date: | dan 2014-01-15 14:17:31 |
Context
2014-01-15
| ||
14:40 | Don't try to verify the schema of transient table (such as generated inside a WITH clause) when generating code for "IN table" operators. check-in: 860aa936 user: drh tags: common-table-expr | |
14:17 | Disable the flattening optimization if the sub-query is a recursive CTE. check-in: 9472f6d8 user: dan tags: common-table-expr | |
02:40 | Use the user-supplied table name in WITH RECURSIVE tables as the internal name of the table and the name of the table in VDBE comments. check-in: a2933023 user: drh tags: common-table-expr | |
Changes
Changes to src/expr.c.
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
....
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
|
} } } return pNew; } With *withDup(sqlite3 *db, With *p){ With *pRet = 0; if( p ){ int nByte = sizeof(*p) + sizeof(p->a[0]) * (p->nCte-1); pRet = sqlite3DbMallocZero(db, nByte); if( pRet ){ int i; pRet->nCte = p->nCte; ................................................................................ pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; pNew->pRightmost = 0; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->addrOpenEphm[2] = -1; pNew->pWith = withDup(db, p->pWith); pNew->pRecurse = p->pRecurse; return pNew; } #else Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){ assert( p==0 ); return 0; } |
>
>
>
>
>
|
>
|
|
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
....
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
|
} } } return pNew; } /* ** Create and return a deep copy of the object passed as the second ** argument. If an OOM condition is encountered, NULL is returned ** and the db->mallocFailed flag set. */ static With *withDup(sqlite3 *db, With *p){ With *pRet = 0; if( p ){ int nByte = sizeof(*p) + sizeof(p->a[0]) * (p->nCte-1); pRet = sqlite3DbMallocZero(db, nByte); if( pRet ){ int i; pRet->nCte = p->nCte; ................................................................................ pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; pNew->pRightmost = 0; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->addrOpenEphm[2] = -1; pNew->pWith = withDup(db, p->pWith); assert( p->pRecurse==0 ); pNew->pRecurse = 0; return pNew; } #else Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){ assert( p==0 ); return 0; } |
Changes to src/parse.y.
680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 |
where_opt(A) ::= . {A = 0;} where_opt(A) ::= WHERE expr(X). {A = X.pExpr;} ////////////////////////// The UPDATE command //////////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE"); sqlite3Update(pParse,X,Y,W,R); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT |
| > |
680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 |
where_opt(A) ::= . {A = 0;} where_opt(A) ::= WHERE expr(X). {A = X.pExpr;} ////////////////////////// The UPDATE command //////////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3WithPush(pParse, C); sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE"); sqlite3Update(pParse,X,Y,W,R); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT |
Changes to src/select.c.
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
....
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
|
**
** The code generated for this simpification gives the same result
** but only has to scan the data once. And because indices might
** exist on the table t1, a complete scan of the data might be
** avoided.
**
** Flattening is only attempted if all of the following are true:
**
** (1) The subquery and the outer query do not both use aggregates.
**
** (2) The subquery is not an aggregate or the outer query is not a join.
**
** (3) The subquery is not the right operand of a left outer join
** (Originally ticket #306. Strengthened by ticket #3300)
................................................................................
if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0;
pSrc = p->pSrc;
assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
pSubitem = &pSrc->a[iFrom];
iParent = pSubitem->iCursor;
pSub = pSubitem->pSelect;
assert( pSub!=0 );
if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */
if( subqueryIsAgg && pSrc->nSrc>1 ) return 0; /* Restriction (2) */
pSubSrc = pSub->pSrc;
assert( pSubSrc );
/* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET
** because they could be computed at compile-time. But when LIMIT and OFFSET
|
>
>
>
|
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
....
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
|
** ** The code generated for this simpification gives the same result ** but only has to scan the data once. And because indices might ** exist on the table t1, a complete scan of the data might be ** avoided. ** ** Flattening is only attempted if all of the following are true: ** ** (0) The subquery is not a recursive CTE. ** ** (1) The subquery and the outer query do not both use aggregates. ** ** (2) The subquery is not an aggregate or the outer query is not a join. ** ** (3) The subquery is not the right operand of a left outer join ** (Originally ticket #306. Strengthened by ticket #3300) ................................................................................ if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0; pSrc = p->pSrc; assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); pSubitem = &pSrc->a[iFrom]; iParent = pSubitem->iCursor; pSub = pSubitem->pSelect; assert( pSub!=0 ); if( pSub->pRecurse ) return 0; /* Restriction (0) */ if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */ if( subqueryIsAgg && pSrc->nSrc>1 ) return 0; /* Restriction (2) */ pSubSrc = pSub->pSrc; assert( pSubSrc ); /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants, ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET ** because they could be computed at compile-time. But when LIMIT and OFFSET |
Changes to test/with1.test.
159 160 161 162 163 164 165 166 167 168 169 170 |
SELECT x FROM i LIMIT 20; } {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0} do_execsql_test 5.5 { WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i) SELECT x FROM i LIMIT 20; } {1 2 3 4 5 6 7 8 9 0} finish_test |
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 |
SELECT x FROM i LIMIT 20; } {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0} do_execsql_test 5.5 { WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i) SELECT x FROM i LIMIT 20; } {1 2 3 4 5 6 7 8 9 0} #------------------------------------------------------------------------- # do_execsql_test 6.1 { CREATE TABLE f( id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT ); INSERT INTO f VALUES(0, NULL, ''); INSERT INTO f VALUES(1, 0, 'bin'); INSERT INTO f VALUES(2, 1, 'true'); INSERT INTO f VALUES(3, 1, 'false'); INSERT INTO f VALUES(4, 1, 'ls'); INSERT INTO f VALUES(5, 1, 'grep'); INSERT INTO f VALUES(6, 0, 'etc'); INSERT INTO f VALUES(7, 6, 'rc.d'); INSERT INTO f VALUES(8, 7, 'rc.apache'); INSERT INTO f VALUES(9, 7, 'rc.samba'); INSERT INTO f VALUES(10, 0, 'home'); INSERT INTO f VALUES(11, 10, 'dan'); INSERT INTO f VALUES(12, 11, 'public_html'); INSERT INTO f VALUES(13, 12, 'index.html'); INSERT INTO f VALUES(14, 13, 'logo.gif'); } do_execsql_test 6.2 { WITH flat(fid, fpath) AS ( SELECT id, '' FROM f WHERE parentid IS NULL UNION ALL SELECT id, fpath || '/' || name FROM f, flat WHERE +parentid=+fid ) SELECT fpath FROM flat WHERE fpath!='' ORDER BY 1; } { /bin /bin/false /bin/grep /bin/ls /bin/true /etc /etc/rc.d /etc/rc.d/rc.apache /etc/rc.d/rc.samba /home /home/dan /home/dan/public_html /home/dan/public_html/index.html /home/dan/public_html/index.html/logo.gif } finish_test |