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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
18745c67acdf7ebec378f55381741179 |
| User & Date: | drh 2002-08-04 00:52:38 |
Context
|
2002-08-06
| ||
| 12:05 | Fix typos the source to webpages html files. (CVS 703) (check-in: 61bb3af7 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: 18745c67 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: bdbdb866 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
|
** 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.106 2002/07/18 00:34:12 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ ................................................................................ sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1); }else{ distinct = -1; } /* Begin the database scan */ pWInfo = sqliteWhereBegin(pParse, p->base, pTabList, pWhere, 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, |
|
|
>
|
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
|
** 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. */ ................................................................................ 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
15
16
17
18
19
20
21
22
...
137
138
139
140
141
142
143
144
145
|
# 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.5 2002/01/22 14:11:30 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # do_test select3-1.0 { ................................................................................ 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 |
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
|
# 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 { ................................................................................ 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 |