/ Check-in [78ced6e3]
Login

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

Overview
Comment:The MIN() aggregate function returns NULL if any element in the result was NULL. This makes MIN() consistent with ORDER BY which sorts NULL first. Ticket #777. (CVS 1679)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | version_2
Files: files | file ages | folders
SHA1: 78ced6e3092d69e7cb77c5c2acff70f3c92e6523
User & Date: drh 2004-06-23 21:16:52
Context
2004-06-26
14:40
Fold in changes submitted by the PHP developers. (CVS 1742) check-in: a4a25708 user: drh tags: version_2
2004-06-23
21:16
The MIN() aggregate function returns NULL if any element in the result was NULL. This makes MIN() consistent with ORDER BY which sorts NULL first. Ticket #777. (CVS 1679) check-in: 78ced6e3 user: drh tags: version_2
2004-06-19
11:57
Add the source code to the sqlite_analyzer utility. (CVS 1637) check-in: 06bf4c7e user: drh tags: version_2
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.43 2004/02/25 22:51:06 rdc Exp $
           19  +** $Id: func.c,v 1.43.2.1 2004/06/23 21:16:52 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 "os.h"
................................................................................
   519    519     assert( argc==2 );
   520    520     if( argv[1][0]=='n' ){
   521    521       xCompare = sqliteCompare;
   522    522     }else{
   523    523       xCompare = strcmp;
   524    524     }
   525    525     mask = (int)sqlite_user_data(context);
          526  +  assert( mask==0 || mask==-1 );
   526    527     p = sqlite_aggregate_context(context, sizeof(*p));
   527         -  if( p==0 || argc<1 || argv[0]==0 ) return;
          528  +  if( p==0 || argc<1 ) return;
          529  +  if( mask==0 && p->zBuf[0]>=2 ) return;
          530  +  if( argv[0]==0 ){
          531  +    if( mask==0 ){
          532  +      p->zBuf[0] |= 2;
          533  +    }
          534  +    return;
          535  +  }
          536  +  assert( p->zBuf[0]<2 );
   528    537     if( p->z==0 || (xCompare(argv[0],p->z)^mask)<0 ){
   529    538       int len;
   530         -    if( !p->zBuf[0] ){
          539  +    if( p->zBuf[0]!=0 ){
   531    540         sqliteFree(p->z);
   532    541       }
   533    542       len = strlen(argv[0]);
   534    543       if( len < sizeof(p->zBuf)-1 ){
   535    544         p->z = &p->zBuf[1];
   536         -      p->zBuf[0] = 1;
          545  +      p->zBuf[0] = 0;
   537    546       }else{
   538    547         p->z = sqliteMalloc( len+1 );
   539         -      p->zBuf[0] = 0;
          548  +      p->zBuf[0] = 1;
   540    549         if( p->z==0 ) return;
   541    550       }
   542    551       strcpy(p->z, argv[0]);
   543    552     }
   544    553   }
   545    554   static void minMaxFinalize(sqlite_func *context){
   546    555     MinMaxCtx *p;
   547    556     p = sqlite_aggregate_context(context, sizeof(*p));
   548         -  if( p && p->z ){
          557  +  if( p && p->z && p->zBuf[0]<2 ){
   549    558       sqlite_set_result_string(context, p->z, strlen(p->z));
   550    559     }
   551         -  if( p && !p->zBuf[0] ){
          560  +  if( p && (p->zBuf[0]&1)!=0 ){
   552    561       sqliteFree(p->z);
   553    562     }
   554    563   }
   555    564   
   556    565   /*
   557    566   ** This function registered all of the above C functions as SQL
   558    567   ** functions.  This should be the only routine in this file with
................................................................................
   617    626       { "stddev", 1, SQLITE_NUMERIC, 0, stdDevStep,   stdDevFinalize },
   618    627   #endif
   619    628     };
   620    629     static const char *azTypeFuncs[] = { "min", "max", "typeof" };
   621    630     int i;
   622    631   
   623    632     for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
   624         -    void *pArg = aFuncs[i].argType==2 ? (void*)(-1) : db;
          633  +    void *pArg;
          634  +    switch( aFuncs[i].argType ){
          635  +      case 0:  pArg = 0;           break;
          636  +      case 1:  pArg = db;          break;
          637  +      case 2:  pArg = (void*)(-1); break;
          638  +    }
   625    639       sqlite_create_function(db, aFuncs[i].zName,
   626    640              aFuncs[i].nArg, aFuncs[i].xFunc, pArg);
   627    641       if( aFuncs[i].xFunc ){
   628    642         sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
   629    643       }
   630    644     }
   631    645     for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
   632         -    void *pArg = aAggs[i].argType==2 ? (void*)(-1) : db;
          646  +    void *pArg;
          647  +    switch( aAggs[i].argType ){
          648  +      case 0:  pArg = 0;           break;
          649  +      case 1:  pArg = db;          break;
          650  +      case 2:  pArg = (void*)(-1); break;
          651  +    }
   633    652       sqlite_create_aggregate(db, aAggs[i].zName,
   634    653              aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, pArg);
   635    654       sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);
   636    655     }
   637    656     for(i=0; i<sizeof(azTypeFuncs)/sizeof(azTypeFuncs[0]); i++){
   638    657       int n = strlen(azTypeFuncs[i]);
   639    658       FuncDef *p = sqliteHashFind(&db->aFunc, azTypeFuncs[i], n);

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.16 2002/11/04 19:32:26 drh Exp $
           14  +# $Id: func.test,v 1.16.2.1 2004/06/23 21:16:52 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 {
................................................................................
   248    248   
   249    249   # Tests for aggregate functions and how they handle NULLs.
   250    250   #
   251    251   do_test func-8.1 {
   252    252     execsql {
   253    253       SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
   254    254     }
   255         -} {68236 3 22745.33 1 67890 5}
          255  +} {68236 3 22745.33 {} 67890 5}
   256    256   do_test func-8.2 {
   257    257     execsql {
   258    258       SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
   259    259     }
   260    260   } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
   261    261   do_test func-8.3 {
   262    262     execsql {
   263    263       CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
   264    264       SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
   265    265     }
   266         -} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
          266  +} {{}}
          267  +do_test func-8.4 {
          268  +  execsql {
          269  +    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
          270  +  }
          271  +} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
   267    272   
   268    273   # How do you test the random() function in a meaningful, deterministic way?
   269    274   #
   270    275   do_test func-9.1 {
   271    276     execsql {
   272    277       SELECT random() is not null;
   273    278     }

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.9 2004/03/13 14:00:37 drh Exp $
           16  +# $Id: minmax.test,v 1.9.2.1 2004/06/23 21:16:52 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}
................................................................................
   271    271   do_test minmax-9.2 {
   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  +
          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.
          282  +#
          283  +do_test minmax-10.1 {
          284  +  execsql {
          285  +    CREATE TABLE t6(x);
          286  +    INSERT INTO t6 VALUES(1);
          287  +    INSERT INTO t6 VALUES(2);
          288  +    INSERT INTO t6 VALUES(NULL);
          289  +    SELECT coalesce(min(x),-1) FROM t6;
          290  +  }
          291  +} {-1}
          292  +do_test minmax-10.2 {
          293  +  execsql {
          294  +    SELECT max(x) FROM t6;
          295  +  }
          296  +} {2}
          297  +do_test minmax-10.3 {
          298  +  execsql {
          299  +    CREATE INDEX i6 ON t6(x);
          300  +    SELECT coalesce(min(x),-1) FROM t6;
          301  +  }
          302  +} {-1}
          303  +do_test minmax-10.4 {
          304  +  execsql {
          305  +    SELECT max(x) FROM t6;
          306  +  }
          307  +} {2}
   278    308   
   279    309   finish_test

Changes to test/null.test.

    90     90   do_test null-2.8 {
    91     91     execsql {
    92     92       select ifnull(case c when b then 1 else 0 end, 99) from t1;
    93     93     }
    94     94   } {1 0 0 1 0 0 0}
    95     95   
    96     96   # Check to see that NULL values are ignored in aggregate functions.
           97  +# (except for min().)
    97     98   #
    98     99   do_test null-3.1 {
    99    100     execsql {
   100    101       select count(*), count(b), count(c), sum(b), sum(c), 
   101    102              avg(b), avg(c), min(b), max(b) from t1;
   102    103     }
   103         -} {7 4 6 2 3 0.5 0.5 0 1}
          104  +} {7 4 6 2 3 0.5 0.5 {} 1}
   104    105   
   105    106   # Check to see how WHERE clauses handle NULL values.  A NULL value
   106    107   # is the same as UNKNOWN.  The WHERE clause should only select those
   107    108   # rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
   108    109   #
   109    110   do_test null-4.1 {
   110    111     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.30 2002/06/02 16:09:03 drh Exp $
           14  +# $Id: select1.test,v 1.30.2.1 2004/06/23 21:16:52 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         -} {11}
          162  +} {xyzzy}
   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 {