/ Check-in [cbbc858d]
Login

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

Overview
Comment:This patch contains the beginnings of the data-typing infrastructure. The new build-in TypeOf() function is added. New opcodes for doing pure text comparisons are added. Most changes are disabled pending the 2.6.0 release. (CVS 632)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:cbbc858d973c2d515c6a2464981316549a241b73
User & Date: drh 2002-06-20 11:36:49
Context
2002-06-20
11:41
Update the file-format change document to describes the changes between versions 2.4.12 and 2.5.0. (CVS 633) check-in: d28a2ecc user: drh tags: trunk
11:36
This patch contains the beginnings of the data-typing infrastructure. The new build-in TypeOf() function is added. New opcodes for doing pure text comparisons are added. Most changes are disabled pending the 2.6.0 release. (CVS 632) check-in: cbbc858d user: drh tags: trunk
03:38
Fix for ticket #73: The ORDER BY clause is significant for subqueries. This passes all regression tests, but more testing is needed to exercise all paths through the new code. (CVS 631) check-in: 43c5aff5 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

    21     21   **     COPY
    22     22   **     VACUUM
    23     23   **     BEGIN TRANSACTION
    24     24   **     COMMIT
    25     25   **     ROLLBACK
    26     26   **     PRAGMA
    27     27   **
    28         -** $Id: build.c,v 1.96 2002/06/17 17:07:20 drh Exp $
           28  +** $Id: build.c,v 1.97 2002/06/20 11:36:49 drh Exp $
    29     29   */
    30     30   #include "sqliteInt.h"
    31     31   #include <ctype.h>
    32     32   
    33     33   /*
    34     34   ** This routine is called after a single SQL statement has been
    35     35   ** parsed and we want to execute the VDBE code to implement 
................................................................................
   500    500   ** first to get things going.  Then this routine is called for each
   501    501   ** column.
   502    502   */
   503    503   void sqliteAddColumn(Parse *pParse, Token *pName){
   504    504     Table *p;
   505    505     int i;
   506    506     char *z = 0;
          507  +  Column *pCol;
   507    508     if( (p = pParse->pNewTable)==0 ) return;
   508    509     sqliteSetNString(&z, pName->z, pName->n, 0);
   509    510     if( z==0 ) return;
   510    511     sqliteDequote(z);
   511    512     for(i=0; i<p->nCol; i++){
   512    513       if( sqliteStrICmp(z, p->aCol[i].zName)==0 ){
   513    514         sqliteSetString(&pParse->zErrMsg, "duplicate column name: ", z, 0);
................................................................................
   518    519     }
   519    520     if( (p->nCol & 0x7)==0 ){
   520    521       Column *aNew;
   521    522       aNew = sqliteRealloc( p->aCol, (p->nCol+8)*sizeof(p->aCol[0]));
   522    523       if( aNew==0 ) return;
   523    524       p->aCol = aNew;
   524    525     }
   525         -  memset(&p->aCol[p->nCol], 0, sizeof(p->aCol[0]));
   526         -  p->aCol[p->nCol++].zName = z;
          526  +  pCol = &p->aCol[p->nCol];
          527  +  memset(pCol, 0, sizeof(p->aCol[0]));
          528  +  pCol->zName = z;
          529  +  pCol->sortOrder = SQLITE_SO_NUM;
          530  +  p->nCol++;
   527    531   }
   528    532   
   529    533   /*
   530    534   ** This routine is called by the parser while in the middle of
   531    535   ** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
   532    536   ** been seen on a column.  This routine sets the notNull flag on
   533    537   ** the column currently under construction.
................................................................................
   550    554   ** in zType.
   551    555   */ 
   552    556   void sqliteAddColumnType(Parse *pParse, Token *pFirst, Token *pLast){
   553    557     Table *p;
   554    558     int i, j;
   555    559     int n;
   556    560     char *z, **pz;
          561  +  Column *pCol;
   557    562     if( (p = pParse->pNewTable)==0 ) return;
   558    563     i = p->nCol-1;
   559    564     if( i<0 ) return;
   560         -  pz = &p->aCol[i].zType;
          565  +  pCol = &p->aCol[i];
          566  +  pz = &pCol->zType;
   561    567     n = pLast->n + Addr(pLast->z) - Addr(pFirst->z);
   562    568     sqliteSetNString(pz, pFirst->z, n, 0);
   563    569     z = *pz;
   564    570     if( z==0 ) return;
   565    571     for(i=j=0; z[i]; i++){
   566    572       int c = z[i];
   567    573       if( isspace(c) ) continue;
   568    574       z[j++] = c;
   569    575     }
   570    576     z[j] = 0;
          577  +  pCol->sortOrder = SQLITE_SO_NUM;
          578  +  for(i=0; z[i]; i++){
          579  +    switch( z[i] ){
          580  +      case 'c':
          581  +      case 'C': {
          582  +        if( sqliteStrNICmp(&z[i],"char",4)==0 ||
          583  +                sqliteStrNICmp(&z[i],"clob",4)==0 ){
          584  +          pCol->sortOrder = SQLITE_SO_TEXT;
          585  +          return;
          586  +        }
          587  +        break;
          588  +      }
          589  +      case 'x':
          590  +      case 'X': {
          591  +        if( i>=2 && sqliteStrNICmp(&z[i-2],"text",4)==0 ){
          592  +          pCol->sortOrder = SQLITE_SO_TEXT;
          593  +          return;
          594  +        }
          595  +        break;
          596  +      }
          597  +      default: {
          598  +        break;
          599  +      }
          600  +    }
          601  +  }
   571    602   }
   572    603   
   573    604   /*
   574    605   ** The given token is the default value for the last column added to
   575    606   ** the table currently under construction.  If "minusFlag" is true, it
   576    607   ** means the value token was preceded by a minus sign.
   577    608   **

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.72 2002/06/17 17:07:20 drh Exp $
           15  +** $Id: expr.c,v 1.73 2002/06/20 11:36:49 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Construct a new expression node and return a pointer to it.  Memory
    22     22   ** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
   456    456           for(j=0; j<pTab->nCol; j++){
   457    457             if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
   458    458               cnt++;
   459    459               pExpr->iTable = i + base;
   460    460               if( j==pTab->iPKey ){
   461    461                 /* Substitute the record number for the INTEGER PRIMARY KEY */
   462    462                 pExpr->iColumn = -1;
          463  +              pExpr->dataType = SQLITE_SO_NUM;
   463    464               }else{
   464    465                 pExpr->iColumn = j;
          466  +              pExpr->dataType = pTab->aCol[j].sortOrder & SQLITE_SO_TYPEMASK;
   465    467               }
   466    468               pExpr->op = TK_COLUMN;
   467    469             }
   468    470           }
   469    471         }
   470    472         if( cnt==0 && pEList!=0 ){
   471    473           int j;
................................................................................
   481    483           } 
   482    484         }
   483    485         if( cnt==0 && sqliteIsRowid(z) ){
   484    486           pExpr->iColumn = -1;
   485    487           pExpr->iTable = base;
   486    488           cnt = 1 + (pTabList->nSrc>1);
   487    489           pExpr->op = TK_COLUMN;
          490  +        pExpr->dataType = SQLITE_SO_NUM;
   488    491         }
   489    492         sqliteFree(z);
   490    493         if( cnt==0 && pExpr->token.z[0]!='"' ){
   491    494           sqliteSetNString(&pParse->zErrMsg, "no such column: ", -1,  
   492    495             pExpr->token.z, pExpr->token.n, 0);
   493    496           pParse->nErr++;
   494    497           return 1;
................................................................................
   542    545               pExpr->iTable = i + base;
   543    546               if( j==pTab->iPKey ){
   544    547                 /* Substitute the record number for the INTEGER PRIMARY KEY */
   545    548                 pExpr->iColumn = -1;
   546    549               }else{
   547    550                 pExpr->iColumn = j;
   548    551               }
          552  +            pExpr->dataType = pTab->aCol[j].sortOrder & SQLITE_SO_TYPEMASK;
   549    553             }
   550    554           }
   551    555         }
   552    556   
   553    557         /* If we have not already resolved this *.* expression, then maybe 
   554    558          * it is a new.* or old.* trigger argument reference */
   555    559         if( cnt == 0 && pParse->trigStack != 0 ){
................................................................................
   564    568             pExpr->iTable = pTriggerStack->oldIdx;
   565    569             cntTab++;
   566    570             t = 1;
   567    571           }
   568    572   
   569    573           if( t ){ 
   570    574   	  int j;
   571         -          for(j=0; j < pTriggerStack->pTab->nCol; j++) {
   572         -            if( sqliteStrICmp(pTriggerStack->pTab->aCol[j].zName, zRight)==0 ){
          575  +          Table *pTab = pTriggerStack->pTab;
          576  +          for(j=0; j < pTab->nCol; j++) {
          577  +            if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
   573    578                 cnt++;
   574    579                 pExpr->iColumn = j;
          580  +              pExpr->dataType = pTab->aCol[j].sortOrder & SQLITE_SO_TYPEMASK;
   575    581               }
   576    582             }
   577    583   	}
   578    584         }
   579    585   
   580    586         if( cnt==0 && cntTab==1 && sqliteIsRowid(zRight) ){
   581    587           cnt = 1;
   582    588           pExpr->iColumn = -1;
          589  +        pExpr->dataType = SQLITE_SO_NUM;
   583    590         }
   584    591         sqliteFree(zLeft);
   585    592         sqliteFree(zRight);
   586    593         if( cnt==0 ){
   587    594           sqliteSetNString(&pParse->zErrMsg, "no such column: ", -1,  
   588    595             pLeft->token.z, pLeft->token.n, ".", 1, 
   589    596             pRight->token.z, pRight->token.n, 0);
................................................................................
   710    717   ** (like count(*) or max(value)) then write a 1 into *pIsAgg.
   711    718   */
   712    719   int sqliteExprCheck(Parse *pParse, Expr *pExpr, int allowAgg, int *pIsAgg){
   713    720     int nErr = 0;
   714    721     if( pExpr==0 ) return 0;
   715    722     switch( pExpr->op ){
   716    723       case TK_FUNCTION: {
   717         -      int n = pExpr->pList ? pExpr->pList->nExpr : 0;
   718         -      int no_such_func = 0;
   719         -      int wrong_num_args = 0;
   720         -      int is_agg = 0;
          724  +      int n = pExpr->pList ? pExpr->pList->nExpr : 0;  /* Number of arguments */
          725  +      int no_such_func = 0;       /* True if no such function exists */
          726  +      int is_type_of = 0;         /* True if is the special TypeOf() function */
          727  +      int wrong_num_args = 0;     /* True if wrong number of arguments */
          728  +      int is_agg = 0;             /* True if is an aggregate function */
   721    729         int i;
   722    730         FuncDef *pDef;
   723    731   
   724    732         pDef = sqliteFindFunction(pParse->db,
   725    733            pExpr->token.z, pExpr->token.n, n, 0);
   726    734         if( pDef==0 ){
   727    735           pDef = sqliteFindFunction(pParse->db,
   728    736              pExpr->token.z, pExpr->token.n, -1, 0);
   729    737           if( pDef==0 ){
   730         -          no_such_func = 1;
          738  +          if( n==1 && pExpr->token.n==6
          739  +               && sqliteStrNICmp(pExpr->token.z, "typeof", 6)==0 ){
          740  +            is_type_of = 1;
          741  +          }else {
          742  +            no_such_func = 1;
          743  +          }
   731    744           }else{
   732    745             wrong_num_args = 1;
   733    746           }
   734    747         }else{
   735    748           is_agg = pDef->xFunc==0;
   736    749         }
   737    750         if( is_agg && !allowAgg ){
................................................................................
   754    767         }
   755    768         if( is_agg ) pExpr->op = TK_AGG_FUNCTION;
   756    769         if( is_agg && pIsAgg ) *pIsAgg = 1;
   757    770         for(i=0; nErr==0 && i<n; i++){
   758    771           nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr,
   759    772                                  allowAgg && !is_agg, pIsAgg);
   760    773         }
          774  +      if( pDef==0 ){
          775  +        if( is_type_of ){
          776  +          pExpr->op = TK_STRING;
          777  +          if( sqliteExprType(pExpr->pList->a[0].pExpr)==SQLITE_SO_NUM ){
          778  +            pExpr->token.z = "numeric";
          779  +            pExpr->token.n = 7;
          780  +          }else{
          781  +            pExpr->token.z = "text";
          782  +            pExpr->token.n = 4;
          783  +          }
          784  +        }
          785  +      }else if( pDef->dataType>=0 ){
          786  +        if( pDef->dataType<n ){
          787  +          pExpr->dataType = 
          788  +             sqliteExprType(pExpr->pList->a[pDef->dataType].pExpr);
          789  +        }else{
          790  +          pExpr->dataType = SQLITE_SO_NUM;
          791  +        }
          792  +      }else if( pDef->dataType==SQLITE_ARGS ){
          793  +        pDef->dataType = SQLITE_SO_TEXT;
          794  +        for(i=0; i<n; i++){
          795  +          if( sqliteExprType(pExpr->pList->a[i].pExpr)==SQLITE_SO_NUM ){
          796  +            pExpr->dataType = SQLITE_SO_NUM;
          797  +            break;
          798  +          }
          799  +        }
          800  +      }else if( pDef->dataType==SQLITE_NUMERIC ){
          801  +        pExpr->dataType = SQLITE_SO_NUM;
          802  +      }else{
          803  +        pExpr->dataType = SQLITE_SO_TEXT;
          804  +      }
   761    805       }
   762    806       default: {
   763    807         if( pExpr->pLeft ){
   764    808           nErr = sqliteExprCheck(pParse, pExpr->pLeft, allowAgg, pIsAgg);
   765    809         }
   766    810         if( nErr==0 && pExpr->pRight ){
   767    811           nErr = sqliteExprCheck(pParse, pExpr->pRight, allowAgg, pIsAgg);
................................................................................
   775    819           }
   776    820         }
   777    821         break;
   778    822       }
   779    823     }
   780    824     return nErr;
   781    825   }
          826  +
          827  +/*
          828  +** Return either SQLITE_SO_NUM or SQLITE_SO_TEXT to indicate whether the
          829  +** given expression should sort as numeric values or as text.
          830  +**
          831  +** The sqliteExprResolveIds() and sqliteExprCheck() routines must have
          832  +** both been called on the expression before it is passed to this routine.
          833  +*/
          834  +int sqliteExprType(Expr *p){
          835  +  if( p==0 ) return SQLITE_SO_NUM;
          836  +  while( p ) switch( p->op ){
          837  +    case TK_PLUS:
          838  +    case TK_MINUS:
          839  +    case TK_STAR:
          840  +    case TK_SLASH:
          841  +    case TK_AND:
          842  +    case TK_OR:
          843  +    case TK_ISNULL:
          844  +    case TK_NOTNULL:
          845  +    case TK_NOT:
          846  +    case TK_UMINUS:
          847  +    case TK_BITAND:
          848  +    case TK_BITOR:
          849  +    case TK_BITNOT:
          850  +    case TK_LSHIFT:
          851  +    case TK_RSHIFT:
          852  +    case TK_REM:
          853  +    case TK_INTEGER:
          854  +    case TK_FLOAT:
          855  +    case TK_IN:
          856  +    case TK_BETWEEN:
          857  +      return SQLITE_SO_NUM;
          858  +
          859  +    case TK_STRING:
          860  +    case TK_NULL:
          861  +    case TK_CONCAT:
          862  +      return SQLITE_SO_TEXT;
          863  +
          864  +    case TK_LT:
          865  +    case TK_LE:
          866  +    case TK_GT:
          867  +    case TK_GE:
          868  +    case TK_NE:
          869  +    case TK_EQ:
          870  +      if( sqliteExprType(p->pLeft)==SQLITE_SO_NUM ){
          871  +        return SQLITE_SO_NUM;
          872  +      }
          873  +      p = p->pRight;
          874  +      break;
          875  +
          876  +    case TK_AS:
          877  +      p = p->pLeft;
          878  +      break;
          879  +
          880  +    case TK_COLUMN:
          881  +    case TK_FUNCTION:
          882  +    case TK_AGG_FUNCTION:
          883  +      return p->dataType;
          884  +
          885  +    case TK_SELECT:
          886  +      assert( p->pSelect );
          887  +      assert( p->pSelect->pEList );
          888  +      assert( p->pSelect->pEList->nExpr>0 );
          889  +      p = p->pSelect->pEList->a[0].pExpr;
          890  +      break;
          891  +
          892  +    default:
          893  +      assert( p->op==TK_ABORT );  /* Can't Happen */
          894  +      break;
          895  +  }
          896  +  return SQLITE_SO_NUM;
          897  +}
   782    898   
   783    899   /*
   784    900   ** Generate code into the current Vdbe to evaluate the given
   785    901   ** expression and leave the result on the top of stack.
   786    902   */
   787    903   void sqliteExprCode(Parse *pParse, Expr *pExpr){
   788    904     Vdbe *v = pParse->pVdbe;
................................................................................
   852    968         sqliteVdbeDequoteP3(v, addr);
   853    969         break;
   854    970       }
   855    971       case TK_NULL: {
   856    972         sqliteVdbeAddOp(v, OP_String, 0, 0);
   857    973         break;
   858    974       }
          975  +    case TK_LT:
          976  +    case TK_LE:
          977  +    case TK_GT:
          978  +    case TK_GE:
          979  +    case TK_NE:
          980  +    case TK_EQ: {
          981  +      if( pParse->db->file_format>=3 && sqliteExprType(pExpr)==SQLITE_SO_TEXT ){
          982  +        op += 6;  /* Convert numeric opcodes to text opcodes */
          983  +      }
          984  +      /* Fall through into the next case */
          985  +    }
   859    986       case TK_AND:
   860    987       case TK_OR:
   861    988       case TK_PLUS:
   862    989       case TK_STAR:
   863    990       case TK_MINUS:
   864    991       case TK_REM:
   865    992       case TK_BITAND:
   866    993       case TK_BITOR:
   867         -    case TK_SLASH:
   868         -    case TK_LT:
   869         -    case TK_LE:
   870         -    case TK_GT:
   871         -    case TK_GE:
   872         -    case TK_NE:
   873         -    case TK_EQ: {
          994  +    case TK_SLASH: {
   874    995         sqliteExprCode(pParse, pExpr->pLeft);
   875    996         sqliteExprCode(pParse, pExpr->pRight);
   876    997         sqliteVdbeAddOp(v, op, 0, 0);
   877    998         break;
   878    999       }
   879   1000       case TK_LSHIFT:
   880   1001       case TK_RSHIFT: {
................................................................................
  1086   1207       case TK_LE:
  1087   1208       case TK_GT:
  1088   1209       case TK_GE:
  1089   1210       case TK_NE:
  1090   1211       case TK_EQ: {
  1091   1212         sqliteExprCode(pParse, pExpr->pLeft);
  1092   1213         sqliteExprCode(pParse, pExpr->pRight);
         1214  +      if( pParse->db->file_format>=3 && sqliteExprType(pExpr)==SQLITE_SO_TEXT ){
         1215  +        op += 6;  /* Convert numeric opcodes to text opcodes */
         1216  +      }
  1093   1217         sqliteVdbeAddOp(v, op, jumpIfNull, dest);
  1094   1218         break;
  1095   1219       }
  1096   1220       case TK_ISNULL:
  1097   1221       case TK_NOTNULL: {
  1098   1222         sqliteExprCode(pParse, pExpr->pLeft);
  1099   1223         sqliteVdbeAddOp(v, op, 1, dest);
................................................................................
  1176   1300       }
  1177   1301       case TK_LT:
  1178   1302       case TK_LE:
  1179   1303       case TK_GT:
  1180   1304       case TK_GE:
  1181   1305       case TK_NE:
  1182   1306       case TK_EQ: {
         1307  +      if( pParse->db->file_format>=3 && sqliteExprType(pExpr)==SQLITE_SO_TEXT ){
         1308  +        op += 6;  /* Convert numeric opcodes to text opcodes */
         1309  +      }
  1183   1310         sqliteExprCode(pParse, pExpr->pLeft);
  1184   1311         sqliteExprCode(pParse, pExpr->pRight);
  1185   1312         sqliteVdbeAddOp(v, op, jumpIfNull, dest);
  1186   1313         break;
  1187   1314       }
  1188   1315       case TK_ISNULL:
  1189   1316       case TK_NOTNULL: {
................................................................................
  1391   1518     if( p==0 && pMaybe ){
  1392   1519       assert( createFlag==0 );
  1393   1520       return pMaybe;
  1394   1521     }
  1395   1522     if( p==0 && createFlag && (p = sqliteMalloc(sizeof(*p)))!=0 ){
  1396   1523       p->nArg = nArg;
  1397   1524       p->pNext = pFirst;
         1525  +    p->dataType = pFirst ? pFirst->dataType : SQLITE_NUMERIC;
  1398   1526       sqliteHashInsert(&db->aFunc, zName, nName, (void*)p);
  1399   1527     }
  1400   1528     return p;
  1401   1529   }

Changes to src/func.c.

    12     12   ** This file contains the C functions that implement various SQL
    13     13   ** functions of SQLite.  
    14     14   **
    15     15   ** There is only one exported symbol in this file - the function
    16     16   ** sqliteRegisterBuildinFunctions() found at the bottom of the file.
    17     17   ** All other code has file scope.
    18     18   **
    19         -** $Id: func.c,v 1.20 2002/06/09 10:14:19 drh Exp $
           19  +** $Id: func.c,v 1.21 2002/06/20 11:36:49 drh Exp $
    20     20   */
    21     21   #include <ctype.h>
    22     22   #include <math.h>
    23     23   #include <stdlib.h>
    24     24   #include <assert.h>
    25     25   #include "sqliteInt.h"
    26     26   
................................................................................
   412    412   ** functions.  This should be the only routine in this file with
   413    413   ** external linkage.
   414    414   */
   415    415   void sqliteRegisterBuiltinFunctions(sqlite *db){
   416    416     static struct {
   417    417        char *zName;
   418    418        int nArg;
          419  +     int dataType;
   419    420        void (*xFunc)(sqlite_func*,int,const char**);
   420    421     } aFuncs[] = {
   421         -    { "min",       -1, minFunc    },
   422         -    { "min",        0, 0          },
   423         -    { "max",       -1, maxFunc    },
   424         -    { "max",        0, 0          },
   425         -    { "length",     1, lengthFunc },
   426         -    { "substr",     3, substrFunc },
   427         -    { "abs",        1, absFunc    },
   428         -    { "round",      1, roundFunc  },
   429         -    { "round",      2, roundFunc  },
   430         -    { "upper",      1, upperFunc  },
   431         -    { "lower",      1, lowerFunc  },
   432         -    { "coalesce",  -1, ifnullFunc },
   433         -    { "coalesce",   0, 0          },
   434         -    { "coalesce",   1, 0          },
   435         -    { "ifnull",     2, ifnullFunc },
   436         -    { "random",    -1, randomFunc },
   437         -    { "like",       2, likeFunc   },
   438         -    { "glob",       2, globFunc   },
   439         -    { "nullif",     2, nullifFunc },
          422  +    { "min",       -1, SQLITE_ARGS,    minFunc    },
          423  +    { "min",        0, 0,              0          },
          424  +    { "max",       -1, SQLITE_ARGS,    maxFunc    },
          425  +    { "max",        0, 0,              0          },
          426  +    { "length",     1, SQLITE_NUMERIC, lengthFunc },
          427  +    { "substr",     3, SQLITE_TEXT,    substrFunc },
          428  +    { "abs",        1, SQLITE_NUMERIC, absFunc    },
          429  +    { "round",      1, SQLITE_NUMERIC, roundFunc  },
          430  +    { "round",      2, SQLITE_NUMERIC, roundFunc  },
          431  +    { "upper",      1, SQLITE_TEXT,    upperFunc  },
          432  +    { "lower",      1, SQLITE_TEXT,    lowerFunc  },
          433  +    { "coalesce",  -1, SQLITE_ARGS,    ifnullFunc },
          434  +    { "coalesce",   0, 0,              0          },
          435  +    { "coalesce",   1, 0,              0          },
          436  +    { "ifnull",     2, SQLITE_ARGS,    ifnullFunc },
          437  +    { "random",    -1, SQLITE_NUMERIC, randomFunc },
          438  +    { "like",       2, SQLITE_NUMERIC, likeFunc   },
          439  +    { "glob",       2, SQLITE_NUMERIC, globFunc   },
          440  +    { "nullif",     2, SQLITE_ARGS,    nullifFunc },
   440    441     };
   441    442     static struct {
   442    443       char *zName;
   443    444       int nArg;
          445  +    int dataType;
   444    446       void (*xStep)(sqlite_func*,int,const char**);
   445    447       void (*xFinalize)(sqlite_func*);
   446    448     } aAggs[] = {
   447         -    { "min",    1, minStep,      minMaxFinalize },
   448         -    { "max",    1, maxStep,      minMaxFinalize },
   449         -    { "sum",    1, sumStep,      sumFinalize    },
   450         -    { "avg",    1, sumStep,      avgFinalize    },
   451         -    { "count",  0, countStep,    countFinalize  },
   452         -    { "count",  1, countStep,    countFinalize  },
          449  +    { "min",    1, 0,              minStep,      minMaxFinalize },
          450  +    { "max",    1, 0,              maxStep,      minMaxFinalize },
          451  +    { "sum",    1, SQLITE_NUMERIC, sumStep,      sumFinalize    },
          452  +    { "avg",    1, SQLITE_NUMERIC, sumStep,      avgFinalize    },
          453  +    { "count",  0, SQLITE_NUMERIC, countStep,    countFinalize  },
          454  +    { "count",  1, SQLITE_NUMERIC, countStep,    countFinalize  },
   453    455   #if 0
   454         -    { "stddev", 1, stdDevStep,   stdDevFinalize },
          456  +    { "stddev", 1, SQLITE_NUMERIC, stdDevStep,   stdDevFinalize },
   455    457   #endif
   456    458     };
   457    459     int i;
   458    460   
   459    461     for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
   460    462       sqlite_create_function(db, aFuncs[i].zName,
   461    463              aFuncs[i].nArg, aFuncs[i].xFunc, 0);
          464  +    if( aFuncs[i].xFunc ){
          465  +      sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
          466  +    }
   462    467     }
   463    468     sqlite_create_function(db, "last_insert_rowid", 0, 
   464    469              last_insert_rowid, db);
          470  +  sqlite_function_type(db, "last_insert_rowid", SQLITE_NUMERIC);
   465    471     for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
   466    472       sqlite_create_aggregate(db, aAggs[i].zName,
   467    473              aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0);
          474  +    sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);
   468    475     }
   469    476   }

