Create separate affinities for INTEGER and REAL. (CVS 2766)

FossilOrigin-Name: ce06c123d0c5663dbaf263c2e0aaf5d9cdeb2ccd

13 files changed, 224 insertions, 153 deletions

diff --git a/src/analyze.c b/src/analyze.c
index 59749704d..41fecb13b 100644
--- a/src/analyze.c
+++ b/src/analyze.c
@@ -11,7 +11,7 @@
 *************************************************************************
 ** This file contains code associated with the ANALYZE command.
 **
-** @(#) $Id: analyze.c,v 1.10 2005/11/01 15:48:24 drh Exp $
+** @(#) $Id: analyze.c,v 1.11 2005/11/14 22:29:05 drh Exp $
 */
 #ifndef SQLITE_OMIT_ANALYZE
 #include "sqliteInt.h"
@@ -195,7 +195,7 @@ static void analyzeOneTable(
         sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
       }
     }
-    sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "ttt", 0);
+    sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "aaa", 0);
     sqlite3VdbeAddOp(v, OP_Insert, iStatCur, 0);
     sqlite3VdbeJumpHere(v, addr);
   }
diff --git a/src/build.c b/src/build.c
index b6ecf0f65..fe86a86c8 100644
--- a/src/build.c
+++ b/src/build.c
@@ -22,7 +22,7 @@
 **     COMMIT
 **     ROLLBACK
 **
-** $Id: build.c,v 1.354 2005/11/03 02:03:13 drh Exp $
+** $Id: build.c,v 1.355 2005/11/14 22:29:05 drh Exp $
 */
 #include "sqliteInt.h"
 #include <ctype.h>
@@ -885,7 +885,7 @@ void sqlite3AddNotNull(Parse *pParse, int onError){
 ** found, the corresponding affinity is returned. If zType contains
 ** more than one of the substrings, entries toward the top of 
 ** the table take priority. For example, if zType is 'BLOBINT', 
-** SQLITE_AFF_NUMERIC is returned.
+** SQLITE_AFF_INTEGER is returned.
 **
 ** Substring     | Affinity
 ** --------------------------------
@@ -894,15 +894,14 @@ void sqlite3AddNotNull(Parse *pParse, int onError){
 ** 'CLOB'        | SQLITE_AFF_TEXT
 ** 'TEXT'        | SQLITE_AFF_TEXT
 ** 'BLOB'        | SQLITE_AFF_NONE
+** 'REAL'        | SQLITE_AFF_REAL
+** 'FLOA'        | SQLITE_AFF_REAL
+** 'DOUB'        | SQLITE_AFF_REAL
 **
 ** If none of the substrings in the above table are found,
 ** SQLITE_AFF_NUMERIC is returned.
-**
-** The SQLITE_AFF_INTEGER type is only returned if useIntType is true.
-** If useIntType is false, then SQLITE_AFF_INTEGER is reported back
-** as SQLITE_AFF_NUMERIC
 */
-char sqlite3AffinityType(const Token *pType, int useIntType){
+char sqlite3AffinityType(const Token *pType){
   u32 h = 0;
   char aff = SQLITE_AFF_NUMERIC;
   const unsigned char *zIn = pType->z;
@@ -918,10 +917,21 @@ char sqlite3AffinityType(const Token *pType, int useIntType){
     }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){       /* TEXT */
       aff = SQLITE_AFF_TEXT;
     }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')          /* BLOB */
-        && aff==SQLITE_AFF_NUMERIC ){
+        && (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
       aff = SQLITE_AFF_NONE;
+#ifndef SQLITE_OMIT_FLOATING_POINT
+    }else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')          /* REAL */
+        && aff==SQLITE_AFF_NUMERIC ){
+      aff = SQLITE_AFF_REAL;
+    }else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')          /* FLOA */
+        && aff==SQLITE_AFF_NUMERIC ){
+      aff = SQLITE_AFF_REAL;
+    }else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b')          /* DOUB */
+        && aff==SQLITE_AFF_NUMERIC ){
+      aff = SQLITE_AFF_REAL;
+#endif
     }else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){    /* INT */
-      aff = useIntType ? SQLITE_AFF_INTEGER : SQLITE_AFF_NUMERIC; 
+      aff = SQLITE_AFF_INTEGER;
       break;
     }
   }
@@ -949,7 +959,7 @@ void sqlite3AddColumnType(Parse *pParse, Token *pType){
   pCol = &p->aCol[i];
   sqliteFree(pCol->zType);
   pCol->zType = sqlite3NameFromToken(pType);
-  pCol->affinity = sqlite3AffinityType(pType, 0);
+  pCol->affinity = sqlite3AffinityType(pType);
 }
 
 /*
diff --git a/src/expr.c b/src/expr.c
index ba25a5ad4..3fce58ca6 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -12,7 +12,7 @@
 ** This file contains routines used for analyzing expressions and
 ** for generating VDBE code that evaluates expressions in SQLite.
 **
-** $Id: expr.c,v 1.236 2005/11/05 15:07:56 drh Exp $
+** $Id: expr.c,v 1.237 2005/11/14 22:29:05 drh Exp $
 */
 #include "sqliteInt.h"
 #include <ctype.h>
