/ Check-in [29ad9e91]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Rename SQLITE_AFF_NONE to SQLITE_AFF_BLOB to avoid confusion with "no affinity".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 29ad9e917330969810ac1bc685bba4282401bdae
User & Date: drh 2015-06-02 16:19:56
Context
2015-06-02
17:25
Add test cases for type affinity rules. check-in: 9678646d user: drh tags: trunk
16:19
Rename SQLITE_AFF_NONE to SQLITE_AFF_BLOB to avoid confusion with "no affinity". check-in: 29ad9e91 user: drh tags: trunk
15:32
Rename SQLITE_AFF_NONE to SQLITE_AFF_BLOB. Closed-Leaf check-in: bce3f041 user: drh tags: blob-affinity-rename
14:02
Fix a faulty assert() in btree.c. Update the database fuzz test file with new test cases. check-in: 4e621af1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   688    688   
   689    689     /* Ensure the default expression is something that sqlite3ValueFromExpr()
   690    690     ** can handle (i.e. not CURRENT_TIME etc.)
   691    691     */
   692    692     if( pDflt ){
   693    693       sqlite3_value *pVal = 0;
   694    694       int rc;
   695         -    rc = sqlite3ValueFromExpr(db, pDflt, SQLITE_UTF8, SQLITE_AFF_NONE, &pVal);
          695  +    rc = sqlite3ValueFromExpr(db, pDflt, SQLITE_UTF8, SQLITE_AFF_BLOB, &pVal);
   696    696       assert( rc==SQLITE_OK || rc==SQLITE_NOMEM );
   697    697       if( rc!=SQLITE_OK ){
   698    698         db->mallocFailed = 1;
   699    699         return;
   700    700       }
   701    701       if( !pVal ){
   702    702         sqlite3ErrorMsg(pParse, "Cannot add a column with non-constant default");

Changes to src/build.c.

  1088   1088       p->aCol = aNew;
  1089   1089     }
  1090   1090     pCol = &p->aCol[p->nCol];
  1091   1091     memset(pCol, 0, sizeof(p->aCol[0]));
  1092   1092     pCol->zName = z;
  1093   1093    
  1094   1094     /* If there is no type specified, columns have the default affinity
  1095         -  ** 'NONE'. If there is a type specified, then sqlite3AddColumnType() will
         1095  +  ** 'BLOB'. If there is a type specified, then sqlite3AddColumnType() will
  1096   1096     ** be called next to set pCol->affinity correctly.
  1097   1097     */
  1098         -  pCol->affinity = SQLITE_AFF_NONE;
         1098  +  pCol->affinity = SQLITE_AFF_BLOB;
  1099   1099     pCol->szEst = 1;
  1100   1100     p->nCol++;
  1101   1101   }
  1102   1102   
  1103   1103   /*
  1104   1104   ** This routine is called by the parser while in the middle of
  1105   1105   ** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
................................................................................
  1126   1126   **
  1127   1127   ** Substring     | Affinity
  1128   1128   ** --------------------------------
  1129   1129   ** 'INT'         | SQLITE_AFF_INTEGER
  1130   1130   ** 'CHAR'        | SQLITE_AFF_TEXT
  1131   1131   ** 'CLOB'        | SQLITE_AFF_TEXT
  1132   1132   ** 'TEXT'        | SQLITE_AFF_TEXT
  1133         -** 'BLOB'        | SQLITE_AFF_NONE
         1133  +** 'BLOB'        | SQLITE_AFF_BLOB
  1134   1134   ** 'REAL'        | SQLITE_AFF_REAL
  1135   1135   ** 'FLOA'        | SQLITE_AFF_REAL
  1136   1136   ** 'DOUB'        | SQLITE_AFF_REAL
  1137   1137   **
  1138   1138   ** If none of the substrings in the above table are found,
  1139   1139   ** SQLITE_AFF_NUMERIC is returned.
  1140   1140   */