Changes to src/main.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** Main file for the SQLite library.  The routines in this file
    13     13   ** implement the programmer interface to the library.  Routines in
    14     14   ** other files are for internal use by SQLite and should not be
    15     15   ** accessed by users of the library.
    16     16   **
    17         -** $Id: main.c,v 1.80 2002/06/16 18:21:44 drh Exp $
           17  +** $Id: main.c,v 1.81 2002/06/20 11:36:49 drh Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "os.h"
    21     21   #include <ctype.h>
    22     22   
    23     23   /*
    24     24   ** This is the callback routine for the code that initializes the
................................................................................
    47     47         int size = atoi(argv[3]);
    48     48         if( size==0 ){ size = MAX_PAGES; }
    49     49         db->cache_size = size;
    50     50         sqliteBtreeSetCacheSize(db->pBe, size);
    51     51         break;
    52     52       }
    53     53       case 'f': {  /* File format */
           54  +      /*
           55  +      ** file_format==1  Version 2.1.0.
           56  +      ** file_format==2  Version 2.2.0.  Integer primary key.
           57  +      ** file_format==3  Version 2.6.0.  Separate text and numeric datatypes.
           58  +      */
    54     59         db->file_format = atoi(argv[3]);
    55     60         break;
    56     61       }
    57     62       case 's': { /* Schema cookie */
    58     63         db->schema_cookie = atoi(argv[3]);
    59     64         db->next_cookie = db->schema_cookie;
    60     65         break;
................................................................................
   823    828     if( p==0 ) return 1;
   824    829     p->xFunc = 0;
   825    830     p->xStep = xStep;
   826    831     p->xFinalize = xFinalize;
   827    832     p->pUserData = pUserData;
   828    833     return 0;
   829    834   }
          835  +
          836  +/*
          837  +** Change the datatype for all functions with a given name.
          838  +*/
          839  +int sqlite_function_type(sqlite *db, const char *zName, int dataType){
          840  +  FuncDef *p = (FuncDef*)sqliteHashFind(&db->aFunc, zName, strlen(zName));
          841  +  while( p ){
          842  +    p->dataType = dataType; 
          843  +    p = p->pNext;
          844  +  }
          845  +}

