[MDEV-454] Addition of a time interval reduces the resulting value Created: 2012-08-15  Updated: 2012-08-30  Resolved: 2012-08-30

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.25
Fix Version/s: 5.5.27, 5.3.9

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: 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.
Generated at Thu Feb 08 06:28:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.