/ Check-in [46ce1a9a]
Login
Home Timeline Branches Tags Tickets Wiki More...
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Change functions to handle NULLs correctly. Added the NULLIF() function. (CVS 590)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 46ce1a9ab6e527958ebc757fa2175c488299cd84
User & Date: drh 2002-05-26 21:34:58
Context
2002-05-26
23:24
Require the INSTEAD OF syntax to create triggers on database views. (CVS 591) check-in: d9e48cd5 user: danielk1977 tags: trunk
21:34
Change functions to handle NULLs correctly. Added the NULLIF() function. (CVS 590) check-in: 46ce1a9a user: drh tags: trunk
20:54
NULL values are distinct. A comparison involving a NULL is always false. Operations on a NULL value yield a NULL result. This change makes SQLite operate more like the SQL spec, but it may break existing applications that assumed the old behavior. All the old tests pass but we still need to add new tests to better verify the new behavior. Fix for ticket #44. (CVS 589) check-in: 90511737 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
..
29
30
31
32
33
34
35

36

37
38
39
40
41
42
43
44
45
46
47
48

49

50
51
52
53
54
55
56
..
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

85
86
87
88
89
90
91
92
...
138
139
140
141
142
143
144

145
146
147
148
149
150
151
152
153
154
155
...
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231

232
233











234
235
236
237
238
239
240
...
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
360
361
362
363
364
365
366
367
368
369




370

371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
...
420
421
422
423
424
425
426

427
428
429
430
431
432
433

** 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.16 2002/04/20 14:24:42 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"

................................................................................
*/
static void minFunc(sqlite_func *context, int argc, const char **argv){
  const char *zBest; 
  int i;

  if( argc==0 ) return;
  zBest = argv[0];

  for(i=1; i<argc; i++){

    if( sqliteCompare(argv[i], zBest)<0 ){
      zBest = argv[i];
    }
  }
  sqlite_set_result_string(context, zBest, -1);
}
static void maxFunc(sqlite_func *context, int argc, const char **argv){
  const char *zBest; 
  int i;

  if( argc==0 ) return;
  zBest = argv[0];

  for(i=1; i<argc; i++){

    if( sqliteCompare(argv[i], zBest)>0 ){
      zBest = argv[i];
    }
  }
  sqlite_set_result_string(context, zBest, -1);
}

................................................................................
*/
static void lengthFunc(sqlite_func *context, int argc, const char **argv){
  const char *z;
  int len;

  assert( argc==1 );
  z = argv[0];
  if( z==0 ){
    len = 0;
  }else{
#ifdef SQLITE_UTF8
    for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; }
#else
    len = strlen(z);
#endif
  }
  sqlite_set_result_int(context, len);
}

/*
** Implementation of the abs() function
*/
static void absFunc(sqlite_func *context, int argc, const char **argv){
  const char *z;
  assert( argc==1 );
  z = argv[0];

  if( z && z[0]=='-' && isdigit(z[1]) ) z++;
  sqlite_set_result_string(context, z, -1);
}

/*
** Implementation of the substr() function
*/
static void substrFunc(sqlite_func *context, int argc, const char **argv){
................................................................................
** Implementation of the round() function
*/
static void roundFunc(sqlite_func *context, int argc, const char **argv){
  int n;
  double r;
  char zBuf[100];
  assert( argc==1 || argc==2 );

  n = argc==2 && argv[1] ? atoi(argv[1]) : 0;
  if( n>30 ) n = 30;
  if( n<0 ) n = 0;
  r = argv[0] ? atof(argv[0]) : 0.0;
  sprintf(zBuf,"%.*f",n,r);
  sqlite_set_result_string(context, zBuf, -1);
}

/*
** Implementation of the upper() and lower() SQL functions.
*/
................................................................................
** string and the second argument is the pattern.  So, the SQL statements:
**
**       A LIKE B
**
** is implemented as like(A,B).
*/
static void likeFunc(sqlite_func *context, int arg, const char **argv){
  sqlite_set_result_int(context, 
     sqliteLikeCompare(argv[0] ? argv[0] : "",argv[1] ? argv[1] : ""));
}

/*
** Implementation of the glob() SQL function.  This function implements
** the build-in GLOB operator.  The first argument to the function is the
** string and the second argument is the pattern.  So, the SQL statements:
**
**       A GLOB B
**
** is implemented as glob(A,B).
*/
static void globFunc(sqlite_func *context, int arg, const char **argv){

  sqlite_set_result_int(context,
    sqliteGlobCompare(argv[0] ? argv[0] : "",argv[1] ? argv[1] : ""));











}

/*
** An instance of the following structure holds the context of a
** sum() or avg() aggregate computation.
*/
typedef struct SumCtx SumCtx;
................................................................................

/*
** This function tracks state information for the min() and max()
** aggregate functions.
*/
typedef struct MinMaxCtx MinMaxCtx;
struct MinMaxCtx {

  char *z;         /* The best so far */
  char zBuf[28];   /* Space that can be used for storage */
};

/*
** Routines to implement min() and max() aggregate functions.
*/
static void minStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 ) return;




  if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)<0 ){

    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }
    if( argv[0] ){
      int len = strlen(argv[0]);
      if( len < sizeof(p->zBuf) ){
        p->z = p->zBuf;
      }else{
        p->z = sqliteMalloc( len+1 );
        if( p->z==0 ) return;
      }
      strcpy(p->z, argv[0]);
    }else{
      p->z = 0;
    }
  }
}
static void maxStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 ) return;




  if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)>0 ){

    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }
    if( argv[0] ){
      int len = strlen(argv[0]);
      if( len < sizeof(p->zBuf) ){
        p->z = p->zBuf;
      }else{
        p->z = sqliteMalloc( len+1 );
        if( p->z==0 ) return;
      }
      strcpy(p->z, argv[0]);
    }else{
      p->z = 0;
    }
  }
}
static void minMaxFinalize(sqlite_func *context){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && p->z ){
    sqlite_set_result_string(context, p->z, strlen(p->z));
  }
  if( p && p->z && p->z!=p->zBuf ){
    sqliteFree(p->z);
  }
}

................................................................................
    { "lower",      1, lowerFunc  },
    { "coalesce",  -1, ifnullFunc },
    { "coalesce",   0, 0          },
    { "coalesce",   1, 0          },
    { "random",    -1, randomFunc },
    { "like",       2, likeFunc   },
    { "glob",       2, globFunc   },

  };
  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
..
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
..
63
64
65
66
67
68
69
70


71
72
73
74
75

76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
...
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
...
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236

237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
...
344
345
346
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

372
373
374
375
376
377
378
379



380
381
382
383
384
385
386
387
388
389
390
391
392
393
394

395
396
397
398
399
400
401
402



403
404
405
406
407
408
409
410
411
412
413
414
415
...
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451

** 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.17 2002/05/26 21:34:58 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"

................................................................................
*/
static void minFunc(sqlite_func *context, int argc, const char **argv){
  const char *zBest; 
  int i;

  if( argc==0 ) return;
  zBest = argv[0];
  if( zBest==0 ) return;
  for(i=1; i<argc; i++){
    if( argv[i]==0 ) return;
    if( sqliteCompare(argv[i], zBest)<0 ){
      zBest = argv[i];
    }
  }
  sqlite_set_result_string(context, zBest, -1);
}
static void maxFunc(sqlite_func *context, int argc, const char **argv){
  const char *zBest; 
  int i;

  if( argc==0 ) return;
  zBest = argv[0];
  if( zBest==0 ) return;
  for(i=1; i<argc; i++){
    if( argv[i]==0 ) return;
    if( sqliteCompare(argv[i], zBest)>0 ){
      zBest = argv[i];
    }
  }
  sqlite_set_result_string(context, zBest, -1);
}

................................................................................
*/
static void lengthFunc(sqlite_func *context, int argc, const char **argv){
  const char *z;
  int len;

  assert( argc==1 );
  z = argv[0];
  if( z==0 ) return;


#ifdef SQLITE_UTF8
  for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; }
#else
  len = strlen(z);
#endif

  sqlite_set_result_int(context, len);
}

/*
** Implementation of the abs() function
*/
static void absFunc(sqlite_func *context, int argc, const char **argv){
  const char *z;
  assert( argc==1 );
  z = argv[0];
  if( z==0 ) return;
  if( z[0]=='-' && isdigit(z[1]) ) z++;
  sqlite_set_result_string(context, z, -1);
}

/*
** Implementation of the substr() function
*/
static void substrFunc(sqlite_func *context, int argc, const char **argv){
................................................................................
** Implementation of the round() function
*/
static void roundFunc(sqlite_func *context, int argc, const char **argv){
  int n;
  double r;
  char zBuf[100];
  assert( argc==1 || argc==2 );
  if( argv[0]==0 || (argc==2 && argv[1]==0) ) return;
  n = argc==2 ? atoi(argv[1]) : 0;
  if( n>30 ) n = 30;
  if( n<0 ) n = 0;
  r = atof(argv[0]);
  sprintf(zBuf,"%.*f",n,r);
  sqlite_set_result_string(context, zBuf, -1);
}

/*
** Implementation of the upper() and lower() SQL functions.
*/
................................................................................
** string and the second argument is the pattern.  So, the SQL statements:
**
**       A LIKE B
**
** is implemented as like(A,B).
*/
static void likeFunc(sqlite_func *context, int arg, const char **argv){
  if( argv[0]==0 || argv[1]==0 ) return;
  sqlite_set_result_int(context, sqliteLikeCompare(argv[0], argv[1]));
}

/*
** Implementation of the glob() SQL function.  This function implements
** the build-in GLOB operator.  The first argument to the function is the
** string and the second argument is the pattern.  So, the SQL statements:
**
**       A GLOB B
**
** is implemented as glob(A,B).
*/
static void globFunc(sqlite_func *context, int arg, const char **argv){
  if( argv[0]==0 || argv[1]==0 ) return;
  sqlite_set_result_int(context, sqliteGlobCompare(argv[0], argv[1]));

}

/*
** Implementation of the NULLIF(x,y) function.  The result is the first
** argument if the arguments are different.  The result is NULL if the
** arguments are equal to each other.
*/
static void nullifFunc(sqlite_func *context, int argc, const char **argv){
  if( argv[0]!=0 && sqliteCompare(argv[0],argv[1])!=0 ){
    sqlite_set_result_string(context, argv[0], -1);
  }
}

/*
** An instance of the following structure holds the context of a
** sum() or avg() aggregate computation.
*/
typedef struct SumCtx SumCtx;
................................................................................

/*
** This function tracks state information for the min() and max()
** aggregate functions.
*/
typedef struct MinMaxCtx MinMaxCtx;
struct MinMaxCtx {
  int isNull;      /* True if the result should be NULL */
  char *z;         /* The best so far */
  char zBuf[28];   /* Space that can be used for storage */
};

/*
** Routines to implement min() and max() aggregate functions.
*/
static void minStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 ) return;
  if( argv[0]==0 || p->isNull ){
    p->isNull = 1;
    return;
  }
  if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)<0 ){
    int len;
    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }

    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf) ){
      p->z = p->zBuf;
    }else{
      p->z = sqliteMalloc( len+1 );
      if( p->z==0 ) return;
    }
    strcpy(p->z, argv[0]);



  }
}
static void maxStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 ) return;
  if( argv[0]==0 || p->isNull ){
    p->isNull = 1;
    return;
  }
  if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)>0 ){
    int len;
    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }

    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf) ){
      p->z = p->zBuf;
    }else{
      p->z = sqliteMalloc( len+1 );
      if( p->z==0 ) return;
    }
    strcpy(p->z, argv[0]);



  }
}
static void minMaxFinalize(sqlite_func *context){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p && p->z && !p->isNull ){
    sqlite_set_result_string(context, p->z, strlen(p->z));
  }
  if( p && p->z && p->z!=p->zBuf ){
    sqliteFree(p->z);
  }
}

................................................................................
    { "lower",      1, lowerFunc  },
    { "coalesce",  -1, ifnullFunc },
    { "coalesce",   0, 0          },
    { "coalesce",   1, 0          },
    { "random",    -1, randomFunc },
    { "like",       2, likeFunc   },
    { "glob",       2, globFunc   },
    { "nullif",     2, nullifFunc },
  };
  static struct {
    char *zName;
    int nArg;
    void (*xStep)(sqlite_func*,int,const char**);
    void (*xFinalize)(sqlite_func*);
  } aAggs[] = {

Changes to src/util.c. 

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675

**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.43 2002/05/10 13:14:07 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
................................................................................
** order is a little bit different.  See sqliteSortCompare below
** for additional information.
*/
int sqliteCompare(const char *atext, const char *btext){
  int result;
  int isNumA, isNumB;
  if( atext==0 ){
    return -(btext!=0);
  }else if( btext==0 ){
    return 1;
  }
  isNumA = sqliteIsNumber(atext);
  isNumB = sqliteIsNumber(btext);
  if( isNumA ){
    if( !isNumB ){








|







 







|








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675

**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.44 2002/05/26 21:34:58 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
................................................................................
** order is a little bit different.  See sqliteSortCompare below
** for additional information.
*/
int sqliteCompare(const char *atext, const char *btext){
  int result;
  int isNumA, isNumB;
  if( atext==0 ){
    return -1;
  }else if( btext==0 ){
    return 1;
  }
  isNumA = sqliteIsNumber(atext);
  isNumB = sqliteIsNumber(btext);
  if( isNumA ){
    if( !isNumB ){

This page was generated in about 0.019s by Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13