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 |