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
--
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".