MySQL Bugs: #69678: General error #1356 when querying VIEW with an ORDER BY clause using an ALIAS

Bug #69678	General error #1356 when querying VIEW with an ORDER BY clause using an ALIAS
Submitted:	5 Jul 2013 14:23	Modified:	18 Nov 2013 15:19
Reporter:	David Coyle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.12	OS:	MacOS (10.8.4)
Assigned to:		CPU Architecture:	Any

Description:
It is not possible to query a VIEW with an ORDER BY clause that references an ALIAS in the SELECT clause of the VIEW definition unless all columns in the view are requested, see "How to repeat".

This affects MySQL Server 5.6.10 and 5.6.12 running on Mac OS X 10.8.4 installed via HomeBrew.

The steps in "How to Repeat" show the most basic way to recreate the bug when directly querying a view this bug becomes more of an issue when the view is part of a join and there are matching column names on both sides of the join and SELECT * would result in ambiguous columns.

The first workaround is to order by the real column name rather than the alias.

If the alias is an expression e.g.
 (a.my_apples/b.total_apples) AS avg_apples
Then the ORDER BY clause in the view has to use the expression rather than the ALIAS e.g.
ORDER BY (a.my_apples/b.total_apples)
in order to work.  This is fine for simple queries but for complex expressions this duplication of code is a maintenance headache because it may not be immediately obvious and breaks DRY principles.

How to repeat:
#This works fine
SELECT <something>, 1 AS `my_alias`
FROM <somewhere>
ORDER BY `my_alias`

#Use the same query in a view
#The view is created with no errors
CREATE VIEW `v_test` as
(
    SELECT <something>, 1 AS `my_alias`
    FROM <somewhere>
    ORDER BY `my_alias`
)

#Query the view
#This works fine
SELECT *
FROM `v_test` `t`

#Be more specific with the columns in the result set
#This produces an error
SELECT `t`.`my_column`
FROM `v_test` `t`

#1356 - View 'my_db.v_test' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Suggested fix:
Enable a VIEW to be queried without error when the ORDER BY clause contains an ALIAS.

This looks like a regression bug, as it works as expected in 5.5.32:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.32    |
+-----------+
1 row in set (0.02 sec)

mysql> select user, 1 as my_alias from mysql.user order by my_alias;
+---------+----------+
| user    | my_alias |
+---------+----------+
| newuser |        1 |
| root    |        1 |
| root    |        1 |
|         |        1 |
| root    |        1 |
| u1      |        1 |
+---------+----------+
6 rows in set (0.15 sec)

mysql> create view v_test as select user, 1 as my_alias from mysql.user order by
 my_alias;
Query OK, 0 rows affected (0.19 sec)

mysql> select * from v_test t;
+---------+----------+
| user    | my_alias |
+---------+----------+
|         |        1 |
| newuser |        1 |
| root    |        1 |
| root    |        1 |
| root    |        1 |
| u1      |        1 |
+---------+----------+
6 rows in set (0.04 sec)

mysql> select t.user from v_test t;
+---------+
| user    |
+---------+
|         |
| newuser |
| root    |
| root    |
| root    |
| u1      |
+---------+
6 rows in set (0.00 sec)

mysql> select t.my_alias from v_test t;
+----------+
| my_alias |
+----------+
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
+----------+
6 rows in set (0.00 sec)

mysql> exit
Bye

But fails with 5.6.x:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user, 1 as my_alias from mysql.user order by my_alias;
+------+----------+
| user | my_alias |
+------+----------+
| root |        1 |
| root |        1 |
| root |        1 |
+------+----------+
3 rows in set (0.00 sec)

mysql> create view v_test as select user, 1 as my_alias from mysql.user order by
 my_alias;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from v_test t;
+------+----------+
| user | my_alias |
+------+----------+
| root |        1 |
| root |        1 |
| root |        1 |
+------+----------+
3 rows in set (0.00 sec)

mysql> select t.user from v_test t;
ERROR 1356 (HY000): View 'test.v_test' references invalid table(s) or column(s)
or function(s) or definer/invoker of view lack rights to use them
mysql> select t.my_alias from v_test t;
+----------+
| my_alias |
+----------+
|        1 |
|        1 |
|        1 |
+----------+
3 rows in set (0.00 sec)

Test case for copy/paste:

select user, 1 as my_alias from mysql.user order by my_alias;
create view v_test as select user, 1 as my_alias from mysql.user order by
select * from v_test t;
select t.user from v_test t;
select t.my_alias from v_test t;

