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: |
2a9573962b837973c4959465d8a5f264 |
| 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
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], ¬Used 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 +