/ Check-in [db88a0c2]
Login

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

Overview
Comment:GROUP BY and HAVING installed (CVS 58)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:db88a0c2d4b5c5cd05e0172f061fc33763fe3829
User & Date: drh 2000-06-06 17:27:05
Context
2000-06-06
18:00
:-) (CVS 59) check-in: a8fa6719 user: drh tags: trunk
17:27
GROUP BY and HAVING installed (CVS 58) check-in: db88a0c2 user: drh tags: trunk
13:54
added IN and BETWEEN operators (CVS 57) check-in: 54d19818 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

    19     19   ** Author contact information:
    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains C code routines used for processing expressions
    25     25   **
    26         -** $Id: expr.c,v 1.9 2000/06/06 13:54:15 drh Exp $
           26  +** $Id: expr.c,v 1.10 2000/06/06 17:27:05 drh Exp $
    27     27   */
    28     28   #include "sqliteInt.h"
    29     29   
    30     30   /*
    31     31   ** Walk an expression tree.  Return 1 if the expression is constant
    32     32   ** and 0 if it involves variables.
    33     33   */
................................................................................
   322    322        int len;
   323    323        int id;
   324    324     } aFunc[] = {
   325    325        { "count",  5, FN_Count },
   326    326        { "min",    3, FN_Min   },
   327    327        { "max",    3, FN_Max   },
   328    328        { "sum",    3, FN_Sum   },
          329  +     { "avg",    3, FN_Avg   },
   329    330     };
   330    331     int i;
   331    332     for(i=0; i<ArraySize(aFunc); i++){
   332    333       if( aFunc[i].len==pToken->n 
   333    334        && sqliteStrNICmp(pToken->z, aFunc[i].zName, aFunc[i].len)==0 ){
   334    335          return aFunc[i].id;
   335    336       }
................................................................................
   345    346   **
   346    347   ** if pIsAgg is not null and this expression is an aggregate function
   347    348   ** (like count(*) or max(value)) then write a 1 into *pIsAgg.
   348    349   */
   349    350   int sqliteExprCheck(Parse *pParse, Expr *pExpr, int allowAgg, int *pIsAgg){
   350    351     int nErr = 0;
   351    352     if( pExpr==0 ) return 0;
   352         -  if( pIsAgg ) *pIsAgg = 0;
   353    353     switch( pExpr->op ){
   354    354       case TK_FUNCTION: {
   355    355         int id = sqliteFuncId(&pExpr->token);
   356    356         int n = pExpr->pList ? pExpr->pList->nExpr : 0;
   357    357         int no_such_func = 0;
   358    358         int too_many_args = 0;
   359    359         int too_few_args = 0;
   360    360         int is_agg = 0;
   361    361         int i;
          362  +      pExpr->iField = id;
   362    363         switch( id ){
   363    364           case FN_Unknown: { 
   364    365             no_such_func = 1;
   365    366             break;
   366    367           }
   367    368           case FN_Count: { 
   368    369             no_such_func = !allowAgg;
................................................................................
   372    373           }
   373    374           case FN_Max:
   374    375           case FN_Min: {
   375    376             too_few_args = allowAgg ? n<1 : n<2;
   376    377             is_agg = n==1;
   377    378             break;
   378    379           }
          380  +        case FN_Avg:
   379    381           case FN_Sum: {
   380    382             no_such_func = !allowAgg;
   381    383             too_many_args = n>1;
   382    384             too_few_args = n<1;
   383    385             is_agg = 1;
   384    386             break;
   385    387           }
................................................................................
   397    399           nErr++;
   398    400         }else if( too_few_args ){
   399    401           sqliteSetNString(&pParse->zErrMsg, "too few arguments to function ",-1,
   400    402              pExpr->token.z, pExpr->token.n, "()", 2, 0);
   401    403           pParse->nErr++;
   402    404           nErr++;
   403    405         }
          406  +      if( is_agg ) pExpr->op = TK_AGG_FUNCTION;
   404    407         if( is_agg && pIsAgg ) *pIsAgg = 1;
   405    408         for(i=0; nErr==0 && i<n; i++){
   406    409           nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr, 0, 0);
   407    410         }
   408    411       }
   409    412       default: {
   410    413         if( pExpr->pLeft ){
   411         -        nErr = sqliteExprCheck(pParse, pExpr->pLeft, 0, 0);
          414  +        nErr = sqliteExprCheck(pParse, pExpr->pLeft, allowAgg, pIsAgg);
   412    415         }
   413    416         if( nErr==0 && pExpr->pRight ){
   414         -        nErr = sqliteExprCheck(pParse, pExpr->pRight, 0, 0);
          417  +        nErr = sqliteExprCheck(pParse, pExpr->pRight, allowAgg, pIsAgg);
   415    418         }
   416    419         if( nErr==0 && pExpr->pList ){
   417    420           int n = pExpr->pList->nExpr;
   418    421           int i;
   419    422           for(i=0; nErr==0 && i<n; i++){
   420         -          nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr, 0, 0);
          423  +          Expr *pE2 = pExpr->pList->a[i].pExpr;
          424  +          nErr = sqliteExprCheck(pParse, pE2, allowAgg, pIsAgg);
   421    425           }
   422    426         }
   423    427         break;
   424    428       }
   425    429     }
   426    430     return nErr;
   427    431   }
................................................................................
   452    456       case TK_NOTNULL:  op = OP_NotNull;  break;
   453    457       case TK_NOT:      op = OP_Not;      break;
   454    458       case TK_UMINUS:   op = OP_Negative; break;
   455    459       default: break;
   456    460     }
   457    461     switch( pExpr->op ){
   458    462       case TK_FIELD: {
   459         -      sqliteVdbeAddOp(v, OP_Field, pExpr->iTable, pExpr->iField, 0, 0);
          463  +      if( pParse->useAgg ){
          464  +        sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg, 0, 0);
          465  +      }else{
          466  +        sqliteVdbeAddOp(v, OP_Field, pExpr->iTable, pExpr->iField, 0, 0);
          467  +      }
   460    468         break;
   461    469       }
   462    470       case TK_INTEGER: {
   463    471         int i = atoi(pExpr->token.z);
   464    472         sqliteVdbeAddOp(v, OP_Integer, i, 0, 0, 0);
   465    473         break;
   466    474       }
