14 messages in com.mysql.lists.mysqlRe: HAVING behaviour
FromSent OnAttachments
tga...@belent.com07 Mar 2003 05:51 
gerald_clark07 Mar 2003 06:52 
Gabriel Tataranu07 Mar 2003 07:19 
gerald_clark07 Mar 2003 12:49 
Gabriel Tataranu07 Mar 2003 13:30 
Uttam07 Mar 2003 22:26 
Bruce Feist08 Mar 2003 07:09 
Gabriel TATARANU08 Mar 2003 11:59 
Sergei Golubchik08 Mar 2003 14:35 
Gabriel TATARANU08 Mar 2003 17:48 
Gabriel TATARANU08 Mar 2003 20:32 
Sergei Golubchik09 Mar 2003 02:53 
Gabriel TATARANU09 Mar 2003 09:06 
Uttam09 Mar 2003 23:07 
Subject:Re: HAVING behaviour
From:Gabriel TATARANU (tga@belent.com)
Date:03/08/2003 05:48:25 PM
List:com.mysql.lists.mysql

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