Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator loop is actually used by the query plan. Fix for ticket [0c4df46116e90f92]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | branch-3.14 |
| Files: | files | file ages | folders |
| SHA1: |
b4f8be1f3258823b3a8f316c92f9019f |
| User & Date: | drh 2016-09-07 12:08:37 |
Context
|
2016-09-07
| ||
| 12:20 | Correct affinity computations for a SELECT on the RHS of an IN operator. Fix for ticket [199df4168c]. (check-in: 2d1983ff user: drh tags: branch-3.14) | |
| 12:08 | The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator loop is actually used by the query plan. Fix for ticket [0c4df46116e90f92]. (check-in: b4f8be1f user: drh tags: branch-3.14) | |
| 11:27 | Fix a problem in internal function sqlite3OpenTableAndIndices causing an operand of an unrelated VM instruction to be overwritten. Fix for [ef360601]. (check-in: e5c30f22 user: drh tags: branch-3.14) | |
| 01:51 | The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator loop is actually used by the query plan. Fix for ticket [0c4df46116e90f92]. (check-in: 820644b8 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
3397 3397 if( MASKBIT(i) & obSat ) continue; 3398 3398 pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr); 3399 3399 if( pOBExpr->op!=TK_COLUMN ) continue; 3400 3400 if( pOBExpr->iTable!=iCur ) continue; 3401 3401 pTerm = sqlite3WhereFindTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn, 3402 3402 ~ready, eqOpMask, 0); 3403 3403 if( pTerm==0 ) continue; 3404 + if( pTerm->eOperator==WO_IN ){ 3405 + /* IN terms are only valid for sorting in the ORDER BY LIMIT 3406 + ** optimization, and then only if they are actually used 3407 + ** by the query plan */ 3408 + assert( wctrlFlags & WHERE_ORDERBY_LIMIT ); 3409 + for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){} 3410 + if( j>=pLoop->nLTerm ) continue; 3411 + } 3404 3412 if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){ 3405 3413 const char *z1, *z2; 3406 3414 pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr); 3407 3415 if( !pColl ) pColl = db->pDfltColl; 3408 3416 z1 = pColl->zName; 3409 3417 pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr); 3410 3418 if( !pColl ) pColl = db->pDfltColl;
Changes to test/limit2.test.
92 92 93 93 SELECT *, '|' FROM t200, t201 WHERE x=b ORDER BY y LIMIT 3; 94 94 } {2 2 2 12345 |} 95 95 do_execsql_test limit2-210 { 96 96 SELECT *, '|' FROM t200 LEFT JOIN t201 ON x=b ORDER BY y LIMIT 3; 97 97 } {1 1 {} {} | 3 3 {} {} | 4 4 {} {} |} 98 98 99 - 99 +# Bug in the ORDER BY LIMIT optimization reported on 2016-09-06. 100 +# Ticket https://www.sqlite.org/src/info/559733b09e96 101 +# 102 +do_execsql_test limit2-300 { 103 + CREATE TABLE t300(a,b,c); 104 + CREATE INDEX t300x ON t300(a,b,c); 105 + INSERT INTO t300 VALUES(0,1,99),(0,1,0),(0,0,0); 106 + SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC; 107 +} {0 1 99 . 0 0 0 . 0 1 0 .} 108 +do_execsql_test limit2-310 { 109 + SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC LIMIT 1; 110 +} {0 1 99 .} 100 111 101 112 102 113 103 114 104 115 finish_test