/ Check-in [28aa6db8]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Disable automatic indices on recursive CTE references.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | common-table-expr
Files: files | file ages | folders
SHA1: 28aa6db8c878655255dbfb618f8d65be78e3d7e5
User & Date: dan 2014-01-15 18:21:41
Context
2014-01-15
18:23
Add a header comment to the searchWith() routine. check-in: d9ae0f5d user: drh tags: common-table-expr
18:21
Disable automatic indices on recursive CTE references. check-in: 28aa6db8 user: dan tags: common-table-expr
18:12
When resolving names, consider a reference to a recursive CTE column as equivalent to a reference to the outermost name-context. This ensures that correlated sub-queries are correctly identified as such. check-in: 61be2da0 user: dan tags: common-table-expr
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4192   4192     if( !pBuilder->pOrSet
  4193   4193      && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0
  4194   4194      && pSrc->pIndex==0
  4195   4195      && !pSrc->viaCoroutine
  4196   4196      && !pSrc->notIndexed
  4197   4197      && HasRowid(pTab)
  4198   4198      && !pSrc->isCorrelated
         4199  +   && !pSrc->isRecursive
  4199   4200     ){
  4200   4201       /* Generate auto-index WhereLoops */
  4201   4202       WhereTerm *pTerm;
  4202   4203       WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
  4203   4204       for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
  4204   4205         if( pTerm->prereqRight & pNew->maskSelf ) continue;
  4205   4206         if( termCanDriveIndex(pTerm, pSrc, 0) ){

Changes to test/with1.test.

   224    224             INSERT INTO f VALUES(14, 13, 'logo.gif');
   225    225   }
   226    226   
   227    227   do_execsql_test 6.2 {
   228    228     WITH flat(fid, fpath) AS (
   229    229       SELECT id, '' FROM f WHERE parentid IS NULL
   230    230       UNION ALL
   231         -    SELECT id, fpath || '/' || name FROM f, flat WHERE +parentid=+fid
          231  +    SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=fid
   232    232     )
   233    233     SELECT fpath FROM flat WHERE fpath!='' ORDER BY 1;
   234    234   } {
   235    235     /bin 
   236    236     /bin/false /bin/grep /bin/ls /bin/true 
   237    237     /etc 
   238    238     /etc/rc.d 
................................................................................
   244    244     /home/dan/public_html/index.html/logo.gif
   245    245   }
   246    246   
   247    247   do_execsql_test 6.3 {
   248    248     WITH flat(fid, fpath) AS (
   249    249       SELECT id, '' FROM f WHERE parentid IS NULL
   250    250       UNION ALL
   251         -    SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=+fid
          251  +    SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=fid
   252    252     )
   253    253     SELECT count(*) FROM flat;
   254    254   } {15}
   255    255   
   256    256   do_execsql_test 6.4 {
   257    257     WITH x(i) AS (
   258    258       SELECT 1