> That is the case. Without a GROUP BY, max() is supposed to apply to the > entire
> table.
Not true. Use of MAX in WHERE clause will show that MAX is applied to
filtered data set. To use my example data set:
mysql> select f2 from tt where f1=2;
+------+
| f2 |
+------+
| 3 |
+------+
1 row in set (0.02 sec)
> In ANSI-compliant SQL, I do not think that it is legal to compare f2 to >max(f2),
> because using an aggregate function works only after summarization, >while referring
> to the detail works only before summarization. I am surprised >that MySQL accepts
> the syntax at all.
Syntax wise I agree that most SQL interpretors restrict usage on HAVING
to follow GROUP BY. Usage of aggregate functions (like MAX) in HAVING is
permitted AFAIK.
> The claim that HAVING is executed after WHERE is usually correct (always, >in
> ANSI-compliant SQL, I believe). I think that MySQL has extended the >syntax in a
> useful but non-standard way which results in it violating this rule.
Resulting in unexpected results I may add.
> The "normal" flow would be: 1) Evaluate and apply WHERE clause 2) >Summarize data
> according to the GROUP BY, or if there is no GROUP BY >generate a single summary row
> for the entire table. 3) Evaluate and apply >HAVING clause. This *cannot* be used in
> the query you have above, because >f2 on
its own is only meaningful before summarization and max(f2) is only >meaningful after
summarization, yet you are comparing them. In order to >evaluate this, MySQL is
apparently doing the equivalent of a subquery: 1) >Evaluate max(f2) for the entire
table as a psuedo-subquery and remember the
>result 2) Evaluate and apply WHERE clause to table f2. 3) Since there is no >more
>summarization to be done, treat the HAVING as a WHERE clause, and >evaluate and apply
>it to the individual rows, using result remembered from (1).
Use of a subquery would solve the problem - that's clear. What it isn't so
clear is the result of the query. Acording to the workflow you described the
results should be f2=4 and yet the result is an empty set.
>And those are the conclusions I draw. Bruce Feist
I'd say either HAVING need to be fixed or syntax should be changed to
allow only "legal" cases where the results are dependable. To play "guess what
may happen if I write this" (ala vi initiation ritual) with databases worth big
money is not my cup of tea.
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
