/ Check-in [0c3cafb7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix the ORDER BY LIMIT optimization backport so that it works when the ORDER BY uses the DESC direction.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.8.9
Files: files | file ages | folders
SHA1: 0c3cafb7ebb887dbfa2652f8bf69b52ed265c344
User & Date: drh 2016-09-23 18:06:22
Context
2016-11-02
16:29
When the block sorting optimization is used in a scalar subquery, be sure to exit the loop as soon as the first valid output row is received. Fix for ticket [cb3aa0641d9a4] backported to the 3.8.9 branch. check-in: 8e4ba115 user: drh tags: branch-3.8.9
2016-09-23
18:06
Fix the ORDER BY LIMIT optimization backport so that it works when the ORDER BY uses the DESC direction. check-in: 0c3cafb7 user: drh tags: branch-3.8.9
2016-09-14
01:43
Backport the ORDER BY LIMIT optimization to version 3.8.9. check-in: db361482 user: drh tags: branch-3.8.9
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  6268   6268         }
  6269   6269       }else{
  6270   6270         pWInfo->nOBSat = pFrom->isOrdered;
  6271   6271         pWInfo->revMask = pFrom->revLoop;
  6272   6272         if( pWInfo->nOBSat<=0 ){
  6273   6273           pWInfo->nOBSat = 0;
  6274   6274           if( nLoop>0 && (pFrom->aLoop[nLoop-1]->wsFlags & WHERE_ONEROW)==0 ){
  6275         -          Bitmask m;
         6275  +          Bitmask m = 0;
  6276   6276             int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
  6277   6277                         WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
  6278   6278             if( rc==pWInfo->pOrderBy->nExpr ){
  6279   6279               pWInfo->bOrderedInnerLoop = 1;
  6280   6280               pWInfo->revMask = m;
  6281   6281             }
  6282   6282           }
  6283   6283         }
  6284         -      pWInfo->revMask = pFrom->revLoop;
  6285   6284       }
  6286   6285       if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
  6287   6286           && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
  6288   6287       ){
  6289   6288         Bitmask revMask = 0;
  6290   6289         int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, 
  6291   6290             pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &revMask

Changes to test/limit2.test.

   105    105     INSERT INTO t300 VALUES(0,1,99),(0,1,0),(0,0,0);
   106    106     SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC;
   107    107   } {0 1 99 . 0 0 0 . 0 1 0 .}
   108    108   do_execsql_test limit2-310 {
   109    109     SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC LIMIT 1;
   110    110   } {0 1 99 .}
   111    111   
   112         -
   113         -
          112  +# Make sure the SELECT loop is ordered correctly for the direction of
          113  +# the ORDER BY
          114  +#
          115  +do_execsql_test limit2-400 {
          116  +  CREATE TABLE t400(a,b);
          117  +  CREATE INDEX t400_ab ON t400(a,b);
          118  +  INSERT INTO t400(a,b) VALUES(1,90),(1,40),(2,80),(2,30),(3,70),(3,20);
          119  +  SELECT *,'x' FROM t400 WHERE a IN (1,2,3) ORDER BY b DESC LIMIT 3;
          120  +  SELECT *,'y' FROM t400 WHERE a IN (1,2,3) ORDER BY +b DESC LIMIT 3;
          121  +} {1 90 x 2 80 x 3 70 x 1 90 y 2 80 y 3 70 y}
   114    122   
   115    123   finish_test