/ Check-in [7a32fdfd]
Login

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

Overview
Comment:Make sure that a GROUP BY that also implements an ORDER BY scans the table in the correct order. Fix for ticket [ba7cbfaedc7e6].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7a32fdfd4be2138c0ab00f3dc6f54a70e4e07be4
User & Date: drh 2014-10-11 02:12:58
Context
2014-10-11
10:52
Require the SQLITE_ENABLE_RTREE compile-time option in speedtest1.c in order to enable the R-Tree tests. check-in: 5d29a033 user: drh tags: trunk
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
01:22
Remove an unnecessary conditional from the EXPLAIN QUERY PLAN logic. check-in: c5dc83eb 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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

5356
5357
5358
5359
5360
5361
5362
5363
5364
5365
5366
5367
5368
5369
5370
....
5821
5822
5823
5824
5825
5826
5827
5828
5829
5830
5831
5832
5833



5834
5835
5836
5837
5838
5839
5840
            pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
            if( !pColl ) pColl = db->pDfltColl;
            if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
          }
          isMatch = 1;
          break;
        }
        if( isMatch && (pWInfo->wctrlFlags & WHERE_GROUPBY)==0 ){
          /* Make sure the sort order is compatible in an ORDER BY clause.
          ** Sort order is irrelevant for a GROUP BY clause. */
          if( revSet ){
            if( (rev ^ revIdx)!=pOrderBy->a[i].sortOrder ) isMatch = 0;
          }else{
            rev = revIdx ^ pOrderBy->a[i].sortOrder;
            if( rev ) *pRevMask |= MASKBIT(iLoop);
................................................................................
      pWInfo->nOBSat = pFrom->isOrdered;
      if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0;
      pWInfo->revMask = pFrom->revLoop;
    }
    if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
        && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
    ){
      Bitmask notUsed = 0;
      int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, 
          pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &notUsed
      );
      assert( pWInfo->sorted==0 );
      pWInfo->sorted = (nOrder==pWInfo->pOrderBy->nExpr);



    }
  }


  pWInfo->nRowOut = pFrom->nRow;

  /* Free temporary memory and return success */







|







 







|

|


|
>
>
>







5356
5357
5358
5359
5360
5361
5362
5363
5364
5365
5366
5367
5368
5369
5370
....
5821
5822
5823
5824
5825
5826
5827
5828
5829
5830
5831
5832
5833
5834
5835
5836
5837
5838
5839
5840
5841
5842
5843
            pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
            if( !pColl ) pColl = db->pDfltColl;
            if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
          }
          isMatch = 1;
          break;
        }
        if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){
          /* Make sure the sort order is compatible in an ORDER BY clause.
          ** Sort order is irrelevant for a GROUP BY clause. */
          if( revSet ){
            if( (rev ^ revIdx)!=pOrderBy->a[i].sortOrder ) isMatch = 0;
          }else{
            rev = revIdx ^ pOrderBy->a[i].sortOrder;
            if( rev ) *pRevMask |= MASKBIT(iLoop);
................................................................................
      pWInfo->nOBSat = pFrom->isOrdered;
      if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0;
      pWInfo->revMask = pFrom->revLoop;
    }
    if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
        && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
    ){
      Bitmask revMask = 0;
      int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, 
          pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &revMask
      );
      assert( pWInfo->sorted==0 );
      if( nOrder==pWInfo->pOrderBy->nExpr ){
        pWInfo->sorted = 1;
        pWInfo->revMask = revMask;
      }
    }
  }


  pWInfo->nRowOut = pFrom->nRow;

  /* Free temporary memory and return success */

Added test/tkt-ba7cbfaedc.test.



































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# 2014-10-11
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
#
# Test that ticket [ba7cbfaedc] has been fixed.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix tkt-ba7cbfaedc

do_execsql_test 1 {
  CREATE TABLE t1 (x, y);
  INSERT INTO t1 VALUES (3, 'a');
  INSERT INTO t1 VALUES (1, 'a'); 
  INSERT INTO t1 VALUES (2, 'b');
  INSERT INTO t1 VALUES (2, 'a');
  INSERT INTO t1 VALUES (3, 'b');
  INSERT INTO t1 VALUES (1, 'b'); 
}

do_execsql_test 1.1 {
  CREATE INDEX i1 ON t1(x, y);
}

foreach {n idx} {
  1 { CREATE INDEX i1 ON t1(x, y) }
  2 { CREATE INDEX i1 ON t1(x DESC, y) }
  3 { CREATE INDEX i1 ON t1(x, y DESC) }
  4 { CREATE INDEX i1 ON t1(x DESC, y DESC) }
} {
  catchsql { DROP INDEX i1 }
  execsql $idx
  foreach {tn q res} {
    1 "GROUP BY x, y ORDER BY x, y"            {1 a 1 b   2 a 2 b   3 a 3 b}
    2 "GROUP BY x, y ORDER BY x DESC, y"       {3 a 3 b   2 a 2 b   1 a 1 b}
    3 "GROUP BY x, y ORDER BY x, y DESC"       {1 b 1 a   2 b 2 a   3 b 3 a}
    4 "GROUP BY x, y ORDER BY x DESC, y DESC"  {3 b 3 a   2 b 2 a   1 b 1 a}
  } {
    do_execsql_test 1.$n.$tn "SELECT * FROM t1 $q" $res
  }
}

do_execsql_test 2.0 {
  drop table if exists t1;
  create table t1(id int);
  insert into t1(id) values(1),(2),(3),(4),(5);
  create index t1_idx_id on t1(id asc);
  select * from t1 group by id order by id;
  select * from t1 group by id order by id asc;
  select * from t1 group by id order by id desc;
} {
  1 2 3 4 5   1 2 3 4 5   5 4 3 2 1
}

finish_test