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 |
Timelines: | family | ancestors | descendants | both | common-table-expr |
Files: | files | file ages | folders |
SHA1: |
9472f6d820a7fb233936d9b8f7a39c9d |
User & Date: | dan 2014-01-15 14:17:31.048 |
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: 860aa93663 user: drh tags: common-table-expr) | |
14:17 | Disable the flattening optimization if the sub-query is a recursive CTE. (check-in: 9472f6d820 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: a29330238b user: drh tags: common-table-expr) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
891 892 893 894 895 896 897 | } } } return pNew; } | > > > > > | | 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 | } } } 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; |
︙ | ︙ | |||
1051 1052 1053 1054 1055 1056 1057 | 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); | > | | 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 | 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 | where_opt(A) ::= . {A = 0;} where_opt(A) ::= WHERE expr(X). {A = X.pExpr;} ////////////////////////// The UPDATE command //////////////////////////////// // %ifdef 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 | ** ** 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) | > > | 2839 2840 2841 2842 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 | ** ** 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) |
︙ | ︙ | |||
2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 | 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 | > | 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 | 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 |