Bug #47434 Ambigous field name in group by clause does not cause an error
Submitted: 18 Sep 2009 10:18 Modified: 23 Sep 2009 4:05
Reporter: Jordi Salvat i Alabart Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.51a-24+lenny1-log, 5.1.38 OS:Linux
Assigned to: CPU Architecture:Any

[18 Sep 2009 10:18] Jordi Salvat i Alabart
If the column name used in a group by clause is ambiguous, the parser doesn't warn about the ambiguity.

Because it does in other cases, the user can reasonably expect it will and skip checking for duplicate naming, resulting in an incorrect query.

How to repeat:
Steps to reproduce:

create table tst (a int, b int);
select a b from tst group by b;
select t1.a from tst t1, tst t2 group by a;
select a from tst t1, tst t2;
drop table tst;

Expected result:

All three SELECT statements will throw an error indicating that 'a' is ambiguous.

Actual result:

Only the third one throws such an error.
[18 Sep 2009 10:46] Valeriy Kravchuk
Indeed, this looks inconsistent:

mysql> create table tst (a int, b int);
Query OK, 0 rows affected (0.14 sec)

mysql> select t1.a from tst t1, tst t2 group by a;
Empty set (0.00 sec)

mysql> select a from tst t1, tst t2;
ERROR 1052 (23000): Column 'a' in field list is ambiguous
mysql> select a b from tst group by b;
Empty set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1052
Message: Column 'b' in group statement is ambiguous
1 row in set (0.00 sec)
[22 Sep 2009 23:31] Omer Barnir
The MySQL extension to SQL GROUP BY first looks up identifiers in the SELECT list, and only if not found there looks to the cross-product of tables
[23 Sep 2009 4:05] Valeriy Kravchuk
Still it does NOT explain why we have only warning about ambiguous column in case of GROUP BY that uses MySQL extension, not error message. This is inconsistent, IMHO.