An error occurred when the CAST function converted the numerical value in the form of Scientific notation

(1) By Chenglin Tian (ChenglinTian) on 2022-11-20 08:55:25 [link] [source]

When we used CAST function to type convert the number represented by Scientific notation, we found the following problem: when converting the number of string type to INT or NUMERIC data type, the result was inconsistent with the expected result.

SELECT CAST('7.2250617031974513E18' AS NUMERIC); -- expected: 7225061703197451300, unexpected: 7.22506170319745e+18

SELECT CAST('7.2250617031974513E18' AS INT);
-- expected: 7225061703197451300, unexpected: 7

(2) By Keith Medcalf (kmedcalf) on 2022-11-20 16:23:40 in reply to 1 [source]

Indeed.

.param init
.param set :s "'7.2250617031974513E18'"
.version
select raw, typeof(raw), 
       numeric, typeof(numeric), 
       integer, typeof(integer), 
       real, typeof(real), 
       best, typeof(best) 
  from (
        select :s as raw, 
               cast(:s as numeric) as numeric, 
               cast(:s as integer) as integer, 
               cast(:s as real) as real, 
               tobesttype(:s) as best
       );

Depending on the compiler and Operating System (this is on the current version of Windows 10), corresponding to checkout [9776fa3175], this is the results I obtained executing the above script:

SQLite version 3.41.0 2022-11-19 18:41:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .param init
sqlite> .param set :s "'7.2250617031974513E18'"
sqlite> .version
SQLite 3.41.0 2022-11-19 18:41:29 3dd6751b8bc1ffdabf28ddd29c107e0ef391db7f86327dae3ee531e4a58ealt1
zlib version 1.2.12
msvc-1929
sqlite> select raw, typeof(raw),
   ...>        numeric, typeof(numeric),
   ...>        integer, typeof(integer),
   ...>        real, typeof(real),
   ...>        best, typeof(best)
   ...>   from (
   ...>         select :s as raw,
   ...>                cast(:s as numeric) as numeric,
   ...>                cast(:s as integer) as integer,
   ...>                cast(:s as real) as real,
   ...>                tobesttype(:s) as best
   ...>        );
┌─────────────────────────┬─────────────┬──────────────────────┬─────────────────┬─────────┬─────────────────┬──────────────────────┬──────────────┬──────────────────────┬──────────────┐
│           raw           │ typeof(raw) │       numeric        │ typeof(numeric) │ integer │ typeof(integer) │         real         │ typeof(real) │         best         │ typeof(best) │
├─────────────────────────┼─────────────┼──────────────────────┼─────────────────┼─────────┼─────────────────┼──────────────────────┼──────────────┼──────────────────────┼──────────────┤
│ '7.2250617031974513E18' │ 'text'      │ 7.22506170319745e+18 │ 'real'          │ 7       │ 'integer'       │ 7.22506170319745e+18 │ 'real'       │ 7.22506170319745e+18 │ 'real'       │
└─────────────────────────┴─────────────┴──────────────────────┴─────────────────┴─────────┴─────────────────┴──────────────────────┴──────────────┴──────────────────────┴──────────────┘

and

SQLite version 3.41.0 2022-11-19 18:41:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .param init
sqlite> .param set :s "'7.2250617031974513E18'"
sqlite> .version
SQLite 3.41.0 2022-11-19 18:41:29 3dd6751b8bc1ffdabf28ddd29c107e0ef391db7f86327dae3ee531e4a58ealt1
zlib version 1.2.12
gcc-12.2.0
sqlite> select raw, typeof(raw),
   ...>        numeric, typeof(numeric),
   ...>        integer, typeof(integer),
   ...>        real, typeof(real),
   ...>        best, typeof(best)
   ...>   from (
   ...>         select :s as raw,
   ...>                cast(:s as numeric) as numeric,
   ...>                cast(:s as integer) as integer,
   ...>                cast(:s as real) as real,
   ...>                tobesttype(:s) as best
   ...>        );
┌─────────────────────────┬─────────────┬──────────────────────┬─────────────────┬─────────┬─────────────────┬──────────────────────┬──────────────┬─────────────────────┬──────────────┐
│           raw           │ typeof(raw) │       numeric        │ typeof(numeric) │ integer │ typeof(integer) │         real         │ typeof(real) │        best         │ typeof(best) │
├─────────────────────────┼─────────────┼──────────────────────┼─────────────────┼─────────┼─────────────────┼──────────────────────┼──────────────┼─────────────────────┼──────────────┤
│ '7.2250617031974513E18' │ 'text'      │ 7.22506170319745e+18 │ 'real'          │ 7       │ 'integer'       │ 7.22506170319745e+18 │ 'real'       │ 7225061703197451300 │ 'integer'    │
└─────────────────────────┴─────────────┴──────────────────────┴─────────────────┴─────────┴─────────────────┴──────────────────────┴──────────────┴─────────────────────┴──────────────┘

You will note the consistency of the results. MSVC only pretends to use extended precision floating point (that is, it recognizes but ignores the modifier 'long' when applied to 'double'), however MinGW GCC 12.2 (and any prior version) do support extended precision (long double).

ToBestType is available here: http://www.dessus.com/files/sqlnumeric.c

(3) By Tim Streater (Clothears) on 2022-11-20 16:41:46 in reply to 1 [link] [source]

Why do you keep suppling text to CAST instead of an actual number? Try:

sqlite> SELECT CAST(7.2250617031974513E18 AS INT);

and get:

7225061703197451264