/ Check-in [91d8a8d0]
Login

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

Overview
Comment:Allow CAST expressions and unary "+" operators to be used in the DEFAULT argument of an ALTER TABLE ADD COLUMN and to be understand on the RHS of range constraints interpreted by STAT3/4. This involves a rewrite of the implementation of the CAST operator.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 91d8a8d0b792ea5c4fe68fd9caaf3345eddea486
User & Date: drh 2014-08-25 20:11:52
Context
2014-08-25
20:21
Test cases added for using unary "+" and CAST operators on the RHS of range constraints and verifying that STAT3/4 can use those constraints. check-in: 42505e5a user: drh tags: trunk
20:11
Allow CAST expressions and unary "+" operators to be used in the DEFAULT argument of an ALTER TABLE ADD COLUMN and to be understand on the RHS of range constraints interpreted by STAT3/4. This involves a rewrite of the implementation of the CAST operator. check-in: 91d8a8d0 user: drh tags: trunk
18:29
In cases where stat4 data is available but cannot be used because the rhs of a range constraint is too complex a expression, fall back to using the default estimates for number of rows scanned. check-in: e06dc6f0 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2591   2591       case TK_AS: {
  2592   2592         inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
  2593   2593         break;
  2594   2594       }
  2595   2595   #ifndef SQLITE_OMIT_CAST
  2596   2596       case TK_CAST: {
  2597   2597         /* Expressions of the form:   CAST(pLeft AS token) */
  2598         -      int aff, to_op;
  2599   2598         inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
  2600         -      assert( !ExprHasProperty(pExpr, EP_IntValue) );
  2601         -      aff = sqlite3AffinityType(pExpr->u.zToken, 0);
  2602         -      to_op = aff - SQLITE_AFF_TEXT + OP_ToText;
  2603         -      assert( to_op==OP_ToText    || aff!=SQLITE_AFF_TEXT    );
  2604         -      assert( to_op==OP_ToBlob    || aff!=SQLITE_AFF_NONE    );
  2605         -      assert( to_op==OP_ToNumeric || aff!=SQLITE_AFF_NUMERIC );
  2606         -      assert( to_op==OP_ToInt     || aff!=SQLITE_AFF_INTEGER );
  2607         -      assert( to_op==OP_ToReal    || aff!=SQLITE_AFF_REAL    );
  2608         -      testcase( to_op==OP_ToText );
  2609         -      testcase( to_op==OP_ToBlob );
  2610         -      testcase( to_op==OP_ToNumeric );
  2611         -      testcase( to_op==OP_ToInt );
  2612         -      testcase( to_op==OP_ToReal );
  2613   2599         if( inReg!=target ){
  2614   2600           sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
  2615   2601           inReg = target;
  2616   2602         }
  2617         -      sqlite3VdbeAddOp1(v, to_op, inReg);
         2603  +      sqlite3VdbeAddOp2(v, OP_Cast, target,
         2604  +                        sqlite3AffinityType(pExpr->u.zToken, 0));
  2618   2605         testcase( usedAsColumnCache(pParse, inReg, inReg) );
  2619   2606         sqlite3ExprCacheAffinityChange(pParse, inReg, 1);
  2620   2607         break;
  2621   2608       }
  2622   2609   #endif /* SQLITE_OMIT_CAST */
  2623   2610       case TK_LT:
  2624   2611       case TK_LE:

Changes to src/vdbe.c.

  1764   1764       sqlite3VdbeMemRealify(pIn1);
  1765   1765     }
  1766   1766     break;
  1767   1767   }
  1768   1768   #endif
  1769   1769   
  1770   1770   #ifndef SQLITE_OMIT_CAST
  1771         -/* Opcode: ToText P1 * * * *
         1771  +/* Opcode: Cast P1 P2 * * *
  1772   1772   **
  1773         -** Force the value in register P1 to be text.
  1774         -** If the value is numeric, convert it to a string using the
  1775         -** equivalent of sprintf().  Blob values are unchanged and
  1776         -** are afterwards simply interpreted as text.
         1773  +** Force the value in register P1 to be the type defined by P2.
         1774  +** 
         1775  +** <ul>
         1776  +** <li value="97"> TEXT
         1777  +** <li value="98"> BLOB
         1778  +** <li value="99"> NUMERIC
         1779  +** <li value="100"> INTEGER
         1780  +** <li value="101"> REAL
         1781  +** </ul>
  1777   1782   **
  1778   1783   ** A NULL value is not changed by this routine.  It remains NULL.
  1779   1784   */
  1780         -case OP_ToText: {                  /* same as TK_TO_TEXT, in1 */
         1785  +case OP_Cast: {                  /* in1 */
  1781   1786     pIn1 = &aMem[pOp->p1];
  1782   1787     memAboutToChange(p, pIn1);
  1783         -  if( pIn1->flags & MEM_Null ) break;
  1784         -  assert( MEM_Str==(MEM_Blob>>3) );
  1785         -  pIn1->flags |= (pIn1->flags&MEM_Blob)>>3;
  1786         -  applyAffinity(pIn1, SQLITE_AFF_TEXT, encoding);
  1787   1788     rc = ExpandBlob(pIn1);
  1788         -  assert( pIn1->flags & MEM_Str || db->mallocFailed );
  1789         -  pIn1->flags &= ~(MEM_Int|MEM_Real|MEM_Blob|MEM_Zero);
         1789  +  sqlite3VdbeMemCast(pIn1, pOp->p2, encoding);
  1790   1790     UPDATE_MAX_BLOBSIZE(pIn1);
  1791   1791     break;
  1792   1792   }
  1793         -
  1794         -/* Opcode: ToBlob P1 * * * *
  1795         -**
  1796         -** Force the value in register P1 to be a BLOB.
  1797         -** If the value is numeric, convert it to a string first.
  1798         -** Strings are simply reinterpreted as blobs with no change
  1799         -** to the underlying data.
  1800         -**
  1801         -** A NULL value is not changed by this routine.  It remains NULL.
  1802         -*/
  1803         -case OP_ToBlob: {                  /* same as TK_TO_BLOB, in1 */
  1804         -  pIn1 = &aMem[pOp->p1];
  1805         -  if( pIn1->flags & MEM_Null ) break;
  1806         -  if( (pIn1->flags & MEM_Blob)==0 ){
  1807         -    applyAffinity(pIn1, SQLITE_AFF_TEXT, encoding);
  1808         -    assert( pIn1->flags & MEM_Str || db->mallocFailed );
  1809         -    MemSetTypeFlag(pIn1, MEM_Blob);
  1810         -  }else{
  1811         -    pIn1->flags &= ~(MEM_TypeMask&~MEM_Blob);
  1812         -  }
  1813         -  UPDATE_MAX_BLOBSIZE(pIn1);
  1814         -  break;
  1815         -}
  1816         -
  1817         -/* Opcode: ToNumeric P1 * * * *
  1818         -**
  1819         -** Force the value in register P1 to be numeric (either an
  1820         -** integer or a floating-point number.)
  1821         -** If the value is text or blob, try to convert it to an using the
  1822         -** equivalent of atoi() or atof() and store 0 if no such conversion 
  1823         -** is possible.
  1824         -**
  1825         -** A NULL value is not changed by this routine.  It remains NULL.
  1826         -*/
  1827         -case OP_ToNumeric: {                  /* same as TK_TO_NUMERIC, in1 */
  1828         -  pIn1 = &aMem[pOp->p1];
  1829         -  sqlite3VdbeMemNumerify(pIn1);
  1830         -  break;
  1831         -}
  1832   1793   #endif /* SQLITE_OMIT_CAST */
  1833   1794   
  1834         -/* Opcode: ToInt P1 * * * *
  1835         -**
  1836         -** Force the value in register P1 to be an integer.  If
  1837         -** The value is currently a real number, drop its fractional part.
  1838         -** If the value is text or blob, try to convert it to an integer using the
  1839         -** equivalent of atoi() and store 0 if no such conversion is possible.
  1840         -**
  1841         -** A NULL value is not changed by this routine.  It remains NULL.
  1842         -*/
  1843         -case OP_ToInt: {                  /* same as TK_TO_INT, in1 */
  1844         -  pIn1 = &aMem[pOp->p1];
  1845         -  if( (pIn1->flags & MEM_Null)==0 ){
  1846         -    sqlite3VdbeMemIntegerify(pIn1);
  1847         -  }
  1848         -  break;
  1849         -}
  1850         -
  1851         -#if !defined(SQLITE_OMIT_CAST) && !defined(SQLITE_OMIT_FLOATING_POINT)
  1852         -/* Opcode: ToReal P1 * * * *
  1853         -**
  1854         -** Force the value in register P1 to be a floating point number.
  1855         -** If The value is currently an integer, convert it.
  1856         -** If the value is text or blob, try to convert it to an integer using the
  1857         -** equivalent of atoi() and store 0.0 if no such conversion is possible.
  1858         -**
  1859         -** A NULL value is not changed by this routine.  It remains NULL.
  1860         -*/
  1861         -case OP_ToReal: {                  /* same as TK_TO_REAL, in1 */
  1862         -  pIn1 = &aMem[pOp->p1];
  1863         -  memAboutToChange(p, pIn1);
  1864         -  if( (pIn1->flags & MEM_Null)==0 ){
  1865         -    sqlite3VdbeMemRealify(pIn1);
  1866         -  }
  1867         -  break;
  1868         -}
  1869         -#endif /* !defined(SQLITE_OMIT_CAST) && !defined(SQLITE_OMIT_FLOATING_POINT) */
  1870         -
  1871   1795   /* Opcode: Lt P1 P2 P3 P4 P5
  1872   1796   ** Synopsis: if r[P1]<r[P3] goto P2
  1873   1797   **
  1874   1798   ** Compare the values in register P1 and P3.  If reg(P3)<reg(P1) then
  1875   1799   ** jump to address P2.  
  1876   1800   **
  1877   1801   ** If the SQLITE_JUMPIFNULL bit of P5 is set and either reg(P1) or

Changes to src/vdbeInt.h.

   421    421   int sqlite3VdbeMemStringify(Mem*, u8, u8);
   422    422   i64 sqlite3VdbeIntValue(Mem*);
   423    423   int sqlite3VdbeMemIntegerify(Mem*);
   424    424   double sqlite3VdbeRealValue(Mem*);
   425    425   void sqlite3VdbeIntegerAffinity(Mem*);
   426    426   int sqlite3VdbeMemRealify(Mem*);
   427    427   int sqlite3VdbeMemNumerify(Mem*);
          428  +void sqlite3VdbeMemCast(Mem*,u8,u8);
   428    429   int sqlite3VdbeMemFromBtree(BtCursor*,u32,u32,int,Mem*);
   429    430   void sqlite3VdbeMemRelease(Mem *p);
   430    431   void sqlite3VdbeMemReleaseExternal(Mem *p);
   431    432   #define VdbeMemDynamic(X)  \
   432    433     (((X)->flags&(MEM_Agg|MEM_Dyn|MEM_RowSet|MEM_Frame))!=0)
   433    434   #define VdbeMemReleaseExtern(X)  \
   434    435     if( VdbeMemDynamic(X) ) sqlite3VdbeMemReleaseExternal(X);

Changes to src/vdbemem.c.

   517    517         sqlite3VdbeIntegerAffinity(pMem);
   518    518       }
   519    519     }
   520    520     assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))!=0 );
   521    521     pMem->flags &= ~(MEM_Str|MEM_Blob);
   522    522     return SQLITE_OK;
   523    523   }
          524  +
          525  +/*
          526  +** Cast the datatype of the value in pMem according to the affinity
          527  +** "aff".  Casting is different from applying affinity in that a cast
          528  +** is forced.  In other words, the value is converted into the desired
          529  +** affinity even if that results in loss of data.  This routine is
          530  +** used (for example) to implement the SQL "cast()" operator.
          531  +*/
          532  +void sqlite3VdbeMemCast(Mem *pMem, u8 aff, u8 encoding){
          533  +  if( pMem->flags & MEM_Null ) return;
          534  +  switch( aff ){
          535  +    case SQLITE_AFF_NONE: {   /* Really a cast to BLOB */
          536  +      if( (pMem->flags & MEM_Blob)==0 ){
          537  +        sqlite3ValueApplyAffinity(pMem, SQLITE_AFF_TEXT, encoding);
          538  +        assert( pMem->flags & MEM_Str || pMem->db->mallocFailed );
          539  +        MemSetTypeFlag(pMem, MEM_Blob);
          540  +      }else{
          541  +        pMem->flags &= ~(MEM_TypeMask&~MEM_Blob);
          542  +      }
          543  +      break;
          544  +    }
          545  +    case SQLITE_AFF_NUMERIC: {
          546  +      sqlite3VdbeMemNumerify(pMem);
          547  +      break;
          548  +    }
          549  +    case SQLITE_AFF_INTEGER: {
          550  +      sqlite3VdbeMemIntegerify(pMem);
          551  +      break;
          552  +    }
          553  +    case SQLITE_AFF_REAL: {
          554  +      sqlite3VdbeMemRealify(pMem);
          555  +      break;
          556  +    }
          557  +    default: {
          558  +      assert( aff==SQLITE_AFF_TEXT );
          559  +      assert( MEM_Str==(MEM_Blob>>3) );
          560  +      pMem->flags |= (pMem->flags&MEM_Blob)>>3;
          561  +      sqlite3ValueApplyAffinity(pMem, SQLITE_AFF_TEXT, encoding);
          562  +      assert( pMem->flags & MEM_Str || pMem->db->mallocFailed );
          563  +      pMem->flags &= ~(MEM_Int|MEM_Real|MEM_Blob|MEM_Zero);
          564  +      break;
          565  +    }
          566  +  }
          567  +}
          568  +
   524    569   
   525    570   /*
   526    571   ** Delete any previous value and set the value stored in *pMem to NULL.
   527    572   */
   528    573   void sqlite3VdbeMemSetNull(Mem *pMem){
   529    574     if( pMem->flags & MEM_Frame ){
   530    575       VdbeFrame *pFrame = pMem->u.pFrame;
................................................................................
  1011   1056     const char *zNeg = "";
  1012   1057     int rc = SQLITE_OK;
  1013   1058   
  1014   1059     if( !pExpr ){
  1015   1060       *ppVal = 0;
  1016   1061       return SQLITE_OK;
  1017   1062     }
  1018         -  op = pExpr->op;
         1063  +  while( (op = pExpr->op)==TK_UPLUS ) pExpr = pExpr->pLeft;
  1019   1064     if( NEVER(op==TK_REGISTER) ) op = pExpr->op2;
         1065  +
         1066  +  if( op==TK_CAST ){
         1067  +    u8 aff = sqlite3AffinityType(pExpr->u.zToken,0);
         1068  +    rc = valueFromExpr(db, pExpr->pLeft, enc, aff, ppVal, pCtx);
         1069  +    if( rc==SQLITE_OK && *ppVal ){
         1070  +      sqlite3VdbeMemCast(*ppVal, aff, SQLITE_UTF8);
         1071  +      sqlite3ValueApplyAffinity(*ppVal, affinity, SQLITE_UTF8);
         1072  +    }
         1073  +    return rc;
         1074  +  }
  1020   1075   
  1021   1076     /* Handle negative integers in a single step.  This is needed in the
  1022   1077     ** case when the value is -9223372036854775808.
  1023   1078     */
  1024   1079     if( op==TK_UMINUS
  1025   1080      && (pExpr->pLeft->op==TK_INTEGER || pExpr->pLeft->op==TK_FLOAT) ){
  1026   1081       pExpr = pExpr->pLeft;

Changes to test/alter4.test.

   141    141   do_test alter4-2.6 {
   142    142     catchsql {
   143    143       alter table t1 add column d DEFAULT CURRENT_TIME;
   144    144     }
   145    145   } {1 {Cannot add a column with non-constant default}}
   146    146   do_test alter4-2.7 {
   147    147     catchsql {
   148         -    alter table t1 add column d default (-+1);
          148  +    alter table t1 add column d default (-5+1);
   149    149     }
   150    150   } {1 {Cannot add a column with non-constant default}}
   151    151   do_test alter4-2.99 {
   152    152     execsql {
   153    153       DROP TABLE t1;
   154    154     }
   155    155   } {}