/ Check-in [6bfa387e]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | common-table-expr
Files: files | file ages | folders
SHA1: 6bfa387e82de47ca1f40225fe28d873e29d6f481
User & Date: dan 2014-01-16 10:58:39
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: a296b733 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: 6bfa387e user: dan tags: common-table-expr
04:37
Merge trunk changes. Fix a possible NULL-pointer deference in WITH clause name resolution. check-in: 7f953b56 user: drh tags: common-table-expr
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1061   1061     pNew->iOffset = 0;
  1062   1062     pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  1063   1063     pNew->pRightmost = 0;
  1064   1064     pNew->addrOpenEphm[0] = -1;
  1065   1065     pNew->addrOpenEphm[1] = -1;
  1066   1066     pNew->addrOpenEphm[2] = -1;
  1067   1067     pNew->pWith = withDup(db, p->pWith);
  1068         -  assert( p->pRecurse==0 );
  1069         -  pNew->pRecurse = 0;
         1068  +  pNew->pRecurse = p->pRecurse;
  1070   1069     return pNew;
  1071   1070   }
  1072   1071   #else
  1073   1072   Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){
  1074   1073     assert( p==0 );
  1075   1074     return 0;
  1076   1075   }

Changes to src/select.c.

  1845   1845       sqlite3VdbeAddOp2(v, OP_Next, tmp1, addrNext);
  1846   1846   
  1847   1847       /* Execute the recursive SELECT. Store the results in tmp2. While this
  1848   1848       ** SELECT is running, the contents of tmp1 are read by recursive 
  1849   1849       ** references to the current CTE.  */
  1850   1850       p->pPrior = 0;
  1851   1851       p->pRecurse->tnum = tmp1;
         1852  +    assert( (p->pRecurse->tabFlags & TF_Recursive)==0 );
  1852   1853       p->pRecurse->tabFlags |= TF_Recursive;
  1853   1854       rc = sqlite3Select(pParse, p, &tmp2dest);
  1854   1855       p->pRecurse->tabFlags &= ~TF_Recursive;
         1856  +    assert( p->pPrior==0 );
  1855   1857       p->pPrior = pPrior;
  1856   1858       if( rc ) goto multi_select_end;
  1857   1859   
  1858   1860       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap);
  1859   1861       sqlite3VdbeResolveLabel(v, iBreak);
  1860   1862     }else
  1861   1863   #endif
................................................................................
  2841   2843   ** The code generated for this simpification gives the same result
  2842   2844   ** but only has to scan the data once.  And because indices might 
  2843   2845   ** exist on the table t1, a complete scan of the data might be
  2844   2846   ** avoided.
  2845   2847   **
  2846   2848   ** Flattening is only attempted if all of the following are true:
  2847   2849   **
  2848         -**   (0)  The subquery is not a recursive CTE.
  2849         -**
  2850   2850   **   (1)  The subquery and the outer query do not both use aggregates.
  2851   2851   **
  2852   2852   **   (2)  The subquery is not an aggregate or the outer query is not a join.
  2853   2853   **
  2854   2854   **   (3)  The subquery is not the right operand of a left outer join
  2855   2855   **        (Originally ticket #306.  Strengthened by ticket #3300)
  2856   2856   **
................................................................................
  2925   2925   **        an ORDER BY clause.  Ticket #3773.  We could relax this constraint
  2926   2926   **        somewhat by saying that the terms of the ORDER BY clause must
  2927   2927   **        appear as unmodified result columns in the outer query.  But we
  2928   2928   **        have other optimizations in mind to deal with that case.
  2929   2929   **
  2930   2930   **  (21)  The subquery does not use LIMIT or the outer query is not
  2931   2931   **        DISTINCT.  (See ticket [752e1646fc]).
         2932  +**
         2933  +**  (22)  The subquery is not a recursive CTE.
         2934  +**
         2935  +**  (23)  The parent is not a recursive CTE, or the sub-query is not a
         2936  +**        compound query. This restriction is because transforming the
         2937  +**        parent to a compound query confuses the code that handles
         2938  +**        recursive queries in multiSelect().
         2939  +**
  2932   2940   **
  2933   2941   ** In this routine, the "p" parameter is a pointer to the outer query.
  2934   2942   ** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
  2935   2943   ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
  2936   2944   **
  2937   2945   ** If flattening is not attempted, this routine is a no-op and returns 0.
  2938   2946   ** If flattening is attempted this routine returns 1.
................................................................................
  2967   2975     if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0;
  2968   2976     pSrc = p->pSrc;
  2969   2977     assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  2970   2978     pSubitem = &pSrc->a[iFrom];
  2971   2979     iParent = pSubitem->iCursor;
  2972   2980     pSub = pSubitem->pSelect;
  2973   2981     assert( pSub!=0 );
  2974         -  if( pSub->pRecurse ) return 0;                         /* Restriction (0)  */
  2975   2982     if( isAgg && subqueryIsAgg ) return 0;                 /* Restriction (1)  */
  2976   2983     if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;          /* Restriction (2)  */
  2977   2984     pSubSrc = pSub->pSrc;
  2978   2985     assert( pSubSrc );
  2979   2986     /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
  2980   2987     ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET
  2981   2988     ** because they could be computed at compile-time.  But when LIMIT and OFFSET
................................................................................
  2998   3005        return 0;                                           /* Restriction (11) */
  2999   3006     }
  3000   3007     if( isAgg && pSub->pOrderBy ) return 0;                /* Restriction (16) */
  3001   3008     if( pSub->pLimit && p->pWhere ) return 0;              /* Restriction (19) */
  3002   3009     if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){
  3003   3010        return 0;         /* Restriction (21) */
  3004   3011     }
         3012  +  if( pSub->pRecurse ) return 0;                         /* Restriction (22)  */
         3013  +  if( p->pRecurse && pSub->pPrior ) return 0;            /* Restriction (23)  */
  3005   3014   
  3006   3015     /* OBSOLETE COMMENT 1:
  3007   3016     ** Restriction 3:  If the subquery is a join, make sure the subquery is 
  3008   3017     ** not used as the right operand of an outer join.  Examples of why this
  3009   3018     ** is not allowed:
  3010   3019     **
  3011   3020     **         t1 LEFT OUTER JOIN (t2 JOIN t3)