Wierd query result on MySQL 5 system

[Paul Lautman]

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 ));

[Paul Lautman]

To see the problem first hand, I have set up
[url]http://www.sunnyacres.co.uk/bb/bbqt.php[/url]
where you can run the query below to see the results.
You can also run the query

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 that the expiry_date is not within the bounds.

Paul Lautman wrote:

> 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`