Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a problem with estimating the number of rows visited by a query that uses a multi-column IN(SELECT...) constraint. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | rowvalue |
Files: | files | file ages | folders |
SHA1: |
3c2f908f5b7312570cfa74afcf4252a8 |
User & Date: | dan 2016-08-03 18:00:49.612 |
Context
2016-08-08
| ||
16:52 | Merge trunk changes with this branch. (check-in: 0e927a7e02 user: dan tags: rowvalue) | |
2016-08-03
| ||
18:00 | Fix a problem with estimating the number of rows visited by a query that uses a multi-column IN(SELECT...) constraint. (check-in: 3c2f908f5b user: dan tags: rowvalue) | |
16:39 | Fix another problem involving vector range constraints and mixed ASC/DESC indexes. (check-in: 1559f4c434 user: dan tags: rowvalue) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 | ); if( eOp & WO_IN ){ Expr *pExpr = pTerm->pExpr; pNew->wsFlags |= WHERE_COLUMN_IN; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ /* "x IN (SELECT ...)": TUNING: the SELECT returns 25 rows */ nIn = 46; assert( 46==sqlite3LogEst(25) ); }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ /* "x IN (value, value, ...)" */ nIn = sqlite3LogEst(pExpr->x.pList->nExpr); | > > > > > > > > > > < | | | | 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 | ); if( eOp & WO_IN ){ Expr *pExpr = pTerm->pExpr; pNew->wsFlags |= WHERE_COLUMN_IN; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ /* "x IN (SELECT ...)": TUNING: the SELECT returns 25 rows */ int i; nIn = 46; assert( 46==sqlite3LogEst(25) ); /* The expression may actually be of the form (x, y) IN (SELECT...). ** In this case there is a separate term for each of (x) and (y). ** However, the nIn multiplier should only be applied once, not once ** for each such term. The following loop checks that pTerm is the ** first such term in use, and sets nIn back to 0 if it is not. */ for(i=0; i<pNew->nLTerm-1; i++){ if( pNew->aLTerm[i]->pExpr==pExpr ) nIn = 0; } }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ /* "x IN (value, value, ...)" */ nIn = sqlite3LogEst(pExpr->x.pList->nExpr); assert( nIn>0 ); /* RHS always has 2 or more terms... The parser ** changes "x IN (?)" into "x=?". */ } }else if( eOp & (WO_EQ|WO_IS) ){ int iCol = pProbe->aiColumn[saved_nEq]; pNew->wsFlags |= WHERE_COLUMN_EQ; assert( saved_nEq==pNew->u.btree.nEq ); if( iCol==XN_ROWID || (iCol>0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1) ){ |
︙ | ︙ |
Changes to test/rowvalue4.test.
︙ | ︙ | |||
207 208 209 210 211 212 213 214 215 216 | do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c>?)} } do_eqp_test 3.2.6 { SELECT * FROM c1 WHERE a=1 AND (c, +b)>(1, 'c') } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} } } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c>?)} } do_eqp_test 3.2.6 { SELECT * FROM c1 WHERE a=1 AND (c, +b)>(1, 'c') } { 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} } } #------------------------------------------------------------------------ do_execsql_test 5.0 { CREATE TABLE d1(x, y); CREATE TABLE d2(a, b, c); CREATE INDEX d2ab ON d2(a, b); CREATE INDEX d2c ON d2(c); WITH i(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM i WHERE i<1000 ) INSERT INTO d2 SELECT i/3, i%3, i/3 FROM i; ANALYZE; } do_eqp_test 5.1 { SELECT * FROM d2 WHERE (a, b) IN (SELECT x, y FROM d1) AND (c) IN (SELECT y FROM d1) } { 0 0 0 {SEARCH TABLE d2 USING INDEX d2ab (a=? AND b=?)} 0 0 0 {EXECUTE LIST SUBQUERY 1} 1 0 0 {SCAN TABLE d1} 0 0 0 {EXECUTE LIST SUBQUERY 2} 2 0 0 {SCAN TABLE d1} } finish_test |