Changes to src/sqlite.h.in.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This header file defines the interface that the SQLite library
    13     13   ** presents to client programs.
    14     14   **
    15         -** @(#) $Id: sqlite.h.in,v 1.31 2002/05/10 05:44:56 drh Exp $
           15  +** @(#) $Id: sqlite.h.in,v 1.32 2002/06/20 11:36:50 drh Exp $
    16     16   */
    17     17   #ifndef _SQLITE_H_
    18     18   #define _SQLITE_H_
    19     19   #include <stdarg.h>     /* Needed for the definition of va_list */
    20     20   
    21     21   /*
    22     22   ** The version of the SQLite library.
................................................................................
   422    422     const char *zName,        /* Name of the function */
   423    423     int nArg,                 /* Number of arguments */
   424    424     void (*xStep)(sqlite_func*,int,const char**), /* Called for each row */
   425    425     void (*xFinalize)(sqlite_func*),       /* Called once to get final result */
   426    426     void *pUserData           /* Available via the sqlite_user_data() call */
   427    427   );
   428    428   
          429  +/*
          430  +** Use the following routine to define the datatype returned by a
          431  +** user-defined function.  The second argument can be one of the
          432  +** constants SQLITE_NUMERIC, SQLITE_TEXT, or SQLITE_ARGS or it
          433  +** can be an integer greater than or equal to zero.  The datatype
          434  +** will be numeric or text (the only two types supported) if the
          435  +** argument is SQLITE_NUMERIC or SQLITE_TEXT.  If the argument is
          436  +** SQLITE_ARGS, then the datatype is numeric if any argument to the
          437  +** function is numeric and is text otherwise.  If the second argument
          438  +** is an integer, then the datatype of the result is the same as the
          439  +** parameter to the function that corresponds to that integer.
          440  +*/
          441  +int sqlite_function_type(
          442  +  sqlite *db,               /* The database there the function is registered */
          443  +  const char *zName,        /* Name of the function */
          444  +  int datatype              /* The datatype for this function */
          445  +);
          446  +#define SQLITE_NUMERIC     (-1)
          447  +#define SQLITE_TEXT        (-2)
          448  +#define SQLITE_ARGS        (-3)
          449  +
   429    450   /*
   430    451   ** The user function implementations call one of the following four routines
   431    452   ** in order to return their results.  The first parameter to each of these
   432    453   ** routines is a copy of the first argument to xFunc() or xFinialize().
   433    454   ** The second parameter to these routines is the result to be returned.
   434    455   ** A NULL can be passed as the second parameter to sqlite_set_result_string()
   435    456   ** in order to return a NULL result.

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.126 2002/06/19 14:27:05 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.127 2002/06/20 11:36:50 drh Exp $
    15     15   */
    16     16   #include "sqlite.h"
    17     17   #include "hash.h"
    18     18   #include "vdbe.h"
    19     19   #include "parse.h"
    20     20   #include "btree.h"
    21     21   #include <stdio.h>
................................................................................
   172    172   typedef struct AggExpr AggExpr;
   173    173   typedef struct FuncDef FuncDef;
   174    174   typedef struct Trigger Trigger;
   175    175   typedef struct TriggerStep TriggerStep;
   176    176   typedef struct TriggerStack TriggerStack;
   177    177   
   178    178   /*
   179         -** Each database is an instance of the following structure
          179  +** Each database is an instance of the following structure.
          180  +**
          181  +** The sqlite.file_format is initialized by the database file
          182  +** and helps determines how the data in the database file is
          183  +** represented.  This field allows newer versions of the library
          184  +** to read and write older databases.  The various file formats
          185  +** are as follows:
          186  +**
          187  +**     file_format==1    Version 2.1.0.
          188  +**     file_format==2    Version 2.2.0. Add support for INTEGER PRIMARY KEY.
          189  +**     file_format==3    Version 2.6.0. Add support for separate numeric and
          190  +**                       text datatypes.
   180    191   */
   181    192   struct sqlite {
   182    193     Btree *pBe;                   /* The B*Tree backend */
   183    194     Btree *pBeTemp;               /* Backend for session temporary tables */
   184    195     int flags;                    /* Miscellanous flags. See below */
   185    196     int file_format;              /* What file format version is this database? */
   186    197     int schema_cookie;            /* Magic number that changes with the schema */
................................................................................
   240    251   ** points to a linked list of these structures.
   241    252   */
   242    253   struct FuncDef {
   243    254     void (*xFunc)(sqlite_func*,int,const char**);  /* Regular function */
   244    255     void (*xStep)(sqlite_func*,int,const char**);  /* Aggregate function step */
   245    256     void (*xFinalize)(sqlite_func*);           /* Aggregate function finializer */
   246    257     int nArg;                                  /* Number of arguments */
          258  +  int dataType;                              /* Datatype of the result */
   247    259     void *pUserData;                           /* User data parameter */
   248    260     FuncDef *pNext;                            /* Next function with same name */
   249    261   };
   250    262   
   251    263   /*
   252    264   ** information about each column of an SQL table is held in an instance
   253    265   ** of this structure.
................................................................................
   872    884   void sqliteUnlinkAndDeleteIndex(sqlite*,Index*);
   873    885   void sqliteCopy(Parse*, Token*, Token*, Token*, int);
   874    886   void sqliteVacuum(Parse*, Token*);
   875    887   int sqliteGlobCompare(const unsigned char*,const unsigned char*);
   876    888   int sqliteLikeCompare(const unsigned char*,const unsigned char*);
   877    889   char *sqliteTableNameFromToken(Token*);
   878    890   int sqliteExprCheck(Parse*, Expr*, int, int*);
          891  +int sqliteExprType(Expr*);
   879    892   int sqliteExprCompare(Expr*, Expr*);
   880    893   int sqliteFuncId(Token*);
   881    894   int sqliteExprResolveIds(Parse*, int, SrcList*, ExprList*, Expr*);
   882    895   int sqliteExprAnalyzeAggregates(Parse*, Expr*);
   883    896   Vdbe *sqliteGetVdbe(Parse*);
   884    897   int sqliteRandomByte(void);
   885    898   int sqliteRandomInteger(void);

Changes to src/vdbe.c.

    26     26   ** type to the other occurs as necessary.
    27     27   ** 
    28     28   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    29     29   ** function which does the work of interpreting a VDBE program.
    30     30   ** But other routines are also provided to help in building up
    31     31   ** a program instruction by instruction.
    32     32   **
    33         -** $Id: vdbe.c,v 1.156 2002/06/14 22:38:43 drh Exp $
           33  +** $Id: vdbe.c,v 1.157 2002/06/20 11:36:50 drh Exp $
    34     34   */
    35     35   #include "sqliteInt.h"
    36     36   #include <ctype.h>
    37     37   
    38     38   /*
    39     39   ** The following global variable is incremented every time a cursor
    40     40   ** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
  1073   1073     "Halt",              "ColumnCount",       "ColumnName",        "Callback",
  1074   1074     "NullCallback",      "Integer",           "String",            "Pop",
  1075   1075     "Dup",               "Pull",              "Push",              "MustBeInt",
  1076   1076     "Add",               "AddImm",            "Subtract",          "Multiply",
  1077   1077     "Divide",            "Remainder",         "BitAnd",            "BitOr",
  1078   1078     "BitNot",            "ShiftLeft",         "ShiftRight",        "AbsValue",
  1079   1079     "Eq",                "Ne",                "Lt",                "Le",
  1080         -  "Gt",                "Ge",                "IsNull",            "NotNull",
  1081         -  "Negative",          "And",               "Or",                "Not",
  1082         -  "Concat",            "Noop",              "Function",          "Limit",
  1083         -  "LimitCk",
         1080  +  "Gt",                "Ge",                "StrEq",             "StrNe",
         1081  +  "StrLt",             "StrLe",             "StrGt",             "StrGe",
         1082  +  "IsNull",            "NotNull",           "Negative",          "And",
         1083  +  "Or",                "Not",               "Concat",            "Noop",
         1084  +  "Function",          "Limit",             "LimitCk",         
  1084   1085   };
  1085   1086   
  1086   1087   /*
  1087   1088   ** Given the name of an opcode, return its number.  Return 0 if
  1088   1089   ** there is no match.
  1089   1090   **
  1090   1091   ** This routine is used for testing and debugging.
................................................................................
  1987   1988   /* Opcode: Eq P1 P2 *
  1988   1989   **
  1989   1990   ** Pop the top two elements from the stack.  If they are equal, then
  1990   1991   ** jump to instruction P2.  Otherwise, continue to the next instruction.
  1991   1992   **
  1992   1993   ** If either operand is NULL (and thus if the result is unknown) then
  1993   1994   ** take the jump if P1 is true.
         1995  +**
         1996  +** If both values are numeric, they are converted to doubles using atof()
         1997  +** and compared for equality that way.  Otherwise the strcmp() library
         1998  +** routine is used for the comparison.  For a pure text comparison
         1999  +** use OP_StrEq.
  1994   2000   **
  1995   2001   ** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
  1996   2002   ** stack if the jump would have been taken, or a 0 if not.  Push a
  1997   2003   ** NULL if either operand was NULL.
  1998   2004   */
  1999   2005   /* Opcode: Ne P1 P2 *
  2000   2006   **
  2001   2007   ** Pop the top two elements from the stack.  If they are not equal, then
  2002   2008   ** jump to instruction P2.  Otherwise, continue to the next instruction.
  2003   2009   **
  2004   2010   ** If either operand is NULL (and thus if the result is unknown) then
  2005   2011   ** take the jump if P1 is true.
         2012  +**
         2013  +** If both values are numeric, they are converted to doubles using atof()
         2014  +** and compared in that format.  Otherwise the strcmp() library
         2015  +** routine is used for the comparison.  For a pure text comparison
         2016  +** use OP_StrNe.
  2006   2017   **
  2007   2018   ** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
  2008   2019   ** stack if the jump would have been taken, or a 0 if not.  Push a
  2009   2020   ** NULL if either operand was NULL.
  2010   2021   */
  2011   2022   /* Opcode: Lt P1 P2 *
  2012   2023   **
................................................................................
  2013   2024   ** Pop the top two elements from the stack.  If second element (the
  2014   2025   ** next on stack) is less than the first (the top of stack), then
  2015   2026   ** jump to instruction P2.  Otherwise, continue to the next instruction.
  2016   2027   ** In other words, jump if NOS<TOS.
  2017   2028   **
  2018   2029   ** If either operand is NULL (and thus if the result is unknown) then
  2019   2030   ** take the jump if P1 is true.
         2031  +**
         2032  +** If both values are numeric, they are converted to doubles using atof()
         2033  +** and compared in that format.  Numeric values are always less than
         2034  +** non-numeric values.  If both operands are non-numeric, the strcmp() library
         2035  +** routine is used for the comparison.  For a pure text comparison
         2036  +** use OP_StrLt.
  2020   2037   **
  2021   2038   ** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
  2022   2039   ** stack if the jump would have been taken, or a 0 if not.  Push a
  2023   2040   ** NULL if either operand was NULL.
  2024   2041   */
  2025   2042   /* Opcode: Le P1 P2 *
  2026   2043   **
  2027   2044   ** Pop the top two elements from the stack.  If second element (the
  2028   2045   ** next on stack) is less than or equal to the first (the top of stack),
  2029   2046   ** then jump to instruction P2. In other words, jump if NOS<=TOS.
  2030   2047   **
  2031   2048   ** If either operand is NULL (and thus if the result is unknown) then
  2032   2049   ** take the jump if P1 is true.
         2050  +**
         2051  +** If both values are numeric, they are converted to doubles using atof()
         2052  +** and compared in that format.  Numeric values are always less than
         2053  +** non-numeric values.  If both operands are non-numeric, the strcmp() library
         2054  +** routine is used for the comparison.  For a pure text comparison
         2055  +** use OP_StrLe.
  2033   2056   **
  2034   2057   ** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
  2035   2058   ** stack if the jump would have been taken, or a 0 if not.  Push a
  2036   2059   ** NULL if either operand was NULL.
  2037   2060   */
  2038   2061   /* Opcode: Gt P1 P2 *
  2039   2062   **
  2040   2063   ** Pop the top two elements from the stack.  If second element (the
  2041   2064   ** next on stack) is greater than the first (the top of stack),
  2042   2065   ** then jump to instruction P2. In other words, jump if NOS>TOS.
  2043   2066   **
  2044   2067   ** If either operand is NULL (and thus if the result is unknown) then
  2045   2068   ** take the jump if P1 is true.
         2069  +**
         2070  +** If both values are numeric, they are converted to doubles using atof()
         2071  +** and compared in that format.  Numeric values are always less than
         2072  +** non-numeric values.  If both operands are non-numeric, the strcmp() library
         2073  +** routine is used for the comparison.  For a pure text comparison
         2074  +** use OP_StrGt.
  2046   2075   **
  2047   2076   ** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
  2048   2077   ** stack if the jump would have been taken, or a 0 if not.  Push a
  2049   2078   ** NULL if either operand was NULL.
  2050   2079   */
  2051   2080   /* Opcode: Ge P1 P2 *
  2052   2081   **
  2053   2082   ** Pop the top two elements from the stack.  If second element (the next
  2054   2083   ** on stack) is greater than or equal to the first (the top of stack),
  2055   2084   ** then jump to instruction P2. In other words, jump if NOS>=TOS.
  2056   2085   **
  2057   2086   ** If either operand is NULL (and thus if the result is unknown) then
  2058   2087   ** take the jump if P1 is true.
         2088  +**
         2089  +** If both values are numeric, they are converted to doubles using atof()
         2090  +** and compared in that format.  Numeric values are always less than
         2091  +** non-numeric values.  If both operands are non-numeric, the strcmp() library
         2092  +** routine is used for the comparison.  For a pure text comparison
         2093  +** use OP_StrGe.
  2059   2094   **
  2060   2095   ** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
  2061   2096   ** stack if the jump would have been taken, or a 0 if not.  Push a
  2062   2097   ** NULL if either operand was NULL.
  2063   2098   */
  2064   2099   case OP_Eq:
  2065   2100   case OP_Ne:
................................................................................
  2092   2127     }else if( (fn & STK_Int)!=0 && (ft & STK_Str)!=0 && isInteger(zStack[tos]) ){
  2093   2128       Integerify(p, tos);
  2094   2129       c = aStack[nos].i - aStack[tos].i;
  2095   2130     }else{
  2096   2131       if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem;
  2097   2132       c = sqliteCompare(zStack[nos], zStack[tos]);
  2098   2133     }
         2134  +  switch( pOp->opcode ){
         2135  +    case OP_Eq:    c = c==0;     break;
         2136  +    case OP_Ne:    c = c!=0;     break;
         2137  +    case OP_Lt:    c = c<0;      break;
         2138  +    case OP_Le:    c = c<=0;     break;
         2139  +    case OP_Gt:    c = c>0;      break;
         2140  +    default:       c = c>=0;     break;
         2141  +  }
         2142  +  POPSTACK;
         2143  +  POPSTACK;
         2144  +  if( pOp->p2 ){
         2145  +    if( c ) pc = pOp->p2-1;
         2146  +  }else{
         2147  +    p->tos++;
         2148  +    aStack[nos].flags = STK_Int;
         2149  +    aStack[nos].i = c;
         2150  +  }
         2151  +  break;
         2152  +}
         2153  +
         2154  +/* Opcode: StrEq P1 P2 *
         2155  +**
         2156  +** Pop the top two elements from the stack.  If they are equal, then
         2157  +** jump to instruction P2.  Otherwise, continue to the next instruction.
         2158  +**
         2159  +** If either operand is NULL (and thus if the result is unknown) then
         2160  +** take the jump if P1 is true.
         2161  +**
         2162  +** The strcmp() library routine is used for the comparison.  For a
         2163  +** numeric comparison, use OP_Eq.
         2164  +**
         2165  +** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
         2166  +** stack if the jump would have been taken, or a 0 if not.  Push a
         2167  +** NULL if either operand was NULL.
         2168  +*/
         2169  +/* Opcode: StrNe P1 P2 *
         2170  +**
         2171  +** Pop the top two elements from the stack.  If they are not equal, then
         2172  +** jump to instruction P2.  Otherwise, continue to the next instruction.
         2173  +**
         2174  +** If either operand is NULL (and thus if the result is unknown) then
         2175  +** take the jump if P1 is true.
         2176  +**
         2177  +** The strcmp() library routine is used for the comparison.  For a
         2178  +** numeric comparison, use OP_Ne.
         2179  +**
         2180  +** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
         2181  +** stack if the jump would have been taken, or a 0 if not.  Push a
         2182  +** NULL if either operand was NULL.
         2183  +*/
         2184  +/* Opcode: StrLt P1 P2 *
         2185  +**
         2186  +** Pop the top two elements from the stack.  If second element (the
         2187  +** next on stack) is less than the first (the top of stack), then
         2188  +** jump to instruction P2.  Otherwise, continue to the next instruction.
         2189  +** In other words, jump if NOS<TOS.
         2190  +**
         2191  +** If either operand is NULL (and thus if the result is unknown) then
         2192  +** take the jump if P1 is true.
         2193  +**
         2194  +** The strcmp() library routine is used for the comparison.  For a
         2195  +** numeric comparison, use OP_Lt.
         2196  +**
         2197  +** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
         2198  +** stack if the jump would have been taken, or a 0 if not.  Push a
         2199  +** NULL if either operand was NULL.
         2200  +*/
         2201  +/* Opcode: StrLe P1 P2 *
         2202  +**
         2203  +** Pop the top two elements from the stack.  If second element (the
         2204  +** next on stack) is less than or equal to the first (the top of stack),
         2205  +** then jump to instruction P2. In other words, jump if NOS<=TOS.
         2206  +**
         2207  +** If either operand is NULL (and thus if the result is unknown) then
         2208  +** take the jump if P1 is true.
         2209  +**
         2210  +** The strcmp() library routine is used for the comparison.  For a
         2211  +** numeric comparison, use OP_Le.
         2212  +**
         2213  +** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
         2214  +** stack if the jump would have been taken, or a 0 if not.  Push a
         2215  +** NULL if either operand was NULL.
         2216  +*/
         2217  +/* Opcode: StrGt P1 P2 *
         2218  +**
         2219  +** Pop the top two elements from the stack.  If second element (the
         2220  +** next on stack) is greater than the first (the top of stack),
         2221  +** then jump to instruction P2. In other words, jump if NOS>TOS.
         2222  +**
         2223  +** If either operand is NULL (and thus if the result is unknown) then
         2224  +** take the jump if P1 is true.
         2225  +**
         2226  +** The strcmp() library routine is used for the comparison.  For a
         2227  +** numeric comparison, use OP_Gt.
         2228  +**
         2229  +** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
         2230  +** stack if the jump would have been taken, or a 0 if not.  Push a
         2231  +** NULL if either operand was NULL.
         2232  +*/
         2233  +/* Opcode: StrGe P1 P2 *
         2234  +**
         2235  +** Pop the top two elements from the stack.  If second element (the next
         2236  +** on stack) is greater than or equal to the first (the top of stack),
         2237  +** then jump to instruction P2. In other words, jump if NOS>=TOS.
         2238  +**
         2239  +** If either operand is NULL (and thus if the result is unknown) then
         2240  +** take the jump if P1 is true.
         2241  +**
         2242  +** The strcmp() library routine is used for the comparison.  For a
         2243  +** numeric comparison, use OP_Ge.
         2244  +**
         2245  +** If P2 is zero, do not jump.  Instead, push an integer 1 onto the
         2246  +** stack if the jump would have been taken, or a 0 if not.  Push a
         2247  +** NULL if either operand was NULL.
         2248  +*/
         2249  +case OP_StrEq:
         2250  +case OP_StrNe:
         2251  +case OP_StrLt:
         2252  +case OP_StrLe:
         2253  +case OP_StrGt:
         2254  +case OP_StrGe: {
         2255  +  int tos = p->tos;
         2256  +  int nos = tos - 1;
         2257  +  int c;
         2258  +  VERIFY( if( nos<0 ) goto not_enough_stack; )
         2259  +  if( (aStack[nos].flags | aStack[tos].flags) & STK_Null ){
         2260  +    POPSTACK;
         2261  +    POPSTACK;
         2262  +    if( pOp->p2 ){
         2263  +      if( pOp->p1 ) pc = pOp->p2-1;
         2264  +    }else{
         2265  +      p->tos++;
         2266  +      aStack[nos].flags = STK_Null;
         2267  +    }
         2268  +    break;
         2269  +  }else{
         2270  +    if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem;
         2271  +    c = strcmp(zStack[nos], zStack[tos]);
         2272  +  }
  2099   2273     switch( pOp->opcode ){
  2100   2274       case OP_Eq:    c = c==0;     break;
  2101   2275       case OP_Ne:    c = c!=0;     break;
  2102   2276       case OP_Lt:    c = c<0;      break;
  2103   2277       case OP_Le:    c = c<=0;     break;
  2104   2278       case OP_Gt:    c = c>0;      break;
  2105   2279       default:       c = c>=0;     break;
................................................................................
  2431   2605     p->tos++;
  2432   2606     aStack[p->tos].n = nByte;
  2433   2607     aStack[p->tos].flags = STK_Str | STK_Dyn;
  2434   2608     zStack[p->tos] = zNewRecord;
  2435   2609     break;
  2436   2610   }
  2437   2611   
  2438         -/* Opcode: MakeKey P1 P2 *
         2612  +/* Opcode: MakeKey P1 P2 P3
  2439   2613   **
  2440   2614   ** Convert the top P1 entries of the stack into a single entry suitable
  2441   2615   ** for use as the key in an index.  The top P1 records are
  2442   2616   ** converted to strings and merged.  The null-terminators 
  2443   2617   ** are retained and used as separators.
  2444   2618   ** The lowest entry in the stack is the first field and the top of the
  2445   2619   ** stack becomes the last.
  2446   2620   **
  2447   2621   ** If P2 is not zero, then the original entries remain on the stack
  2448   2622   ** and the new key is pushed on top.  If P2 is zero, the original
  2449   2623   ** data is popped off the stack first then the new key is pushed
  2450   2624   ** back in its place.
         2625  +**
         2626  +** P3 is a string that is P1 characters long.  Each character is either
         2627  +** an 'n' or a 't' to indicates if the argument should be numeric or
         2628  +** text.  The first character corresponds to the lowest element on the
         2629  +** stack.
  2451   2630   **
  2452   2631   ** See also: MakeIdxKey, SortMakeKey
  2453   2632   */
  2454         -/* Opcode: MakeIdxKey P1 P2 *
         2633  +/* Opcode: MakeIdxKey P1 P2 P3
  2455   2634   **
  2456   2635   ** Convert the top P1 entries of the stack into a single entry suitable
  2457   2636   ** for use as the key in an index.  In addition, take one additional integer
  2458   2637   ** off of the stack, treat that integer as a four-byte record number, and
  2459   2638   ** append the four bytes to the key.  Thus a total of P1+1 entries are
  2460   2639   ** popped from the stack for this instruction and a single entry is pushed
  2461   2640   ** back.  The first P1 entries that are popped are strings and the last
................................................................................
  2468   2647   ** last.
  2469   2648   **
  2470   2649   ** If P2 is not zero and one or more of the P1 entries that go into the
  2471   2650   ** generated key is NULL, then jump to P2 after the new key has been
  2472   2651   ** pushed on the stack.  In other words, jump to P2 if the key is
  2473   2652   ** guaranteed to be unique.  This jump can be used to skip a subsequent
  2474   2653   ** uniqueness test.
         2654  +**
         2655  +** P3 is a string that is P1 characters long.  Each character is either
         2656  +** an 'n' or a 't' to indicates if the argument should be numeric or
         2657  +** text.  The first character corresponds to the lowest element on the
         2658  +** stack.
  2475   2659   **
  2476   2660   ** See also:  MakeKey, SortMakeKey
  2477   2661   */
  2478   2662   case OP_MakeIdxKey:
  2479   2663   case OP_MakeKey: {
  2480   2664     char *zNewKey;
  2481   2665     int nByte;
................................................................................
  2484   2668     int i, j;
  2485   2669     int containsNull = 0;
  2486   2670   
  2487   2671     addRowid = pOp->opcode==OP_MakeIdxKey;
  2488   2672     nField = pOp->p1;
  2489   2673     VERIFY( if( p->tos+1+addRowid<nField ) goto not_enough_stack; )
  2490   2674     nByte = 0;
  2491         -  for(i=p->tos-nField+1; i<=p->tos; i++){
         2675  +  for(j=0, i=p->tos-nField+1; i<=p->tos; i++, j++){
  2492   2676       int flags = aStack[i].flags;
  2493   2677       int len;
  2494   2678       char *z;
  2495   2679       if( flags & STK_Null ){
  2496   2680         nByte += 2;
  2497   2681         containsNull = 1;
         2682  +    }else if( pOp->p3 && pOp->p3[j]=='t' ){
         2683  +      Stringify(p, i);
  2498   2684       }else if( flags & STK_Real ){
  2499   2685         z = aStack[i].z;
  2500   2686         sqliteRealToSortable(aStack[i].r, &z[1]);
  2501   2687         z[0] = 0;
  2502   2688         Release(p, i);
  2503   2689         len = strlen(&z[1]);
  2504   2690         zStack[i] = 0;

Changes to src/vdbe.h.

    11     11   *************************************************************************
    12     12   ** Header file for the Virtual DataBase Engine (VDBE)
    13     13   **
    14     14   ** This header defines the interface to the virtual database engine
    15     15   ** or VDBE.  The VDBE implements an abstract machine that runs a
    16     16   ** simple program to access and modify the underlying database.
    17     17   **
    18         -** $Id: vdbe.h,v 1.55 2002/06/14 22:38:43 drh Exp $
           18  +** $Id: vdbe.h,v 1.56 2002/06/20 11:36:50 drh Exp $
    19     19   */
    20     20   #ifndef _SQLITE_VDBE_H_
    21     21   #define _SQLITE_VDBE_H_
    22     22   #include <stdio.h>
    23     23   
    24     24   /*
    25     25   ** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
   183    183   #define OP_Remainder          98
   184    184   #define OP_BitAnd             99
   185    185   #define OP_BitOr             100
   186    186   #define OP_BitNot            101
   187    187   #define OP_ShiftLeft         102
   188    188   #define OP_ShiftRight        103
   189    189   #define OP_AbsValue          104
          190  +
          191  +/* Note: The code generator assumes that OP_XX+6==OP_StrXX */
   190    192   #define OP_Eq                105
   191    193   #define OP_Ne                106
   192    194   #define OP_Lt                107
   193    195   #define OP_Le                108
   194    196   #define OP_Gt                109
   195    197   #define OP_Ge                110
   196         -#define OP_IsNull            111
   197         -#define OP_NotNull           112
   198         -#define OP_Negative          113
   199         -#define OP_And               114
   200         -#define OP_Or                115
   201         -#define OP_Not               116
   202         -#define OP_Concat            117
   203         -#define OP_Noop              118
   204         -#define OP_Function          119
          198  +#define OP_StrEq             111
          199  +#define OP_StrNe             112
          200  +#define OP_StrLt             113
          201  +#define OP_StrLe             114
          202  +#define OP_StrGt             115
          203  +#define OP_StrGe             116
          204  +/* Note: the code generator assumes that OP_XX+6==OP_StrXX */
          205  +
          206  +#define OP_IsNull            117
          207  +#define OP_NotNull           118
          208  +#define OP_Negative          119
          209  +#define OP_And               120
          210  +#define OP_Or                121
          211  +#define OP_Not               122
          212  +#define OP_Concat            123
          213  +#define OP_Noop              124
          214  +#define OP_Function          125
   205    215   
   206         -#define OP_Limit             120
   207         -#define OP_LimitCk           121
          216  +#define OP_Limit             126
          217  +#define OP_LimitCk           127
   208    218   
   209    219   
   210         -#define OP_MAX               121
          220  +#define OP_MAX               127
   211    221   
   212    222   /*
   213    223   ** Prototypes for the VDBE interface.  See comments on the implementation
   214    224   ** for a description of what each of these routines does.
   215    225   */
   216    226   Vdbe *sqliteVdbeCreate(sqlite*);
   217    227   void sqliteVdbeCreateCallback(Vdbe*, int*);