/ Check-in [adbe31ad]
Login

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

Overview
Comment:Min() and max() functions honor the distinction between TEXT and NUMERIC data. Ticket #623. typeof() is now a user function. Some tests are now failing due to ticket #521. (CVS 1272)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:adbe31adf1ad0ca723203ca3d7dc480324c60d43
User & Date: drh 2004-02-25 13:47:31
Context
2004-02-25
22:51
Add comments and prototype for experimental sqlite_last_statement_changes() API function. Also, allow function to be called from tcl. (CVS 1273) check-in: ca99920b user: rdc tags: trunk
13:47
Min() and max() functions honor the distinction between TEXT and NUMERIC data. Ticket #623. typeof() is now a user function. Some tests are now failing due to ticket #521. (CVS 1272) check-in: adbe31ad user: drh tags: trunk
02:33
Fix the return type of sqliteRunVacuum. Ticket #627. (CVS 1271) check-in: 9c9f4a86 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/copy.c.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the COPY command.
**
** $Id: copy.c,v 1.8 2004/02/24 01:05:32 drh Exp $
*/
#include "sqliteInt.h"

/*
** The COPY command is for compatibility with PostgreSQL and specificially
** for the ability to read the output of pg_dump.  The format is as
** follows:
................................................................................
  Token *pDelimiter,   /* Use this as the field delimiter */
  int onError          /* What to do if a constraint fails */
){
  Table *pTab;
  int i;
  Vdbe *v;
  int addr, end;
  Index *pIdx;
  char *zFile = 0;
  const char *zDb;
  sqlite *db = pParse->db;


  if( sqlite_malloc_failed  ) goto copy_cleanup;
  assert( pTableName->nSrc==1 );







|







 







<







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
33
34
35
36
37
38
39

40
41
42
43
44
45
46
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the COPY command.
**
** $Id: copy.c,v 1.9 2004/02/25 13:47:31 drh Exp $
*/
#include "sqliteInt.h"

/*
** The COPY command is for compatibility with PostgreSQL and specificially
** for the ability to read the output of pg_dump.  The format is as
** follows:
................................................................................
  Token *pDelimiter,   /* Use this as the field delimiter */
  int onError          /* What to do if a constraint fails */
){
  Table *pTab;
  int i;
  Vdbe *v;
  int addr, end;

  char *zFile = 0;
  const char *zDb;
  sqlite *db = pParse->db;


  if( sqlite_malloc_failed  ) goto copy_cleanup;
  assert( pTableName->nSrc==1 );

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
...
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
....
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
....
1265
1266
1267
1268
1269
1270
1271






























1272
1273
1274
1275
1276
1277
1278
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.111 2004/02/22 20:05:01 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
  if( pExpr==0 ) return 0;
  switch( pExpr->op ){
    case TK_GLOB:
    case TK_LIKE:
    case TK_FUNCTION: {
      int n = pExpr->pList ? pExpr->pList->nExpr : 0;  /* Number of arguments */
      int no_such_func = 0;       /* True if no such function exists */
      int is_type_of = 0;         /* True if is the special TypeOf() function */
      int wrong_num_args = 0;     /* True if wrong number of arguments */
      int is_agg = 0;             /* True if is an aggregate function */
      int i;
      int nId;                    /* Number of characters in function name */
      const char *zId;            /* The function name. */
      FuncDef *pDef;

      getFunctionName(pExpr, &zId, &nId);
      pDef = sqliteFindFunction(pParse->db, zId, nId, n, 0);
      if( pDef==0 ){
        pDef = sqliteFindFunction(pParse->db, zId, nId, -1, 0);
        if( pDef==0 ){
          if( n==1 && nId==6 && sqliteStrNICmp(zId, "typeof", 6)==0 ){
            is_type_of = 1;
          }else {
            no_such_func = 1;
          }
        }else{
          wrong_num_args = 1;
        }
      }else{
        is_agg = pDef->xFunc==0;
      }
      if( is_agg && !allowAgg ){
................................................................................
        if( pIsAgg ) *pIsAgg = 1;
      }
      for(i=0; nErr==0 && i<n; i++){
        nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr,
                               allowAgg && !is_agg, pIsAgg);
      }
      if( pDef==0 ){
        if( is_type_of ){
          pExpr->op = TK_STRING;
          if( sqliteExprType(pExpr->pList->a[0].pExpr)==SQLITE_SO_NUM ){
            pExpr->token.z = "numeric";
            pExpr->token.n = 7;
          }else{
            pExpr->token.z = "text";
            pExpr->token.n = 4;
          }
        }
      }else if( pDef->dataType>=0 ){
        if( pDef->dataType<n ){
          pExpr->dataType = 
             sqliteExprType(pExpr->pList->a[pDef->dataType].pExpr);
        }else{
          pExpr->dataType = SQLITE_SO_NUM;
        }
................................................................................
    case TK_AGG_FUNCTION: {
      sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg);
      break;
    }
    case TK_GLOB:
    case TK_LIKE:
    case TK_FUNCTION: {
      int i;
      ExprList *pList = pExpr->pList;
      int nExpr = pList ? pList->nExpr : 0;
      FuncDef *pDef;
      int nId;
      const char *zId;
      getFunctionName(pExpr, &zId, &nId);
      pDef = sqliteFindFunction(pParse->db, zId, nId, nExpr, 0);
      assert( pDef!=0 );
      for(i=0; i<nExpr; i++){
        sqliteExprCode(pParse, pList->a[i].pExpr);
      }
      sqliteVdbeOp3(v, OP_Function, nExpr, 0, (char*)pDef, P3_POINTER);
      break;
    }
    case TK_SELECT: {
      sqliteVdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);
      break;
    }
................................................................................
	  sqliteVdbeOp3(v, OP_Goto, 0, pParse->trigStack->ignoreJump,
                           "(IGNORE jump)", 0);
      }
    }
    break;
  }
}































