/ Check-in [67a68af5]
Login

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

Overview
Comment:When casting string values into numeric and the string has a prefix that looks like a number but total string is not a well-formed number, then take extra care that the result is either integer or real depending on what the prefix looks like. Fix for tickets [e8bedb2a184001] and [4c2d7639f076aa].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 67a68af5578f08d2be2e48cf4fd12a6cf35a09c47d259deda81950f7ee1f02f7
User & Date: drh 2019-06-07 22:26:08
References
2019-06-08
21:31 New ticket [dd6bffbf] CAST to NUMERIC no longer converts to INTEGER. artifact: f550c0f0 user: mrigger
Context
2019-06-07
22:51
Remove code in the round() SQL function that became unreachable due to the optimization of check-in [e95138f5f4febde5] check-in: b141bae3 user: drh tags: trunk
22:26
When casting string values into numeric and the string has a prefix that looks like a number but total string is not a well-formed number, then take extra care that the result is either integer or real depending on what the prefix looks like. Fix for tickets [e8bedb2a184001] and [4c2d7639f076aa]. check-in: 67a68af5 user: drh tags: trunk
18:56
Also upgrade script config.sub to the latest version. This should have been part of the previous commit. check-in: efbf31b8 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/date.c.

   384    384     double r;
   385    385     if( parseYyyyMmDd(zDate,p)==0 ){
   386    386       return 0;
   387    387     }else if( parseHhMmSs(zDate, p)==0 ){
   388    388       return 0;
   389    389     }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
   390    390       return setDateTimeToCurrent(context, p);
   391         -  }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
          391  +  }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){
   392    392       setRawDateNumber(p, r);
   393    393       return 0;
   394    394     }
   395    395     return 1;
   396    396   }
   397    397   
   398    398   /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.
................................................................................
   718    718         **    weekday N
   719    719         **
   720    720         ** Move the date to the same time on the next occurrence of
   721    721         ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
   722    722         ** date is already on the appropriate weekday, this is a no-op.
   723    723         */
   724    724         if( sqlite3_strnicmp(z, "weekday ", 8)==0
   725         -               && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)
          725  +               && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0
   726    726                  && (n=(int)r)==r && n>=0 && r<7 ){
   727    727           sqlite3_int64 Z;
   728    728           computeYMD_HMS(p);
   729    729           p->validTZ = 0;
   730    730           p->validJD = 0;
   731    731           computeJD(p);
   732    732           Z = ((p->iJD + 129600000)/86400000) % 7;
................................................................................
   777    777       case '6':
   778    778       case '7':
   779    779       case '8':
   780    780       case '9': {
   781    781         double rRounder;
   782    782         int i;
   783    783         for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){}
   784         -      if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){
          784  +      if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){
   785    785           rc = 1;
   786    786           break;
   787    787         }
   788    788         if( z[n]==':' ){
   789    789           /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
   790    790           ** specified number of hours, minutes, seconds, and fractional seconds
   791    791           ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be

Changes to src/func.c.

   393    393       r = (double)((sqlite_int64)(r+(r<0?-0.5:+0.5)));
   394    394     }else{
   395    395       zBuf = sqlite3_mprintf("%.*f",n,r);
   396    396       if( zBuf==0 ){
   397    397         sqlite3_result_error_nomem(context);
   398    398         return;
   399    399       }
   400         -    if( !sqlite3AtoF(zBuf, &r, sqlite3Strlen30(zBuf), SQLITE_UTF8) ){
          400  +    if( sqlite3AtoF(zBuf, &r, sqlite3Strlen30(zBuf), SQLITE_UTF8)<=0 ){
   401    401         assert( sqlite3_strglob("*Inf", zBuf)==0 );
   402    402         r = zBuf[0]=='-' ? -HUGE_VAL : +HUGE_VAL;
   403    403       } 
   404    404       sqlite3_free(zBuf);
   405    405     }
   406    406     sqlite3_result_double(context, r);
   407    407   }

Changes to src/util.c.

   361    361   ** uses the encoding enc.  The string is not necessarily zero-terminated.
   362    362   **
   363    363   ** Return TRUE if the result is a valid real number (or integer) and FALSE
   364    364   ** if the string is empty or contains extraneous text.  More specifically
   365    365   ** return
   366    366   **      1          =>  The input string is a pure integer
   367    367   **      2 or more  =>  The input has a decimal point or eNNN clause
   368         -**      0          =>  The input string is not a valid number
          368  +**      0 or less  =>  The input string is not a valid number
          369  +**     -1          =>  Not a valid number, but has a valid prefix which 
          370  +**                     includes a decimal point and/or an eNNN clause
   369    371   **
   370    372   ** Valid numbers are in one of these formats:
   371    373   **
   372    374   **    [+-]digits[E[+-]digits]
   373    375   **    [+-]digits.[digits][E[+-]digits]
   374    376   **    [+-].digits[E[+-]digits]
   375    377   **
................................................................................
   552    554       }
   553    555     }
   554    556   
   555    557     /* store the result */
   556    558     *pResult = result;
   557    559   
   558    560     /* return true if number and no extra non-whitespace chracters after */
   559         -  return z==zEnd && nDigit>0 && eValid && eType>0 ? eType : 0;
          561  +  if( z==zEnd && nDigit>0 && eValid && eType>0 ){
          562  +    return eType;
          563  +  }else if( eType>=2 && (eType==3 || eValid) ){
          564  +    return -1;
          565  +  }else{
          566  +    return 0;
          567  +  }
   560    568   #else
   561    569     return !sqlite3Atoi64(z, pResult, length, enc);
   562    570   #endif /* SQLITE_OMIT_FLOATING_POINT */
   563    571   }
   564    572   
   565    573   /*
   566    574   ** Compare the 19-character string zNum against the text representation
................................................................................
   595    603   
   596    604   /*
   597    605   ** Convert zNum to a 64-bit signed integer.  zNum must be decimal. This
   598    606   ** routine does *not* accept hexadecimal notation.
   599    607   **
   600    608   ** Returns:
   601    609   **
          610  +**    -1    Not even a prefix of the input text looks like an integer
   602    611   **     0    Successful transformation.  Fits in a 64-bit signed integer.
   603    612   **     1    Excess non-space text after the integer value
   604    613   **     2    Integer too large for a 64-bit signed integer or is malformed
   605    614   **     3    Special case of 9223372036854775808
   606    615   **
   607    616   ** length is the number of bytes in the string (bytes, not characters).
   608    617   ** The string is not necessarily zero-terminated.  The encoding is
................................................................................
   654    663       *pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
   655    664     }else if( neg ){
   656    665       *pNum = -(i64)u;
   657    666     }else{
   658    667       *pNum = (i64)u;
   659    668     }
   660    669     rc = 0;
   661         -  if( (i==0 && zStart==zNum)     /* No digits */
   662         -   || nonNum                     /* UTF16 with high-order bytes non-zero */
   663         -  ){
          670  +  if( i==0 && zStart==zNum ){    /* No digits */
          671  +    rc = -1;
          672  +  }else if( nonNum ){            /* UTF16 with high-order bytes non-zero */
   664    673       rc = 1;
   665    674     }else if( &zNum[i]<zEnd ){     /* Extra bytes at the end */
   666    675       int jj = i;
   667    676       do{
   668    677         if( !sqlite3Isspace(zNum[jj]) ){
   669    678           rc = 1;          /* Extra non-space text after the integer */
   670    679           break;

Changes to src/vdbe.c.

   317    317   */
   318    318   static void applyNumericAffinity(Mem *pRec, int bTryForInt){
   319    319     double rValue;
   320    320     u8 enc = pRec->enc;
   321    321     int rc;
   322    322     assert( (pRec->flags & (MEM_Str|MEM_Int|MEM_Real|MEM_IntReal))==MEM_Str );
   323    323     rc = sqlite3AtoF(pRec->z, &rValue, pRec->n, enc);
   324         -  if( rc==0 ) return;
          324  +  if( rc<=0 ) return;
   325    325     if( rc==1 && alsoAnInt(pRec, rValue, &pRec->u.i) ){
   326    326       pRec->flags |= MEM_Int;
   327    327     }else{
   328    328       pRec->u.r = rValue;
   329    329       pRec->flags |= MEM_Real;
   330    330       if( bTryForInt ) sqlite3VdbeIntegerAffinity(pRec);
   331    331     }
................................................................................
   418    418   /*
   419    419   ** pMem currently only holds a string type (or maybe a BLOB that we can
   420    420   ** interpret as a string if we want to).  Compute its corresponding
   421    421   ** numeric type, if has one.  Set the pMem->u.r and pMem->u.i fields
   422    422   ** accordingly.
   423    423   */
   424    424   static u16 SQLITE_NOINLINE computeNumericType(Mem *pMem){
          425  +  int rc;
          426  +  sqlite3_int64 ix;
   425    427     assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_IntReal))==0 );
   426    428     assert( (pMem->flags & (MEM_Str|MEM_Blob))!=0 );
   427    429     ExpandBlob(pMem);
   428         -  if( sqlite3AtoF(pMem->z, &pMem->u.r, pMem->n, pMem->enc)==0 ){
   429         -    return 0;
   430         -  }
   431         -  if( sqlite3Atoi64(pMem->z, &pMem->u.i, pMem->n, pMem->enc)==0 ){
          430  +  rc = sqlite3AtoF(pMem->z, &pMem->u.r, pMem->n, pMem->enc);
          431  +  if( rc<=0 ){
          432  +    if( rc==0 && sqlite3Atoi64(pMem->z, &ix, pMem->n, pMem->enc)<=1 ){
          433  +      pMem->u.i = ix;
          434  +      return MEM_Int;
          435  +    }else{
          436  +      return MEM_Real;
          437  +    }
          438  +  }else if( rc==1 && sqlite3Atoi64(pMem->z, &ix, pMem->n, pMem->enc)==0 ){
          439  +    pMem->u.i = ix;
   432    440       return MEM_Int;
   433    441     }
   434    442     return MEM_Real;
   435    443   }
   436    444   
   437    445   /*
   438    446   ** Return the numeric type for pMem, either MEM_Int or MEM_Real or both or
................................................................................
  1586   1594   ** If either operand is NULL, the result is NULL.
  1587   1595   */
  1588   1596   case OP_Add:                   /* same as TK_PLUS, in1, in2, out3 */
  1589   1597   case OP_Subtract:              /* same as TK_MINUS, in1, in2, out3 */
  1590   1598   case OP_Multiply:              /* same as TK_STAR, in1, in2, out3 */
  1591   1599   case OP_Divide:                /* same as TK_SLASH, in1, in2, out3 */
  1592   1600   case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
  1593         -  char bIntint;   /* Started out as two integer operands */
  1594   1601     u16 flags;      /* Combined MEM_* flags from both inputs */
  1595   1602     u16 type1;      /* Numeric type of left operand */
  1596   1603     u16 type2;      /* Numeric type of right operand */
  1597   1604     i64 iA;         /* Integer value of left operand */
  1598   1605     i64 iB;         /* Integer value of right operand */
  1599   1606     double rA;      /* Real value of left operand */
  1600   1607     double rB;      /* Real value of right operand */
................................................................................
  1604   1611     pIn2 = &aMem[pOp->p2];
  1605   1612     type2 = numericType(pIn2);
  1606   1613     pOut = &aMem[pOp->p3];
  1607   1614     flags = pIn1->flags | pIn2->flags;
  1608   1615     if( (type1 & type2 & MEM_Int)!=0 ){
  1609   1616       iA = pIn1->u.i;
  1610   1617       iB = pIn2->u.i;
  1611         -    bIntint = 1;
  1612   1618       switch( pOp->opcode ){
  1613   1619         case OP_Add:       if( sqlite3AddInt64(&iB,iA) ) goto fp_math;  break;
  1614   1620         case OP_Subtract:  if( sqlite3SubInt64(&iB,iA) ) goto fp_math;  break;
  1615   1621         case OP_Multiply:  if( sqlite3MulInt64(&iB,iA) ) goto fp_math;  break;
  1616   1622         case OP_Divide: {
  1617   1623           if( iA==0 ) goto arithmetic_result_is_null;
  1618   1624           if( iA==-1 && iB==SMALLEST_INT64 ) goto fp_math;
................................................................................
  1627   1633         }
  1628   1634       }
  1629   1635       pOut->u.i = iB;
  1630   1636       MemSetTypeFlag(pOut, MEM_Int);
  1631   1637     }else if( (flags & MEM_Null)!=0 ){
  1632   1638       goto arithmetic_result_is_null;
  1633   1639     }else{
  1634         -    bIntint = 0;
  1635   1640   fp_math:
  1636   1641       rA = sqlite3VdbeRealValue(pIn1);
  1637   1642       rB = sqlite3VdbeRealValue(pIn2);
  1638   1643       switch( pOp->opcode ){
  1639   1644         case OP_Add:         rB += rA;       break;
  1640   1645         case OP_Subtract:    rB -= rA;       break;
  1641   1646         case OP_Multiply:    rB *= rA;       break;
................................................................................
  1659   1664       MemSetTypeFlag(pOut, MEM_Int);
  1660   1665   #else
  1661   1666       if( sqlite3IsNaN(rB) ){
  1662   1667         goto arithmetic_result_is_null;
  1663   1668       }
  1664   1669       pOut->u.r = rB;
  1665   1670       MemSetTypeFlag(pOut, MEM_Real);
  1666         -    if( ((type1|type2)&(MEM_Real|MEM_IntReal))==0 && !bIntint ){
  1667         -      sqlite3VdbeIntegerAffinity(pOut);
  1668         -    }
  1669   1671   #endif
  1670   1672     }
  1671   1673     break;
  1672   1674   
  1673   1675   arithmetic_result_is_null:
  1674   1676     sqlite3VdbeMemSetNull(pOut);
  1675   1677     break;

Changes to src/vdbemem.c.

   713    713   int sqlite3VdbeMemNumerify(Mem *pMem){
   714    714     testcase( pMem->flags & MEM_Int );
   715    715     testcase( pMem->flags & MEM_Real );
   716    716     testcase( pMem->flags & MEM_IntReal );
   717    717     testcase( pMem->flags & MEM_Null );
   718    718     if( (pMem->flags & (MEM_Int|MEM_Real|MEM_IntReal|MEM_Null))==0 ){
   719    719       int rc;
          720  +    sqlite3_int64 ix;
   720    721       assert( (pMem->flags & (MEM_Blob|MEM_Str))!=0 );
   721    722       assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) );
   722         -    rc = sqlite3Atoi64(pMem->z, &pMem->u.i, pMem->n, pMem->enc);
   723         -    if( rc==0 ){
   724         -      MemSetTypeFlag(pMem, MEM_Int);
   725         -    }else{
   726         -      i64 i = pMem->u.i;
   727         -      sqlite3AtoF(pMem->z, &pMem->u.r, pMem->n, pMem->enc);
   728         -      if( rc==1 && sqlite3RealSameAsInt(pMem->u.r, i) ){
   729         -        pMem->u.i = i;
          723  +    rc = sqlite3AtoF(pMem->z, &pMem->u.r, pMem->n, pMem->enc);
          724  +    if( rc<=0 ){
          725  +      if( rc==0 && sqlite3Atoi64(pMem->z, &ix, pMem->n, pMem->enc)<=1 ){
          726  +        pMem->u.i = ix;
   730    727           MemSetTypeFlag(pMem, MEM_Int);
   731    728         }else{
   732    729           MemSetTypeFlag(pMem, MEM_Real);
   733    730         }
          731  +    }else if( rc==1 && sqlite3Atoi64(pMem->z, &ix, pMem->n, pMem->enc)==0 ){
          732  +      pMem->u.i = ix;
          733  +      MemSetTypeFlag(pMem, MEM_Int);
          734  +    }else{
          735  +      MemSetTypeFlag(pMem, MEM_Real);
   734    736       }
   735    737     }
   736    738     assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_IntReal|MEM_Null))!=0 );
   737    739     pMem->flags &= ~(MEM_Str|MEM_Blob|MEM_Zero);
   738    740     return SQLITE_OK;
   739    741   }
   740    742   

Changes to test/cast.test.

   383    383     INSERT INTO t1 VALUES
   384    384        ('9000000000000000001'),
   385    385        ('9000000000000000001 '),
   386    386        (' 9000000000000000001'),
   387    387        (' 9000000000000000001 ');
   388    388     SELECT * FROM t1;
   389    389   } {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001}
          390  +
          391  +# 2019-06-07
          392  +# https://www.sqlite.org/src/info/4c2d7639f076aa7c
          393  +do_execsql_test case-7.1 {
          394  +  SELECT CAST('-' AS NUMERIC);
          395  +} {0}
          396  +do_execsql_test case-7.2 {
          397  +  SELECT CAST('-0' AS NUMERIC);
          398  +} {0}
          399  +do_execsql_test case-7.3 {
          400  +  SELECT CAST('+' AS NUMERIC);
          401  +} {0}
          402  +do_execsql_test case-7.4 {
          403  +  SELECT CAST('/' AS NUMERIC);
          404  +} {0}
          405  +
          406  +# 2019-06-07
          407  +# https://www.sqlite.org/src/info/e8bedb2a184001bb
          408  +do_execsql_test case-7.10 {
          409  +  SELECT '' - 2851427734582196970;
          410  +} {-2851427734582196970}
          411  +do_execsql_test case-7.11 {
          412  +  SELECT 0 - 2851427734582196970;
          413  +} {-2851427734582196970}
          414  +do_execsql_test case-7.12 {
          415  +  SELECT '' - 1;
          416  +} {-1}
   390    417   
   391    418   finish_test

Changes to test/e_expr.test.

  1646   1646   
  1647   1647   # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
  1648   1648   # first does a forced conversion into REAL but then further converts the
  1649   1649   # result into INTEGER if and only if the conversion from REAL to INTEGER
  1650   1650   # is lossless and reversible.
  1651   1651   #
  1652   1652   do_expr_test e_expr-32.1.1 { CAST('45'   AS NUMERIC)  } integer 45
  1653         -do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC)  } integer 45
         1653  +do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC)  } real 45.0
  1654   1654   do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC)  } real 45.2
  1655   1655   do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
  1656   1656   do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
  1657   1657   
  1658   1658   # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
  1659   1659   # is a no-op, even if a real value could be losslessly converted to an
  1660   1660   # integer.
................................................................................
  1697   1697     SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1;
  1698   1698   } [list \
  1699   1699    integer 9000000000000000001 \
  1700   1700    integer 9000000000000000001 \
  1701   1701    integer 9000000000000000001 \
  1702   1702    integer 9000000000000000001 \
  1703   1703    integer 9000000000000000001 \
  1704         - integer 9000000000000000001 \
         1704  + real 9.0e+18 \
  1705   1705    integer 9223372036854775807 \
  1706   1706    integer 9223372036854775807 \
  1707   1707    integer 9223372036854775807 \
  1708   1708    real 9.22337203685478e+18 \
  1709   1709    real 9.22337203685478e+18 \
  1710         - integer 9223372036854775807 \
  1711         - integer 9223372036854775807 \
  1712         - integer -5 \
  1713         - integer -5 \
         1710  + real 9.22337203685478e+18 \
         1711  + real 9.22337203685478e+18 \
         1712  + real -5.0 \
         1713  + real -5.0 \
  1714   1714   ]
  1715   1715   
  1716   1716   # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
  1717   1717   # non-BLOB value into a BLOB and the result from casting any BLOB value
  1718   1718   # into a non-BLOB value may be different depending on whether the
  1719   1719   # database encoding is UTF-8, UTF-16be, or UTF-16le.
  1720   1720   #

Changes to test/tkt-a8a0d2996a.test.

    80     80     SELECT '100x'+'-2y';
    81     81   } {98}
    82     82   do_execsql_test 4.3 {
    83     83     SELECT '100x'+'4.5y';
    84     84   } {104.5}
    85     85   do_execsql_test 4.4 {
    86     86     SELECT '-9223372036854775807x'-'1x';
    87         -} {-9.22337203685478e+18}
           87  +} {-9223372036854775808}
    88     88   do_execsql_test 4.5 {
    89     89     SELECT '9223372036854775806x'+'1x';
    90         -} {9.22337203685478e+18}
           90  +} {9223372036854775807}
    91     91   do_execsql_test 4.6 {
    92         -  SELECT '1234x'/'10y';
    93         -} {123.4}
           92  +  SELECT '1234x'/'10y', '1234x'/'10.y', '1234x'/'1e1y';
           93  +} {123 123.4 123.4}
    94     94   
    95     95   finish_test