Details
Minor
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