@@ -43,7 +43,7 @@ char sqlite3ExprAffinity(Expr *pExpr){
   }
 #ifndef SQLITE_OMIT_CAST
   if( op==TK_CAST ){
-    return sqlite3AffinityType(&pExpr->token, 0);
+    return sqlite3AffinityType(&pExpr->token);
   }
 #endif
   return pExpr->affinity;
@@ -78,7 +78,7 @@ char sqlite3CompareAffinity(Expr *pExpr, char aff2){
     /* Both sides of the comparison are columns. If one has numeric
     ** affinity, use that. Otherwise use no affinity.
     */
-    if( aff1==SQLITE_AFF_NUMERIC || aff2==SQLITE_AFF_NUMERIC ){
+    if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){
       return SQLITE_AFF_NUMERIC;
     }else{
       return SQLITE_AFF_NONE;
@@ -126,7 +126,14 @@ static char comparisonAffinity(Expr *pExpr){
 */
 int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
   char aff = comparisonAffinity(pExpr);
-  return (aff==SQLITE_AFF_NONE) || (aff==idx_affinity);
+  switch( aff ){
+    case SQLITE_AFF_NONE:
+      return 1;
+    case SQLITE_AFF_TEXT:
+      return idx_affinity==SQLITE_AFF_TEXT;
+    default:
+      return sqlite3IsNumericAffinity(idx_affinity);
+  }
 }
 
 /*
@@ -936,7 +943,7 @@ static int lookupName(
     if( cnt==0 && cntTab==1 && sqlite3IsRowid(zCol) ){
       cnt = 1;
       pExpr->iColumn = -1;
-      pExpr->affinity = SQLITE_AFF_NUMERIC;
+      pExpr->affinity = SQLITE_AFF_INTEGER;
     }
 
     /*
@@ -1482,8 +1489,15 @@ void sqlite3ExprCode(Parse *pParse, Expr *pExpr){
         assert( pParse->ckOffset>0 );
         sqlite3VdbeAddOp(v, OP_Dup, pParse->ckOffset-pExpr->iColumn-1, 1);
       }else if( pExpr->iColumn>=0 ){
-        sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
-        sqlite3ColumnDefault(v, pExpr->pTab, pExpr->iColumn);
+        Table *pTab = pExpr->pTab;
+        int iCol = pExpr->iColumn;
+        sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, iCol);
+        sqlite3ColumnDefault(v, pTab, iCol);
+#ifndef SQLITE_OMIT_FLOATING_POINT
+        if( pTab && pTab->aCol[iCol].affinity==SQLITE_AFF_REAL ){
+          sqlite3VdbeAddOp(v, OP_RealAffinity, 0, 0);
+        }
+#endif
       }else{
         sqlite3VdbeAddOp(v, OP_Rowid, pExpr->iTable, 0);
       }
@@ -1536,13 +1550,13 @@ void sqlite3ExprCode(Parse *pParse, Expr *pExpr){
       /* Expressions of the form:   CAST(pLeft AS token) */
       int aff, op;
       sqlite3ExprCode(pParse, pExpr->pLeft);
-      aff = sqlite3AffinityType(&pExpr->token, 1);
-      switch( aff ){
-        case SQLITE_AFF_INTEGER:   op = OP_ToInt;      break;
-        case SQLITE_AFF_NUMERIC:   op = OP_ToNumeric;  break;
-        case SQLITE_AFF_TEXT:      op = OP_ToText;     break;
-        case SQLITE_AFF_NONE:      op = OP_ToBlob;     break;
-      }
+      aff = sqlite3AffinityType(&pExpr->token);
+      op = aff - SQLITE_AFF_TEXT + OP_ToText;
+      assert( op==OP_ToText    || aff!=SQLITE_AFF_TEXT    );
+      assert( op==OP_ToBlob    || aff!=SQLITE_AFF_NONE    );
+      assert( op==OP_ToNumeric || aff!=SQLITE_AFF_NUMERIC );
+      assert( op==OP_ToInt     || aff!=SQLITE_AFF_INTEGER );
+      assert( op==OP_ToReal    || aff!=SQLITE_AFF_REAL    );
       sqlite3VdbeAddOp(v, op, 0, 0);
       stackChng = 0;
       break;
diff --git a/src/insert.c b/src/insert.c
index ce1dc511a..682f3d8d9 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -12,7 +12,7 @@
 ** This file contains C code routines that are called by the parser
 ** to handle INSERT statements in SQLite.
 **
-** $Id: insert.c,v 1.147 2005/11/03 02:15:03 drh Exp $
+** $Id: insert.c,v 1.148 2005/11/14 22:29:05 drh Exp $
 */
 #include "sqliteInt.h"
 
@@ -305,7 +305,7 @@ void sqlite3Insert(
     sqlite3VdbeAddOp(v, OP_Rewind, iCur, base+13);
     sqlite3VdbeAddOp(v, OP_Column, iCur, 0);
     sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
-    sqlite3VdbeAddOp(v, OP_Ne, 28417, base+12);
+    sqlite3VdbeAddOp(v, OP_Ne, 0x100, base+12);
     sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
     sqlite3VdbeAddOp(v, OP_MemStore, counterRowid, 1);
     sqlite3VdbeAddOp(v, OP_Column, iCur, 1);
diff --git a/src/parse.y b/src/parse.y
index d565c78aa..fc57ce1fe 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -14,7 +14,7 @@
 ** the parser.  Lemon will also generate a header file containing
 ** numeric codes for all of the tokens.
 **
-** @(#) $Id: parse.y,v 1.183 2005/11/06 04:06:59 drh Exp $
+** @(#) $Id: parse.y,v 1.184 2005/11/14 22:29:05 drh Exp $
 */
 
 // All token codes are small integers with #defines that begin with "TK_"
@@ -95,6 +95,9 @@ struct AttachKey { int type;  Token key; };
 %nonassoc END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
           COLUMN AGG_FUNCTION AGG_COLUMN CONST_FUNC.
 
+// Extra tokens used by the code generator by never seen by the parser.
+%nonassoc TO_TEXT TO_BLOB TO_NUMERIC TO_INT TO_REAL.
+
 // Input is a single SQL command
 input ::= cmdlist.
 cmdlist ::= cmdlist ecmd.
diff --git a/src/select.c b/src/select.c
index 674157a94..945ce5789 100644
--- a/src/select.c
+++ b/src/select.c
@@ -12,7 +12,7 @@
 ** This file contains C code routines that are called by the parser
 ** to handle SELECT statements in SQLite.
 **
-** $Id: select.c,v 1.278 2005/11/03 00:41:17 drh Exp $
+** $Id: select.c,v 1.279 2005/11/14 22:29:05 drh Exp $
 */
 #include "sqliteInt.h"
 
@@ -687,7 +687,7 @@ static void generateSortTail(
       sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
       sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
       sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
-      sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, "n", P3_STATIC);
+      sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, "c", P3_STATIC);
       sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
       break;
     }
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 84ae5735c..45070cbcf 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
 *************************************************************************
 ** Internal interface definitions for SQLite.
 **
-** @(#) $Id: sqliteInt.h,v 1.427 2005/11/03 02:15:03 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.428 2005/11/14 22:29:05 drh Exp $
 */
 #ifndef _SQLITEINT_H_
 #define _SQLITEINT_H_
@@ -585,12 +585,25 @@ struct CollSeq {
 
 /*
 ** Column affinity types.
+**
+** These used to have mnemonic name like 'i' for SQLITE_AFF_INTEGER and
+** 't' for SQLITE_AFF_TEXT.  But we can save a little space and improve
+** the speed a little by number the values consecutively.  
+**
+** But rather than start with 0 or 1, we begin with 'a'.  That way,
+** when multiple affinity types are concatenated into a string and
+** used as the P3 operand, they will be more readable.
+**
+** Note also that the numeric types are grouped together so that testing
+** for a numeric type is a single comparison.
 */
-#define SQLITE_AFF_NUMERIC  'n'
-#define SQLITE_AFF_INTEGER  'i'  /* Used for CAST operators only */
-#define SQLITE_AFF_TEXT     't'
-#define SQLITE_AFF_NONE     'o'
+#define SQLITE_AFF_TEXT     'a'
+#define SQLITE_AFF_NONE     'b'
+#define SQLITE_AFF_NUMERIC  'c'
+#define SQLITE_AFF_INTEGER  'd'
+#define SQLITE_AFF_REAL     'e'
 
+#define sqlite3IsNumericAffinity(X)  ((X)>=SQLITE_AFF_NUMERIC)
 
 /*
 ** Each SQL table is represented in memory by an instance of the
@@ -1653,7 +1666,7 @@ void sqlite3AlterFinishAddColumn(Parse *, Token *);
 void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
 const char *sqlite3TestErrorName(int);
 CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int);
-char sqlite3AffinityType(const Token*, int);
+char sqlite3AffinityType(const Token*);
 void sqlite3Analyze(Parse*, Token*, Token*);
 int sqlite3InvokeBusyHandler(BusyHandler*);
 int sqlite3FindDb(sqlite3*, Token*);
diff --git a/src/trigger.c b/src/trigger.c
index f39d2bd83..3da62decb 100644
--- a/src/trigger.c
+++ b/src/trigger.c
@@ -223,7 +223,7 @@ void sqlite3FinishTrigger(
       { OP_String8,    0, 0,  "CREATE TRIGGER "},
       { OP_String8,    0, 0,  0          },  /* 6: SQL */
       { OP_Concat,     0, 0,  0          }, 
-      { OP_MakeRecord, 5, 0,  "tttit"    },
+      { OP_MakeRecord, 5, 0,  "aaada"    },
       { OP_Insert,     0, 0,  0          },
     };
     int addr;
diff --git a/src/update.c b/src/update.c
index 007d52572..c5d4f036a 100644
--- a/src/update.c
+++ b/src/update.c
@@ -12,13 +12,13 @@
 ** This file contains C code routines that are called by the parser
 ** to handle UPDATE statements.
 **
-** $Id: update.c,v 1.112 2005/09/20 17:42:23 drh Exp $
+** $Id: update.c,v 1.113 2005/11/14 22:29:05 drh Exp $
 */
 #include "sqliteInt.h"
 
 /*
-** The most recently coded instruction was an OP_Column to retrieve column
-** 'i' of table pTab. This routine sets the P3 parameter of the 
+** The most recently coded instruction was an OP_Column to retrieve the
+** i-th column of table pTab. This routine sets the P3 parameter of the 
 ** OP_Column to the default value, if any.
 **
 ** The default value of a column is specified by a DEFAULT clause in the 
diff --git a/src/vdbe.c b/src/vdbe.c
index 30f57be18..bd9cf93be 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -43,7 +43,7 @@
 ** in this file for details.  If in doubt, do not deviate from existing
 ** commenting and indentation practices when changing or adding code.
 **
-** $Id: vdbe.c,v 1.495 2005/11/01 15:48:24 drh Exp $
+** $Id: vdbe.c,v 1.496 2005/11/14 22:29:05 drh Exp $
 */
 #include "sqliteInt.h"
 #include "os.h"
@@ -118,23 +118,6 @@ int sqlite3_sort_count = 0;
        && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;}
 
 /*
-** Convert the given stack entity into a integer if it isn't one
-** already.
-**
-** Any prior string or real representation is invalidated.  
-** NULLs are converted into 0.
-*/
-#define Integerify(P) sqlite3VdbeMemIntegerify(P)
-
-/*
-** Convert P so that it has type MEM_Real.
-**
-** Any prior string or integer representation is invalidated.
-** NULLs are converted into 0.0.
-*/
-#define Realify(P) sqlite3VdbeMemRealify(P)
-
-/*
 ** Argument pMem points at a memory cell that will be passed to a
 ** user-defined function or returned to the user as the result of a query.
 ** The second argument, 'db_enc' is the text encoding used by the vdbe for
@@ -188,17 +171,25 @@ static Cursor *allocateCursor(Vdbe *p, int iCur){
 }
 
 /*
-** Apply any conversion required by the supplied column affinity to
-** memory cell pRec. affinity may be one of:
+** Processing is determine by the affinity parameter:
+**
+** SQLITE_AFF_INTEGER:
+** SQLITE_AFF_REAL:
+** SQLITE_AFF_NUMERIC:
+**    Try to convert pRec to an integer representation or a 
+**    floating-point representation if an integer representation
+**    is not possible.  Note that the integer representation is
+**    always preferred, even if the affinity is REAL, because
+**    an integer representation is more space efficient on disk.
+**
+** SQLITE_AFF_TEXT:
+**    Convert pRec to a text representation.
 **
-** SQLITE_AFF_NUMERIC
-** SQLITE_AFF_TEXT
-** SQLITE_AFF_NONE
+** SQLITE_AFF_NONE:
+**    No-op.  pRec is unchanged.
 */
 static void applyAffinity(Mem *pRec, char affinity, u8 enc){
-  if( affinity==SQLITE_AFF_NONE ){
-    /* do nothing */
-  }else if( affinity==SQLITE_AFF_TEXT ){
+  if( affinity==SQLITE_AFF_TEXT ){
     /* Only attempt the conversion to TEXT if there is an integer or real
     ** representation (blob and NULL do not get converted) but no string
     ** representation.
@@ -207,7 +198,9 @@ static void applyAffinity(Mem *pRec, char affinity, u8 enc){
       sqlite3VdbeMemStringify(pRec, enc);
     }
     pRec->flags &= ~(MEM_Real|MEM_Int);
-  }else{
+  }else if( affinity!=SQLITE_AFF_NONE ){
+    assert( affinity==SQLITE_AFF_INTEGER || affinity==SQLITE_AFF_REAL
+             || affinity==SQLITE_AFF_NUMERIC );
     if( 0==(pRec->flags&(MEM_Real|MEM_Int)) ){
       /* pRec does not have a valid integer or real representation. 
       ** Attempt a conversion if pRec has a string representation and
@@ -215,11 +208,14 @@ static void applyAffinity(Mem *pRec, char affinity, u8 enc){
       */
       int realnum;
       sqlite3VdbeMemNulTerminate(pRec);
-      if( pRec->flags&MEM_Str && sqlite3IsNumber(pRec->z, &realnum, enc) ){
-        if( realnum ){
-          Realify(pRec);
+      if( (pRec->flags&MEM_Str) && sqlite3IsNumber(pRec->z, &realnum, enc) ){
+        i64 value;
+        if( !realnum && sqlite3atoi64(pRec->z, &value) ){
+          sqlite3VdbeMemRelease(pRec);
+          pRec->i = value;
+          pRec->flags = MEM_Int;
         }else{
-          Integerify(pRec);
+          sqlite3VdbeMemNumerify(pRec);
         }
       }
     }else if( pRec->flags & MEM_Real ){
@@ -635,7 +631,6 @@ case OP_Real: {            /* same as TK_FLOAT, */
   pTos->r = sqlite3VdbeRealValue(pTos);
   pTos->flags |= MEM_Real;
   sqlite3VdbeChangeEncoding(pTos, db->enc);
-  sqlite3VdbeIntegerAffinity(pTos);
   break;
 }
 
@@ -1005,8 +1000,10 @@ case OP_Multiply:              /* same as TK_STAR, no-push */
 case OP_Divide:                /* same as TK_SLASH, no-push */
 case OP_Remainder: {           /* same as TK_REM, no-push */
   Mem *pNos = &pTos[-1];
+  int flags;
   assert( pNos>=p->aStack );
-  if( ((pTos->flags | pNos->flags) & MEM_Null)!=0 ){
+  flags = pTos->flags | pNos->flags;
+  if( (flags & MEM_Null)!=0 ){
     Release(pTos);
     pTos--;
     Release(pTos);
@@ -1021,7 +1018,6 @@ case OP_Remainder: {           /* same as TK_REM, no-push */
       case OP_Multiply:    b *= a;       break;
       case OP_Divide: {
         if( a==0 ) goto divide_by_zero;
-        if( b%a!=0 ) goto floating_point_divide;
         b /= a;
         break;
       }
@@ -1038,7 +1034,6 @@ case OP_Remainder: {           /* same as TK_REM, no-push */
     pTos->flags = MEM_Int;
   }else{
     double a, b;
-    floating_point_divide:
     a = sqlite3VdbeRealValue(pTos);
     b = sqlite3VdbeRealValue(pNos);
     switch( pOp->opcode ){
@@ -1063,7 +1058,9 @@ case OP_Remainder: {           /* same as TK_REM, no-push */
     Release(pTos);
     pTos->r = b;
     pTos->flags = MEM_Real;
-    sqlite3VdbeIntegerAffinity(pTos);
+    if( (flags & MEM_Real)==0 ){
+      sqlite3VdbeIntegerAffinity(pTos);
+    }
   }
   break;
 
@@ -1243,7 +1240,7 @@ case OP_ShiftRight: {           /* same as TK_RSHIFT, no-push */
 */
 case OP_AddImm: {            /* no-push */
   assert( pTos>=p->aStack );
-  Integerify(pTos);
+  sqlite3VdbeMemIntegerify(pTos);
   pTos->i += pOp->p1;
   break;
 }
@@ -1271,7 +1268,8 @@ case OP_ForceInt: {            /* no-push */
   if( pTos->flags & MEM_Int ){
     v = pTos->i + (pOp->p1!=0);
   }else{
-    Realify(pTos);
+    /* FIX ME:  should this not be assert( pTos->flags & MEM_Real ) ??? */
+    sqlite3VdbeMemRealify(pTos);
     v = (int)pTos->r;
     if( pTos->r>(double)v ) v++;
     if( pOp->p1 && pTos->r==(double)v ) v++;
@@ -1311,52 +1309,24 @@ case OP_MustBeInt: {            /* no-push */
   break;
 }
 
-/* Opcode: ToInt * * *
+/* Opcode: RealAffinity * * *
 **
-** Force the value on the top of the stack to be an integer.  If
-** The value is currently a real number, drop its fractional part.
-** If the value is text or blob, try to convert it to an integer using the
-** equivalent of atoi() and store 0 if no such conversion is possible.
+** If the top of the stack is an integer, convert it to a real value.
 **
-** A NULL value is not changed by this routine.  It remains NULL.
+** This opcode is used when extracting information from a column that
+** has REAL affinity.  Such column values may still be stored as
+** integers, for space efficiency, but after extraction we want them
+** to have only a real value.
 */
-case OP_ToInt: {                  /* no-push */
+case OP_RealAffinity: {                  /* no-push */
   assert( pTos>=p->aStack );
-  if( pTos->flags & MEM_Null ) break;
-  assert( MEM_Str==(MEM_Blob>>3) );
-  pTos->flags |= (pTos->flags&MEM_Blob)>>3;
-  applyAffinity(pTos, SQLITE_AFF_NUMERIC, db->enc);
-  sqlite3VdbeMemIntegerify(pTos);
-  break;
-}
-
-#ifndef SQLITE_OMIT_CAST
-/* Opcode: ToNumeric * * *
-**
-** Force the value on the top of the stack to be numeric (either an
-** integer or a floating-point number.
-** If the value is text or blob, try to convert it to an using the
-** equivalent of atoi() or atof() and store 0 if no such conversion 
-** is possible.
-**
-** A NULL value is not changed by this routine.  It remains NULL.
-*/
-case OP_ToNumeric: {                  /* no-push */
-  assert( pTos>=p->aStack );
-  if( pTos->flags & MEM_Null ) break;
-  assert( MEM_Str==(MEM_Blob>>3) );
-  pTos->flags |= (pTos->flags&MEM_Blob)>>3;
-  applyAffinity(pTos, SQLITE_AFF_NUMERIC, db->enc);
-  if( (pTos->flags & (MEM_Int|MEM_Real))==0 ){
+  if( pTos->flags & MEM_Int ){
     sqlite3VdbeMemRealify(pTos);
-  }else{
-    sqlite3VdbeMemRelease(pTos);
   }
-  assert( (pTos->flags & MEM_Dyn)==0 );
-  pTos->flags &= (MEM_Int|MEM_Real);
   break;
 }
 
+#ifndef SQLITE_OMIT_CAST
 /* Opcode: ToText * * *
 **
 ** Force the value on the top of the stack to be text.
@@ -1366,7 +1336,7 @@ case OP_ToNumeric: {                  /* no-push */
 **
 ** A NULL value is not changed by this routine.  It remains NULL.
 */
-case OP_ToText: {                  /* no-push */
+case OP_ToText: {                  /* same as TK_TO_TEXT, no-push */
   assert( pTos>=p->aStack );
   if( pTos->flags & MEM_Null ) break;
   assert( MEM_Str==(MEM_Blob>>3) );
@@ -1386,7 +1356,7 @@ case OP_ToText: {                  /* no-push */
 **
 ** A NULL value is not changed by this routine.  It remains NULL.
 */
-case OP_ToBlob: {                  /* no-push */
+case OP_ToBlob: {                  /* same as TK_TO_BLOB, no-push */
   assert( pTos>=p->aStack );
   if( pTos->flags & MEM_Null ) break;
   if( (pTos->flags & MEM_Blob)==0 ){
@@ -1397,6 +1367,60 @@ case OP_ToBlob: {                  /* no-push */
   pTos->flags &= ~(MEM_Int|MEM_Real|MEM_Str);
   break;
 }
+
+/* Opcode: ToNumeric * * *
+**
+** Force the value on the top of the stack to be numeric (either an
+** integer or a floating-point number.)
+** If the value is text or blob, try to convert it to an using the
+** equivalent of atoi() or atof() and store 0 if no such conversion 
+** is possible.
+**
+** A NULL value is not changed by this routine.  It remains NULL.
+*/
+case OP_ToNumeric: {                  /* same as TK_TO_NUMERIC, no-push */
+  assert( pTos>=p->aStack );
+  if( (pTos->flags & MEM_Null)==0 ){
+    sqlite3VdbeMemNumerify(pTos);
+  }
+  break;
+}
+#endif /* SQLITE_OMIT_CAST */
+
+/* Opcode: ToInt * * *
+**
+** Force the value on the top of the stack to be an integer.  If
+** The value is currently a real number, drop its fractional part.
+** If the value is text or blob, try to convert it to an integer using the
+** equivalent of atoi() and store 0 if no such conversion is possible.
+**
+** A NULL value is not changed by this routine.  It remains NULL.
+*/
+case OP_ToInt: {                  /* same as TK_TO_INT, no-push */
+  assert( pTos>=p->aStack );
+  if( (pTos->flags & MEM_Null)==0 ){
+    sqlite3VdbeMemIntegerify(pTos);
+  }
+  break;
+}
+
+#ifndef SQLITE_OMIT_CAST
+/* Opcode: ToReal * * *
+**
+** Force the value on the top of the stack to be a floating point number.
+** If The value is currently an integer, convert it.
+** If the value is text or blob, try to convert it to an integer using the
+** equivalent of atoi() and store 0 if no such conversion is possible.
+**
+** A NULL value is not changed by this routine.  It remains NULL.
+*/
+case OP_ToReal: {                  /* same as TK_TO_REAL, no-push */
+  assert( pTos>=p->aStack );
+  if( (pTos->flags & MEM_Null)==0 ){
+    sqlite3VdbeMemRealify(pTos);
+  }
+  break;
+}
 #endif /* SQLITE_OMIT_CAST */
 
 /* Opcode: Eq P1 P2 P3
@@ -1416,7 +1440,8 @@ case OP_ToBlob: {                  /* no-push */
 ** 0x200 is set but is NULL when the 0x200 bit of P1 is clear.
 **
 ** The least significant byte of P1 (mask 0xff) must be an affinity character -
-** 'n', 't', or 'o' - or 0x00. An attempt is made to coerce both values
+** SQLITE_AFF_TEXT, SQLITE_AFF_INTEGER, and so forth. An attempt is made 
+** to coerce both values
 ** according to the affinity before the comparison is made. If the byte is
 ** 0x00, then numeric affinity is used.
 **
@@ -1565,13 +1590,13 @@ case OP_Or: {             /* same as TK_OR, no-push */
   if( pTos->flags & MEM_Null ){
     v1 = 2;
   }else{
-    Integerify(pTos);
+    sqlite3VdbeMemIntegerify(pTos);
     v1 = pTos->i==0;
   }
   if( pNos->flags & MEM_Null ){
     v2 = 2;
   }else{
-    Integerify(pNos);
+    sqlite3VdbeMemIntegerify(pNos);
     v2 = pNos->i==0;
   }
   if( pOp->opcode==OP_And ){
@@ -1614,7 +1639,6 @@ case OP_AbsValue: {
       pTos->r = -pTos->r;
     }
     pTos->flags = MEM_Real;
-    sqlite3VdbeIntegerAffinity(pTos);
   }else if( pTos->flags & MEM_Int ){
     Release(pTos);
     if( pOp->opcode==OP_Negative || pTos->i<0 ){
@@ -1624,7 +1648,7 @@ case OP_AbsValue: {
   }else if( pTos->flags & MEM_Null ){
     /* Do nothing */
   }else{
-    Realify(pTos);
+    sqlite3VdbeMemNumerify(pTos);
     goto neg_abs_real_case;
   }
   break;
@@ -1639,7 +1663,7 @@ case OP_AbsValue: {
 case OP_Not: {                /* same as TK_NOT, no-push */
   assert( pTos>=p->aStack );
   if( pTos->flags & MEM_Null ) break;  /* Do nothing to NULLs */
-  Integerify(pTos);
+  sqlite3VdbeMemIntegerify(pTos);
   assert( (pTos->flags & MEM_Dyn)==0 );
   pTos->i = !pTos->i;
   pTos->flags = MEM_Int;
@@ -1655,7 +1679,7 @@ case OP_Not: {                /* same as TK_NOT, no-push */
 case OP_BitNot: {             /* same as TK_BITNOT, no-push */
   assert( pTos>=p->aStack );
   if( pTos->flags & MEM_Null ) break;  /* Do nothing to NULLs */
-  Integerify(pTos);
+  sqlite3VdbeMemIntegerify(pTos);
   assert( (pTos->flags & MEM_Dyn)==0 );
   pTos->i = ~pTos->i;
   pTos->flags = MEM_Int;
@@ -2074,10 +2098,8 @@ op_column_out:
 ** field of the index key (i.e. the first character of P3 corresponds to the
 ** lowest element on the stack).
 **
-** The mapping from character to affinity is as follows:
-**    'n' = NUMERIC.
-**    't' = TEXT.
-**    'o' = NONE.
+** The mapping from character to affinity is given by the SQLITE_AFF_
+** macros defined in sqliteInt.h.
 **
 ** If P3 is NULL then all index fields have the affinity NONE.
 **
@@ -2158,7 +2180,7 @@ case OP_MakeRecord: {
   if( addRowid ){
     pRowid = &pTos[0-nField];
     assert( pRowid>=p->aStack );
-    Integerify(pRowid);
+    sqlite3VdbeMemIntegerify(pRowid);
     serial_type = sqlite3VdbeSerialType(pRowid);
     nData += sqlite3VdbeSerialTypeLen(serial_type);
     nHdr += sqlite3VarintLen(serial_type);
@@ -2388,7 +2410,7 @@ case OP_SetCookie: {       /* no-push */
   pDb = &db->aDb[pOp->p1];
   assert( pDb->pBt!=0 );
   assert( pTos>=p->aStack );
-  Integerify(pTos);
+  sqlite3VdbeMemIntegerify(pTos);
   /* See note about index shifting on OP_ReadCookie */
   rc = sqlite3BtreeUpdateMeta(pDb->pBt, 1+pOp->p2, (int)pTos->i);
   if( pOp->p2==0 ){
@@ -2487,7 +2509,7 @@ case OP_OpenWrite: {       /* no-push */
   Cursor *pCur;
   
   assert( pTos>=p->aStack );
-  Integerify(pTos);
+  sqlite3VdbeMemIntegerify(pTos);
   iDb = pTos->i;
   assert( (pTos->flags & MEM_Dyn)==0 );
   pTos--;
@@ -2497,7 +2519,7 @@ case OP_OpenWrite: {       /* no-push */
   wrFlag = pOp->opcode==OP_OpenWrite;
   if( p2<=0 ){
     assert( pTos>=p->aStack );
-    Integerify(pTos);
+    sqlite3VdbeMemIntegerify(pTos);
     p2 = pTos->i;
     assert( (pTos->flags & MEM_Dyn)==0 );
     pTos--;
@@ -2717,7 +2739,7 @@ case OP_MoveGt: {       /* no-push */
     *pC->pIncrKey = oc==OP_MoveGt || oc==OP_MoveLe;
     if( pC->isTable ){
       i64 iKey;
-      Integerify(pTos);
+      sqlite3VdbeMemIntegerify(pTos);
       iKey = intToKey(pTos->i);
       if( pOp->p2==0 && pOp->opcode==OP_MoveGe ){
         pC->movetoTarget = iKey;
@@ -2887,7 +2909,7 @@ case OP_IsUnique: {        /* no-push */
   /* Pop the value R off the top of the stack
   */
   assert( pNos>=p->aStack );
-  Integerify(pTos);
+  sqlite3VdbeMemIntegerify(pTos);
   R = pTos->i;
   assert( (pTos->flags & MEM_Dyn)==0 );
   pTos--;
@@ -3116,7 +3138,7 @@ case OP_NewRowid: {
         Mem *pMem;
         assert( pOp->p2>0 && pOp->p2<p->nMem );  /* P2 is a valid memory cell */
         pMem = &p->aMem[pOp->p2];
-        Integerify(pMem);
+        sqlite3VdbeMemIntegerify(pMem);
         assert( (pMem->flags & MEM_Int)!=0 );  /* mem(P2) holds an integer */
         if( pMem->i==MAX_ROWID || pC->useRandomRowid ){
           rc = SQLITE_FULL;
@@ -3997,7 +4019,7 @@ case OP_IntegrityCk: {
 */
 case OP_FifoWrite: {        /* no-push */
   assert( pTos>=p->aStack );
-  Integerify(pTos);
+  sqlite3VdbeMemIntegerify(pTos);
   sqlite3VdbeFifoPush(&p->sFifo, pTos->i);
   assert( (pTos->flags & MEM_Dyn)==0 );
   pTos--;
@@ -4121,8 +4143,8 @@ case OP_MemMax: {        /* no-push */
   assert( pTos>=p->aStack );
   assert( i>=0 && i<p->nMem );
   pMem = &p->aMem[i];
-  Integerify(pMem);
-  Integerify(pTos);
+  sqlite3VdbeMemIntegerify(pMem);
+  sqlite3VdbeMemIntegerify(pTos);
   if( pMem->i<pTos->i){
     pMem->i = pTos->i;
   }
diff --git a/src/vdbeInt.h b/src/vdbeInt.h
index e38f809e6..46584986d 100644
--- a/src/vdbeInt.h
+++ b/src/vdbeInt.h
@@ -362,6 +362,7 @@ int sqlite3VdbeMemIntegerify(Mem*);
 double sqlite3VdbeRealValue(Mem*);
 void sqlite3VdbeIntegerAffinity(Mem*);
 int sqlite3VdbeMemRealify(Mem*);
+int sqlite3VdbeMemNumerify(Mem*);
 int sqlite3VdbeMemFromBtree(BtCursor*,int,int,int,Mem*);
 void sqlite3VdbeMemRelease(Mem *p);
 void sqlite3VdbeMemFinalize(Mem*, FuncDef*);
diff --git a/src/vdbemem.c b/src/vdbemem.c
index ba8cf5630..8ee2e2bd2 100644
--- a/src/vdbemem.c
+++ b/src/vdbemem.c
@@ -265,16 +265,6 @@ i64 sqlite3VdbeIntValue(Mem *pMem){
 }
 
 /*
-** Convert pMem to type integer.  Invalidate any prior representations.
-*/
-int sqlite3VdbeMemIntegerify(Mem *pMem){
-  pMem->i = sqlite3VdbeIntValue(pMem);
-  sqlite3VdbeMemRelease(pMem);
-  pMem->flags = MEM_Int;
-  return SQLITE_OK;
-}
-
-/*
 ** Return the best representation of pMem that we can get into a
 ** double.  If pMem is already a double or an integer, return its
 ** value.  If it is a string or blob, try to convert it to a double.
@@ -311,15 +301,33 @@ void sqlite3VdbeIntegerAffinity(Mem *pMem){
   }
 }
 
+/*
+** Convert pMem to type integer.  Invalidate any prior representations.
+*/
+int sqlite3VdbeMemIntegerify(Mem *pMem){
+  pMem->i = sqlite3VdbeIntValue(pMem);
+  sqlite3VdbeMemRelease(pMem);
+  pMem->flags = MEM_Int;
+  return SQLITE_OK;
+}
 
 /*
-** Convert pMem so that it is of type MEM_Real and also MEM_Int if
-** possible.  Invalidate any prior representations.
+** Convert pMem so that it is of type MEM_Real.
+** Invalidate any prior representations.
 */
 int sqlite3VdbeMemRealify(Mem *pMem){
   pMem->r = sqlite3VdbeRealValue(pMem);
   sqlite3VdbeMemRelease(pMem);
   pMem->flags = MEM_Real;
+  return SQLITE_OK;
+}
+
+/*
+** Convert pMem so that it has types MEM_Real or MEM_Int or both.
+** Invalidate any prior representations.
+*/
+int sqlite3VdbeMemNumerify(Mem *pMem){
+  sqlite3VdbeMemRealify(pMem);
   sqlite3VdbeIntegerAffinity(pMem);
   return SQLITE_OK;
 }
diff --git a/src/where.c b/src/where.c
index 21958bf6c..481902306 100644
--- a/src/where.c
+++ b/src/where.c
@@ -16,7 +16,7 @@
 ** so is applicable.  Because this module is responsible for selecting
 ** indices, you might also think of this module as the "query optimizer".
 **
-** $Id: where.c,v 1.180 2005/10/13 02:09:50 drh Exp $
+** $Id: where.c,v 1.181 2005/11/14 22:29:06 drh Exp $
 */
 #include "sqliteInt.h"
 
@@ -1679,7 +1679,7 @@ WhereInfo *sqlite3WhereBegin(
       if( testOp!=OP_Noop ){
         sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
         sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
-        sqlite3VdbeAddOp(v, testOp, 'n', brk);
+        sqlite3VdbeAddOp(v, testOp, SQLITE_AFF_NUMERIC, brk);
       }
     }else if( pLevel->flags & WHERE_COLUMN_RANGE ){
       /* Case 3: The WHERE clause term that refers to the right-most