................................................................................
  1152   1152         zChar = zIn;
  1153   1153       }else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){       /* CLOB */
  1154   1154         aff = SQLITE_AFF_TEXT;
  1155   1155       }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){       /* TEXT */
  1156   1156         aff = SQLITE_AFF_TEXT;
  1157   1157       }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')          /* BLOB */
  1158   1158           && (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
  1159         -      aff = SQLITE_AFF_NONE;
         1159  +      aff = SQLITE_AFF_BLOB;
  1160   1160         if( zIn[0]=='(' ) zChar = zIn;
  1161   1161   #ifndef SQLITE_OMIT_FLOATING_POINT
  1162   1162       }else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')          /* REAL */
  1163   1163           && aff==SQLITE_AFF_NUMERIC ){
  1164   1164         aff = SQLITE_AFF_REAL;
  1165   1165       }else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')          /* FLOA */
  1166   1166           && aff==SQLITE_AFF_NUMERIC ){
................................................................................
  1544   1544     }
  1545   1545     sqlite3_snprintf(n, zStmt, "CREATE TABLE ");
  1546   1546     k = sqlite3Strlen30(zStmt);
  1547   1547     identPut(zStmt, &k, p->zName);
  1548   1548     zStmt[k++] = '(';
  1549   1549     for(pCol=p->aCol, i=0; i<p->nCol; i++, pCol++){
  1550   1550       static const char * const azType[] = {
  1551         -        /* SQLITE_AFF_NONE    */ "",
         1551  +        /* SQLITE_AFF_BLOB    */ "",
  1552   1552           /* SQLITE_AFF_TEXT    */ " TEXT",
  1553   1553           /* SQLITE_AFF_NUMERIC */ " NUM",
  1554   1554           /* SQLITE_AFF_INTEGER */ " INT",
  1555   1555           /* SQLITE_AFF_REAL    */ " REAL"
  1556   1556       };
  1557   1557       int len;
  1558   1558       const char *zType;
  1559   1559   
  1560   1560       sqlite3_snprintf(n-k, &zStmt[k], zSep);
  1561   1561       k += sqlite3Strlen30(&zStmt[k]);
  1562   1562       zSep = zSep2;
  1563   1563       identPut(zStmt, &k, pCol->zName);
  1564         -    assert( pCol->affinity-SQLITE_AFF_NONE >= 0 );
  1565         -    assert( pCol->affinity-SQLITE_AFF_NONE < ArraySize(azType) );
  1566         -    testcase( pCol->affinity==SQLITE_AFF_NONE );
         1564  +    assert( pCol->affinity-SQLITE_AFF_BLOB >= 0 );
         1565  +    assert( pCol->affinity-SQLITE_AFF_BLOB < ArraySize(azType) );
         1566  +    testcase( pCol->affinity==SQLITE_AFF_BLOB );
  1567   1567       testcase( pCol->affinity==SQLITE_AFF_TEXT );
  1568   1568       testcase( pCol->affinity==SQLITE_AFF_NUMERIC );
  1569   1569       testcase( pCol->affinity==SQLITE_AFF_INTEGER );
  1570   1570       testcase( pCol->affinity==SQLITE_AFF_REAL );
  1571   1571       
  1572         -    zType = azType[pCol->affinity - SQLITE_AFF_NONE];
         1572  +    zType = azType[pCol->affinity - SQLITE_AFF_BLOB];
  1573   1573       len = sqlite3Strlen30(zType);
  1574         -    assert( pCol->affinity==SQLITE_AFF_NONE 
         1574  +    assert( pCol->affinity==SQLITE_AFF_BLOB 
  1575   1575               || pCol->affinity==sqlite3AffinityType(zType, 0) );
  1576   1576       memcpy(&zStmt[k], zType, len);
  1577   1577       k += len;
  1578   1578       assert( k<=n );
  1579   1579     }
  1580   1580     sqlite3_snprintf(n-k, &zStmt[k], "%s", zEnd);
  1581   1581     return zStmt;

Changes to src/expr.c.

   187    187     if( aff1 && aff2 ){
   188    188       /* Both sides of the comparison are columns. If one has numeric
   189    189       ** affinity, use that. Otherwise use no affinity.
   190    190       */
   191    191       if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){
   192    192         return SQLITE_AFF_NUMERIC;
   193    193       }else{
   194         -      return SQLITE_AFF_NONE;
          194  +      return SQLITE_AFF_BLOB;
   195    195       }
   196    196     }else if( !aff1 && !aff2 ){
   197    197       /* Neither side of the comparison is a column.  Compare the
   198    198       ** results directly.
   199    199       */
   200         -    return SQLITE_AFF_NONE;
          200  +    return SQLITE_AFF_BLOB;
   201    201     }else{
   202    202       /* One side is a column, the other is not. Use the columns affinity. */
   203    203       assert( aff1==0 || aff2==0 );
   204    204       return (aff1 + aff2);
   205    205     }
   206    206   }
   207    207   
