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
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.0
        • Component/s: OTHER
        • Labels:
          None

          Description

          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 |
+---------+------+-----------------------------------------------------+

            Gliffy Diagrams

              Attachments

                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: