Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Disable the flattening optimization if the parent query is the recursive part of a recursive CTE and the sub-query is a compound query. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | common-table-expr |
Files: | files | file ages | folders |
SHA1: |
6bfa387e82de47ca1f40225fe28d873e |
User & Date: | dan 2014-01-16 10:58:39.954 |
Context
2014-01-16
| ||
18:34 | Allow only a single recursive reference in a recursive CTE. Also require that this reference is not part of a sub-query. (check-in: a296b73360 user: dan tags: common-table-expr) | |
10:58 | Disable the flattening optimization if the parent query is the recursive part of a recursive CTE and the sub-query is a compound query. (check-in: 6bfa387e82 user: dan tags: common-table-expr) | |
04:37 | Merge trunk changes. Fix a possible NULL-pointer deference in WITH clause name resolution. (check-in: 7f953b568b user: drh tags: common-table-expr) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1061 1062 1063 1064 1065 1066 1067 | 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); | < | | 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 | 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; } |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 | sqlite3VdbeAddOp2(v, OP_Next, tmp1, addrNext); /* Execute the recursive SELECT. Store the results in tmp2. While this ** SELECT is running, the contents of tmp1 are read by recursive ** references to the current CTE. */ p->pPrior = 0; p->pRecurse->tnum = tmp1; p->pRecurse->tabFlags |= TF_Recursive; rc = sqlite3Select(pParse, p, &tmp2dest); p->pRecurse->tabFlags &= ~TF_Recursive; p->pPrior = pPrior; if( rc ) goto multi_select_end; sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap); sqlite3VdbeResolveLabel(v, iBreak); }else #endif | > > | 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 | sqlite3VdbeAddOp2(v, OP_Next, tmp1, addrNext); /* Execute the recursive SELECT. Store the results in tmp2. While this ** SELECT is running, the contents of tmp1 are read by recursive ** references to the current CTE. */ p->pPrior = 0; p->pRecurse->tnum = tmp1; assert( (p->pRecurse->tabFlags & TF_Recursive)==0 ); p->pRecurse->tabFlags |= TF_Recursive; rc = sqlite3Select(pParse, p, &tmp2dest); p->pRecurse->tabFlags &= ~TF_Recursive; assert( p->pPrior==0 ); p->pPrior = pPrior; if( rc ) goto multi_select_end; sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap); sqlite3VdbeResolveLabel(v, iBreak); }else #endif |
︙ | ︙ | |||
2841 2842 2843 2844 2845 2846 2847 | ** 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: ** | < < | 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 | ** 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) ** |
︙ | ︙ | |||
2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 | ** an ORDER BY clause. Ticket #3773. We could relax this constraint ** somewhat by saying that the terms of the ORDER BY clause must ** appear as unmodified result columns in the outer query. But we ** have other optimizations in mind to deal with that case. ** ** (21) The subquery does not use LIMIT or the outer query is not ** DISTINCT. (See ticket [752e1646fc]). ** ** In this routine, the "p" parameter is a pointer to the outer query. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates. ** ** If flattening is not attempted, this routine is a no-op and returns 0. ** If flattening is attempted this routine returns 1. | > > > > > > > > | 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 | ** an ORDER BY clause. Ticket #3773. We could relax this constraint ** somewhat by saying that the terms of the ORDER BY clause must ** appear as unmodified result columns in the outer query. But we ** have other optimizations in mind to deal with that case. ** ** (21) The subquery does not use LIMIT or the outer query is not ** DISTINCT. (See ticket [752e1646fc]). ** ** (22) The subquery is not a recursive CTE. ** ** (23) The parent is not a recursive CTE, or the sub-query is not a ** compound query. This restriction is because transforming the ** parent to a compound query confuses the code that handles ** recursive queries in multiSelect(). ** ** ** In this routine, the "p" parameter is a pointer to the outer query. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates. ** ** If flattening is not attempted, this routine is a no-op and returns 0. ** If flattening is attempted this routine returns 1. |
︙ | ︙ | |||
2967 2968 2969 2970 2971 2972 2973 | 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 ); | < | 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 | 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 |
︙ | ︙ | |||
2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 | return 0; /* Restriction (11) */ } if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */ if( pSub->pLimit && p->pWhere ) return 0; /* Restriction (19) */ if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){ return 0; /* Restriction (21) */ } /* OBSOLETE COMMENT 1: ** Restriction 3: If the subquery is a join, make sure the subquery is ** not used as the right operand of an outer join. Examples of why this ** is not allowed: ** ** t1 LEFT OUTER JOIN (t2 JOIN t3) | > > | 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 | return 0; /* Restriction (11) */ } if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */ if( pSub->pLimit && p->pWhere ) return 0; /* Restriction (19) */ if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){ return 0; /* Restriction (21) */ } if( pSub->pRecurse ) return 0; /* Restriction (22) */ if( p->pRecurse && pSub->pPrior ) return 0; /* Restriction (23) */ /* OBSOLETE COMMENT 1: ** Restriction 3: If the subquery is a join, make sure the subquery is ** not used as the right operand of an outer join. Examples of why this ** is not allowed: ** ** t1 LEFT OUTER JOIN (t2 JOIN t3) |
︙ | ︙ |