MySQL Bugs: #9990: CHANGE IN BEHAVIOR OF GROUP BY WORKING BY PARSER

Bug #9990	CHANGE IN BEHAVIOR OF GROUP BY WORKING BY PARSER
Submitted:	19 Apr 2005 5:24	Modified:	25 Sep 2007 10:14
Reporter:	AJIT DIXIT	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	5.0.4	OS:	Linux (Linux)
Assigned to:	Geert Vanderkelen	CPU Architecture:	Any

Description:
I have a query as under 

   SELECT depots.stkdepot as locn , max(nmonth) as nmonth 
   from stktrnd 
   inner join depots on depots.depotid = stktrnd.locn 
   inner join item on item.code = stktrnd.item 
   where bin = 'GOOD' and canc = 0 and doc_dt != '000-00-00' and ts = 'SH' and item.type = 'P'
   group by locn   ;

This query was grouping on 'depots.stkdepot as locn' upto version mysql-4.1.11 and producing correct result
Now in Mysql-5.0.4 This query is grouping on stktrnd.locn field and producing incorrect result

The error comes in grouping when the field name in table is same as the alias name used in query for some different column , the grouping is done on table field name and not the result set

How to repeat:
Create condion as mentioned above and check results

Suggested fix:
Please make clear parsing sequence of GROUP BY clause in Documentation or change the behavior of GROUP BY clause as per 4.1.11

Hi Ajit,

Could you please send us additional data so we could reproduce this? The CREATE TABLE of the tables involved and if possible data?

Thanks,

Geert

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I experienced the same problem. I am on:

% mysql -V
mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (x86_64) using readline 5.0

To reproduce:

1) Create table:

CREATE TABLE `temp` (
  `person_id` int(10) NOT NULL,
  `group_id` int(10) default NULL
);

insert into temp values (1, 1), (2, null), (3, 1), (4, 4), (5, 4), (6, null);

2) Run query:

select 
    person_id,
  case when group_id is null then person_id else group_id end as group_id
    from temp
group by group_id
order by person_id asc

3) In mysql 4, it returns:

+-----------+----------+
| person_id | group_id |
+-----------+----------+
|         1 |        1 |
|         2 |        2 |
|         4 |        4 |
|         6 |        6 |
+-----------+----------+

In mysql 5, it returns:

+-----------+----------+
| person_id | group_id |
+-----------+----------+
|         1 |        1 |
|         2 |        2 |
|         4 |        4 |
+-----------+----------+

It looks like this is because GROUP BY is grouping by the actual column, and not
the alias name, as described by the original submitter.

FYI, the business case for this query might be: I have persons who may or may not be in a group. Give me all persons, but combine people in a group by returning only the first person in that group.

Thanks for the test case Gerard.

My dear colleague Sveta had to hit me with a clue bat :)

The result is more correct in 5.0.  MySQL is grouping by the column instead of the 'alias'. You can see that using SHOW WARNINGS. Best is to use a different name for the alias.