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: |
7b5f3773867ed0e4ed17bd473ba972d5 |
| 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
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 \