/*
** Generate code for a boolean expression such that a jump is made
** to the label "dest" if the expression is true but execution
** continues straight thru if the expression is false.
**
** If the expression evaluates to NULL (neither true nor false), then







|







 







<












<
<
<
|
<







 







|
<
<
<
<
<
<
<
<
<







 







<








|
<
<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
819
820
821
822
823
824
825

826
827
828
829
830
831
832
833
834
835
836
837



838

839
840
841
842
843
844
845
...
859
860
861
862
863
864
865
866









867
868
869
870
871
872
873
....
1137
1138
1139
1140
1141
1142
1143

1144
1145
1146
1147
1148
1149
1150
1151
1152


1153
1154
1155
1156
1157
1158
1159
....
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.112 2004/02/25 13:47:31 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
  if( pExpr==0 ) return 0;
  switch( pExpr->op ){
    case TK_GLOB:
    case TK_LIKE:
    case TK_FUNCTION: {
      int n = pExpr->pList ? pExpr->pList->nExpr : 0;  /* Number of arguments */
      int no_such_func = 0;       /* True if no such function exists */

      int wrong_num_args = 0;     /* True if wrong number of arguments */
      int is_agg = 0;             /* True if is an aggregate function */
      int i;
      int nId;                    /* Number of characters in function name */
      const char *zId;            /* The function name. */
      FuncDef *pDef;

      getFunctionName(pExpr, &zId, &nId);
      pDef = sqliteFindFunction(pParse->db, zId, nId, n, 0);
      if( pDef==0 ){
        pDef = sqliteFindFunction(pParse->db, zId, nId, -1, 0);
        if( pDef==0 ){



          no_such_func = 1;

        }else{
          wrong_num_args = 1;
        }
      }else{
        is_agg = pDef->xFunc==0;
      }
      if( is_agg && !allowAgg ){
................................................................................
        if( pIsAgg ) *pIsAgg = 1;
      }
      for(i=0; nErr==0 && i<n; i++){
        nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr,
                               allowAgg && !is_agg, pIsAgg);
      }
      if( pDef==0 ){
        /* Already reported an error */









      }else if( pDef->dataType>=0 ){
        if( pDef->dataType<n ){
          pExpr->dataType = 
             sqliteExprType(pExpr->pList->a[pDef->dataType].pExpr);
        }else{
          pExpr->dataType = SQLITE_SO_NUM;
        }
................................................................................
    case TK_AGG_FUNCTION: {
      sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg);
      break;
    }
    case TK_GLOB:
    case TK_LIKE:
    case TK_FUNCTION: {

      ExprList *pList = pExpr->pList;
      int nExpr = pList ? pList->nExpr : 0;
      FuncDef *pDef;
      int nId;
      const char *zId;
      getFunctionName(pExpr, &zId, &nId);
      pDef = sqliteFindFunction(pParse->db, zId, nId, nExpr, 0);
      assert( pDef!=0 );
      nExpr = sqliteExprCodeExprList(pParse, pList, pDef->includeTypes);


      sqliteVdbeOp3(v, OP_Function, nExpr, 0, (char*)pDef, P3_POINTER);
      break;
    }
    case TK_SELECT: {
      sqliteVdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);
      break;
    }
