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: | Benjamin Pflugmann (phil...@spin.de) |
| Date: | 07/28/2000 09:49:01 AM |
| List: | com.mysql.lists.bugs |
Hi.
Well, in short, MySQL has its own system, and I am not sure it tries to be really mathematical correct. But will try to show you that your equation isn't even necessarily true in a mathematical sense (sorry for my english, but I did not need until now to express mathematical formulas in english)
On Fri, Jul 28, 2000 at 03:36:25PM +0400, pz...@spylog.ru wrote:
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));
Ok. Let's see. First, you have some typos, IMHO. I think you meant the following:
X = DATE_SUB( DATE_ADD(X,Y), Y ); X = DATE_ADD( DATE_SUB(X,Y), Y );
Let's say The exclusion is invalid X (or Y),
Sorry, but I was not able to find out, what "exclusion" means in this context.
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...
That's the same as to expect that
X = SQRT( X^2 ) (a) X = SQRT(X) ^ 2 (b)
are both defined or undefined. That isn't the case. Within the scope of real numbers, for X = -1, SQRT(X) is not defined at all (the same holds true for DATE_ADD(0, ...) above), so the result of (a) is undefined. While the result of (b) is well defined and just results to 'false'.
That both are defined or undefined would only be true, if both functions are reversible(word?).
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.
No, not really. Given that you considered DATE_SUB to be the reversible function of DATE_ADD, the equation holds, IMHO. It's just that linear functions are reversible, but not all reversible functions are linear. ;-)
I admit, a it was a bad example. :)
[...]
The problem is that you are working with dates and integers and although in MySQL dates are a subset of integers, you may only expect your expression to be changeable in the way you want, if the functions (projections?) do allow this. Which obviously isn't the case for DATE_SUB/DATE_ADD.
Bye,
Benjamin.
PS: Removed Sascha und Monty from the recipients, because you both will get the mail several times anyhow. I did not want to cancel the cross posts to the lists without further notice...