Numeric Literal Evaluation Examples

MySQL Tutorials - Herong's Tutorial Examples

∟Numeric Literal Evaluation Examples

This section provides tutorial examples on how numeric literals are evaluated to values of different datatypes.

Numeric literal evaluation example - NumericLiterals.sql:

-- NumericLiterals.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
SELECT 1 AS LINE_1;
SELECT -2 AS LINE_2;
SELECT 3.3 AS LINE_3;
SELECT -4.4e+4 AS LINE_4;
SELECT 12345678901234567890 AS LINE_5;
SELECT 0.12345678901234567890 AS LINE_6;
SELECT 1234567890.1234567890 AS LINE_7;
SELECT 12345678901234567890.1234567890 AS LINE_8;
SELECT 1234567890.1234567890e+10 AS LINE_9;
SELECT 0.0000000000000000000012345678901234567890 AS LINE_10;
SELECT 1.0e+1234567890 AS LINE_11;

Run NumericLiterals.sql on MySQL 8.0, 5.7, 5.6 and 5.0 servers, you will get:

herong> %mysql%\bin\mysql --user=root --password=TopSecret \
   < NumericLiterals.sql

LINE_1
1
LINE_2
-2
LINE_3
3.3
LINE_4
-44000
LINE_5
123456789012345678901234567890
LINE_6
0.12345678901234567890
LINE_7
1234567890.1234567890
LINE_8
12345678901234567890.1234567890
LINE_9
1.2345678901234567e19
LINE_10
0.0000000000000000000012345678901234567890
ERROR 1367 (22007) at line 14: Illegal double '1.0e+1234567890' value
found during parsing

A number of interesting notes here:

Line 4 tells us that an approximate numeric literal could be evaluated into an integer.
Line 5, 6, 7 and 8 tell us that exact numeric literals are evaluated to the last digits. No roundings are performed.
Line 9 tells us that an approximate numeric literal is evaluated up to about 17 digits.
Line 11 tells us that if the internal storage value limit is reached, an error will be generated.

Run NumericLiterals.sql on MySQL 4.0 server, you will get:

LINE_1
1
LINE_2
-2
LINE_3
3.3
LINE_4
-44000
LINE_5
123456789012345680000000000000
LINE_6
0.12345678901234568000
LINE_7
1234567890.1234567000
LINE_8
12345678901234567000.0000000000
LINE_9
1.2345678901235e+019
LINE_10
1.2345678901235e-021
LINE_11
1.#INF

As you can see, older version of MySQL are not as accurate as newer versions.

Line 4 tells us that an approximate numeric literal could be evaluated into an integer.
Line 5, 6, 7 and 8 tell us that exact numeric literals are evaluated up to about 17 digits.
Line 9 tells us that an approximate numeric literal is evaluated up to about 15 digits.
Line 10 tells us that an exact numeric literal could be converted into an proximate numeric value.
Line 11 tells us that if the internal storage value limit is reached, an approximate numeric literal is evaluated to "1.#INF".