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 |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
7a32fdfd4be2138c0ab00f3dc6f54a70 |
| User & Date: | drh 2014-10-11 02:12:58.777 |
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: 5d29a033b0 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: 7a32fdfd4b user: drh tags: trunk) | |
| 01:22 | Remove an unnecessary conditional from the EXPLAIN QUERY PLAN logic. (check-in: c5dc83ebde 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: 2a9573962b user: dan tags: experimental) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
5356 5357 5358 5359 5360 5361 5362 |
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;
}
| | | 5356 5357 5358 5359 5360 5361 5362 5363 5364 5365 5366 5367 5368 5369 5370 |
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);
|
| ︙ | ︙ | |||
5821 5822 5823 5824 5825 5826 5827 |
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
){
| | | > | > > | 5821 5822 5823 5824 5825 5826 5827 5828 5829 5830 5831 5832 5833 5834 5835 5836 5837 5838 5839 5840 5841 5842 5843 |
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
|