/ Check-in [62818594]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment:SUM returns NULL when it has no inputs. Ticket #1413. (CVS 2678)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6281859425d39c11d82875301fefafad1f08416d
User & Date: drh 2005-09-08 20:37:43
Context
2005-09-09
01:32
Defend against disk I/O errors that happen during an sqlite3OsSeek(). (CVS 2679) check-in: 461e3a0a user: drh tags: trunk
2005-09-08
20:37
SUM returns NULL when it has no inputs. Ticket #1413. (CVS 2678) check-in: 62818594 user: drh tags: trunk
19:45
A SUM() of all NULLs returns NULL. A SUM() of nothing return 0. A SUM() of a mixture of NULLs and numbers returns the sum of the numbers. Ticket #1413. (CVS 2677) check-in: 2e6230ed user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/func.c. 

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
838
839
840
841
842
843
844
845
846
847
848
849
850

851
852
853
854
855
856
857

** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.109 2005/09/08 19:45:58 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"
................................................................................
      p->seenFloat = 1;
    }
  }
}
static void sumFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p==0 ){
    sqlite3_result_int(context, 0);
  }else if( p->seenFloat ){
    sqlite3_result_double(context, p->sum);
  }else if( p->cnt>0 ){
    sqlite3_result_int64(context, (i64)p->sum);

  }
}
static void avgFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    sqlite3_result_double(context, p->sum/(double)p->cnt);








|







 







|
<
|
|
|
|
>








12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
838
839
840
841
842
843
844
845

846
847
848
849
850
851
852
853
854
855
856
857

** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.110 2005/09/08 20:37:43 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"
................................................................................
      p->seenFloat = 1;
    }
  }
}
static void sumFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p && p->cnt>0 ){

    if( p->seenFloat ){
      sqlite3_result_double(context, p->sum);
    }else{
      sqlite3_result_int64(context, (i64)p->sum);
    }
  }
}
static void avgFinalize(sqlite3_context *context){
  SumCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p && p->cnt>0 ){
    sqlite3_result_double(context, p->sum/(double)p->cnt);

Changes to test/func.test. 

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530

#    May you find forgiveness for yourself and forgive others.
#    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 built-in functions.
#
# $Id: func.test,v 1.39 2005/09/08 19:45:58 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
................................................................................
do_test func-18.2 {
  execsql {
    INSERT INTO t5 VALUES(0.0);
    SELECT sum(x) FROM t5;
  }
} {9902.0}

# The sum of nothing is 0.  But the sum of all NULLs is NULL.
#
do_test func-18.3 {
  execsql {
    DELETE FROM t5;
    SELECT sum(x) FROM t5;
  }
} {0}
do_test func-18.4 {
  execsql {
    INSERT INTO t5 VALUES(NULL);
    SELECT sum(x) FROM t5
  }
} {{}}
do_test func-18.5 {








|







 







|






|








7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530

#    May you find forgiveness for yourself and forgive others.
#    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 built-in functions.
#
# $Id: func.test,v 1.40 2005/09/08 20:37:44 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
................................................................................
do_test func-18.2 {
  execsql {
    INSERT INTO t5 VALUES(0.0);
    SELECT sum(x) FROM t5;
  }
} {9902.0}

# The sum of nothing is NULL.  But the sum of all NULLs is NULL.
#
do_test func-18.3 {
  execsql {
    DELETE FROM t5;
    SELECT sum(x) FROM t5;
  }
} {{}}
do_test func-18.4 {
  execsql {
    INSERT INTO t5 VALUES(NULL);
    SELECT sum(x) FROM t5
  }
} {{}}
do_test func-18.5 {

Changes to test/select3.test. 

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
229
230
231
232
233
234
235
236
237
238
239

#    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 {
................................................................................
    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








|







 







|




8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
229
230
231
232
233
234
235
236
237
238
239

#    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.16 2005/09/08 20:37:44 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
    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;
  }
} {{} {}}


finish_test

Changes to test/select5.test. 

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

#    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: select5.test,v 1.12 2005/09/08 10:37:01 drh Exp $

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

# Build some test data
#
execsql {
................................................................................
    SELECT max(x) FROM t1 WHERE x>100
  }
} {{}}
do_test select5-4.5 {
  execsql {
    SELECT sum(x) FROM t1 WHERE x>100
  }
} {0}

# Some tests for queries with a GROUP BY clause but no aggregate functions.
#
# Note: The query in test case 5-5.5 are not legal SQL. So if the 
# implementation changes in the future and it returns different results,
# this is not such a big deal.
#








|







 







|








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

#    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: select5.test,v 1.13 2005/09/08 20:37:44 drh Exp $

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

# Build some test data
#
execsql {
................................................................................
    SELECT max(x) FROM t1 WHERE x>100
  }
} {{}}
do_test select5-4.5 {
  execsql {
    SELECT sum(x) FROM t1 WHERE x>100
  }
} {{}}

# Some tests for queries with a GROUP BY clause but no aggregate functions.
#
# Note: The query in test case 5-5.5 are not legal SQL. So if the 
# implementation changes in the future and it returns different results,
# this is not such a big deal.
#

Changes to test/trigger2.test. 

89
90
91
92
93
94
95
96

97
98
99
100
101
102
103
104

105
106
107
108
109
110
111
112
113

114
115
116
117
118
119
120
...
136
137
138
139
140
141
142
143

144
145
146
147
148
149
150
151

152
153
154
155
156
157
158
...
168
169
170
171
172
173
174
175

176
177
178
179
180
181
182
183

184
185
186
187
188
189
190

      CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
      CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
  
      CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 

  	  new.a, new.b);
      END;
  
      CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 

  	  new.a, new.b);
      END;
  
      CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
        WHEN old.a = 1
        BEGIN
        INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 
  	  old.a, old.b, 
  	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 

  	  new.a, new.b);
      END;
    }
  
    do_test trigger2-1.$ii.1 {
      set r {}
      foreach v [execsql { 
................................................................................
      DELETE FROM tbl;
      INSERT INTO tbl VALUES (100, 100);
      INSERT INTO tbl VALUES (300, 200);
      CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 

  	  0, 0);
      END;
  
      CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 

  	  0, 0);
      END;
    }
    do_test trigger2-1.$ii.2 {
      set r {}
      foreach v [execsql {
        DELETE FROM tbl;
................................................................................
  
    execsql {
      DELETE FROM rlog;
      CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  0, 0,
  	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 

  	  new.a, new.b);
      END;
  
      CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  0, 0,
  	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 

  	  new.a, new.b);
      END;
    }
    do_test trigger2-1.$ii.3 {
      execsql {
  
        CREATE TABLE other_tbl(a, b);








|
>







|
>








|
>







 







|
>







|
>







 







|
>







|
>








89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
...
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
...
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197

      CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
      CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
  
      CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
  
      CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
  
      CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
        WHEN old.a = 1
        BEGIN
        INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
    }
  
    do_test trigger2-1.$ii.1 {
      set r {}
      foreach v [execsql { 
................................................................................
      DELETE FROM tbl;
      INSERT INTO tbl VALUES (100, 100);
      INSERT INTO tbl VALUES (300, 200);
      CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  0, 0);
      END;
  
      CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  old.a, old.b, 
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  0, 0);
      END;
    }
    do_test trigger2-1.$ii.2 {
      set r {}
      foreach v [execsql {
        DELETE FROM tbl;
................................................................................
  
    execsql {
      DELETE FROM rlog;
      CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  0, 0,
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
  
      CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
        BEGIN
        INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
  	  0, 0,
  	  (SELECT coalesce(sum(a),0) FROM tbl),
          (SELECT coalesce(sum(b),0) FROM tbl), 
  	  new.a, new.b);
      END;
    }
    do_test trigger2-1.$ii.3 {
      execsql {
  
        CREATE TABLE other_tbl(a, b);

Changes to www/lang.tcl. 

1
2
3
4
5
6
7
8
9
10
11
....
1356
1357
1358
1359
1360
1361
1362
1363

1364
1365
1366
1367
1368
1369
1370
....
1381
1382
1383
1384
1385
1386
1387
1388








1389
1390
1391
1392
1393
1394
1395

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.98 2005/08/28 17:00:26 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}
................................................................................
aggregate functions written in C may be added using the 
<a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a>
API.</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>avg(<i>X</i>)</td>
<td valign="top">Return the average value of all <i>X</i> within a group.</td>

</tr>

<tr>
<td valign="top" align="right">count(<i>X</i>)<br>count(*)</td>
<td valign="top">The first form return a count of the number of times
that <i>X</i> is not NULL in a group.  The second form (with no argument)
returns the total number of rows in the group.</td>
................................................................................
<td valign="top">Return the minimum non-NULL value of all values in the group.
The usual sort order is used to determine the minimum.  NULL is only returned
if all values in the group are NULL.</td>
</tr>

<tr>
<td valign="top" align="right">sum(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all values in the group.</td>








</tr>
</table>
}


Section INSERT insert





|







 







|
>







 







|
>
>
>
>
>
>
>
>








1
2
3
4
5
6
7
8
9
10
11
....
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
....
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.99 2005/09/08 20:37:44 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}
................................................................................
aggregate functions written in C may be added using the 
<a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a>
API.</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>avg(<i>X</i>)</td>
<td valign="top">Return the average value of all non-NULL <i>X</i> within a
group.  Non-numeric values are interpreted as 0.</td>
</tr>

<tr>
<td valign="top" align="right">count(<i>X</i>)<br>count(*)</td>
<td valign="top">The first form return a count of the number of times
that <i>X</i> is not NULL in a group.  The second form (with no argument)
returns the total number of rows in the group.</td>
................................................................................
<td valign="top">Return the minimum non-NULL value of all values in the group.
The usual sort order is used to determine the minimum.  NULL is only returned
if all values in the group are NULL.</td>
</tr>

<tr>
<td valign="top" align="right">sum(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all numeric values in the group.
   If there are no input rows or all values are NULL, then NULL is returned.
   NULL is not a helpful result in that case (the correct answer should be
   zero) but it is what the SQL standard requires and how 
   most other SQL database engines operate so SQLite does it that way
   in order to be compatible. 
   You will probably want to use
   "<b>coalesce(sum(</b>X<b>),0)</b>" instead of just "<b>sum(</b>X<b>)</b>"
   to work around this design problem in the SQL language.</td>
</tr>
</table>
}


Section INSERT insert

This page was generated in about 0.014s by Fossil 2.11 [b833df21f7] 2020-01-28 15:33:15