/ Check-in [b4f8be1f]
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 | SQL 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
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

3397
3398
3399
3400
3401
3402
3403








3404
3405
3406
3407
3408
3409
3410
      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_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;







>
>
>
>
>
>
>
>







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

  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 {} {} |}

















finish_test







>
>
|
>
>
>
>
>
>
>
>
>





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