Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix for ticket #131: When a SELECT contains a GROUP BY clause it cannot use an index for sorting. It has to sort as a separate operation after the GROUP BY is complete. (CVS 702) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
18745c67acdf7ebec378f55381741179 |
| User & Date: | drh 2002-08-04 00:52:38.000 |
Context
|
2002-08-06
| ||
| 12:05 | Fix typos the source to webpages html files. (CVS 703) (check-in: 61bb3af740 user: drh tags: trunk) | |
|
2002-08-04
| ||
| 00:52 | Fix for ticket #131: When a SELECT contains a GROUP BY clause it cannot use an index for sorting. It has to sort as a separate operation after the GROUP BY is complete. (CVS 702) (check-in: 18745c67ac user: drh tags: trunk) | |
|
2002-08-02
| ||
| 10:36 | Remove the restriction that a transaction cannot be started by one linuxthread and continued by another. Leave in the documentation the warning about not carrying a database connection across fork() but do not test for it any more. Ticket #130. (CVS 701) (check-in: bdbdb866f2 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.107 2002/08/04 00:52:38 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ |
| ︙ | ︙ | |||
1941 1942 1943 1944 1945 1946 1947 |
sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1);
}else{
distinct = -1;
}
/* Begin the database scan
*/
| | > | 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 |
sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1);
}else{
distinct = -1;
}
/* Begin the database scan
*/
pWInfo = sqliteWhereBegin(pParse, p->base, pTabList, pWhere, 0,
pGroupBy ? 0 : &pOrderBy);
if( pWInfo==0 ) goto select_end;
/* Use the standard inner loop if we are not dealing with
** aggregates
*/
if( !isAgg ){
if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
|
| ︙ | ︙ |
Changes to test/select3.test.
| ︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing aggregate functions and the # GROUP BY and HAVING clauses of SELECT statements. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.6 2002/08/04 00:52:38 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Build some test data
#
do_test select3-1.0 {
|
| ︙ | ︙ | |||
137 138 139 140 141 142 143 144 145 |
do_test select3-5.2 {
execsql {
SELECT log, count(*), avg(n), max(n+log*2) FROM t1
GROUP BY log
ORDER BY max(n+log*2), min(log,avg(n))
}
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 |
do_test select3-5.2 {
execsql {
SELECT log, count(*), avg(n), max(n+log*2) FROM t1
GROUP BY log
ORDER BY max(n+log*2), min(log,avg(n))
}
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
# Test sorting of GROUP BY results in the presence of an index
# on the GROUP BY column.
#
do_test select3-6.1 {
execsql {
SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-6.2 {
execsql {
SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;
}
} {5 17 4 9 3 5 2 3 1 2 0 1}
do_test select3-6.3 {
execsql {
SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;
}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-6.4 {
execsql {
SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;
}
} {5 17 4 9 3 5 2 3 1 2 0 1}
do_test select3-6.5 {
execsql {
CREATE INDEX i1 ON t1(log);
SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-6.6 {
execsql {
SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;
}
} {5 17 4 9 3 5 2 3 1 2 0 1}
do_test select3-6.7 {
execsql {
SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;
}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-6.8 {
execsql {
SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;
}
} {5 17 4 9 3 5 2 3 1 2 0 1}
finish_test
|