/ Check-in [7b5f3773]
Login

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

Overview
Comment:Bring in the latest updates from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 7b5f3773867ed0e4ed17bd473ba972d500035318
User & Date: drh 2014-01-28 18:06:17
Context
2014-01-29
14:21
Merge latest fixes from the trunk. check-in: 6b6dcd4c user: dan tags: sessions
2014-01-28
18:06
Bring in the latest updates from trunk. check-in: 7b5f3773 user: drh tags: sessions
17:49
Minor bugfix in main.c so that the library builds with SQLITE_OMIT_WSD defined. check-in: 5e3b9ecc user: dan tags: trunk
2014-01-24
14:05
Bring in all the latest trunk changes, including the Common Table Expressions implementation. check-in: 9b43e559 user: drh tags: sessions
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to ext/fts3/fts3_hash.c.

    92     92   }
    93     93   
    94     94   /*
    95     95   ** Hash and comparison functions when the mode is FTS3_HASH_STRING
    96     96   */
    97     97   static int fts3StrHash(const void *pKey, int nKey){
    98     98     const char *z = (const char *)pKey;
    99         -  int h = 0;
           99  +  unsigned h = 0;
   100    100     if( nKey<=0 ) nKey = (int) strlen(z);
   101    101     while( nKey > 0  ){
   102    102       h = (h<<3) ^ h ^ *z++;
   103    103       nKey--;
   104    104     }
   105         -  return h & 0x7fffffff;
          105  +  return (int)(h & 0x7fffffff);
   106    106   }
   107    107   static int fts3StrCompare(const void *pKey1, int n1, const void *pKey2, int n2){
   108    108     if( n1!=n2 ) return 1;
   109    109     return strncmp((const char*)pKey1,(const char*)pKey2,n1);
   110    110   }
   111    111   
   112    112   /*

Changes to src/delete.c.

   639    639       iOld = pParse->nMem+1;
   640    640       pParse->nMem += (1 + pTab->nCol);
   641    641   
   642    642       /* Populate the OLD.* pseudo-table register array. These values will be 
   643    643       ** used by any BEFORE and AFTER triggers that exist.  */
   644    644       sqlite3VdbeAddOp2(v, OP_Copy, iPk, iOld);
   645    645       for(iCol=0; iCol<pTab->nCol; iCol++){
   646         -      if( mask==0xffffffff || mask&(1<<iCol) ){
          646  +      testcase( mask!=0xffffffff && iCol==31 );
          647  +      if( mask==0xffffffff || (mask & MASKBIT32(iCol))!=0 ){
   647    648           sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, iCol, iOld+iCol+1);
   648    649         }
   649    650       }
   650    651   
   651    652       /* Invoke BEFORE DELETE trigger programs. */
   652    653       addrStart = sqlite3VdbeCurrentAddr(v);
   653    654       sqlite3CodeRowTrigger(pParse, pTrigger, 

Changes to src/expr.c.

  2681   2681       }
  2682   2682       case TK_FUNCTION: {
  2683   2683         ExprList *pFarg;       /* List of function arguments */
  2684   2684         int nFarg;             /* Number of function arguments */
  2685   2685         FuncDef *pDef;         /* The function definition object */
  2686   2686         int nId;               /* Length of the function name in bytes */
  2687   2687         const char *zId;       /* The function name */
  2688         -      int constMask = 0;     /* Mask of function arguments that are constant */
         2688  +      u32 constMask = 0;     /* Mask of function arguments that are constant */
  2689   2689         int i;                 /* Loop counter */
  2690   2690         u8 enc = ENC(db);      /* The text encoding used by this database */
  2691   2691         CollSeq *pColl = 0;    /* A collating sequence */
  2692   2692   
  2693   2693         assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
  2694   2694         if( ExprHasProperty(pExpr, EP_TokenOnly) ){
  2695   2695           pFarg = 0;
................................................................................
  2732   2732           assert( nFarg>=1 );
  2733   2733           sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
  2734   2734           break;
  2735   2735         }
  2736   2736   
  2737   2737         for(i=0; i<nFarg; i++){
  2738   2738           if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
  2739         -          constMask |= (1<<i);
         2739  +          testcase( i==31 );
         2740  +          constMask |= MASKBIT32(i);
  2740   2741           }
  2741   2742           if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){
  2742   2743             pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr);
  2743   2744           }
  2744   2745         }
  2745   2746         if( pFarg ){
  2746   2747           if( constMask ){

Changes to src/func.c.

   133    133   static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
   134    134     assert( argc==1 );
   135    135     UNUSED_PARAMETER(argc);
   136    136     switch( sqlite3_value_type(argv[0]) ){
   137    137       case SQLITE_INTEGER: {
   138    138         i64 iVal = sqlite3_value_int64(argv[0]);
   139    139         if( iVal<0 ){
   140         -        if( (iVal<<1)==0 ){
          140  +        if( iVal==SMALLEST_INT64 ){
   141    141             /* IMP: R-31676-45509 If X is the integer -9223372036854775808
   142    142             ** then abs(X) throws an integer overflow error since there is no
   143    143             ** equivalent positive 64-bit two complement value. */
   144    144             sqlite3_result_error(context, "integer overflow", -1);
   145    145             return;
   146    146           }
   147    147           iVal = -iVal;

Changes to src/hash.c.

    49     49     pH->count = 0;
    50     50   }
    51     51   
    52     52   /*
    53     53   ** The hashing function.
    54     54   */
    55     55   static unsigned int strHash(const char *z, int nKey){
    56         -  int h = 0;
           56  +  unsigned int h = 0;
    57     57     assert( nKey>=0 );
    58     58     while( nKey > 0  ){
    59     59       h = (h<<3) ^ h ^ sqlite3UpperToLower[(unsigned char)*z++];
    60     60       nKey--;
    61     61     }
    62     62     return h;
    63     63   }

Changes to src/main.c.

  3320   3320       ** Set or clear a flag that indicates that the database file is always well-
  3321   3321       ** formed and never corrupt.  This flag is clear by default, indicating that
  3322   3322       ** database files might have arbitrary corruption.  Setting the flag during
  3323   3323       ** testing causes certain assert() statements in the code to be activated
  3324   3324       ** that demonstrat invariants on well-formed database files.
  3325   3325       */
  3326   3326       case SQLITE_TESTCTRL_NEVER_CORRUPT: {
  3327         -      sqlite3Config.neverCorrupt = va_arg(ap, int);
         3327  +      sqlite3GlobalConfig.neverCorrupt = va_arg(ap, int);
  3328   3328         break;
  3329   3329       }
  3330   3330   
  3331   3331     }
  3332   3332     va_end(ap);
  3333   3333   #endif /* SQLITE_OMIT_BUILTIN_TEST */
  3334   3334     return rc;

Changes to src/os_unix.c.

  4093   4093       rc = (rc!=(-1)) ? SQLITE_OK : SQLITE_BUSY;
  4094   4094     }
  4095   4095   
  4096   4096     /* Update the global lock state and do debug tracing */
  4097   4097   #ifdef SQLITE_DEBUG
  4098   4098     { u16 mask;
  4099   4099     OSTRACE(("SHM-LOCK "));
  4100         -  mask = ofst>31 ? 0xffffffff : (1<<(ofst+n)) - (1<<ofst);
         4100  +  mask = ofst>31 ? 0xffff : (1<<(ofst+n)) - (1<<ofst);
  4101   4101     if( rc==SQLITE_OK ){
  4102   4102       if( lockType==F_UNLCK ){
  4103   4103         OSTRACE(("unlock %d ok", ofst));
  4104   4104         pShmNode->exclMask &= ~mask;
  4105   4105         pShmNode->sharedMask &= ~mask;
  4106   4106       }else if( lockType==F_RDLCK ){
  4107   4107         OSTRACE(("read-lock %d ok", ofst));

Changes to src/pcache1.c.

   716    716     int createFlag
   717    717   ){
   718    718     unsigned int nPinned;
   719    719     PCache1 *pCache = (PCache1 *)p;
   720    720     PGroup *pGroup;
   721    721     PgHdr1 *pPage = 0;
   722    722   
          723  +  assert( offsetof(PgHdr1,page)==0 );
   723    724     assert( pCache->bPurgeable || createFlag!=1 );
   724    725     assert( pCache->bPurgeable || pCache->nMin==0 );
   725    726     assert( pCache->bPurgeable==0 || pCache->nMin==10 );
   726    727     assert( pCache->nMin==0 || pCache->bPurgeable );
   727    728     pcache1EnterMutex(pGroup = pCache->pGroup);
   728    729   
   729    730     /* Step 1: Search the hash table for an existing entry. */
................................................................................
   821    822     }
   822    823   
   823    824   fetch_out:
   824    825     if( pPage && iKey>pCache->iMaxKey ){
   825    826       pCache->iMaxKey = iKey;
   826    827     }
   827    828     pcache1LeaveMutex(pGroup);
   828         -  return &pPage->page;
          829  +  return (sqlite3_pcache_page*)pPage;
   829    830   }
   830    831   
   831    832   
   832    833   /*
   833    834   ** Implementation of the sqlite3_pcache.xUnpin method.
   834    835   **
   835    836   ** Mark a page as unpinned (eligible for asynchronous recycling).

Changes to src/select.c.

  1721   1721     }
  1722   1722     assert( iCol>=0 );
  1723   1723     if( pRet==0 && iCol<p->pEList->nExpr ){
  1724   1724       pRet = sqlite3ExprCollSeq(pParse, p->pEList->a[iCol].pExpr);
  1725   1725     }
  1726   1726     return pRet;
  1727   1727   }
  1728         -#endif /* SQLITE_OMIT_COMPOUND_SELECT */
         1728  +
         1729  +/*
         1730  +** The select statement passed as the second parameter is a compound SELECT
         1731  +** with an ORDER BY clause. This function allocates and returns a KeyInfo
         1732  +** structure suitable for implementing the ORDER BY.
         1733  +**
         1734  +** Space to hold the KeyInfo structure is obtained from malloc. The calling
         1735  +** function is responsible for ensuring that this structure is eventually
         1736  +** freed.
         1737  +*/
         1738  +static KeyInfo *multiSelectOrderByKeyInfo(Parse *pParse, Select *p, int nExtra){
         1739  +  ExprList *pOrderBy = p->pOrderBy;
         1740  +  int nOrderBy = p->pOrderBy->nExpr;
         1741  +  sqlite3 *db = pParse->db;
         1742  +  KeyInfo *pRet = sqlite3KeyInfoAlloc(db, nOrderBy+nExtra, 1);
         1743  +  if( pRet ){
         1744  +    int i;
         1745  +    for(i=0; i<nOrderBy; i++){
         1746  +      struct ExprList_item *pItem = &pOrderBy->a[i];
         1747  +      Expr *pTerm = pItem->pExpr;
         1748  +      CollSeq *pColl;
         1749  +
         1750  +      if( pTerm->flags & EP_Collate ){
         1751  +        pColl = sqlite3ExprCollSeq(pParse, pTerm);
         1752  +      }else{
         1753  +        pColl = multiSelectCollSeq(pParse, p, pItem->u.x.iOrderByCol-1);
         1754  +        if( pColl==0 ) pColl = db->pDfltColl;
         1755  +        pOrderBy->a[i].pExpr =
         1756  +          sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
         1757  +      }
         1758  +      assert( sqlite3KeyInfoIsWriteable(pRet) );
         1759  +      pRet->aColl[i] = pColl;
         1760  +      pRet->aSortOrder[i] = pOrderBy->a[i].sortOrder;
         1761  +    }
         1762  +  }
         1763  +
         1764  +  return pRet;
         1765  +}
  1729   1766   
  1730   1767   #ifndef SQLITE_OMIT_CTE
  1731   1768   /*
  1732   1769   ** This routine generates VDBE code to compute the content of a WITH RECURSIVE
  1733   1770   ** query of the form:
  1734   1771   **
  1735   1772   **   <recursive-table> AS (<setup-query> UNION [ALL] <recursive-query>)
................................................................................
  1795   1832     computeLimitRegisters(pParse, p, addrBreak);
  1796   1833     pLimit = p->pLimit;
  1797   1834     pOffset = p->pOffset;
  1798   1835     regLimit = p->iLimit;
  1799   1836     regOffset = p->iOffset;
  1800   1837     p->pLimit = p->pOffset = 0;
  1801   1838     p->iLimit = p->iOffset = 0;
         1839  +  pOrderBy = p->pOrderBy;
  1802   1840   
  1803   1841     /* Locate the cursor number of the Current table */
  1804   1842     for(i=0; ALWAYS(i<pSrc->nSrc); i++){
  1805   1843       if( pSrc->a[i].isRecursive ){
  1806   1844         iCurrent = pSrc->a[i].iCursor;
  1807   1845         break;
  1808   1846       }
  1809   1847     }
  1810   1848   
  1811         -  /* Detach the ORDER BY clause from the compound SELECT */
  1812         -  pOrderBy = p->pOrderBy;
  1813         -  p->pOrderBy = 0;
  1814         -
  1815   1849     /* Allocate cursors numbers for Queue and Distinct.  The cursor number for
  1816   1850     ** the Distinct table must be exactly one greater than Queue in order
  1817   1851     ** for the SRT_DistTable and SRT_DistQueue destinations to work. */
  1818   1852     iQueue = pParse->nTab++;
  1819   1853     if( p->op==TK_UNION ){
  1820   1854       eDest = pOrderBy ? SRT_DistQueue : SRT_DistTable;
  1821   1855       iDistinct = pParse->nTab++;
................................................................................
  1824   1858     }
  1825   1859     sqlite3SelectDestInit(&destQueue, eDest, iQueue);
  1826   1860   
  1827   1861     /* Allocate cursors for Current, Queue, and Distinct. */
  1828   1862     regCurrent = ++pParse->nMem;
  1829   1863     sqlite3VdbeAddOp3(v, OP_OpenPseudo, iCurrent, regCurrent, nCol);
  1830   1864     if( pOrderBy ){
  1831         -    KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pOrderBy, 1);
         1865  +    KeyInfo *pKeyInfo = multiSelectOrderByKeyInfo(pParse, p, 1);
  1832   1866       sqlite3VdbeAddOp4(v, OP_OpenEphemeral, iQueue, pOrderBy->nExpr+2, 0,
  1833   1867                         (char*)pKeyInfo, P4_KEYINFO);
  1834   1868       destQueue.pOrderBy = pOrderBy;
  1835   1869     }else{
  1836   1870       sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iQueue, nCol);
  1837   1871     }
  1838   1872     VdbeComment((v, "Queue table"));
  1839   1873     if( iDistinct ){
  1840   1874       p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iDistinct, 0);
  1841   1875       p->selFlags |= SF_UsesEphemeral;
  1842   1876     }
         1877  +
         1878  +  /* Detach the ORDER BY clause from the compound SELECT */
         1879  +  p->pOrderBy = 0;
  1843   1880   
  1844   1881     /* Store the results of the setup-query in Queue. */
  1845   1882     rc = sqlite3Select(pParse, pSetup, &destQueue);
  1846   1883     if( rc ) goto end_of_recursive_query;
  1847   1884   
  1848   1885     /* Find the next row in the Queue and output that row */
  1849   1886     addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak);
