MySQL Bugs: #19541: Incorrect result set with date

Bug #19541	Incorrect result set with date_add
Submitted:	4 May 2006 18:08	Modified:	5 May 2006 11:25
Reporter:	Paul Lautman	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.19	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I have the following table (complete with sample data).
When I run the query below on a mysql 4.1.14 system, the only row returned
is row 4 as expected.
However, when I run the same query on a mysql 5.0.19 system, I get rows 2, 3
& 4; which is not what I would expect.
I have found that if I replace the "date_add( Reminder_Sent, INTERVAL 7
DAY )" with a hard coded '2006-01-15', then I get the expected row 4 only,
but that defeats the object of the query as the dates in Reminder_Sent are
not normally always the same.

Can anyone point me to what is going on here?

TIA
Regards
Paul Lautman

CREATE TABLE `balls_in_play2` (
  `Ball_Number` tinyint(2) NOT NULL default '0',
  `Name` varchar(50) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `Expiry_Date` date NOT NULL default '0000-00-00',
  `Reminder_Sent` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Ball_Number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `balls_in_play2`
--

INSERT INTO `balls_in_play2` (`Ball_Number`, `Name`, `email`, `Expiry_Date`,
`Reminder_Sent`) VALUES
(1, 'John Doe', '', '2007-03-31', '2006-01-01'),
(2, 'Hilary Smith', '', '2006-11-04', '2006-01-01'),
(3, 'Wendy Jones', '', '2006-10-28', '2006-01-01'),
(4, 'Fred Brown', '', '2006-05-06', '2006-01-01');

SELECT *
FROM   balls_in_play2
WHERE (expiry_date BETWEEN date_add( Reminder_Sent, INTERVAL 7 DAY ) AND
date_add( current_date, INTERVAL 7 DAY ));

How to repeat:
Load a table with the supplied data and run the supplied query.

I have done further testing and discovered that the bug only seems to manifest itself when using BETWEEN ... AND ...
I tried changing the query to use <= and >= and then it works as expected.
I have created http://www.sunnyacres.co.uk/bb/bbqt.php
so that you can try various queries to see the problem first hand. I tried the following queries:
To see the problem:
SELECT *
FROM   balls_in_play2
WHERE (expiry_date BETWEEN date_add( Reminder_Sent, INTERVAL 7 DAY ) AND
date_add( current_date, INTERVAL 7 DAY ));

To see that the expiry date is not withing the BETWEEN range:
SELECT *, date_add( Reminder_Sent, INTERVAL 7 DAY ), date_add( current_date,
INTERVAL 7 DAY ) FROM balls_in_play2 WHERE (expiry_date BETWEEN date_add(
Reminder_Sent, INTERVAL 7 DAY ) AND date_add( current_date, INTERVAL 7
DAY ));

To see it work correctly using <= and =>:
SELECT *
FROM   balls_in_play
WHERE (expiry_date >= date_add( Reminder_Sent, INTERVAL 7 DAY ) AND expiry_date <= date_add( current_date, INTERVAL 7 DAY ))
OR (expiry_date >= date_add( Reminder_Sent, INTERVAL 14 DAY ) AND expiry_date <= date_add( current_date, INTERVAL 14 DAY ))

Hope this helps

duplicate of Bug #14360, fixed in 5.0.21

Well it doesn't seem to have all the defining factors of Bug #14360, but if you reckon it's fixed with it then that's fine.