SQLite

Check-in [7b5f377386]
Login

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

Overview
Comment:Bring in the latest updates from trunk.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 7b5f3773867ed0e4ed17bd473ba972d500035318
User & Date: drh 2014-01-28 18:06:17.362
Context
2014-01-29
14:21
Merge latest fixes from the trunk. (check-in: 6b6dcd4cc7 user: dan tags: sessions)
2014-01-28
18:06
Bring in the latest updates from trunk. (check-in: 7b5f377386 user: drh tags: sessions)
17:49
Minor bugfix in main.c so that the library builds with SQLITE_OMIT_WSD defined. (check-in: 5e3b9ecc7b user: dan tags: trunk)
2014-01-24
14:05
Bring in all the latest trunk changes, including the Common Table Expressions implementation. (check-in: 9b43e55919 user: drh tags: sessions)
Changes
Side-by-Side Diff Show Whitespace Changes Patch
Changes to ext/fts3/fts3_hash.c.
92
93
94
95
96
97
98
99

100
101
102
103
104
105

106
107
108
109
110
111
112
92
93
94
95
96
97
98

99
100
101
102
103
104

105
106
107
108
109
110
111
112







-
+





-
+







}

/*
** Hash and comparison functions when the mode is FTS3_HASH_STRING
*/
static int fts3StrHash(const void *pKey, int nKey){
  const char *z = (const char *)pKey;
  int h = 0;
  unsigned h = 0;
  if( nKey<=0 ) nKey = (int) strlen(z);
  while( nKey > 0  ){
    h = (h<<3) ^ h ^ *z++;
    nKey--;
  }
  return h & 0x7fffffff;
  return (int)(h & 0x7fffffff);
}
static int fts3StrCompare(const void *pKey1, int n1, const void *pKey2, int n2){
  if( n1!=n2 ) return 1;
  return strncmp((const char*)pKey1,(const char*)pKey2,n1);
}

/*
Changes to src/delete.c.
639
640
641
642
643
644
645

646

647
648
649
650
651
652
653
639
640
641
642
643
644
645
646

647
648
649
650
651
652
653
654







+
-
+







    iOld = pParse->nMem+1;
    pParse->nMem += (1 + pTab->nCol);

    /* Populate the OLD.* pseudo-table register array. These values will be 
    ** used by any BEFORE and AFTER triggers that exist.  */
    sqlite3VdbeAddOp2(v, OP_Copy, iPk, iOld);
    for(iCol=0; iCol<pTab->nCol; iCol++){
      testcase( mask!=0xffffffff && iCol==31 );
      if( mask==0xffffffff || mask&(1<<iCol) ){
      if( mask==0xffffffff || (mask & MASKBIT32(iCol))!=0 ){
        sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, iCol, iOld+iCol+1);
      }
    }

    /* Invoke BEFORE DELETE trigger programs. */
    addrStart = sqlite3VdbeCurrentAddr(v);
    sqlite3CodeRowTrigger(pParse, pTrigger, 
Changes to src/expr.c.
2681
2682
2683
2684
2685
2686
2687
2688

2689
2690
2691
2692
2693
2694
2695
2681
2682
2683
2684
2685
2686
2687

2688
2689
2690
2691
2692
2693
2694
2695







-
+







    }
    case TK_FUNCTION: {
      ExprList *pFarg;       /* List of function arguments */
      int nFarg;             /* Number of function arguments */
      FuncDef *pDef;         /* The function definition object */
      int nId;               /* Length of the function name in bytes */
      const char *zId;       /* The function name */
      int constMask = 0;     /* Mask of function arguments that are constant */
      u32 constMask = 0;     /* Mask of function arguments that are constant */
      int i;                 /* Loop counter */
      u8 enc = ENC(db);      /* The text encoding used by this database */
      CollSeq *pColl = 0;    /* A collating sequence */

      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
      if( ExprHasProperty(pExpr, EP_TokenOnly) ){
        pFarg = 0;
2732
2733
2734
2735
2736
2737
2738

2739

2740
2741
2742
2743
2744
2745
2746
2732
2733
2734
2735
2736
2737
2738
2739

2740
2741
2742
2743
2744
2745
2746
2747







+
-
+







        assert( nFarg>=1 );
        sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
        break;
      }

      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
          testcase( i==31 );
          constMask |= (1<<i);
          constMask |= MASKBIT32(i);
        }
        if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){
          pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr);
        }
      }
      if( pFarg ){
        if( constMask ){
Changes to src/func.c.
133
134
135
136
137
138
139
140

141
142
143
144
145
146
147
133
134
135
136
137
138
139

140
141
142
143
144
145
146
147







-
+







static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  assert( argc==1 );
  UNUSED_PARAMETER(argc);
  switch( sqlite3_value_type(argv[0]) ){
    case SQLITE_INTEGER: {
      i64 iVal = sqlite3_value_int64(argv[0]);
      if( iVal<0 ){
        if( (iVal<<1)==0 ){
        if( iVal==SMALLEST_INT64 ){
          /* IMP: R-31676-45509 If X is the integer -9223372036854775808
          ** then abs(X) throws an integer overflow error since there is no
          ** equivalent positive 64-bit two complement value. */
          sqlite3_result_error(context, "integer overflow", -1);
          return;
        }
        iVal = -iVal;
Changes to src/hash.c.
49
50
51
52
53
54
55
56

57
58
59
60
61
62
63
49
50
51
52
53
54
55

56
57
58
59
60
61
62
63







-
+







  pH->count = 0;
}

/*
** The hashing function.
*/
static unsigned int strHash(const char *z, int nKey){
  int h = 0;
  unsigned int h = 0;
  assert( nKey>=0 );
  while( nKey > 0  ){
    h = (h<<3) ^ h ^ sqlite3UpperToLower[(unsigned char)*z++];
    nKey--;
  }
  return h;
}
Changes to src/main.c.
3320
3321
3322
3323
3324
3325
3326
3327

3328
3329
3330
3331
3332
3333
3334
3320
3321
3322
3323
3324
3325
3326

3327
3328
3329
3330
3331
3332
3333
3334







-
+







    ** Set or clear a flag that indicates that the database file is always well-
    ** formed and never corrupt.  This flag is clear by default, indicating that
    ** database files might have arbitrary corruption.  Setting the flag during
    ** testing causes certain assert() statements in the code to be activated
    ** that demonstrat invariants on well-formed database files.
    */
    case SQLITE_TESTCTRL_NEVER_CORRUPT: {
      sqlite3Config.neverCorrupt = va_arg(ap, int);
      sqlite3GlobalConfig.neverCorrupt = va_arg(ap, int);
      break;
    }

  }
  va_end(ap);
#endif /* SQLITE_OMIT_BUILTIN_TEST */
  return rc;
Changes to src/os_unix.c.
4093
4094
4095
4096
4097
4098
4099
4100

4101
4102
4103
4104
4105
4106
4107
4093
4094
4095
4096
4097
4098
4099

4100
4101
4102
4103
4104
4105
4106
4107







-
+







    rc = (rc!=(-1)) ? SQLITE_OK : SQLITE_BUSY;
  }

  /* Update the global lock state and do debug tracing */
#ifdef SQLITE_DEBUG
  { u16 mask;
  OSTRACE(("SHM-LOCK "));
  mask = ofst>31 ? 0xffffffff : (1<<(ofst+n)) - (1<<ofst);
  mask = ofst>31 ? 0xffff : (1<<(ofst+n)) - (1<<ofst);
  if( rc==SQLITE_OK ){
    if( lockType==F_UNLCK ){
      OSTRACE(("unlock %d ok", ofst));
      pShmNode->exclMask &= ~mask;
      pShmNode->sharedMask &= ~mask;
    }else if( lockType==F_RDLCK ){
      OSTRACE(("read-lock %d ok", ofst));
Changes to src/pcache1.c.
716
717
718
719
720
721
722

723
724
725
726
727
728
729
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730







+







  int createFlag
){
  unsigned int nPinned;
  PCache1 *pCache = (PCache1 *)p;
  PGroup *pGroup;
  PgHdr1 *pPage = 0;

  assert( offsetof(PgHdr1,page)==0 );
  assert( pCache->bPurgeable || createFlag!=1 );
  assert( pCache->bPurgeable || pCache->nMin==0 );
  assert( pCache->bPurgeable==0 || pCache->nMin==10 );
  assert( pCache->nMin==0 || pCache->bPurgeable );
  pcache1EnterMutex(pGroup = pCache->pGroup);

  /* Step 1: Search the hash table for an existing entry. */
821
822
823
824
825
826
827
828

829
830
831
832
833
834
835
822
823
824
825
826
827
828

829
830
831
832
833
834
835
836







-
+







  }

fetch_out:
  if( pPage && iKey>pCache->iMaxKey ){
    pCache->iMaxKey = iKey;
  }
  pcache1LeaveMutex(pGroup);
  return &pPage->page;
  return (sqlite3_pcache_page*)pPage;
}


/*
** Implementation of the sqlite3_pcache.xUnpin method.
**
** Mark a page as unpinned (eligible for asynchronous recycling).
Changes to src/select.c.
1721
1722
1723
1724
1725
1726
1727
1728






































1729
1730
1731
1732
1733
1734
1735
1721
1722
1723
1724
1725
1726
1727

1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772







-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+







  }
  assert( iCol>=0 );
  if( pRet==0 && iCol<p->pEList->nExpr ){
    pRet = sqlite3ExprCollSeq(pParse, p->pEList->a[iCol].pExpr);
  }
  return pRet;
}
#endif /* SQLITE_OMIT_COMPOUND_SELECT */

/*
** The select statement passed as the second parameter is a compound SELECT
** with an ORDER BY clause. This function allocates and returns a KeyInfo
** structure suitable for implementing the ORDER BY.
**
** Space to hold the KeyInfo structure is obtained from malloc. The calling
** function is responsible for ensuring that this structure is eventually
** freed.
*/
static KeyInfo *multiSelectOrderByKeyInfo(Parse *pParse, Select *p, int nExtra){
  ExprList *pOrderBy = p->pOrderBy;
  int nOrderBy = p->pOrderBy->nExpr;
  sqlite3 *db = pParse->db;
  KeyInfo *pRet = sqlite3KeyInfoAlloc(db, nOrderBy+nExtra, 1);
  if( pRet ){
    int i;
    for(i=0; i<nOrderBy; i++){
      struct ExprList_item *pItem = &pOrderBy->a[i];
      Expr *pTerm = pItem->pExpr;
      CollSeq *pColl;

      if( pTerm->flags & EP_Collate ){
        pColl = sqlite3ExprCollSeq(pParse, pTerm);
      }else{
        pColl = multiSelectCollSeq(pParse, p, pItem->u.x.iOrderByCol-1);
        if( pColl==0 ) pColl = db->pDfltColl;
        pOrderBy->a[i].pExpr =
          sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
      }
      assert( sqlite3KeyInfoIsWriteable(pRet) );
      pRet->aColl[i] = pColl;
      pRet->aSortOrder[i] = pOrderBy->a[i].sortOrder;
    }
  }

  return pRet;
}

#ifndef SQLITE_OMIT_CTE
/*
** This routine generates VDBE code to compute the content of a WITH RECURSIVE
** query of the form:
**
**   <recursive-table> AS (<setup-query> UNION [ALL] <recursive-query>)
1795
1796
1797
1798
1799
1800
1801

1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831

1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842



1843
1844
1845
1846
1847
1848
1849
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848




1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864

1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886







+









-
-
-
-
















-
+











+
+
+







  computeLimitRegisters(pParse, p, addrBreak);
  pLimit = p->pLimit;
  pOffset = p->pOffset;
  regLimit = p->iLimit;
  regOffset = p->iOffset;
  p->pLimit = p->pOffset = 0;
  p->iLimit = p->iOffset = 0;
  pOrderBy = p->pOrderBy;

  /* Locate the cursor number of the Current table */
  for(i=0; ALWAYS(i<pSrc->nSrc); i++){
    if( pSrc->a[i].isRecursive ){
      iCurrent = pSrc->a[i].iCursor;
      break;
    }
  }

  /* Detach the ORDER BY clause from the compound SELECT */
  pOrderBy = p->pOrderBy;
  p->pOrderBy = 0;

  /* Allocate cursors numbers for Queue and Distinct.  The cursor number for
  ** the Distinct table must be exactly one greater than Queue in order
  ** for the SRT_DistTable and SRT_DistQueue destinations to work. */
  iQueue = pParse->nTab++;
  if( p->op==TK_UNION ){
    eDest = pOrderBy ? SRT_DistQueue : SRT_DistTable;
    iDistinct = pParse->nTab++;
  }else{
    eDest = pOrderBy ? SRT_Queue : SRT_Table;
  }
  sqlite3SelectDestInit(&destQueue, eDest, iQueue);

  /* Allocate cursors for Current, Queue, and Distinct. */
  regCurrent = ++pParse->nMem;
  sqlite3VdbeAddOp3(v, OP_OpenPseudo, iCurrent, regCurrent, nCol);
  if( pOrderBy ){
    KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pOrderBy, 1);
    KeyInfo *pKeyInfo = multiSelectOrderByKeyInfo(pParse, p, 1);
    sqlite3VdbeAddOp4(v, OP_OpenEphemeral, iQueue, pOrderBy->nExpr+2, 0,
                      (char*)pKeyInfo, P4_KEYINFO);
    destQueue.pOrderBy = pOrderBy;
  }else{
    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iQueue, nCol);
  }
  VdbeComment((v, "Queue table"));
  if( iDistinct ){
    p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iDistinct, 0);
    p->selFlags |= SF_UsesEphemeral;
  }

  /* Detach the ORDER BY clause from the compound SELECT */
  p->pOrderBy = 0;

  /* Store the results of the setup-query in Queue. */
  rc = sqlite3Select(pParse, pSetup, &destQueue);
  if( rc ) goto end_of_recursive_query;

  /* Find the next row in the Queue and output that row */
  addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak);
1879
1880
1881
1882
1883
1884
1885
1886

1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1916
1917
1918
1919
1920
1921
1922

1923
1924
1925
1926
1927
1928
1929
1930
1931
1932

1933
1934
1935
1936
1937
1938
1939







-
+









-








end_of_recursive_query:
  p->pOrderBy = pOrderBy;
  p->pLimit = pLimit;
  p->pOffset = pOffset;
  return;
}
#endif
#endif /* SQLITE_OMIT_CTE */

/* Forward references */
static int multiSelectOrderBy(
  Parse *pParse,        /* Parsing context */
  Select *p,            /* The right-most of SELECTs to be coded */
  SelectDest *pDest     /* What to do with query results */
);


#ifndef SQLITE_OMIT_COMPOUND_SELECT
/*
** This routine is called to process a compound query form from
** two or more separate queries using UNION, UNION ALL, EXCEPT, or
** INTERSECT
**
** "p" points to the right-most of the two queries.  the query on the
** left is p->pPrior.  The left query could also be a compound query
2621
2622
2623
2624
2625
2626
2627
2628
2629

2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2657
2658
2659
2660
2661
2662
2663


2664
















2665
2666
2667
2668
2669
2670
2671







-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-







  if( aPermute ){
    struct ExprList_item *pItem;
    for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){
      assert( pItem->u.x.iOrderByCol>0
          && pItem->u.x.iOrderByCol<=p->pEList->nExpr );
      aPermute[i] = pItem->u.x.iOrderByCol - 1;
    }
    pKeyMerge = sqlite3KeyInfoAlloc(db, nOrderBy, 1);
    if( pKeyMerge ){
    pKeyMerge = multiSelectOrderByKeyInfo(pParse, p, 1);
      for(i=0; i<nOrderBy; i++){
        CollSeq *pColl;
        Expr *pTerm = pOrderBy->a[i].pExpr;
        if( pTerm->flags & EP_Collate ){
          pColl = sqlite3ExprCollSeq(pParse, pTerm);
        }else{
          pColl = multiSelectCollSeq(pParse, p, aPermute[i]);
          if( pColl==0 ) pColl = db->pDfltColl;
          pOrderBy->a[i].pExpr =
             sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
        }
        assert( sqlite3KeyInfoIsWriteable(pKeyMerge) );
        pKeyMerge->aColl[i] = pColl;
        pKeyMerge->aSortOrder[i] = pOrderBy->a[i].sortOrder;
      }
    }
  }else{
    pKeyMerge = 0;
  }

  /* Reattach the ORDER BY clause to the query.
  */
  p->pOrderBy = pOrderBy;
Changes to src/sqliteInt.h.
1986
1987
1988
1989
1990
1991
1992

1993
1994
1995
1996
1997
1998
1999
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000







+







*/
#define BMS  ((int)(sizeof(Bitmask)*8))

/*
** A bit in a Bitmask
*/
#define MASKBIT(n)   (((Bitmask)1)<<(n))
#define MASKBIT32(n) (((unsigned int)1)<<(n))

/*
** The following structure describes the FROM clause of a SELECT statement.
** Each table or subquery in the FROM clause is a separate element of
** the SrcList.a[] array.
**
** With the addition of multiple database support, the following structure
Changes to src/tclsqlite.c.
913
914
915
916
917
918
919
920

921
922
923
924
925
926
927
913
914
915
916
917
918
919

920
921
922
923
924
925
926
927







-
+







  void *pArg,
  int code,
  const char *zArg1,
  const char *zArg2,
  const char *zArg3,
  const char *zArg4
){
  char *zCode;
  const char *zCode;
  Tcl_DString str;
  int rc;
  const char *zReply;
  SqliteDb *pDb = (SqliteDb*)pArg;
  if( pDb->disableAuth ) return SQLITE_OK;

  switch( code ){
1039
1040
1041
1042
1043
1044
1045
1046

1047
1048
1049
1050
1051
1052
1053
1039
1040
1041
1042
1043
1044
1045

1046
1047
1048
1049
1050
1051
1052
1053







-
+







** the transaction or savepoint opened by the [transaction] command.
*/
static int DbTransPostCmd(
  ClientData data[],                   /* data[0] is the Sqlite3Db* for $db */
  Tcl_Interp *interp,                  /* Tcl interpreter */
  int result                           /* Result of evaluating SCRIPT */
){
  static const char *azEnd[] = {
  static const char *const azEnd[] = {
    "RELEASE _tcl_transaction",        /* rc==TCL_ERROR, nTransaction!=0 */
    "COMMIT",                          /* rc!=TCL_ERROR, nTransaction==0 */
    "ROLLBACK TO _tcl_transaction ; RELEASE _tcl_transaction",
    "ROLLBACK"                         /* rc==TCL_ERROR, nTransaction==0 */
  };
  SqliteDb *pDb = (SqliteDb*)data[0];
  int rc = result;
2018
2019
2020
2021
2022
2023
2024
2025

2026
2027
2028
2029
2030
2031
2032
2018
2019
2020
2021
2022
2023
2024

2025
2026
2027
2028
2029
2030
2031
2032







-
+







      Tcl_WrongNumArgs(interp, 2, objv, "?CALLBACK?");
      return TCL_ERROR;
    }else if( objc==2 ){
      if( pDb->zCommit ){
        Tcl_AppendResult(interp, pDb->zCommit, 0);
      }
    }else{
      char *zCommit;
      const char *zCommit;
      int len;
      if( pDb->zCommit ){
        Tcl_Free(pDb->zCommit);
      }
      zCommit = Tcl_GetStringFromObj(objv[2], &len);
      if( zCommit && len>0 ){
        pDb->zCommit = Tcl_Alloc( len + 1 );
2091
2092
2093
2094
2095
2096
2097
2098

2099
2100
2101
2102
2103
2104
2105


2106
2107
2108
2109
2110
2111
2112
2091
2092
2093
2094
2095
2096
2097

2098
2099
2100
2101
2102
2103


2104
2105
2106
2107
2108
2109
2110
2111
2112







-
+





-
-
+
+







    int nByte;                  /* Number of bytes in an SQL string */
    int i, j;                   /* Loop counters */
    int nSep;                   /* Number of bytes in zSep[] */
    int nNull;                  /* Number of bytes in zNull[] */
    char *zSql;                 /* An SQL statement */
    char *zLine;                /* A single line of input from the file */
    char **azCol;               /* zLine[] broken up into columns */
    char *zCommit;              /* How to commit changes */
    const char *zCommit;        /* How to commit changes */
    FILE *in;                   /* The input file */
    int lineno = 0;             /* Line number of input file */
    char zLineNum[80];          /* Line number print buffer */
    Tcl_Obj *pResult;           /* interp result */

    char *zSep;
    char *zNull;
    const char *zSep;
    const char *zNull;
    if( objc<5 || objc>7 ){
      Tcl_WrongNumArgs(interp, 2, objv, 
         "CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?");
      return TCL_ERROR;
    }
    if( objc>=6 ){
      zSep = Tcl_GetStringFromObj(objv[5], 0);
3077
3078
3079
3080
3081
3082
3083
3084

3085
3086
3087
3088
3089
3090
3091
3077
3078
3079
3080
3081
3082
3083

3084
3085
3086
3087
3088
3089
3090
3091







-
+







#else
  flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX;
#endif

  if( objc==2 ){
    zArg = Tcl_GetStringFromObj(objv[1], 0);
    if( strcmp(zArg,"-version")==0 ){
      Tcl_AppendResult(interp,sqlite3_version,0);
      Tcl_AppendResult(interp,sqlite3_libversion(),0);
      return TCL_OK;
    }
    if( strcmp(zArg,"-has-codec")==0 ){
#ifdef SQLITE_HAS_CODEC
      Tcl_AppendResult(interp,"1",0);
#else
      Tcl_AppendResult(interp,"0",0);
3159
3160
3161
3162
3163
3164
3165
3166

3167
3168
3169
3170
3171
3172
3173
3159
3160
3161
3162
3163
3164
3165

3166
3167
3168
3169
3170
3171
3172
3173







-
+







#endif
    );
    return TCL_ERROR;
  }
  zErrMsg = 0;
  p = (SqliteDb*)Tcl_Alloc( sizeof(*p) );
  if( p==0 ){
    Tcl_SetResult(interp, "malloc failed", TCL_STATIC);
    Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
    return TCL_ERROR;
  }
  memset(p, 0, sizeof(*p));
  zFile = Tcl_GetStringFromObj(objv[2], 0);
  zFile = Tcl_TranslateFileName(interp, zFile, &translatedFilename);
  rc = sqlite3_open_v2(zFile, &p->db, flags, zVfs);
  Tcl_DStringFree(&translatedFilename);
Changes to src/test8.c.
888
889
890
891
892
893
894
895

896
897
898
899
900
901
902
888
889
890
891
892
893
894

895
896
897
898
899
900
901
902







-
+







  pIdxInfo->idxNum = hashString(zQuery);
  pIdxInfo->idxStr = zQuery;
  pIdxInfo->needToFreeIdxStr = 1;
  if( useCost ){
    pIdxInfo->estimatedCost = cost;
  }else if( useIdx ){
    /* Approximation of log2(nRow). */
    for( ii=0; ii<(sizeof(int)*8); ii++ ){
    for( ii=0; ii<(sizeof(int)*8)-1; ii++ ){
      if( nRow & (1<<ii) ){
        pIdxInfo->estimatedCost = (double)ii;
      }
    }
  }else{
    pIdxInfo->estimatedCost = (double)nRow;
  }
Changes to src/update.c.
463
464
465
466
467
468
469
470

471
472

473
474
475
476
477
478
479
463
464
465
466
467
468
469

470
471
472
473
474
475
476
477
478
479
480







-
+


+







  if( chngPk || hasFK || pTrigger ){
    u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
    oldmask |= sqlite3TriggerColmask(pParse, 
        pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError
    );
    for(i=0; i<pTab->nCol; i++){
      if( oldmask==0xffffffff
       || (i<32 && (oldmask & (1<<i)))
       || (i<32 && (oldmask & MASKBIT32(i))!=0)
       || (pTab->aCol[i].colFlags & COLFLAG_PRIMKEY)!=0
      ){
        testcase(  oldmask!=0xffffffff && i==31 );
        sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regOld+i);
      }else{
        sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
      }
    }
    if( chngRowid==0 && pPk==0 ){
      sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
500
501
502
503
504
505
506
507

508
509
510
511
512
513
514
501
502
503
504
505
506
507

508
509
510
511
512
513
514
515







-
+







  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i);
    }else{
      j = aXRef[i];
      if( j>=0 ){
        sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
      }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){
      }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i)) ){
        /* This branch loads the value of a column that will not be changed 
        ** into a register. This is done if there are no BEFORE triggers, or
        ** if there are one or more BEFORE triggers that use this value via
        ** a new.* reference in a trigger program.
        */
        testcase( i==31 );
        testcase( i==32 );
Changes to src/util.c.
997
998
999
1000
1001
1002
1003

1004

1005
1006
1007
1008
1009
1010
1011
997
998
999
1000
1001
1002
1003
1004

1005
1006
1007
1008
1009
1010
1011
1012







+
-
+







}


/*
** Read or write a four-byte big-endian integer value.
*/
u32 sqlite3Get4byte(const u8 *p){
  testcase( p[0]&0x80 );
  return (p[0]<<24) | (p[1]<<16) | (p[2]<<8) | p[3];
  return ((unsigned)p[0]<<24) | (p[1]<<16) | (p[2]<<8) | p[3];
}
void sqlite3Put4byte(unsigned char *p, u32 v){
  p[0] = (u8)(v>>24);
  p[1] = (u8)(v>>16);
  p[2] = (u8)(v>>8);
  p[3] = (u8)v;
}
Changes to src/vdbe.c.
6231
6232
6233
6234
6235
6236
6237
6238

6239
6240
6241
6242
6243
6244
6245
6231
6232
6233
6234
6235
6236
6237

6238
6239
6240
6241
6242
6243
6244
6245







-
+







    sqlite3DbFree(db, z);
  }
#ifdef SQLITE_USE_FCNTL_TRACE
  zTrace = (pOp->p4.z ? pOp->p4.z : p->zSql);
  if( zTrace ){
    int i;
    for(i=0; i<db->nDb; i++){
      if( ((1<<i) & p->btreeMask)==0 ) continue;
      if( MASKBIT(i) & p->btreeMask)==0 ) continue;
      sqlite3_file_control(db, db->aDb[i].zName, SQLITE_FCNTL_TRACE, zTrace);
    }
  }
#endif /* SQLITE_USE_FCNTL_TRACE */
#ifdef SQLITE_DEBUG
  if( (db->flags & SQLITE_SqlTrace)!=0
   && (zTrace = (pOp->p4.z ? pOp->p4.z : p->zSql))!=0
Changes to src/vdbeaux.c.
2612
2613
2614
2615
2616
2617
2618
2619

2620

2621
2622
2623
2624
2625
2626
2627
2612
2613
2614
2615
2616
2617
2618

2619
2620
2621
2622
2623
2624
2625
2626
2627
2628







-
+

+







**      function parameter corrsponds to bit 0 etc.).
*/
void sqlite3VdbeDeleteAuxData(Vdbe *pVdbe, int iOp, int mask){
  AuxData **pp = &pVdbe->pAuxData;
  while( *pp ){
    AuxData *pAux = *pp;
    if( (iOp<0)
     || (pAux->iOp==iOp && (pAux->iArg>31 || !(mask & ((u32)1<<pAux->iArg))))
     || (pAux->iOp==iOp && (pAux->iArg>31 || !(mask & MASKBIT32(pAux->iArg))))
    ){
      testcase( pAux->iArg==31 );
      if( pAux->xDelete ){
        pAux->xDelete(pAux->pAux);
      }
      *pp = pAux->pNext;
      sqlite3DbFree(pVdbe->db, pAux);
    }else{
      pp= &pAux->pNext;
2928
2929
2930
2931
2932
2933
2934



2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947

2948

2949
2950
2951
2952

2953

2954
2955
2956
2957
2958


2959
2960
2961
2962
2963
2964


2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988


2989
2990
2991
2992
2993
2994
2995
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952

2953
2954
2955
2956
2957
2958

2959
2960
2961
2962
2963

2964
2965
2966
2967
2968
2969


2970
2971
2972
2973
2974
2975
2976
2977
2978


2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990



2991
2992
2993
2994
2995
2996
2997
2998
2999







+
+
+













+
-
+




+
-
+




-
+
+




-
-
+
+







-
-












-
-
-
+
+







** and store the result in pMem.  Return the number of bytes read.
*/ 
u32 sqlite3VdbeSerialGet(
  const unsigned char *buf,     /* Buffer to deserialize from */
  u32 serial_type,              /* Serial type to deserialize */
  Mem *pMem                     /* Memory cell to write value into */
){
  u64 x;
  u32 y;
  int i;
  switch( serial_type ){
    case 10:   /* Reserved for future use */
    case 11:   /* Reserved for future use */
    case 0: {  /* NULL */
      pMem->flags = MEM_Null;
      break;
    }
    case 1: { /* 1-byte signed integer */
      pMem->u.i = (signed char)buf[0];
      pMem->flags = MEM_Int;
      return 1;
    }
    case 2: { /* 2-byte signed integer */
      i = 256*(signed char)buf[0] | buf[1];
      pMem->u.i = (((signed char)buf[0])<<8) | buf[1];
      pMem->u.i = (i64)i;
      pMem->flags = MEM_Int;
      return 2;
    }
    case 3: { /* 3-byte signed integer */
      i = 65536*(signed char)buf[0] | (buf[1]<<8) | buf[2];
      pMem->u.i = (((signed char)buf[0])<<16) | (buf[1]<<8) | buf[2];
      pMem->u.i = (i64)i;
      pMem->flags = MEM_Int;
      return 3;
    }
    case 4: { /* 4-byte signed integer */
      pMem->u.i = (buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
      y = ((unsigned)buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
      pMem->u.i = (i64)*(int*)&y;
      pMem->flags = MEM_Int;
      return 4;
    }
    case 5: { /* 6-byte signed integer */
      u64 x = (((signed char)buf[0])<<8) | buf[1];
      u32 y = (buf[2]<<24) | (buf[3]<<16) | (buf[4]<<8) | buf[5];
      x = 256*(signed char)buf[0] + buf[1];
      y = ((unsigned)buf[2]<<24) | (buf[3]<<16) | (buf[4]<<8) | buf[5];
      x = (x<<32) | y;
      pMem->u.i = *(i64*)&x;
      pMem->flags = MEM_Int;
      return 6;
    }
    case 6:   /* 8-byte signed integer */
    case 7: { /* IEEE floating point */
      u64 x;
      u32 y;
#if !defined(NDEBUG) && !defined(SQLITE_OMIT_FLOATING_POINT)
      /* Verify that integers and floating point values use the same
      ** byte order.  Or, that if SQLITE_MIXED_ENDIAN_64BIT_FLOAT is
      ** defined that 64-bit floating point values really are mixed
      ** endian.
      */
      static const u64 t1 = ((u64)0x3ff00000)<<32;
      static const double r1 = 1.0;
      u64 t2 = t1;
      swapMixedEndianFloat(t2);
      assert( sizeof(r1)==sizeof(t2) && memcmp(&r1, &t2, sizeof(r1))==0 );
#endif

      x = (buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
      y = (buf[4]<<24) | (buf[5]<<16) | (buf[6]<<8) | buf[7];
      x = ((unsigned)buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
      y = ((unsigned)buf[4]<<24) | (buf[5]<<16) | (buf[6]<<8) | buf[7];
      x = (x<<32) | y;
      if( serial_type==6 ){
        pMem->u.i = *(i64*)&x;
        pMem->flags = MEM_Int;
      }else{
        assert( sizeof(x)==8 && sizeof(pMem->r)==8 );
        swapMixedEndianFloat(x);
Changes to src/vdbemem.c.
594
595
596
597
598
599
600
601

602
603
604
605
606
607
608
594
595
596
597
598
599
600

601
602
603
604
605
606
607
608







-
+







  pMem->pScopyFrom = 0;
}
#endif /* SQLITE_DEBUG */

/*
** Size of struct Mem not including the Mem.zMalloc member.
*/
#define MEMCELLSIZE (size_t)(&(((Mem *)0)->zMalloc))
#define MEMCELLSIZE offsetof(Mem,zMalloc)

/*
** Make an shallow copy of pFrom into pTo.  Prior contents of
** pTo are freed.  The pFrom->z field is not duplicated.  If
** pFrom->z is used, then pTo->z points to the same thing as pFrom->z
** and flags gets srcType (either MEM_Ephem or MEM_Static).
*/
Changes to test/closure01.test.
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

107
108











109
110
111
112
113
114
115
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
107
108
109

110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165







-
+






+
+
+

-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-






-
+


+
+
+
+
+
+
+
-
+
+
+
+

-
+


+
+
+
+
+
+
+
+
-
+
+
+
+

-
+


+
+
+
+
+
+
+
+
+
+
+










-
+






+
+
+
+
+
+
+
+
-
+
+
+
+

-
+



+
+
+
+
+
+
+
+
+
+
+




















-
+


+
+
+
+
+
+
+
+
+
+
+







# 
# Test cases for transitive_closure virtual table.

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix closure01

ifcapable !vtab { finish_test ; return }
ifcapable !vtab||!cte { finish_test ; return }

load_static_extension db closure

do_execsql_test 1.0 {
  BEGIN;
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER);
  WITH RECURSIVE
    cnt(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM cnt LIMIT 131072)
  INSERT INTO t1(x, y) SELECT i, nullif(i,1)/2 FROM cnt;
  CREATE INDEX t1y ON t1(y);
  INSERT INTO t1(x) VALUES(1),(2);
  INSERT INTO t1(x) SELECT x+2 FROM t1;
  INSERT INTO t1(x) SELECT x+4 FROM t1;
  INSERT INTO t1(x) SELECT x+8 FROM t1;
  INSERT INTO t1(x) SELECT x+16 FROM t1;
  INSERT INTO t1(x) SELECT x+32 FROM t1;
  INSERT INTO t1(x) SELECT x+64 FROM t1;
  INSERT INTO t1(x) SELECT x+128 FROM t1;
  INSERT INTO t1(x) SELECT x+256 FROM t1;
  INSERT INTO t1(x) SELECT x+512 FROM t1;
  INSERT INTO t1(x) SELECT x+1024 FROM t1;
  INSERT INTO t1(x) SELECT x+2048 FROM t1;
  INSERT INTO t1(x) SELECT x+4096 FROM t1;
  INSERT INTO t1(x) SELECT x+8192 FROM t1;
  INSERT INTO t1(x) SELECT x+16384 FROM t1;
  INSERT INTO t1(x) SELECT x+32768 FROM t1;
  INSERT INTO t1(x) SELECT x+65536 FROM t1;
  UPDATE t1 SET y=x/2 WHERE x>1;
  COMMIT;
  CREATE VIRTUAL TABLE cx 
   USING transitive_closure(tablename=t1, idcolumn=x, parentcolumn=y);
} {}

# The entire table
do_execsql_test 1.1 {
do_timed_execsql_test 1.1 {
  SELECT count(*), depth FROM cx WHERE root=1 GROUP BY depth ORDER BY 1;
} {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}
do_timed_execsql_test 1.1-cte {
  WITH RECURSIVE
    below(id,depth) AS (
      VALUES(1,0)
       UNION ALL
      SELECT t1.x, below.depth+1
        FROM t1 JOIN below on t1.y=below.id

    )
  SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1;
} {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}

# descendents of 32768
do_execsql_test 1.2 {
do_timed_execsql_test 1.2 {
  SELECT * FROM cx WHERE root=32768 ORDER BY id;
} {32768 0 65536 1 65537 1 131072 2}
do_timed_execsql_test 1.2-cte {
  WITH RECURSIVE
    below(id,depth) AS (
      VALUES(32768,0)
       UNION ALL
      SELECT t1.x, below.depth+1
        FROM t1 JOIN below on t1.y=below.id
       WHERE below.depth<2

    )
  SELECT id, depth FROM below ORDER BY id;
} {32768 0 65536 1 65537 1 131072 2}

# descendents of 16384
do_execsql_test 1.3 {
do_timed_execsql_test 1.3 {
  SELECT * FROM cx WHERE root=16384 AND depth<=2 ORDER BY id;
} {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}
do_timed_execsql_test 1.3-cte {
  WITH RECURSIVE
    below(id,depth) AS (
      VALUES(16384,0)
       UNION ALL
      SELECT t1.x, below.depth+1
        FROM t1 JOIN below on t1.y=below.id
       WHERE below.depth<2
    )
  SELECT id, depth FROM below ORDER BY id;
} {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}

# children of 16384
do_execsql_test 1.4 {
  SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
   WHERE root=16384
     AND depth=1
   ORDER BY id;
} {32768 1 {} t1 x y 32769 1 {} t1 x y}

# great-grandparent of 16384
do_execsql_test 1.5 {
do_timed_execsql_test 1.5 {
  SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
   WHERE root=16384
     AND depth=3
     AND idcolumn='Y'
     AND parentcolumn='X';
} {2048 3 {} t1 Y X}
do_timed_execsql_test 1.5-cte {
  WITH RECURSIVE
    above(id,depth) AS (
      VALUES(16384,0)
      UNION ALL
      SELECT t1.y, above.depth+1
        FROM t1 JOIN above ON t1.x=above.id
       WHERE above.depth<3

    )
  SELECT id FROM above WHERE depth=3;
} {2048}

# depth<5
do_execsql_test 1.6 {
do_timed_execsql_test 1.6 {
  SELECT count(*), depth FROM cx WHERE root=1 AND depth<5
   GROUP BY depth ORDER BY 1;
} {1 0 2 1 4 2 8 3 16 4}
do_timed_execsql_test 1.6-cte {
  WITH RECURSIVE
    below(id,depth) AS (
      VALUES(1,0)
      UNION ALL
      SELECT t1.x, below.depth+1
        FROM t1 JOIN below ON t1.y=below.id
       WHERE below.depth<4
    )
  SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1;
} {1 0 2 1 4 2 8 3 16 4}

# depth<=5
do_execsql_test 1.7 {
  SELECT count(*), depth FROM cx WHERE root=1 AND depth<=5
   GROUP BY depth ORDER BY 1;
} {1 0 2 1 4 2 8 3 16 4 32 5}

# depth==5
do_execsql_test 1.8 {
  SELECT count(*), depth FROM cx WHERE root=1 AND depth=5
   GROUP BY depth ORDER BY 1;
} {32 5}

# depth BETWEEN 3 AND 5
do_execsql_test 1.9 {
  SELECT count(*), depth FROM cx WHERE root=1 AND depth BETWEEN 3 AND 5
   GROUP BY depth ORDER BY 1;
} {8 3 16 4 32 5}

# depth==5 with min() and max()
do_execsql_test 1.10 {
do_timed_execsql_test 1.10 {
  SELECT count(*), min(id), max(id) FROM cx WHERE root=1 AND depth=5;
} {32 32 63}
do_timed_execsql_test 1.10-cte {
  WITH RECURSIVE
    below(id,depth) AS (
      VALUES(1,0)
      UNION ALL
      SELECT t1.x, below.depth+1
        FROM t1 JOIN below ON t1.y=below.id
       WHERE below.depth<5
    )
  SELECT count(*), min(id), max(id) FROM below WHERE depth=5;
} {32 32 63}

# Create a much smaller table t2 with only 32 elements 
db eval {
  CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
  INSERT INTO t2 SELECT x, y FROM t1 WHERE x<32;
  CREATE INDEX t2y ON t2(y);
  CREATE VIRTUAL TABLE c2 
Changes to test/corruptH.test.
62
63
64
65
66
67
68

69
70
71
72
73
74
75
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76







+








#-------------------------------------------------------------------------
reset_db

# Initialize the database.
#
do_execsql_test 2.1 {
  PRAGMA auto_vacuum=0;
  PRAGMA page_size=1024;

  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  INSERT INTO t1 VALUES(1, 'one');
  INSERT INTO t1 VALUES(2, 'two');

  CREATE TABLE t3(x);
91
92
93
94
95
96
97






















98
99

100
101
102
103
104
105
106
107

108

109
110

111
112
113
114
115
116
117
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

136
137
138
139
140
141
142
143







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+


+








+

+

-
+







  hexio_write test.db [expr {($fl-1) * 1024 + 4}] 00000001 
  hexio_write test.db [expr {($fl-1) * 1024 + 8}] [format %.8X $r(t1)]
  hexio_write test.db 36 00000002

  sqlite3 db test.db
} {}


# The corruption migration caused by the test case below does not 
# cause corruption to be detected in mmap mode.
#
# The trick here is that the root page of the tree scanned by the outer 
# query is also currently on the free-list. So while the first seek on
# the table (for a==1) works, by the time the second is attempted The 
# "INSERT INTO t2..." statements have recycled the root page of t1 and
# used it as an index leaf. Normally, BtreeMovetoUnpacked() detects
# that the PgHdr object associated with said root page does not match
# the cursor (as it is now marked with PgHdr.intKey==0) and returns
# SQLITE_CORRUPT. 
#
# However, in mmap mode, the outer query and the inner queries use 
# different PgHdr objects (same data, but different PgHdr container 
# objects). And so the corruption is not detected. Instead, the second
# seek fails to find anything and only a single row is returned.
#
set res23 {1 {database disk image is malformed}}
if {[permutation]=="mmap"} {
  set res23 {0 one}
}
do_test 2.3 {
  list [catch {
  set res [list]
  db eval { SELECT * FROM t1 WHERE a IN (1, 2) } {
    db eval { 
      INSERT INTO t2 SELECT randomblob(100) FROM t2;
      INSERT INTO t2 SELECT randomblob(100) FROM t2;
      INSERT INTO t2 SELECT randomblob(100) FROM t2;
      INSERT INTO t2 SELECT randomblob(100) FROM t2;
      INSERT INTO t2 SELECT randomblob(100) FROM t2;
    }
    lappend res $b
  }
  set res
  } msg] $msg
} {1 {database disk image is malformed}}
} $res23

#-------------------------------------------------------------------------
reset_db

# Initialize the database.
#
do_execsql_test 3.1 {
Changes to test/e_select.test.
329
330
331
332
333
334
335
336
337
338



339
340
341
342
343
344
345
329
330
331
332
333
334
335



336
337
338
339
340
341
342
343
344
345







-
-
-
+
+
+







  2 "SELECT 'abc' WHERE NULL" {}
  3 "SELECT NULL"             {{}}
  4 "SELECT count(*)"         {1}
  5 "SELECT count(*) WHERE 0" {0}
  6 "SELECT count(*) WHERE 1" {1}
}

# EVIDENCE-OF: R-48114-33255 If there is only a single table in the
# join-source following the FROM clause, then the input data used by the
# SELECT statement is the contents of the named table.
# EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery
# in the FROM clause, then the input data used by the SELECT statement
# is the contents of the named table.
#
#   The results of the SELECT queries suggest that they are operating on the
#   contents of the table 'xx'.
#
do_execsql_test e_select-1.2.0 {
  CREATE TABLE xx(x, y);
  INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
353
354
355
356
357
358
359
360
361
362
363




364
365
366
367
368
369
370
353
354
355
356
357
358
359




360
361
362
363
364
365
366
367
368
369
370







-
-
-
-
+
+
+
+







     -17.89           'linguistically'                
  }

  2  "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}
  3  "SELECT sum(x), sum(y) FROM xx"               {-17.89 -16.87}
}

# EVIDENCE-OF: R-23593-12456 If there is more than one table specified
# as part of the join-source following the FROM keyword, then the
# contents of each named table are joined into a single dataset for the
# simple SELECT statement to operate on.
# EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery
# in FROM clause then the contents of all tables and/or subqueries are
# joined into a single dataset for the simple SELECT statement to
# operate on.
#
#   There are more detailed tests for subsequent requirements that add 
#   more detail to this idea. We just add a single test that shows that
#   data is coming from each of the three tables following the FROM clause
#   here to show that the statement, vague as it is, is not incorrect.
#
do_select_tests e_select-1.3 {
379
380
381
382
383
384
385
386
387
388
389




390
391
392
393
394
395
396
379
380
381
382
383
384
385




386
387
388
389
390
391
392
393
394
395
396







-
-
-
-
+
+
+
+







}

#
# The following block of tests - e_select-1.4.* - test that the description
# of cartesian joins in the SELECT documentation is consistent with SQLite.
# In doing so, we test the following three requirements as a side-effect:
#
# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
# then the result of the join is simply the cartesian product of the
# left and right-hand datasets.
# EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
# "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
# clause, then the result of the join is simply the cartesian product of
# the left and right-hand datasets.
#
#    The tests are built on this assertion. Really, they test that the output
#    of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
#    of calculating the cartesian product of the left and right-hand datasets. 
#
# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
# JOIN", "JOIN" and "," join operators.
509
510
511
512
513
514
515
516
517
518
519
520




521
522
523
524
525
526
527
509
510
511
512
513
514
515





516
517
518
519
520
521
522
523
524
525
526







-
-
-
-
-
+
+
+
+







do_select_tests e_select-1.4.5 [list                                   \
    1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
    2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
    3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
    4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
]


# EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
# the ON expression is evaluated for each row of the cartesian product
# as a boolean expression. All rows for which the expression evaluates
# to false are excluded from the dataset.
# EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON
# expression is evaluated for each row of the cartesian product as a
# boolean expression. Only rows for which the expression evaluates to
# true are included from the dataset.
#
foreach {tn select res} [list                                              \
    1 { SELECT * FROM t1 %JOIN% t2 ON (1) }       $t1_cross_t2             \
    2 { SELECT * FROM t1 %JOIN% t2 ON (0) }       [list]                   \
    3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) }    [list]                   \
    4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') }   [list]                   \
    5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') }   $t1_cross_t2             \
536
537
538
539
540
541
542
543
544
545



546
547
548
549
550
551
552
553
554
555

556
557
558


559
560
561
562
563
564
565
566
567
568
569
570


571
572
573
574
575
576
577
535
536
537
538
539
540
541



542
543
544
545
546
547
548
549
550
551
552
553

554
555


556
557
558
559
560
561
562
563
564
565
566
567


568
569
570
571
572
573
574
575
576







-
-
-
+
+
+









-
+

-
-
+
+










-
-
+
+







   11 { SELECT t1.b, t2.b 
        FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \
      {two I two II two III three I three II three III}                    \
] {
  do_join_test e_select-1.3.$tn $select $res
}

# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
# part of the join-constraint, then each of the column names specified
# must exist in the datasets to both the left and right of the join-op.
# EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the
# column names specified must exist in the datasets to both the left and
# right of the join-operator.
#
do_select_tests e_select-1.4 -error {
  cannot join using column %s - column not present in both tables
} {
  1 { SELECT * FROM t1, t3 USING (b) }   "b"
  2 { SELECT * FROM t3, t1 USING (c) }   "c"
  3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"
} 

# EVIDENCE-OF: R-55987-04584 For each pair of namesake columns, the
# EVIDENCE-OF: R-22776-52830 For each pair of named columns, the
# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
# product as a boolean expression. All rows for which one or more of the
# expressions evaluates to false are excluded from the result set.
# product as a boolean expression. Only rows for which all such
# expressions evaluates to true are included from the result set.
#
do_select_tests e_select-1.5 {
  1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
  2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}
} 

# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
# USING clause, the normal rules for handling affinities, collation
# sequences and NULL values in comparisons apply.
#
# EVIDENCE-OF: R-35466-18578 The column from the dataset on the
# left-hand side of the join operator is considered to be on the
# EVIDENCE-OF: R-38422-04402 The column from the dataset on the
# left-hand side of the join-operator is considered to be on the
# left-hand side of the comparison operator (=) for the purposes of
# collation sequence and affinity precedence.
#
do_execsql_test e_select-1.6.0 {
  CREATE TABLE t5(a COLLATE nocase, b COLLATE binary);
  INSERT INTO t5 VALUES('AA', 'cc');
  INSERT INTO t5 VALUES('BB', 'dd');
618
619
620
621
622
623
624
625

626
627
628


629
630
631
632
633
634
635
617
618
619
620
621
622
623

624



625
626
627
628
629
630
631
632
633







-
+
-
-
-
+
+







     {aa cc cc bb DD dd}
  4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x
       %JOIN% t5 ON (x.a=t5.a) } 
     {aa cc AA cc bb DD BB dd}
} {
  do_join_test e_select-1.7.$tn $select $res
}

# EVIDENCE-OF: R-42531-52874 If the join-operator is a "LEFT JOIN" or
# EVIDENCE-OF: R-41434-12448 If the join-op is a "LEFT JOIN" or "LEFT
# OUTER JOIN", then after the ON or USING filtering clauses have been
# applied, an extra row is added to the output for each row in the
# "LEFT OUTER JOIN", then after the ON or USING filtering clauses have
# been applied, an extra row is added to the output for each row in the
# original left-hand input dataset that corresponds to no rows at all in
# the composite dataset (if any).
#
do_execsql_test e_select-1.8.0 {
  CREATE TABLE t7(a, b, c);
  CREATE TABLE t8(a, d, e);

656
657
658
659
660
661
662
663
664


665
666
667
668
669
670
671
654
655
656
657
658
659
660


661
662
663
664
665
666
667
668
669







-
-
+
+







  1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}
  1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 
     {x ex 24 x abc 24 y why 25 {} {} {}}
  2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
  2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}
}

# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
# the join-ops, then an implicit USING clause is added to the
# EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the
# join-operator then an implicit USING clause is added to the
# join-constraints. The implicit USING clause contains each of the
# column names that appear in both the left and right-hand input
# datasets.
#
do_select_tests e_select-1-10 {
  1a "SELECT * FROM t7 JOIN t8 USING (a)"        {x ex 24 abc 24}
  1b "SELECT * FROM t7 NATURAL JOIN t8"          {x ex 24 abc 24}
Changes to test/e_select2.test.
340
341
342
343
344
345
346
347
348
349
350




351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374




375
376
377
378
379
380
381
340
341
342
343
344
345
346




347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370




371
372
373
374
375
376
377
378
379
380
381







-
-
-
-
+
+
+
+




















-
-
-
-
+
+
+
+







} {

  catchsql { DROP INDEX i1 }
  catchsql { DROP INDEX i2 }
  catchsql { DROP INDEX i3 }
  execsql $indexes

  # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
  # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
  # then the result of the join is simply the cartesian product of the
  # left and right-hand datasets.
  # EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
  # "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
  # clause, then the result of the join is simply the cartesian product of
  # the left and right-hand datasets.
  #
  # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
  # JOIN", "JOIN" and "," join operators.
  #
  # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
  # same result as the "INNER JOIN", "JOIN" and "," operators
  #
  test_join $tn.1.1  "t1, t2"                {t1 t2}
  test_join $tn.1.2  "t1 INNER JOIN t2"      {t1 t2}
  test_join $tn.1.3  "t1 CROSS JOIN t2"      {t1 t2}
  test_join $tn.1.4  "t1 JOIN t2"            {t1 t2}
  test_join $tn.1.5  "t2, t3"                {t2 t3}
  test_join $tn.1.6  "t2 INNER JOIN t3"      {t2 t3}
  test_join $tn.1.7  "t2 CROSS JOIN t3"      {t2 t3}
  test_join $tn.1.8  "t2 JOIN t3"            {t2 t3}
  test_join $tn.1.9  "t2, t2 AS x"           {t2 t2}
  test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
  test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
  test_join $tn.1.12 "t2 JOIN t2 AS x"       {t2 t2}

  # EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
  # the ON expression is evaluated for each row of the cartesian product
  # as a boolean expression. All rows for which the expression evaluates
  # to false are excluded from the dataset.
  # EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON
  # expression is evaluated for each row of the cartesian product as a
  # boolean expression. Only rows for which the expression evaluates to
  # true are included from the dataset.
  #
  test_join $tn.2.1  "t1, t2 ON (t1.a=t2.a)"  {t1 t2 -on {te_equals a a}}
  test_join $tn.2.2  "t2, t1 ON (t1.a=t2.a)"  {t2 t1 -on {te_equals a a}}
  test_join $tn.2.3  "t2, t1 ON (1)"          {t2 t1 -on te_true}
  test_join $tn.2.4  "t2, t1 ON (NULL)"       {t2 t1 -on te_false}
  test_join $tn.2.5  "t2, t1 ON (1.1-1.1)"    {t2 t1 -on te_false}
  test_join $tn.2.6  "t1, t2 ON (1.1-1.0)"    {t1 t2 -on te_true}
500
501
502
503
504
505
506
507
508
509
510




511
512
513
514



515
516
517
518
519
520
521
500
501
502
503
504
505
506




507
508
509
510
511



512
513
514
515
516
517
518
519
520
521







-
-
-
-
+
+
+
+

-
-
-
+
+
+







  CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary);

  INSERT INTO t4 VALUES('2.0');
  INSERT INTO t4 VALUES('TWO');
  INSERT INTO t5 VALUES(2, 'two');
} {}

# EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source
# following the FROM clause in a simple SELECT statement is handled as
# if it was a table containing the data returned by executing the
# sub-select statement.
# EVIDENCE-OF: R-59237-46742 A subquery specified in the
# table-or-subquery following the FROM clause in a simple SELECT
# statement is handled as if it was a table containing the data returned
# by executing the subquery statement.
#
# EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset
# inherits the collation sequence and affinity of the corresponding
# expression in the sub-select statement.
# EVIDENCE-OF: R-27438-53558 Each column of the subquery has the
# collation sequence and affinity of the corresponding expression in the
# subquery statement.
#
foreach {tn subselect select spec} {
  1   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss%" 
      {t1 %ss%}

  2   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" 
      {t1 %ss% -on {te_equals 0 0}}
Changes to test/pager4.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
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











-
+







+
+
+
+
+







# 2013-12-06
#
# 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.
#
#***********************************************************************
#
# Tests for the SQLITE_IOERR_NODB error condition: the database file file
# Tests for the SQLITE_READONLY_DBMOVED error condition: the database file
# is unlinked or renamed out from under SQLite.
#

if {$tcl_platform(platform)!="unix"} return

set testdir [file dirname $argv0]
source $testdir/tester.tcl

if {[permutation]=="inmemory_journal"} {
  finish_test
  return
}

# Create a database file for testing
#
do_execsql_test pager4-1.1 {
  CREATE TABLE t1(a,b,c);
  INSERT INTO t1 VALUES(673,'stone','philips');
  SELECT * FROM t1;
Changes to test/pagerfault.test.
1540
1541
1542
1543
1544
1545
1546

1547
1548
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549







+


  faultsim_test_result {0 {}}
  catch { db close }
  catch { db2 close }
}

sqlite3_shutdown
sqlite3_config_uri 0
sqlite3_initialize

finish_test
Changes to test/printf2.test.
55
56
57
58
59
60
61
62

63
64
65
66
67
68
69
55
56
57
58
59
60
61

62
63
64
65
66
67
68
69







-
+







do_execsql_test printf2-1.10 {
  SELECT printf('%lld',314159.2653);
} {314159}
do_execsql_test printf2-1.11 {
  SELECT printf('%lld%n',314159.2653,'hi');
} {314159}

# EVIDENCE-OF: R-20555-31089 The %z format is interchangable with %s.
# EVIDENCE-OF: R-17002-27534 The %z format is interchangeable with %s.
#
do_execsql_test printf2-1.12 {
  SELECT printf('%.*z',5,'abcdefghijklmnop');
} {abcde}
do_execsql_test printf2-1.13 {
  SELECT printf('%c','abcdefghijklmnop');
} {a}
Changes to test/spellfix.test.
230
231
232
233
234
235
236

237
238
239
240
241
242
243
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244







+







do_execsql_test 6.1.2 {
  SELECT word, distance FROM t3 WHERE rowid = 10;
} {keener {}}
do_execsql_test 6.1.3 {
  SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
} {keener 300}

ifcapable trace {
proc trace_callback {sql} {
  if {[string range $sql 0 2] == "-- "} {
    lappend ::trace [string range $sql 3 end]
  }
}

proc do_tracesql_test {tn sql {res {}}} {
260
261
262
263
264
265
266

267
268
269
270
271
261
262
263
264
265
266
267
268
269
270
271
272
273







+





  {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?}
}
do_tracesql_test 6.2.3 {
  SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
} {keener 300
  {SELECT id, word, rank, k1  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
}
}




finish_test
Changes to test/tester.tcl.
55
56
57
58
59
60
61

62
63
64
65
66
67
68
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69







+







#      do_ioerr_test          TESTNAME ARGS...
#      crashsql               ARGS...
#      integrity_check        TESTNAME ?DB?
#      verify_ex_errcode      TESTNAME EXPECTED ?DB?
#      do_test                TESTNAME SCRIPT EXPECTED
#      do_execsql_test        TESTNAME SQL EXPECTED
#      do_catchsql_test       TESTNAME SQL EXPECTED
#      do_timed_execsql_test  TESTNAME SQL EXPECTED
#
# Commands providing a lower level interface to the global test counters:
#
#      set_test_counter       COUNTER ?VALUE?
#      omit_test              TESTNAME REASON ?APPEND?
#      fail_test              TESTNAME
#      incr_ntest
719
720
721
722
723
724
725





726
727
728
729
730
731
732
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738







+
+
+
+
+







  fix_testname testname
  uplevel do_test [list $testname] [list "execsql {$sql}"] [list [list {*}$result]]
}
proc do_catchsql_test {testname sql result} {
  fix_testname testname
  uplevel do_test [list $testname] [list "catchsql {$sql}"] [list $result]
}
proc do_timed_execsql_test {testname sql {result {}}} {
  fix_testname testname
  uplevel do_test [list $testname] [list "execsql_timed {$sql}"]\
                                   [list [list {*}$result]]
}
proc do_eqp_test {name sql res} {
  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
}

#-------------------------------------------------------------------------
#   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
#
1014
1015
1016
1017
1018
1019
1020








1021
1022
1023
1024
1025
1026
1027
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041







+
+
+
+
+
+
+
+







}

# A procedure to execute SQL
#
proc execsql {sql {db db}} {
  # puts "SQL = $sql"
  uplevel [list $db eval $sql]
}
proc execsql_timed {sql {db db}} {
  set tm [time {
    set x [uplevel [list $db eval $sql]]
  } 1]
  set tm [lindex $tm 0]
  puts -nonewline " ([expr {$tm*0.001}]ms) "
  set x
}

# Execute SQL and catch exceptions.
#
proc catchsql {sql {db db}} {
  # puts "SQL = $sql"
  set r [catch [list uplevel [list $db eval $sql]] msg]
Changes to test/with1.test.
448
449
450
451
452
453
454





















































































































































































































































455
456
457
458
459

460
461
462
463
464
465
466
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703

704
705
706
707
708
709
710
711







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+




-
+







                          + ((ind-1)/27) * 27 + lp
                          + ((lp-1) / 3) * 6, 1)
           )
    )
  SELECT s FROM x WHERE ind=0;
} {534678912672195348198342567859761423426853791713924856961537284287419635345286179}

#--------------------------------------------------------------------------
# Some tests that use LIMIT and OFFSET in the definition of recursive CTEs.
# 
set I [list 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20]
proc limit_test {tn iLimit iOffset} {
  if {$iOffset < 0} { set iOffset 0 }
  if {$iLimit < 0 } {
    set result [lrange $::I $iOffset end]
  } else {
    set result [lrange $::I $iOffset [expr $iLimit+$iOffset-1]]
  }
  uplevel [list do_execsql_test $tn [subst -nocommands {
    WITH ii(a) AS (
      VALUES(1)
      UNION ALL 
      SELECT a+1 FROM ii WHERE a<20 
      LIMIT $iLimit OFFSET $iOffset
    )
    SELECT * FROM ii
  }] $result]
}

limit_test 9.1    20  0
limit_test 9.2     0  0
limit_test 9.3    19  1
limit_test 9.4    20 -1
limit_test 9.5     5  5
limit_test 9.6     0 -1
limit_test 9.7    40 -1
limit_test 9.8    -1 -1
limit_test 9.9    -1 -1

#--------------------------------------------------------------------------
# Test the ORDER BY clause on recursive tables.
#

do_execsql_test 10.1 {
  DROP TABLE IF EXISTS tree;
  CREATE TABLE tree(id INTEGER PRIMARY KEY, parentid, payload);
}

proc insert_into_tree {L} {
  db eval { DELETE FROM tree }
  foreach key $L {
    unset -nocomplain parentid
    foreach seg [split $key /] {
      if {$seg==""} continue
      set id [db one {
        SELECT id FROM tree WHERE parentid IS $parentid AND payload=$seg
      }]
      if {$id==""} {
        db eval { INSERT INTO tree VALUES(NULL, $parentid, $seg) }
        set parentid [db last_insert_rowid]
      } else {
        set parentid $id
      }
    }
  }
}

insert_into_tree {
  /a/a/a
  /a/b/c
  /a/b/c/d
  /a/b/d
}
do_execsql_test 10.2 {
  WITH flat(fid, p) AS (
    SELECT id, '/' || payload FROM tree WHERE parentid IS NULL
    UNION ALL
    SELECT id, p || '/' || payload FROM flat, tree WHERE parentid=fid
  )
  SELECT p FROM flat ORDER BY p;
} {
  /a /a/a /a/a/a 
     /a/b /a/b/c /a/b/c/d
          /a/b/d
}

# Scan the tree-structure currently stored in table tree. Return a list
# of nodes visited.
#
proc scan_tree {bDepthFirst bReverse} {

  set order "ORDER BY "
  if {$bDepthFirst==0} { append order "2 ASC," }
  if {$bReverse==0} { 
    append order " 3 ASC" 
  } else {
    append order " 3 DESC" 
  }

  db eval "
    WITH flat(fid, depth, p) AS (
        SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
        UNION ALL
        SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
        $order
    )
    SELECT p FROM flat;
  "
}

insert_into_tree {
  /a/b
  /a/b/c
  /a/d
  /a/d/e
  /a/d/f
  /g/h
}

# Breadth first, siblings in ascending order.
#
do_test 10.3 {
  scan_tree 0 0
} [list {*}{
  /a /g
  /a/b /a/d /g/h
  /a/b/c /a/d/e /a/d/f
}]

# Depth first, siblings in ascending order.
#
do_test 10.4 {
  scan_tree 1 0
} [list {*}{
  /a /a/b /a/b/c
     /a/d /a/d/e 
          /a/d/f
  /g /g/h
}]

# Breadth first, siblings in descending order.
#
do_test 10.5 {
  scan_tree 0 1
} [list {*}{
  /g /a 
  /g/h /a/d /a/b 
  /a/d/f /a/d/e /a/b/c 
}]

# Depth first, siblings in ascending order.
#
do_test 10.6 {
  scan_tree 1 1
} [list {*}{
  /g /g/h
  /a /a/d /a/d/f 
          /a/d/e 
     /a/b /a/b/c
}]


# Test name resolution in ORDER BY clauses.
#
do_catchsql_test 10.7.1 {
  WITH t(a) AS (
    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY a
  ) 
  SELECT * FROM t
} {1 {1st ORDER BY term does not match any column in the result set}}
do_execsql_test 10.7.2 {
  WITH t(a) AS (
    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY b
  ) 
  SELECT * FROM t
} {1 2 3 4 5}
do_execsql_test 10.7.3 {
  WITH t(a) AS (
    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY c
  ) 
  SELECT * FROM t
} {1 2 3 4 5}

# Test COLLATE clauses attached to ORDER BY.
#
insert_into_tree {
  /a/b
  /a/C
  /a/d
  /B/e
  /B/F
  /B/g
  /c/h
  /c/I
  /c/j
}

do_execsql_test 10.8.1 {
  WITH flat(fid, depth, p) AS (
    SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
    UNION ALL
    SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
    ORDER BY 2, 3 COLLATE nocase
  )
  SELECT p FROM flat;
} {
  /a /B /c
  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
}
do_execsql_test 10.8.2 {
  WITH flat(fid, depth, p) AS (
      SELECT id, 1, ('/' || payload) COLLATE nocase 
      FROM tree WHERE parentid IS NULL
    UNION ALL
      SELECT id, depth+1, (p||'/'||payload)
      FROM flat, tree WHERE parentid=fid
    ORDER BY 2, 3
  )
  SELECT p FROM flat;
} {
  /a /B /c
  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
}

do_execsql_test 10.8.3 {
  WITH flat(fid, depth, p) AS (
      SELECT id, 1, ('/' || payload)
      FROM tree WHERE parentid IS NULL
    UNION ALL
      SELECT id, depth+1, (p||'/'||payload) COLLATE nocase 
      FROM flat, tree WHERE parentid=fid
    ORDER BY 2, 3
  )
  SELECT p FROM flat;
} {
  /a /B /c
  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
}

do_execsql_test 10.8.4.1 {
  CREATE TABLE tst(a,b);
  INSERT INTO tst VALUES('a', 'A');
  INSERT INTO tst VALUES('b', 'B');
  INSERT INTO tst VALUES('c', 'C');
  SELECT a COLLATE nocase FROM tst UNION ALL SELECT b FROM tst ORDER BY 1;
} {a A b B c C}
do_execsql_test 10.8.4.2 {
  SELECT a FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
} {A B C a b c}
do_execsql_test 10.8.4.3 {
  SELECT a||'' FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
} {a A b B c C}

# Test cases to illustrate on the ORDER BY clause on a recursive query can be
# used to control depth-first versus breath-first search in a tree.
#
do_execsql_test 9.1 {
do_execsql_test 11.1 {
  CREATE TABLE org(
    name TEXT PRIMARY KEY,
    boss TEXT REFERENCES org
  ) WITHOUT ROWID;
  INSERT INTO org VALUES('Alice',NULL);
  INSERT INTO org VALUES('Bob','Alice');
  INSERT INTO org VALUES('Cindy','Alice');
509
510
511
512
513
514
515
516

517
518
519
520
521
522
523
754
755
756
757
758
759
760

761
762
763
764
765
766
767
768







-
+







.........Mary
.........Noland
.........Olivia}}

# The previous query used "ORDER BY level" to yield a breath-first search.
# Change that to "ORDER BY level DESC" for a depth-first search.
#
do_execsql_test 9.2 {
do_execsql_test 11.2 {
  WITH RECURSIVE
    under_alice(name,level) AS (
       VALUES('Alice','0')
       UNION ALL
       SELECT org.name, under_alice.level+1
         FROM org, under_alice
        WHERE org.boss=under_alice.name
540
541
542
543
544
545
546
547

548
549
550
551
552
553
554
785
786
787
788
789
790
791

792
793
794
795
796
797
798
799







-
+







......Gail
.........Noland
.........Olivia}}

# Without an ORDER BY clause, the recursive query should use a FIFO,
# resulting in a breath-first search.
#
do_execsql_test 9.3 {
do_execsql_test 11.3 {
  WITH RECURSIVE
    under_alice(name,level) AS (
       VALUES('Alice','0')
       UNION ALL
       SELECT org.name, under_alice.level+1
         FROM org, under_alice
        WHERE org.boss=under_alice.name
568
569
570
571
572
573
574

813
814
815
816
817
818
819
820







+
.........Kate
.........Lanny
.........Mary
.........Noland
.........Olivia}}

finish_test

Changes to tool/omittest.tcl.
186
187
188
189
190
191
192

193
194
195
196
197
198
199
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200







+







    SQLITE_OMIT_BTREECOUNT \
    SQLITE_OMIT_BUILTIN_TEST \
    SQLITE_OMIT_CAST \
    SQLITE_OMIT_CHECK \
    SQLITE_OMIT_COMPILEOPTION_DIAGS \
    SQLITE_OMIT_COMPLETE \
    SQLITE_OMIT_COMPOUND_SELECT \
    SQLITE_OMIT_CTE \
    SQLITE_OMIT_DATETIME_FUNCS \
    SQLITE_OMIT_DECLTYPE \
    SQLITE_OMIT_DEPRECATED \
    SQLITE_OMIT_EXPLAIN \
    SQLITE_OMIT_FLAG_PRAGMAS \
    SQLITE_OMIT_FLOATING_POINT \
    SQLITE_OMIT_FOREIGN_KEY \