|
Explicit CAST to DOUBLE:
SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE);
|
SHOW WARNINGS;
|
returns
+----------------------+--------------------------------+------------------------------+
|
| CAST('x0' AS DOUBLE) | CAST(COALESCE('x1') AS DOUBLE) | CAST(CONCAT('x2') AS DOUBLE) |
|
+----------------------+--------------------------------+------------------------------+
|
| 0 | 0 | 0 |
|
+----------------------+--------------------------------+------------------------------+
|
1 row in set, 1 warning (0.05 sec)
|
|
+---------+------+----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+----------------------------------------+
|
| Warning | 1292 | Truncated incorrect DOUBLE value: 'x0' |
|
+---------+------+----------------------------------------+
|
1 row in set (0.00 sec)
|
Notice, the string literal produced a warning, while the functions did not.
The same thing happens in implicit CAST to DOUBLE:
SELECT 0+'x0', 0+CONCAT('x1'), 0+COALESCE('x2');
|
SHOW WARNINGS;
|
Casting a prepare statement argument does not produce a warning:
PREPARE stmt FROM 'SELECT CAST(? AS DOUBLE)';
|
SET @tmp='0x';
|
EXECUTE stmt USING @tmp;
|
Explicit decimal CAST in the same scenario:
SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2;
|
SHOW WARNINGS;
|
returns
+----+----+----+
|
| x0 | x1 | x2 |
|
+----+----+----+
|
| 0 | 0 | 0 |
|
+----+----+----+
|
1 row in set, 3 warnings (0.00 sec)
|
|
+---------+------+---------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------------------------+
|
| Warning | 1292 | Truncated incorrect DECIMAL value: 'x0' |
|
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
|
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
|
+---------+------+---------------------------------------------------------+
|
3 rows in set (0.00 sec)
|
Notice, different warnings. The warning for the string literal looks better.
Implicit CAST to INT works even differently:
SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2'));
|
SHOW WARNINGS;
|
+----+----+--------------------------+
|
| x0 | x1 | LEFT('a',COALESCE('x2')) |
|
+----+----+--------------------------+
|
| | | |
|
+----+----+--------------------------+
|
1 row in set, 2 warnings (0.00 sec)
|
|
+---------+------+-----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: 'x0' |
|
| Warning | 1292 | Truncated incorrect INTEGER value: 'x0' |
|
+---------+------+-----------------------------------------+
|
2 rows in set (0.00 sec)
|
Notice, the string literal produced two warnings, while the functions produced no warnings.
|