................................................................................
	  sqliteVdbeOp3(v, OP_Goto, 0, pParse->trigStack->ignoreJump,
                           "(IGNORE jump)", 0);
      }
    }
    break;
  }
}

/*
** Generate code that pushes the value of every element of the given
** expression list onto the stack.  If the includeTypes flag is true,
** then also push a string that is the datatype of each element onto
** the stack after the value.
**
** Return the number of elements pushed onto the stack.
*/
int sqliteExprCodeExprList(
  Parse *pParse,     /* Parsing context */
  ExprList *pList,   /* The expression list to be coded */
  int includeTypes   /* TRUE to put datatypes on the stack too */
){
  struct ExprList_item *pItem;
  int i, n;
  Vdbe *v;
  if( pList==0 ) return 0;
  v = sqliteGetVdbe(pParse);
  n = pList->nExpr;
  for(pItem=pList->a, i=0; i<n; i++, pItem++){
    sqliteExprCode(pParse, pItem->pExpr);
    if( includeTypes ){
      sqliteVdbeOp3(v, OP_String, 0, 0, 
         sqliteExprType(pItem->pExpr)==SQLITE_SO_NUM ? "numeric" : "text",
         P3_STATIC);
    }
  }
  return includeTypes ? n*2 : n;
}

/*
** Generate code for a boolean expression such that a jump is made
** to the label "dest" if the expression is true but execution
** continues straight thru if the expression is false.
**
** If the expression evaluates to NULL (neither true nor false), then

Changes to src/func.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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
61
62
63
64
65
66
...
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516



517
518
519
520

521
522
523
524
525
526

527
528
529
530
531
532
533
534
535
536
...
558
559
560
561
562
563
564
565
566

567
568
569
570
571
572

573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589




590
591
592
593
594
595
596
597
598
599
600

601
602
603
604
605
606
607
608
609
610
611
612
613

614
615
616

617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632

633
634
635








636
637
638
** 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.41 2004/02/22 17:49:34 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "os.h"

/*
** Implementation of the non-aggregate min() and max() functions
*/
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);
}

/*
** Implementation of the length() function
*/
static void lengthFunc(sqlite_func *context, int argc, const char **argv){
  const char *z;
................................................................................
  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 || argv[0]==0 ) return;
  if( p->z==0 || sqliteCompare(argv[0],p->z)<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] = 1;



    }else{
      p->z = sqliteMalloc( len+1 );
      p->zBuf[0] = 0;
      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 || argv[0]==0 ) return;
  if( p->z==0 || sqliteCompare(argv[0],p->z)>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];
