MySQL Bugs: #21677: Change in results with "now() BETWEEN date_field1 AND date

Bug #21677	Change in results with "now() BETWEEN date_field1 AND date_field2"
Submitted:	16 Aug 2006 18:48	Modified:	6 Feb 2014 14:01
Reporter:	Benjamin Morin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	4.1.21, 5.0.23	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	between, date, date comparison, timestamp

Description:
A query using "now() BETWEEN date_field1 AND date_field2" is has changed its result set from version 4.1.20 and 4.1.21.

How to repeat:
drop database if exists datetest;
create database datetest;
use datetest;
drop table if exists d;
create table d (d int primary key auto_increment, start_date date, end_date date);
insert into d (start_date, end_date) values (date_sub(curdate(), interval 1 day), date_add(curdate(), interval 1 day));
select version(), (select count(*) from d where now() between start_date and end_date) as now, (select count(*) from d where curdate() between start_date and end_date) as curdate;

+-----------------+------+---------+
| version()       | now  | curdate |
+-----------------+------+---------+
| 4.1.18-standard |    1 |       1 |
+-----------------+------+---------+
+-----------------+------+---------+
| version()       | now  | curdate |
+-----------------+------+---------+
| 4.1.19-standard |    1 |       1 |
+-----------------+------+---------+
+-----------------+------+---------+
| version()       | now  | curdate |
+-----------------+------+---------+
| 4.1.20-standard |    1 |       1 |
+-----------------+------+---------+
+-----------------+------+---------+
| version()       | now  | curdate |
+-----------------+------+---------+
| 4.1.21-standard |    0 |       1 |
+-----------------+------+---------+

Suggested fix:
Re-implement whatever change happened in 4.1.21 that caused the change in behavior in such a was that the queries still give the same results.

This change also happens between 5.0.22 and 5.0.23:

+-----------------+------+---------+
| version()       | now  | curdate |
+-----------------+------+---------+
| 5.0.22-standard |    1 |       1 | 
+-----------------+------+---------+
+-----------------+------+---------+
| version()       | now  | curdate |
+-----------------+------+---------+
| 5.0.23-standard |    0 |       1 | 
+-----------------+------+---------+
+-----------------+------+---------+
| version()       | now  | curdate |
+-----------------+------+---------+
| 5.0.24-standard |    0 |       1 | 
+-----------------+------+---------+

Verified as described on Linux using last 4.1 and 5.0 BK sources:

mysql> select version(), (select count(*) from d where now() between start_date and end_date) as now, (select count(*) from d where curdate() between start_date and end_date) as curdate;
+-----------+------+---------+
| version() | now  | curdate |
+-----------+------+---------+
| 5.0.25    |    0 |       1 |
+-----------+------+---------+
1 row in set (0.00 sec)

mysql> select version(), (select count(*) from d where now() >= start_date and now() <= end_date) as now, (select count(*) from d where curdate() >= start_date and curdate() <= end_date) as curdate;
+-----------+------+---------+
| version() | now  | curdate |
+-----------+------+---------+
| 5.0.25    |    1 |       1 |
+-----------+------+---------+
1 row in set (0.00 sec)

mysql> select now() as now, start_date, end_date from d;
+---------------------+------------+------------+
| now                 | start_date | end_date   |
+---------------------+------------+------------+
| 2006-08-16 22:34:09 | 2006-08-15 | 2006-08-17 |
+---------------------+------------+------------+
1 row in set (0.00 sec)

There is duplicate bug #22058

Pushed into 4.1.22/5.0.26/5.1.12-beta

If a user wants to use date/time string constants like '2001-1-1' in a comparison then such constants should be casted to the date/time type like CAST('2001-1-1' AS DATE). Otherwise a wrong comparison result may occur.
It should be noted somewhere in the manual.

This problem isn't with string to date conversions.  It has to do with a datetime field being used in a comparison using between with 2 date fields.  It's more likely that the datetime fields are not being converted to datetime fields for the comparison.  Using a string constant in place of the now() statement actually returns correct results, so I don't understand the context of your comment.

Sorry for making it unclear.

This bug is caused by fix for #16377. After discussion with Monty, fix for
#16377 was removed from trees. So this case is working again. But now if a use wants to use a string date/type constants he should cast them to date/time type.

Thank you for your attention to this.

> But now if a use[r] wants to use a string date/type
> constants he should cast them to date/time type.

It sounds like you're saying, "We reverted to the old behavior, but we reserve the right to resume this behavior at any time because the bug was in your code all along."

Is it difficult for MySQL to promote dates to datetimes when comparing the two?  Extending a date by assuming a time of "00:00:00" is a natural idea, similar to the way "3.2 between 3 and 4" works without casting.

If type promotion can't happen, then I believe that failing with an error message is far better than returning a nonsense result.

Or perhaps I am misunderstanding your comment.

This bug isn't an easy one and the correct fix will significantly affect the server internals. Thus, in order to preserve stability the correct fix will be available only in 5.2+.

Thank you very much for the clarification.