SQLite

Check-in [6bfa387e82]
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
Timelines: family | ancestors | descendants | both | common-table-expr
Files: files | file ages | folders
SHA1: 6bfa387e82de47ca1f40225fe28d873e29d6f481
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
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/expr.c.
1061
1062
1063
1064
1065
1066
1067
1068
1069

1070
1071
1072
1073
1074
1075
1076
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);
  assert( p->pRecurse==0 );
  pNew->pRecurse = 0;
  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
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
2848
2849
2850
2851
2852
2853
2854
2855
2856
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:
**
**   (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)
**
2925
2926
2927
2928
2929
2930
2931








2932
2933
2934
2935
2936
2937
2938
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
2974
2975
2976
2977
2978
2979
2980
2981
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( 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
2998
2999
3000
3001
3002
3003
3004


3005
3006
3007
3008
3009
3010
3011
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)