Hi! On Mar 07, [EMAIL PROTECTED] wrote: > >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"... > >How-To-Repeat: > mysql: create table tt (f1 int, f2 int); > Query OK, 0 rows affected (0.01 sec) > > mysql: insert into tt values(1,1); > Query OK, 1 row affected (0.01 sec) > > mysql: insert into tt values(1,2); > Query OK, 1 row affected (0.00 sec) > > mysql: insert into tt values(2,3); > Query OK, 1 row affected (0.00 sec) > > mysql: select * from tt where f1=1 having f2=max(f2); > Empty set (0.00 sec) 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 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). 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. You should NEVER rely on the value of column from GROUP BY, if it's not the column you group by. Regards, Sergei -- MySQL Development Team __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ --------------------------------------------------------------------- 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
