/ Check-in [2ac985a3]
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:Keep the full precision of integers if possible when casting to "numeric". Ticket #2364. (CVS 4012)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2ac985a38034da87b0fa3837976e1f2164b22672
User & Date: drh 2007-05-16 11:55:57
Context
2007-05-16
13:55
Add a --nostatic option to mksqlite3c.tcl. With this option turned on, the extra "static" storage class markers are not inserted into the amalgamation. (CVS 4013) check-in: 57e17c7c user: drh tags: trunk
11:55
Keep the full precision of integers if possible when casting to "numeric". Ticket #2364. (CVS 4012) check-in: 2ac985a3 user: drh tags: trunk
2007-05-15
18:35
Additional tests for malformed UTF-8. (CVS 4011) check-in: 448d3ef6 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

    39     39   **
    40     40   ** Various scripts scan this source file in order to generate HTML
    41     41   ** documentation, headers files, or other derived files.  The formatting
    42     42   ** of the code in this file is, therefore, important.  See other comments
    43     43   ** in this file for details.  If in doubt, do not deviate from existing
    44     44   ** commenting and indentation practices when changing or adding code.
    45     45   **
    46         -** $Id: vdbe.c,v 1.618 2007/05/12 12:08:51 drh Exp $
           46  +** $Id: vdbe.c,v 1.619 2007/05/16 11:55:57 drh Exp $
    47     47   */
    48     48   #include "sqliteInt.h"
    49     49   #include "os.h"
    50     50   #include <ctype.h>
    51     51   #include <math.h>
    52     52   #include "vdbeInt.h"
    53     53   
