Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8806

Numeric CAST produce different warnings for strings literals vs functions

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0
        • Fix Version/s: 10.1.8
        • Component/s: OTHER
        • Labels:
          None
        • Sprint:
          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8466 CAST works differently for DECIMAL/INT vs DOUBLE for empty strings

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8852 Implicit or explicit CAST from MAX(string) to INT,DOUBLE,DECIMAL does not produce warnings

                • Major - Major loss of function.
                • Closed

                  Activity

                  There are no comments yet on this issue.

                    People

                    • Assignee:
                      bar Alexander Barkov
                      Reporter:
                      bar Alexander Barkov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Agile