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

CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 10.1.8
    • OTHER
    • None
    • 10.1.8-1, 10.1.8-2

    Description

      Implicit cast of a string with trailing spaces silently accepts the value in case of DECIMAL and INT:

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

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

      A similar script with DOUBLE:

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

      returns an error:

      ERROR 1265 (01000): Data truncated for column 'a' at row 1

      Comparing to the behavior exposed by CAST (see MDEV-8466), it's exactly the opposite:

      • CAST is stricter for DECIMAL/INT comparing to DOUBLE (on empty strings)
      • INSERT is weaker for DECINAL/INT comparing to DOUBLE (on trailing spaces)

      Explicit CAST also works differently:

      SELECT CAST('1 ' AS DOUBLE), CAST('2 ' AS DECIMAL), CAST('3 ' AS INT);
      		 
      SHOW WARNINGS;

      returns

      +----------------------+-----------------------+-------------------+
      		 
      | CAST('1 ' AS DOUBLE) | CAST('2 ' AS DECIMAL) | CAST('3 ' AS INT) |
      		 
      +----------------------+-----------------------+-------------------+
      		 
      |                    1 |                     2 |                 3 |
      		 
      +----------------------+-----------------------+-------------------+
      		 
      1 row in set, 1 warning (0.01 sec)
      		 
       
      		 
      +---------+------+-----------------------------------------+
      		 
      | Level   | Code | Message                                 |
      		 
      +---------+------+-----------------------------------------+
      		 
      | Warning | 1292 | Truncated incorrect INTEGER value: '3 ' |
      		 
      +---------+------+-----------------------------------------+
      		 
      1 row in set (0.00 sec)

      Notice, DOUBLE and DECIMAL ignore the trailing spaces silently, while INT produces a warning.

      Sergei and Bar during a discussion on maria-developers agreed that all data types should produce a NOTE (not a warning) for all numeric data types, in all query parts (INSERT,CAST,dynamic columns).

      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.
          • Closed

          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.
          • Stalled

          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

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description Implicit cast of a string with trailing spaces silently accepts the value in case of DECIMAL and INT:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DECIMAL);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1 ');
            {code}

            A similar script with DOUBLE:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DOUBLE);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            returns an error:
            {noformat}
            ERROR 1265 (01000): Data truncated for column 'a' at row 1
            {noformat}
            		 Implicit cast of a string with trailing spaces silently accepts the value in case of DECIMAL and INT:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DECIMAL);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1 ');
            {code}

            A similar script with DOUBLE:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DOUBLE);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            returns an error:
            {noformat}
            ERROR 1265 (01000): Data truncated for column 'a' at row 1
            {noformat}


            Comparing to the behavior exposed by CAST (see MDEV-8466), it's exactly the opposite:
            - CAST is stricter for DECIMAL/INT comparing to DOUBLE (on empty strings)
            - INSERT is weaker for DECINAL/INT comparing to DOUBLE (on trailing spaces)
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Summary INSERT works differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces
            bar Alexander Barkov made changes -
            Description Implicit cast of a string with trailing spaces silently accepts the value in case of DECIMAL and INT:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DECIMAL);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1 ');
            {code}

            A similar script with DOUBLE:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DOUBLE);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            returns an error:
            {noformat}
            ERROR 1265 (01000): Data truncated for column 'a' at row 1
            {noformat}


            Comparing to the behavior exposed by CAST (see MDEV-8466), it's exactly the opposite:
            - CAST is stricter for DECIMAL/INT comparing to DOUBLE (on empty strings)
            - INSERT is weaker for DECINAL/INT comparing to DOUBLE (on trailing spaces)
            		 Implicit cast of a string with trailing spaces silently accepts the value in case of DECIMAL and INT:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DECIMAL);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1 ');
            {code}

            A similar script with DOUBLE:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DOUBLE);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            returns an error:
            {noformat}
            ERROR 1265 (01000): Data truncated for column 'a' at row 1
            {noformat}


            Comparing to the behavior exposed by CAST (see MDEV-8466), it's exactly the opposite:
            - CAST is stricter for DECIMAL/INT comparing to DOUBLE (on empty strings)
            - INSERT is weaker for DECINAL/INT comparing to DOUBLE (on trailing spaces)

            Explicit CAST also works differently:
            {code}
            SELECT CAST('1 ' AS DOUBLE), CAST('2 ' AS DECIMAL), CAST('3 ' AS INT);
            SHOW WARNINGS;
            {code}
            returns
            {noformat}
            +----------------------+-----------------------+-------------------+
            | CAST('1 ' AS DOUBLE) | CAST('2 ' AS DECIMAL) | CAST('3 ' AS INT) |
            +----------------------+-----------------------+-------------------+
            | 1 | 2 | 3 |
            +----------------------+-----------------------+-------------------+
            1 row in set, 1 warning (0.01 sec)

            +---------+------+-----------------------------------------+
            | Level | Code | Message |
            +---------+------+-----------------------------------------+
            | Warning | 1292 | Truncated incorrect INTEGER value: '3 ' |
            +---------+------+-----------------------------------------+
            1 row in set (0.00 sec)
            {noformat}
            Notice, DOUBLE and DECIMAL ignore the trailing spaces silently, while INT produces a warning.
            bar Alexander Barkov made changes -
            Description Implicit cast of a string with trailing spaces silently accepts the value in case of DECIMAL and INT:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DECIMAL);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1 ');
            {code}

            A similar script with DOUBLE:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DOUBLE);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            returns an error:
            {noformat}
            ERROR 1265 (01000): Data truncated for column 'a' at row 1
            {noformat}


            Comparing to the behavior exposed by CAST (see MDEV-8466), it's exactly the opposite:
            - CAST is stricter for DECIMAL/INT comparing to DOUBLE (on empty strings)
            - INSERT is weaker for DECINAL/INT comparing to DOUBLE (on trailing spaces)

            Explicit CAST also works differently:
            {code}
            SELECT CAST('1 ' AS DOUBLE), CAST('2 ' AS DECIMAL), CAST('3 ' AS INT);
            SHOW WARNINGS;
            {code}
            returns
            {noformat}
            +----------------------+-----------------------+-------------------+
            | CAST('1 ' AS DOUBLE) | CAST('2 ' AS DECIMAL) | CAST('3 ' AS INT) |
            +----------------------+-----------------------+-------------------+
            | 1 | 2 | 3 |
            +----------------------+-----------------------+-------------------+
            1 row in set, 1 warning (0.01 sec)

            +---------+------+-----------------------------------------+
            | Level | Code | Message |
            +---------+------+-----------------------------------------+
            | Warning | 1292 | Truncated incorrect INTEGER value: '3 ' |
            +---------+------+-----------------------------------------+
            1 row in set (0.00 sec)
            {noformat}
            Notice, DOUBLE and DECIMAL ignore the trailing spaces silently, while INT produces a warning.
            		 Implicit cast of a string with trailing spaces silently accepts the value in case of DECIMAL and INT:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DECIMAL);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1 ');
            {code}

            A similar script with DOUBLE:
            {code}
            SET sql_mode='STRICT_ALL_TABLES';
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a DOUBLE);
            INSERT INTO t1 VALUES ('1 ');
            {code}
            returns an error:
            {noformat}
            ERROR 1265 (01000): Data truncated for column 'a' at row 1
            {noformat}


            Comparing to the behavior exposed by CAST (see MDEV-8466), it's exactly the opposite:
            - CAST is stricter for DECIMAL/INT comparing to DOUBLE (on empty strings)
            - INSERT is weaker for DECINAL/INT comparing to DOUBLE (on trailing spaces)

            Explicit CAST also works differently:
            {code}
            SELECT CAST('1 ' AS DOUBLE), CAST('2 ' AS DECIMAL), CAST('3 ' AS INT);
            SHOW WARNINGS;
            {code}
            returns
            {noformat}
            +----------------------+-----------------------+-------------------+
            | CAST('1 ' AS DOUBLE) | CAST('2 ' AS DECIMAL) | CAST('3 ' AS INT) |
            +----------------------+-----------------------+-------------------+
            | 1 | 2 | 3 |
            +----------------------+-----------------------+-------------------+
            1 row in set, 1 warning (0.01 sec)

            +---------+------+-----------------------------------------+
            | Level | Code | Message |
            +---------+------+-----------------------------------------+
            | Warning | 1292 | Truncated incorrect INTEGER value: '3 ' |
            +---------+------+-----------------------------------------+
            1 row in set (0.00 sec)
            {noformat}
            Notice, DOUBLE and DECIMAL ignore the trailing spaces silently, while INT produces a warning.

            Sergei and Bar during a discussion on maria-developers agreed that all data types should produce a NOTE (not a warning) for all numeric data types, in all query parts (INSERT,CAST,dynamic columns).
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.0 [ 16000 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.8-1 [ 13 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Sprint 10.1.8-1 [ 13 ] 10.1.8-1, 10.1.8-2 [ 13, 14 ]
            serg Sergei Golubchik made changes -
            Rank Ranked higher
            bar Alexander Barkov made changes -
            Fix Version/s 10.1.8 [ 19605 ]
            Fix Version/s 10.1 [ 16100 ]
            bar Alexander Barkov made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 70631 ] MariaDB v4 [ 149371 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.