................................................................................
** This function registered all of the above C functions as SQL
** functions.  This should be the only routine in this file with
** external linkage.
*/
void sqliteRegisterBuiltinFunctions(sqlite *db){
  static struct {
     char *zName;
     int nArg;
     int dataType;

     void (*xFunc)(sqlite_func*,int,const char**);
  } aFuncs[] = {
    { "min",       -1, SQLITE_ARGS,    minFunc    },
    { "min",        0, 0,              0          },
    { "max",       -1, SQLITE_ARGS,    maxFunc    },
    { "max",        0, 0,              0          },

    { "length",     1, SQLITE_NUMERIC, lengthFunc },
    { "substr",     3, SQLITE_TEXT,    substrFunc },
    { "abs",        1, SQLITE_NUMERIC, absFunc    },
    { "round",      1, SQLITE_NUMERIC, roundFunc  },
    { "round",      2, SQLITE_NUMERIC, roundFunc  },
    { "upper",      1, SQLITE_TEXT,    upperFunc  },
    { "lower",      1, SQLITE_TEXT,    lowerFunc  },
    { "coalesce",  -1, SQLITE_ARGS,    ifnullFunc },
    { "coalesce",   0, 0,              0          },
    { "coalesce",   1, 0,              0          },
    { "ifnull",     2, SQLITE_ARGS,    ifnullFunc },
    { "random",    -1, SQLITE_NUMERIC, randomFunc },
    { "like",       2, SQLITE_NUMERIC, likeFunc   },
    { "glob",       2, SQLITE_NUMERIC, globFunc   },
    { "nullif",     2, SQLITE_ARGS,    nullifFunc },
    { "sqlite_version",0,SQLITE_TEXT,  versionFunc},
    { "quote",      1, SQLITE_ARGS,    quoteFunc  },




#ifdef SQLITE_SOUNDEX
    { "soundex",    1, SQLITE_TEXT,    soundexFunc},
#endif
#ifdef SQLITE_TEST
    { "randstr",    2, SQLITE_TEXT,    randStr    },
#endif
  };
  static struct {
    char *zName;
    int nArg;
    int dataType;

    void (*xStep)(sqlite_func*,int,const char**);
    void (*xFinalize)(sqlite_func*);
  } aAggs[] = {
    { "min",    1, 0,              minStep,      minMaxFinalize },
    { "max",    1, 0,              maxStep,      minMaxFinalize },
    { "sum",    1, SQLITE_NUMERIC, sumStep,      sumFinalize    },
    { "avg",    1, SQLITE_NUMERIC, sumStep,      avgFinalize    },
    { "count",  0, SQLITE_NUMERIC, countStep,    countFinalize  },
    { "count",  1, SQLITE_NUMERIC, countStep,    countFinalize  },
#if 0
    { "stddev", 1, SQLITE_NUMERIC, stdDevStep,   stdDevFinalize },
#endif
  };

  int i;

  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){

    sqlite_create_function(db, aFuncs[i].zName,
           aFuncs[i].nArg, aFuncs[i].xFunc, 0);
    if( aFuncs[i].xFunc ){
      sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
    }
  }
  sqlite_create_function(db, "last_insert_rowid", 0, 
           last_insert_rowid, db);
  sqlite_function_type(db, "last_insert_rowid", SQLITE_NUMERIC);
  sqlite_create_function(db, "change_count", 0, change_count, db);
  sqlite_function_type(db, "change_count", SQLITE_NUMERIC);
  sqlite_create_function(db, "last_statement_change_count", 0, 
           last_statement_change_count, db);
  sqlite_function_type(db, "last_statement_change_count", SQLITE_NUMERIC);

  for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){

    sqlite_create_aggregate(db, aAggs[i].zName,
           aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0);
    sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);








  }
  sqliteRegisterDateTimeFunctions(db);
}







|











|


>
>


>


>
>
>
>
>
|

|





|
|
|
|
|
|
|
<
<
<
<
<
<
<







 







|

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


|







 







|
|
>


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

|


|




|
|
>



|
|
|
|
|
|

|


>



>

|




<
<
<
<
<
<
<
<
<

>

|

>
>
>
>
>
>
>
>



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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







61
62
63
64
65
66
67
...
498
499
500
501
502
503
504
505
506
507
508




509




510
511
512
513



514
515





516
517
518
519
520
521
522
523
524
525
526
...
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621









622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
** 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.42 2004/02/25 13:47:32 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "os.h"

/*
** Implementation of the non-aggregate min() and max() functions
*/
static void minmaxFunc(sqlite_func *context, int argc, const char **argv){
  const char *zBest; 
  int i;
  int (*xCompare)(const char*, const char*);
  int mask;    /* 0 for min() or 0xffffffff for max() */

  if( argc==0 ) return;
  mask = (int)sqlite_user_data(context);
  zBest = argv[0];
  if( zBest==0 ) return;
  if( argv[1][0]=='n' ){
    xCompare = sqliteCompare;
  }else{
    xCompare = strcmp;
  }
  for(i=2; i<argc; i+=2){
    if( argv[i]==0 ) return;
    if( (xCompare(argv[i], zBest)^mask)<0 ){
      zBest = argv[i];
    }
  }
  sqlite_set_result_string(context, zBest, -1);
}

/*
** Return the type of the argument.
*/
static void typeofFunc(sqlite_func *context, int argc, const char **argv){
  assert( argc==2 );
  sqlite_set_result_string(context, argv[1], -1);







}

