> > >Description:
> > HAVING in SELECT is evaluated before WHERE. This is not the
> > correct behaviour as HAVING should deal with the remainder of the WHERE
> > selection (according to manual)
>
> I cannot understand how the following can show that "HAVING in SELECT is
> evaluated before WHERE"...
What I ment here is that HAVING is not evaluated last. That was
my first reaction to the problem. I guess my first analysis of the problem
was not the best one :-(.
>
> First - about some other replies in this thread.
> Of course, MAX applies only to part of the table from WHERE, not to the
> whole table. Try
That is correct.
>
> SELECT MAX(f2) FROM tt WHERE f1=1;
>
> Then, when you use group functions without explicit GROUP BY it's the
> same as GROUP BY const - that is, group functions are applied to the
> whole table (or, rather, sub-table after the WHERE).
Ok, so we're deducting here that WHERE is applied and the
subset resulted is subjected to group functions. All well so far.
> And, of course, GROUP BY const (you may try to add it manually, to see
> what happens) will resul in only one row to be returned.
> And - absolutely according to SQL standards - what value each
> column will have (from the set of values it takes in the result set from
> WHERE) is *undefined*.
>
> Try:
>
> SELECT *,MAX(f2) FROM tt WHERE f1=1 GROUP BY 1+1;
>
> (check GROUP BY syntax to see why I didn't write simply GROUP BY 1)
>
> You will get
>
> +------+------+---------+
> | f1 | f2 | max(f2) |
> +------+------+---------+
> | 1 | 1 | 2 |
> +------+------+---------+
> 1 row in set (0.00 sec)
>
> Note, that max(f2) is correct, and f2's value can be either 1 or 2, in
> this case it happen to be 1. Naturally, 2 != 1, so your query fails.
That make sense. This is a very good answer to my original post.
But wait, read my previos post. I changed the data set, added a fourth
record (1,4) and did the same thing and got nothing. That disagree with
all the posts so far, including mine.
Even without that, should mysql allow implicit GROUP BY without
any warning ? My feeling is that mysql should at least warn the user
about a possible pitfall (IIRC there is a warning about implicit join when a
table is used in WHERE but not declared in the table list) or disallow the
syntax altogether.
> You should NEVER rely on the value of column from GROUP BY,
> if it's not the column you group by.
That's a very good statement. It should be included at least in the
documentation of mysql.
A very good quality post Sergei. I can't wait to hear your analysis of
the latest data set.
Regards,
Gabriel
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
