Hi!
On Mar 08, Gabriel TATARANU wrote:
> >
> > 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.
No, not with mine :)
mysql> select * from tt where f1=1;
+------+------+
| f1 | f2 |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 4 |
+------+------+
3 rows in set (0.01 sec)
mysql> select *,max(f2) from tt where f1=1 group by 1+1;
+------+------+---------+
| f1 | f2 | max(f2) |
+------+------+---------+
| 1 | 1 | 4 |
+------+------+---------+
1 row in set (0.00 sec)
same as above, f2 can take any value from {1,2,4} - and it gets the
value 1.
> 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.
As for join - it will be not a warning, but an error:
mysql> select * from t1 where tt.f1=1;
ERROR 1109: Unknown table 'tt' in where clause
As for GROUP BY...
Implicit GROUP BY is very often used in e.g.
SELECT COUNT(*) FROM table;
or
SELECT MAX(col) FROM table;
And there is --ansi mode that won't allow using "undefined" values (in
the above sence) - in fact no column that is not present in GROUP BY
(even f1, though we know it's well defined):
mysql> select * from tt where f1=1 group by 1+1 having max(f2)=f2;
ERROR 1055: 'tt.f1' isn't in GROUP BY
It could be a problem if it were ... WHERE f1 IN (1,2) ...
But it this particular case f1 is constant and could be added to
GROUP BY.
mysql> select * from tt where f1=1 group by f1 having max(f2)=f2;
ERROR 1055: 'tt.f2' isn't in GROUP BY
So your original query would be impossible.
What I found inconsistent is that ansi mode does not catch implicit
GROUP BY const:
mysql> select * from tt where f1=1 having max(f2)=f2;
Empty set (0.00 sec)
Probably, it should be fixed.
> > 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.
Yes, I agree - I pass it to our doc team.
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