/*
** Implementation of the length() function
*/
static void lengthFunc(sqlite_func *context, int argc, const char **argv){
  const char *z;
................................................................................
  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 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);
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 || argv[0]==0 ) 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];
................................................................................
** This function registered all of the above C functions as SQL
** functions.  This should be the only routine in this file with
** external linkage.
*/
void sqliteRegisterBuiltinFunctions(sqlite *db){
  static struct {
     char *zName;
     signed char nArg;
     signed char dataType;
     u8 argType;               /* 0: none.  1: db  2: (-1) */
     void (*xFunc)(sqlite_func*,int,const char**);
  } aFuncs[] = {
    { "min",       -1, SQLITE_ARGS,    0, minmaxFunc },
    { "min",        0, 0,              0, 0          },
    { "max",       -1, SQLITE_ARGS,    2, minmaxFunc },
    { "max",        0, 0,              2, 0          },
    { "typeof",     1, SQLITE_TEXT,    0, typeofFunc },
    { "length",     1, SQLITE_NUMERIC, 0, lengthFunc },
    { "substr",     3, SQLITE_TEXT,    0, substrFunc },
    { "abs",        1, SQLITE_NUMERIC, 0, absFunc    },
    { "round",      1, SQLITE_NUMERIC, 0, roundFunc  },
    { "round",      2, SQLITE_NUMERIC, 0, roundFunc  },
    { "upper",      1, SQLITE_TEXT,    0, upperFunc  },
    { "lower",      1, SQLITE_TEXT,    0, lowerFunc  },
    { "coalesce",  -1, SQLITE_ARGS,    0, ifnullFunc },
    { "coalesce",   0, 0,              0, 0          },
    { "coalesce",   1, 0,              0, 0          },
    { "ifnull",     2, SQLITE_ARGS,    0, ifnullFunc },
    { "random",    -1, SQLITE_NUMERIC, 0, randomFunc },
    { "like",       2, SQLITE_NUMERIC, 0, likeFunc   },
    { "glob",       2, SQLITE_NUMERIC, 0, globFunc   },
    { "nullif",     2, SQLITE_ARGS,    0, nullifFunc },
    { "sqlite_version",0,SQLITE_TEXT,  0, versionFunc},
    { "quote",      1, SQLITE_ARGS,    0, quoteFunc  },
    { "last_insert_rowid", 0, SQLITE_NUMERIC, 1, last_insert_rowid },
    { "change_count",      0, SQLITE_NUMERIC, 1, change_count      },
    { "last_statement_change_count",
                           0, SQLITE_NUMERIC, 1, last_statement_change_count },
#ifdef SQLITE_SOUNDEX
    { "soundex",    1, SQLITE_TEXT,    0, soundexFunc},
#endif
#ifdef SQLITE_TEST
    { "randstr",    2, SQLITE_TEXT,    0, randStr    },
#endif
  };
  static struct {
    char *zName;
    signed char nArg;
    signed char dataType;
    u8 argType;
    void (*xStep)(sqlite_func*,int,const char**);
    void (*xFinalize)(sqlite_func*);
  } aAggs[] = {
    { "min",    1, 0,              0, minmaxStep,   minMaxFinalize },
    { "max",    1, 0,              2, minmaxStep,   minMaxFinalize },
    { "sum",    1, SQLITE_NUMERIC, 0, sumStep,      sumFinalize    },
    { "avg",    1, SQLITE_NUMERIC, 0, sumStep,      avgFinalize    },
    { "count",  0, SQLITE_NUMERIC, 0, countStep,    countFinalize  },
    { "count",  1, SQLITE_NUMERIC, 0, countStep,    countFinalize  },
#if 0
    { "stddev", 1, SQLITE_NUMERIC, 0, stdDevStep,   stdDevFinalize },
#endif
  };
  static const char *azTypeFuncs[] = { "min", "max", "typeof" };
  int i;

  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
    void *pArg = aFuncs[i].argType==2 ? (void*)(-1) : db;
    sqlite_create_function(db, aFuncs[i].zName,
           aFuncs[i].nArg, aFuncs[i].xFunc, pArg);
    if( aFuncs[i].xFunc ){
      sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
    }
  }









  for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
    void *pArg = aAggs[i].argType==2 ? (void*)(-1) : db;
    sqlite_create_aggregate(db, aAggs[i].zName,
           aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, pArg);
    sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);
  }
  for(i=0; i<sizeof(azTypeFuncs)/sizeof(azTypeFuncs[0]); i++){
    int n = strlen(azTypeFuncs[i]);
    FuncDef *p = sqliteHashFind(&db->aFunc, azTypeFuncs[i], n);
    while( p ){
      p->includeTypes = 1;
      p = p->pNext;
    }
  }
  sqliteRegisterDateTimeFunctions(db);
}

Changes to src/main.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967

968
969
970
971
972
973
974
...
982
983
984
985
986
987
988

989
990
991
992
993
994
995
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.159 2004/02/20 23:34:07 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
................................................................................
** Passing a NULL xFunc argument or NULL xStep and xFinalize arguments
** disables the function.  Calling sqlite_create_function() with the
** same name and number of arguments as a prior call to
** sqlite_create_aggregate() disables the prior call to
** sqlite_create_aggregate(), and vice versa.
**
** If nArg is -1 it means that this function will accept any number
** of arguments, including 0.
*/
int sqlite_create_function(
  sqlite *db,          /* Add the function to this database connection */
  const char *zName,   /* Name of the function to add */
  int nArg,            /* Number of arguments */
  void (*xFunc)(sqlite_func*,int,const char**),  /* The implementation */
  void *pUserData      /* User data */
){
  FuncDef *p;
  int nName;
  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;

  nName = strlen(zName);
  if( nName>255 ) return 1;
  p = sqliteFindFunction(db, zName, nName, nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = xFunc;
  p->xStep = 0;
  p->xFinalize = 0;
................................................................................
  void (*xStep)(sqlite_func*,int,const char**), /* The step function */
  void (*xFinalize)(sqlite_func*),              /* The finalizer */
  void *pUserData      /* User data */
){
  FuncDef *p;
  int nName;
  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;

  nName = strlen(zName);
  if( nName>255 ) return 1;
  p = sqliteFindFunction(db, zName, nName, nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = 0;
  p->xStep = xStep;
  p->xFinalize = xFinalize;







|







 







|











>







 







>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
...
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.160 2004/02/25 13:47:32 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
................................................................................
** Passing a NULL xFunc argument or NULL xStep and xFinalize arguments
** disables the function.  Calling sqlite_create_function() with the
** same name and number of arguments as a prior call to
** sqlite_create_aggregate() disables the prior call to
** sqlite_create_aggregate(), and vice versa.
**
** If nArg is -1 it means that this function will accept any number
** of arguments, including 0.  The maximum allowed value of nArg is 127.
*/
int sqlite_create_function(
  sqlite *db,          /* Add the function to this database connection */
  const char *zName,   /* Name of the function to add */
  int nArg,            /* Number of arguments */
  void (*xFunc)(sqlite_func*,int,const char**),  /* The implementation */
  void *pUserData      /* User data */
){
  FuncDef *p;
  int nName;
  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;
  if( nArg<-1 || nArg>127 ) return 1;
  nName = strlen(zName);
  if( nName>255 ) return 1;
  p = sqliteFindFunction(db, zName, nName, nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = xFunc;
  p->xStep = 0;
  p->xFinalize = 0;
................................................................................
  void (*xStep)(sqlite_func*,int,const char**), /* The step function */
  void (*xFinalize)(sqlite_func*),              /* The finalizer */
  void *pUserData      /* User data */
){
  FuncDef *p;
  int nName;
  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;
  if( nArg<-1 || nArg>127 ) return 1;
  nName = strlen(zName);
  if( nName>255 ) return 1;
  p = sqliteFindFunction(db, zName, nName, nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = 0;
  p->xStep = xStep;
  p->xFinalize = xFinalize;

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2305
2306
2307
2308
2309
2310
2311

2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
**    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.158 2004/02/22 20:05:01 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    }
  }

  /* If we are dealing with aggregates, then do the special aggregate
  ** processing.  
  */
  else{

    if( pGroupBy ){
      int lbl1;
      for(i=0; i<pGroupBy->nExpr; i++){
        sqliteExprCode(pParse, pGroupBy->a[i].pExpr);
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pGroupBy->nExpr, 0);
      if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pGroupBy);
      lbl1 = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_AggFocus, 0, lbl1);
      for(i=0; i<pParse->nAgg; i++){
        if( pParse->aAgg[i].isAgg ) continue;
        sqliteExprCode(pParse, pParse->aAgg[i].pExpr);
        sqliteVdbeAddOp(v, OP_AggSet, 0, i);
      }
      sqliteVdbeResolveLabel(v, lbl1);
    }
    for(i=0; i<pParse->nAgg; i++){
      Expr *pE;
      int j;
      if( !pParse->aAgg[i].isAgg ) continue;
      pE = pParse->aAgg[i].pExpr;
      assert( pE->op==TK_AGG_FUNCTION );
      if( pE->pList ){
        for(j=0; j<pE->pList->nExpr; j++){
          sqliteExprCode(pParse, pE->pList->a[j].pExpr);
        }
      }
      sqliteVdbeAddOp(v, OP_Integer, i, 0);
      sqliteVdbeAddOp(v, OP_AggFunc, 0, pE->pList ? pE->pList->nExpr : 0);
      assert( pParse->aAgg[i].pFunc!=0 );
      assert( pParse->aAgg[i].pFunc->xStep!=0 );
      sqliteVdbeChangeP3(v, -1, (char*)pParse->aAgg[i].pFunc, P3_POINTER);
    }
  }

  /* End the database scan loop.
  */
  sqliteWhereEnd(pWInfo);








|







 







>









|
|
|




|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342


2343
2344
2345
2346
2347
2348
2349
**    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.159 2004/02/25 13:47:33 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    }
  }

  /* If we are dealing with aggregates, then do the special aggregate
  ** processing.  
  */
  else{
    AggExpr *pAgg;
    if( pGroupBy ){
      int lbl1;
      for(i=0; i<pGroupBy->nExpr; i++){
        sqliteExprCode(pParse, pGroupBy->a[i].pExpr);
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pGroupBy->nExpr, 0);
      if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pGroupBy);
      lbl1 = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_AggFocus, 0, lbl1);
      for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
        if( pAgg->isAgg ) continue;
        sqliteExprCode(pParse, pAgg->pExpr);
        sqliteVdbeAddOp(v, OP_AggSet, 0, i);
      }
      sqliteVdbeResolveLabel(v, lbl1);
    }
    for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
      Expr *pE;
      int nExpr;
      FuncDef *pDef;
      if( !pAgg->isAgg ) continue;
      assert( pAgg->pFunc!=0 );
      assert( pAgg->pFunc->xStep!=0 );
      pDef = pAgg->pFunc;
      pE = pAgg->pExpr;
      assert( pE!=0 );
      assert( pE->op==TK_AGG_FUNCTION );
      nExpr = sqliteExprCodeExprList(pParse, pE->pList, pDef->includeTypes);
      sqliteVdbeAddOp(v, OP_Integer, i, 0);
      sqliteVdbeOp3(v, OP_AggFunc, 0, nExpr, (char*)pDef, P3_POINTER);


    }
  }

  /* End the database scan loop.
  */
  sqliteWhereEnd(pWInfo);

