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 7d9e2218 to c57deced
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: 5e43bf01 user: dan tags: trunk) | |
01:13 | Fix a couple comment typos and one overly long line. No functional changes. (Closed-Leaf check-in: c57deced 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: cc1cb321 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: 7d9e2218 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: eba8a564 user: dan tags: trunk) | |
Changes to addopcodes.awk.
︙ | ︙ | |||
27 28 29 30 31 32 33 34 | printf "#define TK_%-29s %4d\n", "FUNCTION", ++max printf "#define TK_%-29s %4d\n", "COLUMN", ++max printf "#define TK_%-29s %4d\n", "AGG_FUNCTION", ++max printf "#define TK_%-29s %4d\n", "AGG_COLUMN", ++max printf "#define TK_%-29s %4d\n", "UMINUS", ++max printf "#define TK_%-29s %4d\n", "UPLUS", ++max printf "#define TK_%-29s %4d\n", "REGISTER", ++max } | > | 27 28 29 30 31 32 33 34 35 | printf "#define TK_%-29s %4d\n", "FUNCTION", ++max printf "#define TK_%-29s %4d\n", "COLUMN", ++max printf "#define TK_%-29s %4d\n", "AGG_FUNCTION", ++max printf "#define TK_%-29s %4d\n", "AGG_COLUMN", ++max printf "#define TK_%-29s %4d\n", "UMINUS", ++max printf "#define TK_%-29s %4d\n", "UPLUS", ++max printf "#define TK_%-29s %4d\n", "REGISTER", ++max printf "#define TK_%-29s %4d\n", "LEVEL", ++max } |
Changes to src/expr.c.
︙ | ︙ | |||
2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 | } } inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab, pExpr->iColumn, iTab, target, pExpr->op2); break; } case TK_INTEGER: { codeInteger(pParse, pExpr, 0, target); break; } #ifndef SQLITE_OMIT_FLOATING_POINT case TK_FLOAT: { assert( !ExprHasProperty(pExpr, EP_IntValue) ); | > > > > > > | 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 | } } inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab, pExpr->iColumn, iTab, target, pExpr->op2); break; } #ifndef SQLITE_OMIT_CTE case TK_LEVEL: { inReg = pParse->regLevel; break; } #endif case TK_INTEGER: { codeInteger(pParse, pExpr, 0, target); break; } #ifndef SQLITE_OMIT_FLOATING_POINT case TK_FLOAT: { assert( !ExprHasProperty(pExpr, EP_IntValue) ); |
︙ | ︙ | |||
3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 | ** VDBE program, in order to factor it out of the evaluation loop. */ int sqlite3ExprCodeTemp(Parse *pParse, Expr *pExpr, int *pReg){ int r2; pExpr = sqlite3ExprSkipCollate(pExpr); if( ConstFactorOk(pParse) && pExpr->op!=TK_REGISTER && sqlite3ExprIsConstantNotJoin(pExpr) ){ ExprList *p = pParse->pConstExpr; int i; *pReg = 0; if( p ){ struct ExprList_item *pItem; | > | 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 3094 3095 3096 3097 | ** VDBE program, in order to factor it out of the evaluation loop. */ int sqlite3ExprCodeTemp(Parse *pParse, Expr *pExpr, int *pReg){ int r2; pExpr = sqlite3ExprSkipCollate(pExpr); if( ConstFactorOk(pParse) && pExpr->op!=TK_REGISTER && pExpr->op!=TK_LEVEL && sqlite3ExprIsConstantNotJoin(pExpr) ){ ExprList *p = pParse->pConstExpr; int i; *pReg = 0; if( p ){ struct ExprList_item *pItem; |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
222 223 224 225 226 227 228 | int cntTab = 0; /* Number of matching table names */ int nSubquery = 0; /* How many levels of subquery */ sqlite3 *db = pParse->db; /* The database connection */ struct SrcList_item *pItem; /* Use for looping over pSrcList items */ struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ | | | 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 | int cntTab = 0; /* Number of matching table names */ int nSubquery = 0; /* How many levels of subquery */ sqlite3 *db = pParse->db; /* The database connection */ struct SrcList_item *pItem; /* Use for looping over pSrcList items */ struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ u8 newOp = TK_COLUMN; /* pExpr->op after resolving name */ Table *pTab = 0; /* Table hold the row */ Column *pCol; /* A column of pTab */ assert( pNC ); /* the name context cannot be NULL. */ assert( zCol ); /* The Z in X.Y.Z cannot be NULL */ assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) ); |
︙ | ︙ | |||
262 263 264 265 266 267 268 269 270 271 272 273 274 275 | } } /* Start at the inner-most context and move outward until a match is found */ while( pNC && cnt==0 ){ ExprList *pEList; SrcList *pSrcList = pNC->pSrcList; if( pSrcList ){ for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){ pTab = pItem->pTab; assert( pTab!=0 && pTab->zName!=0 ); assert( pTab->nCol>0 ); if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){ | > > > > > > > > > > > > > > > > > > > > | 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 | } } /* Start at the inner-most context and move outward until a match is found */ while( pNC && cnt==0 ){ ExprList *pEList; SrcList *pSrcList = pNC->pSrcList; #ifndef SQLITE_OMIT_CTE /* The identifier "LEVEL", without a table or database qualifier and ** within a recursive common table expression, resolves to the special ** LEVEL pseudo-column. To access table names called "level", add a ** table qualifier. */ if( (pNC->ncFlags&NC_Recursive)!=0 && zTab==0 && sqlite3_stricmp(zCol,"level")==0 ){ assert( cnt==0 ); cnt = 1; newOp = TK_LEVEL; pExpr->iColumn = -1; pExpr->affinity = SQLITE_AFF_INTEGER; pNC->ncFlags |= NC_UsesLevel; break; } #endif if( pSrcList ){ for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){ pTab = pItem->pTab; assert( pTab!=0 && pTab->zName!=0 ); assert( pTab->nCol>0 ); if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){ |
︙ | ︙ | |||
367 368 369 370 371 372 373 | }else{ testcase( iCol==31 ); testcase( iCol==32 ); pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); } pExpr->iColumn = (i16)iCol; pExpr->pTab = pTab; | | | 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 | }else{ testcase( iCol==31 ); testcase( iCol==32 ); pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); } pExpr->iColumn = (i16)iCol; pExpr->pTab = pTab; newOp = TK_TRIGGER; } } } #endif /* !defined(SQLITE_OMIT_TRIGGER) */ /* ** Perhaps the name is a reference to the ROWID |
︙ | ︙ | |||
491 492 493 494 495 496 497 | /* Clean up and return */ sqlite3ExprDelete(db, pExpr->pLeft); pExpr->pLeft = 0; sqlite3ExprDelete(db, pExpr->pRight); pExpr->pRight = 0; | | | | 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 | /* Clean up and return */ sqlite3ExprDelete(db, pExpr->pLeft); pExpr->pLeft = 0; sqlite3ExprDelete(db, pExpr->pRight); pExpr->pRight = 0; pExpr->op = newOp; lookupname_end: if( cnt==1 ){ assert( pNC!=0 ); if( pExpr->op!=TK_AS && pExpr->op!=TK_LEVEL ){ sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList); } /* Increment the nRef value on all name contexts from TopNC up to ** the point where the name matched. */ for(;;){ assert( pTopNC!=0 ); pTopNC->nRef++; |
︙ | ︙ | |||
1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 | } } /* Set up the local name-context to pass to sqlite3ResolveExprNames() to ** resolve the result-set expression list. */ sNC.ncFlags = NC_AllowAgg; sNC.pSrcList = p->pSrc; sNC.pNext = pOuterNC; /* Resolve names in the result set. */ pEList = p->pEList; assert( pEList!=0 ); for(i=0; i<pEList->nExpr; i++){ | > | 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 | } } /* Set up the local name-context to pass to sqlite3ResolveExprNames() to ** resolve the result-set expression list. */ sNC.ncFlags = NC_AllowAgg; if( p->selFlags & SF_Recursive ) sNC.ncFlags |= NC_Recursive; sNC.pSrcList = p->pSrc; sNC.pNext = pOuterNC; /* Resolve names in the result set. */ pEList = p->pEList; assert( pEList!=0 ); for(i=0; i<pEList->nExpr; i++){ |
︙ | ︙ | |||
1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 | if( ExprHasProperty(pItem->pExpr, EP_Agg) ){ sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in " "the GROUP BY clause"); return WRC_Abort; } } } /* Advance to the next term of the compound */ p = p->pPrior; nCompound++; } | > > > > | 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 | if( ExprHasProperty(pItem->pExpr, EP_Agg) ){ sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in " "the GROUP BY clause"); return WRC_Abort; } } } if( sNC.ncFlags & NC_UsesLevel ){ p->selFlags |= SF_UsesLevel; } sNC.ncFlags &= ~(NC_Recursive|NC_UsesLevel); /* Advance to the next term of the compound */ p = p->pPrior; nCompound++; } |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
1788 1789 1790 1791 1792 1793 1794 | } rc = 1; goto multi_select_end; } #ifndef SQLITE_OMIT_CTE if( p->selFlags & SF_Recursive ){ | | | > > | 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 | } rc = 1; goto multi_select_end; } #ifndef SQLITE_OMIT_CTE if( p->selFlags & SF_Recursive ){ SrcList *pSrc = p->pSrc; /* The FROM clause of the right-most SELECT */ int nCol = p->pEList->nExpr; /* Number of columns in the result set */ int addrNext; int addrSwap; int iCont, iBreak; int tmp1; /* Intermediate table */ int tmp2; /* Next intermediate table */ int tmp3 = 0; /* To ensure unique results if UNION */ int eDest = SRT_Table; SelectDest tmp2dest; int i; int regLevel = 0; /* Register for LEVEL value */ int savedRegLevel; /* Saved value of pParse->regLevel */ /* Check that there is no ORDER BY or LIMIT clause. Neither of these ** are supported on recursive queries. */ assert( p->pOffset==0 || p->pLimit ); if( p->pOrderBy || p->pLimit ){ sqlite3ErrorMsg(pParse, "%s in a recursive query", p->pOrderBy ? "ORDER BY" : "LIMIT" |
︙ | ︙ | |||
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 1874 1875 1876 | 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 return ** 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; rc = sqlite3Select(pParse, p, &tmp2dest); assert( p->pPrior==0 ); p->pPrior = pPrior; if( rc ) goto multi_select_end; sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap); sqlite3VdbeResolveLabel(v, iBreak); }else #endif /* Compound SELECTs that have an ORDER BY clause are handled separately. */ if( p->pOrderBy ){ return multiSelectOrderBy(pParse, p, pDest); | > > > > > > > > > > > > > | 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 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 | 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; /* Allocate and initialize a register to hold the LEVEL pseudo-column */ savedRegLevel = pParse->regLevel; if( p->selFlags & SF_UsesLevel ){ regLevel = pParse->regLevel = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regLevel); VdbeComment((v, "level=0")); } /* Clear tmp1. Then switch the contents of tmp1 and tmp2. Then return ** 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); if( regLevel ){ sqlite3VdbeAddOp2(v, OP_AddImm, regLevel, 1); VdbeComment((v, "level++")); } /* 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; rc = sqlite3Select(pParse, p, &tmp2dest); assert( p->pPrior==0 ); p->pPrior = pPrior; if( rc ) goto multi_select_end; sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap); sqlite3VdbeResolveLabel(v, iBreak); pParse->regLevel = savedRegLevel; }else #endif /* Compound SELECTs that have an ORDER BY clause are handled separately. */ if( p->pOrderBy ){ return multiSelectOrderBy(pParse, p, pDest); |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 | ** Allowed values for the NameContext, ncFlags field. */ #define NC_AllowAgg 0x01 /* Aggregate functions are allowed here */ #define NC_HasAgg 0x02 /* One or more aggregate functions seen */ #define NC_IsCheck 0x04 /* True if resolving names in a CHECK constraint */ #define NC_InAggFunc 0x08 /* True if analyzing arguments to an agg func */ #define NC_PartIdx 0x10 /* True if resolving a partial index WHERE */ /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** ** nLimit is set to -1 if there is no LIMIT clause. nOffset is set to 0. ** If there is a LIMIT clause, the parser sets nLimit to the value of the | > > | 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 | ** Allowed values for the NameContext, ncFlags field. */ #define NC_AllowAgg 0x01 /* Aggregate functions are allowed here */ #define NC_HasAgg 0x02 /* One or more aggregate functions seen */ #define NC_IsCheck 0x04 /* True if resolving names in a CHECK constraint */ #define NC_InAggFunc 0x08 /* True if analyzing arguments to an agg func */ #define NC_PartIdx 0x10 /* True if resolving a partial index WHERE */ #define NC_Recursive 0x20 /* Resolving a recursive CTE definition */ #define NC_UsesLevel 0x40 /* The LEVEL pseudo-column has been seen */ /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** ** nLimit is set to -1 if there is no LIMIT clause. nOffset is set to 0. ** If there is a LIMIT clause, the parser sets nLimit to the value of the |
︙ | ︙ | |||
2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 | #define SF_HasTypeInfo 0x0020 /* FROM subqueries have Table metadata */ #define SF_UseSorter 0x0040 /* Sort using a sorter */ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ #define SF_Materialize 0x0100 /* Force materialization of views */ #define SF_NestedFrom 0x0200 /* Part of a parenthesized FROM clause */ #define SF_MaybeConvert 0x0400 /* Need convertCompoundSelectToSubquery() */ #define SF_Recursive 0x0800 /* The recursive part of a recursive CTE */ /* ** The results of a select can be distributed in several ways. The ** "SRT" prefix means "SELECT Result Type". */ #define SRT_Union 1 /* Store result as keys in an index */ | > | 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 | #define SF_HasTypeInfo 0x0020 /* FROM subqueries have Table metadata */ #define SF_UseSorter 0x0040 /* Sort using a sorter */ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ #define SF_Materialize 0x0100 /* Force materialization of views */ #define SF_NestedFrom 0x0200 /* Part of a parenthesized FROM clause */ #define SF_MaybeConvert 0x0400 /* Need convertCompoundSelectToSubquery() */ #define SF_Recursive 0x0800 /* The recursive part of a recursive CTE */ #define SF_UsesLevel 0x1000 /* Uses the LEVEL pseudo-column */ /* ** The results of a select can be distributed in several ways. The ** "SRT" prefix means "SELECT Result Type". */ #define SRT_Union 1 /* Store result as keys in an index */ |
︙ | ︙ | |||
2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 | #ifndef SQLITE_OMIT_VIRTUALTABLE Token sArg; /* Complete text of a module argument */ Table **apVtabLock; /* Pointer to virtual tables needing locking */ #endif Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ With *pWith; /* Current WITH clause, or NULL */ u8 bFreeWith; /* True if pWith should be freed with parser */ }; /* ** Return true if currently inside an sqlite3_declare_vtab() call. */ #ifdef SQLITE_OMIT_VIRTUALTABLE | > | 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 | #ifndef SQLITE_OMIT_VIRTUALTABLE Token sArg; /* Complete text of a module argument */ Table **apVtabLock; /* Pointer to virtual tables needing locking */ #endif Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ With *pWith; /* Current WITH clause, or NULL */ int regLevel; /* Register holding the LEVEL variable */ u8 bFreeWith; /* True if pWith should be freed with parser */ }; /* ** Return true if currently inside an sqlite3_declare_vtab() call. */ #ifdef SQLITE_OMIT_VIRTUALTABLE |
︙ | ︙ |
Added test/with3.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | # 2014 January 11 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the WITH clause and in particular the # LEVEL pseudo-column # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix with3 ifcapable {!cte} { finish_test return } do_execsql_test 1.0 { WITH RECURSIVE cnt(x) AS (VALUES(10) UNION SELECT x+1 FROM cnt WHERE level<10) SELECT * FROM cnt; } {10 11 12 13 14 15 16 17 18 19} do_execsql_test 1.1 { WITH RECURSIVE cnt(x,y) AS (VALUES(10,0) UNION SELECT x+1,level FROM cnt WHERE level<6) SELECT x, y, '|' FROM cnt; } {10 0 | 11 1 | 12 2 | 13 3 | 14 4 | 15 5 |} do_execsql_test 1.2 { WITH RECURSIVE cnt(x,level) AS ( VALUES(10,99) UNION SELECT x+1, level FROM cnt WHERE level<6 ) SELECT x, level, '|' FROM cnt; } {10 99 | 11 1 | 12 2 | 13 3 | 14 4 | 15 5 |} do_execsql_test 1.3 { WITH RECURSIVE cnt(x,level) AS ( VALUES(10,99) UNION SELECT x+1, cnt.level FROM cnt WHERE level<6 ) SELECT x, level, '|' FROM cnt; } {10 99 | 11 99 | 12 99 | 13 99 | 14 99 | 15 99 |} do_execsql_test 1.4 { WITH RECURSIVE cnt(x,level) AS ( VALUES(10,0) UNION SELECT x+1, cnt.level+level FROM cnt WHERE level<6 ) SELECT x, level, '|' FROM cnt; } {10 0 | 11 1 | 12 3 | 13 6 | 14 10 | 15 15 |} do_execsql_test 1.5 { CREATE TABLE t1(level); WITH RECURSIVE cnt(x) AS (VALUES(10) UNION SELECT x*10 FROM cnt WHERE level<4) INSERT INTO t1 SELECT x FROM cnt; SELECT * FROM t1; } {10 100 1000 10000} do_execsql_test 1.6 { WITH RECURSIVE cnt(x, level) AS ( VALUES(1,1) UNION SELECT x+1, level*t1.level FROM cnt, t1 WHERE level<3 ) SELECT x, level FROM cnt ORDER BY x, level; } {1 1 2 10 2 100 2 1000 2 10000 3 20 3 200 3 2000 3 20000} do_execsql_test 1.11 { CREATE TEMP TABLE powersoftwo(a,b); WITH RECURSIVE tmp(a,b) AS (VALUES(0,1) UNION SELECT a+1, b*2 FROM tmp WHERE level<32) INSERT INTO powersoftwo SELECT a, b FROM tmp; WITH RECURSIVE cnt(x,y) AS ( VALUES(0,0) UNION SELECT x+1, (x+1)*(SELECT b FROM powersoftwo WHERE a=level) FROM cnt WHERE level<5 ) SELECT * FROM cnt; } {0 0 1 2 2 8 3 24 4 64} do_catchsql_test 2.1 { WITH RECURSIVE cnt(x) AS (VALUES(1) UNION SELECT x+1 FROM cnt WHERE level<10) SELECT x, level FROM cnt; } {1 {no such column: level}} do_catchsql_test 2.2 { WITH RECURSIVE cnt(x) AS (VALUES(level) UNION SELECT x+1 FROM cnt WHERE level<10) SELECT x FROM cnt; } {1 {no such column: level}} finish_test |