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