/ Check-in [7d86749d]
Login

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

Overview
Comment:Change the IFNULL and NVL functions to COALESCE. Change MIN and MAX so that they require at least one argument. (CVS 404)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:7d86749d4a78d05930bae2b6491d9428f06fe836
User & Date: drh 2002-02-28 03:14:18
Context
2002-02-28
03:31
Remove unused opcodes from the VDBE and fix a bug in the substr() function. (CVS 405) check-in: e8611e0c user: drh tags: trunk
03:14
Change the IFNULL and NVL functions to COALESCE. Change MIN and MAX so that they require at least one argument. (CVS 404) check-in: 7d86749d user: drh tags: trunk
03:04
The new function code passes regression tests. (CVS 403) check-in: b00cf110 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
...
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179



180
181
182
183
184
185
186
...
347
348
349
350
351
352
353
354
355


356
357
358
359
360
361
362
363
364



365
366
367
368
369
370
371
** 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.9 2002/02/28 03:04:48 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"

................................................................................
  if( z==0 ) return;
  for(i=0; z[i]; i++){
    if( isupper(z[i]) ) z[i] = tolower(z[i]);
  }
}

/*
** Implementation of the IFNULL() and NVL() functions.  (both do the
** same thing.  They return their first argument if it is not NULL or
** their second argument if the first is NULL.
*/
static void ifnullFunc(sqlite_func *context, int argc, const char **argv){
  const char *z;
  assert( argc==2 );
  z = argv[0] ? argv[0] : argv[1];
  sqlite_set_result_string(context, z, -1);



}

/*
** An instance of the following structure holds the context of a
** sum() or avg() aggregate computation.
*/
typedef struct SumCtx SumCtx;
................................................................................
*/
void sqliteRegisterBuildinFunctions(sqlite *db){
  static struct {
     char *zName;
     int nArg;
     void (*xFunc)(sqlite_func*,int,const char**);
  } aFuncs[] = {
    { "min",  -1,  minFunc    },
    { "max",  -1,  maxFunc    },


    { "length", 1, lengthFunc },
    { "substr", 3, substrFunc },
    { "abs",    1, absFunc    },
    { "round",  1, roundFunc  },
    { "round",  2, roundFunc  },
    { "upper",  1, upperFunc  },
    { "lower",  1, lowerFunc  },
    { "ifnull", 2, ifnullFunc },
    { "nvl",    2, ifnullFunc },



  };
  static struct {
    char *zName;
    int nArg;
    void (*xStep)(sqlite_func*,int,const char**);
    void (*xFinalize)(sqlite_func*);
  } aAggs[] = {







|







 







|
|
|


|
|
|
|
>
>
>







 







|
|
>
>
|
|
|
|
|
|
|
|
<
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
...
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368

369
370
371
372
373
374
375
376
377
378
** 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.10 2002/02/28 03:14:18 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"

................................................................................
  if( z==0 ) return;
  for(i=0; z[i]; i++){
    if( isupper(z[i]) ) z[i] = tolower(z[i]);
  }
}

/*
** Implementation of the IFNULL(), NVL(), and COALESCE() functions.  
** All three do the same thing.  They return the first argument
** non-NULL argument.
*/
static void ifnullFunc(sqlite_func *context, int argc, const char **argv){
  int i;
  for(i=0; i<argc; i++){
    if( argv[i] ){
      sqlite_set_result_string(context, argv[i], -1);
      break;
    }
  }
}

/*
** An instance of the following structure holds the context of a
** sum() or avg() aggregate computation.
*/
typedef struct SumCtx SumCtx;
................................................................................
*/
void sqliteRegisterBuildinFunctions(sqlite *db){
  static struct {
     char *zName;
     int nArg;
     void (*xFunc)(sqlite_func*,int,const char**);
  } aFuncs[] = {
    { "min",       -1, minFunc    },
    { "min",        0, 0          },
    { "max",       -1, maxFunc    },
    { "max",        0, 0          },
    { "length",     1, lengthFunc },
    { "substr",     3, substrFunc },
    { "abs",        1, absFunc    },
    { "round",      1, roundFunc  },
    { "round",      2, roundFunc  },
    { "upper",      1, upperFunc  },
    { "lower",      1, lowerFunc  },
    { "coalesce",  -1, ifnullFunc },

    { "coalesce",   0, 0          },
    { "coalesce",   1, 0          },

  };
  static struct {
    char *zName;
    int nArg;
    void (*xStep)(sqlite_func*,int,const char**);
    void (*xFinalize)(sqlite_func*);
  } aAggs[] = {

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
#    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.20 2002/02/28 03:04:48 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
do_test select1-2.5 {
  set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg]
  lappend v $msg
} {0 3}
do_test select1-2.6 {
  set v [catch {execsql {SELECT min(*) FROM test1}} msg]
  lappend v $msg
} {0 {{} {}}}
do_test select1-2.7 {
  set v [catch {execsql {SELECT Min(f1) FROM test1}} msg]
  lappend v $msg
} {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.9 {
  set v [catch {execsql {SELECT MAX(*) FROM test1}} msg]
  lappend v $msg
} {0 {{} {}}}
do_test select1-2.10 {
  set v [catch {execsql {SELECT Max(f1) FROM test1}} msg]
  lappend v $msg
} {0 33}
do_test select1-2.11 {
  set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg]
  lappend v [lsort $msg]







|







 







|











|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
#    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.21 2002/02/28 03:14:18 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
do_test select1-2.5 {
  set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg]
  lappend v $msg
} {0 3}
do_test select1-2.6 {
  set v [catch {execsql {SELECT min(*) FROM test1}} msg]
  lappend v $msg
} {1 {wrong number of arguments to function min()}}
do_test select1-2.7 {
  set v [catch {execsql {SELECT Min(f1) FROM test1}} msg]
  lappend v $msg
} {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.9 {
  set v [catch {execsql {SELECT MAX(*) FROM test1}} msg]
  lappend v $msg
} {1 {wrong number of arguments to function MAX()}}
do_test select1-2.10 {
  set v [catch {execsql {SELECT Max(f1) FROM test1}} msg]
  lappend v $msg
} {0 33}
do_test select1-2.11 {
  set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg]
  lappend v [lsort $msg]