MySQL Bugs: #61943: Syntax to specify a field from select

Bug #61943	Syntax to specify a field from select_expr for Group By
Submitted:	21 Jul 2011 13:16	Modified:	7 Jul 2012 17:24
Reporter:	Sergei Kulakov (Candidate Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.5.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Starting from version 5, MySQL resolves unqualified column or alias references in GROUP BY clauses by searching in the FROM clause before searching in the select_expr values. To me this is a lot of trouble. I can use column positions for now but they are deprecated. I want a syntax to specify that I want that field to be taken from the Select list. 

Of course I can repeat the expressions that I use in Select in the Group By part but that will look cumbersome and I'm not sure MySql will optimize that right. 

How to repeat:

create table groupby(s varchar(32));
insert into groupby values ('a'), ('b'), ('c');
mysql> select Upper(s) as s from groupby group by s;
+------+
| s    |
+------+
| A    |
| B    |
| C    |
+------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1052 | Column 's' in group statement is ambiguous |
+---------+------+--------------------------------------------+

mysql> select Upper(s) as s from groupby group by 1;
+------+
| s    |
+------+
| A    |
| B    |
| C    |
+------+
3 rows in set (0.00 sec)  (no warnings here!)

mysql> select Upper(s) as s from groupby group by Upper(s);
+------+
| s    |
+------+
| A    |
| B    |
| C    |
+------+
3 rows in set (0.00 sec) (no warnings again but cumbersome)

Suggested fix:
I want a way to specify that I want to group by "Upper(s) as s" from the select list like this: 

select Upper(s) as s from groupby group by select.s;

Do not suggest that I rename the alias to something else because in this example it seems easy to do but in much more complicated queries it causes a lot of troubles!

With two possible workarounds (use numeric position of filed and use unique alias) and known for years behavior of MySQL in this case, I do not think there are many chances to see this request implemented any time soon...

Note that the documentation says numeric positions are deprecated. Eventually they may get dropped! In that case the number of workarounds decrements to 1. also, numeric positions are not very nice. 

And I do not sugggest to change the behavour of MySql, I want to extend its syntax. If you use the usual syntax the behaviour does not change. I want to HAVE A CHOICE to select which field I group by.

Also, the mere fact that you have to use workarounds to do what you want because the native syntax does not allow you to achieve that is wrong. 

I suggest to extend the syntax to give more flexibility and use no workarounds. This may also beapplied to ORDER BY and HAVING clauses. Why not? The default behaviour is good but why not have a choice?!