/ Check-in [a5c2a54a]
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:Add code to handle recursive CTEs.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | common-table-expr
Files: files | file ages | folders
SHA1: a5c2a54a07d35166911abc792008c05dea897742
User & Date: dan 2014-01-14 20:14:09
Context
2014-01-15
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: a2933023 user: drh tags: common-table-expr
2014-01-14
20:14
Add code to handle recursive CTEs. check-in: a5c2a54a user: dan tags: common-table-expr
2014-01-13
16:36
Fix some memory leaks and crashes that could follow an OOM condition during WITH clause parsing. check-in: 8839850c user: dan tags: common-table-expr
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

7346
7347
7348
7349
7350
7351
7352

7353
7354
7355
7356
7357
7358
7359
7360

7361
7362
7363
7364
7365
7366
7367
7368
7369
7370
7371
7372
7373
7374
7375
7376
7377
7378
7379
7380
7381
7382
7383
7384
7385
7386
7387
  int freePageFlag,        /* Deallocate page if true */
  int *pnChange            /* Add number of Cells freed to this counter */
){
  MemPage *pPage;
  int rc;
  unsigned char *pCell;
  int i;


  assert( sqlite3_mutex_held(pBt->mutex) );
  if( pgno>btreePagecount(pBt) ){
    return SQLITE_CORRUPT_BKPT;
  }

  rc = getAndInitPage(pBt, pgno, &pPage, 0);
  if( rc ) return rc;

  for(i=0; i<pPage->nCell; i++){
    pCell = findCell(pPage, i);
    if( !pPage->leaf ){
      rc = clearDatabasePage(pBt, get4byte(pCell), 1, pnChange);
      if( rc ) goto cleardatabasepage_out;
    }
    rc = clearCell(pPage, pCell);
    if( rc ) goto cleardatabasepage_out;
  }
  if( !pPage->leaf ){
    rc = clearDatabasePage(pBt, get4byte(&pPage->aData[8]), 1, pnChange);
    if( rc ) goto cleardatabasepage_out;
  }else if( pnChange ){
    assert( pPage->intKey );
    *pnChange += pPage->nCell;
  }
  if( freePageFlag ){
    freePage(pPage, &rc);
  }else if( (rc = sqlite3PagerWrite(pPage->pDbPage))==0 ){
    zeroPage(pPage, pPage->aData[0] | PTF_LEAF);
  }

cleardatabasepage_out:
  releasePage(pPage);
  return rc;
}








>








>










|








|







7346
7347
7348
7349
7350
7351
7352
7353
7354
7355
7356
7357
7358
7359
7360
7361
7362
7363
7364
7365
7366
7367
7368
7369
7370
7371
7372
7373
7374
7375
7376
7377
7378
7379
7380
7381
7382
7383
7384
7385
7386
7387
7388
7389
  int freePageFlag,        /* Deallocate page if true */
  int *pnChange            /* Add number of Cells freed to this counter */
){
  MemPage *pPage;
  int rc;
  unsigned char *pCell;
  int i;
  int hdr;

  assert( sqlite3_mutex_held(pBt->mutex) );
  if( pgno>btreePagecount(pBt) ){
    return SQLITE_CORRUPT_BKPT;
  }

  rc = getAndInitPage(pBt, pgno, &pPage, 0);
  if( rc ) return rc;
  hdr = pPage->hdrOffset;
  for(i=0; i<pPage->nCell; i++){
    pCell = findCell(pPage, i);
    if( !pPage->leaf ){
      rc = clearDatabasePage(pBt, get4byte(pCell), 1, pnChange);
      if( rc ) goto cleardatabasepage_out;
    }
    rc = clearCell(pPage, pCell);
    if( rc ) goto cleardatabasepage_out;
  }
  if( !pPage->leaf ){
    rc = clearDatabasePage(pBt, get4byte(&pPage->aData[hdr+8]), 1, pnChange);
    if( rc ) goto cleardatabasepage_out;
  }else if( pnChange ){
    assert( pPage->intKey );
    *pnChange += pPage->nCell;
  }
  if( freePageFlag ){
    freePage(pPage, &rc);
  }else if( (rc = sqlite3PagerWrite(pPage->pDbPage))==0 ){
    zeroPage(pPage, pPage->aData[hdr] | PTF_LEAF);
  }

cleardatabasepage_out:
  releasePage(pPage);
  return rc;
}

