/ Check-in [2a957396]
Login

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

Overview
Comment:Account for the ASC/DESC properties of ORDER BY expressions when using the same index for GROUP BY and ORDER BY. Candidate fix for [ba7cbfaedc].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 2a9573962b837973c4959465d8a5f2641d109a5a
User & Date: dan 2014-10-10 20:52:53
Context
2014-10-11
02:12
Make sure that a GROUP BY that also implements an ORDER BY scans the table in the correct order. Fix for ticket [ba7cbfaedc7e6]. check-in: 7a32fdfd user: drh tags: trunk
2014-10-10
20:52
Account for the ASC/DESC properties of ORDER BY expressions when using the same index for GROUP BY and ORDER BY. Candidate fix for [ba7cbfaedc]. Closed-Leaf check-in: 2a957396 user: dan tags: experimental
19:15
Restrict the scope of the explainIndexRange() function in where.c. check-in: c3012452 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/where.c.

  5823   5823         pWInfo->nOBSat = pFrom->isOrdered;
  5824   5824         if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0;
  5825   5825         pWInfo->revMask = pFrom->revLoop;
  5826   5826       }
  5827   5827       if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
  5828   5828           && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
  5829   5829       ){
  5830         -      Bitmask notUsed = 0;
  5831         -      int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, 
  5832         -          pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &notUsed
  5833         -      );
         5830  +      Bitmask revMask = 0;
         5831  +      int nOrder;
         5832  +      assert( pWInfo->wctrlFlags & WHERE_GROUPBY );
  5834   5833         assert( pWInfo->sorted==0 );
  5835         -      pWInfo->sorted = (nOrder==pWInfo->pOrderBy->nExpr);
         5834  +      pWInfo->wctrlFlags &= ~WHERE_GROUPBY;
         5835  +      nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, 
         5836  +          pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &revMask
         5837  +      );
         5838  +      pWInfo->wctrlFlags |= WHERE_GROUPBY;
         5839  +      if( nOrder==pWInfo->pOrderBy->nExpr ){
         5840  +        pWInfo->sorted = 1;
         5841  +        pWInfo->revMask = revMask;
         5842  +      }
  5836   5843       }
  5837   5844     }
  5838   5845   
  5839   5846   
  5840   5847     pWInfo->nRowOut = pFrom->nRow;
  5841   5848   
  5842   5849     /* Free temporary memory and return success */

Added test/tkt-ba7cbfaedc.test.

            1  +# 2014-10-11
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +# Test that ticket [ba7cbfaedc] has been fixed.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix tkt-ba7cbfaedc
           18  +
           19  +do_execsql_test 1 {
           20  +  CREATE TABLE t1 (x, y);
           21  +  INSERT INTO t1 VALUES (3, 'a');
           22  +  INSERT INTO t1 VALUES (1, 'a'); 
           23  +  INSERT INTO t1 VALUES (2, 'b');
           24  +  INSERT INTO t1 VALUES (2, 'a');
           25  +  INSERT INTO t1 VALUES (3, 'b');
           26  +  INSERT INTO t1 VALUES (1, 'b'); 
           27  +}
           28  +
           29  +do_execsql_test 1.1 {
           30  +  CREATE INDEX i1 ON t1(x, y);
           31  +}
           32  +
           33  +foreach {n idx} {
           34  +  1 { CREATE INDEX i1 ON t1(x, y) }
           35  +  2 { CREATE INDEX i1 ON t1(x DESC, y) }
           36  +  3 { CREATE INDEX i1 ON t1(x, y DESC) }
           37  +  4 { CREATE INDEX i1 ON t1(x DESC, y DESC) }
           38  +} {
           39  +  catchsql { DROP INDEX i1 }
           40  +  execsql $idx
           41  +  foreach {tn q res} {
           42  +    1 "GROUP BY x, y ORDER BY x, y"            {1 a 1 b   2 a 2 b   3 a 3 b}
           43  +    2 "GROUP BY x, y ORDER BY x DESC, y"       {3 a 3 b   2 a 2 b   1 a 1 b}
           44  +    3 "GROUP BY x, y ORDER BY x, y DESC"       {1 b 1 a   2 b 2 a   3 b 3 a}
           45  +    4 "GROUP BY x, y ORDER BY x DESC, y DESC"  {3 b 3 a   2 b 2 a   1 b 1 a}
           46  +  } {
           47  +    do_execsql_test 1.$n.$tn "SELECT * FROM t1 $q" $res
           48  +  }
           49  +}
           50  +
           51  +do_execsql_test 2.0 {
           52  +  drop table if exists t1;
           53  +  create table t1(id int);
           54  +  insert into t1(id) values(1),(2),(3),(4),(5);
           55  +  create index t1_idx_id on t1(id asc);
           56  +  select * from t1 group by id order by id;
           57  +  select * from t1 group by id order by id asc;
           58  +  select * from t1 group by id order by id desc;
           59  +} {
           60  +  1 2 3 4 5   1 2 3 4 5   5 4 3 2 1
           61  +}
           62  +
           63  +finish_test
           64  +
           65  +