MySQL Bugs: #5054: Bug comparing date and datetime with 'between'

Bug #5054	Bug comparing date and datetime with 'between'
Submitted:	14 Aug 2004 2:49	Modified:	8 Jan 2014 13:17
Reporter:	Robert Nice	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	4.1.2	OS:	Linux (Linux FedCore 2)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
According to the manual between is the same as

min <= thing <= max

Not so when thing is a date field and min/max are datetime.

How to repeat:
create table t (tID int unsigned not null auto_increment, LastPaid date not null, primary key(tID));

create table s (sID int unsigned not null auto_increment, tID int unsigned not null, Entry datetime not null, Description varchar(255) not null, primary key(sID));

insert into s (tID, Entry, Description) values (1, '2004-01-20', 'Janaury Payday');
insert into s (tID, Entry, Description) values (1, '2004-02-20', 'February Payday');
insert into s (tID, Entry, Description) values (1, '2004-06-20', 'June Payday');
insert into s (tID, Entry, Description) values (1, '2004-07-20', 'July Payday');

Works:
select s.Entry, s.Description FROM t,s WHERE t.tID = s.tID AND s.Entry >= t.LastPaid AND s.Entry <= NOW();
+---------------------+-------------+
| Entry               | Description |
+---------------------+-------------+
| 2004-06-20 00:00:00 | June Payday |
| 2004-07-20 00:00:00 | July Payday |
+---------------------+-------------+
2 rows in set (0.00 sec)

Doesn't work, too many rows:
select s.Entry, s.Description FROM t,s WHERE t.tID = s.tID AND s.Entry BETWEEN t.LastPaid AND NOW();
+---------------------+-----------------+
| Entry               | Description     |
+---------------------+-----------------+
| 2004-01-20 00:00:00 | Janaury Payday  |
| 2004-02-20 00:00:00 | February Payday |
| 2004-06-20 00:00:00 | June Payday     |
| 2004-07-20 00:00:00 | July Payday     |
+---------------------+-----------------+
4 rows in set (0.00 sec)

Still broken (same output):
select s.Entry, s.Description FROM t,s WHERE t.tID = s.tID AND s.Entry BETWEEN DATE(t.LastPaid) AND DATE(NOW());

Suggested fix:
No idea.

Was able to verify with latest snapshot of 4.1 bk-tree.
(One should also do  "insert into t values (1, 20040301);" before running selects)

Hi!

The problem that in this case (and many others) MySQL compares datetime values as integers.
So instead of comparing for some row if '2004-03-01' between '2004-01-20 00:00:00' and 
'2004-08-17 22:00:00' we check if 20040301 between 20040120000000 and 20040817220000. 
Comparing datetimes as strings will help in this case but will break other cases...

Since it can be fixed easily in 4.1 branch but we have plans to do in later versions I am marking 
this bug as Deferred.

Possible work-around:
select s.Entry, s.Description FROM t,s WHERE t.tID = s.tID AND CAST(s.Entry AS DATETIME) BETWEEN t.LastPaid AND NOW();

The above should of course be

    Since it can NOT be fixed easily in 4.1 branch

:)

Sorry for confusion

Few things:

a) >= and <= works but 'between' doesn't, why?.
They should be syntactical sugar for the same thing. ie.

select CAST('2004-05-15' AS DATE) >= CAST('2004-05-01 00:00:00' AS DATETIME) AND CAST('2004-05-15' AS DATE) <= CAST('2004-05-30 23:59:59' AS DATETIME); /* works */
select CAST('2004-05-15' AS DATE) BETWEEN CAST('2004-05-01 00:00:00' AS DATETIME) AND CAST('2004-05-15' AS DATE) <= CAST('2004-05-30 23:59:59' AS DATETIME); /* doesn't */

b) My test case tried casting the right side to dates which should make them all the same type and it doesn't work, why?

I got php code and the customer want to find a range.
[code]
	if($_GET['datefrom'] && $_GET['dateto']) { 
									  	$sql_distinct_bis.=" AND (bis_date between ". str_replace("-","",ConvertToMySQLDate($datefrom))."  AND ". str_replace("-","",ConvertToMySQLDate($dateto)).")";
									  }
									  if($_GET['datefrom'] && empty($_GET['dateto'])) { 
									 	$sql_distinct_bis.=" AND bis_date='". str_replace("-","",ConvertToMySQLDate($datefrom))."'"; 
									  }
[/code]

My most point why we  must put  or replace "-" while mysql since 3.0  have two date variance either 2008-09-10 format or 20080910 format.This very critical for our customer

This isn't repeatable on recent 5.1, 5.5, 5.6 or trunk.