................................................................................
   518    526         int dest;
   519    527         sqliteVdbeAddOp(v, OP_Integer, 1, 0, 0, 0);
   520    528         sqliteExprCode(pParse, pExpr->pLeft);
   521    529         dest = sqliteVdbeCurrentAddr(v) + 2;
   522    530         sqliteVdbeAddOp(v, op, 0, dest, 0, 0);
   523    531         sqliteVdbeAddOp(v, OP_AddImm, -1, 0, 0, 0);
   524    532         break;
          533  +    }
          534  +    case TK_AGG_FUNCTION: {
          535  +      sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg, 0, 0);
          536  +      if( pExpr->iField==FN_Avg ){
          537  +        assert( pParse->iAggCount>=0 && pParse->iAggCount<pParse->nAgg );
          538  +        sqliteVdbeAddOp(v, OP_AggGet, 0, pParse->iAggCount, 0, 0);
          539  +        sqliteVdbeAddOp(v, OP_Divide, 0, 0, 0, 0);
          540  +      }
          541  +      break;
   525    542       }
   526    543       case TK_FUNCTION: {
   527         -      int id = sqliteFuncId(&pExpr->token);
          544  +      int id = pExpr->iField;
   528    545         int op;
   529    546         int i;
   530    547         ExprList *pList = pExpr->pList;
   531    548         op = id==FN_Min ? OP_Min : OP_Max;
   532    549         for(i=0; i<pList->nExpr; i++){
   533    550           sqliteExprCode(pParse, pList->a[i].pExpr);
   534    551           if( i>0 ){
................................................................................
   740    757         sqliteExprCode(pParse, pExpr);
   741    758         sqliteVdbeAddOp(v, OP_Not, 0, 0, 0, 0);
   742    759         sqliteVdbeAddOp(v, OP_If, 0, dest, 0, 0);
   743    760         break;
   744    761       }
   745    762     }
   746    763   }
          764  +
          765  +/*
          766  +** Do a deep comparison of two expression trees.  Return TRUE (non-zero)
          767  +** if they are identical and return FALSE if they differ in any way.
          768  +*/
          769  +static int exprDeepCompare(Expr *pA, Expr *pB){
          770  +  int i;
          771  +  if( pA==0 ){
          772  +    return pB==0;
          773  +  }else if( pB==0 ){
          774  +    return 0;
          775  +  }
          776  +  if( pA->op!=pB->op ) return 0;
          777  +  if( !exprDeepCompare(pA->pLeft, pB->pLeft) ) return 0;
          778  +  if( !exprDeepCompare(pA->pRight, pB->pRight) ) return 0;
          779  +  if( pA->pList ){
          780  +    if( pB->pList==0 ) return 0;
          781  +    if( pA->pList->nExpr!=pB->pList->nExpr ) return 0;
          782  +    for(i=0; i<pA->pList->nExpr; i++){
          783  +      if( !exprDeepCompare(pA->pList->a[i].pExpr, pB->pList->a[i].pExpr) ){
          784  +        return 0;
          785  +      }
          786  +    }
          787  +  }else if( pB->pList ){
          788  +    return 0;
          789  +  }
          790  +  if( pA->pSelect || pB->pSelect ) return 0;
          791  +  if( pA->token.z ){
          792  +    if( pB->token.z==0 ) return 0;
          793  +    if( pB->token.n!=pA->token.n ) return 0;
          794  +    if( sqliteStrNICmp(pA->token.z, pB->token.z, pA->token.n)!=0 ) return 0;
          795  +  }
          796  +  return 1;
          797  +}
          798  +
          799  +/*
          800  +** Add a new element to the pParse->aAgg[] array and return its index.
          801  +*/
          802  +static int appendAggInfo(Parse *pParse){
          803  +  if( (pParse->nAgg & 0x7)==0 ){
          804  +    int amt = pParse->nAgg + 8;
          805  +    pParse->aAgg = sqliteRealloc(pParse->aAgg, amt*sizeof(pParse->aAgg[0]));
          806  +    if( pParse->aAgg==0 ){
          807  +      sqliteSetString(&pParse->zErrMsg, "out of memory", 0);
          808  +      pParse->nErr++;
          809  +      return -1;
          810  +    }
          811  +  }
          812  +  memset(&pParse->aAgg[pParse->nAgg], 0, sizeof(pParse->aAgg[0]));
          813  +  return pParse->nAgg++;
          814  +}
          815  +
          816  +/*
          817  +** Analyze the given expression looking for aggregate functions and
          818  +** for variables that need to be added to the pParse->aAgg[] array.
          819  +** Make additional entries to the pParse->aAgg[] array as necessary.
          820  +**
          821  +** This routine should only be called after the expression has been
          822  +** analyzed by sqliteExprResolveIds() and sqliteExprCheck().
          823  +**
          824  +** If errors are seen, leave an error message in zErrMsg and return
          825  +** the number of errors.
          826  +*/
          827  +int sqliteExprAnalyzeAggregates(Parse *pParse, Expr *pExpr){
          828  +  int i;
          829  +  AggExpr *aAgg;
          830  +  int nErr = 0;
          831  +
          832  +  if( pExpr==0 ) return 0;
          833  +  switch( pExpr->op ){
          834  +    case TK_FIELD: {
          835  +      aAgg = pParse->aAgg;
          836  +      for(i=0; i<pParse->nAgg; i++){
          837  +        if( aAgg[i].isAgg ) continue;
          838  +        if( aAgg[i].pExpr->iTable==pExpr->iTable
          839  +         && aAgg[i].pExpr->iField==pExpr->iField ){
          840  +          pExpr->iAgg = i;
          841  +          break;
          842  +        }
          843  +      }
          844  +      if( i>=pParse->nAgg ){
          845  +        i = appendAggInfo(pParse);
          846  +        if( i<0 ) return 1;
          847  +        pParse->aAgg[i].isAgg = 0;
          848  +        pParse->aAgg[i].pExpr = pExpr;
          849  +      }
          850  +      break;
          851  +    }
          852  +    case TK_AGG_FUNCTION: {
          853  +      if( pExpr->iField==FN_Count || pExpr->iField==FN_Avg ){
          854  +        if( pParse->iAggCount>=0 ){
          855  +          i = pParse->iAggCount;
          856  +        }else{
          857  +          i = appendAggInfo(pParse);
          858  +          if( i<0 ) return 1;
          859  +          pParse->aAgg[i].isAgg = 1;
          860  +          pParse->aAgg[i].pExpr = 0;
          861  +          pParse->iAggCount = i;
          862  +        }
          863  +        if( pExpr->iField==FN_Count ){
          864  +          pExpr->iAgg = i;
          865  +          break;
          866  +        }
          867  +      }
          868  +      aAgg = pParse->aAgg;
          869  +      for(i=0; i<pParse->nAgg; i++){
          870  +        if( !aAgg[i].isAgg ) continue;
          871  +        if( exprDeepCompare(aAgg[i].pExpr, pExpr) ){
          872  +          break;
          873  +        }
          874  +      }
          875  +      if( i>=pParse->nAgg ){
          876  +        i = appendAggInfo(pParse);
          877  +        if( i<0 ) return 1;
          878  +        pParse->aAgg[i].isAgg = 1;
          879  +        pParse->aAgg[i].pExpr = pExpr;
          880  +      }
          881  +      pExpr->iAgg = i;
          882  +      break;
          883  +    }
          884  +    default: {
          885  +      if( pExpr->pLeft ){
          886  +        nErr = sqliteExprAnalyzeAggregates(pParse, pExpr->pLeft);
          887  +      }
          888  +      if( nErr==0 && pExpr->pRight ){
          889  +        nErr = sqliteExprAnalyzeAggregates(pParse, pExpr->pRight);
          890  +      }
          891  +      if( nErr==0 && pExpr->pList ){
          892  +        int n = pExpr->pList->nExpr;
          893  +        int i;
          894  +        for(i=0; nErr==0 && i<n; i++){
          895  +          nErr = sqliteExprAnalyzeAggregates(pParse, pExpr->pList->a[i].pExpr);
          896  +        }
          897  +      }
          898  +      break;
          899  +    }
          900  +  }
          901  +  return nErr;
          902  +}

