Hi,
> No, not with mine :)
Well , you have the wrong data. Let me post the whole thing to let
people what I'm talking about.
mysql> select * from tt;
+------+------+
| f1 | f2 |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 1 | 4 |
+------+------+
4 rows in set (0.01 sec)
mysql> select f2 from tt where f1=1 and f2=max(f2);
ERROR 1111: Invalid use of group function
mysql> select f2 from tt where f1=1 having f2=max(f2);
Empty set (0.02 sec)
> As for join - it will be not a warning, but an error:
I'm ok with that. In fact WHERE does that as shown;
>
> mysql> select * from t1 where tt.f1=1;
> ERROR 1109: Unknown table 'tt' in where clause
I guess thae warning was wen using fields from another table that
wasn't in the table list.
>
> As for GROUP BY...
> Implicit GROUP BY is very often used in e.g.
>
> SELECT COUNT(*) FROM table;
>
> or
>
> SELECT MAX(col) FROM table;
It's interesting to explain why the need to GROUP BY in these
cases. More so, why HAVING seems to have a problem with that.
> 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.
That's a bit unclear. Once there is no GROUP BY constant and
now it is.
> 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.
He, he. Would be is the word here
> 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.
I'd say the sooner the better, otherwise unexpected results may
arrise.
>
> Yes, I agree - I pass it to our doc team.
So at this point we're pretty sure the syntax interpretor in not
rejecting an invalid query format. This is reason enough to alert the mysql
bug fixing team.
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
