/ Check-in [223521c0]
Login

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

Overview
Comment:Min() and max() ignore NULL values. Ticket #800. (CVS 1803)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | version_2
Files: files | file ages | folders
SHA1: 223521c04e8ad39e06fee455f7dbb31ace2d3800
User & Date: drh 2004-07-18 21:14:05
Context
2004-07-18
22:25
The %W date specifier in strftime should be measured from the first Monday of the year. Ticket #758. (CVS 1806) check-in: 135e5447 user: drh tags: version_2
21:14
Min() and max() ignore NULL values. Ticket #800. (CVS 1803) check-in: 223521c0 user: drh tags: version_2
2004-07-17
21:59
Fix for ticket #812 in the 2.8 branch. (CVS 1801) check-in: ea364dff user: drh tags: version_2
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
...
513
514
515
516
517
518
519

520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
...
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
** 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.43.2.1 2004/06/23 21:16:52 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "os.h"
................................................................................
*/
static void minmaxStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  int (*xCompare)(const char*, const char*);
  int mask;    /* 0 for min() or 0xffffffff for max() */

  assert( argc==2 );

  if( argv[1][0]=='n' ){
    xCompare = sqliteCompare;
  }else{
    xCompare = strcmp;
  }
  mask = (int)sqlite_user_data(context);
  assert( mask==0 || mask==-1 );
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 ) return;
  if( mask==0 && p->zBuf[0]>=2 ) return;
  if( argv[0]==0 ){
    if( mask==0 ){
      p->zBuf[0] |= 2;
    }
    return;
  }
  assert( p->zBuf[0]<2 );
  if( p->z==0 || (xCompare(argv[0],p->z)^mask)<0 ){
    int len;
    if( p->zBuf[0]!=0 ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf)-1 ){
      p->z = &p->zBuf[1];
      p->zBuf[0] = 0;
    }else{
................................................................................
}
static void minMaxFinalize(sqlite_func *context){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && p->z && p->zBuf[0]<2 ){
    sqlite_set_result_string(context, p->z, strlen(p->z));
  }
  if( p && (p->zBuf[0]&1)!=0 ){
    sqliteFree(p->z);
  }
}

/*
** This function registered all of the above C functions as SQL
** functions.  This should be the only routine in this file with







|







 







>









<
<
<
<
<
<
<
<


|







 







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529








530
531
532
533
534
535
536
537
538
539
...
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
** 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.43.2.2 2004/07/18 21:14:05 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "os.h"
................................................................................
*/
static void minmaxStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  int (*xCompare)(const char*, const char*);
  int mask;    /* 0 for min() or 0xffffffff for max() */

  assert( argc==2 );
  if( argv[0]==0 ) return;  /* Ignore NULL values */
  if( argv[1][0]=='n' ){
    xCompare = sqliteCompare;
  }else{
    xCompare = strcmp;
  }
  mask = (int)sqlite_user_data(context);
  assert( mask==0 || mask==-1 );
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 ) return;








  if( p->z==0 || (xCompare(argv[0],p->z)^mask)<0 ){
    int len;
    if( p->zBuf[0] ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf)-1 ){
      p->z = &p->zBuf[1];
      p->zBuf[0] = 0;
    }else{
................................................................................
}
static void minMaxFinalize(sqlite_func *context){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && p->z && p->zBuf[0]<2 ){
    sqlite_set_result_string(context, p->z, strlen(p->z));
  }
  if( p && p->zBuf[0] ){
    sqliteFree(p->z);
  }
}

