/ 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 Side-by-Side Diffs Ignore Whitespace Patch

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