Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make the result of an IN or NOT IN expression with an empty set on the right-hand side always either false or true, respectively, even if the left-hand side is NULL. Ticket [80e031a00f45dc] |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
c288ac644d0bfda2b9bc204dc86df8e7 |
User & Date: | drh 2010-07-14 18:24:06.000 |
References
2010-07-14
| ||
20:23 | The expression list on the RHS of an IN operator can no longer be empty because an empty expression list is now optimized out by changes in check-in [c288ac644d0bf]. Therefore add ALWAYS() macros around tests for the expression list being non-empty. (check-in: fd130ae56c user: drh tags: trunk) | |
Context
2010-07-14
| ||
19:31 | Additional test cases and evidence marks for the empty RHS bug on the IN operator - ticket [80e031a00f45dca877] (check-in: 6851c517ac user: drh tags: trunk) | |
18:24 | Make the result of an IN or NOT IN expression with an empty set on the right-hand side always either false or true, respectively, even if the left-hand side is NULL. Ticket [80e031a00f45dc] (check-in: c288ac644d user: drh tags: trunk) | |
18:10 | Fix an assert() failure in wal2.test caused by messing with the contents of shared memory. (check-in: 9f452514d9 user: dan tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1701 1702 1703 1704 1705 1706 1707 | default: { /* If this has to be a scalar SELECT. Generate code to put the ** value of this select in a memory cell and record the number ** of the memory cell in iColumn. If this is an EXISTS, write ** an integer 0 (not exists) or 1 (exists) into a memory cell ** and record that memory cell in iColumn. */ | < | > | 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 | default: { /* If this has to be a scalar SELECT. Generate code to put the ** value of this select in a memory cell and record the number ** of the memory cell in iColumn. If this is an EXISTS, write ** an integer 0 (not exists) or 1 (exists) into a memory cell ** and record that memory cell in iColumn. */ Select *pSel; /* SELECT statement to encode */ SelectDest dest; /* How to deal with SELECt result */ testcase( pExpr->op==TK_EXISTS ); testcase( pExpr->op==TK_SELECT ); assert( pExpr->op==TK_EXISTS || pExpr->op==TK_SELECT ); assert( ExprHasProperty(pExpr, EP_xIsSelect) ); pSel = pExpr->x.pSelect; sqlite3SelectDestInit(&dest, 0, ++pParse->nMem); if( pExpr->op==TK_SELECT ){ dest.eDest = SRT_Mem; sqlite3VdbeAddOp2(v, OP_Null, 0, dest.iParm); VdbeComment((v, "Init subquery result")); }else{ dest.eDest = SRT_Exists; sqlite3VdbeAddOp2(v, OP_Integer, 0, dest.iParm); VdbeComment((v, "Init EXISTS result")); } sqlite3ExprDelete(pParse->db, pSel->pLimit); pSel->pLimit = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[1]); if( sqlite3Select(pParse, pSel, &dest) ){ return 0; } rReg = dest.iParm; ExprSetIrreducible(pExpr); break; } |
︙ | ︙ | |||
1790 1791 1792 1793 1794 1795 1796 | affinity = comparisonAffinity(pExpr); /* Code the LHS, the <expr> from "<expr> IN (...)". */ sqlite3ExprCachePush(pParse); r1 = sqlite3GetTempReg(pParse); sqlite3ExprCode(pParse, pExpr->pLeft, r1); | > > > > > > > | > > > > > | | 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 | affinity = comparisonAffinity(pExpr); /* Code the LHS, the <expr> from "<expr> IN (...)". */ sqlite3ExprCachePush(pParse); r1 = sqlite3GetTempReg(pParse); sqlite3ExprCode(pParse, pExpr->pLeft, r1); /* If the LHS is NULL, then the result is either false or NULL depending ** on whether the RHS is empty or not, respectively. */ if( destIfNull==destIfFalse ){ /* Shortcut for the common case where the false and NULL outcomes are ** the same. */ sqlite3VdbeAddOp2(v, OP_IsNull, r1, destIfNull); }else{ int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse); sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfNull); sqlite3VdbeJumpHere(v, addr1); } if( eType==IN_INDEX_ROWID ){ /* In this case, the RHS is the ROWID of table b-tree */ sqlite3VdbeAddOp2(v, OP_MustBeInt, r1, destIfFalse); sqlite3VdbeAddOp3(v, OP_NotExists, pExpr->iTable, destIfFalse, r1); }else{ |
︙ | ︙ |
Changes to src/global.c.
︙ | ︙ | |||
171 172 173 174 175 176 177 178 179 180 181 182 183 184 | /* ** Hash table for global functions - functions common to all ** database connections. After initialization, this table is ** read-only. */ SQLITE_WSD FuncDefHash sqlite3GlobalFunctions; /* ** The value of the "pending" byte must be 0x40000000 (1 byte past the ** 1-gibabyte boundary) in a compatible database. SQLite never uses ** the database page that contains the pending byte. It never attempts ** to read or write that page. The pending byte page is set assign ** for use by the VFS layers as space for managing file locks. | > > > > > > > > > | 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 | /* ** Hash table for global functions - functions common to all ** database connections. After initialization, this table is ** read-only. */ SQLITE_WSD FuncDefHash sqlite3GlobalFunctions; /* ** Constant tokens for values 0 and 1. */ const Token sqlite3IntTokens[] = { { "0", 1 }, { "1", 1 } }; /* ** The value of the "pending" byte must be 0x40000000 (1 byte past the ** 1-gibabyte boundary) in a compatible database. SQLite never uses ** the database page that contains the pending byte. It never attempts ** to read or write that page. The pending byte page is set assign ** for use by the VFS layers as space for managing file locks. |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
955 956 957 958 959 960 961 | A.zEnd = Y.zEnd; } %ifndef SQLITE_OMIT_SUBQUERY %type in_op {int} in_op(A) ::= IN. {A = 0;} in_op(A) ::= NOT IN. {A = 1;} expr(A) ::= expr(X) in_op(N) LP exprlist(Y) RP(E). [IN] { | > > > > > > > > > > > > | | | | | | | | > | 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 | A.zEnd = Y.zEnd; } %ifndef SQLITE_OMIT_SUBQUERY %type in_op {int} in_op(A) ::= IN. {A = 0;} in_op(A) ::= NOT IN. {A = 1;} expr(A) ::= expr(X) in_op(N) LP exprlist(Y) RP(E). [IN] { if( Y==0 ){ // Expressions of the form // // expr1 IN () // expr1 NOT IN () // // simplify to constants 0 (false) and 1 (true), respectively, // regardless of the value of expr1. // A.pExpr = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[N]); sqlite3ExprDelete(pParse->db, X.pExpr); }else{ A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0); if( A.pExpr ){ A.pExpr->x.pList = Y; sqlite3ExprSetHeight(pParse, A.pExpr); }else{ sqlite3ExprListDelete(pParse->db, Y); } if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0); } A.zStart = X.zStart; A.zEnd = &E.z[E.n]; } expr(A) ::= LP(B) select(X) RP(E). { A.pExpr = sqlite3PExpr(pParse, TK_SELECT, 0, 0, 0); if( A.pExpr ){ A.pExpr->x.pSelect = X; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 2898 2899 | #endif int sqlite3ValueFromExpr(sqlite3 *, Expr *, u8, u8, sqlite3_value **); void sqlite3ValueApplyAffinity(sqlite3_value *, u8, u8); #ifndef SQLITE_AMALGAMATION extern const unsigned char sqlite3OpcodeProperty[]; extern const unsigned char sqlite3UpperToLower[]; extern const unsigned char sqlite3CtypeMap[]; extern SQLITE_WSD struct Sqlite3Config sqlite3Config; extern SQLITE_WSD FuncDefHash sqlite3GlobalFunctions; #ifndef SQLITE_OMIT_WSD extern int sqlite3PendingByte; #endif #endif void sqlite3RootPageMoved(Db*, int, int); | > | 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 2898 2899 2900 | #endif int sqlite3ValueFromExpr(sqlite3 *, Expr *, u8, u8, sqlite3_value **); void sqlite3ValueApplyAffinity(sqlite3_value *, u8, u8); #ifndef SQLITE_AMALGAMATION extern const unsigned char sqlite3OpcodeProperty[]; extern const unsigned char sqlite3UpperToLower[]; extern const unsigned char sqlite3CtypeMap[]; extern const Token sqlite3IntTokens[]; extern SQLITE_WSD struct Sqlite3Config sqlite3Config; extern SQLITE_WSD FuncDefHash sqlite3GlobalFunctions; #ifndef SQLITE_OMIT_WSD extern int sqlite3PendingByte; #endif #endif void sqlite3RootPageMoved(Db*, int, int); |
︙ | ︙ |
Added test/tkt-80e031a00f.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 | # 2010 July 14 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. Specifically, # it tests that ticket [80e031a00f45dca877ed92b225209cfa09280f4f] has been # resolved. That ticket is about IN and NOT IN operators with empty-set # right-hand sides. Such expressions should always return TRUE or FALSE # even if the left-hand side is NULL. # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/lock_common.tcl source $testdir/malloc_common.tcl do_execsql_test tkt-80e031a00f.1 {SELECT 1 IN ()} 0 do_execsql_test tkt-80e031a00f.2 {SELECT 1 NOT IN ()} 1 do_execsql_test tkt-80e031a00f.3 {SELECT null IN ()} 0 do_execsql_test tkt-80e031a00f.4 {SELECT null NOT IN ()} 1 do_execsql_test tkt-80e031a00f.5 { CREATE TABLE t1(x); SELECT 1 IN t1; } 0 do_execsql_test tkt-80e031a00f.6 {SELECT 1 NOT IN t1} 1 do_execsql_test tkt-80e031a00f.7 {SELECT null IN t1} 0 do_execsql_test tkt-80e031a00f.8 {SELECT null NOT IN t1} 1 do_execsql_test tkt-80e031a00f.9 { CREATE TABLE t2(y INTEGER PRIMARY KEY); SELECT 1 IN t2; } 0 do_execsql_test tkt-80e031a00f.10 {SELECT 1 NOT IN t2} 1 do_execsql_test tkt-80e031a00f.11 {SELECT null IN t2} 0 do_execsql_test tkt-80e031a00f.12 {SELECT null NOT IN t2} 1 do_execsql_test tkt-80e031a00f.9 { CREATE TABLE t3(z INT UNIQUE); SELECT 1 IN t3; } 0 do_execsql_test tkt-80e031a00f.13 {SELECT 1 NOT IN t3} 1 do_execsql_test tkt-80e031a00f.14 {SELECT null IN t3} 0 do_execsql_test tkt-80e031a00f.15 {SELECT null NOT IN t3} 1 finish_test |