/*
** This function registered all of the above C functions as SQL
** functions.  This should be the only routine in this file with

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1912
1913
1914
1915
1916
1917
1918






1919
1920
1921
1922
1923
1924
1925
**    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.161 2004/03/13 14:00:36 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  }
  cont = sqliteVdbeMakeLabel(v);
  if( pIdx==0 ){
    sqliteVdbeAddOp(v, seekOp, base, 0);
  }else{
    sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
    sqliteVdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum, pIdx->zName, P3_STATIC);






    sqliteVdbeAddOp(v, seekOp, base+1, 0);
    sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
    sqliteVdbeAddOp(v, OP_Close, base+1, 0);
    sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
  }
  eList.nExpr = 1;
  memset(&eListItem, 0, sizeof(eListItem));







|







 







>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
**    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.161.2.1 2004/07/18 21:14:05 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  }
  cont = sqliteVdbeMakeLabel(v);
  if( pIdx==0 ){
    sqliteVdbeAddOp(v, seekOp, base, 0);
  }else{
    sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
    sqliteVdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum, pIdx->zName, P3_STATIC);
    if( seekOp==OP_Rewind ){
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeAddOp(v, OP_MakeKey, 1, 0);
      sqliteVdbeAddOp(v, OP_IncrKey, 0, 0);
      seekOp = OP_MoveTo;
    }
    sqliteVdbeAddOp(v, seekOp, base+1, 0);
    sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
    sqliteVdbeAddOp(v, OP_Close, base+1, 0);
    sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
  }
  eList.nExpr = 1;
  memset(&eListItem, 0, sizeof(eListItem));

Changes to test/func.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
#    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.16.2.1 2004/06/23 21:16:52 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
................................................................................

# Tests for aggregate functions and how they handle NULLs.
#
do_test func-8.1 {
  execsql {
    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
  }
} {68236 3 22745.33 {} 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.3 {
  execsql {
    CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
    SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {{}}
do_test func-8.4 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

# How do you test the random() function in a meaningful, deterministic way?







|







 







|










|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
#    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.16.2.2 2004/07/18 21:14:05 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
................................................................................

# Tests for aggregate functions and how they handle NULLs.
#
do_test func-8.1 {
  execsql {
    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
  }
} {68236 3 22745.33 1 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.3 {
  execsql {
    CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
    SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.4 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

# How do you test the random() function in a meaningful, deterministic way?

Changes to test/minmax.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
...
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307




308















































309
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.9.2.1 2004/06/23 21:16:52 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test minmax-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax-1.6 {
  set sqlite_search_count
} {1}
do_test minmax-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax-1.8 {
  set sqlite_search_count
} {1}
................................................................................
} {0}

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {-1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 2 5 3 22 4 92 5 90 6 0}
................................................................................
  execsql {
    SELECT max(rowid) FROM (
      SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
    )
  }
} {{}}

# If there is a NULL in an aggregate max(), ignore it.  If a NULL
# occurs in an aggregate min(), then the result will be NULL because
# NULL compares less than all other values.
#
do_test minmax-10.1 {
  execsql {
    CREATE TABLE t6(x);
    INSERT INTO t6 VALUES(1);
    INSERT INTO t6 VALUES(2);
    INSERT INTO t6 VALUES(NULL);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {-1}
do_test minmax-10.2 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}
do_test minmax-10.3 {
  execsql {
    CREATE INDEX i6 ON t6(x);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {-1}
do_test minmax-10.4 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}




















































finish_test







|







 







|







 







|







 







|
|
<









|










|





>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
...
272
273
274
275
276
277
278
279
280

281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.9.2.2 2004/07/18 21:14:05 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test minmax-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax-1.6 {
  set sqlite_search_count
} {2}
do_test minmax-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax-1.8 {
  set sqlite_search_count
} {1}
................................................................................
} {0}

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 2 5 3 22 4 92 5 90 6 0}
................................................................................
  execsql {
    SELECT max(rowid) FROM (
      SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
    )
  }
} {{}}

# If there is a NULL in an aggregate max() or min(), ignore it.  An
# aggregate min() or max() will only return NULL if all values are NULL.

#
do_test minmax-10.1 {
  execsql {
    CREATE TABLE t6(x);
    INSERT INTO t6 VALUES(1);
    INSERT INTO t6 VALUES(2);
    INSERT INTO t6 VALUES(NULL);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {1}
do_test minmax-10.2 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}
do_test minmax-10.3 {
  execsql {
    CREATE INDEX i6 ON t6(x);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {1}
do_test minmax-10.4 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}
do_test minmax-10.5 {
  execsql {
    DELETE FROM t6 WHERE x NOT NULL;
    SELECT count(*) FROM t6;
  }
} 1
do_test minmax-10.6 {
  execsql {
    SELECT count(x) FROM t6;
  }
} 0
do_test minmax-10.7 {
  execsql {
    SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
  }
} {{} {}}
do_test minmax-10.8 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}
do_test minmax-10.9 {
  execsql {
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    INSERT INTO t6 SELECT * FROM t6;
    SELECT count(*) FROM t6;
  }
} 1024
do_test minmax-10.10 {
  execsql {
    SELECT count(x) FROM t6;
  }
} 0
do_test minmax-10.11 {
  execsql {
    SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
  }
} {{} {}}
do_test minmax-10.12 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}

finish_test

Changes to test/null.test.

97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
# (except for min().)
#
do_test null-3.1 {
  execsql {
    select count(*), count(b), count(c), sum(b), sum(c), 
           avg(b), avg(c), min(b), max(b) from t1;
  }
} {7 4 6 2 3 0.5 0.5 {} 1}

# Check to see how WHERE clauses handle NULL values.  A NULL value
# is the same as UNKNOWN.  The WHERE clause should only select those
# rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
#
do_test null-4.1 {
  execsql {







|







97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
# (except for min().)
#
do_test null-3.1 {
  execsql {
    select count(*), count(b), count(c), sum(b), sum(c), 
           avg(b), avg(c), min(b), max(b) from t1;
  }
} {7 4 6 2 3 0.5 0.5 0 1}

# Check to see how WHERE clauses handle NULL values.  A NULL value
# is the same as UNKNOWN.  The WHERE clause should only select those
# rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
#
do_test null-4.1 {
  execsql {

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
#    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 the SELECT statement.
#
# $Id: select1.test,v 1.30.2.1 2004/06/23 21:16:52 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
} {0 11}
do_test select1-2.8 {
  set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
  lappend v [lsort $msg]
} {0 {11 33}}
do_test select1-2.8.1 {
  execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
} {xyzzy}
do_test select1-2.8.2 {
  execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
} {11}
do_test select1-2.8.3 {
  execsql {SELECT min(b), min(b) FROM t4}
} [list $long $long]
do_test select1-2.9 {







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
#    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 the SELECT statement.
#
# $Id: select1.test,v 1.30.2.2 2004/07/18 21:14:05 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
} {0 11}
do_test select1-2.8 {
  set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
  lappend v [lsort $msg]
} {0 {11 33}}
do_test select1-2.8.1 {
  execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
} {11}
do_test select1-2.8.2 {
  execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
} {11}
do_test select1-2.8.3 {
  execsql {SELECT min(b), min(b) FROM t4}
} [list $long $long]
do_test select1-2.9 {