Changes to src/sqlite.h.in.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.57 2004/02/12 20:49:36 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.
................................................................................
  void *pUserData           /* Available via the sqlite_user_data() call */
);

/*
** Use the following routine to define the datatype returned by a
** user-defined function.  The second argument can be one of the
** constants SQLITE_NUMERIC, SQLITE_TEXT, or SQLITE_ARGS or it
** can be an integer greater than or equal to zero.  The datatype
** will be numeric or text (the only two types supported) if the
** argument is SQLITE_NUMERIC or SQLITE_TEXT.  If the argument is
** SQLITE_ARGS, then the datatype is numeric if any argument to the
** function is numeric and is text otherwise.  If the second argument
** is an integer, then the datatype of the result is the same as the
** parameter to the function that corresponds to that integer.
*/
int sqlite_function_type(
  sqlite *db,               /* The database there the function is registered */
  const char *zName,        /* Name of the function */
  int datatype              /* The datatype for this function */
);
#define SQLITE_NUMERIC     (-1)







|







 







|
|
|
|
|
|
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
436
437
438
439
440
441
442
443
444
445
446
447
448

449
450
451
452
453
454
455
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.58 2004/02/25 13:47:33 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.
................................................................................
  void *pUserData           /* Available via the sqlite_user_data() call */
);

/*
** Use the following routine to define the datatype returned by a
** user-defined function.  The second argument can be one of the
** constants SQLITE_NUMERIC, SQLITE_TEXT, or SQLITE_ARGS or it
** can be an integer greater than or equal to zero.  When the datatype
** parameter is non-negative, the type of the result will be the
** same as the datatype-th argument.  If datatype==SQLITE_NUMERIC
** then the result is always numeric.  If datatype==SQLITE_TEXT then
** the result is always text.  If datatype==SQLITE_ARGS then the result
** is numeric if any argument is numeric and is text otherwise.

*/
int sqlite_function_type(
  sqlite *db,               /* The database there the function is registered */
  const char *zName,        /* Name of the function */
  int datatype              /* The datatype for this function */
);
#define SQLITE_NUMERIC     (-1)

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
419
420
421
422
423
424
425
426
427


428
429
430
431
432
433
434
435
436
....
1168
1169
1170
1171
1172
1173
1174

1175
1176
1177
1178
1179
1180
1181
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.219 2004/02/24 01:05:33 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {
  void (*xFunc)(sqlite_func*,int,const char**);  /* Regular function */
  void (*xStep)(sqlite_func*,int,const char**);  /* Aggregate function step */
  void (*xFinalize)(sqlite_func*);           /* Aggregate function finializer */
  int nArg;                                  /* Number of arguments */
  int dataType;                              /* Datatype of the result */


  void *pUserData;                           /* User data parameter */
  FuncDef *pNext;                            /* Next function with same name */
};

