9 messages in com.mysql.lists.bugsRe: Query with having does not work p...| From | Sent On | Attachments |
|---|---|---|
| Peter Zaitsev | 25 Jul 2000 06:23 | |
| sas...@mysql.com | 25 Jul 2000 16:17 | |
| Michael Widenius | 26 Jul 2000 03:07 | |
| Peter Zaitsev | 27 Jul 2000 05:38 | |
| Benjamin Pflugmann | 27 Jul 2000 10:24 | |
| Peter Zaitsev | 28 Jul 2000 04:36 | |
| Benjamin Pflugmann | 28 Jul 2000 09:49 | |
| sas...@mysql.com | 28 Jul 2000 15:45 | |
| Michael Widenius | 29 Jul 2000 11:08 |
| Subject: | Re: Query with having does not work properly. |
|---|---|
| From: | Peter Zaitsev (pz...@spylog.ru) |
| Date: | 07/28/2000 04:36:03 AM |
| List: | com.mysql.lists.bugs |
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:
I'm just basing on
X=(DATE_SUB(DATE_ADD(X,Y)); X=(DATE_ADD(DATE_SUB(X,Y));
Let's say The exclusion is invalid X (or Y), in this case it should in my expression we come to comparement od two dates (the thing is they probably really are converted to integer before, but that's another story) so we have
NULL<DATE or DATE<NULL in expressions (NULL means invalid date as expression result) - this expressions should both return null if we're threatning 0 as invalid date, but my examples shows 0 is sometime threated as valid date in compare functions and sometimes not, that's not too good...
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).
That's other thing.
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.
Ok. I found this solutions, I just wanted to point as some cases there mysql arithmetics with it's implicit conversions has some problems, at my oppinion.