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

INT,DOUBLE,DECIMAL produce different warnings on comparison

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: OTHER
    • Labels:
      None

      Description

      Update:

      • In versions 5.5 and 10.0 it works as described below
      • In versions 10.1, 10.2, 10.3, 10.4 there are no warnings produced for all data types. Looks wrong. One warning is expected.

      This script with INT data type:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, KEY(a));
      INSERT INTO t1 VALUES (0),(10),(20),(30);
      SELECT * FROM t1 WHERE a='x';
      SHOW WARNINGS;
      

      returns no warnings.

      So does the same script with DOUBLE:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DOUBLE, KEY(a));
      INSERT INTO t1 VALUES (0),(10),(20),(30);
      SELECT * FROM t1 WHERE a='x'; SHOW WARNINGS;
      

      The script with DECIMAL works differently:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL, KEY(a));
      INSERT INTO t1 VALUES (0),(10),(20),(30);
      SELECT * FROM t1 WHERE a='x';
      SHOW WARNINGS;
      

      It returns a number of warnings, some of them are obviously redundant:

      +---------+------+------------------------------------------------------+
      | Level   | Code | Message                                              |
      +---------+------+------------------------------------------------------+
      | Warning | 1366 | Incorrect decimal value: 'x' for column 'a' at row 1 |
      | Warning | 1366 | Incorrect decimal value: 'x' for column 'a' at row 1 |
      | Warning | 1366 | Incorrect decimal value: 'x' for column 'a' at row 1 |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'x'                |
      +---------+------+------------------------------------------------------+
      4 rows in set (0.00 sec)
      

      With an empty string INT and DOUBLE produce no warnings:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, KEY(a));
      INSERT INTO t1 VALUES (0),(10),(20),(30);
      SELECT * FROM t1 WHERE a='';
      SHOW WARNINGS;
      

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DOUBLE, KEY(a));
      INSERT INTO t1 VALUES (0),(10),(20),(30);
      SELECT * FROM t1 WHERE a='';
      SHOW WARNINGS;
      

      while DECIMAL:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL, KEY(a));
      INSERT INTO t1 VALUES (0),(10),(20),(30);
      SELECT * FROM t1 WHERE a='';
      SHOW WARNINGS;
      

      produces a number of similar warnings:

      +---------+------+-----------------------------------------------------+
      | Level   | Code | Message                                             |
      +---------+------+-----------------------------------------------------+
      | Warning | 1366 | Incorrect decimal value: '' for column 'a' at row 1 |
      | Warning | 1366 | Incorrect decimal value: '' for column 'a' at row 1 |
      | Warning | 1366 | Incorrect decimal value: '' for column 'a' at row 1 |
      +---------+------+-----------------------------------------------------+
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated: