Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.5.25
-
None
-
None
Description
I presume it affects 5.3 too, but I didn't check.
The issue comes from the fact that date and datetime values like '2012-00-00' are vaguely supported (on one hand, MySQL docs say that ranges for month and day are 1..12 and 1..31, respectively; on the other hand, it only mentions that zero values for month and day are invalid for TIMESTAMP, but doesn't say anything for DATE/DATETIME).
So, this works, no errors or warnings:
create table t1 (d date);
|
# Query OK, 0 rows affected (0.08 sec)
|
|
|
insert into t1 values ('2012-00-00');
|
# Query OK, 1 row affected (0.04 sec)
|
|
|
select * from t1;
|
# +------------+
|
# | d |
|
# +------------+
|
# | 2012-00-00 |
|
# +------------+
|
Comparison works too:
select * from t1 where d > 20120000;
|
# Empty set (0.00 sec)
|
|
|
select * from t1 where d >= 20120000;
|
# +------------+
|
# | d |
|
# +------------+
|
# | 2012-00-00 |
|
# +------------+
|
# 1 row in set (0.00 sec)
|
Now, we add 1 day to the value. Again, no warnings or errors:
update t1 set d = adddate(d, interval 1 day);
|
# Query OK, 1 row affected (0.04 sec)
|
# Rows matched: 1 Changed: 1 Warnings: 0
|
But the result is not what someone would expect:
|
|
select * from t1 where d >= 20120000;
|
# Empty set (0.00 sec)
|
|
|
select * from t1;
|
# +------------+
|
# | d |
|
# +------------+
|
# | 2011-12-01 |
|
# +------------+
|
# 1 row in set (0.00 sec)
|
In MySQL (5.6, did not try earlier) it is also not perfect: adddate turns the value to NULL, again without a warning. But it seems somewhat less strange than reducing it.