Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Merge the latest enhancements from trunk. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | schemalint |
Files: | files | file ages | folders |
SHA3-256: |
a7dcf6a79f7e1c5884baee2909a4bf31 |
User & Date: | drh 2017-05-02 19:45:14.234 |
Context
2017-05-02
| ||
20:42 | In the sqlite3_expert command-line tool, allow two-dash options. Do not accept the database name if it begins with "-". (check-in: af7d159604 user: drh tags: schemalint) | |
19:45 | Merge the latest enhancements from trunk. (check-in: a7dcf6a79f user: drh tags: schemalint) | |
18:00 | Fix the fts3EvalAverageDocsize() routine so that it returns errors from sqlite3_reset() rather than always returning SQLITE_OK. (check-in: 430f539cbb user: drh tags: trunk) | |
2017-05-01
| ||
14:25 | Update this branch with latest trunk changes. (check-in: 11f4761c3a user: dan tags: schemalint) | |
Changes
Changes to ext/expert/test_expert.c.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2017 April 07 ** ** 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. ** ************************************************************************* */ | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2017 April 07 ** ** 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. ** ************************************************************************* */ #if defined(SQLITE_TEST) #include "sqlite3expert.h" #include <assert.h> #include <string.h> #if defined(INCLUDE_SQLITE_TCL_H) # include "sqlite_tcl.h" |
︙ | ︙ | |||
208 209 210 211 212 213 214 | struct Cmd *p = &aCmd[i]; Tcl_CreateObjCommand(interp, p->zCmd, p->xProc, 0, 0); } return TCL_OK; } | < < < < | 208 209 210 211 212 213 214 215 | struct Cmd *p = &aCmd[i]; Tcl_CreateObjCommand(interp, p->zCmd, p->xProc, 0, 0); } return TCL_OK; } #endif |
Changes to ext/fts3/fts3.c.
︙ | ︙ | |||
4783 4784 4785 4786 4787 4788 4789 | ** Entry 0 of the %_stat table is a blob containing (nCol+1) FTS3 ** varints, where nCol is the number of columns in the FTS3 table. ** The first varint is the number of documents currently stored in ** the table. The following nCol varints contain the total amount of ** data stored in all rows of each column of the table, from left ** to right. */ | < | 4783 4784 4785 4786 4787 4788 4789 4790 4791 4792 4793 4794 4795 4796 | ** Entry 0 of the %_stat table is a blob containing (nCol+1) FTS3 ** varints, where nCol is the number of columns in the FTS3 table. ** The first varint is the number of documents currently stored in ** the table. The following nCol varints contain the total amount of ** data stored in all rows of each column of the table, from left ** to right. */ Fts3Table *p = (Fts3Table*)pCsr->base.pVtab; sqlite3_stmt *pStmt; sqlite3_int64 nDoc = 0; sqlite3_int64 nByte = 0; const char *pEnd; const char *a; |
︙ | ︙ |
Changes to src/btree.c.
︙ | ︙ | |||
8186 8187 8188 8189 8190 8191 8192 8193 8194 8195 8196 8197 8198 8199 | return SQLITE_OK; } dropCell(pPage, idx, info.nSize, &rc); if( rc ) goto end_insert; }else if( loc<0 && pPage->nCell>0 ){ assert( pPage->leaf ); idx = ++pCur->ix; }else{ assert( pPage->leaf ); } insertCell(pPage, idx, newCell, szNew, 0, 0, &rc); assert( pPage->nOverflow==0 || rc==SQLITE_OK ); assert( rc!=SQLITE_OK || pPage->nCell>0 || pPage->nOverflow>0 ); | > | 8186 8187 8188 8189 8190 8191 8192 8193 8194 8195 8196 8197 8198 8199 8200 | return SQLITE_OK; } dropCell(pPage, idx, info.nSize, &rc); if( rc ) goto end_insert; }else if( loc<0 && pPage->nCell>0 ){ assert( pPage->leaf ); idx = ++pCur->ix; pCur->curFlags &= ~BTCF_ValidNKey; }else{ assert( pPage->leaf ); } insertCell(pPage, idx, newCell, szNew, 0, 0, &rc); assert( pPage->nOverflow==0 || rc==SQLITE_OK ); assert( rc!=SQLITE_OK || pPage->nCell>0 || pPage->nOverflow>0 ); |
︙ | ︙ | |||
9310 9311 9312 9313 9314 9315 9316 9317 9318 9319 9320 9321 9322 9323 | /* Check for integer primary key out of range */ if( pPage->intKey ){ if( keyCanBeEqual ? (info.nKey > maxKey) : (info.nKey >= maxKey) ){ checkAppendMsg(pCheck, "Rowid %lld out of order", info.nKey); } maxKey = info.nKey; } /* Check the content overflow list */ if( info.nPayload>info.nLocal ){ int nPage; /* Number of pages on the overflow chain */ Pgno pgnoOvfl; /* First page of the overflow chain */ assert( pc + info.nSize - 4 <= usableSize ); | > | 9311 9312 9313 9314 9315 9316 9317 9318 9319 9320 9321 9322 9323 9324 9325 | /* Check for integer primary key out of range */ if( pPage->intKey ){ if( keyCanBeEqual ? (info.nKey > maxKey) : (info.nKey >= maxKey) ){ checkAppendMsg(pCheck, "Rowid %lld out of order", info.nKey); } maxKey = info.nKey; keyCanBeEqual = 0; /* Only the first key on the page may ==maxKey */ } /* Check the content overflow list */ if( info.nPayload>info.nLocal ){ int nPage; /* Number of pages on the overflow chain */ Pgno pgnoOvfl; /* First page of the overflow chain */ assert( pc + info.nSize - 4 <= usableSize ); |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 | ** expression must not refer to any non-deterministic function nor any ** table other than iCur. */ int sqlite3ExprIsTableConstant(Expr *p, int iCur){ return exprIsConst(p, 3, iCur); } /* ** Walk an expression tree. Return non-zero if the expression is constant ** or a function call with constant arguments. Return and 0 if there ** are any variables. ** ** For the purposes of this function, a double-quoted string (ex: "abc") ** is considered a variable but a single-quoted string (ex: 'abc') is | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 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 | ** expression must not refer to any non-deterministic function nor any ** table other than iCur. */ int sqlite3ExprIsTableConstant(Expr *p, int iCur){ return exprIsConst(p, 3, iCur); } /* ** sqlite3WalkExpr() callback used by sqlite3ExprIsConstantOrGroupBy(). */ static int exprNodeIsConstantOrGroupBy(Walker *pWalker, Expr *pExpr){ ExprList *pGroupBy = pWalker->u.pGroupBy; int i; /* Check if pExpr is identical to any GROUP BY term. If so, consider ** it constant. */ for(i=0; i<pGroupBy->nExpr; i++){ Expr *p = pGroupBy->a[i].pExpr; if( sqlite3ExprCompare(pExpr, p, -1)<2 ){ CollSeq *pColl = sqlite3ExprCollSeq(pWalker->pParse, p); if( pColl==0 || sqlite3_stricmp("BINARY", pColl->zName)==0 ){ return WRC_Prune; } } } /* Check if pExpr is a sub-select. If so, consider it variable. */ if( ExprHasProperty(pExpr, EP_xIsSelect) ){ pWalker->eCode = 0; return WRC_Abort; } return exprNodeIsConstant(pWalker, pExpr); } /* ** Walk the expression tree passed as the first argument. Return non-zero ** if the expression consists entirely of constants or copies of terms ** in pGroupBy that sort with the BINARY collation sequence. ** ** This routine is used to determine if a term of the HAVING clause can ** be promoted into the WHERE clause. In order for such a promotion to work, ** the value of the HAVING clause term must be the same for all members of ** a "group". The requirement that the GROUP BY term must be BINARY ** assumes that no other collating sequence will have a finer-grained ** grouping than binary. In other words (A=B COLLATE binary) implies ** A=B in every other collating sequence. The requirement that the ** GROUP BY be BINARY is stricter than necessary. It would also work ** to promote HAVING clauses that use the same alternative collating ** sequence as the GROUP BY term, but that is much harder to check, ** alternative collating sequences are uncommon, and this is only an ** optimization, so we take the easy way out and simply require the ** GROUP BY to use the BINARY collating sequence. */ int sqlite3ExprIsConstantOrGroupBy(Parse *pParse, Expr *p, ExprList *pGroupBy){ Walker w; memset(&w, 0, sizeof(w)); w.eCode = 1; w.xExprCallback = exprNodeIsConstantOrGroupBy; w.u.pGroupBy = pGroupBy; w.pParse = pParse; sqlite3WalkExpr(&w, p); return w.eCode; } /* ** Walk an expression tree. Return non-zero if the expression is constant ** or a function call with constant arguments. Return and 0 if there ** are any variables. ** ** For the purposes of this function, a double-quoted string (ex: "abc") ** is considered a variable but a single-quoted string (ex: 'abc') is |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
4874 4875 4876 4877 4878 4879 4880 4881 4882 4883 4884 4885 4886 4887 | pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC ); } } #else # define explainSimpleCount(a,b,c) #endif /* ** Generate code for the SELECT statement given in the p argument. ** ** The results are returned according to the SelectDest structure. ** See comments in sqliteInt.h for further information. ** | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 4874 4875 4876 4877 4878 4879 4880 4881 4882 4883 4884 4885 4886 4887 4888 4889 4890 4891 4892 4893 4894 4895 4896 4897 4898 4899 4900 4901 4902 4903 4904 4905 4906 4907 4908 4909 4910 4911 4912 4913 4914 4915 4916 4917 4918 4919 4920 4921 4922 4923 4924 4925 4926 4927 4928 4929 4930 4931 4932 4933 4934 4935 4936 4937 4938 4939 4940 4941 4942 4943 4944 4945 4946 4947 4948 4949 4950 4951 4952 4953 4954 4955 4956 4957 4958 4959 4960 4961 4962 4963 4964 4965 4966 4967 4968 4969 4970 4971 4972 4973 4974 4975 4976 4977 4978 4979 4980 4981 4982 4983 4984 | pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC ); } } #else # define explainSimpleCount(a,b,c) #endif /* ** Context object for havingToWhereExprCb(). */ struct HavingToWhereCtx { Expr **ppWhere; ExprList *pGroupBy; }; /* ** sqlite3WalkExpr() callback used by havingToWhere(). ** ** If the node passed to the callback is a TK_AND node, return ** WRC_Continue to tell sqlite3WalkExpr() to iterate through child nodes. ** ** Otherwise, return WRC_Prune. In this case, also check if the ** sub-expression matches the criteria for being moved to the WHERE ** clause. If so, add it to the WHERE clause and replace the sub-expression ** within the HAVING expression with a constant "1". */ static int havingToWhereExprCb(Walker *pWalker, Expr *pExpr){ if( pExpr->op!=TK_AND ){ struct HavingToWhereCtx *p = pWalker->u.pHavingCtx; if( sqlite3ExprIsConstantOrGroupBy(pWalker->pParse, pExpr, p->pGroupBy) ){ sqlite3 *db = pWalker->pParse->db; Expr *pNew = sqlite3ExprAlloc(db, TK_INTEGER, &sqlite3IntTokens[1], 0); if( pNew ){ Expr *pWhere = *(p->ppWhere); SWAP(Expr, *pNew, *pExpr); pNew = sqlite3ExprAnd(db, pWhere, pNew); *(p->ppWhere) = pNew; } } return WRC_Prune; } return WRC_Continue; } /* ** Transfer eligible terms from the HAVING clause of a query, which is ** processed after grouping, to the WHERE clause, which is processed before ** grouping. For example, the query: ** ** SELECT * FROM <tables> WHERE a=? GROUP BY b HAVING b=? AND c=? ** ** can be rewritten as: ** ** SELECT * FROM <tables> WHERE a=? AND b=? GROUP BY b HAVING c=? ** ** A term of the HAVING expression is eligible for transfer if it consists ** entirely of constants and expressions that are also GROUP BY terms that ** use the "BINARY" collation sequence. */ static void havingToWhere( Parse *pParse, ExprList *pGroupBy, Expr *pHaving, Expr **ppWhere ){ struct HavingToWhereCtx sCtx; Walker sWalker; sCtx.ppWhere = ppWhere; sCtx.pGroupBy = pGroupBy; memset(&sWalker, 0, sizeof(sWalker)); sWalker.pParse = pParse; sWalker.xExprCallback = havingToWhereExprCb; sWalker.u.pHavingCtx = &sCtx; sqlite3WalkExpr(&sWalker, pHaving); } /* ** Check to see if the pThis entry of pTabList is a self-join of a prior view. ** If it is, then return the SrcList_item for the prior view. If it is not, ** then return 0. */ static struct SrcList_item *isSelfJoinView( SrcList *pTabList, /* Search for self-joins in this FROM clause */ struct SrcList_item *pThis /* Search for prior reference to this subquery */ ){ struct SrcList_item *pItem; for(pItem = pTabList->a; pItem<pThis; pItem++){ if( pItem->pSelect==0 ) continue; if( pItem->fg.viaCoroutine ) continue; if( pItem->zName==0 ) continue; if( sqlite3_stricmp(pItem->zDatabase, pThis->zDatabase)!=0 ) continue; if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue; if( sqlite3ExprCompare(pThis->pSelect->pWhere, pItem->pSelect->pWhere, -1) ){ /* The view was modified by some other optimization such as ** pushDownWhereTerms() */ continue; } return pItem; } return 0; } /* ** Generate code for the SELECT statement given in the p argument. ** ** The results are returned according to the SelectDest structure. ** See comments in sqliteInt.h for further information. ** |
︙ | ︙ | |||
5109 5110 5111 5112 5113 5114 5115 5116 5117 5118 5119 5120 5121 5122 5123 5124 5125 5126 5127 5128 | ** the content of this subquery. pItem->addrFillSub will point ** to the address of the generated subroutine. pItem->regReturn ** is a register allocated to hold the subroutine return address */ int topAddr; int onceAddr = 0; int retAddr; assert( pItem->addrFillSub==0 ); pItem->regReturn = ++pParse->nMem; topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn); pItem->addrFillSub = topAddr+1; if( pItem->fg.isCorrelated==0 ){ /* If the subquery is not correlated and if we are not inside of ** a trigger, then we only need to compute the value of the subquery ** once. */ onceAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName)); }else{ VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName)); } | > > > > > > | | | > | 5206 5207 5208 5209 5210 5211 5212 5213 5214 5215 5216 5217 5218 5219 5220 5221 5222 5223 5224 5225 5226 5227 5228 5229 5230 5231 5232 5233 5234 5235 5236 5237 5238 5239 5240 5241 5242 | ** the content of this subquery. pItem->addrFillSub will point ** to the address of the generated subroutine. pItem->regReturn ** is a register allocated to hold the subroutine return address */ int topAddr; int onceAddr = 0; int retAddr; struct SrcList_item *pPrior; assert( pItem->addrFillSub==0 ); pItem->regReturn = ++pParse->nMem; topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn); pItem->addrFillSub = topAddr+1; if( pItem->fg.isCorrelated==0 ){ /* If the subquery is not correlated and if we are not inside of ** a trigger, then we only need to compute the value of the subquery ** once. */ onceAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName)); }else{ VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName)); } pPrior = isSelfJoinView(pTabList, pItem); if( pPrior ){ sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor); }else{ sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); } pItem->pTab->nRowLogEst = pSub->nSelectRow; if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn); VdbeComment((v, "end %s", pItem->pTab->zName)); sqlite3VdbeChangeP1(v, topAddr, retAddr); sqlite3ClearTempRegCache(pParse); } |
︙ | ︙ | |||
5343 5344 5345 5346 5347 5348 5349 5350 5351 5352 5353 5354 5355 5356 | sNC.pAggInfo = &sAggInfo; sAggInfo.mnReg = pParse->nMem+1; sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr : 0; sAggInfo.pGroupBy = pGroupBy; sqlite3ExprAnalyzeAggList(&sNC, pEList); sqlite3ExprAnalyzeAggList(&sNC, sSort.pOrderBy); if( pHaving ){ sqlite3ExprAnalyzeAggregates(&sNC, pHaving); } sAggInfo.nAccumulator = sAggInfo.nColumn; for(i=0; i<sAggInfo.nFunc; i++){ assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) ); sNC.ncFlags |= NC_InAggFunc; sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->x.pList); | > > > > > | 5447 5448 5449 5450 5451 5452 5453 5454 5455 5456 5457 5458 5459 5460 5461 5462 5463 5464 5465 | sNC.pAggInfo = &sAggInfo; sAggInfo.mnReg = pParse->nMem+1; sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr : 0; sAggInfo.pGroupBy = pGroupBy; sqlite3ExprAnalyzeAggList(&sNC, pEList); sqlite3ExprAnalyzeAggList(&sNC, sSort.pOrderBy); if( pHaving ){ if( pGroupBy ){ assert( pWhere==p->pWhere ); havingToWhere(pParse, pGroupBy, pHaving, &p->pWhere); pWhere = p->pWhere; } sqlite3ExprAnalyzeAggregates(&sNC, pHaving); } sAggInfo.nAccumulator = sAggInfo.nColumn; for(i=0; i<sAggInfo.nFunc; i++){ assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) ); sNC.ncFlags |= NC_InAggFunc; sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->x.pList); |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
853 854 855 856 857 858 859 | ** anti-virus programs. By default, the windows VFS will retry file read, ** file write, and file delete operations up to 10 times, with a delay ** of 25 milliseconds before the first retry and with the delay increasing ** by an additional 25 milliseconds with each subsequent retry. This ** opcode allows these two values (10 retries and 25 milliseconds of delay) ** to be adjusted. The values are changed for all database connections ** within the same process. The argument is a pointer to an array of two | | | 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 | ** anti-virus programs. By default, the windows VFS will retry file read, ** file write, and file delete operations up to 10 times, with a delay ** of 25 milliseconds before the first retry and with the delay increasing ** by an additional 25 milliseconds with each subsequent retry. This ** opcode allows these two values (10 retries and 25 milliseconds of delay) ** to be adjusted. The values are changed for all database connections ** within the same process. The argument is a pointer to an array of two ** integers where the first integer is the new retry count and the second ** integer is the delay. If either integer is negative, then the setting ** is not changed but instead the prior value of that setting is written ** into the array entry, allowing the current retry settings to be ** interrogated. The zDbName parameter is ignored. ** ** <li>[[SQLITE_FCNTL_PERSIST_WAL]] ** ^The [SQLITE_FCNTL_PERSIST_WAL] opcode is used to set or query the |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
3314 3315 3316 3317 3318 3319 3320 | Parse *pParse; /* Parser context. */ int (*xExprCallback)(Walker*, Expr*); /* Callback for expressions */ int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */ int walkerDepth; /* Number of subqueries */ u8 eCode; /* A small processing code */ union { /* Extra data for callback */ | | | | | | | | | | > > | 3314 3315 3316 3317 3318 3319 3320 3321 3322 3323 3324 3325 3326 3327 3328 3329 3330 3331 3332 3333 3334 3335 3336 3337 3338 | Parse *pParse; /* Parser context. */ int (*xExprCallback)(Walker*, Expr*); /* Callback for expressions */ int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */ int walkerDepth; /* Number of subqueries */ u8 eCode; /* A small processing code */ union { /* Extra data for callback */ NameContext *pNC; /* Naming context */ int n; /* A counter */ int iCur; /* A cursor number */ SrcList *pSrcList; /* FROM clause */ struct SrcCount *pSrcCount; /* Counting column references */ struct CCurHint *pCCurHint; /* Used by codeCursorHint() */ int *aiCol; /* array of column indexes */ struct IdxCover *pIdxCover; /* Check for index coverage */ struct IdxExprTrans *pIdxTrans; /* Convert indexed expr to column */ ExprList *pGroupBy; /* GROUP BY clause */ struct HavingToWhereCtx *pHavingCtx; /* HAVING to WHERE clause ctx */ } u; }; /* Forward declarations */ int sqlite3WalkExpr(Walker*, Expr*); int sqlite3WalkExprList(Walker*, ExprList*); int sqlite3WalkSelect(Walker*, Select*); |
︙ | ︙ | |||
3792 3793 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 3805 | void sqlite3RollbackTransaction(Parse*); void sqlite3Savepoint(Parse*, int, Token*); void sqlite3CloseSavepoints(sqlite3 *); void sqlite3LeaveMutexAndCloseZombie(sqlite3*); int sqlite3ExprIsConstant(Expr*); int sqlite3ExprIsConstantNotJoin(Expr*); int sqlite3ExprIsConstantOrFunction(Expr*, u8); int sqlite3ExprIsTableConstant(Expr*,int); #ifdef SQLITE_ENABLE_CURSOR_HINTS int sqlite3ExprContainsSubquery(Expr*); #endif int sqlite3ExprIsInteger(Expr*, int*); int sqlite3ExprCanBeNull(const Expr*); int sqlite3ExprNeedsNoAffinityChange(const Expr*, char); | > | 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 | void sqlite3RollbackTransaction(Parse*); void sqlite3Savepoint(Parse*, int, Token*); void sqlite3CloseSavepoints(sqlite3 *); void sqlite3LeaveMutexAndCloseZombie(sqlite3*); int sqlite3ExprIsConstant(Expr*); int sqlite3ExprIsConstantNotJoin(Expr*); int sqlite3ExprIsConstantOrFunction(Expr*, u8); int sqlite3ExprIsConstantOrGroupBy(Parse*, Expr*, ExprList*); int sqlite3ExprIsTableConstant(Expr*,int); #ifdef SQLITE_ENABLE_CURSOR_HINTS int sqlite3ExprContainsSubquery(Expr*); #endif int sqlite3ExprIsInteger(Expr*, int*); int sqlite3ExprCanBeNull(const Expr*); int sqlite3ExprNeedsNoAffinityChange(const Expr*, char); |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
3535 3536 3537 3538 3539 3540 3541 3542 3543 3544 3545 3546 3547 3548 | testcase( pOp->p2 & OPFLAG_SEEKEQ ); #endif sqlite3BtreeCursorHintFlags(pCur->uc.pCursor, (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ))); if( rc ) goto abort_due_to_error; break; } /* Opcode: OpenEphemeral P1 P2 * P4 P5 ** Synopsis: nColumn=P2 ** ** Open a new cursor P1 to a transient table. ** The cursor is always opened read/write even if ** the main database is read-only. The ephemeral | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3535 3536 3537 3538 3539 3540 3541 3542 3543 3544 3545 3546 3547 3548 3549 3550 3551 3552 3553 3554 3555 3556 3557 3558 3559 3560 3561 3562 3563 3564 3565 3566 3567 3568 3569 3570 3571 3572 3573 3574 3575 3576 3577 3578 3579 | testcase( pOp->p2 & OPFLAG_SEEKEQ ); #endif sqlite3BtreeCursorHintFlags(pCur->uc.pCursor, (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ))); if( rc ) goto abort_due_to_error; break; } /* Opcode: OpenDup P1 P2 * * * ** ** Open a new cursor P1 that points to the same ephemeral table as ** cursor P2. The P2 cursor must have been opened by a prior OP_OpenEphemeral ** opcode. Only ephemeral cursors may be duplicated. ** ** Duplicate ephemeral cursors are used for self-joins of materialized views. */ case OP_OpenDup: { VdbeCursor *pOrig; /* The original cursor to be duplicated */ VdbeCursor *pCx; /* The new cursor */ pOrig = p->apCsr[pOp->p2]; assert( pOrig->pBtx!=0 ); /* Only ephemeral cursors can be duplicated */ pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE); if( pCx==0 ) goto no_mem; pCx->nullRow = 1; pCx->isEphemeral = 1; pCx->pKeyInfo = pOrig->pKeyInfo; pCx->isTable = pOrig->isTable; rc = sqlite3BtreeCursor(pOrig->pBtx, MASTER_ROOT, BTREE_WRCSR, pCx->pKeyInfo, pCx->uc.pCursor); /* The sqlite3BtreeCursor() routine can only fail for the first cursor ** opened for a database. Since there is already an open cursor when this ** opcode is run, the sqlite3BtreeCursor() cannot fail */ assert( rc==SQLITE_OK ); break; } /* Opcode: OpenEphemeral P1 P2 * P4 P5 ** Synopsis: nColumn=P2 ** ** Open a new cursor P1 to a transient table. ** The cursor is always opened read/write even if ** the main database is read-only. The ephemeral |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
2040 2041 2042 2043 2044 2045 2046 | assert( pCx->pBtx==0 || pCx->eCurType==CURTYPE_BTREE ); switch( pCx->eCurType ){ case CURTYPE_SORTER: { sqlite3VdbeSorterClose(p->db, pCx); break; } case CURTYPE_BTREE: { | | | | 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 | assert( pCx->pBtx==0 || pCx->eCurType==CURTYPE_BTREE ); switch( pCx->eCurType ){ case CURTYPE_SORTER: { sqlite3VdbeSorterClose(p->db, pCx); break; } case CURTYPE_BTREE: { if( pCx->isEphemeral ){ if( pCx->pBtx ) sqlite3BtreeClose(pCx->pBtx); /* The pCx->pCursor will be close automatically, if it exists, by ** the call above. */ }else{ assert( pCx->uc.pCursor!=0 ); sqlite3BtreeCloseCursor(pCx->uc.pCursor); } break; |
︙ | ︙ |
Changes to test/conflict3.test.
︙ | ︙ | |||
15 16 17 18 19 20 21 22 23 24 25 26 27 | # # This file focuses on making sure that combinations of REPLACE, # IGNORE, and FAIL conflict resolution play well together. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !conflict { finish_test return } | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > > > > > > > > > > > > > | 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 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 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 | # # This file focuses on making sure that combinations of REPLACE, # IGNORE, and FAIL conflict resolution play well together. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix conflict3 ifcapable !conflict { finish_test return } do_execsql_test 1.1 { CREATE TABLE t1( a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 1.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 1.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 1.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Replete the tests above, but this time on a table non-INTEGER primary key. # do_execsql_test 2.1 { DROP TABLE t1; CREATE TABLE t1( a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 2.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 2.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 2.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Replete again on a WITHOUT ROWID table. # do_execsql_test 3.1 { DROP TABLE t1; CREATE TABLE t1( a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 3.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 3.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 3.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test 4.1 { DROP TABLE t1; CREATE TABLE t1( b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL, a INT PRIMARY KEY ON CONFLICT REPLACE ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 4.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 4.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 4.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test 5.1 { DROP TABLE t1; CREATE TABLE t1( b UNIQUE ON CONFLICT IGNORE, a INT PRIMARY KEY ON CONFLICT REPLACE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 5.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 5.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 5.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test 6.1 { DROP TABLE t1; CREATE TABLE t1( c UNIQUE ON CONFLICT FAIL, a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 6.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 6.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 6.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 7.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INTEGER PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 7.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 7.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 7.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 8.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INT PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 8.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 8.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 8.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 9.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INT PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 9.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 9.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 9.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 10.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c INTEGER PRIMARY KEY ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 10.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 10.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 10.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 11.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c PRIMARY KEY ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 11.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 11.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 11.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Check that ticket [f68dc596c4] has been fixed. # do_execsql_test 12.1 { CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); INSERT INTO t2 VALUES(111, '111'); } do_execsql_test 12.2 { REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B'); } do_execsql_test 12.3 { SELECT * FROM t2; } {111 111B 112 112} finish_test |
Added test/having.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 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 | # 2017 April 30 # # 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. # #*********************************************************************** # # Test the HAVING->WHERE optimization. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix having do_execsql_test 1.0 { CREATE TABLE t2(c, d); CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 1); INSERT INTO t1 VALUES(2, 2); INSERT INTO t1 VALUES(1, 3); INSERT INTO t1 VALUES(2, 4); INSERT INTO t1 VALUES(1, 5); INSERT INTO t1 VALUES(2, 6); } {} foreach {tn sql res} { 1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2" {2 12} 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2 AND sum(b)>10" {2 12} 3 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>12" {} } { do_execsql_test 1.$tn $sql $res } # Run an EXPLAIN command for both SQL statements. Return true if # the outputs are identical, or false otherwise. # proc compare_vdbe {sql1 sql2} { set r1 [list] set r2 [list] db eval "explain $sql1" { lappend r1 $opcode $p1 $p2 $p3 $p4 $p5} db eval "explain $sql2" { lappend r2 $opcode $p1 $p2 $p3 $p4 $p5} return [expr {$r1==$r2}] } proc do_compare_vdbe_test {tn sql1 sql2 res} { uplevel [list do_test $tn [list compare_vdbe $sql1 $sql2] $res] } #------------------------------------------------------------------------- # Test that various statements that are eligible for the optimization # produce the same VDBE code as optimizing by hand does. # foreach {tn sql1 sql2} { 1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2" "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a" 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>5 AND a=2" "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a HAVING sum(b)>5" 3 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING a=2" "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE binary" 4 { SELECT x,y FROM ( SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a ) WHERE x BETWEEN 8888 AND 9999 } { SELECT x,y FROM ( SELECT a AS x, sum(b) AS y FROM t1 WHERE x BETWEEN 8888 AND 9999 GROUP BY a ) } 5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 0" "SELECT a, sum(b) FROM t1 WHERE 0 GROUP BY a COLLATE binary" 6 "SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d" "SELECT count(*) FROM t1,t2 WHERE a=c AND b=d GROUP BY b, d" 7 { SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d COLLATE nocase } { SELECT count(*) FROM t1,t2 WHERE a=c AND b=d COLLATE nocase GROUP BY b, d } 8 "SELECT a, sum(b) FROM t1 GROUP BY a||b HAVING substr(a||b, 1, 1)='a'" "SELECT a, sum(b) FROM t1 WHERE substr(a||b, 1, 1)='a' GROUP BY a||b" } { do_compare_vdbe_test 2.$tn $sql1 $sql2 1 } #------------------------------------------------------------------------- # 1: Test that the optimization is only applied if the GROUP BY term # uses BINARY collation. # # 2: Not applied if there is a non-deterministic function in the HAVING # term. # foreach {tn sql1 sql2} { 1 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE nocase HAVING a=2" "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE nocase" 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING randomblob(a)<X'88'" "SELECT a, sum(b) FROM t1 WHERE randomblob(a)<X'88' GROUP BY a" } { do_compare_vdbe_test 3.$tn $sql1 $sql2 0 } #------------------------------------------------------------------------- # Test that non-deterministic functions disqualify a term from being # moved from the HAVING to WHERE clause. # do_execsql_test 4.1 { CREATE TABLE t3(a, b); INSERT INTO t3 VALUES(1, 1); INSERT INTO t3 VALUES(1, 2); INSERT INTO t3 VALUES(1, 3); INSERT INTO t3 VALUES(2, 1); INSERT INTO t3 VALUES(2, 2); INSERT INTO t3 VALUES(2, 3); } proc nondeter {args} { incr ::nondeter_ret expr {$::nondeter_ret % 2} } db func nondeter nondeter set ::nondeter_ret 0 do_execsql_test 4.2 { SELECT a, sum(b) FROM t3 GROUP BY a HAVING nondeter(a) } {1 6} # If the term where moved, the query above would return the same # result as the following. But it does not. # set ::nondeter_ret 0 do_execsql_test 4.3 { SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a } {1 4 2 2} finish_test |