Changes to src/expr.c.

1051
1052
1053
1054
1055
1056
1057

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

  return pNew;
}
#else
Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){
  assert( p==0 );
  return 0;
}







>







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

Changes to src/select.c.

687
688
689
690
691
692
693

694
695
696
697
698
699













700
701
702
703
704
705
706
....
1725
1726
1727
1728
1729
1730
1731

1732
1733
1734
1735
1736
1737
1738
....
1769
1770
1771
1772
1773
1774
1775
1776










1777
1778
1779
1780
1781




























































1782
1783
1784
1785
1786
1787
1788
....
3445
3446
3447
3448
3449
3450
3451



































































3452
3453
3454
3455
3456
3457
3458
....
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
3527
3528
3529
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
3547
3548
3549
....
3827
3828
3829
3830
3831
3832
3833
3834
3835
3836

3837
3838
3839
3840
3841
3842
3843
      sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nColumn);
      break;
    }
#endif

    /* Store the result as data using a unique key.
    */

    case SRT_Table:
    case SRT_EphemTab: {
      int r1 = sqlite3GetTempReg(pParse);
      testcase( eDest==SRT_Table );
      testcase( eDest==SRT_EphemTab );
      sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);













      if( pOrderBy ){
        pushOntoSorter(pParse, pOrderBy, p, r1);
      }else{
        int r2 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
        sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
        sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
................................................................................
  int iSub2;            /* EQP id of right-hand query */
#endif

  /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  */
  assert( p && p->pPrior );  /* Calling function guarantees this much */

  db = pParse->db;
  pPrior = p->pPrior;
  assert( pPrior->pRightmost!=pPrior );
  assert( pPrior->pRightmost==p->pRightmost );
  dest = *pDest;
  if( pPrior->pOrderBy ){
    sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
................................................................................
    }else{
      sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
        " do not have the same number of result columns", selectOpName(p->op));
    }
    rc = 1;
    goto multi_select_end;
  }











  /* Compound SELECTs that have an ORDER BY clause are handled separately.
  */
  if( p->pOrderBy ){
    return multiSelectOrderBy(pParse, p, pDest);
  }





























































  /* Generate code for the left and right SELECT statements.
  */
  switch( p->op ){
    case TK_ALL: {
      int addr = 0;
      int nLimit;
................................................................................
static void ctePop(Parse *pParse, struct Cte *pCte){
  if( pCte ){
    assert( pParse->pCte==pCte );
    pParse->pCte = pCte->pOuterCte;
  }
}





































































/*
** This routine is a Walker callback for "expanding" a SELECT statement.
** "Expanding" means to do the following:
**
**    (1)  Make sure VDBE cursor numbers have been assigned to every
**         element of the FROM clause.
................................................................................
      ** to go further. */
      assert( i==0 );
      return WRC_Prune;
    }
#ifndef SQLITE_OMIT_CTE
    pCte = searchWith(pParse, pFrom);
    if( pCte ){
      pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0);
      if( pFrom->pSelect==0 ) return WRC_Abort;
    }
#endif
    if( pFrom->zName==0 || pCte ){
#ifndef SQLITE_OMIT_SUBQUERY
      ExprList *pEList;
      Select *pSel = pFrom->pSelect;
      /* A sub-query in the FROM clause of a SELECT */
      assert( pSel!=0 );
      assert( pFrom->pTab==0 );
      if( ctePush(pParse, pCte) ) return WRC_Abort;
      sqlite3WalkSelect(pWalker, pSel);
      ctePop(pParse, pCte);
      pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
      if( pTab==0 ) return WRC_Abort;
      pTab->nRef = 1;
      pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);
      while( pSel->pPrior ){ pSel = pSel->pPrior; }
      if( pCte && pCte->pCols ){
        pEList = pCte->pCols;
      }else{
        pEList = pSel->pEList;
      }
      selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
      pTab->iPKey = -1;
      pTab->nRowEst = 1048576;
      pTab->tabFlags |= TF_Ephemeral;
#endif
    }else{
      /* An ordinary table or view name in the FROM clause */
      assert( pFrom->pTab==0 );
................................................................................
    pParse = pWalker->pParse;
    pTabList = p->pSrc;
    for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
      Table *pTab = pFrom->pTab;
      if( ALWAYS(pTab!=0) && (pTab->tabFlags & TF_Ephemeral)!=0 ){
        /* A sub-query in the FROM clause of a SELECT */
        Select *pSel = pFrom->pSelect;
        assert( pSel );
        while( pSel->pPrior ) pSel = pSel->pPrior;
        selectAddColumnTypeAndCollation(pParse, pTab, pSel);

      }
    }
  }
  return WRC_Continue;
}
#endif








>






>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







 








>
>
>
>
>
>
>
>
>
>





>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
|
<

|

<




<

<





<
<
<
<
<
|







 







|
|
|
>







687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
....
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
....
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
....
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
....
3663
3664
3665
3666
3667
3668
3669
3670
3671

3672
3673
3674

3675
3676
3677
3678

3679

3680
3681
3682
3683
3684





3685
3686
3687
3688
3689
3690
3691
3692
....
3970
3971
3972
3973
3974
3975
3976
3977
3978
3979
3980
3981
3982
3983
3984
3985
3986
3987
      sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nColumn);
      break;
    }
#endif

    /* Store the result as data using a unique key.
    */
    case SRT_DistTable:
    case SRT_Table:
    case SRT_EphemTab: {
      int r1 = sqlite3GetTempReg(pParse);
      testcase( eDest==SRT_Table );
      testcase( eDest==SRT_EphemTab );
      sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
#ifndef SQLITE_OMIT_CTE
      if( eDest==SRT_DistTable ){
        /* If the destination is DistTable, then cursor (iParm+1) is open
        ** on an ephemeral index. If the current row is already present
        ** in the index, do not write it to the output. If not, add the
        ** current row to the index and proceed with writing it to the
        ** output table as well.  */
        int addr = sqlite3VdbeCurrentAddr(v) + 4;
        sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm+1, r1);
        assert( pOrderBy==0 );
      }
#endif
      if( pOrderBy ){
        pushOntoSorter(pParse, pOrderBy, p, r1);
      }else{
        int r2 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
        sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
        sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
................................................................................
  int iSub2;            /* EQP id of right-hand query */
#endif

  /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  */
  assert( p && p->pPrior );  /* Calling function guarantees this much */
  assert( p->pRecurse==0 || p->op==TK_ALL || p->op==TK_UNION );
  db = pParse->db;
  pPrior = p->pPrior;
  assert( pPrior->pRightmost!=pPrior );
  assert( pPrior->pRightmost==p->pRightmost );
  dest = *pDest;
  if( pPrior->pOrderBy ){
    sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
................................................................................
    }else{
      sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
        " do not have the same number of result columns", selectOpName(p->op));
    }
    rc = 1;
    goto multi_select_end;
  }

  /* If this is a recursive query, check that there is no ORDER BY or
  ** LIMIT clause. Neither of these are supported.  */
  assert( p->pOffset==0 || p->pLimit );
  if( p->pRecurse && (p->pOrderBy || p->pLimit) ){
    sqlite3ErrorMsg(pParse, "%s in a recursive query is not allowed",
        p->pOrderBy ? "ORDER BY" : "LIMIT"
    );
    goto multi_select_end;
  }

  /* Compound SELECTs that have an ORDER BY clause are handled separately.
  */
  if( p->pOrderBy ){
    return multiSelectOrderBy(pParse, p, pDest);
  }

#ifndef SQLITE_OMIT_CTE
  if( p->pRecurse ){
    int nCol = p->pEList->nExpr;
    int addrNext;
    int addrSwap;
    int iCont, iBreak;
    int tmp1, tmp2;               /* Cursors used to access temporary tables */
    int tmp3 = 0;                 /* To ensure unique results if UNION */
    int eDest = SRT_Table;
    SelectDest tmp2dest;

    iBreak = sqlite3VdbeMakeLabel(v);
    iCont = sqlite3VdbeMakeLabel(v);

    tmp1 = pParse->nTab++;
    tmp2 = pParse->nTab++;
    if( p->op==TK_UNION ){
      eDest = SRT_DistTable;
      tmp3 = pParse->nTab++;
    }
    sqlite3SelectDestInit(&tmp2dest, eDest, tmp2);

    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp1, nCol);
    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp2, nCol);
    if( tmp3 ){
      p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp3, 0);
      p->selFlags |= SF_UsesEphemeral;
    }

    /* Store the results of the initial SELECT in tmp2. */
    rc = sqlite3Select(pParse, pPrior, &tmp2dest);
    if( rc ) goto multi_select_end;

    /* Clear tmp1. Then switch the contents of tmp1 and tmp2. Then teturn 
    ** the contents of tmp1 to the caller. Or, if tmp1 is empty at this
    ** point, the recursive query has finished - jump to address iBreak.  */
    addrSwap = sqlite3VdbeAddOp2(v, OP_SwapCursors, tmp1, tmp2);
    sqlite3VdbeAddOp2(v, OP_Rewind, tmp1, iBreak);
    addrNext = sqlite3VdbeCurrentAddr(v);
    selectInnerLoop(pParse, p, p->pEList, tmp1, p->pEList->nExpr,
        0, 0, &dest, iCont, iBreak);
    sqlite3VdbeResolveLabel(v, iCont);
    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

  /* Generate code for the left and right SELECT statements.
  */
  switch( p->op ){
    case TK_ALL: {
      int addr = 0;
      int nLimit;
................................................................................
static void ctePop(Parse *pParse, struct Cte *pCte){
  if( pCte ){
    assert( pParse->pCte==pCte );
    pParse->pCte = pCte->pOuterCte;
  }
}

static int withExpand(
  Walker *pWalker, 
  struct SrcList_item *pFrom,
  struct Cte *pCte
){
  Table *pTab;
  Parse *pParse = pWalker->pParse;
  sqlite3 *db = pParse->db;

  assert( pFrom->pSelect==0 );
  assert( pFrom->pTab==0 );

  if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
    /* This is the recursive part of a recursive CTE */
    pFrom->pTab = pTab;
    pTab->nRef++;
  }else{
    ExprList *pEList;
    Select *pSel;
    int bRecursive;

    pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
    if( pTab==0 ) return WRC_Abort;
    pTab->nRef = 1;
    pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);
    pTab->iPKey = -1;
    pTab->nRowEst = 1048576;
    pTab->tabFlags |= TF_Ephemeral;
    pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0);
    if( db->mallocFailed ) return SQLITE_NOMEM;
    assert( pFrom->pSelect );

    if( ctePush(pParse, pCte) ) return WRC_Abort;
    pSel = pFrom->pSelect;
    bRecursive = (pSel->op==TK_ALL || pSel->op==TK_UNION);
    if( bRecursive ){
      assert( pSel->pPrior );
      sqlite3WalkSelect(pWalker, pSel->pPrior);
    }else{
      sqlite3WalkSelect(pWalker, pSel);
    }

    if( pCte->pCols ){
      pEList = pCte->pCols;
    }else{
      Select *pLeft;
      for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
      pEList = pLeft->pEList;
    }
    selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);

    if( bRecursive ){
      int nRef = pTab->nRef;
      pCte->pTab = pTab;
      sqlite3WalkSelect(pWalker, pSel);
      pCte->pTab = 0;
      if( pTab->nRef > nRef){
        pSel->pRecurse = pTab;
        assert( pTab->tnum==0 );
      }
    }

    ctePop(pParse, pCte);
  }

  return SQLITE_OK;
}

/*
** This routine is a Walker callback for "expanding" a SELECT statement.
** "Expanding" means to do the following:
**
**    (1)  Make sure VDBE cursor numbers have been assigned to every
**         element of the FROM clause.
................................................................................
      ** to go further. */
      assert( i==0 );
      return WRC_Prune;
    }
#ifndef SQLITE_OMIT_CTE
    pCte = searchWith(pParse, pFrom);
    if( pCte ){
      if( withExpand(pWalker, pFrom, pCte) ) return WRC_Abort;
    }else

#endif
    if( pFrom->zName==0 ){
#ifndef SQLITE_OMIT_SUBQUERY

      Select *pSel = pFrom->pSelect;
      /* A sub-query in the FROM clause of a SELECT */
      assert( pSel!=0 );
      assert( pFrom->pTab==0 );

      sqlite3WalkSelect(pWalker, pSel);

      pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
      if( pTab==0 ) return WRC_Abort;
      pTab->nRef = 1;
      pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);
      while( pSel->pPrior ){ pSel = pSel->pPrior; }





      selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol);
      pTab->iPKey = -1;
      pTab->nRowEst = 1048576;
      pTab->tabFlags |= TF_Ephemeral;
#endif
    }else{
      /* An ordinary table or view name in the FROM clause */
      assert( pFrom->pTab==0 );
................................................................................
    pParse = pWalker->pParse;
    pTabList = p->pSrc;
    for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
      Table *pTab = pFrom->pTab;
      if( ALWAYS(pTab!=0) && (pTab->tabFlags & TF_Ephemeral)!=0 ){
        /* A sub-query in the FROM clause of a SELECT */
        Select *pSel = pFrom->pSelect;
        if( pSel ){
          while( pSel->pPrior ) pSel = pSel->pPrior;
          selectAddColumnTypeAndCollation(pParse, pTab, pSel);
        }
      }
    }
  }
  return WRC_Continue;
}
#endif

Changes to src/sqliteInt.h.

1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439

1440
1441
1442
1443
1444
1445
1446
....
2125
2126
2127
2128
2129
2130
2131

2132
2133
2134
2135
2136
2137
2138
....
2178
2179
2180
2181
2182
2183
2184

2185
2186
2187
2188
2189
2190
2191
....
2643
2644
2645
2646
2647
2648
2649

2650
2651
2652
2653
2654
2655
2656
#endif
  Trigger *pTrigger;   /* List of triggers stored in pSchema */
  Schema *pSchema;     /* Schema that contains this table */
  Table *pNextZombie;  /* Next on the Parse.pZombieTab list */
};

/*
** Allowed values for Tabe.tabFlags.
*/
#define TF_Readonly        0x01    /* Read-only system table */
#define TF_Ephemeral       0x02    /* An ephemeral table */
#define TF_HasPrimaryKey   0x04    /* Table has a primary key */
#define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
#define TF_Virtual         0x10    /* Is a virtual table */
#define TF_WithoutRowid    0x20    /* No rowid used. PRIMARY KEY is the key */



/*
** Test to see whether or not a table is a virtual table.  This is
** done as a macro so that it will be optimized out when virtual
** table support is omitted from the build.
*/
................................................................................
** enough information about the compound query is known at that point.
** The KeyInfo for addrOpenTran[0] and [1] contains collating sequences
** for the result set.  The KeyInfo for addrOpenEphm[2] contains collating
** sequences for the ORDER BY clause.
*/
struct Select {
  ExprList *pEList;      /* The fields of the result */

  u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  u16 selFlags;          /* Various SF_* values */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
  u64 nSelectRow;        /* Estimated number of result rows */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
................................................................................

#define SRT_Output       5  /* Output each row of result */
#define SRT_Mem          6  /* Store result in a memory cell */
#define SRT_Set          7  /* Store results as keys in an index */
#define SRT_Table        8  /* Store result as data with an automatic rowid */
#define SRT_EphemTab     9  /* Create transient tab and store like SRT_Table */
#define SRT_Coroutine   10  /* Generate a single row of result */


/*
** An instance of this object describes where to put of the results of
** a SELECT statement.
*/
struct SelectDest {
  u8 eDest;         /* How to dispose of the results.  On of SRT_* above. */
................................................................................
  int nCte;                       /* Number of CTEs */
  With *pOuter;                   /* Containing WITH clause, or NULL */
  struct Cte {
    char *zName;                  /* Name of this CTE */
    ExprList *pCols;              /* List of explicit column names, or NULL */
    Select *pSelect;              /* The contents of the CTE */
    struct Cte *pOuterCte;

  } a[1];
};

/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/







|







>







 







>







 







>







 







>







1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
....
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
....
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
....
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
#endif
  Trigger *pTrigger;   /* List of triggers stored in pSchema */
  Schema *pSchema;     /* Schema that contains this table */
  Table *pNextZombie;  /* Next on the Parse.pZombieTab list */
};

/*
** Allowed values for Table.tabFlags.
*/
#define TF_Readonly        0x01    /* Read-only system table */
#define TF_Ephemeral       0x02    /* An ephemeral table */
#define TF_HasPrimaryKey   0x04    /* Table has a primary key */
#define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
#define TF_Virtual         0x10    /* Is a virtual table */
#define TF_WithoutRowid    0x20    /* No rowid used. PRIMARY KEY is the key */
#define TF_Recursive       0x40    /* Recursive reference within CTE */


/*
** Test to see whether or not a table is a virtual table.  This is
** done as a macro so that it will be optimized out when virtual
** table support is omitted from the build.
*/
................................................................................
** enough information about the compound query is known at that point.
** The KeyInfo for addrOpenTran[0] and [1] contains collating sequences
** for the result set.  The KeyInfo for addrOpenEphm[2] contains collating
** sequences for the ORDER BY clause.
*/
struct Select {
  ExprList *pEList;      /* The fields of the result */
  Table *pRecurse;       /* Non-NULL for the recursive part of recursive CTE */
  u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  u16 selFlags;          /* Various SF_* values */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
  u64 nSelectRow;        /* Estimated number of result rows */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
................................................................................

#define SRT_Output       5  /* Output each row of result */
#define SRT_Mem          6  /* Store result in a memory cell */
#define SRT_Set          7  /* Store results as keys in an index */
#define SRT_Table        8  /* Store result as data with an automatic rowid */
#define SRT_EphemTab     9  /* Create transient tab and store like SRT_Table */
#define SRT_Coroutine   10  /* Generate a single row of result */
#define SRT_DistTable   11  /* Like SRT_TABLE, but unique results only */

/*
** An instance of this object describes where to put of the results of
** a SELECT statement.
*/
struct SelectDest {
  u8 eDest;         /* How to dispose of the results.  On of SRT_* above. */
................................................................................
  int nCte;                       /* Number of CTEs */
  With *pOuter;                   /* Containing WITH clause, or NULL */
  struct Cte {
    char *zName;                  /* Name of this CTE */
    ExprList *pCols;              /* List of explicit column names, or NULL */
    Select *pSelect;              /* The contents of the CTE */
    struct Cte *pOuterCte;
    Table *pTab;
  } a[1];
};

/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/

Changes to src/vdbe.c.

3364
3365
3366
3367
3368
3369
3370















































3371
3372
3373
3374
3375
3376
3377
      rc = sqlite3BtreeCursor(pCx->pBt, MASTER_ROOT, 1, 0, pCx->pCursor);
      pCx->isTable = 1;
    }
  }
  pCx->isOrdered = (pOp->p5!=BTREE_UNORDERED);
  break;
}
















































/* Opcode: SorterOpen P1 * * P4 *
**
** This opcode works like OP_OpenEphemeral except that it opens
** a transient index that is specifically designed to sort large
** tables using an external merge-sort algorithm.
*/







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
      rc = sqlite3BtreeCursor(pCx->pBt, MASTER_ROOT, 1, 0, pCx->pCursor);
      pCx->isTable = 1;
    }
  }
  pCx->isOrdered = (pOp->p5!=BTREE_UNORDERED);
  break;
}

/* Opcode: OpenEphreader P1 P2 * * *
**
** P2 is a cursor opened by the OpenEphemeral opcode. This opcode opens
** a new read-only cursor named P1 that accesses the same epheremal table 
** as P2.
*/
case OP_OpenEphreader: {
  VdbeCursor *pEph;
  VdbeCursor *pCx;
  Pgno pgno;

  pEph = p->apCsr[pOp->p2];
  pCx = allocateCursor(p, pOp->p1, pEph->nField, -1, 1);
  if( pCx==0 ) goto no_mem;
  pCx->nullRow = 1;
  pCx->pKeyInfo = pEph->pKeyInfo;
  pCx->isTable = pEph->isTable;
  pCx->isOrdered = pEph->isOrdered;
  pgno = MASTER_ROOT + !pCx->isTable;
  rc = sqlite3BtreeCursor(pEph->pBt, pgno, 0, pCx->pKeyInfo, pCx->pCursor);
  break;
}

/* Opcode: SwapCursors P1 P2 * * *
**
** Parameters P1 and P2 are both cursors opened by the OpenEphemeral
** opcode. This opcode deletes the contents of epheremal table P1,
** then renames P2 to P1 and P1 to P2. In other words, following this
** opcode cursor P2 is open on an empty table and P1 is open on the
** table that was initially accessed by P2.
*/
case OP_SwapCursors: {
  Mem tmp;
  VdbeCursor *pTmp;

  tmp = p->aMem[p->nMem - pOp->p1];
  p->aMem[p->nMem - pOp->p1] = p->aMem[p->nMem - pOp->p2];
  p->aMem[p->nMem - pOp->p2] = tmp;

  pTmp = p->apCsr[pOp->p1];
  p->apCsr[pOp->p1] = p->apCsr[pOp->p2];
  p->apCsr[pOp->p2] = pTmp;

  rc = sqlite3BtreeClearTable(pTmp->pBt, MASTER_ROOT + !pTmp->isTable, 0);
  break;
}

/* Opcode: SorterOpen P1 * * P4 *
**
** This opcode works like OP_OpenEphemeral except that it opens
** a transient index that is specifically designed to sort large
** tables using an external merge-sort algorithm.
*/

Changes to src/where.c.

5649
5650
5651
5652
5653
5654
5655




5656
5657
5658
5659
5660
5661
5662
5663
    struct SrcList_item *pTabItem;

    pTabItem = &pTabList->a[pLevel->iFrom];
    pTab = pTabItem->pTab;
    iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
    pLoop = pLevel->pWLoop;
    if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){




      /* Do nothing */
    }else
#ifndef SQLITE_OMIT_VIRTUALTABLE
    if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0 ){
      const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
      int iCur = pTabItem->iCursor;
      sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, pVTab, P4_VTAB);
    }else if( IsVirtual(pTab) ){







>
>
>
>
|







5649
5650
5651
5652
5653
5654
5655
5656
5657
5658
5659
5660
5661
5662
5663
5664
5665
5666
5667
    struct SrcList_item *pTabItem;

    pTabItem = &pTabList->a[pLevel->iFrom];
    pTab = pTabItem->pTab;
    iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
    pLoop = pLevel->pWLoop;
    if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){
      if( pTab->tabFlags & TF_Recursive ){
        int iCur = pTabItem->iCursor;
        sqlite3VdbeAddOp2(v, OP_OpenEphreader, iCur, pTab->tnum);
      }
      /* Otherwise do nothing */
    }else
#ifndef SQLITE_OMIT_VIRTUALTABLE
    if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0 ){
      const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
      int iCur = pTabItem->iCursor;
      sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, pVTab, P4_VTAB);
    }else if( IsVirtual(pTab) ){

Changes to test/with1.test.

132
133
134
135
136
137
138



























139
140
141
142
143
} {1 3 2 4}

do_execsql_test 4.3 {
  WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
  UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
  SELECT * FROM t1;
} {1 3 8 9}




























finish_test










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>





132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
} {1 3 2 4}

do_execsql_test 4.3 {
  WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
  UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
  SELECT * FROM t1;
} {1 3 8 9}

#-------------------------------------------------------------------------
#
do_execsql_test 5.1 {
  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i)
  SELECT x FROM i LIMIT 10;
} {1 2 3 4 5 6 7 8 9 10}

do_catchsql_test 5.2 {
  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i ORDER BY 1)
  SELECT x FROM i LIMIT 10;
} {1 {ORDER BY in a recursive query is not allowed}}

do_catchsql_test 5.3 {
  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i LIMIT 10 )
  SELECT x FROM i LIMIT 10;
} {1 {LIMIT in a recursive query is not allowed}}

do_execsql_test 5.4 {
  WITH i(x) AS ( VALUES(1) UNION ALL SELECT (x+1)%10 FROM i)
  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