/*
** information about each column of an SQL table is held in an instance
** of this structure.
*/
struct Column {
................................................................................
Table *sqliteSrcListLookup(Parse*, SrcList*);
int sqliteIsReadOnly(Parse*, Table*, int);
void sqliteDeleteFrom(Parse*, SrcList*, Expr*);
void sqliteUpdate(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqliteWhereBegin(Parse*, SrcList*, Expr*, int, ExprList**);
void sqliteWhereEnd(WhereInfo*);
void sqliteExprCode(Parse*, Expr*);

void sqliteExprIfTrue(Parse*, Expr*, int, int);
void sqliteExprIfFalse(Parse*, Expr*, int, int);
Table *sqliteFindTable(sqlite*,const char*, const char*);
Table *sqliteLocateTable(Parse*,const char*, const char*);
Index *sqliteFindIndex(sqlite*,const char*, const char*);
void sqliteUnlinkAndDeleteIndex(sqlite*,Index*);
void sqliteCopy(Parse*, SrcList*, Token*, Token*, int);







|







 







|
|
>
>
|
|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
....
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.220 2004/02/25 13:47:33 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {
  void (*xFunc)(sqlite_func*,int,const char**);  /* Regular function */
  void (*xStep)(sqlite_func*,int,const char**);  /* Aggregate function step */
  void (*xFinalize)(sqlite_func*);           /* Aggregate function finializer */
  signed char nArg;         /* Number of arguments.  -1 means unlimited */
  signed char dataType;     /* Arg that determines datatype.  -1=NUMERIC, */
                            /* -2=TEXT. -3=SQLITE_ARGS */
  u8 includeTypes;          /* Add datatypes to args of xFunc and xStep */
  void *pUserData;          /* User data parameter */
  FuncDef *pNext;           /* Next function with same name */
};

/*
** information about each column of an SQL table is held in an instance
** of this structure.
*/
struct Column {
................................................................................
Table *sqliteSrcListLookup(Parse*, SrcList*);
int sqliteIsReadOnly(Parse*, Table*, int);
void sqliteDeleteFrom(Parse*, SrcList*, Expr*);
void sqliteUpdate(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqliteWhereBegin(Parse*, SrcList*, Expr*, int, ExprList**);
void sqliteWhereEnd(WhereInfo*);
void sqliteExprCode(Parse*, Expr*);
int sqliteExprCodeExprList(Parse*, ExprList*, int);
void sqliteExprIfTrue(Parse*, Expr*, int, int);
void sqliteExprIfFalse(Parse*, Expr*, int, int);
Table *sqliteFindTable(sqlite*,const char*, const char*);
Table *sqliteLocateTable(Parse*,const char*, const char*);
Index *sqliteFindIndex(sqlite*,const char*, const char*);
void sqliteUnlinkAndDeleteIndex(sqlite*,Index*);
void sqliteCopy(Parse*, SrcList*, Token*, Token*, int);

Changes to test/minmax.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
...
231
232
233
234
235
236
237






















238
239
240
#
#***********************************************************************
# 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.7 2004/01/30 02:01:05 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
} {999}
do_test minmax-3.3 {
  set sqlite_search_count
} {0}

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x),-1), coalesce(max(x),-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)
................................................................................
  }
} 1
do_test minmax-7.4 {
  execsql {
    SELECT * FROM (SELECT min(x) FROM t1);
  }
} 1
























finish_test







|







 







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
...
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
#
#***********************************************************************
# 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.8 2004/02/25 13:47:34 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
} {999}
do_test minmax-3.3 {
  set sqlite_search_count
} {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)
................................................................................
  }
} 1
do_test minmax-7.4 {
  execsql {
    SELECT * FROM (SELECT min(x) FROM t1);
  }
} 1

# Make sure min(x) and max(x) work correctly when the datatype is
# TEXT instead of NUMERIC.  Ticket #623.
#
do_test minmax-8.1 {
  execsql {
    CREATE TABLE t4(a TEXT);
    INSERT INTO t4 VALUES('1234');
    INSERT INTO t4 VALUES('234');
    INSERT INTO t4 VALUES('34');
    SELECT min(a), max(a) FROM t4;
  }
} {1234 34}
do_test minmax-8.2 {
  execsql {
    CREATE TABLE t5(a INTEGER);
    INSERT INTO t5 VALUES('1234');
    INSERT INTO t5 VALUES('234');
    INSERT INTO t5 VALUES('34');
    SELECT min(a), max(a) FROM t5;
  }
} {34 1234}


finish_test