................................................................................
  1879   1916   
  1880   1917   end_of_recursive_query:
  1881   1918     p->pOrderBy = pOrderBy;
  1882   1919     p->pLimit = pLimit;
  1883   1920     p->pOffset = pOffset;
  1884   1921     return;
  1885   1922   }
  1886         -#endif
         1923  +#endif /* SQLITE_OMIT_CTE */
  1887   1924   
  1888   1925   /* Forward references */
  1889   1926   static int multiSelectOrderBy(
  1890   1927     Parse *pParse,        /* Parsing context */
  1891   1928     Select *p,            /* The right-most of SELECTs to be coded */
  1892   1929     SelectDest *pDest     /* What to do with query results */
  1893   1930   );
  1894   1931   
  1895   1932   
  1896         -#ifndef SQLITE_OMIT_COMPOUND_SELECT
  1897   1933   /*
  1898   1934   ** This routine is called to process a compound query form from
  1899   1935   ** two or more separate queries using UNION, UNION ALL, EXCEPT, or
  1900   1936   ** INTERSECT
  1901   1937   **
  1902   1938   ** "p" points to the right-most of the two queries.  the query on the
  1903   1939   ** left is p->pPrior.  The left query could also be a compound query
................................................................................
  2621   2657     if( aPermute ){
  2622   2658       struct ExprList_item *pItem;
  2623   2659       for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){
  2624   2660         assert( pItem->u.x.iOrderByCol>0
  2625   2661             && pItem->u.x.iOrderByCol<=p->pEList->nExpr );
  2626   2662         aPermute[i] = pItem->u.x.iOrderByCol - 1;
  2627   2663       }
  2628         -    pKeyMerge = sqlite3KeyInfoAlloc(db, nOrderBy, 1);
  2629         -    if( pKeyMerge ){
  2630         -      for(i=0; i<nOrderBy; i++){
  2631         -        CollSeq *pColl;
  2632         -        Expr *pTerm = pOrderBy->a[i].pExpr;
  2633         -        if( pTerm->flags & EP_Collate ){
  2634         -          pColl = sqlite3ExprCollSeq(pParse, pTerm);
  2635         -        }else{
  2636         -          pColl = multiSelectCollSeq(pParse, p, aPermute[i]);
  2637         -          if( pColl==0 ) pColl = db->pDfltColl;
  2638         -          pOrderBy->a[i].pExpr =
  2639         -             sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
  2640         -        }
  2641         -        assert( sqlite3KeyInfoIsWriteable(pKeyMerge) );
  2642         -        pKeyMerge->aColl[i] = pColl;
  2643         -        pKeyMerge->aSortOrder[i] = pOrderBy->a[i].sortOrder;
  2644         -      }
  2645         -    }
         2664  +    pKeyMerge = multiSelectOrderByKeyInfo(pParse, p, 1);
  2646   2665     }else{
  2647   2666       pKeyMerge = 0;
  2648   2667     }
  2649   2668   
  2650   2669     /* Reattach the ORDER BY clause to the query.
  2651   2670     */
  2652   2671     p->pOrderBy = pOrderBy;

