|
In the example below, a warning on '2009-01-32' is expected, but not produced:
CREATE TABLE t1(c2 DATE NULL, INDEX(c2)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('2009-01-27'),('2009-01-31'),('2009-02-28'),('2010-01-13');
|
EXPLAIN EXTENDED SELECT COUNT(*) FROM t1 WHERE c2='2009-01-32' OR c2='2009-01-30' OR c2='2009-04-31' OR c2='2009-09-31';
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 range c2 c2 4 NULL 3 100.00 Using where; Using index
|
Warnings:
|
Note 1003 select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`c2` = '2009-01-32') or (`test`.`t1`.`c2` = '2009-01-30') or (`test`.`t1`.`c2` = '2009-04-31') or (`test`.`t1`.`c2` = '2009-09-31'))
|
SELECT COUNT(*) FROM t1 WHERE c2='2009-01-32' OR c2='2009-01-30' OR c2='2009-04-31' OR c2='2009-09-31';
|
COUNT(*)
|
0
|
However if I modify the test case just a little bit, e.g. remove one value from the table (or from the OR list), so that the type changes from range to index, the warning shows up:
|
DROP TABLE IF EXISTS t1,t2;
|
CREATE TABLE t1(c2 DATE NULL, INDEX(c2));
|
INSERT INTO t1 VALUES ('2009-01-27'),('2009-01-31'),('2009-02-28'),('2010-01-13');
|
EXPLAIN EXTENDED SELECT COUNT(*) FROM t1 WHERE c2='2009-01-32' OR c2='2009-01-30' OR c2='2009-09-31';
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 index c2 c2 4 NULL 4 50.00 Using where; Using index
|
Warnings:
|
Note 1003 select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`c2` = '2009-01-32') or (`test`.`t1`.`c2` = '2009-01-30') or (`test`.`t1`.`c2` = '2009-09-31'))
|
SELECT COUNT(*) FROM t1 WHERE c2='2009-01-32' OR c2='2009-01-30' OR c2='2009-09-31';
|
COUNT(*)
|
0
|
Warnings:
|
Warning 1292 Incorrect datetime value: '2009-01-32'
|
Below is another example, now with signed int values. When the field with index is compared to the number, the warning is produced, while if a field of the same type, but without an index, is compared to the same constant, the warning is missing. Unlike in the first example where the date is obviously incorrect, here I am not even sure whether the warning is supposed to be produced or not, but apparently it should at least be consistent.
CREATE TABLE t1(c1 INT SIGNED, c2 INT SIGNED, INDEX(c2));
|
INSERT INTO t1 VALUES(106,106),(107,107);
|
EXPLAIN EXTENDED
|
SELECT * FROM t1 WHERE c2=2147483648;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ref c2 c2 5 const 1 100.00 Using index condition
|
Warnings:
|
Warning 1264 Out of range value for column 'c2' at row 1
|
Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c2` = 2147483648)
|
SELECT * FROM t1 WHERE c2=2147483648;
|
c1 c2
|
Warnings:
|
Warning 1264 Out of range value for column 'c2' at row 1
|
EXPLAIN EXTENDED
|
SELECT * FROM t1 WHERE c1=2147483648;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
Warnings:
|
Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` = 2147483648)
|
SELECT * FROM t1 WHERE c1=2147483648;
|
c1 c2
|
|