/ Changes On Branch level-pseudocolumn
Login

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

Changes In Branch level-pseudocolumn Excluding Merge-Ins

This is equivalent to a diff from 7d9e22187d to c57deced09

2014-01-21
15:04
Remove the undocumented requirement for applications that use an SQLITE_ENABLE_SQLLOG build to define a sqlite3_init_sqllog() function. (check-in: 5e43bf0132 user: dan tags: trunk)
01:13
Fix a couple comment typos and one overly long line. No functional changes. (Closed-Leaf check-in: c57deced09 user: mistachkin tags: level-pseudocolumn)
00:19
Add support for the LEVEL pseudo-column in the recursive part of a common table expression. LEVEL has the value of 1 on the first iteration and successively larger integer values of subsequent iterations. It cannot have a table qualifier. Actual columns named "level" can still be accessed by including the table name qualifier. (check-in: cc1cb32178 user: drh tags: level-pseudocolumn)
2014-01-20
19:55
In where.c, do not allocate space in sqlite3_index_info structures for the internal WHERE clause "terms" generated to record column equivalencies. Fix for ticket [1a1a194d1e5f8]. (check-in: 7d9e22187d user: dan tags: trunk)
18:25
Handle a few obscure problems that could manifest if a database corrupted in a certain way was written by a connection in the middle of a SELECT statement on the same db. (check-in: eba8a564e6 user: dan tags: trunk)

Changes to addopcodes.awk.

    27     27     printf "#define TK_%-29s %4d\n", "FUNCTION",        ++max
    28     28     printf "#define TK_%-29s %4d\n", "COLUMN",          ++max
    29     29     printf "#define TK_%-29s %4d\n", "AGG_FUNCTION",    ++max
    30     30     printf "#define TK_%-29s %4d\n", "AGG_COLUMN",      ++max
    31     31     printf "#define TK_%-29s %4d\n", "UMINUS",          ++max
    32     32     printf "#define TK_%-29s %4d\n", "UPLUS",           ++max
    33     33     printf "#define TK_%-29s %4d\n", "REGISTER",        ++max
           34  +  printf "#define TK_%-29s %4d\n", "LEVEL",           ++max
    34     35   }

Changes to src/expr.c.

  2454   2454           }
  2455   2455         }
  2456   2456         inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab,
  2457   2457                                  pExpr->iColumn, iTab, target,
  2458   2458                                  pExpr->op2);
  2459   2459         break;
  2460   2460       }
         2461  +#ifndef SQLITE_OMIT_CTE
         2462  +    case TK_LEVEL: {
         2463  +      inReg = pParse->regLevel;
         2464  +      break;
         2465  +    }
         2466  +#endif
  2461   2467       case TK_INTEGER: {
  2462   2468         codeInteger(pParse, pExpr, 0, target);
  2463   2469         break;
  2464   2470       }
  2465   2471   #ifndef SQLITE_OMIT_FLOATING_POINT
  2466   2472       case TK_FLOAT: {
  2467   2473         assert( !ExprHasProperty(pExpr, EP_IntValue) );
................................................................................
  3077   3083   ** VDBE program, in order to factor it out of the evaluation loop.
  3078   3084   */
  3079   3085   int sqlite3ExprCodeTemp(Parse *pParse, Expr *pExpr, int *pReg){
  3080   3086     int r2;
  3081   3087     pExpr = sqlite3ExprSkipCollate(pExpr);
  3082   3088     if( ConstFactorOk(pParse)
  3083   3089      && pExpr->op!=TK_REGISTER
         3090  +   && pExpr->op!=TK_LEVEL
  3084   3091      && sqlite3ExprIsConstantNotJoin(pExpr)
  3085   3092     ){
  3086   3093       ExprList *p = pParse->pConstExpr;
  3087   3094       int i;
  3088   3095       *pReg  = 0;
  3089   3096       if( p ){
  3090   3097         struct ExprList_item *pItem;

Changes to src/resolve.c.

   222    222     int cntTab = 0;                   /* Number of matching table names */
   223    223     int nSubquery = 0;                /* How many levels of subquery */
   224    224     sqlite3 *db = pParse->db;         /* The database connection */
   225    225     struct SrcList_item *pItem;       /* Use for looping over pSrcList items */
   226    226     struct SrcList_item *pMatch = 0;  /* The matching pSrcList item */
   227    227     NameContext *pTopNC = pNC;        /* First namecontext in the list */
   228    228     Schema *pSchema = 0;              /* Schema of the expression */
   229         -  int isTrigger = 0;                /* True if resolved to a trigger column */
          229  +  u8 newOp = TK_COLUMN;             /* pExpr->op after resolving name */
   230    230     Table *pTab = 0;                  /* Table hold the row */
   231    231     Column *pCol;                     /* A column of pTab */
   232    232   
   233    233     assert( pNC );     /* the name context cannot be NULL. */
   234    234     assert( zCol );    /* The Z in X.Y.Z cannot be NULL */
   235    235     assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) );
   236    236   
................................................................................
   262    262       }
   263    263     }
   264    264   
   265    265     /* Start at the inner-most context and move outward until a match is found */
   266    266     while( pNC && cnt==0 ){
   267    267       ExprList *pEList;
   268    268       SrcList *pSrcList = pNC->pSrcList;
          269  +
          270  +#ifndef SQLITE_OMIT_CTE
          271  +    /* The identifier "LEVEL", without a table or database qualifier and
          272  +    ** within a recursive common table expression, resolves to the special
          273  +    ** LEVEL pseudo-column.  To access table names called "level", add a
          274  +    ** table qualifier.
          275  +    */
          276  +    if( (pNC->ncFlags&NC_Recursive)!=0
          277  +     && zTab==0
          278  +     && sqlite3_stricmp(zCol,"level")==0
          279  +    ){
          280  +      assert( cnt==0 );
          281  +      cnt = 1;
          282  +      newOp = TK_LEVEL;
          283  +      pExpr->iColumn = -1;
          284  +      pExpr->affinity = SQLITE_AFF_INTEGER;
          285  +      pNC->ncFlags |= NC_UsesLevel;
          286  +      break;
          287  +    }
          288  +#endif
   269    289   
   270    290       if( pSrcList ){
   271    291         for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){
   272    292           pTab = pItem->pTab;
   273    293           assert( pTab!=0 && pTab->zName!=0 );
   274    294           assert( pTab->nCol>0 );
   275    295           if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){
................................................................................
   367    387             }else{
   368    388               testcase( iCol==31 );
   369    389               testcase( iCol==32 );
   370    390               pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
   371    391             }
   372    392             pExpr->iColumn = (i16)iCol;
   373    393             pExpr->pTab = pTab;
   374         -          isTrigger = 1;
          394  +          newOp = TK_TRIGGER;
   375    395           }
   376    396         }
   377    397       }
   378    398   #endif /* !defined(SQLITE_OMIT_TRIGGER) */
   379    399   
   380    400       /*
   381    401       ** Perhaps the name is a reference to the ROWID
................................................................................
   491    511   
   492    512     /* Clean up and return
   493    513     */
   494    514     sqlite3ExprDelete(db, pExpr->pLeft);
   495    515     pExpr->pLeft = 0;
   496    516     sqlite3ExprDelete(db, pExpr->pRight);
   497    517     pExpr->pRight = 0;
   498         -  pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN);
          518  +  pExpr->op = newOp;
   499    519   lookupname_end:
   500    520     if( cnt==1 ){
   501    521       assert( pNC!=0 );
   502         -    if( pExpr->op!=TK_AS ){
          522  +    if( pExpr->op!=TK_AS && pExpr->op!=TK_LEVEL ){
   503    523         sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList);
   504    524       }
   505    525       /* Increment the nRef value on all name contexts from TopNC up to
   506    526       ** the point where the name matched. */
   507    527       for(;;){
   508    528         assert( pTopNC!=0 );
   509    529         pTopNC->nRef++;
................................................................................
  1192   1212         }
  1193   1213       }
  1194   1214     
  1195   1215       /* Set up the local name-context to pass to sqlite3ResolveExprNames() to
  1196   1216       ** resolve the result-set expression list.
  1197   1217       */
  1198   1218       sNC.ncFlags = NC_AllowAgg;
         1219  +    if( p->selFlags & SF_Recursive ) sNC.ncFlags |= NC_Recursive;
  1199   1220       sNC.pSrcList = p->pSrc;
  1200   1221       sNC.pNext = pOuterNC;
  1201   1222     
  1202   1223       /* Resolve names in the result set. */
  1203   1224       pEList = p->pEList;
  1204   1225       assert( pEList!=0 );
  1205   1226       for(i=0; i<pEList->nExpr; i++){
................................................................................
  1270   1291           if( ExprHasProperty(pItem->pExpr, EP_Agg) ){
  1271   1292             sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
  1272   1293                 "the GROUP BY clause");
  1273   1294             return WRC_Abort;
  1274   1295           }
  1275   1296         }
  1276   1297       }
         1298  +    if( sNC.ncFlags & NC_UsesLevel ){
         1299  +      p->selFlags |= SF_UsesLevel;
         1300  +    }
         1301  +    sNC.ncFlags &= ~(NC_Recursive|NC_UsesLevel);
  1277   1302   
  1278   1303       /* Advance to the next term of the compound
  1279   1304       */
  1280   1305       p = p->pPrior;
  1281   1306       nCompound++;
  1282   1307     }
  1283   1308   

Changes to src/select.c.

  1788   1788       }
  1789   1789       rc = 1;
  1790   1790       goto multi_select_end;
  1791   1791     }
  1792   1792   
  1793   1793   #ifndef SQLITE_OMIT_CTE
  1794   1794     if( p->selFlags & SF_Recursive ){
  1795         -    SrcList *pSrc = p->pSrc;
  1796         -    int nCol = p->pEList->nExpr;
         1795  +    SrcList *pSrc = p->pSrc;      /* The FROM clause of the right-most SELECT */
         1796  +    int nCol = p->pEList->nExpr;  /* Number of columns in the result set */
  1797   1797       int addrNext;
  1798   1798       int addrSwap;
  1799   1799       int iCont, iBreak;
  1800   1800       int tmp1;                     /* Intermediate table */
  1801   1801       int tmp2;                     /* Next intermediate table */
  1802   1802       int tmp3 = 0;                 /* To ensure unique results if UNION */
  1803   1803       int eDest = SRT_Table;
  1804   1804       SelectDest tmp2dest;
  1805   1805       int i;
         1806  +    int regLevel = 0;             /* Register for LEVEL value */
         1807  +    int savedRegLevel;            /* Saved value of pParse->regLevel */
  1806   1808   
  1807   1809       /* Check that there is no ORDER BY or LIMIT clause. Neither of these 
  1808   1810       ** are supported on recursive queries.  */
  1809   1811       assert( p->pOffset==0 || p->pLimit );
  1810   1812       if( p->pOrderBy || p->pLimit ){
  1811   1813         sqlite3ErrorMsg(pParse, "%s in a recursive query",
  1812   1814             p->pOrderBy ? "ORDER BY" : "LIMIT"
................................................................................
  1840   1842         p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp3, 0);
  1841   1843         p->selFlags |= SF_UsesEphemeral;
  1842   1844       }
  1843   1845   
  1844   1846       /* Store the results of the initial SELECT in tmp2. */
  1845   1847       rc = sqlite3Select(pParse, pPrior, &tmp2dest);
  1846   1848       if( rc ) goto multi_select_end;
         1849  +
         1850  +    /* Allocate and initialize a register to hold the LEVEL pseudo-column */
         1851  +    savedRegLevel = pParse->regLevel;
         1852  +    if( p->selFlags & SF_UsesLevel ){
         1853  +      regLevel = pParse->regLevel = ++pParse->nMem;
         1854  +      sqlite3VdbeAddOp2(v, OP_Integer, 0, regLevel);
         1855  +      VdbeComment((v, "level=0"));
         1856  +    }
  1847   1857   
  1848   1858       /* Clear tmp1. Then switch the contents of tmp1 and tmp2. Then return 
  1849   1859       ** the contents of tmp1 to the caller. Or, if tmp1 is empty at this
  1850   1860       ** point, the recursive query has finished - jump to address iBreak.  */
  1851   1861       addrSwap = sqlite3VdbeAddOp2(v, OP_SwapCursors, tmp1, tmp2);
  1852   1862       sqlite3VdbeAddOp2(v, OP_Rewind, tmp1, iBreak);
  1853   1863       addrNext = sqlite3VdbeCurrentAddr(v);
  1854   1864       selectInnerLoop(pParse, p, p->pEList, tmp1, p->pEList->nExpr,
  1855   1865           0, 0, &dest, iCont, iBreak);
  1856   1866       sqlite3VdbeResolveLabel(v, iCont);
  1857   1867       sqlite3VdbeAddOp2(v, OP_Next, tmp1, addrNext);
         1868  +    if( regLevel ){
         1869  +      sqlite3VdbeAddOp2(v, OP_AddImm, regLevel, 1);
         1870  +      VdbeComment((v, "level++"));
         1871  +    }
  1858   1872   
  1859   1873       /* Execute the recursive SELECT. Store the results in tmp2. While this
  1860   1874       ** SELECT is running, the contents of tmp1 are read by recursive 
  1861   1875       ** references to the current CTE.  */
  1862   1876       p->pPrior = 0;
  1863   1877       rc = sqlite3Select(pParse, p, &tmp2dest);
  1864   1878       assert( p->pPrior==0 );
  1865   1879       p->pPrior = pPrior;
  1866   1880       if( rc ) goto multi_select_end;
  1867   1881   
  1868   1882       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap);
  1869   1883       sqlite3VdbeResolveLabel(v, iBreak);
         1884  +    pParse->regLevel = savedRegLevel;
  1870   1885     }else
  1871   1886   #endif
  1872   1887   
  1873   1888     /* Compound SELECTs that have an ORDER BY clause are handled separately.
  1874   1889     */
  1875   1890     if( p->pOrderBy ){
  1876   1891       return multiSelectOrderBy(pParse, p, pDest);

Changes to src/sqliteInt.h.

  2103   2103   ** Allowed values for the NameContext, ncFlags field.
  2104   2104   */
  2105   2105   #define NC_AllowAgg  0x01    /* Aggregate functions are allowed here */
  2106   2106   #define NC_HasAgg    0x02    /* One or more aggregate functions seen */
  2107   2107   #define NC_IsCheck   0x04    /* True if resolving names in a CHECK constraint */
  2108   2108   #define NC_InAggFunc 0x08    /* True if analyzing arguments to an agg func */
  2109   2109   #define NC_PartIdx   0x10    /* True if resolving a partial index WHERE */
         2110  +#define NC_Recursive 0x20    /* Resolving a recursive CTE definition */
         2111  +#define NC_UsesLevel 0x40    /* The LEVEL pseudo-column has been seen */
  2110   2112   
  2111   2113   /*
  2112   2114   ** An instance of the following structure contains all information
  2113   2115   ** needed to generate code for a single SELECT statement.
  2114   2116   **
  2115   2117   ** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
  2116   2118   ** If there is a LIMIT clause, the parser sets nLimit to the value of the
................................................................................
  2160   2162   #define SF_HasTypeInfo     0x0020  /* FROM subqueries have Table metadata */
  2161   2163   #define SF_UseSorter       0x0040  /* Sort using a sorter */
  2162   2164   #define SF_Values          0x0080  /* Synthesized from VALUES clause */
  2163   2165   #define SF_Materialize     0x0100  /* Force materialization of views */
  2164   2166   #define SF_NestedFrom      0x0200  /* Part of a parenthesized FROM clause */
  2165   2167   #define SF_MaybeConvert    0x0400  /* Need convertCompoundSelectToSubquery() */
  2166   2168   #define SF_Recursive       0x0800  /* The recursive part of a recursive CTE */
         2169  +#define SF_UsesLevel       0x1000  /* Uses the LEVEL pseudo-column */
  2167   2170   
  2168   2171   
  2169   2172   /*
  2170   2173   ** The results of a select can be distributed in several ways.  The
  2171   2174   ** "SRT" prefix means "SELECT Result Type".
  2172   2175   */
  2173   2176   #define SRT_Union        1  /* Store result as keys in an index */
................................................................................
  2366   2369   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2367   2370     Token sArg;               /* Complete text of a module argument */
  2368   2371     Table **apVtabLock;       /* Pointer to virtual tables needing locking */
  2369   2372   #endif
  2370   2373     Table *pZombieTab;        /* List of Table objects to delete after code gen */
  2371   2374     TriggerPrg *pTriggerPrg;  /* Linked list of coded triggers */
  2372   2375     With *pWith;              /* Current WITH clause, or NULL */
         2376  +  int regLevel;             /* Register holding the LEVEL variable */
  2373   2377     u8 bFreeWith;             /* True if pWith should be freed with parser */
  2374   2378   };
  2375   2379   
  2376   2380   /*
  2377   2381   ** Return true if currently inside an sqlite3_declare_vtab() call.
  2378   2382   */
  2379   2383   #ifdef SQLITE_OMIT_VIRTUALTABLE

Added test/with3.test.

            1  +# 2014 January 11
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the WITH clause and in particular the
           13  +# LEVEL pseudo-column
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set ::testprefix with3
           19  +
           20  +ifcapable {!cte} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +do_execsql_test 1.0 {
           26  +  WITH RECURSIVE
           27  +    cnt(x) AS (VALUES(10) UNION SELECT x+1 FROM cnt WHERE level<10)
           28  +  SELECT * FROM cnt;
           29  +} {10 11 12 13 14 15 16 17 18 19}
           30  +do_execsql_test 1.1 {
           31  +  WITH RECURSIVE
           32  +    cnt(x,y) AS (VALUES(10,0) UNION SELECT x+1,level FROM cnt WHERE level<6)
           33  +  SELECT x, y, '|' FROM cnt;
           34  +} {10 0 | 11 1 | 12 2 | 13 3 | 14 4 | 15 5 |}
           35  +do_execsql_test 1.2 {
           36  +  WITH RECURSIVE
           37  +    cnt(x,level) AS (
           38  +       VALUES(10,99)
           39  +       UNION
           40  +       SELECT x+1, level FROM cnt WHERE level<6
           41  +  )
           42  +  SELECT x, level, '|' FROM cnt;
           43  +} {10 99 | 11 1 | 12 2 | 13 3 | 14 4 | 15 5 |}
           44  +do_execsql_test 1.3 {
           45  +  WITH RECURSIVE
           46  +    cnt(x,level) AS (
           47  +       VALUES(10,99)
           48  +       UNION
           49  +       SELECT x+1, cnt.level FROM cnt WHERE level<6
           50  +  )
           51  +  SELECT x, level, '|' FROM cnt;
           52  +} {10 99 | 11 99 | 12 99 | 13 99 | 14 99 | 15 99 |}
           53  +do_execsql_test 1.4 {
           54  +  WITH RECURSIVE
           55  +    cnt(x,level) AS (
           56  +       VALUES(10,0)
           57  +       UNION
           58  +       SELECT x+1, cnt.level+level FROM cnt WHERE level<6
           59  +  )
           60  +  SELECT x, level, '|' FROM cnt;
           61  +} {10 0 | 11 1 | 12 3 | 13 6 | 14 10 | 15 15 |}
           62  +do_execsql_test 1.5 {
           63  +  CREATE TABLE t1(level);
           64  +  WITH RECURSIVE
           65  +    cnt(x) AS (VALUES(10) UNION SELECT x*10 FROM cnt WHERE level<4)
           66  +  INSERT INTO t1 SELECT x FROM cnt;
           67  +  SELECT * FROM t1;
           68  +} {10 100 1000 10000}
           69  +do_execsql_test 1.6 {
           70  +  WITH RECURSIVE
           71  +    cnt(x, level) AS (
           72  +      VALUES(1,1)
           73  +      UNION
           74  +      SELECT x+1, level*t1.level FROM cnt, t1 WHERE level<3
           75  +    )
           76  +  SELECT x, level FROM cnt ORDER BY x, level;
           77  +} {1 1 2 10 2 100 2 1000 2 10000 3 20 3 200 3 2000 3 20000}
           78  +
           79  +do_execsql_test 1.11 {
           80  +  CREATE TEMP TABLE powersoftwo(a,b);
           81  +  WITH RECURSIVE
           82  +    tmp(a,b) AS (VALUES(0,1) UNION SELECT a+1, b*2 FROM tmp WHERE level<32)
           83  +  INSERT INTO powersoftwo SELECT a, b FROM tmp;
           84  +  WITH RECURSIVE
           85  +    cnt(x,y) AS (
           86  +      VALUES(0,0) UNION
           87  +      SELECT x+1, (x+1)*(SELECT b FROM powersoftwo WHERE a=level) FROM cnt
           88  +       WHERE level<5
           89  +    )
           90  +  SELECT * FROM cnt;
           91  +} {0 0 1 2 2 8 3 24 4 64}
           92  +
           93  +do_catchsql_test 2.1 {
           94  +  WITH RECURSIVE
           95  +    cnt(x) AS (VALUES(1) UNION SELECT x+1 FROM cnt WHERE level<10)
           96  +  SELECT x, level FROM cnt;
           97  +} {1 {no such column: level}}
           98  +do_catchsql_test 2.2 {
           99  +  WITH RECURSIVE
          100  +    cnt(x) AS (VALUES(level) UNION SELECT x+1 FROM cnt WHERE level<10)
          101  +  SELECT x FROM cnt;
          102  +} {1 {no such column: level}}
          103  +
          104  +
          105  +
          106  +finish_test