................................................................................
  1505   1505   ** equivalent of atoi() or atof() and store 0 if no such conversion 
  1506   1506   ** is possible.
  1507   1507   **
  1508   1508   ** A NULL value is not changed by this routine.  It remains NULL.
  1509   1509   */
  1510   1510   case OP_ToNumeric: {                  /* same as TK_TO_NUMERIC, no-push */
  1511   1511     assert( pTos>=p->aStack );
  1512         -  if( (pTos->flags & MEM_Null)==0 ){
         1512  +  if( (pTos->flags & (MEM_Null|MEM_Int|MEM_Real))==0 ){
  1513   1513       sqlite3VdbeMemNumerify(pTos);
  1514   1514     }
  1515   1515     break;
  1516   1516   }
  1517   1517   #endif /* SQLITE_OMIT_CAST */
  1518   1518   
  1519   1519   /* Opcode: ToInt * * *
................................................................................
  1759   1759   ** Treat the top of the stack as a numeric quantity.  Replace it
  1760   1760   ** with its absolute value. If the top of the stack is NULL
  1761   1761   ** its value is unchanged.
  1762   1762   */
  1763   1763   case OP_Negative:              /* same as TK_UMINUS, no-push */
  1764   1764   case OP_AbsValue: {
  1765   1765     assert( pTos>=p->aStack );
         1766  +  if( (pTos->flags & (MEM_Real|MEM_Int|MEM_Null))==0 ){
         1767  +    sqlite3VdbeMemNumerify(pTos);
         1768  +  }
  1766   1769     if( pTos->flags & MEM_Real ){
  1767         -    neg_abs_real_case:
  1768   1770       Release(pTos);
  1769   1771       if( pOp->opcode==OP_Negative || pTos->r<0.0 ){
  1770   1772         pTos->r = -pTos->r;
  1771   1773       }
  1772   1774       pTos->flags = MEM_Real;
  1773   1775     }else if( pTos->flags & MEM_Int ){
  1774   1776       Release(pTos);
  1775   1777       if( pOp->opcode==OP_Negative || pTos->u.i<0 ){
  1776   1778         pTos->u.i = -pTos->u.i;
  1777   1779       }
  1778   1780       pTos->flags = MEM_Int;
  1779         -  }else if( pTos->flags & MEM_Null ){
  1780         -    /* Do nothing */
  1781         -  }else{
  1782         -    sqlite3VdbeMemNumerify(pTos);
  1783         -    goto neg_abs_real_case;
  1784   1781     }
  1785   1782     break;
  1786   1783   }
  1787   1784   
  1788   1785   /* Opcode: Not * * *
  1789   1786   **
  1790   1787   ** Interpret the top of the stack as a boolean value.  Replace it

Changes to src/vdbemem.c.

   358    358   }
   359    359   
   360    360   /*
   361    361   ** Convert pMem so that it has types MEM_Real or MEM_Int or both.
   362    362   ** Invalidate any prior representations.
   363    363   */
   364    364   int sqlite3VdbeMemNumerify(Mem *pMem){
   365         -  sqlite3VdbeMemRealify(pMem);
   366         -  sqlite3VdbeIntegerAffinity(pMem);
          365  +  double r1, r2;
          366  +  i64 i;
          367  +  assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))==0 );
          368  +  assert( (pMem->flags & (MEM_Blob|MEM_Str))!=0 );
          369  +  r1 = sqlite3VdbeRealValue(pMem);
          370  +  i = (i64)r1;
          371  +  r2 = (double)i;
          372  +  if( r1==r2 ){
          373  +    sqlite3VdbeMemIntegerify(pMem);
          374  +  }else{
          375  +    pMem->r = r1;
          376  +    pMem->flags = MEM_Real;
          377  +    sqlite3VdbeMemRelease(pMem);
          378  +  }
   367    379     return SQLITE_OK;
   368    380   }
   369    381   
   370    382   /*
   371    383   ** Delete any previous value and set the value stored in *pMem to NULL.
   372    384   */
   373    385   void sqlite3VdbeMemSetNull(Mem *pMem){

Changes to test/cast.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the CAST operator.
    13     13   #
    14         -# $Id: cast.test,v 1.5 2006/03/03 19:12:30 drh Exp $
           14  +# $Id: cast.test,v 1.6 2007/05/16 11:55:57 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Only run these tests if the build includes the CAST operator
    20     20   ifcapable !cast {
    21     21     finish_test
................................................................................
   187    187   #
   188    188   do_test cast-2.1 {
   189    189     execsql {SELECT CAST('   123' AS integer)}
   190    190   } 123
   191    191   do_test cast-2.2 {
   192    192     execsql {SELECT CAST('   -123.456' AS real)}
   193    193   } -123.456
          194  +
          195  +# ticket #2364.  Use full percision integers if possible when casting
          196  +# to numeric.  Do not fallback to real (and the corresponding 48-bit
          197  +# mantissa) unless absolutely necessary.
          198  +#
          199  +do_test cast-3.1 {
          200  +  execsql {SELECT CAST(9223372036854774800 AS integer)}
          201  +} 9223372036854774800
          202  +do_test cast-3.2 {
          203  +  execsql {SELECT CAST(9223372036854774800 AS numeric)}
          204  +} 9223372036854774800
          205  +do_test cast-3.3 {
          206  +  execsql {SELECT CAST(9223372036854774800 AS real)}
          207  +} 9.22337203685477e+18
          208  +do_test cast-3.4 {
          209  +  execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}
          210  +} 9223372036854774784
          211  +do_test cast-3.5 {
          212  +  execsql {SELECT CAST(-9223372036854774800 AS integer)}
          213  +} -9223372036854774800
          214  +do_test cast-3.6 {
          215  +  execsql {SELECT CAST(-9223372036854774800 AS numeric)}
          216  +} -9223372036854774800
          217  +do_test cast-3.7 {
          218  +  execsql {SELECT CAST(-9223372036854774800 AS real)}
          219  +} -9.22337203685477e+18
          220  +do_test cast-3.8 {
          221  +  execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}
          222  +} -9223372036854774784
          223  +do_test cast-3.11 {
          224  +  execsql {SELECT CAST('9223372036854774800' AS integer)}
          225  +} 9223372036854774800
          226  +do_test cast-3.12 {
          227  +  execsql {SELECT CAST('9223372036854774800' AS numeric)}
          228  +} 9223372036854774800
          229  +do_test cast-3.13 {
          230  +  execsql {SELECT CAST('9223372036854774800' AS real)}
          231  +} 9.22337203685477e+18
          232  +do_test cast-3.14 {
          233  +  execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}
          234  +} 9223372036854774784
          235  +do_test cast-3.15 {
          236  +  execsql {SELECT CAST('-9223372036854774800' AS integer)}
          237  +} -9223372036854774800
          238  +do_test cast-3.16 {
          239  +  execsql {SELECT CAST('-9223372036854774800' AS numeric)}
          240  +} -9223372036854774800
          241  +do_test cast-3.17 {
          242  +  execsql {SELECT CAST('-9223372036854774800' AS real)}
          243  +} -9.22337203685477e+18
          244  +do_test cast-3.18 {
          245  +  execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}
          246  +} -9223372036854774784
          247  +if {[db eval {PRAGMA encoding}]=="UTF-8"} {
          248  +  do_test cast-3.21 {
          249  +    execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}
          250  +  } 9223372036854774800
          251  +  do_test cast-3.22 {
          252  +    execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}
          253  +  } 9223372036854774800
          254  +  do_test cast-3.23 {
          255  +    execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}
          256  +  } 9.22337203685477e+18
          257  +  do_test cast-3.24 {
          258  +    execsql {SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real) AS integer)}
          259  +  } 9223372036854774784
          260  +}
          261  +do_test case-3.31 {
          262  +  execsql {SELECT CAST(NULL AS numeric)}
          263  +} {{}}
   194    264   
   195    265   
   196    266   finish_test