Bug #20984 Error when grouping columns

• Submitted: 12 Jul 2006 3:51
• Modified: 12 Jul 2006 10:40
• Reporter: Marcelo Machado
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.21
• OS: Windows (windows / linux)
• CPU Architecture: Any
• Tags: group by;

[12 Jul 2006 3:51] Marcelo Machado

Description:
When running a querie with group by clause, and selectiong a extra field mysql don't return error.

How to repeat:
Create 2 tables, city and customer. One city has many customers and a customer has only 1 city.

Create table city (
citycode int primary key,
cityname varchar(50));

create table customer (
customercode int primary key,
customername varchar(50),
customercitycode int not null,
foreign key (customercitycode) references city(citycode)
);

Then insert some records in each table and run the querie:

Select cityname, customername, count(*) 
from city inner join customer on citycode=customercitycode
group by cityname;

The field customername is not being aggregated and is not part of the group by, but no error is returned by mysql returns a customername to each city;

Suggested fix:
ANSI SQL does not permit that kind of sintax.

[12 Jul 2006 10:40] Valeriy Kravchuk

Thank you for taking the time to write to us, but this is not a bug. Please double-check the manual, http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html:

"MySQL extends the use of GROUP BY so that you can use non-aggregated columns or calculations in the SELECT list that do not appear in the GROUP BY clause. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. For example, you do not need to group on customer.name in the following query: 

SELECT order.custid, customer.name, MAX(payments)
  FROM order,customer
  WHERE order.custid = customer.custid
  GROUP BY order.custid;

In standard SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the name is redundant. 

Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."