MySQL Bugs: #25718: group by with alias in expression (follow up bug #11694)

Bug #25718	group by with alias in expression (follow up bug #11694)
Submitted:	19 Jan 2007 12:32	Modified:	19 Jan 2007 13:57
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.22 and 5.0.27	OS:
Assigned to:		CPU Architecture:	Any
Tags:	alias, GROUP BY

Description:
This is the same as in bug #11694. It seems not completly fixed.

If you alias a constant expression instead of a field, you can use the alias standalone, but not as part of an expression.

Same applies, for aliases on an expression / order by

all but the 2 first selects in how to repeat fail:
ERROR 1054 (42S22): Unknown column 'an_alias' in 'group statement'

How to repeat:
drop table if exists t1;
create table t1 (f1 int);

select f1 as an_alias from t1 group by an_alias;
select f1 as an_alias from t1 group by 20 - an_alias;

select 100 as an_alias from t1 group by 20 -  an_alias;
select f1, 100 as an_alias from t1 group by 20 - an_alias;

select (f1 = 100) as an_alias from t1 group by 20 - an_alias;
select (f1 + 100) as an_alias from t1 group by 20 - an_alias;

select (f1 + 100) as an_alias from t1 order by 20 - an_alias;

drop table if exists t1;

Suggested fix:
-

Thank you for a problem report. Sorry, but I am not able to repeat this on latest 5.0.36-BK on Linux. Both test case for bug #11694 and your one works:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists foo;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE foo ( i int(10) );
Query OK, 0 rows affected (0.01 sec)

mysql>  INSERT INTO foo (i) VALUES (10), (20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>  SELECT i AS BAR FROM foo ORDER BY BAR;
+------+
| BAR  |
+------+
|   10 |
|   20 |
+------+
2 rows in set (0.00 sec)

mysql>  SELECT i AS BAR FROM foo ORDER BY 30 - i;
+------+
| BAR  |
+------+
|   20 |
|   10 |
+------+
2 rows in set (0.01 sec)

mysql>  SELECT i AS BAR FROM foo ORDER BY 30 - BAR;
+------+
| BAR  |
+------+
|   20 |
|   10 |
+------+
2 rows in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (f1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> select f1 as an_alias from t1 group by an_alias;
Empty set (0.01 sec)

mysql> select f1 as an_alias from t1 group by 20 - an_alias;
Empty set (0.00 sec)

mysql> select 100 as an_alias from t1 group by 20 -  an_alias;
Empty set (0.00 sec)

mysql> select f1, 100 as an_alias from t1 group by 20 - an_alias;
Empty set (0.00 sec)

mysql> select (f1 + 100) as an_alias from t1 group by 20 - an_alias;
Empty set (0.00 sec)

mysql> select (f1 + 100) as an_alias from t1 order by 20 - an_alias;
Empty set (0.01 sec)

Same result (it works) - with 4.1.23-BK:

openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (f1 int);

Query OK, 0 rows affected (0.01 sec)

mysql> select f1 as an_alias from t1 group by an_alias;
Empty set (0.00 sec)

mysql> select f1 as an_alias from t1 group by 20 - an_alias;
Empty set (0.00 sec)

mysql> select 100 as an_alias from t1 group by 20 -  an_alias;
Empty set (0.00 sec)

mysql> select f1, 100 as an_alias from t1 group by 20 - an_alias;
Empty set (0.00 sec)

mysql> select (f1 = 100) as an_alias from t1 group by 20 - an_alias;
Empty set (0.00 sec)

mysql> select (f1 + 100) as an_alias from t1 group by 20 - an_alias;
Empty set (0.00 sec)

mysql> select (f1 + 100) as an_alias from t1 order by 20 - an_alias;
Empty set (0.00 sec)

I got the bug when I enclose the alias in single or double quotes (some of my aslias names have spaces in them.)  The bug shows up in MySQL 5.1.11 but not MySQL 5.0.22:

drop table if exists foo;
CREATE TABLE foo ( i int(10) );
INSERT INTO foo (i) VALUES (10), (20);
SELECT i AS 'BAR' FROM foo GROUP BY 'BAR';