src/func.c

   1 /*
   2 ** 2002 February 23
   3 **
   4 ** The author disclaims copyright to this source code.  In place of
   5 ** a legal notice, here is a blessing:
   6 **
   7 **    May you do good and not evil.
   8 **    May you find forgiveness for yourself and forgive others.
   9 **    May you share freely, never taking more than you give.
  10 **
  11 *************************************************************************
  12 ** This file contains the C functions that implement various SQL
  13 ** functions of SQLite.
  14 **
  15 ** There is only one exported symbol in this file - the function
  16 ** sqliteRegisterBuildinFunctions() found at the bottom of the file.
  17 ** All other code has file scope.
  18 */
  19 #include "sqliteInt.h"
  20 #include <stdlib.h>
  21 #include <assert.h>
  22 #include "vdbeInt.h"
  23
  24 /*
  25 ** Return the collating function associated with a function.
  26 */
  27 static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
  28   return context->pColl;
  29 }
  30
  31 /*
  32 ** Indicate that the accumulator load should be skipped on this
  33 ** iteration of the aggregate loop.
  34 */
  35 static void sqlite3SkipAccumulatorLoad(sqlite3_context *context){
  36   context->skipFlag = 1;
  37 }
  38
  39 /*
  40 ** Implementation of the non-aggregate min() and max() functions
  41 */
  42 static void minmaxFunc(
  43   sqlite3_context *context,
  44   int argc,
  45   sqlite3_value **argv
  46 ){
  47   int i;
  48   int mask;    /* 0 for min() or 0xffffffff for max() */
  49   int iBest;
  50   CollSeq *pColl;
  51
  52   assert( argc>1 );
  53   mask = sqlite3_user_data(context)==0 ? 0 : -1;
  54   pColl = sqlite3GetFuncCollSeq(context);
  55   assert( pColl );
  56   assert( mask==-1 || mask==0 );
  57   iBest = 0;
  58   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  59   for(i=1; i<argc; i++){
  60     if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return;
  61     if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){
  62       testcase( mask==0 );
  63       iBest = i;
  64     }
  65   }
  66   sqlite3_result_value(context, argv[iBest]);
  67 }
  68
  69 /*
  70 ** Return the type of the argument.
  71 */
  72 static void typeofFunc(
  73   sqlite3_context *context,
  74   int NotUsed,
  75   sqlite3_value **argv
  76 ){
  77   const char *z = 0;
  78   UNUSED_PARAMETER(NotUsed);
  79   switch( sqlite3_value_type(argv[0]) ){
  80     case SQLITE_INTEGER: z = "integer"; break;
  81     case SQLITE_TEXT:    z = "text";    break;
  82     case SQLITE_FLOAT:   z = "real";    break;
  83     case SQLITE_BLOB:    z = "blob";    break;
  84     default:             z = "null";    break;
  85   }
  86   sqlite3_result_text(context, z, -1, SQLITE_STATIC);
  87 }
  88
  89
  90 /*
  91 ** Implementation of the length() function
  92 */
  93 static void lengthFunc(
  94   sqlite3_context *context,
  95   int argc,
  96   sqlite3_value **argv
  97 ){
  98   int len;
  99
 100   assert( argc==1 );
 101   UNUSED_PARAMETER(argc);
 102   switch( sqlite3_value_type(argv[0]) ){
 103     case SQLITE_BLOB:
 104     case SQLITE_INTEGER:
 105     case SQLITE_FLOAT: {
 106       sqlite3_result_int(context, sqlite3_value_bytes(argv[0]));
 107       break;
 108     }
 109     case SQLITE_TEXT: {
 110       const unsigned char *z = sqlite3_value_text(argv[0]);
 111       if( z==0 ) return;
 112       len = 0;
 113       while( *z ){
 114         len++;
 115         SQLITE_SKIP_UTF8(z);
 116       }
 117       sqlite3_result_int(context, len);
 118       break;
 119     }
 120     default: {
 121       sqlite3_result_null(context);
 122       break;
 123     }
 124   }
 125 }
 126
 127 /*
 128 ** Implementation of the abs() function.
 129 **
 130 ** IMP: R-23979-26855 The abs(X) function returns the absolute value of
 131 ** the numeric argument X.
 132 */
 133 static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 134   assert( argc==1 );
 135   UNUSED_PARAMETER(argc);
 136   switch( sqlite3_value_type(argv[0]) ){
 137     case SQLITE_INTEGER: {
 138       i64 iVal = sqlite3_value_int64(argv[0]);
 139       if( iVal<0 ){
 140         if( (iVal<<1)==0 ){
 141           /* IMP: R-35460-15084 If X is the integer -9223372036854775807 then
 142           ** abs(X) throws an integer overflow error since there is no
 143           ** equivalent positive 64-bit two complement value. */
 144           sqlite3_result_error(context, "integer overflow", -1);
 145           return;
 146         }
 147         iVal = -iVal;
 148       }
 149       sqlite3_result_int64(context, iVal);
 150       break;
 151     }
 152     case SQLITE_NULL: {
 153       /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */
 154       sqlite3_result_null(context);
 155       break;
 156     }
 157     default: {
 158       /* Because sqlite3_value_double() returns 0.0 if the argument is not
 159       ** something that can be converted into a number, we have:
 160       ** IMP: R-57326-31541 Abs(X) return 0.0 if X is a string or blob that
 161       ** cannot be converted to a numeric value.
 162       */
 163       double rVal = sqlite3_value_double(argv[0]);
 164       if( rVal<0 ) rVal = -rVal;
 165       sqlite3_result_double(context, rVal);
 166       break;
 167     }
 168   }
 169 }
 170
 171 /*
 172 ** Implementation of the substr() function.
 173 **
 174 ** substr(x,p1,p2)  returns p2 characters of x[] beginning with p1.
 175 ** p1 is 1-indexed.  So substr(x,1,1) returns the first character
 176 ** of x.  If x is text, then we actually count UTF-8 characters.
 177 ** If x is a blob, then we count bytes.
 178 **
 179 ** If p1 is negative, then we begin abs(p1) from the end of x[].
 180 **
 181 ** If p2 is negative, return the p2 characters preceeding p1.
 182 */
 183 static void substrFunc(
 184   sqlite3_context *context,
 185   int argc,
 186   sqlite3_value **argv
 187 ){
 188   const unsigned char *z;
 189   const unsigned char *z2;
 190   int len;
 191   int p0type;
 192   i64 p1, p2;
 193   int negP2 = 0;
 194
 195   assert( argc==3 || argc==2 );
 196   if( sqlite3_value_type(argv[1])==SQLITE_NULL
 197    || (argc==3 && sqlite3_value_type(argv[2])==SQLITE_NULL)
 198   ){
 199     return;
 200   }
 201   p0type = sqlite3_value_type(argv[0]);
 202   p1 = sqlite3_value_int(argv[1]);
 203   if( p0type==SQLITE_BLOB ){
 204     len = sqlite3_value_bytes(argv[0]);
 205     z = sqlite3_value_blob(argv[0]);
 206     if( z==0 ) return;
 207     assert( len==sqlite3_value_bytes(argv[0]) );
 208   }else{
 209     z = sqlite3_value_text(argv[0]);
 210     if( z==0 ) return;
 211     len = 0;
 212     if( p1<0 ){
 213       for(z2=z; *z2; len++){
 214         SQLITE_SKIP_UTF8(z2);
 215       }
 216     }
 217   }
 218   if( argc==3 ){
 219     p2 = sqlite3_value_int(argv[2]);
 220     if( p2<0 ){
 221       p2 = -p2;
 222       negP2 = 1;
 223     }
 224   }else{
 225     p2 = sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH];
 226   }
 227   if( p1<0 ){
 228     p1 += len;
 229     if( p1<0 ){
 230       p2 += p1;
 231       if( p2<0 ) p2 = 0;
 232       p1 = 0;
 233     }
 234   }else if( p1>0 ){
 235     p1--;
 236   }else if( p2>0 ){
 237     p2--;
 238   }
 239   if( negP2 ){
 240     p1 -= p2;
 241     if( p1<0 ){
 242       p2 += p1;
 243       p1 = 0;
 244     }
 245   }
 246   assert( p1>=0 && p2>=0 );
 247   if( p0type!=SQLITE_BLOB ){
 248     while( *z && p1 ){
 249       SQLITE_SKIP_UTF8(z);
 250       p1--;
 251     }
 252     for(z2=z; *z2 && p2; p2--){
 253       SQLITE_SKIP_UTF8(z2);
 254     }
 255     sqlite3_result_text(context, (char*)z, (int)(z2-z), SQLITE_TRANSIENT);
 256   }else{
 257     if( p1+p2>len ){
 258       p2 = len-p1;
 259       if( p2<0 ) p2 = 0;
 260     }
 261     sqlite3_result_blob(context, (char*)&z[p1], (int)p2, SQLITE_TRANSIENT);
 262   }
 263 }
 264
 265 /*
 266 ** Implementation of the round() function
 267 */
 268 #ifndef SQLITE_OMIT_FLOATING_POINT
 269 static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 270   int n = 0;
 271   double r;
 272   char *zBuf;
 273   assert( argc==1 || argc==2 );
 274   if( argc==2 ){
 275     if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
 276     n = sqlite3_value_int(argv[1]);
 277     if( n>30 ) n = 30;
 278     if( n<0 ) n = 0;
 279   }
 280   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
 281   r = sqlite3_value_double(argv[0]);
 282   /* If Y==0 and X will fit in a 64-bit int,
 283   ** handle the rounding directly,
 284   ** otherwise use printf.
 285   */
 286   if( n==0 && r>=0 && r<LARGEST_INT64-1 ){
 287     r = (double)((sqlite_int64)(r+0.5));
 288   }else if( n==0 && r<0 && (-r)<LARGEST_INT64-1 ){
 289     r = -(double)((sqlite_int64)((-r)+0.5));
 290   }else{
 291     zBuf = sqlite3_mprintf("%.*f",n,r);
 292     if( zBuf==0 ){
 293       sqlite3_result_error_nomem(context);
 294       return;
 295     }
 296     sqlite3AtoF(zBuf, &r, sqlite3Strlen30(zBuf), SQLITE_UTF8);
 297     sqlite3_free(zBuf);
 298   }
 299   sqlite3_result_double(context, r);
 300 }
 301 #endif
 302
 303 /*
 304 ** Allocate nByte bytes of space using sqlite3_malloc(). If the
 305 ** allocation fails, call sqlite3_result_error_nomem() to notify
 306 ** the database handle that malloc() has failed and return NULL.
 307 ** If nByte is larger than the maximum string or blob length, then
 308 ** raise an SQLITE_TOOBIG exception and return NULL.
 309 */
 310 static void *contextMalloc(sqlite3_context *context, i64 nByte){
 311   char *z;
 312   sqlite3 *db = sqlite3_context_db_handle(context);
 313   assert( nByte>0 );
 314   testcase( nByte==db->aLimit[SQLITE_LIMIT_LENGTH] );
 315   testcase( nByte==db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
 316   if( nByte>db->aLimit[SQLITE_LIMIT_LENGTH] ){
 317     sqlite3_result_error_toobig(context);
 318     z = 0;
 319   }else{
 320     z = sqlite3Malloc((int)nByte);
 321     if( !z ){
 322       sqlite3_result_error_nomem(context);
 323     }
 324   }
 325   return z;
 326 }
 327
 328 /*
 329 ** Implementation of the upper() and lower() SQL functions.
 330 */
 331 static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 332   char *z1;
 333   const char *z2;
 334   int i, n;
 335   UNUSED_PARAMETER(argc);
 336   z2 = (char*)sqlite3_value_text(argv[0]);
 337   n = sqlite3_value_bytes(argv[0]);
 338   /* Verify that the call to _bytes() does not invalidate the _text() pointer */
 339   assert( z2==(char*)sqlite3_value_text(argv[0]) );
 340   if( z2 ){
 341     z1 = contextMalloc(context, ((i64)n)+1);
 342     if( z1 ){
 343       for(i=0; i<n; i++){
 344         z1[i] = (char)sqlite3Toupper(z2[i]);
 345       }
 346       sqlite3_result_text(context, z1, n, sqlite3_free);
 347     }
 348   }
 349 }
 350 static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 351   char *z1;
 352   const char *z2;
 353   int i, n;
 354   UNUSED_PARAMETER(argc);
 355   z2 = (char*)sqlite3_value_text(argv[0]);
 356   n = sqlite3_value_bytes(argv[0]);
 357   /* Verify that the call to _bytes() does not invalidate the _text() pointer */
 358   assert( z2==(char*)sqlite3_value_text(argv[0]) );
 359   if( z2 ){
 360     z1 = contextMalloc(context, ((i64)n)+1);
 361     if( z1 ){
 362       for(i=0; i<n; i++){
 363         z1[i] = sqlite3Tolower(z2[i]);
 364       }
 365       sqlite3_result_text(context, z1, n, sqlite3_free);
 366     }
 367   }
 368 }
 369
 370
 371 #if 0  /* This function is never used. */
 372 /*
 373 ** The COALESCE() and IFNULL() functions used to be implemented as shown
 374 ** here.  But now they are implemented as VDBE code so that unused arguments
 375 ** do not have to be computed.  This legacy implementation is retained as
 376 ** comment.
 377 */
 378 /*
 379 ** Implementation of the IFNULL(), NVL(), and COALESCE() functions.
 380 ** All three do the same thing.  They return the first non-NULL
 381 ** argument.
 382 */
 383 static void ifnullFunc(
 384   sqlite3_context *context,
 385   int argc,
 386   sqlite3_value **argv
 387 ){
 388   int i;
 389   for(i=0; i<argc; i++){
 390     if( SQLITE_NULL!=sqlite3_value_type(argv[i]) ){
 391       sqlite3_result_value(context, argv[i]);
 392       break;
 393     }
 394   }
 395 }
 396 #endif /* NOT USED */
 397 #define ifnullFunc versionFunc   /* Substitute function - never called */
 398
 399 /*
 400 ** Implementation of random().  Return a random integer.
 401 */
 402 static void randomFunc(
 403   sqlite3_context *context,
 404   int NotUsed,
 405   sqlite3_value **NotUsed2
 406 ){
 407   sqlite_int64 r;
 408   UNUSED_PARAMETER2(NotUsed, NotUsed2);
 409   sqlite3_randomness(sizeof(r), &r);
 410   if( r<0 ){
 411     /* We need to prevent a random number of 0x8000000000000000
 412     ** (or -9223372036854775808) since when you do abs() of that
 413     ** number of you get the same value back again.  To do this
 414     ** in a way that is testable, mask the sign bit off of negative
 415     ** values, resulting in a positive value.  Then take the
 416     ** 2s complement of that positive value.  The end result can
 417     ** therefore be no less than -9223372036854775807.
 418     */
 419     r = -(r & LARGEST_INT64);
 420   }
 421   sqlite3_result_int64(context, r);
 422 }
 423
 424 /*
 425 ** Implementation of randomblob(N).  Return a random blob
 426 ** that is N bytes long.
 427 */
 428 static void randomBlob(
 429   sqlite3_context *context,
 430   int argc,
 431   sqlite3_value **argv
 432 ){
 433   int n;
 434   unsigned char *p;
 435   assert( argc==1 );
 436   UNUSED_PARAMETER(argc);
 437   n = sqlite3_value_int(argv[0]);
 438   if( n<1 ){
 439     n = 1;
 440   }
 441   p = contextMalloc(context, n);
 442   if( p ){
 443     sqlite3_randomness(n, p);
 444     sqlite3_result_blob(context, (char*)p, n, sqlite3_free);
 445   }
 446 }
 447
 448 /*
 449 ** Implementation of the last_insert_rowid() SQL function.  The return
 450 ** value is the same as the sqlite3_last_insert_rowid() API function.
 451 */
 452 static void last_insert_rowid(
 453   sqlite3_context *context,
 454   int NotUsed,
 455   sqlite3_value **NotUsed2
 456 ){
 457   sqlite3 *db = sqlite3_context_db_handle(context);
 458   UNUSED_PARAMETER2(NotUsed, NotUsed2);
 459   /* IMP: R-51513-12026 The last_insert_rowid() SQL function is a
 460   ** wrapper around the sqlite3_last_insert_rowid() C/C++ interface
 461   ** function. */
 462   sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));
 463 }
 464
 465 /*
 466 ** Implementation of the changes() SQL function.
 467 **
 468 ** IMP: R-62073-11209 The changes() SQL function is a wrapper
 469 ** around the sqlite3_changes() C/C++ function and hence follows the same
 470 ** rules for counting changes.
 471 */
 472 static void changes(
 473   sqlite3_context *context,
 474   int NotUsed,
 475   sqlite3_value **NotUsed2
 476 ){
 477   sqlite3 *db = sqlite3_context_db_handle(context);
 478   UNUSED_PARAMETER2(NotUsed, NotUsed2);
 479   sqlite3_result_int(context, sqlite3_changes(db));
 480 }
 481
 482 /*
 483 ** Implementation of the total_changes() SQL function.  The return value is
 484 ** the same as the sqlite3_total_changes() API function.
 485 */
 486 static void total_changes(
 487   sqlite3_context *context,
 488   int NotUsed,
 489   sqlite3_value **NotUsed2
 490 ){
 491   sqlite3 *db = sqlite3_context_db_handle(context);
 492   UNUSED_PARAMETER2(NotUsed, NotUsed2);
 493   /* IMP: R-52756-41993 This function is a wrapper around the
 494   ** sqlite3_total_changes() C/C++ interface. */
 495   sqlite3_result_int(context, sqlite3_total_changes(db));
 496 }
 497
 498 /*
 499 ** A structure defining how to do GLOB-style comparisons.
 500 */
 501 struct compareInfo {
 502   u8 matchAll;
 503   u8 matchOne;
 504   u8 matchSet;
 505   u8 noCase;
 506 };
 507
 508 /*
 509 ** For LIKE and GLOB matching on EBCDIC machines, assume that every
 510 ** character is exactly one byte in size.  Also, all characters are
 511 ** able to participate in upper-case-to-lower-case mappings in EBCDIC
 512 ** whereas only characters less than 0x80 do in ASCII.
 513 */
 514 #if defined(SQLITE_EBCDIC)
 515 # define sqlite3Utf8Read(A,C)  (*(A++))
 516 # define GlogUpperToLower(A)   A = sqlite3UpperToLower[A]
 517 #else
 518 # define GlogUpperToLower(A)   if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; }
 519 #endif
 520
 521 static const struct compareInfo globInfo = { '*', '?', '[', 0 };
 522 /* The correct SQL-92 behavior is for the LIKE operator to ignore
 523 ** case.  Thus  'a' LIKE 'A' would be true. */
 524 static const struct compareInfo likeInfoNorm = { '%', '_',   0, 1 };
 525 /* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator
 526 ** is case sensitive causing 'a' LIKE 'A' to be false */
 527 static const struct compareInfo likeInfoAlt = { '%', '_',   0, 0 };
 528
 529 /*
 530 ** Compare two UTF-8 strings for equality where the first string can
 531 ** potentially be a "glob" expression.  Return true (1) if they
 532 ** are the same and false (0) if they are different.
 533 **
 534 ** Globbing rules:
 535 **
 536 **      '*'       Matches any sequence of zero or more characters.
 537 **
 538 **      '?'       Matches exactly one character.
 539 **
 540 **     [...]      Matches one character from the enclosed list of
 541 **                characters.
 542 **
 543 **     [^...]     Matches one character not in the enclosed list.
 544 **
 545 ** With the [...] and [^...] matching, a ']' character can be included
 546 ** in the list by making it the first character after '[' or '^'.  A
 547 ** range of characters can be specified using '-'.  Example:
 548 ** "[a-z]" matches any single lower-case letter.  To match a '-', make
 549 ** it the last character in the list.
 550 **
 551 ** This routine is usually quick, but can be N**2 in the worst case.
 552 **
 553 ** Hints: to match '*' or '?', put them in "[]".  Like this:
 554 **
 555 **         abc[*]xyz        Matches "abc*xyz" only
 556 */
 557 static int patternCompare(
 558   const u8 *zPattern,              /* The glob pattern */
 559   const u8 *zString,               /* The string to compare against the glob */
 560   const struct compareInfo *pInfo, /* Information about how to do the compare */
 561   u32 esc                          /* The escape character */
 562 ){
 563   u32 c, c2;
 564   int invert;
 565   int seen;
 566   u8 matchOne = pInfo->matchOne;
 567   u8 matchAll = pInfo->matchAll;
 568   u8 matchSet = pInfo->matchSet;
 569   u8 noCase = pInfo->noCase;
 570   int prevEscape = 0;     /* True if the previous character was 'escape' */
 571
 572   while( (c = sqlite3Utf8Read(zPattern,&zPattern))!=0 ){
 573     if( !prevEscape && c==matchAll ){
 574       while( (c=sqlite3Utf8Read(zPattern,&zPattern)) == matchAll
 575                || c == matchOne ){
 576         if( c==matchOne && sqlite3Utf8Read(zString, &zString)==0 ){
 577           return 0;
 578         }
 579       }
 580       if( c==0 ){
 581         return 1;
 582       }else if( c==esc ){
 583         c = sqlite3Utf8Read(zPattern, &zPattern);
 584         if( c==0 ){
 585           return 0;
 586         }
 587       }else if( c==matchSet ){
 588         assert( esc==0 );         /* This is GLOB, not LIKE */
 589         assert( matchSet<0x80 );  /* '[' is a single-byte character */
 590         while( *zString && patternCompare(&zPattern[-1],zString,pInfo,esc)==0 ){
 591           SQLITE_SKIP_UTF8(zString);
 592         }
 593         return *zString!=0;
 594       }
 595       while( (c2 = sqlite3Utf8Read(zString,&zString))!=0 ){
 596         if( noCase ){
 597           GlogUpperToLower(c2);
 598           GlogUpperToLower(c);
 599           while( c2 != 0 && c2 != c ){
 600             c2 = sqlite3Utf8Read(zString, &zString);
 601             GlogUpperToLower(c2);
 602           }
 603         }else{
 604           while( c2 != 0 && c2 != c ){
 605             c2 = sqlite3Utf8Read(zString, &zString);
 606           }
 607         }
 608         if( c2==0 ) return 0;
 609         if( patternCompare(zPattern,zString,pInfo,esc) ) return 1;
 610       }
 611       return 0;
 612     }else if( !prevEscape && c==matchOne ){
 613       if( sqlite3Utf8Read(zString, &zString)==0 ){
 614         return 0;
 615       }
 616     }else if( c==matchSet ){
 617       u32 prior_c = 0;
 618       assert( esc==0 );    /* This only occurs for GLOB, not LIKE */
 619       seen = 0;
 620       invert = 0;
 621       c = sqlite3Utf8Read(zString, &zString);
 622       if( c==0 ) return 0;
 623       c2 = sqlite3Utf8Read(zPattern, &zPattern);
 624       if( c2=='^' ){
 625         invert = 1;
 626         c2 = sqlite3Utf8Read(zPattern, &zPattern);
 627       }
 628       if( c2==']' ){
 629         if( c==']' ) seen = 1;
 630         c2 = sqlite3Utf8Read(zPattern, &zPattern);
 631       }
 632       while( c2 && c2!=']' ){
 633         if( c2=='-' && zPattern[0]!=']' && zPattern[0]!=0 && prior_c>0 ){
 634           c2 = sqlite3Utf8Read(zPattern, &zPattern);
 635           if( c>=prior_c && c<=c2 ) seen = 1;
 636           prior_c = 0;
 637         }else{
 638           if( c==c2 ){
 639             seen = 1;
 640           }
 641           prior_c = c2;
 642         }
 643         c2 = sqlite3Utf8Read(zPattern, &zPattern);
 644       }
 645       if( c2==0 || (seen ^ invert)==0 ){
 646         return 0;
 647       }
 648     }else if( esc==c && !prevEscape ){
 649       prevEscape = 1;
 650     }else{
 651       c2 = sqlite3Utf8Read(zString, &zString);
 652       if( noCase ){
 653         GlogUpperToLower(c);
 654         GlogUpperToLower(c2);
 655       }
 656       if( c!=c2 ){
 657         return 0;
 658       }
 659       prevEscape = 0;
 660     }
 661   }
 662   return *zString==0;
 663 }
 664
 665 /*
 666 ** Count the number of times that the LIKE operator (or GLOB which is
 667 ** just a variation of LIKE) gets called.  This is used for testing
 668 ** only.
 669 */
 670 #ifdef SQLITE_TEST
 671 int sqlite3_like_count = 0;
 672 #endif
 673
 674
 675 /*
 676 ** Implementation of the like() SQL function.  This function implements
 677 ** the build-in LIKE operator.  The first argument to the function is the
 678 ** pattern and the second argument is the string.  So, the SQL statements:
 679 **
 680 **       A LIKE B
 681 **
 682 ** is implemented as like(B,A).
 683 **
 684 ** This same function (with a different compareInfo structure) computes
 685 ** the GLOB operator.
 686 */
 687 static void likeFunc(
 688   sqlite3_context *context,
 689   int argc,
 690   sqlite3_value **argv
 691 ){
 692   const unsigned char *zA, *zB;
 693   u32 escape = 0;
 694   int nPat;
 695   sqlite3 *db = sqlite3_context_db_handle(context);
 696
 697   zB = sqlite3_value_text(argv[0]);
 698   zA = sqlite3_value_text(argv[1]);
 699
 700   /* Limit the length of the LIKE or GLOB pattern to avoid problems
 701   ** of deep recursion and N*N behavior in patternCompare().
 702   */
 703   nPat = sqlite3_value_bytes(argv[0]);
 704   testcase( nPat==db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] );
 705   testcase( nPat==db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH]+1 );
 706   if( nPat > db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] ){
 707     sqlite3_result_error(context, "LIKE or GLOB pattern too complex", -1);
 708     return;
 709   }
 710   assert( zB==sqlite3_value_text(argv[0]) );  /* Encoding did not change */
 711
 712   if( argc==3 ){
 713     /* The escape character string must consist of a single UTF-8 character.
 714     ** Otherwise, return an error.
 715     */
 716     const unsigned char *zEsc = sqlite3_value_text(argv[2]);
 717     if( zEsc==0 ) return;
 718     if( sqlite3Utf8CharLen((char*)zEsc, -1)!=1 ){
 719       sqlite3_result_error(context,
 720           "ESCAPE expression must be a single character", -1);
 721       return;
 722     }
 723     escape = sqlite3Utf8Read(zEsc, &zEsc);
 724   }
 725   if( zA && zB ){
 726     struct compareInfo *pInfo = sqlite3_user_data(context);
 727 #ifdef SQLITE_TEST
 728     sqlite3_like_count++;
 729 #endif
 730
 731     sqlite3_result_int(context, patternCompare(zB, zA, pInfo, escape));
 732   }
 733 }
 734
 735 /*
 736 ** Implementation of the NULLIF(x,y) function.  The result is the first
 737 ** argument if the arguments are different.  The result is NULL if the
 738 ** arguments are equal to each other.
 739 */
 740 static void nullifFunc(
 741   sqlite3_context *context,
 742   int NotUsed,
 743   sqlite3_value **argv
 744 ){
 745   CollSeq *pColl = sqlite3GetFuncCollSeq(context);
 746   UNUSED_PARAMETER(NotUsed);
 747   if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){
 748     sqlite3_result_value(context, argv[0]);
 749   }
 750 }
 751
 752 /*
 753 ** Implementation of the sqlite_version() function.  The result is the version
 754 ** of the SQLite library that is running.
 755 */
 756 static void versionFunc(
 757   sqlite3_context *context,
 758   int NotUsed,
 759   sqlite3_value **NotUsed2
 760 ){
 761   UNUSED_PARAMETER2(NotUsed, NotUsed2);
 762   /* IMP: R-48699-48617 This function is an SQL wrapper around the
 763   ** sqlite3_libversion() C-interface. */
 764   sqlite3_result_text(context, sqlite3_libversion(), -1, SQLITE_STATIC);
 765 }
 766
 767 /*
 768 ** Implementation of the sqlite_source_id() function. The result is a string
 769 ** that identifies the particular version of the source code used to build
 770 ** SQLite.
 771 */
 772 static void sourceidFunc(
 773   sqlite3_context *context,
 774   int NotUsed,
 775   sqlite3_value **NotUsed2
 776 ){
 777   UNUSED_PARAMETER2(NotUsed, NotUsed2);
 778   /* IMP: R-24470-31136 This function is an SQL wrapper around the
 779   ** sqlite3_sourceid() C interface. */
 780   sqlite3_result_text(context, sqlite3_sourceid(), -1, SQLITE_STATIC);
 781 }
 782
 783 /*
 784 ** Implementation of the sqlite_log() function.  This is a wrapper around
 785 ** sqlite3_log().  The return value is NULL.  The function exists purely for
 786 ** its side-effects.
 787 */
 788 static void errlogFunc(
 789   sqlite3_context *context,
 790   int argc,
 791   sqlite3_value **argv
 792 ){
 793   UNUSED_PARAMETER(argc);
 794   UNUSED_PARAMETER(context);
 795   sqlite3_log(sqlite3_value_int(argv[0]), "%s", sqlite3_value_text(argv[1]));
 796 }
 797
 798 /*
 799 ** Implementation of the sqlite_compileoption_used() function.
 800 ** The result is an integer that identifies if the compiler option
 801 ** was used to build SQLite.
 802 */
 803 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
 804 static void compileoptionusedFunc(
 805   sqlite3_context *context,
 806   int argc,
 807   sqlite3_value **argv
 808 ){
 809   const char *zOptName;
 810   assert( argc==1 );
 811   UNUSED_PARAMETER(argc);
 812   /* IMP: R-39564-36305 The sqlite_compileoption_used() SQL
 813   ** function is a wrapper around the sqlite3_compileoption_used() C/C++
 814   ** function.
 815   */
 816   if( (zOptName = (const char*)sqlite3_value_text(argv[0]))!=0 ){
 817     sqlite3_result_int(context, sqlite3_compileoption_used(zOptName));
 818   }
 819 }
 820 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
 821
 822 /*
 823 ** Implementation of the sqlite_compileoption_get() function.
 824 ** The result is a string that identifies the compiler options
 825 ** used to build SQLite.
 826 */
 827 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
 828 static void compileoptiongetFunc(
 829   sqlite3_context *context,
 830   int argc,
 831   sqlite3_value **argv
 832 ){
 833   int n;
 834   assert( argc==1 );
 835   UNUSED_PARAMETER(argc);
 836   /* IMP: R-04922-24076 The sqlite_compileoption_get() SQL function
 837   ** is a wrapper around the sqlite3_compileoption_get() C/C++ function.
 838   */
 839   n = sqlite3_value_int(argv[0]);
 840   sqlite3_result_text(context, sqlite3_compileoption_get(n), -1, SQLITE_STATIC);
 841 }
 842 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
 843
 844 /* Array for converting from half-bytes (nybbles) into ASCII hex
 845 ** digits. */
 846 static const char hexdigits[] = {
 847   '0', '1', '2', '3', '4', '5', '6', '7',
 848   '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
 849 };
 850
 851 /*
 852 ** EXPERIMENTAL - This is not an official function.  The interface may
 853 ** change.  This function may disappear.  Do not write code that depends
 854 ** on this function.
 855 **
 856 ** Implementation of the QUOTE() function.  This function takes a single
 857 ** argument.  If the argument is numeric, the return value is the same as
 858 ** the argument.  If the argument is NULL, the return value is the string
 859 ** "NULL".  Otherwise, the argument is enclosed in single quotes with
 860 ** single-quote escapes.
 861 */
 862 static void quoteFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 863   assert( argc==1 );
 864   UNUSED_PARAMETER(argc);
 865   switch( sqlite3_value_type(argv[0]) ){
 866     case SQLITE_INTEGER:
 867     case SQLITE_FLOAT: {
 868       sqlite3_result_value(context, argv[0]);
 869       break;
 870     }
 871     case SQLITE_BLOB: {
 872       char *zText = 0;
 873       char const *zBlob = sqlite3_value_blob(argv[0]);
 874       int nBlob = sqlite3_value_bytes(argv[0]);
 875       assert( zBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */
 876       zText = (char *)contextMalloc(context, (2*(i64)nBlob)+4);
 877       if( zText ){
 878         int i;
 879         for(i=0; i<nBlob; i++){
 880           zText[(i*2)+2] = hexdigits[(zBlob[i]>>4)&0x0F];
 881           zText[(i*2)+3] = hexdigits[(zBlob[i])&0x0F];
 882         }
 883         zText[(nBlob*2)+2] = '\'';
 884         zText[(nBlob*2)+3] = '\0';
 885         zText[0] = 'X';
 886         zText[1] = '\'';
 887         sqlite3_result_text(context, zText, -1, SQLITE_TRANSIENT);
 888         sqlite3_free(zText);
 889       }
 890       break;
 891     }
 892     case SQLITE_TEXT: {
 893       int i,j;
 894       u64 n;
 895       const unsigned char *zArg = sqlite3_value_text(argv[0]);
 896       char *z;
 897
 898       if( zArg==0 ) return;
 899       for(i=0, n=0; zArg[i]; i++){ if( zArg[i]=='\'' ) n++; }
 900       z = contextMalloc(context, ((i64)i)+((i64)n)+3);
 901       if( z ){
 902         z[0] = '\'';
 903         for(i=0, j=1; zArg[i]; i++){
 904           z[j++] = zArg[i];
 905           if( zArg[i]=='\'' ){
 906             z[j++] = '\'';
 907           }
 908         }
 909         z[j++] = '\'';
 910         z[j] = 0;
 911         sqlite3_result_text(context, z, j, sqlite3_free);
 912       }
 913       break;
 914     }
 915     default: {
 916       assert( sqlite3_value_type(argv[0])==SQLITE_NULL );
 917       sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC);
 918       break;
 919     }
 920   }
 921 }
 922
 923 /*
 924 ** The hex() function.  Interpret the argument as a blob.  Return
 925 ** a hexadecimal rendering as text.
 926 */
 927 static void hexFunc(
 928   sqlite3_context *context,
 929   int argc,
 930   sqlite3_value **argv
 931 ){
 932   int i, n;
 933   const unsigned char *pBlob;
 934   char *zHex, *z;
 935   assert( argc==1 );
 936   UNUSED_PARAMETER(argc);
 937   pBlob = sqlite3_value_blob(argv[0]);
 938   n = sqlite3_value_bytes(argv[0]);
 939   assert( pBlob==sqlite3_value_blob(argv[0]) );  /* No encoding change */
 940   z = zHex = contextMalloc(context, ((i64)n)*2 + 1);
 941   if( zHex ){
 942     for(i=0; i<n; i++, pBlob++){
 943       unsigned char c = *pBlob;
 944       *(z++) = hexdigits[(c>>4)&0xf];
 945       *(z++) = hexdigits[c&0xf];
 946     }
 947     *z = 0;
 948     sqlite3_result_text(context, zHex, n*2, sqlite3_free);
 949   }
 950 }
 951
 952 /*
 953 ** The zeroblob(N) function returns a zero-filled blob of size N bytes.
 954 */
 955 static void zeroblobFunc(
 956   sqlite3_context *context,
 957   int argc,
 958   sqlite3_value **argv
 959 ){
 960   i64 n;
 961   sqlite3 *db = sqlite3_context_db_handle(context);
 962   assert( argc==1 );
 963   UNUSED_PARAMETER(argc);
 964   n = sqlite3_value_int64(argv[0]);
 965   testcase( n==db->aLimit[SQLITE_LIMIT_LENGTH] );
 966   testcase( n==db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
 967   if( n>db->aLimit[SQLITE_LIMIT_LENGTH] ){
 968     sqlite3_result_error_toobig(context);
 969   }else{
 970     sqlite3_result_zeroblob(context, (int)n); /* IMP: R-00293-64994 */
 971   }
 972 }
 973
 974 /*
 975 ** The replace() function.  Three arguments are all strings: call
 976 ** them A, B, and C. The result is also a string which is derived
 977 ** from A by replacing every occurance of B with C.  The match
 978 ** must be exact.  Collating sequences are not used.
 979 */
 980 static void replaceFunc(
 981   sqlite3_context *context,
 982   int argc,
 983   sqlite3_value **argv
 984 ){
 985   const unsigned char *zStr;        /* The input string A */
 986   const unsigned char *zPattern;    /* The pattern string B */
 987   const unsigned char *zRep;        /* The replacement string C */
 988   unsigned char *zOut;              /* The output */
 989   int nStr;                /* Size of zStr */
 990   int nPattern;            /* Size of zPattern */
 991   int nRep;                /* Size of zRep */
 992   i64 nOut;                /* Maximum size of zOut */
 993   int loopLimit;           /* Last zStr[] that might match zPattern[] */
 994   int i, j;                /* Loop counters */
 995
 996   assert( argc==3 );
 997   UNUSED_PARAMETER(argc);
 998   zStr = sqlite3_value_text(argv[0]);
 999   if( zStr==0 ) return;
1000   nStr = sqlite3_value_bytes(argv[0]);
1001   assert( zStr==sqlite3_value_text(argv[0]) );  /* No encoding change */
1002   zPattern = sqlite3_value_text(argv[1]);
1003   if( zPattern==0 ){
1004     assert( sqlite3_value_type(argv[1])==SQLITE_NULL
1005             || sqlite3_context_db_handle(context)->mallocFailed );
1006     return;
1007   }
1008   if( zPattern[0]==0 ){
1009     assert( sqlite3_value_type(argv[1])!=SQLITE_NULL );
1010     sqlite3_result_value(context, argv[0]);
1011     return;
1012   }
1013   nPattern = sqlite3_value_bytes(argv[1]);
1014   assert( zPattern==sqlite3_value_text(argv[1]) );  /* No encoding change */
1015   zRep = sqlite3_value_text(argv[2]);
1016   if( zRep==0 ) return;
1017   nRep = sqlite3_value_bytes(argv[2]);
1018   assert( zRep==sqlite3_value_text(argv[2]) );
1019   nOut = nStr + 1;
1020   assert( nOut<SQLITE_MAX_LENGTH );
1021   zOut = contextMalloc(context, (i64)nOut);
1022   if( zOut==0 ){
1023     return;
1024   }
1025   loopLimit = nStr - nPattern;
1026   for(i=j=0; i<=loopLimit; i++){
1027     if( zStr[i]!=zPattern[0] || memcmp(&zStr[i], zPattern, nPattern) ){
1028       zOut[j++] = zStr[i];
1029     }else{
1030       u8 *zOld;
1031       sqlite3 *db = sqlite3_context_db_handle(context);
1032       nOut += nRep - nPattern;
1033       testcase( nOut-1==db->aLimit[SQLITE_LIMIT_LENGTH] );
1034       testcase( nOut-2==db->aLimit[SQLITE_LIMIT_LENGTH] );
1035       if( nOut-1>db->aLimit[SQLITE_LIMIT_LENGTH] ){
1036         sqlite3_result_error_toobig(context);
1037         sqlite3_free(zOut);
1038         return;
1039       }
1040       zOld = zOut;
1041       zOut = sqlite3_realloc(zOut, (int)nOut);
1042       if( zOut==0 ){
1043         sqlite3_result_error_nomem(context);
1044         sqlite3_free(zOld);
1045         return;
1046       }
1047       memcpy(&zOut[j], zRep, nRep);
1048       j += nRep;
1049       i += nPattern-1;
1050     }
1051   }
1052   assert( j+nStr-i+1==nOut );
1053   memcpy(&zOut[j], &zStr[i], nStr-i);
1054   j += nStr - i;
1055   assert( j<=nOut );
1056   zOut[j] = 0;
1057   sqlite3_result_text(context, (char*)zOut, j, sqlite3_free);
1058 }
1059
1060 /*
1061 ** Implementation of the TRIM(), LTRIM(), and RTRIM() functions.
1062 ** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both.
1063 */
1064 static void trimFunc(
1065   sqlite3_context *context,
1066   int argc,
1067   sqlite3_value **argv
1068 ){
1069   const unsigned char *zIn;         /* Input string */
1070   const unsigned char *zCharSet;    /* Set of characters to trim */
1071   int nIn;                          /* Number of bytes in input */
1072   int flags;                        /* 1: trimleft  2: trimright  3: trim */
1073   int i;                            /* Loop counter */
1074   unsigned char *aLen = 0;          /* Length of each character in zCharSet */
1075   unsigned char **azChar = 0;       /* Individual characters in zCharSet */
1076   int nChar;                        /* Number of characters in zCharSet */
1077
1078   if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
1079     return;
1080   }
1081   zIn = sqlite3_value_text(argv[0]);
1082   if( zIn==0 ) return;
1083   nIn = sqlite3_value_bytes(argv[0]);
1084   assert( zIn==sqlite3_value_text(argv[0]) );
1085   if( argc==1 ){
1086     static const unsigned char lenOne[] = { 1 };
1087     static unsigned char * const azOne[] = { (u8*)" " };
1088     nChar = 1;
1089     aLen = (u8*)lenOne;
1090     azChar = (unsigned char **)azOne;
1091     zCharSet = 0;
1092   }else if( (zCharSet = sqlite3_value_text(argv[1]))==0 ){
1093     return;
1094   }else{
1095     const unsigned char *z;
1096     for(z=zCharSet, nChar=0; *z; nChar++){
1097       SQLITE_SKIP_UTF8(z);
1098     }
1099     if( nChar>0 ){
1100       azChar = contextMalloc(context, ((i64)nChar)*(sizeof(char*)+1));
1101       if( azChar==0 ){
1102         return;
1103       }
1104       aLen = (unsigned char*)&azChar[nChar];
1105       for(z=zCharSet, nChar=0; *z; nChar++){
1106         azChar[nChar] = (unsigned char *)z;
1107         SQLITE_SKIP_UTF8(z);
1108         aLen[nChar] = (u8)(z - azChar[nChar]);
1109       }
1110     }
1111   }
1112   if( nChar>0 ){
1113     flags = SQLITE_PTR_TO_INT(sqlite3_user_data(context));
1114     if( flags & 1 ){
1115       while( nIn>0 ){
1116         int len = 0;
1117         for(i=0; i<nChar; i++){
1118           len = aLen[i];
1119           if( len<=nIn && memcmp(zIn, azChar[i], len)==0 ) break;
1120         }
1121         if( i>=nChar ) break;
1122         zIn += len;
1123         nIn -= len;
1124       }
1125     }
1126     if( flags & 2 ){
1127       while( nIn>0 ){
1128         int len = 0;
1129         for(i=0; i<nChar; i++){
1130           len = aLen[i];
1131           if( len<=nIn && memcmp(&zIn[nIn-len],azChar[i],len)==0 ) break;
1132         }
1133         if( i>=nChar ) break;
1134         nIn -= len;
1135       }
1136     }
1137     if( zCharSet ){
1138       sqlite3_free(azChar);
1139     }
1140   }
1141   sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT);
1142 }
1143
1144
1145 /* IMP: R-25361-16150 This function is omitted from SQLite by default. It
1146 ** is only available if the SQLITE_SOUNDEX compile-time option is used
1147 ** when SQLite is built.
1148 */
1149 #ifdef SQLITE_SOUNDEX
1150 /*
1151 ** Compute the soundex encoding of a word.
1152 **
1153 ** IMP: R-59782-00072 The soundex(X) function returns a string that is the
1154 ** soundex encoding of the string X.
1155 */
1156 static void soundexFunc(
1157   sqlite3_context *context,
1158   int argc,
1159   sqlite3_value **argv
1160 ){
1161   char zResult[8];
1162   const u8 *zIn;
1163   int i, j;
1164   static const unsigned char iCode[] = {
1165     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1166     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1167     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1168     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1169     0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1170     1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1171     0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1172     1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1173   };
1174   assert( argc==1 );
1175   zIn = (u8*)sqlite3_value_text(argv[0]);
1176   if( zIn==0 ) zIn = (u8*)"";
1177   for(i=0; zIn[i] && !sqlite3Isalpha(zIn[i]); i++){}
1178   if( zIn[i] ){
1179     u8 prevcode = iCode[zIn[i]&0x7f];
1180     zResult[0] = sqlite3Toupper(zIn[i]);
1181     for(j=1; j<4 && zIn[i]; i++){
1182       int code = iCode[zIn[i]&0x7f];
1183       if( code>0 ){
1184         if( code!=prevcode ){
1185           prevcode = code;
1186           zResult[j++] = code + '0';
1187         }
1188       }else{
1189         prevcode = 0;
1190       }
1191     }
1192     while( j<4 ){
1193       zResult[j++] = '0';
1194     }
1195     zResult[j] = 0;
1196     sqlite3_result_text(context, zResult, 4, SQLITE_TRANSIENT);
1197   }else{
1198     /* IMP: R-64894-50321 The string "?000" is returned if the argument
1199     ** is NULL or contains no ASCII alphabetic characters. */
1200     sqlite3_result_text(context, "?000", 4, SQLITE_STATIC);
1201   }
1202 }
1203 #endif /* SQLITE_SOUNDEX */
1204
1205 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1206 /*
1207 ** A function that loads a shared-library extension then returns NULL.
1208 */
1209 static void loadExt(sqlite3_context *context, int argc, sqlite3_value **argv){
1210   const char *zFile = (const char *)sqlite3_value_text(argv[0]);
1211   const char *zProc;
1212   sqlite3 *db = sqlite3_context_db_handle(context);
1213   char *zErrMsg = 0;
1214
1215   if( argc==2 ){
1216     zProc = (const char *)sqlite3_value_text(argv[1]);
1217   }else{
1218     zProc = 0;
1219   }
1220   if( zFile && sqlite3_load_extension(db, zFile, zProc, &zErrMsg) ){
1221     sqlite3_result_error(context, zErrMsg, -1);
1222     sqlite3_free(zErrMsg);
1223   }
1224 }
1225 #endif
1226
1227
1228 /*
1229 ** An instance of the following structure holds the context of a
1230 ** sum() or avg() aggregate computation.
1231 */
1232 typedef struct SumCtx SumCtx;
1233 struct SumCtx {
1234   double rSum;      /* Floating point sum */
1235   i64 iSum;         /* Integer sum */
1236   i64 cnt;          /* Number of elements summed */
1237   u8 overflow;      /* True if integer overflow seen */
1238   u8 approx;        /* True if non-integer value was input to the sum */
1239 };
1240
1241 /*
1242 ** Routines used to compute the sum, average, and total.
1243 **
1244 ** The SUM() function follows the (broken) SQL standard which means
1245 ** that it returns NULL if it sums over no inputs.  TOTAL returns
1246 ** 0.0 in that case.  In addition, TOTAL always returns a float where
1247 ** SUM might return an integer if it never encounters a floating point
1248 ** value.  TOTAL never fails, but SUM might through an exception if
1249 ** it overflows an integer.
1250 */
1251 static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
1252   SumCtx *p;
1253   int type;
1254   assert( argc==1 );
1255   UNUSED_PARAMETER(argc);
1256   p = sqlite3_aggregate_context(context, sizeof(*p));
1257   type = sqlite3_value_numeric_type(argv[0]);
1258   if( p && type!=SQLITE_NULL ){
1259     p->cnt++;
1260     if( type==SQLITE_INTEGER ){
1261       i64 v = sqlite3_value_int64(argv[0]);
1262       p->rSum += v;
1263       if( (p->approx|p->overflow)==0 && sqlite3AddInt64(&p->iSum, v) ){
1264         p->overflow = 1;
1265       }
1266     }else{
1267       p->rSum += sqlite3_value_double(argv[0]);
1268       p->approx = 1;
1269     }
1270   }
1271 }
1272 static void sumFinalize(sqlite3_context *context){
1273   SumCtx *p;
1274   p = sqlite3_aggregate_context(context, 0);
1275   if( p && p->cnt>0 ){
1276     if( p->overflow ){
1277       sqlite3_result_error(context,"integer overflow",-1);
1278     }else if( p->approx ){
1279       sqlite3_result_double(context, p->rSum);
1280     }else{
1281       sqlite3_result_int64(context, p->iSum);
1282     }
1283   }
1284 }
1285 static void avgFinalize(sqlite3_context *context){
1286   SumCtx *p;
1287   p = sqlite3_aggregate_context(context, 0);
1288   if( p && p->cnt>0 ){
1289     sqlite3_result_double(context, p->rSum/(double)p->cnt);
1290   }
1291 }
1292 static void totalFinalize(sqlite3_context *context){
1293   SumCtx *p;
1294   p = sqlite3_aggregate_context(context, 0);
1295   /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
1296   sqlite3_result_double(context, p ? p->rSum : (double)0);
1297 }
1298
1299 /*
1300 ** The following structure keeps track of state information for the
1301 ** count() aggregate function.
1302 */
1303 typedef struct CountCtx CountCtx;
1304 struct CountCtx {
1305   i64 n;
1306 };
1307
1308 /*
1309 ** Routines to implement the count() aggregate function.
1310 */
1311 static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
1312   CountCtx *p;
1313   p = sqlite3_aggregate_context(context, sizeof(*p));
1314   if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
1315     p->n++;
1316   }
1317
1318 #ifndef SQLITE_OMIT_DEPRECATED
1319   /* The sqlite3_aggregate_count() function is deprecated.  But just to make
1320   ** sure it still operates correctly, verify that its count agrees with our
1321   ** internal count when using count(*) and when the total count can be
1322   ** expressed as a 32-bit integer. */
1323   assert( argc==1 || p==0 || p->n>0x7fffffff
1324           || p->n==sqlite3_aggregate_count(context) );
1325 #endif
1326 }
1327 static void countFinalize(sqlite3_context *context){
1328   CountCtx *p;
1329   p = sqlite3_aggregate_context(context, 0);
1330   sqlite3_result_int64(context, p ? p->n : 0);
1331 }
1332
1333 /*
1334 ** Routines to implement min() and max() aggregate functions.
1335 */
1336 static void minmaxStep(
1337   sqlite3_context *context,
1338   int NotUsed,
1339   sqlite3_value **argv
1340 ){
1341   Mem *pArg  = (Mem *)argv[0];
1342   Mem *pBest;
1343   UNUSED_PARAMETER(NotUsed);
1344
1345   pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
1346   if( !pBest ) return;
1347
1348   if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
1349     if( pBest->flags ) sqlite3SkipAccumulatorLoad(context);
1350   }else if( pBest->flags ){
1351     int max;
1352     int cmp;
1353     CollSeq *pColl = sqlite3GetFuncCollSeq(context);
1354     /* This step function is used for both the min() and max() aggregates,
1355     ** the only difference between the two being that the sense of the
1356     ** comparison is inverted. For the max() aggregate, the
1357     ** sqlite3_user_data() function returns (void *)-1. For min() it
1358     ** returns (void *)db, where db is the sqlite3* database pointer.
1359     ** Therefore the next statement sets variable 'max' to 1 for the max()
1360     ** aggregate, or 0 for min().
1361     */
1362     max = sqlite3_user_data(context)!=0;
1363     cmp = sqlite3MemCompare(pBest, pArg, pColl);
1364     if( (max && cmp<0) || (!max && cmp>0) ){
1365       sqlite3VdbeMemCopy(pBest, pArg);
1366     }else{
1367       sqlite3SkipAccumulatorLoad(context);
1368     }
1369   }else{
1370     sqlite3VdbeMemCopy(pBest, pArg);
1371   }
1372 }
1373 static void minMaxFinalize(sqlite3_context *context){
1374   sqlite3_value *pRes;
1375   pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0);
1376   if( pRes ){
1377     if( pRes->flags ){
1378       sqlite3_result_value(context, pRes);
1379     }
1380     sqlite3VdbeMemRelease(pRes);
1381   }
1382 }
1383
1384 /*
1385 ** group_concat(EXPR, ?SEPARATOR?)
1386 */
1387 static void groupConcatStep(
1388   sqlite3_context *context,
1389   int argc,
1390   sqlite3_value **argv
1391 ){
1392   const char *zVal;
1393   StrAccum *pAccum;
1394   const char *zSep;
1395   int nVal, nSep;
1396   assert( argc==1 || argc==2 );
1397   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
1398   pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
1399
1400   if( pAccum ){
1401     sqlite3 *db = sqlite3_context_db_handle(context);
1402     int firstTerm = pAccum->useMalloc==0;
1403     pAccum->useMalloc = 2;
1404     pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH];
1405     if( !firstTerm ){
1406       if( argc==2 ){
1407         zSep = (char*)sqlite3_value_text(argv[1]);
1408         nSep = sqlite3_value_bytes(argv[1]);
1409       }else{
1410         zSep = ",";
1411         nSep = 1;
1412       }
1413       sqlite3StrAccumAppend(pAccum, zSep, nSep);
1414     }
1415     zVal = (char*)sqlite3_value_text(argv[0]);
1416     nVal = sqlite3_value_bytes(argv[0]);
1417     sqlite3StrAccumAppend(pAccum, zVal, nVal);
1418   }
1419 }
1420 static void groupConcatFinalize(sqlite3_context *context){
1421   StrAccum *pAccum;
1422   pAccum = sqlite3_aggregate_context(context, 0);
1423   if( pAccum ){
1424     if( pAccum->tooBig ){
1425       sqlite3_result_error_toobig(context);
1426     }else if( pAccum->mallocFailed ){
1427       sqlite3_result_error_nomem(context);
1428     }else{
1429       sqlite3_result_text(context, sqlite3StrAccumFinish(pAccum), -1,
1430                           sqlite3_free);
1431     }
1432   }
1433 }
1434
1435 /*
1436 ** This routine does per-connection function registration.  Most
1437 ** of the built-in functions above are part of the global function set.
1438 ** This routine only deals with those that are not global.
1439 */
1440 void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
1441   int rc = sqlite3_overload_function(db, "MATCH", 2);
1442 #ifndef OMIT_EXPORT
1443   extern void sqlcipher_exportFunc(sqlite3_context *, int, sqlite3_value **);
1444 #endif
1445   assert( rc==SQLITE_NOMEM || rc==SQLITE_OK );
1446   if( rc==SQLITE_NOMEM ){
1447     db->mallocFailed = 1;
1448   }
1449 #ifndef OMIT_EXPORT
1450   sqlite3CreateFunc(db, "sqlcipher_export", 1, SQLITE_TEXT, 0, sqlcipher_exportFunc, 0, 0, 0);
1451 #endif
1452 }
1453
1454 /*
1455 ** Set the LIKEOPT flag on the 2-argument function with the given name.
1456 */
1457 static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
1458   FuncDef *pDef;
1459   pDef = sqlite3FindFunction(db, zName, sqlite3Strlen30(zName),
1460                              2, SQLITE_UTF8, 0);
1461   if( ALWAYS(pDef) ){
1462     pDef->flags = flagVal;
1463   }
1464 }
1465
1466 /*
1467 ** Register the built-in LIKE and GLOB functions.  The caseSensitive
1468 ** parameter determines whether or not the LIKE operator is case
1469 ** sensitive.  GLOB is always case sensitive.
1470 */
1471 void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
1472   struct compareInfo *pInfo;
1473   if( caseSensitive ){
1474     pInfo = (struct compareInfo*)&likeInfoAlt;
1475   }else{
1476     pInfo = (struct compareInfo*)&likeInfoNorm;
1477   }
1478   sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
1479   sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
1480   sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8,
1481       (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0);
1482   setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE);
1483   setLikeOptFlag(db, "like",
1484       caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE);
1485 }
1486
1487 /*
1488 ** pExpr points to an expression which implements a function.  If
1489 ** it is appropriate to apply the LIKE optimization to that function
1490 ** then set aWc[0] through aWc[2] to the wildcard characters and
1491 ** return TRUE.  If the function is not a LIKE-style function then
1492 ** return FALSE.
1493 */
1494 int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
1495   FuncDef *pDef;
1496   if( pExpr->op!=TK_FUNCTION
1497    || !pExpr->x.pList
1498    || pExpr->x.pList->nExpr!=2
1499   ){
1500     return 0;
1501   }
1502   assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
1503   pDef = sqlite3FindFunction(db, pExpr->u.zToken,
1504                              sqlite3Strlen30(pExpr->u.zToken),
1505                              2, SQLITE_UTF8, 0);
1506   if( NEVER(pDef==0) || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){
1507     return 0;
1508   }
1509
1510   /* The memcpy() statement assumes that the wildcard characters are
1511   ** the first three statements in the compareInfo structure.  The
1512   ** asserts() that follow verify that assumption
1513   */
1514   memcpy(aWc, pDef->pUserData, 3);
1515   assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
1516   assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
1517   assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
1518   *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0;
1519   return 1;
1520 }
1521
1522 /*
1523 ** All all of the FuncDef structures in the aBuiltinFunc[] array above
1524 ** to the global function hash table.  This occurs at start-time (as
1525 ** a consequence of calling sqlite3_initialize()).
1526 **
1527 ** After this routine runs
1528 */
1529 void sqlite3RegisterGlobalFunctions(void){
1530   /*
1531   ** The following array holds FuncDef structures for all of the functions
1532   ** defined in this file.
1533   **
1534   ** The array cannot be constant since changes are made to the
1535   ** FuncDef.pHash elements at start-time.  The elements of this array
1536   ** are read-only after initialization is complete.
1537   */
1538   static SQLITE_WSD FuncDef aBuiltinFunc[] = {
1539     FUNCTION(ltrim,              1, 1, 0, trimFunc         ),
1540     FUNCTION(ltrim,              2, 1, 0, trimFunc         ),
1541     FUNCTION(rtrim,              1, 2, 0, trimFunc         ),
1542     FUNCTION(rtrim,              2, 2, 0, trimFunc         ),
1543     FUNCTION(trim,               1, 3, 0, trimFunc         ),
1544     FUNCTION(trim,               2, 3, 0, trimFunc         ),
1545     FUNCTION(min,               -1, 0, 1, minmaxFunc       ),
1546     FUNCTION(min,                0, 0, 1, 0                ),
1547     AGGREGATE(min,               1, 0, 1, minmaxStep,      minMaxFinalize ),
1548     FUNCTION(max,               -1, 1, 1, minmaxFunc       ),
1549     FUNCTION(max,                0, 1, 1, 0                ),
1550     AGGREGATE(max,               1, 1, 1, minmaxStep,      minMaxFinalize ),
1551     FUNCTION2(typeof,            1, 0, 0, typeofFunc,  SQLITE_FUNC_TYPEOF),
1552     FUNCTION2(length,            1, 0, 0, lengthFunc,  SQLITE_FUNC_LENGTH),
1553     FUNCTION(substr,             2, 0, 0, substrFunc       ),
1554     FUNCTION(substr,             3, 0, 0, substrFunc       ),
1555     FUNCTION(abs,                1, 0, 0, absFunc          ),
1556 #ifndef SQLITE_OMIT_FLOATING_POINT
1557     FUNCTION(round,              1, 0, 0, roundFunc        ),
1558     FUNCTION(round,              2, 0, 0, roundFunc        ),
1559 #endif
1560     FUNCTION(upper,              1, 0, 0, upperFunc        ),
1561     FUNCTION(lower,              1, 0, 0, lowerFunc        ),
1562     FUNCTION(coalesce,           1, 0, 0, 0                ),
1563     FUNCTION(coalesce,           0, 0, 0, 0                ),
1564     FUNCTION2(coalesce,         -1, 0, 0, ifnullFunc,  SQLITE_FUNC_COALESCE),
1565     FUNCTION(hex,                1, 0, 0, hexFunc          ),
1566     FUNCTION2(ifnull,            2, 0, 0, ifnullFunc,  SQLITE_FUNC_COALESCE),
1567     FUNCTION(random,             0, 0, 0, randomFunc       ),
1568     FUNCTION(randomblob,         1, 0, 0, randomBlob       ),
1569     FUNCTION(nullif,             2, 0, 1, nullifFunc       ),
1570     FUNCTION(sqlite_version,     0, 0, 0, versionFunc      ),
1571     FUNCTION(sqlite_source_id,   0, 0, 0, sourceidFunc     ),
1572     FUNCTION(sqlite_log,         2, 0, 0, errlogFunc       ),
1573 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
1574     FUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc  ),
1575     FUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc  ),
1576 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
1577     FUNCTION(quote,              1, 0, 0, quoteFunc        ),
1578     FUNCTION(last_insert_rowid,  0, 0, 0, last_insert_rowid),
1579     FUNCTION(changes,            0, 0, 0, changes          ),
1580     FUNCTION(total_changes,      0, 0, 0, total_changes    ),
1581     FUNCTION(replace,            3, 0, 0, replaceFunc      ),
1582     FUNCTION(zeroblob,           1, 0, 0, zeroblobFunc     ),
1583   #ifdef SQLITE_SOUNDEX
1584     FUNCTION(soundex,            1, 0, 0, soundexFunc      ),
1585   #endif
1586   #ifndef SQLITE_OMIT_LOAD_EXTENSION
1587     FUNCTION(load_extension,     1, 0, 0, loadExt          ),
1588     FUNCTION(load_extension,     2, 0, 0, loadExt          ),
1589   #endif
1590     AGGREGATE(sum,               1, 0, 0, sumStep,         sumFinalize    ),
1591     AGGREGATE(total,             1, 0, 0, sumStep,         totalFinalize    ),
1592     AGGREGATE(avg,               1, 0, 0, sumStep,         avgFinalize    ),
1593  /* AGGREGATE(count,             0, 0, 0, countStep,       countFinalize  ), */
1594     {0,SQLITE_UTF8,SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0},
1595     AGGREGATE(count,             1, 0, 0, countStep,       countFinalize  ),
1596     AGGREGATE(group_concat,      1, 0, 0, groupConcatStep, groupConcatFinalize),
1597     AGGREGATE(group_concat,      2, 0, 0, groupConcatStep, groupConcatFinalize),
1598
1599     LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1600   #ifdef SQLITE_CASE_SENSITIVE_LIKE
1601     LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1602     LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1603   #else
1604     LIKEFUNC(like, 2, &likeInfoNorm, SQLITE_FUNC_LIKE),
1605     LIKEFUNC(like, 3, &likeInfoNorm, SQLITE_FUNC_LIKE),
1606   #endif
1607   };
1608
1609   int i;
1610   FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);
1611   FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aBuiltinFunc);
1612
1613   for(i=0; i<ArraySize(aBuiltinFunc); i++){
1614     sqlite3FuncDefInsert(pHash, &aFunc[i]);
1615   }
1616   sqlite3RegisterDateTimeFunctions();
1617 #ifndef SQLITE_OMIT_ALTERTABLE
1618   sqlite3AlterFunctions();
1619 #endif
1620 }