9 messages in com.mysql.lists.bugsRe: Query with having does not work p...
FromSent OnAttachments
Peter Zaitsev25 Jul 2000 06:23 
sas...@mysql.com25 Jul 2000 16:17 
Michael Widenius26 Jul 2000 03:07 
Peter Zaitsev27 Jul 2000 05:38 
Benjamin Pflugmann27 Jul 2000 10:24 
Peter Zaitsev28 Jul 2000 04:36 
Benjamin Pflugmann28 Jul 2000 09:49 
sas...@mysql.com28 Jul 2000 15:45 
Michael Widenius29 Jul 2000 11:08 
Subject:Re: Query with having does not work properly.
From:Benjamin Pflugmann (phil@spin.de)
Date:07/27/2000 10:24:02 AM
List:com.mysql.lists.bugs

Hi.

On Thu, Jul 27, 2000 at 04:38:38PM +0400, pz@spylog.ru wrote:

Your patch looks ok, but I have now split the date add/sub code to make it faster and shorter; This will be in 3.23.22 (I don't submit a patch now as I want to test this throughly before releasing it!)

I'm not shure if this is allready a source of this problems, but currently such thing is happening:

mysql> select date_add(0, interval 7 day)<20000101; +--------------------------------------+ | date_add(0, interval 7 day)<20000101 | +--------------------------------------+ | NULL | +--------------------------------------+ 1 row in set (0.01 sec)

mysql> select 0<date_sub(20000101,interval 7 day); +-------------------------------------+ | 0<date_sub(20000101,interval 7 day) | +-------------------------------------+ | 1 | +-------------------------------------+ 1 row in set (0.00 sec)

Therefore this selects are mathematically equal.

That's not true. You assume that DATE_ADD/SUB and '<' are linear(word?) functions, which they aren't. What I mean is more obvious with an another example:

1 + CONCAT("1","0") is not the same as CONCAT(1+"1","0"). The one results in 11, the other in 20. Only with certain kind of functions the equation f( g(x,y) ,z ) = g( x, f(y,z) ) is true (you use this implicitly).

Within DATE_ADD '0' is interpreted as date and considered invalid, so adding an interval to it gives NULL. The comparison with NULL (against anything, but here it is a number) also results in NULL. As you said, this is somthing to be expected.

What does the second select do? 20000101 (an integer) is interpreted by DATE_SUB as date, which will result in a valid one, then 7 days are substracted, of which the result is still a valid date. Then this date has to be compared against a number and therefore it is converted to one, which is also valid. The comparison of the two numbers results in true, so 1 is returned.

Okay, now where is the difference? In one case, 0 is converted to a date (forced by DATE_ADD), in the other case the given date is converted to an integer to be compared with the 0.

You may want to argue, that instead in the second case the 0 should be converted to a date, too. But it is easy to construct a case, where this behaviour would be the wrong. MySQL has an internal "casting" convention and if you need to differ from it, you have to tell MySQL explictly what you ant.

For example (there will be a better method, but I am too lazy to look), you can force both sides of the comparison to be of type date this way:

SELECT DATE_ADD(0, INTERVAL 0 DAYS) < DATE_SUB( 20000101, INTERVAL 7 DAY );

And you will get the expected NULL.

Bye,

Benjamin.