Changes to src/sqliteInt.h.

  1986   1986   */
  1987   1987   #define BMS  ((int)(sizeof(Bitmask)*8))
  1988   1988   
  1989   1989   /*
  1990   1990   ** A bit in a Bitmask
  1991   1991   */
  1992   1992   #define MASKBIT(n)   (((Bitmask)1)<<(n))
         1993  +#define MASKBIT32(n) (((unsigned int)1)<<(n))
  1993   1994   
  1994   1995   /*
  1995   1996   ** The following structure describes the FROM clause of a SELECT statement.
  1996   1997   ** Each table or subquery in the FROM clause is a separate element of
  1997   1998   ** the SrcList.a[] array.
  1998   1999   **
  1999   2000   ** With the addition of multiple database support, the following structure

Changes to src/tclsqlite.c.

   913    913     void *pArg,
   914    914     int code,
   915    915     const char *zArg1,
   916    916     const char *zArg2,
   917    917     const char *zArg3,
   918    918     const char *zArg4
   919    919   ){
   920         -  char *zCode;
          920  +  const char *zCode;
   921    921     Tcl_DString str;
   922    922     int rc;
   923    923     const char *zReply;
   924    924     SqliteDb *pDb = (SqliteDb*)pArg;
   925    925     if( pDb->disableAuth ) return SQLITE_OK;
   926    926   
   927    927     switch( code ){
................................................................................
  1039   1039   ** the transaction or savepoint opened by the [transaction] command.
  1040   1040   */
  1041   1041   static int DbTransPostCmd(
  1042   1042     ClientData data[],                   /* data[0] is the Sqlite3Db* for $db */
  1043   1043     Tcl_Interp *interp,                  /* Tcl interpreter */
  1044   1044     int result                           /* Result of evaluating SCRIPT */
  1045   1045   ){
  1046         -  static const char *azEnd[] = {
         1046  +  static const char *const azEnd[] = {
  1047   1047       "RELEASE _tcl_transaction",        /* rc==TCL_ERROR, nTransaction!=0 */
  1048   1048       "COMMIT",                          /* rc!=TCL_ERROR, nTransaction==0 */
  1049   1049       "ROLLBACK TO _tcl_transaction ; RELEASE _tcl_transaction",
  1050   1050       "ROLLBACK"                         /* rc==TCL_ERROR, nTransaction==0 */
  1051   1051     };
  1052   1052     SqliteDb *pDb = (SqliteDb*)data[0];
  1053   1053     int rc = result;
................................................................................
  2018   2018         Tcl_WrongNumArgs(interp, 2, objv, "?CALLBACK?");
  2019   2019         return TCL_ERROR;
  2020   2020       }else if( objc==2 ){
  2021   2021         if( pDb->zCommit ){
  2022   2022           Tcl_AppendResult(interp, pDb->zCommit, 0);
  2023   2023         }
  2024   2024       }else{
  2025         -      char *zCommit;
         2025  +      const char *zCommit;
  2026   2026         int len;
  2027   2027         if( pDb->zCommit ){
  2028   2028           Tcl_Free(pDb->zCommit);
  2029   2029         }
  2030   2030         zCommit = Tcl_GetStringFromObj(objv[2], &len);
  2031   2031         if( zCommit && len>0 ){
  2032   2032           pDb->zCommit = Tcl_Alloc( len + 1 );
................................................................................
  2091   2091       int nByte;                  /* Number of bytes in an SQL string */
  2092   2092       int i, j;                   /* Loop counters */
  2093   2093       int nSep;                   /* Number of bytes in zSep[] */
  2094   2094       int nNull;                  /* Number of bytes in zNull[] */
  2095   2095       char *zSql;                 /* An SQL statement */
  2096   2096       char *zLine;                /* A single line of input from the file */
  2097   2097       char **azCol;               /* zLine[] broken up into columns */
  2098         -    char *zCommit;              /* How to commit changes */
         2098  +    const char *zCommit;        /* How to commit changes */
  2099   2099       FILE *in;                   /* The input file */
  2100   2100       int lineno = 0;             /* Line number of input file */
  2101   2101       char zLineNum[80];          /* Line number print buffer */
  2102   2102       Tcl_Obj *pResult;           /* interp result */
  2103   2103   
  2104         -    char *zSep;
  2105         -    char *zNull;
         2104  +    const char *zSep;
         2105  +    const char *zNull;
  2106   2106       if( objc<5 || objc>7 ){
  2107   2107         Tcl_WrongNumArgs(interp, 2, objv, 
  2108   2108            "CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?");
  2109   2109         return TCL_ERROR;
  2110   2110       }
  2111   2111       if( objc>=6 ){
  2112   2112         zSep = Tcl_GetStringFromObj(objv[5], 0);
................................................................................
  3077   3077   #else
  3078   3078     flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX;
  3079   3079   #endif
  3080   3080   
  3081   3081     if( objc==2 ){
  3082   3082       zArg = Tcl_GetStringFromObj(objv[1], 0);
  3083   3083       if( strcmp(zArg,"-version")==0 ){
  3084         -      Tcl_AppendResult(interp,sqlite3_version,0);
         3084  +      Tcl_AppendResult(interp,sqlite3_libversion(),0);
  3085   3085         return TCL_OK;
  3086   3086       }
  3087   3087       if( strcmp(zArg,"-has-codec")==0 ){
  3088   3088   #ifdef SQLITE_HAS_CODEC
  3089   3089         Tcl_AppendResult(interp,"1",0);
  3090   3090   #else
  3091   3091         Tcl_AppendResult(interp,"0",0);
................................................................................
  3159   3159   #endif
  3160   3160       );
  3161   3161       return TCL_ERROR;
  3162   3162     }
  3163   3163     zErrMsg = 0;
  3164   3164     p = (SqliteDb*)Tcl_Alloc( sizeof(*p) );
  3165   3165     if( p==0 ){
  3166         -    Tcl_SetResult(interp, "malloc failed", TCL_STATIC);
         3166  +    Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
  3167   3167       return TCL_ERROR;
  3168   3168     }
  3169   3169     memset(p, 0, sizeof(*p));
  3170   3170     zFile = Tcl_GetStringFromObj(objv[2], 0);
  3171   3171     zFile = Tcl_TranslateFileName(interp, zFile, &translatedFilename);
  3172   3172     rc = sqlite3_open_v2(zFile, &p->db, flags, zVfs);
  3173   3173     Tcl_DStringFree(&translatedFilename);

Changes to src/test8.c.

   888    888     pIdxInfo->idxNum = hashString(zQuery);
   889    889     pIdxInfo->idxStr = zQuery;
   890    890     pIdxInfo->needToFreeIdxStr = 1;
   891    891     if( useCost ){
   892    892       pIdxInfo->estimatedCost = cost;
   893    893     }else if( useIdx ){
   894    894       /* Approximation of log2(nRow). */
   895         -    for( ii=0; ii<(sizeof(int)*8); ii++ ){
          895  +    for( ii=0; ii<(sizeof(int)*8)-1; ii++ ){
   896    896         if( nRow & (1<<ii) ){
   897    897           pIdxInfo->estimatedCost = (double)ii;
   898    898         }
   899    899       }
   900    900     }else{
   901    901       pIdxInfo->estimatedCost = (double)nRow;
   902    902     }

Changes to src/update.c.

   463    463     if( chngPk || hasFK || pTrigger ){
   464    464       u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
   465    465       oldmask |= sqlite3TriggerColmask(pParse, 
   466    466           pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError
   467    467       );
   468    468       for(i=0; i<pTab->nCol; i++){
   469    469         if( oldmask==0xffffffff
   470         -       || (i<32 && (oldmask & (1<<i)))
          470  +       || (i<32 && (oldmask & MASKBIT32(i))!=0)
   471    471          || (pTab->aCol[i].colFlags & COLFLAG_PRIMKEY)!=0
   472    472         ){
          473  +        testcase(  oldmask!=0xffffffff && i==31 );
   473    474           sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regOld+i);
   474    475         }else{
   475    476           sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
   476    477         }
   477    478       }
   478    479       if( chngRowid==0 && pPk==0 ){
   479    480         sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
................................................................................
   500    501     for(i=0; i<pTab->nCol; i++){
   501    502       if( i==pTab->iPKey ){
   502    503         sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i);
   503    504       }else{
   504    505         j = aXRef[i];
   505    506         if( j>=0 ){
   506    507           sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
   507         -      }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){
          508  +      }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i)) ){
   508    509           /* This branch loads the value of a column that will not be changed 
   509    510           ** into a register. This is done if there are no BEFORE triggers, or
   510    511           ** if there are one or more BEFORE triggers that use this value via
   511    512           ** a new.* reference in a trigger program.
   512    513           */
   513    514           testcase( i==31 );
   514    515           testcase( i==32 );

Changes to src/util.c.

   997    997   }
   998    998   
   999    999   
  1000   1000   /*
  1001   1001   ** Read or write a four-byte big-endian integer value.
  1002   1002   */
  1003   1003   u32 sqlite3Get4byte(const u8 *p){
  1004         -  return (p[0]<<24) | (p[1]<<16) | (p[2]<<8) | p[3];
         1004  +  testcase( p[0]&0x80 );
         1005  +  return ((unsigned)p[0]<<24) | (p[1]<<16) | (p[2]<<8) | p[3];
  1005   1006   }
  1006   1007   void sqlite3Put4byte(unsigned char *p, u32 v){
  1007   1008     p[0] = (u8)(v>>24);
  1008   1009     p[1] = (u8)(v>>16);
  1009   1010     p[2] = (u8)(v>>8);
  1010   1011     p[3] = (u8)v;
  1011   1012   }

Changes to src/vdbe.c.

  6231   6231       sqlite3DbFree(db, z);
  6232   6232     }
  6233   6233   #ifdef SQLITE_USE_FCNTL_TRACE
  6234   6234     zTrace = (pOp->p4.z ? pOp->p4.z : p->zSql);
  6235   6235     if( zTrace ){
  6236   6236       int i;
  6237   6237       for(i=0; i<db->nDb; i++){
  6238         -      if( ((1<<i) & p->btreeMask)==0 ) continue;
         6238  +      if( MASKBIT(i) & p->btreeMask)==0 ) continue;
  6239   6239         sqlite3_file_control(db, db->aDb[i].zName, SQLITE_FCNTL_TRACE, zTrace);
  6240   6240       }
  6241   6241     }
  6242   6242   #endif /* SQLITE_USE_FCNTL_TRACE */
  6243   6243   #ifdef SQLITE_DEBUG
  6244   6244     if( (db->flags & SQLITE_SqlTrace)!=0
  6245   6245      && (zTrace = (pOp->p4.z ? pOp->p4.z : p->zSql))!=0

Changes to src/vdbeaux.c.

  2612   2612   **      function parameter corrsponds to bit 0 etc.).
  2613   2613   */
  2614   2614   void sqlite3VdbeDeleteAuxData(Vdbe *pVdbe, int iOp, int mask){
  2615   2615     AuxData **pp = &pVdbe->pAuxData;
  2616   2616     while( *pp ){
  2617   2617       AuxData *pAux = *pp;
  2618   2618       if( (iOp<0)
  2619         -     || (pAux->iOp==iOp && (pAux->iArg>31 || !(mask & ((u32)1<<pAux->iArg))))
         2619  +     || (pAux->iOp==iOp && (pAux->iArg>31 || !(mask & MASKBIT32(pAux->iArg))))
  2620   2620       ){
         2621  +      testcase( pAux->iArg==31 );
  2621   2622         if( pAux->xDelete ){
  2622   2623           pAux->xDelete(pAux->pAux);
  2623   2624         }
  2624   2625         *pp = pAux->pNext;
  2625   2626         sqlite3DbFree(pVdbe->db, pAux);
  2626   2627       }else{
  2627   2628         pp= &pAux->pNext;
................................................................................
  2928   2929   ** and store the result in pMem.  Return the number of bytes read.
  2929   2930   */ 
  2930   2931   u32 sqlite3VdbeSerialGet(
  2931   2932     const unsigned char *buf,     /* Buffer to deserialize from */
  2932   2933     u32 serial_type,              /* Serial type to deserialize */
  2933   2934     Mem *pMem                     /* Memory cell to write value into */
  2934   2935   ){
         2936  +  u64 x;
         2937  +  u32 y;
         2938  +  int i;
  2935   2939     switch( serial_type ){
  2936   2940       case 10:   /* Reserved for future use */
  2937   2941       case 11:   /* Reserved for future use */
  2938   2942       case 0: {  /* NULL */
  2939   2943         pMem->flags = MEM_Null;
  2940   2944         break;
  2941   2945       }
  2942   2946       case 1: { /* 1-byte signed integer */
  2943   2947         pMem->u.i = (signed char)buf[0];
  2944   2948         pMem->flags = MEM_Int;
  2945   2949         return 1;
  2946   2950       }
  2947   2951       case 2: { /* 2-byte signed integer */
  2948         -      pMem->u.i = (((signed char)buf[0])<<8) | buf[1];
         2952  +      i = 256*(signed char)buf[0] | buf[1];
         2953  +      pMem->u.i = (i64)i;
  2949   2954         pMem->flags = MEM_Int;
  2950   2955         return 2;
  2951   2956       }
  2952   2957       case 3: { /* 3-byte signed integer */
  2953         -      pMem->u.i = (((signed char)buf[0])<<16) | (buf[1]<<8) | buf[2];
         2958  +      i = 65536*(signed char)buf[0] | (buf[1]<<8) | buf[2];
         2959  +      pMem->u.i = (i64)i;
  2954   2960         pMem->flags = MEM_Int;
  2955   2961         return 3;
  2956   2962       }
  2957   2963       case 4: { /* 4-byte signed integer */
  2958         -      pMem->u.i = (buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
         2964  +      y = ((unsigned)buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
         2965  +      pMem->u.i = (i64)*(int*)&y;
  2959   2966         pMem->flags = MEM_Int;
  2960   2967         return 4;
  2961   2968       }
  2962   2969       case 5: { /* 6-byte signed integer */
  2963         -      u64 x = (((signed char)buf[0])<<8) | buf[1];
  2964         -      u32 y = (buf[2]<<24) | (buf[3]<<16) | (buf[4]<<8) | buf[5];
         2970  +      x = 256*(signed char)buf[0] + buf[1];
         2971  +      y = ((unsigned)buf[2]<<24) | (buf[3]<<16) | (buf[4]<<8) | buf[5];
  2965   2972         x = (x<<32) | y;
  2966   2973         pMem->u.i = *(i64*)&x;
  2967   2974         pMem->flags = MEM_Int;
  2968   2975         return 6;
  2969   2976       }
  2970   2977       case 6:   /* 8-byte signed integer */
  2971   2978       case 7: { /* IEEE floating point */
  2972         -      u64 x;
  2973         -      u32 y;
  2974   2979   #if !defined(NDEBUG) && !defined(SQLITE_OMIT_FLOATING_POINT)
  2975   2980         /* Verify that integers and floating point values use the same
  2976   2981         ** byte order.  Or, that if SQLITE_MIXED_ENDIAN_64BIT_FLOAT is
  2977   2982         ** defined that 64-bit floating point values really are mixed
  2978   2983         ** endian.
  2979   2984         */
  2980   2985         static const u64 t1 = ((u64)0x3ff00000)<<32;
  2981   2986         static const double r1 = 1.0;
  2982   2987         u64 t2 = t1;
  2983   2988         swapMixedEndianFloat(t2);
  2984   2989         assert( sizeof(r1)==sizeof(t2) && memcmp(&r1, &t2, sizeof(r1))==0 );
  2985   2990   #endif
  2986         -
  2987         -      x = (buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
  2988         -      y = (buf[4]<<24) | (buf[5]<<16) | (buf[6]<<8) | buf[7];
         2991  +      x = ((unsigned)buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
         2992  +      y = ((unsigned)buf[4]<<24) | (buf[5]<<16) | (buf[6]<<8) | buf[7];
  2989   2993         x = (x<<32) | y;
  2990   2994         if( serial_type==6 ){
  2991   2995           pMem->u.i = *(i64*)&x;
  2992   2996           pMem->flags = MEM_Int;
  2993   2997         }else{
  2994   2998           assert( sizeof(x)==8 && sizeof(pMem->r)==8 );
  2995   2999           swapMixedEndianFloat(x);

Changes to src/vdbemem.c.

   594    594     pMem->pScopyFrom = 0;
   595    595   }
   596    596   #endif /* SQLITE_DEBUG */
   597    597   
   598    598   /*
   599    599   ** Size of struct Mem not including the Mem.zMalloc member.
   600    600   */
   601         -#define MEMCELLSIZE (size_t)(&(((Mem *)0)->zMalloc))
          601  +#define MEMCELLSIZE offsetof(Mem,zMalloc)
   602    602   
   603    603   /*
   604    604   ** Make an shallow copy of pFrom into pTo.  Prior contents of
   605    605   ** pTo are freed.  The pFrom->z field is not duplicated.  If
   606    606   ** pFrom->z is used, then pTo->z points to the same thing as pFrom->z
   607    607   ** and flags gets srcType (either MEM_Ephem or MEM_Static).
   608    608   */

Changes to test/closure01.test.

    11     11   # 
    12     12   # Test cases for transitive_closure virtual table.
    13     13   
    14     14   set testdir [file dirname $argv0]
    15     15   source $testdir/tester.tcl
    16     16   set testprefix closure01
    17     17   
    18         -ifcapable !vtab { finish_test ; return }
           18  +ifcapable !vtab||!cte { finish_test ; return }
    19     19   
    20     20   load_static_extension db closure
    21     21   
    22     22   do_execsql_test 1.0 {
    23     23     BEGIN;
    24     24     CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER);
           25  +  WITH RECURSIVE
           26  +    cnt(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM cnt LIMIT 131072)
           27  +  INSERT INTO t1(x, y) SELECT i, nullif(i,1)/2 FROM cnt;
    25     28     CREATE INDEX t1y ON t1(y);
    26         -  INSERT INTO t1(x) VALUES(1),(2);
    27         -  INSERT INTO t1(x) SELECT x+2 FROM t1;
    28         -  INSERT INTO t1(x) SELECT x+4 FROM t1;
    29         -  INSERT INTO t1(x) SELECT x+8 FROM t1;
    30         -  INSERT INTO t1(x) SELECT x+16 FROM t1;
    31         -  INSERT INTO t1(x) SELECT x+32 FROM t1;
    32         -  INSERT INTO t1(x) SELECT x+64 FROM t1;
    33         -  INSERT INTO t1(x) SELECT x+128 FROM t1;
    34         -  INSERT INTO t1(x) SELECT x+256 FROM t1;
    35         -  INSERT INTO t1(x) SELECT x+512 FROM t1;
    36         -  INSERT INTO t1(x) SELECT x+1024 FROM t1;
    37         -  INSERT INTO t1(x) SELECT x+2048 FROM t1;
    38         -  INSERT INTO t1(x) SELECT x+4096 FROM t1;
    39         -  INSERT INTO t1(x) SELECT x+8192 FROM t1;
    40         -  INSERT INTO t1(x) SELECT x+16384 FROM t1;
    41         -  INSERT INTO t1(x) SELECT x+32768 FROM t1;
    42         -  INSERT INTO t1(x) SELECT x+65536 FROM t1;
    43         -  UPDATE t1 SET y=x/2 WHERE x>1;
    44     29     COMMIT;
    45     30     CREATE VIRTUAL TABLE cx 
    46     31      USING transitive_closure(tablename=t1, idcolumn=x, parentcolumn=y);
    47     32   } {}
    48     33   
    49     34   # The entire table
    50         -do_execsql_test 1.1 {
           35  +do_timed_execsql_test 1.1 {
    51     36     SELECT count(*), depth FROM cx WHERE root=1 GROUP BY depth ORDER BY 1;
    52     37   } {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}
           38  +do_timed_execsql_test 1.1-cte {
           39  +  WITH RECURSIVE
           40  +    below(id,depth) AS (
           41  +      VALUES(1,0)
           42  +       UNION ALL
           43  +      SELECT t1.x, below.depth+1
           44  +        FROM t1 JOIN below on t1.y=below.id
           45  +    )
           46  +  SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1;
           47  +} {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}
    53     48   
    54     49   # descendents of 32768
    55         -do_execsql_test 1.2 {
           50  +do_timed_execsql_test 1.2 {
    56     51     SELECT * FROM cx WHERE root=32768 ORDER BY id;
    57     52   } {32768 0 65536 1 65537 1 131072 2}
           53  +do_timed_execsql_test 1.2-cte {
           54  +  WITH RECURSIVE
           55  +    below(id,depth) AS (
           56  +      VALUES(32768,0)
           57  +       UNION ALL
           58  +      SELECT t1.x, below.depth+1
           59  +        FROM t1 JOIN below on t1.y=below.id
           60  +       WHERE below.depth<2
           61  +    )
           62  +  SELECT id, depth FROM below ORDER BY id;
           63  +} {32768 0 65536 1 65537 1 131072 2}
    58     64   
    59     65   # descendents of 16384
    60         -do_execsql_test 1.3 {
           66  +do_timed_execsql_test 1.3 {
    61     67     SELECT * FROM cx WHERE root=16384 AND depth<=2 ORDER BY id;
    62     68   } {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}
           69  +do_timed_execsql_test 1.3-cte {
           70  +  WITH RECURSIVE
           71  +    below(id,depth) AS (
           72  +      VALUES(16384,0)
           73  +       UNION ALL
           74  +      SELECT t1.x, below.depth+1
           75  +        FROM t1 JOIN below on t1.y=below.id
           76  +       WHERE below.depth<2
           77  +    )
           78  +  SELECT id, depth FROM below ORDER BY id;
           79  +} {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}
    63     80   
    64     81   # children of 16384
    65     82   do_execsql_test 1.4 {
    66     83     SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
    67     84      WHERE root=16384
    68     85        AND depth=1
    69     86      ORDER BY id;
    70     87   } {32768 1 {} t1 x y 32769 1 {} t1 x y}
    71     88   
    72     89   # great-grandparent of 16384
    73         -do_execsql_test 1.5 {
           90  +do_timed_execsql_test 1.5 {
    74     91     SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
    75     92      WHERE root=16384
    76     93        AND depth=3
    77     94        AND idcolumn='Y'
    78     95        AND parentcolumn='X';
    79     96   } {2048 3 {} t1 Y X}
           97  +do_timed_execsql_test 1.5-cte {
           98  +  WITH RECURSIVE
           99  +    above(id,depth) AS (
          100  +      VALUES(16384,0)
          101  +      UNION ALL
          102  +      SELECT t1.y, above.depth+1
          103  +        FROM t1 JOIN above ON t1.x=above.id
          104  +       WHERE above.depth<3
          105  +    )
          106  +  SELECT id FROM above WHERE depth=3;
          107  +} {2048}
    80    108   
    81    109   # depth<5
    82         -do_execsql_test 1.6 {
          110  +do_timed_execsql_test 1.6 {
    83    111     SELECT count(*), depth FROM cx WHERE root=1 AND depth<5
    84    112      GROUP BY depth ORDER BY 1;
    85    113   } {1 0 2 1 4 2 8 3 16 4}
          114  +do_timed_execsql_test 1.6-cte {
          115  +  WITH RECURSIVE
          116  +    below(id,depth) AS (
          117  +      VALUES(1,0)
          118  +      UNION ALL
          119  +      SELECT t1.x, below.depth+1
          120  +        FROM t1 JOIN below ON t1.y=below.id
          121  +       WHERE below.depth<4
          122  +    )
          123  +  SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1;
          124  +} {1 0 2 1 4 2 8 3 16 4}
    86    125   
    87    126   # depth<=5
    88    127   do_execsql_test 1.7 {
    89    128     SELECT count(*), depth FROM cx WHERE root=1 AND depth<=5
    90    129      GROUP BY depth ORDER BY 1;
    91    130   } {1 0 2 1 4 2 8 3 16 4 32 5}
    92    131   
................................................................................
    99    138   # depth BETWEEN 3 AND 5
   100    139   do_execsql_test 1.9 {
   101    140     SELECT count(*), depth FROM cx WHERE root=1 AND depth BETWEEN 3 AND 5
   102    141      GROUP BY depth ORDER BY 1;
   103    142   } {8 3 16 4 32 5}
   104    143   
   105    144   # depth==5 with min() and max()
   106         -do_execsql_test 1.10 {
          145  +do_timed_execsql_test 1.10 {
   107    146     SELECT count(*), min(id), max(id) FROM cx WHERE root=1 AND depth=5;
   108    147   } {32 32 63}
          148  +do_timed_execsql_test 1.10-cte {
          149  +  WITH RECURSIVE
          150  +    below(id,depth) AS (
          151  +      VALUES(1,0)
          152  +      UNION ALL
          153  +      SELECT t1.x, below.depth+1
          154  +        FROM t1 JOIN below ON t1.y=below.id
          155  +       WHERE below.depth<5
          156  +    )
          157  +  SELECT count(*), min(id), max(id) FROM below WHERE depth=5;
          158  +} {32 32 63}
   109    159   
   110    160   # Create a much smaller table t2 with only 32 elements 
   111    161   db eval {
   112    162     CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
   113    163     INSERT INTO t2 SELECT x, y FROM t1 WHERE x<32;
   114    164     CREATE INDEX t2y ON t2(y);
   115    165     CREATE VIRTUAL TABLE c2 

Changes to test/corruptH.test.

    62     62   
    63     63   #-------------------------------------------------------------------------
    64     64   reset_db
    65     65   
    66     66   # Initialize the database.
    67     67   #
    68     68   do_execsql_test 2.1 {
           69  +  PRAGMA auto_vacuum=0;
    69     70     PRAGMA page_size=1024;
    70     71   
    71     72     CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
    72     73     INSERT INTO t1 VALUES(1, 'one');
    73     74     INSERT INTO t1 VALUES(2, 'two');
    74     75   
    75     76     CREATE TABLE t3(x);
................................................................................
    91     92     hexio_write test.db [expr {($fl-1) * 1024 + 4}] 00000001 
    92     93     hexio_write test.db [expr {($fl-1) * 1024 + 8}] [format %.8X $r(t1)]
    93     94     hexio_write test.db 36 00000002
    94     95   
    95     96     sqlite3 db test.db
    96     97   } {}
    97     98   
           99  +
          100  +# The corruption migration caused by the test case below does not 
          101  +# cause corruption to be detected in mmap mode.
          102  +#
          103  +# The trick here is that the root page of the tree scanned by the outer 
          104  +# query is also currently on the free-list. So while the first seek on
          105  +# the table (for a==1) works, by the time the second is attempted The 
          106  +# "INSERT INTO t2..." statements have recycled the root page of t1 and
          107  +# used it as an index leaf. Normally, BtreeMovetoUnpacked() detects
          108  +# that the PgHdr object associated with said root page does not match
          109  +# the cursor (as it is now marked with PgHdr.intKey==0) and returns
          110  +# SQLITE_CORRUPT. 
          111  +#
          112  +# However, in mmap mode, the outer query and the inner queries use 
          113  +# different PgHdr objects (same data, but different PgHdr container 
          114  +# objects). And so the corruption is not detected. Instead, the second
          115  +# seek fails to find anything and only a single row is returned.
          116  +#
          117  +set res23 {1 {database disk image is malformed}}
          118  +if {[permutation]=="mmap"} {
          119  +  set res23 {0 one}
          120  +}
    98    121   do_test 2.3 {
    99    122     list [catch {
          123  +  set res [list]
   100    124     db eval { SELECT * FROM t1 WHERE a IN (1, 2) } {
   101    125       db eval { 
   102    126         INSERT INTO t2 SELECT randomblob(100) FROM t2;
   103    127         INSERT INTO t2 SELECT randomblob(100) FROM t2;
   104    128         INSERT INTO t2 SELECT randomblob(100) FROM t2;
   105    129         INSERT INTO t2 SELECT randomblob(100) FROM t2;
   106    130         INSERT INTO t2 SELECT randomblob(100) FROM t2;
   107    131       }
          132  +    lappend res $b
   108    133     }
          134  +  set res
   109    135     } msg] $msg
   110         -} {1 {database disk image is malformed}}
          136  +} $res23
   111    137   
   112    138   #-------------------------------------------------------------------------
   113    139   reset_db
   114    140   
   115    141   # Initialize the database.
   116    142   #
   117    143   do_execsql_test 3.1 {

Changes to test/e_select.test.

   329    329     2 "SELECT 'abc' WHERE NULL" {}
   330    330     3 "SELECT NULL"             {{}}
   331    331     4 "SELECT count(*)"         {1}
   332    332     5 "SELECT count(*) WHERE 0" {0}
   333    333     6 "SELECT count(*) WHERE 1" {1}
   334    334   }
   335    335   
   336         -# EVIDENCE-OF: R-48114-33255 If there is only a single table in the
   337         -# join-source following the FROM clause, then the input data used by the
   338         -# SELECT statement is the contents of the named table.
          336  +# EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery
          337  +# in the FROM clause, then the input data used by the SELECT statement
          338  +# is the contents of the named table.
   339    339   #
   340    340   #   The results of the SELECT queries suggest that they are operating on the
   341    341   #   contents of the table 'xx'.
   342    342   #
   343    343   do_execsql_test e_select-1.2.0 {
   344    344     CREATE TABLE xx(x, y);
   345    345     INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
................................................................................
   353    353        -17.89           'linguistically'                
   354    354     }
   355    355   
   356    356     2  "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}
   357    357     3  "SELECT sum(x), sum(y) FROM xx"               {-17.89 -16.87}
   358    358   }
   359    359   
   360         -# EVIDENCE-OF: R-23593-12456 If there is more than one table specified
   361         -# as part of the join-source following the FROM keyword, then the
   362         -# contents of each named table are joined into a single dataset for the
   363         -# simple SELECT statement to operate on.
          360  +# EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery
          361  +# in FROM clause then the contents of all tables and/or subqueries are
          362  +# joined into a single dataset for the simple SELECT statement to
          363  +# operate on.
   364    364   #
   365    365   #   There are more detailed tests for subsequent requirements that add 
   366    366   #   more detail to this idea. We just add a single test that shows that
   367    367   #   data is coming from each of the three tables following the FROM clause
   368    368   #   here to show that the statement, vague as it is, is not incorrect.
   369    369   #
   370    370   do_select_tests e_select-1.3 {
................................................................................
   379    379   }
   380    380   
   381    381   #
   382    382   # The following block of tests - e_select-1.4.* - test that the description
   383    383   # of cartesian joins in the SELECT documentation is consistent with SQLite.
   384    384   # In doing so, we test the following three requirements as a side-effect:
   385    385   #
   386         -# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
   387         -# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
   388         -# then the result of the join is simply the cartesian product of the
   389         -# left and right-hand datasets.
          386  +# EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
          387  +# "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
          388  +# clause, then the result of the join is simply the cartesian product of
          389  +# the left and right-hand datasets.
   390    390   #
   391    391   #    The tests are built on this assertion. Really, they test that the output
   392    392   #    of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
   393    393   #    of calculating the cartesian product of the left and right-hand datasets. 
   394    394   #
   395    395   # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
   396    396   # JOIN", "JOIN" and "," join operators.
................................................................................
   509    509   do_select_tests e_select-1.4.5 [list                                   \
   510    510       1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
   511    511       2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
   512    512       3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
   513    513       4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
   514    514   ]
   515    515   
   516         -
   517         -# EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
   518         -# the ON expression is evaluated for each row of the cartesian product
   519         -# as a boolean expression. All rows for which the expression evaluates
   520         -# to false are excluded from the dataset.
          516  +# EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON
          517  +# expression is evaluated for each row of the cartesian product as a
          518  +# boolean expression. Only rows for which the expression evaluates to
          519  +# true are included from the dataset.
   521    520   #
   522    521   foreach {tn select res} [list                                              \
   523    522       1 { SELECT * FROM t1 %JOIN% t2 ON (1) }       $t1_cross_t2             \
   524    523       2 { SELECT * FROM t1 %JOIN% t2 ON (0) }       [list]                   \
   525    524       3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) }    [list]                   \
   526    525       4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') }   [list]                   \
   527    526       5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') }   $t1_cross_t2             \
................................................................................
   536    535      11 { SELECT t1.b, t2.b 
   537    536           FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \
   538    537         {two I two II two III three I three II three III}                    \
   539    538   ] {
   540    539     do_join_test e_select-1.3.$tn $select $res
   541    540   }
   542    541   
   543         -# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
   544         -# part of the join-constraint, then each of the column names specified
   545         -# must exist in the datasets to both the left and right of the join-op.
          542  +# EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the
          543  +# column names specified must exist in the datasets to both the left and
          544  +# right of the join-operator.
   546    545   #
   547    546   do_select_tests e_select-1.4 -error {
   548    547     cannot join using column %s - column not present in both tables
   549    548   } {
   550    549     1 { SELECT * FROM t1, t3 USING (b) }   "b"
   551    550     2 { SELECT * FROM t3, t1 USING (c) }   "c"
   552    551     3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"
   553    552   } 
   554    553   
   555         -# EVIDENCE-OF: R-55987-04584 For each pair of namesake columns, the
          554  +# EVIDENCE-OF: R-22776-52830 For each pair of named columns, the
   556    555   # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
   557         -# product as a boolean expression. All rows for which one or more of the
   558         -# expressions evaluates to false are excluded from the result set.
          556  +# product as a boolean expression. Only rows for which all such
          557  +# expressions evaluates to true are included from the result set.
   559    558   #
   560    559   do_select_tests e_select-1.5 {
   561    560     1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
   562    561     2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}
   563    562   } 
   564    563   
   565    564   # EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
   566    565   # USING clause, the normal rules for handling affinities, collation
   567    566   # sequences and NULL values in comparisons apply.
   568    567   #
   569         -# EVIDENCE-OF: R-35466-18578 The column from the dataset on the
   570         -# left-hand side of the join operator is considered to be on the
          568  +# EVIDENCE-OF: R-38422-04402 The column from the dataset on the
          569  +# left-hand side of the join-operator is considered to be on the
   571    570   # left-hand side of the comparison operator (=) for the purposes of
   572    571   # collation sequence and affinity precedence.
   573    572   #
   574    573   do_execsql_test e_select-1.6.0 {
   575    574     CREATE TABLE t5(a COLLATE nocase, b COLLATE binary);
   576    575     INSERT INTO t5 VALUES('AA', 'cc');
   577    576     INSERT INTO t5 VALUES('BB', 'dd');
................................................................................
   618    617        {aa cc cc bb DD dd}
   619    618     4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x
   620    619          %JOIN% t5 ON (x.a=t5.a) } 
   621    620        {aa cc AA cc bb DD BB dd}
   622    621   } {
   623    622     do_join_test e_select-1.7.$tn $select $res
   624    623   }
   625         -
   626         -# EVIDENCE-OF: R-41434-12448 If the join-op is a "LEFT JOIN" or "LEFT
   627         -# OUTER JOIN", then after the ON or USING filtering clauses have been
   628         -# applied, an extra row is added to the output for each row in the
          624  +# EVIDENCE-OF: R-42531-52874 If the join-operator is a "LEFT JOIN" or
          625  +# "LEFT OUTER JOIN", then after the ON or USING filtering clauses have
          626  +# been applied, an extra row is added to the output for each row in the
   629    627   # original left-hand input dataset that corresponds to no rows at all in
   630    628   # the composite dataset (if any).
   631    629   #
   632    630   do_execsql_test e_select-1.8.0 {
   633    631     CREATE TABLE t7(a, b, c);
   634    632     CREATE TABLE t8(a, d, e);
   635    633   
................................................................................
   656    654     1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}
   657    655     1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 
   658    656        {x ex 24 x abc 24 y why 25 {} {} {}}
   659    657     2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
   660    658     2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}
   661    659   }
   662    660   
   663         -# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
   664         -# the join-ops, then an implicit USING clause is added to the
          661  +# EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the
          662  +# join-operator then an implicit USING clause is added to the
   665    663   # join-constraints. The implicit USING clause contains each of the
   666    664   # column names that appear in both the left and right-hand input
   667    665   # datasets.
   668    666   #
   669    667   do_select_tests e_select-1-10 {
   670    668     1a "SELECT * FROM t7 JOIN t8 USING (a)"        {x ex 24 abc 24}
   671    669     1b "SELECT * FROM t7 NATURAL JOIN t8"          {x ex 24 abc 24}

Changes to test/e_select2.test.

   340    340   } {
   341    341   
   342    342     catchsql { DROP INDEX i1 }
   343    343     catchsql { DROP INDEX i2 }
   344    344     catchsql { DROP INDEX i3 }
   345    345     execsql $indexes
   346    346   
   347         -  # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
   348         -  # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
   349         -  # then the result of the join is simply the cartesian product of the
   350         -  # left and right-hand datasets.
          347  +  # EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
          348  +  # "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
          349  +  # clause, then the result of the join is simply the cartesian product of
          350  +  # the left and right-hand datasets.
   351    351     #
   352    352     # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
   353    353     # JOIN", "JOIN" and "," join operators.
   354    354     #
   355    355     # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
   356    356     # same result as the "INNER JOIN", "JOIN" and "," operators
   357    357     #
................................................................................
   364    364     test_join $tn.1.7  "t2 CROSS JOIN t3"      {t2 t3}
   365    365     test_join $tn.1.8  "t2 JOIN t3"            {t2 t3}
   366    366     test_join $tn.1.9  "t2, t2 AS x"           {t2 t2}
   367    367     test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
   368    368     test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
   369    369     test_join $tn.1.12 "t2 JOIN t2 AS x"       {t2 t2}
   370    370   
   371         -  # EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
   372         -  # the ON expression is evaluated for each row of the cartesian product
   373         -  # as a boolean expression. All rows for which the expression evaluates
   374         -  # to false are excluded from the dataset.
          371  +  # EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON
          372  +  # expression is evaluated for each row of the cartesian product as a
          373  +  # boolean expression. Only rows for which the expression evaluates to
          374  +  # true are included from the dataset.
   375    375     #
   376    376     test_join $tn.2.1  "t1, t2 ON (t1.a=t2.a)"  {t1 t2 -on {te_equals a a}}
   377    377     test_join $tn.2.2  "t2, t1 ON (t1.a=t2.a)"  {t2 t1 -on {te_equals a a}}
   378    378     test_join $tn.2.3  "t2, t1 ON (1)"          {t2 t1 -on te_true}
   379    379     test_join $tn.2.4  "t2, t1 ON (NULL)"       {t2 t1 -on te_false}
   380    380     test_join $tn.2.5  "t2, t1 ON (1.1-1.1)"    {t2 t1 -on te_false}
   381    381     test_join $tn.2.6  "t1, t2 ON (1.1-1.0)"    {t1 t2 -on te_true}
................................................................................
   500    500     CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary);
   501    501   
   502    502     INSERT INTO t4 VALUES('2.0');
   503    503     INSERT INTO t4 VALUES('TWO');
   504    504     INSERT INTO t5 VALUES(2, 'two');
   505    505   } {}
   506    506   
   507         -# EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source
   508         -# following the FROM clause in a simple SELECT statement is handled as
   509         -# if it was a table containing the data returned by executing the
   510         -# sub-select statement.
          507  +# EVIDENCE-OF: R-59237-46742 A subquery specified in the
          508  +# table-or-subquery following the FROM clause in a simple SELECT
          509  +# statement is handled as if it was a table containing the data returned
          510  +# by executing the subquery statement.
   511    511   #
   512         -# EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset
   513         -# inherits the collation sequence and affinity of the corresponding
   514         -# expression in the sub-select statement.
          512  +# EVIDENCE-OF: R-27438-53558 Each column of the subquery has the
          513  +# collation sequence and affinity of the corresponding expression in the
          514  +# subquery statement.
   515    515   #
   516    516   foreach {tn subselect select spec} {
   517    517     1   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss%" 
   518    518         {t1 %ss%}
   519    519   
   520    520     2   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" 
   521    521         {t1 %ss% -on {te_equals 0 0}}

Changes to test/pager4.test.

     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12         -# Tests for the SQLITE_IOERR_NODB error condition: the database file file
           12  +# Tests for the SQLITE_READONLY_DBMOVED error condition: the database file
    13     13   # is unlinked or renamed out from under SQLite.
    14     14   #
    15     15   
    16     16   if {$tcl_platform(platform)!="unix"} return
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
           20  +
           21  +if {[permutation]=="inmemory_journal"} {
           22  +  finish_test
           23  +  return
           24  +}
    20     25   
    21     26   # Create a database file for testing
    22     27   #
    23     28   do_execsql_test pager4-1.1 {
    24     29     CREATE TABLE t1(a,b,c);
    25     30     INSERT INTO t1 VALUES(673,'stone','philips');
    26     31     SELECT * FROM t1;

Changes to test/pagerfault.test.

  1540   1540     faultsim_test_result {0 {}}
  1541   1541     catch { db close }
  1542   1542     catch { db2 close }
  1543   1543   }
  1544   1544   
  1545   1545   sqlite3_shutdown
  1546   1546   sqlite3_config_uri 0
         1547  +sqlite3_initialize
  1547   1548   
  1548   1549   finish_test

Changes to test/printf2.test.

    55     55   do_execsql_test printf2-1.10 {
    56     56     SELECT printf('%lld',314159.2653);
    57     57   } {314159}
    58     58   do_execsql_test printf2-1.11 {
    59     59     SELECT printf('%lld%n',314159.2653,'hi');
    60     60   } {314159}
    61     61   
    62         -# EVIDENCE-OF: R-20555-31089 The %z format is interchangable with %s.
           62  +# EVIDENCE-OF: R-17002-27534 The %z format is interchangeable with %s.
    63     63   #
    64     64   do_execsql_test printf2-1.12 {
    65     65     SELECT printf('%.*z',5,'abcdefghijklmnop');
    66     66   } {abcde}
    67     67   do_execsql_test printf2-1.13 {
    68     68     SELECT printf('%c','abcdefghijklmnop');
    69     69   } {a}

Changes to test/spellfix.test.

   230    230   do_execsql_test 6.1.2 {
   231    231     SELECT word, distance FROM t3 WHERE rowid = 10;
   232    232   } {keener {}}
   233    233   do_execsql_test 6.1.3 {
   234    234     SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
   235    235   } {keener 300}
   236    236   
          237  +ifcapable trace {
   237    238   proc trace_callback {sql} {
   238    239     if {[string range $sql 0 2] == "-- "} {
   239    240       lappend ::trace [string range $sql 3 end]
   240    241     }
   241    242   }
   242    243   
   243    244   proc do_tracesql_test {tn sql {res {}}} {
................................................................................
   260    261     {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?}
   261    262   }
   262    263   do_tracesql_test 6.2.3 {
   263    264     SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
   264    265   } {keener 300
   265    266     {SELECT id, word, rank, k1  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
   266    267   }
          268  +}
   267    269   
   268    270   
   269    271   
   270    272   
   271    273   finish_test

Changes to test/tester.tcl.

    55     55   #      do_ioerr_test          TESTNAME ARGS...
    56     56   #      crashsql               ARGS...
    57     57   #      integrity_check        TESTNAME ?DB?
    58     58   #      verify_ex_errcode      TESTNAME EXPECTED ?DB?
    59     59   #      do_test                TESTNAME SCRIPT EXPECTED
    60     60   #      do_execsql_test        TESTNAME SQL EXPECTED
    61     61   #      do_catchsql_test       TESTNAME SQL EXPECTED
           62  +#      do_timed_execsql_test  TESTNAME SQL EXPECTED
    62     63   #
    63     64   # Commands providing a lower level interface to the global test counters:
    64     65   #
    65     66   #      set_test_counter       COUNTER ?VALUE?
    66     67   #      omit_test              TESTNAME REASON ?APPEND?
    67     68   #      fail_test              TESTNAME
    68     69   #      incr_ntest
................................................................................
   719    720     fix_testname testname
   720    721     uplevel do_test [list $testname] [list "execsql {$sql}"] [list [list {*}$result]]
   721    722   }
   722    723   proc do_catchsql_test {testname sql result} {
   723    724     fix_testname testname
   724    725     uplevel do_test [list $testname] [list "catchsql {$sql}"] [list $result]
   725    726   }
          727  +proc do_timed_execsql_test {testname sql {result {}}} {
          728  +  fix_testname testname
          729  +  uplevel do_test [list $testname] [list "execsql_timed {$sql}"]\
          730  +                                   [list [list {*}$result]]
          731  +}
   726    732   proc do_eqp_test {name sql res} {
   727    733     uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
   728    734   }
   729    735   
   730    736   #-------------------------------------------------------------------------
   731    737   #   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
   732    738   #
................................................................................
  1014   1020   }
  1015   1021   
  1016   1022   # A procedure to execute SQL
  1017   1023   #
  1018   1024   proc execsql {sql {db db}} {
  1019   1025     # puts "SQL = $sql"
  1020   1026     uplevel [list $db eval $sql]
         1027  +}
         1028  +proc execsql_timed {sql {db db}} {
         1029  +  set tm [time {
         1030  +    set x [uplevel [list $db eval $sql]]
         1031  +  } 1]
         1032  +  set tm [lindex $tm 0]
         1033  +  puts -nonewline " ([expr {$tm*0.001}]ms) "
         1034  +  set x
  1021   1035   }
  1022   1036   
  1023   1037   # Execute SQL and catch exceptions.
  1024   1038   #
  1025   1039   proc catchsql {sql {db db}} {
  1026   1040     # puts "SQL = $sql"
  1027   1041     set r [catch [list uplevel [list $db eval $sql]] msg]

Changes to test/with1.test.

   448    448                             + ((ind-1)/27) * 27 + lp
   449    449                             + ((lp-1) / 3) * 6, 1)
   450    450              )
   451    451       )
   452    452     SELECT s FROM x WHERE ind=0;
   453    453   } {534678912672195348198342567859761423426853791713924856961537284287419635345286179}
   454    454   
          455  +#--------------------------------------------------------------------------
          456  +# Some tests that use LIMIT and OFFSET in the definition of recursive CTEs.
          457  +# 
          458  +set I [list 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20]
          459  +proc limit_test {tn iLimit iOffset} {
          460  +  if {$iOffset < 0} { set iOffset 0 }
          461  +  if {$iLimit < 0 } {
          462  +    set result [lrange $::I $iOffset end]
          463  +  } else {
          464  +    set result [lrange $::I $iOffset [expr $iLimit+$iOffset-1]]
          465  +  }
          466  +  uplevel [list do_execsql_test $tn [subst -nocommands {
          467  +    WITH ii(a) AS (
          468  +      VALUES(1)
          469  +      UNION ALL 
          470  +      SELECT a+1 FROM ii WHERE a<20 
          471  +      LIMIT $iLimit OFFSET $iOffset
          472  +    )
          473  +    SELECT * FROM ii
          474  +  }] $result]
          475  +}
          476  +
          477  +limit_test 9.1    20  0
          478  +limit_test 9.2     0  0
          479  +limit_test 9.3    19  1
          480  +limit_test 9.4    20 -1
          481  +limit_test 9.5     5  5
          482  +limit_test 9.6     0 -1
          483  +limit_test 9.7    40 -1
          484  +limit_test 9.8    -1 -1
          485  +limit_test 9.9    -1 -1
          486  +
          487  +#--------------------------------------------------------------------------
          488  +# Test the ORDER BY clause on recursive tables.
          489  +#
          490  +
          491  +do_execsql_test 10.1 {
          492  +  DROP TABLE IF EXISTS tree;
          493  +  CREATE TABLE tree(id INTEGER PRIMARY KEY, parentid, payload);
          494  +}
          495  +
          496  +proc insert_into_tree {L} {
          497  +  db eval { DELETE FROM tree }
          498  +  foreach key $L {
          499  +    unset -nocomplain parentid
          500  +    foreach seg [split $key /] {
          501  +      if {$seg==""} continue
          502  +      set id [db one {
          503  +        SELECT id FROM tree WHERE parentid IS $parentid AND payload=$seg
          504  +      }]
          505  +      if {$id==""} {
          506  +        db eval { INSERT INTO tree VALUES(NULL, $parentid, $seg) }
          507  +        set parentid [db last_insert_rowid]
          508  +      } else {
          509  +        set parentid $id
          510  +      }
          511  +    }
          512  +  }
          513  +}
          514  +
          515  +insert_into_tree {
          516  +  /a/a/a
          517  +  /a/b/c
          518  +  /a/b/c/d
          519  +  /a/b/d
          520  +}
          521  +do_execsql_test 10.2 {
          522  +  WITH flat(fid, p) AS (
          523  +    SELECT id, '/' || payload FROM tree WHERE parentid IS NULL
          524  +    UNION ALL
          525  +    SELECT id, p || '/' || payload FROM flat, tree WHERE parentid=fid
          526  +  )
          527  +  SELECT p FROM flat ORDER BY p;
          528  +} {
          529  +  /a /a/a /a/a/a 
          530  +     /a/b /a/b/c /a/b/c/d
          531  +          /a/b/d
          532  +}
          533  +
          534  +# Scan the tree-structure currently stored in table tree. Return a list
          535  +# of nodes visited.
          536  +#
          537  +proc scan_tree {bDepthFirst bReverse} {
          538  +
          539  +  set order "ORDER BY "
          540  +  if {$bDepthFirst==0} { append order "2 ASC," }
          541  +  if {$bReverse==0} { 
          542  +    append order " 3 ASC" 
          543  +  } else {
          544  +    append order " 3 DESC" 
          545  +  }
          546  +
          547  +  db eval "
          548  +    WITH flat(fid, depth, p) AS (
          549  +        SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
          550  +        UNION ALL
          551  +        SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
          552  +        $order
          553  +    )
          554  +    SELECT p FROM flat;
          555  +  "
          556  +}
          557  +
          558  +insert_into_tree {
          559  +  /a/b
          560  +  /a/b/c
          561  +  /a/d
          562  +  /a/d/e
          563  +  /a/d/f
          564  +  /g/h
          565  +}
          566  +
          567  +# Breadth first, siblings in ascending order.
          568  +#
          569  +do_test 10.3 {
          570  +  scan_tree 0 0
          571  +} [list {*}{
          572  +  /a /g
          573  +  /a/b /a/d /g/h
          574  +  /a/b/c /a/d/e /a/d/f
          575  +}]
          576  +
          577  +# Depth first, siblings in ascending order.
          578  +#
          579  +do_test 10.4 {
          580  +  scan_tree 1 0
          581  +} [list {*}{
          582  +  /a /a/b /a/b/c
          583  +     /a/d /a/d/e 
          584  +          /a/d/f
          585  +  /g /g/h
          586  +}]
          587  +
          588  +# Breadth first, siblings in descending order.
          589  +#
          590  +do_test 10.5 {
          591  +  scan_tree 0 1
          592  +} [list {*}{
          593  +  /g /a 
          594  +  /g/h /a/d /a/b 
          595  +  /a/d/f /a/d/e /a/b/c 
          596  +}]
          597  +
          598  +# Depth first, siblings in ascending order.
          599  +#
          600  +do_test 10.6 {
          601  +  scan_tree 1 1
          602  +} [list {*}{
          603  +  /g /g/h
          604  +  /a /a/d /a/d/f 
          605  +          /a/d/e 
          606  +     /a/b /a/b/c
          607  +}]
          608  +
          609  +
          610  +# Test name resolution in ORDER BY clauses.
          611  +#
          612  +do_catchsql_test 10.7.1 {
          613  +  WITH t(a) AS (
          614  +    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY a
          615  +  ) 
          616  +  SELECT * FROM t
          617  +} {1 {1st ORDER BY term does not match any column in the result set}}
          618  +do_execsql_test 10.7.2 {
          619  +  WITH t(a) AS (
          620  +    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY b
          621  +  ) 
          622  +  SELECT * FROM t
          623  +} {1 2 3 4 5}
          624  +do_execsql_test 10.7.3 {
          625  +  WITH t(a) AS (
          626  +    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY c
          627  +  ) 
          628  +  SELECT * FROM t
          629  +} {1 2 3 4 5}
          630  +
          631  +# Test COLLATE clauses attached to ORDER BY.
          632  +#
          633  +insert_into_tree {
          634  +  /a/b
          635  +  /a/C
          636  +  /a/d
          637  +  /B/e
          638  +  /B/F
          639  +  /B/g
          640  +  /c/h
          641  +  /c/I
          642  +  /c/j
          643  +}
          644  +
          645  +do_execsql_test 10.8.1 {
          646  +  WITH flat(fid, depth, p) AS (
          647  +    SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
          648  +    UNION ALL
          649  +    SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
          650  +    ORDER BY 2, 3 COLLATE nocase
          651  +  )
          652  +  SELECT p FROM flat;
          653  +} {
          654  +  /a /B /c
          655  +  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
          656  +}
          657  +do_execsql_test 10.8.2 {
          658  +  WITH flat(fid, depth, p) AS (
          659  +      SELECT id, 1, ('/' || payload) COLLATE nocase 
          660  +      FROM tree WHERE parentid IS NULL
          661  +    UNION ALL
          662  +      SELECT id, depth+1, (p||'/'||payload)
          663  +      FROM flat, tree WHERE parentid=fid
          664  +    ORDER BY 2, 3
          665  +  )
          666  +  SELECT p FROM flat;
          667  +} {
          668  +  /a /B /c
          669  +  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
          670  +}
          671  +
          672  +do_execsql_test 10.8.3 {
          673  +  WITH flat(fid, depth, p) AS (
          674  +      SELECT id, 1, ('/' || payload)
          675  +      FROM tree WHERE parentid IS NULL
          676  +    UNION ALL
          677  +      SELECT id, depth+1, (p||'/'||payload) COLLATE nocase 
          678  +      FROM flat, tree WHERE parentid=fid
          679  +    ORDER BY 2, 3
          680  +  )
          681  +  SELECT p FROM flat;
          682  +} {
          683  +  /a /B /c
          684  +  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
          685  +}
          686  +
          687  +do_execsql_test 10.8.4.1 {
          688  +  CREATE TABLE tst(a,b);
          689  +  INSERT INTO tst VALUES('a', 'A');
          690  +  INSERT INTO tst VALUES('b', 'B');
          691  +  INSERT INTO tst VALUES('c', 'C');
          692  +  SELECT a COLLATE nocase FROM tst UNION ALL SELECT b FROM tst ORDER BY 1;
          693  +} {a A b B c C}
          694  +do_execsql_test 10.8.4.2 {
          695  +  SELECT a FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
          696  +} {A B C a b c}
          697  +do_execsql_test 10.8.4.3 {
          698  +  SELECT a||'' FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
          699  +} {a A b B c C}
   455    700   
   456    701   # Test cases to illustrate on the ORDER BY clause on a recursive query can be
   457    702   # used to control depth-first versus breath-first search in a tree.
   458    703   #
   459         -do_execsql_test 9.1 {
          704  +do_execsql_test 11.1 {
   460    705     CREATE TABLE org(
   461    706       name TEXT PRIMARY KEY,
   462    707       boss TEXT REFERENCES org
   463    708     ) WITHOUT ROWID;
   464    709     INSERT INTO org VALUES('Alice',NULL);
   465    710     INSERT INTO org VALUES('Bob','Alice');
   466    711     INSERT INTO org VALUES('Cindy','Alice');
................................................................................
   509    754   .........Mary
   510    755   .........Noland
   511    756   .........Olivia}}
   512    757   
   513    758   # The previous query used "ORDER BY level" to yield a breath-first search.
   514    759   # Change that to "ORDER BY level DESC" for a depth-first search.
   515    760   #
   516         -do_execsql_test 9.2 {
          761  +do_execsql_test 11.2 {
   517    762     WITH RECURSIVE
   518    763       under_alice(name,level) AS (
   519    764          VALUES('Alice','0')
   520    765          UNION ALL
   521    766          SELECT org.name, under_alice.level+1
   522    767            FROM org, under_alice
   523    768           WHERE org.boss=under_alice.name
................................................................................
   540    785   ......Gail
   541    786   .........Noland
   542    787   .........Olivia}}
   543    788   
   544    789   # Without an ORDER BY clause, the recursive query should use a FIFO,
   545    790   # resulting in a breath-first search.
   546    791   #
   547         -do_execsql_test 9.3 {
          792  +do_execsql_test 11.3 {
   548    793     WITH RECURSIVE
   549    794       under_alice(name,level) AS (
   550    795          VALUES('Alice','0')
   551    796          UNION ALL
   552    797          SELECT org.name, under_alice.level+1
   553    798            FROM org, under_alice
   554    799           WHERE org.boss=under_alice.name
................................................................................
   568    813   .........Kate
   569    814   .........Lanny
   570    815   .........Mary
   571    816   .........Noland
   572    817   .........Olivia}}
   573    818   
   574    819   finish_test
          820  +

Changes to tool/omittest.tcl.

   186    186       SQLITE_OMIT_BTREECOUNT \
   187    187       SQLITE_OMIT_BUILTIN_TEST \
   188    188       SQLITE_OMIT_CAST \
   189    189       SQLITE_OMIT_CHECK \
   190    190       SQLITE_OMIT_COMPILEOPTION_DIAGS \
   191    191       SQLITE_OMIT_COMPLETE \
   192    192       SQLITE_OMIT_COMPOUND_SELECT \
          193  +    SQLITE_OMIT_CTE \
   193    194       SQLITE_OMIT_DATETIME_FUNCS \
   194    195       SQLITE_OMIT_DECLTYPE \
   195    196       SQLITE_OMIT_DEPRECATED \
   196    197       SQLITE_OMIT_EXPLAIN \
   197    198       SQLITE_OMIT_FLAG_PRAGMAS \
   198    199       SQLITE_OMIT_FLOATING_POINT \
   199    200       SQLITE_OMIT_FOREIGN_KEY \