Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
None
-
10.1.8-3
Description
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.
Attachments
Issue Links
Activity
| Description |
{code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. |
{code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. Decimal CAST in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} |
| Description |
{code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. Decimal CAST in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} |
{code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. Decimal CAST in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. |
| Description |
{code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. Decimal CAST in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. |
{code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. Decimal CAST in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. Implicit CAST to INT works even differently: {code} SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2')); SHOW WARNINGS; {code} {noformat} +----+----+--------------------------+ | 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) {noformat} Notice, string literal produced two warnings, while the functions produced no warnings. |
| Description |
{code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. Decimal CAST in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. Implicit CAST to INT works even differently: {code} SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2')); SHOW WARNINGS; {code} {noformat} +----+----+--------------------------+ | 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) {noformat} Notice, string literal produced two warnings, while the functions produced no warnings. |
*Explicit CAST to DOUBLE*: {code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. The same thing happens in *implicit CAST to DOUBLE*: {code} SELECT 0+'x0', 0+CONCAT('x1'), 0+COALESCE('x2'); SHOW WARNINGS; {code} *Explicit decimal CAST* in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. *Implicit CAST to INT* works even differently: {code} SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2')); SHOW WARNINGS; {code} {noformat} +----+----+--------------------------+ | 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) {noformat} Notice, string literal produced two warnings, while the functions produced no warnings. |
| Description |
*Explicit CAST to DOUBLE*: {code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. The same thing happens in *implicit CAST to DOUBLE*: {code} SELECT 0+'x0', 0+CONCAT('x1'), 0+COALESCE('x2'); SHOW WARNINGS; {code} *Explicit decimal CAST* in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. *Implicit CAST to INT* works even differently: {code} SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2')); SHOW WARNINGS; {code} {noformat} +----+----+--------------------------+ | 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) {noformat} Notice, string literal produced two warnings, while the functions produced no warnings. |
*Explicit CAST to DOUBLE*: {code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. The same thing happens in *implicit CAST to DOUBLE*: {code} SELECT 0+'x0', 0+CONCAT('x1'), 0+COALESCE('x2'); SHOW WARNINGS; {code} *Explicit decimal CAST* in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. *Implicit CAST to INT* works even differently: {code} SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2')); SHOW WARNINGS; {code} {noformat} +----+----+--------------------------+ | 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) {noformat} Notice, the string literal produced two warnings, while the functions produced no warnings. |
| Description |
*Explicit CAST to DOUBLE*: {code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. The same thing happens in *implicit CAST to DOUBLE*: {code} SELECT 0+'x0', 0+CONCAT('x1'), 0+COALESCE('x2'); SHOW WARNINGS; {code} *Explicit decimal CAST* in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. *Implicit CAST to INT* works even differently: {code} SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2')); SHOW WARNINGS; {code} {noformat} +----+----+--------------------------+ | 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) {noformat} Notice, the string literal produced two warnings, while the functions produced no warnings. |
*Explicit CAST to DOUBLE*: {code} SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE); SHOW WARNINGS; {code} returns {noformat} +----------------------+--------------------------------+------------------------------+ | 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) {noformat} Notice, the string literal produced a warning, while the functions did not. The same thing happens in *implicit CAST to DOUBLE*: {code} SELECT 0+'x0', 0+CONCAT('x1'), 0+COALESCE('x2'); SHOW WARNINGS; {code} Casting a prepare statement argument does not produce a warning: {code} PREPARE stmt FROM 'SELECT CAST(? AS DOUBLE)'; SET @tmp='0x'; EXECUTE stmt USING @tmp; {code} *Explicit decimal CAST* in the same scenario: {code} SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2; SHOW WARNINGS; {code} returns {noformat} +----+----+----+ | 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) {noformat} Notice, different warnings. The warning for the string literal looks better. *Implicit CAST to INT* works even differently: {code} SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2')); SHOW WARNINGS; {code} {noformat} +----+----+--------------------------+ | 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) {noformat} Notice, the string literal produced two warnings, while the functions produced no warnings. |
| Sprint | 10.1.8-3 [ 15 ] |
| Rank | Ranked higher |
| Fix Version/s | 10.1.8 [ 19605 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 71618 ] | MariaDB v4 [ 149618 ] |