SQLite

Check-in [9472f6d820]
Login

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: 9472f6d820a7fb233936d9b8f7a39c9d4c4d6d73
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
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
891
892
893
894
895
896
897





898
899
900
901
902
903
904
905
      }

    }
  }
  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;







>
>
>
>
>
|







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

1058
1059
1060
1061
1062
1063
1064
1065
  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;
}







>
|







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
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
**
** 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