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

    XMLWordPrintable

    Details

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

            Activity

              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: