/ Check-in [a7f528ff]
Login

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

Overview
Comment:Add support for the TOTAL() aggregate function - works like SUM() except that it returns 0 instead of NULL when presented with an empty list. (CVS 2930)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a7f528ff3446d50b280fb0b85063879e3ac5751a
User & Date: drh 2006-01-12 22:17:50
Context
2006-01-13
01:17
Terminate the va_start in the getDigits function of date.c. (CVS 2931) check-in: 94eac140 user: drh tags: trunk
2006-01-12
22:17
Add support for the TOTAL() aggregate function - works like SUM() except that it returns 0 instead of NULL when presented with an empty list. (CVS 2930) check-in: a7f528ff user: drh tags: trunk
20:28
Performance boost in sqlite3VdbeRecordCompare. (CVS 2929) check-in: 14c42307 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/func.c.

    12     12   ** This file contains the C functions that implement various SQL
    13     13   ** functions of SQLite.  
    14     14   **
    15     15   ** There is only one exported symbol in this file - the function
    16     16   ** sqliteRegisterBuildinFunctions() found at the bottom of the file.
    17     17   ** All other code has file scope.
    18     18   **
    19         -** $Id: func.c,v 1.115 2006/01/09 16:12:05 danielk1977 Exp $
           19  +** $Id: func.c,v 1.116 2006/01/12 22:17:50 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   #include <ctype.h>
    23     23   /* #include <math.h> */
    24     24   #include <stdlib.h>
    25     25   #include <assert.h>
    26     26   #include "vdbeInt.h"
................................................................................
   819    819   struct SumCtx {
   820    820     double sum;     /* Sum of terms */
   821    821     int cnt;        /* Number of elements summed */
   822    822     u8 seenFloat;   /* True if there has been any floating point value */
   823    823   };
   824    824   
   825    825   /*
   826         -** Routines used to compute the sum or average.
          826  +** Routines used to compute the sum, average, and total.
          827  +**
          828  +** The SUM() function follows the (broken) SQL standard which means
          829  +** that it returns NULL if it sums over no inputs.  TOTAL returns
          830  +** 0.0 in that case.  In addition, TOTAL always returns a float where
          831  +** SUM might return an integer if it never encounters a floating point
          832  +** value.
   827    833   */
   828    834   static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
   829    835     SumCtx *p;
   830    836     int type;
   831    837     assert( argc==1 );
   832    838     p = sqlite3_aggregate_context(context, sizeof(*p));
   833    839     type = sqlite3_value_type(argv[0]);
................................................................................
   852    858   }
   853    859   static void avgFinalize(sqlite3_context *context){
   854    860     SumCtx *p;
   855    861     p = sqlite3_aggregate_context(context, 0);
   856    862     if( p && p->cnt>0 ){
   857    863       sqlite3_result_double(context, p->sum/(double)p->cnt);
   858    864     }
          865  +}
          866  +static void totalFinalize(sqlite3_context *context){
          867  +  SumCtx *p;
          868  +  p = sqlite3_aggregate_context(context, 0);
          869  +  sqlite3_result_double(context, p ? p->sum : 0.0);
   859    870   }
   860    871   
   861    872   /*
   862    873   ** An instance of the following structure holds the context of a
   863    874   ** variance or standard deviation computation.
   864    875   */
   865    876   typedef struct StdDevCtx StdDevCtx;
................................................................................
   996   1007       u8 needCollSeq;
   997   1008       void (*xStep)(sqlite3_context*,int,sqlite3_value**);
   998   1009       void (*xFinalize)(sqlite3_context*);
   999   1010     } aAggs[] = {
  1000   1011       { "min",    1, 0, 1, minmaxStep,   minMaxFinalize },
  1001   1012       { "max",    1, 2, 1, minmaxStep,   minMaxFinalize },
  1002   1013       { "sum",    1, 0, 0, sumStep,      sumFinalize    },
         1014  +    { "total",  1, 0, 0, sumStep,      totalFinalize    },
  1003   1015       { "avg",    1, 0, 0, sumStep,      avgFinalize    },
  1004   1016       { "count",  0, 0, 0, countStep,    countFinalize  },
  1005   1017       { "count",  1, 0, 0, countStep,    countFinalize  },
  1006   1018     };
  1007   1019     int i;
  1008   1020   
  1009   1021     for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){

Changes to test/null.test.

    97     97   #
    98     98   do_test null-3.1 {
    99     99     execsql {
   100    100       select count(*), count(b), count(c), sum(b), sum(c), 
   101    101              avg(b), avg(c), min(b), max(b) from t1;
   102    102     }
   103    103   } {7 4 6 2 3 0.5 0.5 0 1}
          104  +
          105  +# The sum of zero entries is a NULL, but the total of zero entries is 0.
          106  +#
          107  +do_test null-3.2 {
          108  +  execsql {
          109  +    SELECT sum(b), total(b) FROM t1 WHERE b<0
          110  +  }
          111  +} {{} 0.0}
   104    112   
   105    113   # Check to see how WHERE clauses handle NULL values.  A NULL value
   106    114   # is the same as UNKNOWN.  The WHERE clause should only select those
   107    115   # rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
   108    116   #
   109    117   do_test null-4.1 {
   110    118     execsql {

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the lang-*.html files.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.104 2006/01/04 15:58:29 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.105 2006/01/12 22:17:50 drh Exp $}
     5      5   source common.tcl
     6      6   
     7      7   if {[llength $argv]>0} {
     8      8     set outputdir [lindex $argv 0]
     9      9   } else {
    10     10     set outputdir ""
    11     11   }
................................................................................
  1391   1391   <td valign="top" align="right">min(<i>X</i>)</td>
  1392   1392   <td valign="top">Return the minimum non-NULL value of all values in the group.
  1393   1393   The usual sort order is used to determine the minimum.  NULL is only returned
  1394   1394   if all values in the group are NULL.</td>
  1395   1395   </tr>
  1396   1396   
  1397   1397   <tr>
  1398         -<td valign="top" align="right">sum(<i>X</i>)</td>
         1398  +<td valign="top" align="right">sum(<i>X</i>)<br>total(<i>X</i>)</td>
  1399   1399   <td valign="top">Return the numeric sum of all numeric values in the group.
  1400         -   If there are no input rows or all values are NULL, then NULL is returned.
         1400  +   If there are no input rows or all values are NULL, then sum() returns
         1401  +   NULL but total() returns zero.
  1401   1402      NULL is not a helpful result in that case (the correct answer should be
  1402         -   zero) but it is what the SQL standard requires and how 
  1403         -   most other SQL database engines operate so SQLite does it that way
  1404         -   in order to be compatible. 
  1405         -   You will probably want to use
  1406         -   "<b>coalesce(sum(</b>X<b>),0)</b>" instead of just "<b>sum(</b>X<b>)</b>"
         1403  +   zero) but the SQL standard requires that behavior from sum() and that is how 
         1404  +   most other SQL database engines implement sum() so SQLite does it that way
         1405  +   in order to be compatible.   The non-standard total() function is provided
         1406  +   as a convenient way
  1407   1407      to work around this design problem in the SQL language.</td>
  1408   1408   </tr>
  1409   1409   </table>
  1410   1410   }
  1411   1411   
  1412   1412   
  1413   1413   Section INSERT insert