SQLite

Check-in [0c3e368d47]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Aggregates with GROUP BY can sometimes return no result rows. (CVS 2674)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0c3e368d4766685b94a44f2c514e2dea806c297c
User & Date: drh 2005-09-08 12:57:28.000
Context
2005-09-08
14:17
Remove a few unused variables detected by Borland C. Ticket #1412. (CVS 2675) (check-in: cdfe372a95 user: drh tags: trunk)
12:57
Aggregates with GROUP BY can sometimes return no result rows. (CVS 2674) (check-in: 0c3e368d47 user: drh tags: trunk)
12:38
While doing a transaction comment, use fdatasync() instead of fsync() in cases there the file size is unchanged. (CVS 2673) (check-in: 3c555a8749 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
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.265 2005/09/08 01:58:43 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.








|








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.266 2005/09/08 12:57:28 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
︙︙
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930

            sqlite3VdbeAddOp(v, OP_Column, pCol->iTable, pCol->iColumn);
          }
          j++;
        }
        sqlite3VdbeAddOp(v, OP_MakeRecord, j, 0);
        sqlite3VdbeAddOp(v, OP_IdxInsert, sAggInfo.sortingIdx, 0);
        sqlite3WhereEnd(pWInfo);
        sqlite3VdbeAddOp(v, OP_Sort, sAggInfo.sortingIdx, 0);
        sAggInfo.useSortingIdx = 1;
      }

      /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
      ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
      ** Then compare the current GROUP BY terms against the GROUP BY terms
      ** from the previous row currently stored in a0, a1, a2...








|








2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930

            sqlite3VdbeAddOp(v, OP_Column, pCol->iTable, pCol->iColumn);
          }
          j++;
        }
        sqlite3VdbeAddOp(v, OP_MakeRecord, j, 0);
        sqlite3VdbeAddOp(v, OP_IdxInsert, sAggInfo.sortingIdx, 0);
        sqlite3WhereEnd(pWInfo);
        sqlite3VdbeAddOp(v, OP_Sort, sAggInfo.sortingIdx, addrEnd);
        sAggInfo.useSortingIdx = 1;
      }

      /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
      ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
      ** Then compare the current GROUP BY terms against the GROUP BY terms
      ** from the previous row currently stored in a0, a1, a2...
︙︙
Changes to test/select3.test.
︙︙
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.14 2005/09/08 10:37:01 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
do_test select3-1.0 {








|








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.15 2005/09/08 12:57:28 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
do_test select3-1.0 {
︙︙
216
217
218
219
220
221
222














223
224
225

} {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








>
>
>
>
>
>
>
>
>
>
>
>
>
>




216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239

} {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}

# Sometimes an aggregate query can return no rows at all.
#
do_test select3-7.1 {
  execsql {
    CREATE TABLE t2(a,b);
    INSERT INTO t2 VALUES(1,2);
    SELECT a, sum(b) FROM t2 WHERE b=5 GROUP BY a;
  }
} {}
do_test select3-7.2 {
  execsql {
    SELECT a, sum(b) FROM t2 WHERE b=5;
  }
} {{} 0}


finish_test