Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a problem with vector range constraints involving the rowid column. And other issues. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | rowvalue |
Files: | files | file ages | folders |
SHA1: |
3ef75d45ebcd8ede91596d69e55fe7d6 |
User & Date: | dan 2016-08-01 20:14:31.976 |
Context
2016-08-02
| ||
16:18 | Add missing comments and make some code on this branch clearer. (check-in: 6937677cc2 user: dan tags: rowvalue) | |
2016-08-01
| ||
20:14 | Fix a problem with vector range constraints involving the rowid column. And other issues. (check-in: 3ef75d45eb user: dan tags: rowvalue) | |
16:37 | Fix a problem with IN(...) constraints where the LHS is a sub-select that is an aggregate query. (check-in: 1f4dba87da user: dan tags: rowvalue) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1884 1885 1886 1887 1888 1889 1890 | ** populated epheremal table. ** IN_INDEX_NOOP - No cursor was allocated. The IN operator must be ** implemented as a sequence of comparisons. ** ** An existing b-tree might be used if the RHS expression pX is a simple ** subquery such as: ** | | | | | | | > > > > > > > > > > > | 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 | ** populated epheremal table. ** IN_INDEX_NOOP - No cursor was allocated. The IN operator must be ** implemented as a sequence of comparisons. ** ** An existing b-tree might be used if the RHS expression pX is a simple ** subquery such as: ** ** SELECT <column1>, <column2>... FROM <table> ** ** If the RHS of the IN operator is a list or a more complex subquery, then ** an ephemeral table might need to be generated from the RHS and then ** pX->iTable made to point to the ephemeral table instead of an ** existing table. ** ** The inFlags parameter must contain exactly one of the bits ** IN_INDEX_MEMBERSHIP or IN_INDEX_LOOP. If inFlags contains ** IN_INDEX_MEMBERSHIP, then the generated table will be used for a ** fast membership test. When the IN_INDEX_LOOP bit is set, the ** IN index will be used to loop over all values of the RHS of the ** IN operator. ** ** When IN_INDEX_LOOP is used (and the b-tree will be used to iterate ** through the set members) then the b-tree must not contain duplicates. ** An epheremal table must be used unless the selected columns are guaranteed ** to be unique - either because it is an INTEGER PRIMARY KEY or due to ** a UNIQUE constraint or index. ** ** When IN_INDEX_MEMBERSHIP is used (and the b-tree will be used ** for fast set membership tests) then an epheremal table must ** be used unless <columns> is a single INTEGER PRIMARY KEY column or an ** index can be found with the specified <columns> as its left-most. ** ** If the IN_INDEX_NOOP_OK and IN_INDEX_MEMBERSHIP are both set and ** if the RHS of the IN operator is a list (not a subquery) then this ** routine might decide that creating an ephemeral b-tree for membership ** testing is too expensive and return IN_INDEX_NOOP. In that case, the ** calling routine should implement the IN operator using a sequence ** of Eq or Ne comparison operations. ** ** When the b-tree is being used for membership tests, the calling function ** might need to know whether or not the RHS side of the IN operator ** contains a NULL. If prRhsHasNull is not a NULL pointer and ** if there is any chance that the (...) might contain a NULL value at ** runtime, then a register is allocated and the register number written ** to *prRhsHasNull. If there is no chance that the (...) contains a ** NULL value, then *prRhsHasNull is left unchanged. ** ** If a register is allocated and its location stored in *prRhsHasNull, then ** the value in that register will be NULL if the b-tree contains one or more ** NULL values, and it will be some non-NULL value if the b-tree contains no ** NULL values. ** ** If the aiMap parameter is not NULL, it must point to an array containing ** one element for each column returned by the SELECT statement on the RHS ** of the IN(...) operator. The i'th entry of the array is populated with the ** offset of the index column that matches the i'th column returned by the ** SELECT. For example, if the expression and selected index are: ** ** (?,?,?) IN (SELECT a, b, c FROM t1) ** CREATE INDEX i1 ON t1(b, c, a); ** ** then aiMap[] is populated with {2, 0, 1}. */ #ifndef SQLITE_OMIT_SUBQUERY int sqlite3FindInIndex( Parse *pParse, Expr *pX, u32 inFlags, int *prRhsHasNull, |
︙ | ︙ | |||
2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 | n = sqlite3ExprVectorSize(pX->pLeft); for(i=0; i<n; i++) aiMap[i] = i; } return eType; } #endif static char *exprINAffinity(Parse *pParse, Expr *pExpr){ Expr *pLeft = pExpr->pLeft; int nVal = sqlite3ExprVectorSize(pLeft); char *zRet; zRet = sqlite3DbMallocZero(pParse->db, nVal+1); if( zRet ){ int i; for(i=0; i<nVal; i++){ | > > > > > > > > > > | | | | | | < < | 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 | n = sqlite3ExprVectorSize(pX->pLeft); for(i=0; i<n; i++) aiMap[i] = i; } return eType; } #endif /* ** Argument pExpr is an (?, ?...) IN(...) expression. This ** function allocates and returns a nul-terminated string containing ** the affinities to be used for each column of the comparison. ** ** It is the responsibility of the caller to ensure that the returned ** string is eventually freed using sqlite3DbFree(). */ static char *exprINAffinity(Parse *pParse, Expr *pExpr){ Expr *pLeft = pExpr->pLeft; int nVal = sqlite3ExprVectorSize(pLeft); Select *pSelect = (pExpr->flags & EP_xIsSelect) ? pExpr->x.pSelect : 0; char *zRet; assert( pExpr->op==TK_IN ); zRet = sqlite3DbMallocZero(pParse->db, nVal+1); if( zRet ){ int i; for(i=0; i<nVal; i++){ Expr *pA = exprVectorField(pLeft, i); char a = sqlite3ExprAffinity(pA); if( pSelect ){ zRet[i] = sqlite3CompareAffinity(pSelect->pEList->a[i].pExpr, a); }else{ zRet[i] = a; } } zRet[nVal] = '\0'; } return zRet; } #ifndef SQLITE_OMIT_SUBQUERY |
︙ | ︙ | |||
2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 | case TK_IN: { int addr; /* Address of OP_OpenEphemeral instruction */ Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */ KeyInfo *pKeyInfo = 0; /* Key information */ int nVal; /* Size of vector pLeft */ nVal = sqlite3ExprVectorSize(pLeft); /* Whether this is an 'x IN(SELECT...)' or an 'x IN(<exprlist>)' ** expression it is handled the same way. An ephemeral table is | > | | | 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 | case TK_IN: { int addr; /* Address of OP_OpenEphemeral instruction */ Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */ KeyInfo *pKeyInfo = 0; /* Key information */ int nVal; /* Size of vector pLeft */ nVal = sqlite3ExprVectorSize(pLeft); assert( !isRowid || nVal==1 ); /* Whether this is an 'x IN(SELECT...)' or an 'x IN(<exprlist>)' ** expression it is handled the same way. An ephemeral table is ** filled with index keys representing the results from the ** SELECT or the <exprlist>. ** ** If the 'x' expression is a column value, or the SELECT... ** statement returns a column value, then the affinity of that ** column is used to build the index keys. If both 'x' and the ** SELECT... statement are columns, then numeric affinity is used ** if either column has NUMERIC or INTEGER affinity. If neither ** 'x' nor the SELECT... statement are columns, then numeric affinity |
︙ | ︙ | |||
2466 2467 2468 2469 2470 2471 2472 | int rRhsHasNull = 0; /* Register that is true if RHS contains NULL values */ int eType; /* Type of the RHS */ int r1; /* Temporary use register */ Vdbe *v; /* Statement under construction */ int *aiMap = 0; /* Map from vector field to index column */ char *zAff = 0; /* Affinity string for comparisons */ int nVector; /* Size of vectors for this IN(...) op */ | < > > > | < | | 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 | int rRhsHasNull = 0; /* Register that is true if RHS contains NULL values */ int eType; /* Type of the RHS */ int r1; /* Temporary use register */ Vdbe *v; /* Statement under construction */ int *aiMap = 0; /* Map from vector field to index column */ char *zAff = 0; /* Affinity string for comparisons */ int nVector; /* Size of vectors for this IN(...) op */ Expr *pLeft = pExpr->pLeft; int i; if( sqlite3ExprCheckIN(pParse, pExpr) ) return; zAff = exprINAffinity(pParse, pExpr); nVector = sqlite3ExprVectorSize(pExpr->pLeft); aiMap = (int*)sqlite3DbMallocZero( pParse->db, nVector*(sizeof(int) + sizeof(char)) + 1 ); if( !zAff || !aiMap ){ sqlite3DbFree(pParse->db, aiMap); return; } /* Attempt to compute the RHS. After this step, if anything other than ** IN_INDEX_NOOP is returned, the table opened ith cursor pExpr->iTable ** contains the values that make up the RHS. If IN_INDEX_NOOP is returned, ** the RHS has not yet been coded. */ v = pParse->pVdbe; assert( v!=0 ); /* OOM detected prior to this routine */ |
︙ | ︙ | |||
2501 2502 2503 2504 2505 2506 2507 | /* Code the LHS, the <expr> from "<expr> IN (...)". If the LHS is a ** vector, then it is stored in an array of nVector registers starting ** at r1. */ r1 = sqlite3GetTempRange(pParse, nVector); sqlite3ExprCachePush(pParse); if( nVector>1 && (pLeft->flags & EP_xIsSelect) ){ | | < | | < | < < | > > | < < < < < < < | 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 | /* Code the LHS, the <expr> from "<expr> IN (...)". If the LHS is a ** vector, then it is stored in an array of nVector registers starting ** at r1. */ r1 = sqlite3GetTempRange(pParse, nVector); sqlite3ExprCachePush(pParse); if( nVector>1 && (pLeft->flags & EP_xIsSelect) ){ int regSelect = sqlite3CodeSubselect(pParse, pLeft, 0, 0); for(i=0; i<nVector; i++){ sqlite3VdbeAddOp3(v, OP_Copy, regSelect+i, r1+aiMap[i], 0); } }else{ for(i=0; i<nVector; i++){ Expr *pLhs = exprVectorField(pLeft, i); sqlite3ExprCode(pParse, pLhs, r1+aiMap[i]); } } /* If sqlite3FindInIndex() did not find or create an index that is ** suitable for evaluating the IN operator, then evaluate using a ** sequence of comparisons. */ |
︙ | ︙ | |||
2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 | sqlite3VdbeJumpHere(v, addr1); } } } sqlite3ReleaseTempReg(pParse, r1); sqlite3ExprCachePop(pParse); sqlite3DbFree(pParse->db, aiMap); VdbeComment((v, "end IN expr")); } #endif /* SQLITE_OMIT_SUBQUERY */ #ifndef SQLITE_OMIT_FLOATING_POINT /* ** Generate an instruction that will put the floating point | > | 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 | sqlite3VdbeJumpHere(v, addr1); } } } sqlite3ReleaseTempReg(pParse, r1); sqlite3ExprCachePop(pParse); sqlite3DbFree(pParse->db, aiMap); sqlite3DbFree(pParse->db, zAff); VdbeComment((v, "end IN expr")); } #endif /* SQLITE_OMIT_SUBQUERY */ #ifndef SQLITE_OMIT_FLOATING_POINT /* ** Generate an instruction that will put the floating point |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
874 875 876 877 878 879 880 | ** does not matter. But there might be a LIMIT clause, in which ** case the order does matter */ pushOntoSorter(pParse, pSort, p, regResult, regResult, 1, nPrefixReg); }else{ int r1 = sqlite3GetTempReg(pParse); assert( sqlite3Strlen30(pDest->zAffSdst)==nResultCol ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, nResultCol, | | | 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 | ** does not matter. But there might be a LIMIT clause, in which ** case the order does matter */ pushOntoSorter(pParse, pSort, p, regResult, regResult, 1, nPrefixReg); }else{ int r1 = sqlite3GetTempReg(pParse); assert( sqlite3Strlen30(pDest->zAffSdst)==nResultCol ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, nResultCol, r1, pDest->zAffSdst, nResultCol); sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol); sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1); sqlite3ReleaseTempReg(pParse, r1); } break; } |
︙ | ︙ | |||
1257 1258 1259 1260 1261 1262 1263 | sqlite3VdbeChangeP5(v, OPFLAG_APPEND); break; } #ifndef SQLITE_OMIT_SUBQUERY case SRT_Set: { assert( nColumn==sqlite3Strlen30(pDest->zAffSdst) ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, nColumn, regRowid, | | < | 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 | sqlite3VdbeChangeP5(v, OPFLAG_APPEND); break; } #ifndef SQLITE_OMIT_SUBQUERY case SRT_Set: { assert( nColumn==sqlite3Strlen30(pDest->zAffSdst) ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, nColumn, regRowid, pDest->zAffSdst, nColumn); sqlite3ExprCacheAffinityChange(pParse, regRow, nColumn); sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRowid); break; } case SRT_Mem: { /* The LIMIT clause will terminate the loop for us */ break; } #endif default: { assert( eDest==SRT_Output || eDest==SRT_Coroutine ); testcase( eDest==SRT_Output ); |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
2185 2186 2187 2188 2189 2190 2191 | } } if( pLoop->nOut > nRow-iReduce ) pLoop->nOut = nRow - iReduce; } /* ** Term pTerm is a vector range comparison operation. The first comparison | | > > > > > > > > > > > > > | 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 | } } if( pLoop->nOut > nRow-iReduce ) pLoop->nOut = nRow - iReduce; } /* ** Term pTerm is a vector range comparison operation. The first comparison ** in the vector can be optimized using column nEq of the index. This ** function returns the total number of vector elements that can be used ** as part of the range comparison. ** ** For example, if the query is: ** ** WHERE a = ? AND (b, c, d) > (?, ?, ?) ** ** and the index: ** ** CREATE INDEX ... ON (a, b, c, d, e) ** ** then this function would be invoked with nEq=1. The value returned in ** this case is 3. */ int whereRangeVectorLen( Parse *pParse, int iCur, Index *pIdx, int nEq, WhereTerm *pTerm ){ int nCmp = sqlite3ExprVectorSize(pTerm->pExpr->pLeft); int i; |
︙ | ︙ |
Changes to src/wherecode.c.
︙ | ︙ | |||
946 947 948 949 950 951 952 953 954 955 956 957 958 959 | if( pIdx->aiColumn[i]>=0 ) ai[pIdx->aiColumn[i]+1] = i+1; } sqlite3VdbeChangeP4(v, -1, (char*)ai, P4_INTARRAY); } } } static void codeExprOrVector(Parse *pParse, Expr *p, int iReg, int nReg){ assert( nReg>0 ); if( sqlite3ExprIsVector(p) ){ int i; if( (p->flags & EP_xIsSelect)==0 ){ ExprList *pList = p->x.pList; assert( nReg<=pList->nExpr ); | > > > > > > > > > | 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 | if( pIdx->aiColumn[i]>=0 ) ai[pIdx->aiColumn[i]+1] = i+1; } sqlite3VdbeChangeP4(v, -1, (char*)ai, P4_INTARRAY); } } } /* ** If the expression passed as the second argument is a vector, generate ** code to write the first nReg elements of the vector into an array ** of registers starting with iReg. ** ** If the expression is not a vector, then nReg must be passed 1. In ** this case, generate code to evaluate the expression and leave the ** result in register iReg. */ static void codeExprOrVector(Parse *pParse, Expr *p, int iReg, int nReg){ assert( nReg>0 ); if( sqlite3ExprIsVector(p) ){ int i; if( (p->flags & EP_xIsSelect)==0 ){ ExprList *pList = p->x.pList; assert( nReg<=pList->nExpr ); |
︙ | ︙ | |||
1234 1235 1236 1237 1238 1239 1240 | if( 0==sqlite3ExprIsVector(pX->pRight) && (pX->op==TK_LT || pX->op==TK_GT) ){ testOp = bRev ? OP_Le : OP_Ge; }else{ testOp = bRev ? OP_Lt : OP_Gt; } | > | > | 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 | if( 0==sqlite3ExprIsVector(pX->pRight) && (pX->op==TK_LT || pX->op==TK_GT) ){ testOp = bRev ? OP_Le : OP_Ge; }else{ testOp = bRev ? OP_Lt : OP_Gt; } if( 0==sqlite3ExprIsVector(pX->pRight) ){ disableTerm(pLevel, pEnd); } } start = sqlite3VdbeCurrentAddr(v); pLevel->op = bRev ? OP_Prev : OP_Next; pLevel->p1 = iCur; pLevel->p2 = start; assert( pLevel->p5==0 ); if( testOp!=OP_Noop ){ |
︙ | ︙ |
Changes to test/rowvalue.test.
︙ | ︙ | |||
196 197 198 199 200 201 202 203 204 205 206 | do_execsql_test 8.0 { CREATE TABLE j1(a); } do_execsql_test 8.1 { SELECT * FROM j1 WHERE (select min(a) FROM j1) IN (?, ?, ?) } finish_test | > > > > > > > > > > > > > > > > > > | 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 | do_execsql_test 8.0 { CREATE TABLE j1(a); } do_execsql_test 8.1 { SELECT * FROM j1 WHERE (select min(a) FROM j1) IN (?, ?, ?) } do_execsql_test 9.0 { CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); INSERT INTO t2 VALUES(1, 1, 1); INSERT INTO t2 VALUES(2, 2, 2); INSERT INTO t2 VALUES(3, 3, 3); INSERT INTO t2 VALUES(4, 4, 4); INSERT INTO t2 VALUES(5, 5, 5); } foreach {tn q res} { 1 "(a, b) > (2, 1)" {2 3 4 5} 2 "(a, b) > (2, 2)" {3 4 5} 3 "(a, b) < (4, 5)" {1 2 3 4} 4 "(a, b) < (4, 3)" {1 2 3} } { do_execsql_test 9.$tn "SELECT c FROM t2 WHERE $q" $res } finish_test |