Mysql 5.6.12 bug : error 1356 when using order by alias in a view definition

Question

I have a very simple Query, which works fine:

SELECT *
FROM images i
INNER JOIN v_images_stats s
ON i.id = s.id

By SELECT * I end up creating duplicate column names so I edited the query to be more specific and ignore the duplicate column names but MySQL throws a #1356 error:

SELECT i.is as id, s.id as imageid
FROM images i
INNER JOIN v_images_stats s
ON i.id = s.id

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

The v_image_stats view and the underlying views and tables it is based have the correct privileges, I'm completely flummoxed.

I'm using MySQL 5.6 and phpmyadmin to create and manage the views, I am also accessing the DB via PHP PDO within the Phalcon framework but I'm pretty sure this a MySQL issue rather than anything else.

Any help gratefully received.

I originally posted the full output of SHOW CREATE VIEW but it was too verbose, here is the simplest example I can create to recreate the problem:

CREATE ALGORITHM=UNDEFINED DEFINER=`events`@`localhost` SQL SECURITY DEFINER VIEW `v_image_stats` AS
(
    select it.*, 1 AS `my_alias`
    from `v_image_totals` `it`
    order by `my_alias` asc)
)

It also fails if I use a table instead of a view in the FROM clause.

Answer 1

This appears to be a bug affecting MySQL 5.6.10 and above (I have not tested against MySQL 5.7). I have raised a bug with MySQL so this can be tracked and hopefully fixed: http://bugs.mysql.com/bug.php?id=69678

See the comments for an effective workaround.