................................................................................
   217    217     assert( pExpr->pLeft );
   218    218     aff = sqlite3ExprAffinity(pExpr->pLeft);
   219    219     if( pExpr->pRight ){
   220    220       aff = sqlite3CompareAffinity(pExpr->pRight, aff);
   221    221     }else if( ExprHasProperty(pExpr, EP_xIsSelect) ){
   222    222       aff = sqlite3CompareAffinity(pExpr->x.pSelect->pEList->a[0].pExpr, aff);
   223    223     }else if( !aff ){
   224         -    aff = SQLITE_AFF_NONE;
          224  +    aff = SQLITE_AFF_BLOB;
   225    225     }
   226    226     return aff;
   227    227   }
   228    228   
   229    229   /*
   230    230   ** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
   231    231   ** idx_affinity is the affinity of an indexed column. Return true
   232    232   ** if the index with affinity idx_affinity may be used to implement
   233    233   ** the comparison in pExpr.
   234    234   */
   235    235   int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
   236    236     char aff = comparisonAffinity(pExpr);
   237    237     switch( aff ){
   238         -    case SQLITE_AFF_NONE:
          238  +    case SQLITE_AFF_BLOB:
   239    239         return 1;
   240    240       case SQLITE_AFF_TEXT:
   241    241         return idx_affinity==SQLITE_AFF_TEXT;
   242    242       default:
   243    243         return sqlite3IsNumericAffinity(idx_affinity);
   244    244     }
   245    245   }
................................................................................
  1478   1478   ** This routine is used to determine if the OP_Affinity operation
  1479   1479   ** can be omitted.  When in doubt return FALSE.  A false negative
  1480   1480   ** is harmless.  A false positive, however, can result in the wrong
  1481   1481   ** answer.
  1482   1482   */
  1483   1483   int sqlite3ExprNeedsNoAffinityChange(const Expr *p, char aff){
  1484   1484     u8 op;
  1485         -  if( aff==SQLITE_AFF_NONE ) return 1;
         1485  +  if( aff==SQLITE_AFF_BLOB ) return 1;
  1486   1486     while( p->op==TK_UPLUS || p->op==TK_UMINUS ){ p = p->pLeft; }
  1487   1487     op = p->op;
  1488   1488     if( op==TK_REGISTER ) op = p->op2;
  1489   1489     switch( op ){
  1490   1490       case TK_INTEGER: {
  1491   1491         return aff==SQLITE_AFF_INTEGER || aff==SQLITE_AFF_NUMERIC;
  1492   1492       }
................................................................................
  1929   1929           */
  1930   1930           int i;
  1931   1931           ExprList *pList = pExpr->x.pList;
  1932   1932           struct ExprList_item *pItem;
  1933   1933           int r1, r2, r3;
  1934   1934   
  1935   1935           if( !affinity ){
  1936         -          affinity = SQLITE_AFF_NONE;
         1936  +          affinity = SQLITE_AFF_BLOB;
  1937   1937           }
  1938   1938           if( pKeyInfo ){
  1939   1939             assert( sqlite3KeyInfoIsWriteable(pKeyInfo) );
  1940   1940             pKeyInfo->aColl[0] = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
  1941   1941           }
  1942   1942   
  1943   1943           /* Loop through each expression in <exprlist>. */

Changes to src/insert.c.

    52     52   /*
    53     53   ** Return a pointer to the column affinity string associated with index
    54     54   ** pIdx. A column affinity string has one character for each column in 
    55     55   ** the table, according to the affinity of the column:
    56     56   **
    57     57   **  Character      Column affinity
    58     58   **  ------------------------------
    59         -**  'A'            NONE
           59  +**  'A'            BLOB
    60     60   **  'B'            TEXT
    61     61   **  'C'            NUMERIC
    62     62   **  'D'            INTEGER
    63     63   **  'F'            REAL
    64     64   **
    65     65   ** An extra 'D' is appended to the end of the string to cover the
    66     66   ** rowid that appears as the last column in every index.
................................................................................
    95     95     }
    96     96    
    97     97     return pIdx->zColAff;
    98     98   }
    99     99   
   100    100   /*
   101    101   ** Compute the affinity string for table pTab, if it has not already been
   102         -** computed.  As an optimization, omit trailing SQLITE_AFF_NONE affinities.
          102  +** computed.  As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
   103    103   **
   104         -** If the affinity exists (if it is no entirely SQLITE_AFF_NONE values) and
          104  +** If the affinity exists (if it is no entirely SQLITE_AFF_BLOB values) and
   105    105   ** if iReg>0 then code an OP_Affinity opcode that will set the affinities
   106    106   ** for register iReg and following.  Or if affinities exists and iReg==0,
   107    107   ** then just set the P4 operand of the previous opcode (which should  be
   108    108   ** an OP_MakeRecord) to the affinity string.
   109    109   **
   110    110   ** A column affinity string has one character per column:
   111    111   **
   112    112   **  Character      Column affinity
   113    113   **  ------------------------------
   114         -**  'A'            NONE
          114  +**  'A'            BLOB
   115    115   **  'B'            TEXT
   116    116   **  'C'            NUMERIC
   117    117   **  'D'            INTEGER
   118    118   **  'E'            REAL
   119    119   */
   120    120   void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
   121    121     int i;
................................................................................
   129    129       }
   130    130   
   131    131       for(i=0; i<pTab->nCol; i++){
   132    132         zColAff[i] = pTab->aCol[i].affinity;
   133    133       }
   134    134       do{
   135    135         zColAff[i--] = 0;
   136         -    }while( i>=0 && zColAff[i]==SQLITE_AFF_NONE );
          136  +    }while( i>=0 && zColAff[i]==SQLITE_AFF_BLOB );
   137    137       pTab->zColAff = zColAff;
   138    138     }
   139    139     i = sqlite3Strlen30(zColAff);
   140    140     if( i ){
   141    141       if( iReg ){
   142    142         sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);
   143    143       }else{

Changes to src/select.c.

  1705   1705     for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){
  1706   1706       p = a[i].pExpr;
  1707   1707       if( pCol->zType==0 ){
  1708   1708         pCol->zType = sqlite3DbStrDup(db, columnType(&sNC, p,0,0,0, &pCol->szEst));
  1709   1709       }
  1710   1710       szAll += pCol->szEst;
  1711   1711       pCol->affinity = sqlite3ExprAffinity(p);
  1712         -    if( pCol->affinity==0 ) pCol->affinity = SQLITE_AFF_NONE;
         1712  +    if( pCol->affinity==0 ) pCol->affinity = SQLITE_AFF_BLOB;
  1713   1713       pColl = sqlite3ExprCollSeq(pParse, p);
  1714   1714       if( pColl && pCol->zColl==0 ){
  1715   1715         pCol->zColl = sqlite3DbStrDup(db, pColl->zName);
  1716   1716       }
  1717   1717     }
  1718   1718     pTab->szTabRow = sqlite3LogEst(szAll*4);
  1719   1719   }

