/ Check-in [166234a2]
Login

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

Overview
Comment:min() ignores NULL values. Ticket #800. (CVS 1802)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 166234a2b61e1d6a501e48dde1caec0a02bec90b
User & Date: drh 2004-07-18 20:52:32
Context
2004-07-18
21:33
An improved fix for the min() problem of ticket #800. (CVS 1804) check-in: b6e8b7a1 user: drh tags: trunk
20:52
min() ignores NULL values. Ticket #800. (CVS 1802) check-in: 166234a2 user: drh tags: trunk
2004-07-17
21:56
mprintf() correctly handles "%s","". Fix for ticket #812. (CVS 1800) check-in: 4f56db11 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes 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.77 2004/06/28 13:09:11 danielk1977 Exp $
           19  +** $Id: func.c,v 1.78 2004/07/18 20:52:32 drh Exp $
    20     20   */
    21     21   #include <ctype.h>
    22     22   #include <math.h>
    23     23   #include <stdlib.h>
    24     24   #include <assert.h>
    25     25   #include "sqliteInt.h"
    26     26   #include "vdbeInt.h"
................................................................................
   975    975     char zBuf[28];   /* Space that can be used for storage */
   976    976   };
   977    977   
   978    978   /*
   979    979   ** Routines to implement min() and max() aggregate functions.
   980    980   */
   981    981   static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv){
   982         -  int max = 0;
   983         -  int cmp = 0;
   984    982     Mem *pArg  = (Mem *)argv[0];
   985         -  Mem *pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
          983  +  Mem *pBest;
          984  +
          985  +  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
          986  +  pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
   986    987     if( !pBest ) return;
   987    988   
   988    989     if( pBest->flags ){
          990  +    int max;
          991  +    int cmp;
   989    992       CollSeq *pColl = sqlite3GetFuncCollSeq(context);
   990    993       /* This step function is used for both the min() and max() aggregates,
   991    994       ** the only difference between the two being that the sense of the
   992    995       ** comparison is inverted. For the max() aggregate, the
   993    996       ** sqlite3_user_data() function returns (void *)-1. For min() it
   994    997       ** returns (void *)db, where db is the sqlite3* database pointer.
   995    998       ** Therefore the next statement sets variable 'max' to 1 for the max()

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.195 2004/06/21 10:45:09 danielk1977 Exp $
           15  +** $Id: select.c,v 1.196 2004/07/18 20:52:32 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Allocate a new Select structure and return a pointer to that
    22     22   ** structure.
................................................................................
  2072   2072     if( pIdx==0 ){
  2073   2073       sqlite3VdbeAddOp(v, seekOp, base, 0);
  2074   2074     }else{
  2075   2075       sqlite3VdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
  2076   2076       sqlite3VdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum,
  2077   2077                      (char*)&pIdx->keyInfo, P3_KEYINFO);
  2078   2078       sqlite3VdbeAddOp(v, seekOp, base+1, 0);
         2079  +    if( seekOp==OP_Rewind ){
         2080  +      int addr;
         2081  +      sqlite3VdbeAddOp(v, OP_SetNumColumns, base+1, pIdx->nColumn+1);
         2082  +      sqlite3VdbeAddOp(v, OP_KeyAsData, base+1, 1);
         2083  +      addr = sqlite3VdbeAddOp(v, OP_IdxColumn, base+1, 0);
         2084  +      sqlite3VdbeAddOp(v, OP_NotNull, 1, addr+3);
         2085  +      sqlite3VdbeAddOp(v, OP_Next, 1, addr);
         2086  +    }
  2079   2087       sqlite3VdbeAddOp(v, OP_IdxRecno, base+1, 0);
  2080   2088       sqlite3VdbeAddOp(v, OP_Close, base+1, 0);
  2081   2089       sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
  2082   2090     }
  2083   2091     eList.nExpr = 1;
  2084   2092     memset(&eListItem, 0, sizeof(eListItem));
  2085   2093     eList.a = &eListItem;

Changes to test/func.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing built-in functions.
    13     13   #
    14         -# $Id: func.test,v 1.27 2004/06/29 13:18:24 danielk1977 Exp $
           14  +# $Id: func.test,v 1.28 2004/07/18 20:52:32 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Create a table to work with.
    20     20   #
    21     21   do_test func-0.0 {
................................................................................
   247    247   
   248    248   # Tests for aggregate functions and how they handle NULLs.
   249    249   #
   250    250   do_test func-8.1 {
   251    251     execsql {
   252    252       SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
   253    253     }
   254         -} {68236 3 22745.33 {} 67890 5}
          254  +} {68236 3 22745.33 1 67890 5}
   255    255   do_test func-8.2 {
   256    256     execsql {
   257    257       SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
   258    258     }
   259    259   } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
   260    260   do_test func-8.3 {
   261    261     execsql {
   262    262       CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
   263    263       SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
   264    264     }
   265         -} {{}}
          265  +} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
   266    266   do_test func-8.4 {
   267    267     execsql {
   268    268       SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
   269    269     }
   270    270   } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
   271    271   
   272    272   # How do you test the random() function in a meaningful, deterministic way?

Changes to test/minmax.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing SELECT statements that contain
    13     13   # aggregate min() and max() functions and which are handled as
    14     14   # as a special case.
    15     15   #
    16         -# $Id: minmax.test,v 1.10 2004/06/24 00:20:05 danielk1977 Exp $
           16  +# $Id: minmax.test,v 1.11 2004/07/18 20:52:32 drh Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   do_test minmax-1.0 {
    22     22     execsql {
    23     23       BEGIN;
................................................................................
   123    123   } {0}
   124    124   
   125    125   do_test minmax-4.1 {
   126    126     execsql {
   127    127       SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
   128    128         (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
   129    129     }
   130         -} {-1 20}
          130  +} {1 20}
   131    131   do_test minmax-4.2 {
   132    132     execsql {
   133    133       SELECT y, sum(x) FROM
   134    134         (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
   135    135       GROUP BY y ORDER BY y;
   136    136     }
   137    137   } {1 1 2 5 3 22 4 92 5 90 6 0}
................................................................................
   272    272     execsql {
   273    273       SELECT max(rowid) FROM (
   274    274         SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
   275    275       )
   276    276     }
   277    277   } {{}}
   278    278   
   279         -# If there is a NULL in an aggregate max(), ignore it.  If a NULL
   280         -# occurs in an aggregate min(), then the result will be NULL because
   281         -# NULL compares less than all other values.
          279  +# If there is a NULL in an aggregate max() or min(), ignore it.  An
          280  +# aggregate min() or max() will only return NULL if all values are NULL.
   282    281   #
   283    282   do_test minmax-10.1 {
   284    283     execsql {
   285    284       CREATE TABLE t6(x);
   286    285       INSERT INTO t6 VALUES(1);
   287    286       INSERT INTO t6 VALUES(2);
   288    287       INSERT INTO t6 VALUES(NULL);
   289    288       SELECT coalesce(min(x),-1) FROM t6;
   290    289     }
   291         -} {-1}
          290  +} {1}
   292    291   do_test minmax-10.2 {
   293    292     execsql {
   294    293       SELECT max(x) FROM t6;
   295    294     }
   296    295   } {2}
   297    296   do_test minmax-10.3 {
   298    297     execsql {
   299    298       CREATE INDEX i6 ON t6(x);
   300    299       SELECT coalesce(min(x),-1) FROM t6;
   301    300     }
   302         -} {-1}
          301  +} {1}
   303    302   do_test minmax-10.4 {
   304    303     execsql {
   305    304       SELECT max(x) FROM t6;
   306    305     }
   307    306   } {2}
          307  +do_test minmax-10.5 {
          308  +  execsql {
          309  +    DELETE FROM t6 WHERE x NOT NULL;
          310  +    SELECT count(*) FROM t6;
          311  +  }
          312  +} 1
          313  +do_test minmax-10.6 {
          314  +  execsql {
          315  +    SELECT count(x) FROM t6;
          316  +  }
          317  +} 0
          318  +do_test minmax-10.7 {
          319  +  execsql {
          320  +    SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
          321  +  }
          322  +} {{} {}}
          323  +do_test minmax-10.8 {
          324  +  execsql {
          325  +    SELECT min(x), max(x) FROM t6;
          326  +  }
          327  +} {{} {}}
          328  +do_test minmax-10.9 {
          329  +  execsql {
          330  +    INSERT INTO t6 SELECT * FROM t6;
          331  +    INSERT INTO t6 SELECT * FROM t6;
          332  +    INSERT INTO t6 SELECT * FROM t6;
          333  +    INSERT INTO t6 SELECT * FROM t6;
          334  +    INSERT INTO t6 SELECT * FROM t6;
          335  +    INSERT INTO t6 SELECT * FROM t6;
          336  +    INSERT INTO t6 SELECT * FROM t6;
          337  +    INSERT INTO t6 SELECT * FROM t6;
          338  +    INSERT INTO t6 SELECT * FROM t6;
          339  +    INSERT INTO t6 SELECT * FROM t6;
          340  +    SELECT count(*) FROM t6;
          341  +  }
          342  +} 1024
          343  +do_test minmax-10.10 {
          344  +  execsql {
          345  +    SELECT count(x) FROM t6;
          346  +  }
          347  +} 0
          348  +do_test minmax-10.11 {
          349  +  execsql {
          350  +    SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
          351  +  }
          352  +} {{} {}}
          353  +do_test minmax-10.12 {
          354  +  execsql {
          355  +    SELECT min(x), max(x) FROM t6;
          356  +  }
          357  +} {{} {}}
          358  +
   308    359   
   309    360   finish_test

Changes to test/null.test.

    96     96   # Check to see that NULL values are ignored in aggregate functions.
    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         -} {7 4 6 2 3 0.5 0.5 {} 1}
          103  +} {7 4 6 2 3 0.5 0.5 0 1}
   104    104   
   105    105   # Check to see how WHERE clauses handle NULL values.  A NULL value
   106    106   # is the same as UNKNOWN.  The WHERE clause should only select those
   107    107   # rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
   108    108   #
   109    109   do_test null-4.1 {
   110    110     execsql {

Changes to test/select1.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the SELECT statement.
    13     13   #
    14         -# $Id: select1.test,v 1.33 2004/06/24 00:20:05 danielk1977 Exp $
           14  +# $Id: select1.test,v 1.34 2004/07/18 20:52:32 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Try to select on a non-existant table.
    20     20   #
    21     21   do_test select1-1.1 {
................................................................................
   155    155   } {0 11}
   156    156   do_test select1-2.8 {
   157    157     set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
   158    158     lappend v [lsort $msg]
   159    159   } {0 {11 33}}
   160    160   do_test select1-2.8.1 {
   161    161     execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
   162         -} {xyzzy}
          162  +} {11}
   163    163   do_test select1-2.8.2 {
   164    164     execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
   165    165   } {11}
   166    166   do_test select1-2.8.3 {
   167    167     execsql {SELECT min(b), min(b) FROM t4}
   168    168   } [list $long $long]
   169    169   do_test select1-2.9 {

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.70 2004/06/18 11:25:21 danielk1977 Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.71 2004/07/18 20:52:32 drh Exp $}
     5      5   source common.tcl
     6      6   header {Query Language Understood by SQLite}
     7      7   puts {
     8      8   <h2>SQL As Understood By SQLite</h2>
     9      9   
    10     10   <p>The SQLite library understands most of the standard SQL
    11     11   language.  But it does <a href="omitted.html">omit some features</a>
................................................................................
  1088   1088   <td valign="top" align="right">max(<i>X</i>)</td>
  1089   1089   <td valign="top">Return the maximum value of all values in the group.
  1090   1090   The usual sort order is used to determine the maximum.</td>
  1091   1091   </tr>
  1092   1092   
  1093   1093   <tr>
  1094   1094   <td valign="top" align="right">min(<i>X</i>)</td>
  1095         -<td valign="top">Return the minimum value of all values in the group.
  1096         -The usual sort order is used to determine the minimum.</td>
         1095  +<td valign="top">Return the minimum non-NULL value of all values in the group.
         1096  +The usual sort order is used to determine the minimum.  NULL is only returned
         1097  +if all values in the group are NULL.</td>
  1097   1098   </tr>
  1098   1099   
  1099   1100   <tr>
  1100   1101   <td valign="top" align="right">sum(<i>X</i>)</td>
  1101   1102   <td valign="top">Return the numeric sum of all values in the group.</td>
  1102   1103   </tr>
  1103   1104   </table>