MySQL Bugs: #47434: Ambigous field name in group by clause does not cause an error

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:	None
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

Description:
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.

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)

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

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.