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

CAST works differently for DECIMAL/INT vs DOUBLE for empty strings

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.1.8
        • Component/s: OTHER
        • Labels:
          None

          Description

          DECIMAL/INT vs DOUBLE work differently on CAST for empty strings:

          SET sql_mode='STRICT_ALL_TABLES';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (CAST('' AS INT));

          and

          SET sql_mode='STRICT_ALL_TABLES';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DECIMAL);
INSERT INTO t1 VALUES (CAST('' AS DECIMAL));

          correctly return

          ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''

          DOUBLE works differently:

          SET sql_mode='STRICT_ALL_TABLES';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (CAST('' AS DOUBLE));

          It accepts the value silently:

          Query OK, 1 row affected (0.03 sec)

          This happens because the CAST alone does not produce any warnings for DOUBLE:

          SELECT CAST('' AS DOUBLE);

          and produces a warning for DECIMAL and INT:

          MariaDB [test]> SELECT CAST('' AS INT);
+-----------------+
| CAST('' AS INT) |
+-----------------+
|               0 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [test]> SELECT CAST('' AS INT);
+-----------------+
| CAST('' AS INT) |
+-----------------+
|               0 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

          During a discussion on maria-developers, Sergei and Bar agreed that a warning should be generated for all numeric data types.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8109 unexpected CAST result

                • Major - Major loss of function.
                • In Review

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8300 CAST('' AS DECIMAL) is too strict on INSERT in strict mode

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8806 Numeric CAST produce different warnings for strings literals vs functions

                • 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:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: