SQLite

Check-in [b4f8be1f32]
Login

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
Timelines: family | ancestors | descendants | both | branch-3.14
Files: files | file ages | folders
SHA1: b4f8be1f3258823b3a8f316c92f9019f71ef86a0
User & Date: drh 2016-09-07 12:08:37.662
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: 2d1983ff9f 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: b4f8be1f32 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: e5c30f227b 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: 820644b886 user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
︙
3397
3398
3399
3400
3401
3402
3403








3404
3405
3406
3407
3408
3409
3410

3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418








+
+
+
+
+
+
+
+








      if( MASKBIT(i) & obSat ) continue;
      pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
      if( pOBExpr->op!=TK_COLUMN ) continue;
      if( pOBExpr->iTable!=iCur ) continue;
      pTerm = sqlite3WhereFindTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
                       ~ready, eqOpMask, 0);
      if( pTerm==0 ) continue;
      if( pTerm->eOperator==WO_IN ){
        /* IN terms are only valid for sorting in the ORDER BY LIMIT 
        ** optimization, and then only if they are actually used
        ** by the query plan */
        assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
        for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){}
        if( j>=pLoop->nLTerm ) continue;
      }
      if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
        const char *z1, *z2;
        pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
        if( !pColl ) pColl = db->pDfltColl;
        z1 = pColl->zName;
        pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
        if( !pColl ) pColl = db->pDfltColl;
︙
Changes to test/limit2.test.
︙
92
93
94
95
96
97
98


99










100
101
102
103
104

92
93
94
95
96
97
98
99
100

101
102
103
104
105
106
107
108
109
110
111
112
113
114
115








+
+
-
+
+
+
+
+
+
+
+
+
+







  SELECT *, '|' FROM t200, t201 WHERE x=b ORDER BY y LIMIT 3;
} {2 2 2 12345 |}
do_execsql_test limit2-210 {
  SELECT *, '|' FROM t200 LEFT JOIN t201 ON x=b ORDER BY y LIMIT 3;
} {1 1 {} {} | 3 3 {} {} | 4 4 {} {} |}

# Bug in the ORDER BY LIMIT optimization reported on 2016-09-06.
# Ticket https://www.sqlite.org/src/info/559733b09e96

#
do_execsql_test limit2-300 {
  CREATE TABLE t300(a,b,c);
  CREATE INDEX t300x ON t300(a,b,c);
  INSERT INTO t300 VALUES(0,1,99),(0,1,0),(0,0,0);
  SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC;
} {0 1 99 . 0 0 0 . 0 1 0 .}
do_execsql_test limit2-310 {
  SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC LIMIT 1;
} {0 1 99 .}




finish_test