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

[19 Apr 2005 5:24] AJIT DIXIT
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
[28 Apr 2005 8:14] Geert Vanderkelen
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?


[28 May 2005 23:00] Bugs System
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".
[24 Sep 2007 21:51] Gerard Sychay
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:

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

  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.
[25 Sep 2007 10:14] Geert Vanderkelen
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.