MySQL Bugs: #6818: undocumented change in alias behavior

Bug #6818	undocumented change in alias behavior
Submitted:	25 Nov 2004 1:42	Modified:	26 Nov 2004 7:35
Reporter:	Michael Stassen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
As originally reported on the mail list by Geoffrey R. Thompson <http://lists.mysql.com/mysql/
176269>, one used to be able to refer to a column using table_alias.column_alias, but not in 
4.1.7.  That is, 

  SELECT t1.col1 AS c1 FROM table1 t1 GROUP BY t1.c1;

used to work, but gives an error in 4.1.7.  I could not find anywhere this change in behavior is 
documented.

How to repeat:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 4.0.20    |
+-----------+
1 row in set (0.01 sec)

mysql> CREATE TABLE alias_bug(g CHAR(3), val INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO alias_bug 
    -> VALUES ('one',4), ('one', 8), ('one', 16), ('two', 5);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT ab.g AS ag, MAX(val) FROM alias_bug ab GROUP BY ab.ag;
+------+----------+
| ag   | MAX(val) |
+------+----------+
| one  |       16 |
| two  |        5 |
+------+----------+
2 rows in set (0.00 sec)

mysql> SELECT ab.g AS ag, val FROM alias_bug ab ORDER BY ab.ag;
+------+------+
| ag   | val  |
+------+------+
| one  |    4 |
| one  |    8 |
| one  |   16 |
| two  |    5 |
+------+------+
4 rows in set (0.00 sec)

#####################

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 4.1.7     |
+-----------+
1 row in set (0.01 sec)

mysql> CREATE TABLE alias_bug(g CHAR(3), val INT);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO alias_bug 
    -> VALUES ('one',4), ('one', 8), ('one', 16), ('two', 5);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT ab.g AS ag, MAX(val) FROM alias_bug ab GROUP BY ab.ag;
ERROR 1054 (42S22): Unknown column 'ab.ag' in 'group statement'

mysql> SELECT ab.g AS ag, val FROM alias_bug ab ORDER BY ab.ag;
ERROR 1054 (42S22): Unknown column 'ab.ag' in 'order clause'

As far as i can tell it was sort of a bug that it worked in 4.0 at all
as aliases are defined on the query and not on the table level.

I'm changing this to Documentation category

From the original post, it worked in 3.23 as well, which suggests that it has always worked until 4.1.  I agree that it is strange that it worked, so treating it as a prior bug which is now fixed makes sense to me, but the change wasn't documented (as far as I can tell), hence the bug report.  Also, if it is a bug to be fixed, note that it has not been fixed in 4.0.20.  Is it fixed in the latest 4.0?

Hi, Michael!

This change appeared in MySQL 4.1.4. It was done as part of fix for bug #4302.
The old behavior was non-standard and caused problems for some queries.
(E.g.: SELECT id, calculate_price(price) as price FROM items ORDER BY items.price )   

I have added note about this change to our release history 
http://dev.mysql.com/doc/mysql/en/News-4.1.4.html 

So far we don't have plans to fix it in 4.0 branch.

Thank you spotting this and sorry about being so late with this note!!!