Changes to src/sqliteInt.h.

  1497   1497   ** the speed a little by numbering the values consecutively.  
  1498   1498   **
  1499   1499   ** But rather than start with 0 or 1, we begin with 'A'.  That way,
  1500   1500   ** when multiple affinity types are concatenated into a string and
  1501   1501   ** used as the P4 operand, they will be more readable.
  1502   1502   **
  1503   1503   ** Note also that the numeric types are grouped together so that testing
  1504         -** for a numeric type is a single comparison.  And the NONE type is first.
         1504  +** for a numeric type is a single comparison.  And the BLOB type is first.
  1505   1505   */
  1506         -#define SQLITE_AFF_NONE     'A'
         1506  +#define SQLITE_AFF_BLOB     'A'
  1507   1507   #define SQLITE_AFF_TEXT     'B'
  1508   1508   #define SQLITE_AFF_NUMERIC  'C'
  1509   1509   #define SQLITE_AFF_INTEGER  'D'
  1510   1510   #define SQLITE_AFF_REAL     'E'
  1511   1511   
  1512   1512   #define sqlite3IsNumericAffinity(X)  ((X)>=SQLITE_AFF_NUMERIC)
  1513   1513   

Changes to src/vdbe.c.

   266    266   **    is not possible.  Note that the integer representation is
   267    267   **    always preferred, even if the affinity is REAL, because
   268    268   **    an integer representation is more space efficient on disk.
   269    269   **
   270    270   ** SQLITE_AFF_TEXT:
   271    271   **    Convert pRec to a text representation.
   272    272   **
   273         -** SQLITE_AFF_NONE:
          273  +** SQLITE_AFF_BLOB:
   274    274   **    No-op.  pRec is unchanged.
   275    275   */
   276    276   static void applyAffinity(
   277    277     Mem *pRec,          /* The value to apply affinity to */
   278    278     char affinity,      /* The affinity to be applied */
   279    279     u8 enc              /* Use this text encoding */
   280    280   ){
................................................................................
  1776   1776   ** <li value="100"> INTEGER
  1777   1777   ** <li value="101"> REAL
  1778   1778   ** </ul>
  1779   1779   **
  1780   1780   ** A NULL value is not changed by this routine.  It remains NULL.
  1781   1781   */
  1782   1782   case OP_Cast: {                  /* in1 */
  1783         -  assert( pOp->p2>=SQLITE_AFF_NONE && pOp->p2<=SQLITE_AFF_REAL );
         1783  +  assert( pOp->p2>=SQLITE_AFF_BLOB && pOp->p2<=SQLITE_AFF_REAL );
  1784   1784     testcase( pOp->p2==SQLITE_AFF_TEXT );
  1785         -  testcase( pOp->p2==SQLITE_AFF_NONE );
         1785  +  testcase( pOp->p2==SQLITE_AFF_BLOB );
  1786   1786     testcase( pOp->p2==SQLITE_AFF_NUMERIC );
  1787   1787     testcase( pOp->p2==SQLITE_AFF_INTEGER );
  1788   1788     testcase( pOp->p2==SQLITE_AFF_REAL );
  1789   1789     pIn1 = &aMem[pOp->p1];
  1790   1790     memAboutToChange(p, pIn1);
  1791   1791     rc = ExpandBlob(pIn1);
  1792   1792     sqlite3VdbeMemCast(pIn1, pOp->p2, encoding);
................................................................................
  2589   2589   ** P4 may be a string that is P2 characters long.  The nth character of the
  2590   2590   ** string indicates the column affinity that should be used for the nth
  2591   2591   ** field of the index key.
  2592   2592   **
  2593   2593   ** The mapping from character to affinity is given by the SQLITE_AFF_
  2594   2594   ** macros defined in sqliteInt.h.
  2595   2595   **
  2596         -** If P4 is NULL then all index fields have the affinity NONE.
         2596  +** If P4 is NULL then all index fields have the affinity BLOB.
  2597   2597   */
  2598   2598   case OP_MakeRecord: {
  2599   2599     u8 *zNewRecord;        /* A buffer to hold the data for the new record */
  2600   2600     Mem *pRec;             /* The new record */
  2601   2601     u64 nData;             /* Number of bytes of data space */
  2602   2602     int nHdr;              /* Number of bytes of header space */
  2603   2603     i64 nByte;             /* Data space required for this record */

Changes to src/vdbemem.c.

   584    584   ** is forced.  In other words, the value is converted into the desired
   585    585   ** affinity even if that results in loss of data.  This routine is
   586    586   ** used (for example) to implement the SQL "cast()" operator.
   587    587   */
   588    588   void sqlite3VdbeMemCast(Mem *pMem, u8 aff, u8 encoding){
   589    589     if( pMem->flags & MEM_Null ) return;
   590    590     switch( aff ){
   591         -    case SQLITE_AFF_NONE: {   /* Really a cast to BLOB */
          591  +    case SQLITE_AFF_BLOB: {   /* Really a cast to BLOB */
   592    592         if( (pMem->flags & MEM_Blob)==0 ){
   593    593           sqlite3ValueApplyAffinity(pMem, SQLITE_AFF_TEXT, encoding);
   594    594           assert( pMem->flags & MEM_Str || pMem->db->mallocFailed );
   595    595           MemSetTypeFlag(pMem, MEM_Blob);
   596    596         }else{
   597    597           pMem->flags &= ~(MEM_TypeMask&~MEM_Blob);
   598    598         }
................................................................................
  1307   1307       if( ExprHasProperty(pExpr, EP_IntValue) ){
  1308   1308         sqlite3VdbeMemSetInt64(pVal, (i64)pExpr->u.iValue*negInt);
  1309   1309       }else{
  1310   1310         zVal = sqlite3MPrintf(db, "%s%s", zNeg, pExpr->u.zToken);
  1311   1311         if( zVal==0 ) goto no_mem;
  1312   1312         sqlite3ValueSetStr(pVal, -1, zVal, SQLITE_UTF8, SQLITE_DYNAMIC);
  1313   1313       }
  1314         -    if( (op==TK_INTEGER || op==TK_FLOAT ) && affinity==SQLITE_AFF_NONE ){
         1314  +    if( (op==TK_INTEGER || op==TK_FLOAT ) && affinity==SQLITE_AFF_BLOB ){
  1315   1315         sqlite3ValueApplyAffinity(pVal, SQLITE_AFF_NUMERIC, SQLITE_UTF8);
  1316   1316       }else{
  1317   1317         sqlite3ValueApplyAffinity(pVal, affinity, SQLITE_UTF8);
  1318   1318       }
  1319   1319       if( pVal->flags & (MEM_Int|MEM_Real) ) pVal->flags &= ~MEM_Str;
  1320   1320       if( enc!=SQLITE_UTF8 ){
  1321   1321         rc = sqlite3VdbeChangeEncoding(pVal, enc);

Changes to src/where.c.

   676    676     assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */
   677    677   
   678    678     pRight = sqlite3ExprSkipCollate(pList->a[0].pExpr);
   679    679     op = pRight->op;
   680    680     if( op==TK_VARIABLE ){
   681    681       Vdbe *pReprepare = pParse->pReprepare;
   682    682       int iCol = pRight->iColumn;
   683         -    pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_NONE);
          683  +    pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_BLOB);
   684    684       if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
   685    685         z = (char *)sqlite3_value_text(pVal);
   686    686       }
   687    687       sqlite3VdbeSetVarmask(pParse->pVdbe, iCol);
   688    688       assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
   689    689     }else if( op==TK_STRING ){
   690    690       z = pRight->u.zToken;
................................................................................
  2820   2820     }
  2821   2821   }
  2822   2822   
  2823   2823   /*
  2824   2824   ** Code an OP_Affinity opcode to apply the column affinity string zAff
  2825   2825   ** to the n registers starting at base. 
  2826   2826   **
  2827         -** As an optimization, SQLITE_AFF_NONE entries (which are no-ops) at the
         2827  +** As an optimization, SQLITE_AFF_BLOB entries (which are no-ops) at the
  2828   2828   ** beginning and end of zAff are ignored.  If all entries in zAff are
  2829         -** SQLITE_AFF_NONE, then no code gets generated.
         2829  +** SQLITE_AFF_BLOB, then no code gets generated.
  2830   2830   **
  2831   2831   ** This routine makes its own copy of zAff so that the caller is free
  2832   2832   ** to modify zAff after this routine returns.
  2833   2833   */
  2834   2834   static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){
  2835   2835     Vdbe *v = pParse->pVdbe;
  2836   2836     if( zAff==0 ){
  2837   2837       assert( pParse->db->mallocFailed );
  2838   2838       return;
  2839   2839     }
  2840   2840     assert( v!=0 );
  2841   2841   
  2842         -  /* Adjust base and n to skip over SQLITE_AFF_NONE entries at the beginning
         2842  +  /* Adjust base and n to skip over SQLITE_AFF_BLOB entries at the beginning
  2843   2843     ** and end of the affinity string.
  2844   2844     */
  2845         -  while( n>0 && zAff[0]==SQLITE_AFF_NONE ){
         2845  +  while( n>0 && zAff[0]==SQLITE_AFF_BLOB ){
  2846   2846       n--;
  2847   2847       base++;
  2848   2848       zAff++;
  2849   2849     }
  2850         -  while( n>1 && zAff[n-1]==SQLITE_AFF_NONE ){
         2850  +  while( n>1 && zAff[n-1]==SQLITE_AFF_BLOB ){
  2851   2851       n--;
  2852   2852     }
  2853   2853   
  2854   2854     /* Code the OP_Affinity opcode if there is anything left to do. */
  2855   2855     if( n>0 ){
  2856   2856       sqlite3VdbeAddOp2(v, OP_Affinity, base, n);
  2857   2857       sqlite3VdbeChangeP4(v, -1, zAff, n);
................................................................................
  2973   2973   ** key value of the loop.  If one or more IN operators appear, then
  2974   2974   ** this routine allocates an additional nEq memory cells for internal
  2975   2975   ** use.
  2976   2976   **
  2977   2977   ** Before returning, *pzAff is set to point to a buffer containing a
  2978   2978   ** copy of the column affinity string of the index allocated using
  2979   2979   ** sqlite3DbMalloc(). Except, entries in the copy of the string associated
  2980         -** with equality constraints that use NONE affinity are set to
  2981         -** SQLITE_AFF_NONE. This is to deal with SQL such as the following:
         2980  +** with equality constraints that use BLOB or NONE affinity are set to
         2981  +** SQLITE_AFF_BLOB. This is to deal with SQL such as the following:
  2982   2982   **
  2983   2983   **   CREATE TABLE t1(a TEXT PRIMARY KEY, b);
  2984   2984   **   SELECT ... FROM t1 AS t2, t1 WHERE t1.a = t2.b;
  2985   2985   **
  2986   2986   ** In the example above, the index on t1(a) has TEXT affinity. But since
  2987         -** the right hand side of the equality constraint (t2.b) has NONE affinity,
         2987  +** the right hand side of the equality constraint (t2.b) has BLOB/NONE affinity,
  2988   2988   ** no conversion should be attempted before using a t2.b value as part of
  2989   2989   ** a key to search the index. Hence the first byte in the returned affinity
  2990         -** string in this example would be set to SQLITE_AFF_NONE.
         2990  +** string in this example would be set to SQLITE_AFF_BLOB.
  2991   2991   */
  2992   2992   static int codeAllEqualityTerms(
  2993   2993     Parse *pParse,        /* Parsing context */
  2994   2994     WhereLevel *pLevel,   /* Which nested loop of the FROM we are coding */
  2995   2995     int bRev,             /* Reverse the order of IN operators */
  2996   2996     int nExtraReg,        /* Number of extra registers to allocate */
  2997   2997     char **pzAff          /* OUT: Set to point to affinity string */
................................................................................
  3070   3070       if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
  3071   3071         Expr *pRight = pTerm->pExpr->pRight;
  3072   3072         if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){
  3073   3073           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
  3074   3074           VdbeCoverage(v);
  3075   3075         }
  3076   3076         if( zAff ){
  3077         -        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_NONE ){
  3078         -          zAff[j] = SQLITE_AFF_NONE;
         3077  +        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_BLOB ){
         3078  +          zAff[j] = SQLITE_AFF_BLOB;
  3079   3079           }
  3080   3080           if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[j]) ){
  3081         -          zAff[j] = SQLITE_AFF_NONE;
         3081  +          zAff[j] = SQLITE_AFF_BLOB;
  3082   3082           }
  3083   3083         }
  3084   3084       }
  3085   3085     }
  3086   3086     *pzAff = zAff;
  3087   3087     return regBase;
  3088   3088   }
................................................................................
  3721   3721         if( (pRangeStart->wtFlags & TERM_VNULL)==0
  3722   3722          && sqlite3ExprCanBeNull(pRight)
  3723   3723         ){
  3724   3724           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3725   3725           VdbeCoverage(v);
  3726   3726         }
  3727   3727         if( zStartAff ){
  3728         -        if( sqlite3CompareAffinity(pRight, zStartAff[nEq])==SQLITE_AFF_NONE){
         3728  +        if( sqlite3CompareAffinity(pRight, zStartAff[nEq])==SQLITE_AFF_BLOB){
  3729   3729             /* Since the comparison is to be performed with no conversions
  3730   3730             ** applied to the operands, set the affinity to apply to pRight to 
  3731         -          ** SQLITE_AFF_NONE.  */
  3732         -          zStartAff[nEq] = SQLITE_AFF_NONE;
         3731  +          ** SQLITE_AFF_BLOB.  */
         3732  +          zStartAff[nEq] = SQLITE_AFF_BLOB;
  3733   3733           }
  3734   3734           if( sqlite3ExprNeedsNoAffinityChange(pRight, zStartAff[nEq]) ){
  3735         -          zStartAff[nEq] = SQLITE_AFF_NONE;
         3735  +          zStartAff[nEq] = SQLITE_AFF_BLOB;
  3736   3736           }
  3737   3737         }  
  3738   3738         nConstraint++;
  3739   3739         testcase( pRangeStart->wtFlags & TERM_VIRTUAL );
  3740   3740       }else if( bSeekPastNull ){
  3741   3741         sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
  3742   3742         nConstraint++;
................................................................................
  3766   3766         whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
  3767   3767         if( (pRangeEnd->wtFlags & TERM_VNULL)==0
  3768   3768          && sqlite3ExprCanBeNull(pRight)
  3769   3769         ){
  3770   3770           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3771   3771           VdbeCoverage(v);
  3772   3772         }
  3773         -      if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE
         3773  +      if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_BLOB
  3774   3774          && !sqlite3ExprNeedsNoAffinityChange(pRight, cEndAff)
  3775   3775         ){
  3776   3776           codeApplyAffinity(pParse, regBase+nEq, 1, &cEndAff);
  3777   3777         }
  3778   3778         nConstraint++;
  3779   3779         testcase( pRangeEnd->wtFlags & TERM_VIRTUAL );
  3780   3780       }else if( bStopAtNull ){