List: General Discussion « Previous MessageNext Message » From: Peter Zaitsev Date: July 28 2000 11:36am Subject: Re: Query with having does not work properly. View as plain text
```> 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.

```
Thread
Does MySQL handle CSV files in which records _don't_ end on a CR?Jason Haar15 Mar
• Re: Query with having does not work properly.sasha26 Jul
• Re: Query with having does not work properly.Peter Zaitsev27 Jul
• Re: Query with having does not work properly.Peter Zaitsev28 Jul
• Re: Query with having does not work properly.sasha28 Jul