Changes to src/parse.y.

    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains SQLite's grammar for SQL.  Process this file
    25     25   ** using the lemon parser generator to generate C code that runs
    26     26   ** the parser.  Lemon will also generate a header file containing
    27     27   ** numeric codes for all of the tokens.
    28     28   **
    29         -** @(#) $Id: parse.y,v 1.12 2000/06/06 13:54:15 drh Exp $
           29  +** @(#) $Id: parse.y,v 1.13 2000/06/06 17:27:05 drh Exp $
    30     30   */
    31     31   %token_prefix TK_
    32     32   %token_type {Token}
    33     33   %extra_argument {Parse *pParse}
    34     34   %syntax_error {
    35     35     sqliteSetNString(&pParse->zErrMsg,"syntax error near \"",0,TOKEN.z,TOKEN.n,
    36     36                      "\"", 1, 0);
................................................................................
    47     47   input ::= cmdlist.
    48     48   
    49     49   // These are extra tokens used by the lexer but never seen by the
    50     50   // parser.  We put them in a rule so that the parser generator will
    51     51   // add them to the sqliteTokens.h output file.
    52     52   //
    53     53   input ::= END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
    54         -          UMINUS FIELD.
           54  +          UMINUS FIELD AGG_FUNCTION.
    55     55   
    56     56   // A list of commands is zero or more commands
    57     57   //
    58     58   cmdlist ::= ecmd.
    59     59   cmdlist ::= cmdlist SEMI ecmd.
    60     60   ecmd ::= explain cmd.  {sqliteExec(pParse);}
    61     61   ecmd ::= cmd.          {sqliteExec(pParse);}
................................................................................
   137    137     sqliteSelectDelete(X);
   138    138   }
   139    139   
   140    140   %type select {Select*}
   141    141   %destructor select {sqliteSelectDelete($$);}
   142    142   
   143    143   select(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y)
   144         -              orderby_opt(Z). {
   145         -  A = sqliteSelectNew(W,X,Y,0,0,Z,D);
          144  +              groupby_opt(P) having_opt(Q) orderby_opt(Z). {
          145  +  A = sqliteSelectNew(W,X,Y,P,Q,Z,D);
   146    146   }
   147    147   
   148    148   // The "distinct" nonterminal is true (1) if the DISTINCT keyword is
   149    149   // present and false (0) if it is not.
   150    150   //
   151    151   %type distinct {int}
   152    152   distinct(A) ::= DISTINCT.   {A = 1;}
................................................................................
   208    208   
   209    209   %type sortorder {int}
   210    210   
   211    211   sortorder(A) ::= ASC.      {A = 0;}
   212    212   sortorder(A) ::= DESC.     {A = 1;}
   213    213   sortorder(A) ::= .         {A = 0;}
   214    214   
          215  +%type groupby_opt {ExprList*}
          216  +%destructor groupby_opt {sqliteExprListDelete($$);}
          217  +groupby_opt(A) ::= .     {A = 0;}
          218  +groupby_opt(A) ::= GROUP BY exprlist(X).  {A = X;}
          219  +
          220  +%type having_opt {Expr*}
          221  +%destructor having_opt {sqliteExprDelete($$);}
          222  +having_opt(A) ::= .      {A = 0;}
          223  +having_opt(A) ::= HAVING expr(X).  {A = X;}
          224  +
   215    225   cmd ::= DELETE FROM ID(X) where_opt(Y).
   216    226       {sqliteDeleteFrom(pParse, &X, Y);}
   217    227   
   218    228   %type where_opt {Expr*}
   219    229   %destructor where_opt {sqliteExprDelete($$);}
   220    230   
   221    231   where_opt(A) ::= .                    {A = 0;}

Changes to src/select.c.

    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains C code routines that are called by the parser
    25     25   ** to handle SELECT statements.
    26     26   **
    27         -** $Id: select.c,v 1.10 2000/06/06 13:54:15 drh Exp $
           27  +** $Id: select.c,v 1.11 2000/06/06 17:27:05 drh Exp $
    28     28   */
    29     29   #include "sqliteInt.h"
    30         -
    31     30   
    32     31   /*
    33     32   ** Allocate a new Select structure and return a pointer to that
    34     33   ** structure.
    35     34   */
    36     35   Select *sqliteSelectNew(
    37     36     ExprList *pEList,
................................................................................
    63     62     sqliteIdListDelete(p->pSrc);
    64     63     sqliteExprDelete(p->pWhere);
    65     64     sqliteExprListDelete(p->pGroupBy);
    66     65     sqliteExprDelete(p->pHaving);
    67     66     sqliteExprListDelete(p->pOrderBy);
    68     67     sqliteFree(p);
    69     68   }
           69  +
           70  +/*
           71  +** Delete the aggregate information from the parse structure.
           72  +*/
           73  +void sqliteParseInfoReset(Parse *pParse){
           74  +  sqliteFree(pParse->aAgg);
           75  +  pParse->aAgg = 0;
           76  +  pParse->nAgg = 0;
           77  +  pParse->iAggCount = -1;
           78  +  pParse->useAgg = 0;
           79  +}
           80  +
           81  +/*
           82  +** This routine generates the code for the inside of the inner loop
           83  +** of a SELECT.
           84  +*/
           85  +static int selectInnerLoop(
           86  +  Parse *pParse,          /* The parser context */
           87  +  ExprList *pEList,       /* List of values being extracted */
           88  +  ExprList *pOrderBy,     /* If not NULL, sort results using this key */
           89  +  int distinct,           /* If >=0, make sure results are distinct */
           90  +  int eDest,              /* How to dispose of the results */
           91  +  int iParm,              /* An argument to the disposal method */
           92  +  int iContinue,          /* Jump here to continue with next row */
           93  +  int iBreak              /* Jump here to break out of the inner loop */
           94  +){
           95  +  Vdbe *v = pParse->pVdbe;
           96  +  int i;
           97  +
           98  +  /* Pull the requested fields.
           99  +  */
          100  +  for(i=0; i<pEList->nExpr; i++){
          101  +    sqliteExprCode(pParse, pEList->a[i].pExpr);
          102  +  }
          103  +
          104  +  /* If the current result is not distinct, skip the rest
          105  +  ** of the processing for the current row.
          106  +  */
          107  +  if( distinct>=0 ){
          108  +    int lbl = sqliteVdbeMakeLabel(v);
          109  +    sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1, 0, 0);
          110  +    sqliteVdbeAddOp(v, OP_Distinct, distinct, lbl, 0, 0);
          111  +    sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0, 0, 0);
          112  +    sqliteVdbeAddOp(v, OP_Goto, 0, iContinue, 0, 0);
          113  +    sqliteVdbeAddOp(v, OP_String, 0, 0, "", lbl);
          114  +    sqliteVdbeAddOp(v, OP_Put, distinct, 0, 0, 0);
          115  +  }
          116  +  /* If there is an ORDER BY clause, then store the results
          117  +  ** in a sorter.
          118  +  */
          119  +  if( pOrderBy ){
          120  +    char *zSortOrder;
          121  +    sqliteVdbeAddOp(v, OP_SortMakeRec, pEList->nExpr, 0, 0, 0);
          122  +    zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
          123  +    if( zSortOrder==0 ) return 1;
          124  +    for(i=0; i<pOrderBy->nExpr; i++){
          125  +      zSortOrder[i] = pOrderBy->a[i].idx ? '-' : '+';
          126  +      sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
          127  +    }
          128  +    zSortOrder[pOrderBy->nExpr] = 0;
          129  +    sqliteVdbeAddOp(v, OP_SortMakeKey, pOrderBy->nExpr, 0, zSortOrder, 0);
          130  +    sqliteVdbeAddOp(v, OP_SortPut, 0, 0, 0, 0);
          131  +  }else 
          132  +
          133  +  /* If we are writing to a table, then write the results to the table.
          134  +  */
          135  +  if( eDest==SRT_Table ){
          136  +    sqliteVdbeAddOp(v, OP_MakeRecord, pEList->nExpr, 0, 0, 0);
          137  +    sqliteVdbeAddOp(v, OP_New, iParm, 0, 0, 0);
          138  +    sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
          139  +    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
          140  +  }else 
          141  +
          142  +  /* If we are creating a set for an "expr IN (SELECT ...)" construct,
          143  +  ** then there should be a single item on the stack.  Write this
          144  +  ** item into the set table with bogus data.
          145  +  */
          146  +  if( eDest==SRT_Set ){
          147  +    assert( pEList->nExpr==1 );
          148  +    sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
          149  +    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
          150  +  }else 
          151  +
          152  +  /* If this is a scalar select that is part of an expression, then
          153  +  ** store the results in the appropriate memory cell and break out
          154  +  ** of the scan loop.
          155  +  */
          156  +  if( eDest==SRT_Mem ){
          157  +    sqliteVdbeAddOp(v, OP_MemStore, iParm, 0, 0, 0);
          158  +    sqliteVdbeAddOp(v, OP_Goto, 0, iBreak, 0, 0);
          159  +  }else
          160  +
          161  +  /* If none of the above, send the data to the callback function.
          162  +  */
          163  +  {
          164  +    sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
          165  +  }
          166  +  return 0;
          167  +}
    70    168   
    71    169   /*
    72    170   ** Generate code for the given SELECT statement.
    73    171   **
    74    172   ** The results are distributed in various ways depending on the
    75    173   ** value of eDest and iParm.
    76    174   **
................................................................................
   101    199     WhereInfo *pWInfo;
   102    200     Vdbe *v;
   103    201     int isAgg = 0;         /* True for select lists like "count(*)" */
   104    202     ExprList *pEList;      /* List of fields to extract.  NULL means "*" */
   105    203     IdList *pTabList;      /* List of tables to select from */
   106    204     Expr *pWhere;          /* The WHERE clause.  May be NULL */
   107    205     ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
          206  +  ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
          207  +  Expr *pHaving;         /* The HAVING clause.  May be NULL */
   108    208     int isDistinct;        /* True if the DISTINCT keyword is present */
   109    209     int distinct;          /* Table to use for the distinct set */
   110    210   
   111    211     pEList = p->pEList;
   112    212     pTabList = p->pSrc;
   113    213     pWhere = p->pWhere;
   114    214     pOrderBy = p->pOrderBy;
          215  +  pGroupBy = p->pGroupBy;
          216  +  pHaving = p->pHaving;
   115    217     isDistinct = p->isDistinct;
   116    218   
   117    219     /* 
   118    220     ** Do not even attempt to generate any code if we have already seen
   119    221     ** errors before this routine starts.
   120    222     */
   121    223     if( pParse->nErr>0 ) return 0;
          224  +  sqliteParseInfoReset(pParse);
   122    225   
   123    226     /* Look up every table in the table list.
   124    227     */
   125    228     for(i=0; i<pTabList->nId; i++){
   126    229       pTabList->a[i].pTab = sqliteFindTable(pParse->db, pTabList->a[i].zName);
   127    230       if( pTabList->a[i].pTab==0 ){
   128    231         sqliteSetString(&pParse->zErrMsg, "no such table: ", 
................................................................................
   129    232            pTabList->a[i].zName, 0);
   130    233         pParse->nErr++;
   131    234         return 1;
   132    235       }
   133    236     }
   134    237   
   135    238     /* Allocate a temporary table to use for the DISTINCT set, if
   136         -  ** necessary.
          239  +  ** necessary.  This must be done early to allocate the cursor before
          240  +  ** any calls to sqliteExprResolveIds().
   137    241     */
   138    242     if( isDistinct ){
   139    243       distinct = pParse->nTab++;
          244  +  }else{
          245  +    distinct = -1;
   140    246     }
   141    247   
   142    248     /* If the list of fields to retrieve is "*" then replace it with
   143    249     ** a list of all fields from all tables.
   144    250     */
   145    251     if( pEList==0 ){
   146    252       for(i=0; i<pTabList->nId; i++){
................................................................................
   150    256           pExpr->iTable = i + pParse->nTab;
   151    257           pExpr->iField = j;
   152    258           pEList = sqliteExprListAppend(pEList, pExpr, 0);
   153    259         }
   154    260       }
   155    261     }
   156    262   
   157         -  /* If writing to memory, only a single column may be output.
          263  +  /* If writing to memory or generating a set
          264  +  ** only a single column may be output.
   158    265     */
   159    266     if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
   160    267       sqliteSetString(&pParse->zErrMsg, "only a single result allowed for "
   161    268          "a SELECT that is part of an expression", 0);
   162    269       pParse->nErr++;
   163    270       return 1;
   164    271     }
   165    272   
   166         -  /* Resolve the field names and do a semantics check on all the expressions.
          273  +  /* ORDER BY is ignored if we are not sending the result to a callback.
          274  +  */
          275  +  if( eDest!=SRT_Callback ){
          276  +    pOrderBy = 0;
          277  +  }
          278  +
          279  +  /* Allocate cursors for "expr IN (SELECT ...)" constructs.
   167    280     */
   168    281     for(i=0; i<pEList->nExpr; i++){
   169    282       sqliteExprResolveInSelect(pParse, pEList->a[i].pExpr);
   170    283     }
   171    284     if( pWhere ) sqliteExprResolveInSelect(pParse, pWhere);
   172    285     if( pOrderBy ){
   173    286       for(i=0; i<pOrderBy->nExpr; i++){
   174    287         sqliteExprResolveInSelect(pParse, pOrderBy->a[i].pExpr);
   175    288       }
   176    289     }
          290  +  if( pGroupBy ){
          291  +    for(i=0; i<pGroupBy->nExpr; i++){
          292  +      sqliteExprResolveInSelect(pParse, pGroupBy->a[i].pExpr);
          293  +    }
          294  +  }
          295  +  if( pHaving ) sqliteExprResolveInSelect(pParse, pHaving);
          296  +
          297  +  /* Resolve the field names and do a semantics check on all the expressions.
          298  +  */
   177    299     for(i=0; i<pEList->nExpr; i++){
   178    300       if( sqliteExprResolveIds(pParse, pTabList, pEList->a[i].pExpr) ){
   179    301         return 1;
   180    302       }
   181         -    if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &pEList->a[i].isAgg) ){
          303  +    if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
   182    304         return 1;
   183    305       }
   184    306     }
   185         -  if( pEList->nExpr>0 ){
   186         -    isAgg = pEList->a[0].isAgg;
   187         -    for(i=1; i<pEList->nExpr; i++){
   188         -      if( pEList->a[i].isAgg!=isAgg ){
   189         -        sqliteSetString(&pParse->zErrMsg, "some selected items are aggregates "
   190         -          "and others are not", 0);
   191         -        pParse->nErr++;
   192         -        return 1;
   193         -      }
   194         -    }
   195         -  }
   196    307     if( pWhere ){
   197    308       if( sqliteExprResolveIds(pParse, pTabList, pWhere) ){
   198    309         return 1;
   199    310       }
   200    311       if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
   201    312         return 1;
   202    313       }
   203    314     }
   204    315     if( pOrderBy ){
   205    316       for(i=0; i<pOrderBy->nExpr; i++){
   206         -      if( sqliteExprResolveIds(pParse, pTabList, pOrderBy->a[i].pExpr) ){
          317  +      Expr *pE = pOrderBy->a[i].pExpr;
          318  +      if( sqliteExprResolveIds(pParse, pTabList, pE) ){
          319  +        return 1;
          320  +      }
          321  +      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
          322  +        return 1;
          323  +      }
          324  +    }
          325  +  }
          326  +  if( pGroupBy ){
          327  +    for(i=0; i<pGroupBy->nExpr; i++){
          328  +      Expr *pE = pGroupBy->a[i].pExpr;
          329  +      if( sqliteExprResolveIds(pParse, pTabList, pE) ){
   207    330           return 1;
   208    331         }
   209         -      if( sqliteExprCheck(pParse, pOrderBy->a[i].pExpr, 0, 0) ){
          332  +      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
   210    333           return 1;
   211    334         }
   212    335       }
          336  +  }
          337  +  if( pHaving ){
          338  +    if( pGroupBy==0 ){
          339  +      sqliteSetString(&pParse->zErrMsg, "a GROUP BY clause is required to "
          340  +         "use HAVING", 0);
          341  +      pParse->nErr++;
          342  +      return 1;
          343  +    }
          344  +    if( sqliteExprResolveIds(pParse, pTabList, pHaving) ){
          345  +      return 1;
          346  +    }
          347  +    if( sqliteExprCheck(pParse, pHaving, 0, 0) ){
          348  +      return 1;
          349  +    }
   213    350     }
   214    351   
   215         -  /* ORDER BY is ignored if we are not invoking callbacks.
          352  +  /* Do an analysis of aggregate expressions.
   216    353     */
   217         -  if( isAgg || eDest!=SRT_Callback ){
   218         -    pOrderBy = 0;
   219         -  }
   220         -
   221         -  /* Turn off distinct if this is an aggregate or writing to memory.
   222         -  */
   223         -  if( isAgg || eDest==SRT_Mem ){
   224         -    isDistinct = 0;
          354  +  if( isAgg ){
          355  +    for(i=0; i<pEList->nExpr; i++){
          356  +      if( sqliteExprAnalyzeAggregates(pParse, pEList->a[i].pExpr) ){
          357  +        return 1;
          358  +      }
          359  +    }
          360  +    if( pGroupBy ){
          361  +      for(i=0; i<pGroupBy->nExpr; i++){
          362  +        if( sqliteExprAnalyzeAggregates(pParse, pGroupBy->a[i].pExpr) ){
          363  +          return 1;
          364  +        }
          365  +      }
          366  +    }
          367  +    if( pHaving && sqliteExprAnalyzeAggregates(pParse, pHaving) ){
          368  +      return 1;
          369  +    }
   225    370     }
   226    371   
   227    372     /* Begin generating code.
   228    373     */
   229    374     v = pParse->pVdbe;
   230    375     if( v==0 ){
   231    376       v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
................................................................................
   235    380       pParse->nErr++;
   236    381       return 1;
   237    382     }
   238    383     if( pOrderBy ){
   239    384       sqliteVdbeAddOp(v, OP_SortOpen, 0, 0, 0, 0);
   240    385     }
   241    386   
   242         -  /* Identify column names if we will be using a callback.  This
          387  +  /* Identify column names if we will be using in the callback.  This
   243    388     ** step is skipped if the output is going to a table or a memory cell.
   244    389     */
   245    390     if( eDest==SRT_Callback ){
   246    391       sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr, 0, 0, 0);
   247    392       for(i=0; i<pEList->nExpr; i++){
   248    393         Expr *p;
   249    394         if( pEList->a[i].zName ){
................................................................................
   275    420             char *zName = pTab->aCol[p->iField].zName;
   276    421             sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
   277    422           }
   278    423         }
   279    424       }
   280    425     }
   281    426   
   282         -  /* Initialize the stack to contain aggregate seed values
          427  +  /* Reset the aggregator
   283    428     */
   284    429     if( isAgg ){
   285         -    for(i=0; i<pEList->nExpr; i++){
   286         -      Expr *p = pEList->a[i].pExpr;
   287         -      switch( sqliteFuncId(&p->token) ){
   288         -        case FN_Min:
   289         -        case FN_Max: {
   290         -          sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
   291         -          break;
   292         -        }
   293         -        default: {
   294         -          sqliteVdbeAddOp(v, OP_Integer, 0, 0, 0, 0);
   295         -          break;
   296         -        }
   297         -      }
   298         -    }
          430  +    sqliteVdbeAddOp(v, OP_AggReset, 0, pParse->nAgg, 0, 0);
   299    431     }
   300    432   
   301    433     /* Initialize the memory cell to NULL
   302    434     */
   303    435     if( eDest==SRT_Mem ){
   304    436       sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
   305    437       sqliteVdbeAddOp(v, OP_MemStore, iParm, 0, 0, 0);
................................................................................
   309    441     */
   310    442     if( isDistinct ){
   311    443       sqliteVdbeAddOp(v, OP_Open, distinct, 1, 0, 0);
   312    444     }
   313    445     pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 0);
   314    446     if( pWInfo==0 ) return 1;
   315    447   
   316         -  /* Pull the requested fields.
          448  +  /* Use the standard inner loop if we are not dealing with
          449  +  ** aggregates
   317    450     */
   318    451     if( !isAgg ){
   319         -    for(i=0; i<pEList->nExpr; i++){
   320         -      sqliteExprCode(pParse, pEList->a[i].pExpr);
          452  +    if( selectInnerLoop(pParse, pEList, pOrderBy, distinct, eDest, iParm,
          453  +                    pWInfo->iContinue, pWInfo->iBreak) ){
          454  +       return 1;
   321    455       }
   322    456     }
   323    457   
   324         -  /* If the current result is not distinct, script the remainder
   325         -  ** of this processing.
          458  +  /* If we are dealing with aggregates, then to the special aggregate
          459  +  ** processing.  
   326    460     */
   327         -  if( isDistinct ){
   328         -    int lbl = sqliteVdbeMakeLabel(v);
   329         -    sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1, 0, 0);
   330         -    sqliteVdbeAddOp(v, OP_Distinct, distinct, lbl, 0, 0);
   331         -    sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0, 0, 0);
   332         -    sqliteVdbeAddOp(v, OP_Goto, 0, pWInfo->iContinue, 0, 0);
   333         -    sqliteVdbeAddOp(v, OP_String, 0, 0, "", lbl);
   334         -    sqliteVdbeAddOp(v, OP_Put, distinct, 0, 0, 0);
   335         -  }
   336         -  
   337         -  /* If there is no ORDER BY clause, then we can invoke the callback
   338         -  ** right away.  If there is an ORDER BY, then we need to put the
   339         -  ** data into an appropriate sorter record.
   340         -  */
   341         -  if( pOrderBy ){
   342         -    char *zSortOrder;
   343         -    sqliteVdbeAddOp(v, OP_SortMakeRec, pEList->nExpr, 0, 0, 0);
   344         -    zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
   345         -    if( zSortOrder==0 ) return 1;
   346         -    for(i=0; i<pOrderBy->nExpr; i++){
   347         -      zSortOrder[i] = pOrderBy->a[i].idx ? '-' : '+';
   348         -      sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
          461  +  else{
          462  +    int doFocus;
          463  +    if( pGroupBy ){
          464  +      for(i=0; i<pGroupBy->nExpr; i++){
          465  +        sqliteExprCode(pParse, pGroupBy->a[i].pExpr);
          466  +      }
          467  +      sqliteVdbeAddOp(v, OP_MakeKey, pGroupBy->nExpr, 0, 0, 0);
          468  +      doFocus = 1;
          469  +    }else{
          470  +      doFocus = 0;
          471  +      for(i=0; i<pParse->nAgg; i++){
          472  +        if( !pParse->aAgg[i].isAgg ){
          473  +          doFocus = 1;
          474  +          break;
          475  +        }
          476  +      }
          477  +      if( doFocus ){
          478  +        sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
          479  +      }
          480  +    }
          481  +    if( doFocus ){
          482  +      int lbl1 = sqliteVdbeMakeLabel(v);
          483  +      sqliteVdbeAddOp(v, OP_AggFocus, 0, lbl1, 0, 0);
          484  +      for(i=0; i<pParse->nAgg; i++){
          485  +        if( pParse->aAgg[i].isAgg ) continue;
          486  +        sqliteExprCode(pParse, pParse->aAgg[i].pExpr);
          487  +        sqliteVdbeAddOp(v, OP_AggSet, 0, i, 0, 0);
          488  +      }
          489  +      sqliteVdbeResolveLabel(v, lbl1);
   349    490       }
   350         -    zSortOrder[pOrderBy->nExpr] = 0;
   351         -    sqliteVdbeAddOp(v, OP_SortMakeKey, pOrderBy->nExpr, 0, zSortOrder, 0);
   352         -    sqliteVdbeAddOp(v, OP_SortPut, 0, 0, 0, 0);
   353         -  }else if( isAgg ){
   354         -    int n = pEList->nExpr;
   355         -    for(i=0; i<n; i++){
   356         -      Expr *p = pEList->a[i].pExpr;
   357         -      int id = sqliteFuncId(&p->token);
   358         -      int op, p1;
   359         -      if( n>1 ){
   360         -        sqliteVdbeAddOp(v, OP_Pull, n-1, 0, 0, 0);
          491  +    for(i=0; i<pParse->nAgg; i++){
          492  +      Expr *pE;
          493  +      int op;
          494  +      if( !pParse->aAgg[i].isAgg ) continue;
          495  +      pE = pParse->aAgg[i].pExpr;
          496  +      if( pE==0 ){
          497  +        sqliteVdbeAddOp(v, OP_AggIncr, 1, i, 0, 0);
          498  +        continue;
          499  +      }
          500  +      assert( pE->op==TK_AGG_FUNCTION );
          501  +      assert( pE->pList!=0 && pE->pList->nExpr==1 );
          502  +      sqliteExprCode(pParse, pE->pList->a[0].pExpr);
          503  +      sqliteVdbeAddOp(v, OP_AggGet, 0, i, 0, 0);
          504  +      switch( pE->iField ){
          505  +        case FN_Min:  op = OP_Min;   break;
          506  +        case FN_Max:  op = OP_Max;   break;
          507  +        case FN_Avg:  op = OP_Add;   break;
          508  +        case FN_Sum:  op = OP_Add;   break;
   361    509         }
   362         -      if( id!=FN_Count && p->pList && p->pList->nExpr>=1 ){
   363         -        sqliteExprCode(pParse, p->pList->a[0].pExpr);
   364         -        sqliteVdbeAddOp(v, OP_Concat, 1, 0, 0, 0);
   365         -      }
   366         -      switch( sqliteFuncId(&p->token) ){
   367         -        case FN_Count: op = OP_AddImm; p1 = 1; break;
   368         -        case FN_Sum:   op = OP_Add;    p1 = 0; break;
   369         -        case FN_Min:   op = OP_Min;    p1 = 1; break;
   370         -        case FN_Max:   op = OP_Max;    p1 = 0; break;
   371         -      }
   372         -      sqliteVdbeAddOp(v, op, p1, 0, 0, 0);
          510  +      sqliteVdbeAddOp(v, op, 0, 0, 0, 0);
          511  +      sqliteVdbeAddOp(v, OP_AggSet, 0, i, 0, 0);
   373    512       }
   374         -  }else if( eDest==SRT_Table ){
   375         -    sqliteVdbeAddOp(v, OP_MakeRecord, pEList->nExpr, 0, 0, 0);
   376         -    sqliteVdbeAddOp(v, OP_New, iParm, 0, 0, 0);
   377         -    sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
   378         -    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
   379         -  }else if( eDest==SRT_Set ){
   380         -    sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
   381         -    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
   382         -  }else if( eDest==SRT_Mem ){
   383         -    sqliteVdbeAddOp(v, OP_MemStore, iParm, 0, 0, 0);
   384         -    sqliteVdbeAddOp(v, OP_Goto, 0, pWInfo->iBreak, 0, 0);
   385         -  }else{
   386         -    sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
   387    513     }
          514  +
   388    515   
   389    516     /* End the database scan loop.
   390    517     */
   391    518     sqliteWhereEnd(pWInfo);
          519  +
          520  +  /* If we are processing aggregates, we need to set up a second loop
          521  +  ** over all of the aggregate values and process them.
          522  +  */
          523  +  if( isAgg ){
          524  +    int endagg = sqliteVdbeMakeLabel(v);
          525  +    int startagg;
          526  +    startagg = sqliteVdbeAddOp(v, OP_AggNext, 0, endagg, 0, 0);
          527  +    pParse->useAgg = 1;
          528  +    if( pHaving ){
          529  +      sqliteExprIfFalse(pParse, pHaving, startagg);
          530  +    }
          531  +    if( selectInnerLoop(pParse, pEList, pOrderBy, distinct, eDest, iParm,
          532  +                    startagg, endagg) ){
          533  +      return 1;
          534  +    }
          535  +    sqliteVdbeAddOp(v, OP_Goto, 0, startagg, 0, 0);
          536  +    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, endagg);
          537  +    pParse->useAgg = 0;
          538  +  }
   392    539   
   393    540     /* If there is an ORDER BY clause, then we need to sort the results
   394    541     ** and send them to the callback one by one.
   395    542     */
   396    543     if( pOrderBy ){
   397    544       int end = sqliteVdbeMakeLabel(v);
   398    545       int addr;
   399    546       sqliteVdbeAddOp(v, OP_Sort, 0, 0, 0, 0);
   400    547       addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end, 0, 0);
   401    548       sqliteVdbeAddOp(v, OP_SortCallback, pEList->nExpr, 0, 0, 0);
   402    549       sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
   403    550       sqliteVdbeAddOp(v, OP_SortClose, 0, 0, 0, end);
   404    551     }
   405         -
   406         -  /* If this is an aggregate, then we need to invoke the callback
   407         -  ** exactly once.
   408         -  */
   409         -  if( isAgg ){
   410         -    if( eDest==SRT_Table ){
   411         -      sqliteVdbeAddOp(v, OP_MakeRecord, pEList->nExpr, 0, 0, 0);
   412         -      sqliteVdbeAddOp(v, OP_New, iParm, 0, 0, 0);
   413         -      sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
   414         -      sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
   415         -    }else if( eDest==SRT_Set ){
   416         -      sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
   417         -      sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
   418         -    }else if( eDest==SRT_Mem ){
   419         -      sqliteVdbeAddOp(v, OP_MemStore, iParm, 0, 0, 0);
   420         -    }else{
   421         -      sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
   422         -    }
   423         -  }
   424    552     return 0;
   425    553   }

Changes to src/sqliteInt.h.

    19     19   ** Author contact information:
    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** Internal interface definitions for SQLite.
    25     25   **
    26         -** @(#) $Id: sqliteInt.h,v 1.17 2000/06/06 13:54:15 drh Exp $
           26  +** @(#) $Id: sqliteInt.h,v 1.18 2000/06/06 17:27:05 drh Exp $
    27     27   */
    28     28   #include "sqlite.h"
    29     29   #include "dbbe.h"
    30     30   #include "vdbe.h"
    31     31   #include "parse.h"
    32     32   #include <gdbm.h>
    33     33   #include <stdio.h>
................................................................................
    84     84   typedef struct Expr Expr;
    85     85   typedef struct ExprList ExprList;
    86     86   typedef struct Parse Parse;
    87     87   typedef struct Token Token;
    88     88   typedef struct IdList IdList;
    89     89   typedef struct WhereInfo WhereInfo;
    90     90   typedef struct Select Select;
           91  +typedef struct AggExpr AggExpr;
    91     92   
    92     93   /*
    93     94   ** Each database is an instance of the following structure
    94     95   */
    95     96   struct sqlite {
    96     97     Dbbe *pBe;                 /* The backend driver */
    97     98     int flags;                 /* Miscellanous flags */
................................................................................
   157    158   */
   158    159   struct Expr {
   159    160     int op;                /* Operation performed by this node */
   160    161     Expr *pLeft, *pRight;  /* Left and right subnodes */
   161    162     ExprList *pList;       /* A list of expressions used as a function argument */
   162    163     Token token;           /* An operand token */
   163    164     int iTable, iField;    /* When op==TK_FIELD, then this node means the
   164         -                         ** iField-th field of the iTable-th table */
          165  +                         ** iField-th field of the iTable-th table.  When
          166  +                         ** op==TK_FUNCTION, iField holds the function id */
          167  +  int iAgg;              /* When op==TK_FIELD and pParse->useAgg==TRUE, pull
          168  +                         ** value from these element of the aggregator */
   165    169     Select *pSelect;       /* When the expression is a sub-select */
   166    170   };
   167    171   
   168    172   /*
   169    173   ** A list of expressions.  Each expression may optionally have a
   170    174   ** name.  An expr/name combination can be used in several ways, such
   171    175   ** as the list of "expr AS ID" fields following a "SELECT" or in the
................................................................................
   230    234   ** The results of a select can be distributed in several ways.
   231    235   */
   232    236   #define SRT_Callback     1  /* Invoke a callback with each row of result */
   233    237   #define SRT_Mem          2  /* Store result in a memory cell */
   234    238   #define SRT_Set          3  /* Store result in a table for use with "IN" */
   235    239   #define SRT_Table        4  /* Store result in a regular table */
   236    240   
          241  +/*
          242  +** When a SELECT uses aggregate functions (like "count(*)" or "avg(f1)")
          243  +** we have to do some additional analysis of expressions.  An instance
          244  +** of the following structure holds information about a single subexpression
          245  +** somewhere in the SELECT statement.  An array of these structures holds
          246  +** all the information we need to generate code for aggregate
          247  +** expressions.
          248  +**
          249  +** Note that when analyzing a SELECT containing aggregates, both
          250  +** non-aggregate field variables and aggregate functions are stored
          251  +** in the AggExpr array of the Parser structure.
          252  +**
          253  +** The pExpr field points to an expression that is part of either the
          254  +** field list, the GROUP BY clause, the HAVING clause or the ORDER BY
          255  +** clause.  The expression will be freed when those clauses are cleaned
          256  +** up.  Do not try to delete the expression attached to AggExpr.pExpr.
          257  +**
          258  +** If AggExpr.pExpr==0, that means the expression is "count(*)".
          259  +*/
          260  +struct AggExpr {
          261  +  int isAgg;        /* if TRUE contains an aggregate function */
          262  +  Expr *pExpr;      /* The expression */
          263  +};
          264  +
   237    265   /*
   238    266   ** An SQL parser context
   239    267   */
   240    268   struct Parse {
   241    269     sqlite *db;          /* The main database structure */
   242    270     sqlite_callback xCallback;  /* The callback function */
   243    271     void *pArg;          /* First argument to the callback function */
................................................................................
   249    277     Vdbe *pVdbe;         /* An engine for executing database bytecode */
   250    278     int explain;         /* True if the EXPLAIN flag is found on the query */
   251    279     int initFlag;        /* True if reparsing CREATE TABLEs */
   252    280     int nErr;            /* Number of errors seen */
   253    281     int nTab;            /* Number of previously allocated cursors */
   254    282     int nMem;            /* Number of memory cells used so far */
   255    283     int nSet;            /* Number of sets used so far */
          284  +  int nAgg;            /* Number of aggregate expressions */
          285  +  AggExpr *aAgg;       /* An array of aggregate expressions */
          286  +  int iAggCount;       /* Index of the count(*) aggregate in aAgg[] */
          287  +  int useAgg;          /* If true, extract field values from the aggregator
          288  +                       ** while generating expressions.  Normally false */
   256    289   };
   257    290   
   258    291   /*
   259    292   ** Internal function prototypes
   260    293   */
   261    294   int sqliteStrICmp(const char *, const char *);
   262    295   int sqliteStrNICmp(const char *, const char *, int);
................................................................................
   311    344   int sqliteGlobCompare(const char*,const char*);
   312    345   int sqliteLikeCompare(const unsigned char*,const unsigned char*);
   313    346   char *sqliteTableNameFromToken(Token*);
   314    347   int sqliteExprCheck(Parse*, Expr*, int, int*);
   315    348   int sqliteFuncId(Token*);
   316    349   int sqliteExprResolveIds(Parse*, IdList*, Expr*);
   317    350   void sqliteExprResolveInSelect(Parse*, Expr*);
          351  +int sqliteExprAnalyzeAggregates(Parse*, Expr*);
          352  +void sqlitePArseInfoReset(Parse*);

Changes to src/tokenize.c.

    23     23   *************************************************************************
    24     24   ** An tokenizer for SQL
    25     25   **
    26     26   ** This file contains C code that splits an SQL input string up into
    27     27   ** individual tokens and sends those tokens one-by-one over to the
    28     28   ** parser for analysis.
    29     29   **
    30         -** $Id: tokenize.c,v 1.6 2000/06/06 01:50:43 drh Exp $
           30  +** $Id: tokenize.c,v 1.7 2000/06/06 17:27:06 drh Exp $
    31     31   */
    32     32   #include "sqliteInt.h"
    33     33   #include <ctype.h>
    34     34   #include <stdlib.h>
    35     35   
    36     36   /*
    37     37   ** All the keywords of the SQL language are stored as in a hash
................................................................................
    64     64     { "DELIMITERS",        0, TK_DELIMITERS,       0 },
    65     65     { "DESC",              0, TK_DESC,             0 },
    66     66     { "DISTINCT",          0, TK_DISTINCT,         0 },
    67     67     { "DROP",              0, TK_DROP,             0 },
    68     68     { "EXPLAIN",           0, TK_EXPLAIN,          0 },
    69     69     { "FROM",              0, TK_FROM,             0 },
    70     70     { "GLOB",              0, TK_GLOB,             0 },
           71  +  { "GROUP",             0, TK_GROUP,            0 },
           72  +  { "HAVING",            0, TK_HAVING,           0 },
    71     73     { "IN",                0, TK_IN,               0 },
    72     74     { "INDEX",             0, TK_INDEX,            0 },
    73     75     { "INSERT",            0, TK_INSERT,           0 },
    74     76     { "INTO",              0, TK_INTO,             0 },
    75     77     { "IS",                0, TK_IS,               0 },
    76     78     { "ISNULL",            0, TK_ISNULL,           0 },
    77     79     { "KEY",               0, TK_KEY,              0 },
................................................................................
   294    296     static FILE *trace = 0;
   295    297     extern void *sqliteParserAlloc(void*(*)(int));
   296    298     extern void sqliteParserFree(void*, void(*)(void*));
   297    299     extern int sqliteParser(void*, int, ...);
   298    300     extern void sqliteParserTrace(FILE*, char *);
   299    301   
   300    302     i = 0;
          303  +  sqliteParseInfoReset(pParse);
   301    304     pEngine = sqliteParserAlloc((void*(*)(int))malloc);
   302    305     if( pEngine==0 ){
   303    306       sqliteSetString(pzErrMsg, "out of memory", 0);
   304    307       return 1;
   305    308     }
   306    309     sqliteParserTrace(trace, "parser: ");
   307    310     while( nErr==0 && i>=0 && zSql[i]!=0 ){
................................................................................
   378    381       sqliteVdbeDelete(pParse->pVdbe);
   379    382       pParse->pVdbe = 0;
   380    383     }
   381    384     if( pParse->pNewTable ){
   382    385       sqliteDeleteTable(pParse->db, pParse->pNewTable);
   383    386       pParse->pNewTable = 0;
   384    387     }
          388  +  sqliteParseInfoReset(pParse);
   385    389     return nErr;
   386    390   }

Changes to src/vdbe.c.

    37     37   ** inplicit conversion from one type to the other occurs as necessary.
    38     38   ** 
    39     39   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    40     40   ** function which does the work of interpreting a VDBE program.
    41     41   ** But other routines are also provided to help in building up
    42     42   ** a program instruction by instruction.
    43     43   **
    44         -** $Id: vdbe.c,v 1.20 2000/06/06 13:54:16 drh Exp $
           44  +** $Id: vdbe.c,v 1.21 2000/06/06 17:27:06 drh Exp $
    45     45   */
    46     46   #include "sqliteInt.h"
    47     47   #include <unistd.h>
    48     48   
    49     49   /*
    50     50   ** SQL is translated into a sequence of instructions to be
    51     51   ** executed by a virtual machine.  Each instruction is an instance
................................................................................
   419    419   /*
   420    420   ** Insert a new element and make it the current element.  
   421    421   **
   422    422   ** Return 0 on success and 1 if memory is exhausted.
   423    423   */
   424    424   static int AggInsert(Agg *p, char *zKey){
   425    425     AggElem *pElem;
   426         -  if( p->nHash < p->nElem*2 ){
          426  +  int i;
          427  +  if( p->nHash <= p->nElem*2 ){
   427    428       AggRehash(p, p->nElem*2 + 103);
   428    429     }
   429    430     if( p->nHash==0 ) return 1;
   430    431     pElem = sqliteMalloc( sizeof(AggElem) + strlen(zKey) + 1 +
   431    432                           (p->nMem-1)*sizeof(pElem->aMem[0]) );
   432    433     if( pElem==0 ) return 1;
   433    434     pElem->zKey = (char*)&pElem->aMem[p->nMem];
   434    435     strcpy(pElem->zKey, zKey);
   435    436     AggEnhash(p, pElem);
   436    437     pElem->pNext = p->pFirst;
   437    438     p->pFirst = pElem;
   438    439     p->nElem++;
   439    440     p->pCurrent = pElem;
          441  +  for(i=0; i<p->nMem; i++){
          442  +    pElem->aMem[i].s.flags = STK_Null;
          443  +  }
   440    444     return 0;
   441    445   }
   442    446   
   443    447   /*
   444    448   ** Get the AggElem currently in focus
   445    449   */
   446    450   #define AggInFocus(P)   ((P).pCurrent ? (P).pCurrent : _AggInFocus(&(P)))
   447    451   static AggElem *_AggInFocus(Agg *p){
   448    452     AggElem *pFocus = p->pFirst;
   449    453     if( pFocus ){
   450    454       p->pCurrent = pFocus;
   451    455     }else{
   452    456       AggInsert(p,"");
   453         -    pFocus = p->pCurrent;
          457  +    pFocus = p->pCurrent = p->pFirst;
   454    458     }
   455    459     return pFocus;
   456    460   }
   457    461   
   458    462   /*
   459    463   ** Erase all information from a Set
   460    464   */
................................................................................
  1141   1145         /* Opcode: Divide * * *
  1142   1146         **
  1143   1147         ** Pop the top two elements from the stack, divide the
  1144   1148         ** first (what was on top of the stack) from the second (the
  1145   1149         ** next on stack)
  1146   1150         ** and push the result back onto the stack.  If either element
  1147   1151         ** is a string then it is converted to a double using the atof()
  1148         -      ** function before the division.  Division by zero causes the
  1149         -      ** program to abort with an error.
         1152  +      ** function before the division.  Division by zero returns NULL.
  1150   1153         */
  1151   1154         case OP_Add:
  1152   1155         case OP_Subtract:
  1153   1156         case OP_Multiply:
  1154   1157         case OP_Divide: {
  1155   1158           int tos = p->tos;
  1156   1159           int nos = tos - 1;
................................................................................
  1160   1163             a = p->aStack[tos].i;
  1161   1164             b = p->aStack[nos].i;
  1162   1165             switch( pOp->opcode ){
  1163   1166               case OP_Add:         b += a;       break;
  1164   1167               case OP_Subtract:    b -= a;       break;
  1165   1168               case OP_Multiply:    b *= a;       break;
  1166   1169               default: {
  1167         -              if( a==0 ){ 
  1168         -                sqliteSetString(pzErrMsg, "division by zero", 0);
  1169         -                rc = SQLITE_ERROR;
  1170         -                goto cleanup;
  1171         -              }
         1170  +              if( a==0 ) goto divide_by_zero;
  1172   1171                 b /= a;
  1173   1172                 break;
  1174   1173               }
  1175   1174             }
  1176   1175             PopStack(p, 2);
  1177   1176             p->tos = nos;
  1178   1177             p->aStack[nos].i = b;
................................................................................
  1184   1183             a = p->aStack[tos].r;
  1185   1184             b = p->aStack[nos].r;
  1186   1185             switch( pOp->opcode ){
  1187   1186               case OP_Add:         b += a;       break;
  1188   1187               case OP_Subtract:    b -= a;       break;
  1189   1188               case OP_Multiply:    b *= a;       break;
  1190   1189               default: {
  1191         -              if( a==0.0 ){ 
  1192         -                sqliteSetString(pzErrMsg, "division by zero", 0);
  1193         -                rc = SQLITE_ERROR;
  1194         -                goto cleanup;
  1195         -              }
         1190  +              if( a==0.0 ) goto divide_by_zero;
  1196   1191                 b /= a;
  1197   1192                 break;
  1198   1193               }
  1199   1194             }
  1200   1195             PopStack(p, 1);
  1201   1196             Release(p, nos);
  1202   1197             p->aStack[nos].r = b;
  1203   1198             p->aStack[nos].flags = STK_Real;
  1204   1199           }
  1205   1200           break;
         1201  +
         1202  +      divide_by_zero:
         1203  +        PopStack(p, 2);
         1204  +        p->tos = nos;
         1205  +        p->aStack[nos].flags = STK_Null;
         1206  +        break;
  1206   1207         }
  1207   1208   
  1208   1209         /* Opcode: Max * * *
  1209   1210         **
  1210   1211         ** Pop the top two elements from the stack then push back the
  1211   1212         ** largest of the two.
  1212   1213         */
................................................................................
  1244   1245           break;
  1245   1246         }
  1246   1247   
  1247   1248         /* Opcode: Min * * *
  1248   1249         **
  1249   1250         ** Pop the top two elements from the stack then push back the
  1250   1251         ** smaller of the two. 
  1251         -      **
  1252         -      ** If P1==1, always choose TOS for the min and decrement P1.
  1253         -      ** This is self-altering code...
  1254   1252         */
  1255   1253         case OP_Min: {
  1256   1254           int tos = p->tos;
  1257   1255           int nos = tos - 1;
  1258   1256           int ft, fn;
  1259   1257           int copy = 0;
  1260   1258           if( nos<0 ) goto not_enough_stack;
  1261   1259           ft = p->aStack[tos].flags;
  1262   1260           fn = p->aStack[nos].flags;
  1263         -        if( pOp->p1 ){
         1261  +        if( fn & STK_Null ){
  1264   1262             copy = 1;
  1265         -          pOp->p1 = 0;
  1266         -        }else if( fn & STK_Null ){
  1267         -          copy = 1;
         1263  +        }else if( ft & STK_Null ){
         1264  +          copy = 0;
  1268   1265           }else if( (ft & fn & STK_Int)==STK_Int ){
  1269   1266             copy = p->aStack[nos].i>p->aStack[tos].i;
  1270   1267           }else if( ( (ft|fn) & (STK_Int|STK_Real) ) !=0 ){
  1271   1268             Realify(p, tos);
  1272   1269             Realify(p, nos);
  1273   1270             copy = p->aStack[tos].r<p->aStack[nos].r;
  1274   1271           }else{
................................................................................
  2724   2721           }
  2725   2722           if( pElem ){
  2726   2723             p->agg.pCurrent = pElem;
  2727   2724             pc = pOp->p2 - 1;
  2728   2725           }else{
  2729   2726             AggInsert(&p->agg, zKey);
  2730   2727           }
         2728  +        PopStack(p, 1);
  2731   2729           break; 
  2732   2730         }
  2733   2731   
  2734   2732         /* Opcode: AggIncr P1 P2 *
  2735   2733         **
  2736   2734         ** Increment the P2-th field of the aggregate element current
  2737   2735         ** in focus by an amount P1.

Changes to test/select1.test.

    19     19   #   drh@hwaci.com
    20     20   #   http://www.hwaci.com/drh/
    21     21   #
    22     22   #***********************************************************************
    23     23   # This file implements regression tests for SQLite library.  The
    24     24   # focus of this file is testing the SELECT statement.
    25     25   #
    26         -# $Id: select1.test,v 1.2 2000/05/31 18:20:14 drh Exp $
           26  +# $Id: select1.test,v 1.3 2000/06/06 17:27:06 drh Exp $
    27     27   
    28     28   set testdir [file dirname $argv0]
    29     29   source $testdir/tester.tcl
    30     30   
    31     31   # Try to select on a non-existant table.
    32     32   #
    33         -do_test select-1.1 {
           33  +do_test select1-1.1 {
    34     34     set v [catch {execsql {SELECT * FROM test1}} msg]
    35     35     lappend v $msg
    36     36   } {1 {no such table: test1}}
    37     37   
    38     38   execsql {CREATE TABLE test1(f1 int, f2 int)}
    39     39   
    40         -do_test select-1.2 {
           40  +do_test select1-1.2 {
    41     41     set v [catch {execsql {SELECT * FROM test1, test2}} msg]
    42     42     lappend v $msg
    43     43   } {1 {no such table: test2}}
    44         -do_test select-1.3 {
           44  +do_test select1-1.3 {
    45     45     set v [catch {execsql {SELECT * FROM test2, test1}} msg]
    46     46     lappend v $msg
    47     47   } {1 {no such table: test2}}
    48     48   
    49     49   execsql {INSERT INTO test1(f1,f2) VALUES(11,22)}
    50     50   
    51     51   
    52     52   # Make sure the fields are extracted correctly.
    53     53   #
    54         -do_test select-1.4 {
           54  +do_test select1-1.4 {
    55     55     execsql {SELECT f1 FROM test1}
    56     56   } {11}
    57         -do_test select-1.5 {
           57  +do_test select1-1.5 {
    58     58     execsql {SELECT f2 FROM test1}
    59     59   } {22}
    60         -do_test select-1.6 {
           60  +do_test select1-1.6 {
    61     61     execsql {SELECT f2, f1 FROM test1}
    62     62   } {22 11}
    63         -do_test select-1.7 {
           63  +do_test select1-1.7 {
    64     64     execsql {SELECT f1, f2 FROM test1}
    65     65   } {11 22}
    66         -do_test select-1.8 {
           66  +do_test select1-1.8 {
    67     67     execsql {SELECT * FROM test1}
    68     68   } {11 22}
    69     69   
    70     70   execsql {CREATE TABLE test2(r1 real, r2 real)}
    71     71   execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}
    72     72   
    73         -do_test select-1.9 {
           73  +do_test select1-1.9 {
    74     74     execsql {SELECT * FROM test1, test2}
    75     75   } {11 22 1.1 2.2}
    76         -do_test select-1.10 {
           76  +do_test select1-1.10 {
    77     77     execsql {SELECT test1.f1, test2.r1 FROM test1, test2}
    78     78   } {11 1.1}
    79         -do_test select-1.11 {
           79  +do_test select1-1.11 {
    80     80     execsql {SELECT test1.f1, test2.r1 FROM test2, test1}
    81     81   } {11 1.1}
    82         -do_test select-1.12 {
           82  +do_test select1-1.12 {
    83     83     execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2)
    84     84              FROM test2, test1}
    85     85   } {11 2.2}
    86         -do_test select-1.13 {
           86  +do_test select1-1.13 {
    87     87     execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2)
    88     88              FROM test1, test2}
    89     89   } {1.1 22}
    90     90   
    91     91   execsql {DROP TABLE test2}
    92     92   execsql {DELETE FROM test1}
    93     93   execsql {INSERT INTO test1 VALUES(11,22)}
    94     94   execsql {INSERT INTO test1 VALUES(33,44)}
    95     95   
    96     96   # Error messges from sqliteExprCheck
    97     97   #
    98         -do_test select-2.1 {
           98  +do_test select1-2.1 {
    99     99     set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg]
   100    100     lappend v $msg
   101    101   } {1 {too many arguments to function count()}}
   102         -do_test select-2.2 {
          102  +do_test select1-2.2 {
   103    103     set v [catch {execsql {SELECT count(f1) FROM test1}} msg]
   104    104     lappend v $msg
   105    105   } {0 2}
   106         -do_test select-2.3 {
          106  +do_test select1-2.3 {
   107    107     set v [catch {execsql {SELECT Count() FROM test1}} msg]
   108    108     lappend v $msg
   109    109   } {0 2}
   110         -do_test select-2.4 {
          110  +do_test select1-2.4 {
   111    111     set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg]
   112    112     lappend v $msg
   113    113   } {0 2}
   114         -do_test select-2.5 {
          114  +do_test select1-2.5 {
   115    115     set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg]
   116    116     lappend v $msg
   117         -} {1 {no such function: COUNT}}
   118         -do_test select-2.6 {
          117  +} {0 3}
          118  +do_test select1-2.6 {
   119    119     set v [catch {execsql {SELECT min(*) FROM test1}} msg]
   120    120     lappend v $msg
   121    121   } {1 {too few arguments to function min()}}
   122         -do_test select-2.7 {
          122  +do_test select1-2.7 {
   123    123     set v [catch {execsql {SELECT Min(f1) FROM test1}} msg]
   124    124     lappend v $msg
   125    125   } {0 11}
   126         -do_test select-2.8 {
          126  +do_test select1-2.8 {
   127    127     set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
   128    128     lappend v [lsort $msg]
   129    129   } {0 {11 33}}
   130         -do_test select-2.9 {
          130  +do_test select1-2.9 {
   131    131     set v [catch {execsql {SELECT MAX(*) FROM test1}} msg]
   132    132     lappend v $msg
   133    133   } {1 {too few arguments to function MAX()}}
   134         -do_test select-2.10 {
          134  +do_test select1-2.10 {
   135    135     set v [catch {execsql {SELECT Max(f1) FROM test1}} msg]
   136    136     lappend v $msg
   137    137   } {0 33}
   138         -do_test select-2.11 {
          138  +do_test select1-2.11 {
   139    139     set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg]
   140    140     lappend v [lsort $msg]
   141    141   } {0 {22 44}}
   142         -do_test select-2.12 {
          142  +do_test select1-2.12 {
   143    143     set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg]
   144    144     lappend v [lsort $msg]
   145    145   } {0 {23 45}}
   146         -do_test select-2.13 {
          146  +do_test select1-2.13 {
   147    147     set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg]
   148    148     lappend v $msg
   149         -} {1 {too few arguments to function MAX()}}
   150         -do_test select-2.14 {
          149  +} {0 34}
          150  +do_test select1-2.14 {
   151    151     set v [catch {execsql {SELECT SUM(*) FROM test1}} msg]
   152    152     lappend v $msg
   153    153   } {1 {too few arguments to function SUM()}}
   154         -do_test select-2.15 {
          154  +do_test select1-2.15 {
   155    155     set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg]
   156    156     lappend v $msg
   157    157   } {0 44}
   158         -do_test select-2.16 {
          158  +do_test select1-2.16 {
   159    159     set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg]
   160    160     lappend v $msg
   161    161   } {1 {too many arguments to function sum()}}
   162         -do_test select-2.17 {
          162  +do_test select1-2.17 {
   163    163     set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg]
   164    164     lappend v $msg
   165         -} {1 {no such function: SUM}}
   166         -do_test select-2.18 {
          165  +} {0 45}
          166  +do_test select1-2.18 {
   167    167     set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg]
   168    168     lappend v $msg
   169    169   } {1 {no such function: XYZZY}}
   170         -do_test select-2.19 {
          170  +do_test select1-2.19 {
   171    171     set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg]
   172    172     lappend v $msg
   173    173   } {0 44}
   174         -do_test select-2.20 {
          174  +do_test select1-2.20 {
   175    175     set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg]
   176    176     lappend v $msg
   177    177   } {1 {too few arguments to function min()}}
   178    178   
   179    179   # WHERE clause expressions
   180    180   #
   181         -do_test select-3.1 {
          181  +do_test select1-3.1 {
   182    182     set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg]
   183    183     lappend v $msg
   184    184   } {0 {}}
   185         -do_test select-3.2 {
          185  +do_test select1-3.2 {
   186    186     set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg]
   187    187     lappend v $msg
   188    188   } {0 11}
   189         -do_test select-3.3 {
          189  +do_test select1-3.3 {
   190    190     set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg]
   191    191     lappend v $msg
   192    192   } {0 11}
   193         -do_test select-3.4 {
          193  +do_test select1-3.4 {
   194    194     set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg]
   195    195     lappend v [lsort $msg]
   196    196   } {0 {11 33}}
   197         -do_test select-3.5 {
          197  +do_test select1-3.5 {
   198    198     set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg]
   199    199     lappend v [lsort $msg]
   200    200   } {0 33}
   201         -do_test select-3.6 {
          201  +do_test select1-3.6 {
   202    202     set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg]
   203    203     lappend v [lsort $msg]
   204    204   } {0 33}
   205         -do_test select-3.7 {
          205  +do_test select1-3.7 {
   206    206     set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg]
   207    207     lappend v [lsort $msg]
   208    208   } {0 33}
   209         -do_test select-3.8 {
          209  +do_test select1-3.8 {
   210    210     set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg]
   211    211     lappend v [lsort $msg]
   212    212   } {0 {11 33}}
   213         -do_test select-3.9 {
          213  +do_test select1-3.9 {
   214    214     set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg]
   215    215     lappend v $msg
   216    216   } {1 {no such function: count}}
   217    217   
   218    218   # ORDER BY expressions
   219    219   #
   220         -do_test select-4.1 {
          220  +do_test select1-4.1 {
   221    221     set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg]
   222    222     lappend v $msg
   223    223   } {0 {11 33}}
   224         -do_test select-4.2 {
          224  +do_test select1-4.2 {
   225    225     set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg]
   226    226     lappend v $msg
   227    227   } {0 {33 11}}
   228         -do_test select-4.3 {
          228  +do_test select1-4.3 {
   229    229     set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
   230    230     lappend v $msg
   231    231   } {0 {11 33}}
   232         -do_test select-4.4 {
          232  +do_test select1-4.4 {
   233    233     set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
   234    234     lappend v $msg
   235    235   } {1 {too few arguments to function min()}}
   236    236   
   237    237   # ORDER BY ignored on an aggregate query
   238    238   #
   239         -do_test select-5.1 {
          239  +do_test select1-5.1 {
   240    240     set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
   241    241     lappend v $msg
   242    242   } {0 33}
   243    243   
   244    244   execsql {CREATE TABLE test2(t1 test, t2 text)}
   245    245   execsql {INSERT INTO test2 VALUES('abc','xyz')}
   246    246   
   247    247   # Check for field naming
   248    248   #
   249         -do_test select-6.1 {
          249  +do_test select1-6.1 {
   250    250     set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
   251    251     lappend v $msg
   252    252   } {0 {f1 11 f1 33}}
   253         -do_test select-6.2 {
          253  +do_test select1-6.2 {
   254    254     set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]
   255    255     lappend v $msg
   256    256   } {0 {xyzzy 11 xyzzy 33}}
   257         -do_test select-6.3 {
          257  +do_test select1-6.3 {
   258    258     set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
   259    259     lappend v $msg
   260    260   } {0 {xyzzy 11 xyzzy 33}}
   261         -do_test select-6.4 {
          261  +do_test select1-6.4 {
   262    262     set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
   263    263     lappend v $msg
   264    264   } {0 {xyzzy 33 xyzzy 77}}
   265         -do_test select-6.5 {
          265  +do_test select1-6.5 {
   266    266     set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
   267    267     lappend v $msg
   268    268   } {0 {field1 33 field1 77}}
   269         -do_test select-6.6 {
          269  +do_test select1-6.6 {
   270    270     set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 
   271    271            ORDER BY f2}} msg]
   272    272     lappend v $msg
   273    273   } {0 {field1 33 test2.t1 abc field1 77 test2.t1 abc}}
   274         -do_test select-6.7 {
          274  +do_test select1-6.7 {
   275    275     set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 
   276    276            ORDER BY f2}} msg]
   277    277     lappend v $msg
   278    278   } {0 {A.f1 11 test2.t1 abc A.f1 33 test2.t1 abc}}
   279         -do_test select-6.8 {
          279  +do_test select1-6.8 {
   280    280     set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 
   281    281            ORDER BY f2}} msg]
   282    282     lappend v $msg
   283    283   } {1 {ambiguous field name: f1}}
   284         -do_test select-6.8 {
          284  +do_test select1-6.8 {
   285    285     set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
   286    286            ORDER BY f2}} msg]
   287    287     lappend v $msg
   288    288   } {1 {ambiguous field name: f2}}
   289         -do_test select-6.9 {
          289  +do_test select1-6.9 {
   290    290     set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
   291    291            ORDER BY A.f1, B.f1}} msg]
   292    292     lappend v $msg
   293    293   } {0 {A.f1 11 B.f1 11 A.f1 11 B.f1 33 A.f1 33 B.f1 11 A.f1 33 B.f1 33}}
   294    294   
   295    295   finish_test