Sorry, missed one line in the test:

select user, 1 as my_alias from mysql.user order by my_alias;
create view v_test as select user, 1 as my_alias from mysql.user order by my_alias;
select * from v_test t;
select t.user from v_test t;
select t.my_alias from v_test t;

Hello David,
 
Thank you for the bug report. 
Verified as described.

Thanks,
Umesh

// 5.5.32

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.32-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select user, 1 as my_alias from mysql.user order by my_alias;
+------+----------+
| user | my_alias |
+------+----------+
| root |        1 |
| root |        1 |
|      |        1 |
| root |        1 |
|      |        1 |
| root |        1 |
+------+----------+
6 rows in set (0.00 sec)

mysql> create view v_test as select user, 1 as my_alias from mysql.user order by my_alias;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from v_test t;
+------+----------+
| user | my_alias |
+------+----------+
|      |        1 |
|      |        1 |
| root |        1 |
| root |        1 |
| root |        1 |
| root |        1 |
+------+----------+
6 rows in set (0.00 sec)

mysql> select t.user from v_test t;
+------+
| user |
+------+
|      |
|      |
| root |
| root |
| root |
| root |
+------+
6 rows in set (0.00 sec)

mysql> select t.my_alias from v_test t;
+----------+
| my_alias |
+----------+
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
+----------+
6 rows in set (0.00 sec)

// 5.6.12

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.12-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select user, 1 as my_alias from mysql.user order by my_alias;
+------+----------+
| user | my_alias |
+------+----------+
|      |        1 |
| root |        1 |
| root |        1 |
|      |        1 |
| root |        1 |
|      |        1 |
| root |        1 |
+------+----------+
7 rows in set (0.00 sec)

mysql> create view v_test as select user, 1 as my_alias from mysql.user order by my_alias;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_test t;
+------+----------+
| user | my_alias |
+------+----------+
|      |        1 |
| root |        1 |
| root |        1 |
|      |        1 |
| root |        1 |
|      |        1 |
| root |        1 |
+------+----------+
7 rows in set (0.00 sec)

mysql> select t.user from v_test t;
ERROR 1356 (HY000): View 'test.v_test' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> select t.my_alias from v_test t;
+----------+
| my_alias |
+----------+
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
+----------+
7 rows in set (0.00 sec)

Noted in 5.6.16, 5.7.4 changelogs.

It was not possible to query a view with an ORDER BY clause that 
referenced an alias in the SELECT clause of the view definition,
unless all columns in the view were named in the select list.

To handle this problem, the server now writes a view differently into
the .frm file that stores the view definition. If you experience
view-evaluation errors such as just described, drop and recreate the
view so that the .frm file contains the updated view representation.

5.6$ bzr log -r 5587
------------------------------------------------------------
revno: 5587
committer: Guilhem Bichot <guilhem.bichot@oracle.com>
branch nick: 5.6
timestamp: Thu 2013-09-26 11:29:38 +0200
message:
  Fix for Bug#17077305 GENERAL ERROR #1356 WHEN QUERYING VIEW WITH AN
  ORDER BY CLAUSE USING AN ALIAS
  Before the fix for Bug #11923239 ERROR WITH CORRELATED SUBQUERY IN
  VIEW WITH ONLY_FULL_GROUP_BY SQL MODE,
  this statement:
  CREATE VIEW v1 AS SELECT 2*a AS z FROM t1 ORDER BY z;
  produced this definition (written to the view's FRM file):
  CREATE VIEW v1 AS SELECT 2*a AS z FROM t1 ORDER BY 2*a;
  in other words, the alias in ORDER BY was rewritten to its expression.
  The same was done for an alias in GROUP BY.
  The fix removed this rewriting. It was a good fix because:
  – using the alias made only_full_group_by logic work better
  – staying close to the original query "feels better".
  However, this introduced a regression. Indeed, if we select from
  the view above without selecting 'z':
  SELECT 1 FROM v1;
  After view merging, this query becomes
  SELECT 1 FROM t1 ORDER BY z;
  and the resolver fails to find what 'z' is (ORDER item 'z', created
  during mysql_make_view(), is searched in the top query's SELECT
  list and then in the name resolution context of the item i.e. {t1}).
  In this patch, we partially undo the changes of Bug #11923239:
  if the view is merge-able, we write the expression instead of
  the alias, again. This corrects the regression. It does not
  reintroduce any problem for only_full_group_by, because the
  merge-able view never has GROUP BY.