MySQL Bugs: #56480: View is not properly prepared: Unknown column '*' in 'field list'

Bug #56480	View is not properly prepared: Unknown column '*' in 'field list'
Submitted:	2 Sep 2010 6:55	Modified:	10 Mar 2015 15:45
Reporter:	Tor Didriksen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.91, 5.1.50, 5.1-bugteam, next-mr-bugfixing	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Discovered this when trying to run
./mtr subquery_none --view-protocol

CURRENT_TEST: main.subquery_none
mysqltest: In included file "./include/subquery.inc": At line 4948: query 'SELECT * FROM t2 UNION SELECT * FROM t2
ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE))' failed: 1356: View 'test.mysqltest_tmp_v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

How to repeat:
A simpler test case:
CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (b INT, c INT);
INSERT INTO t2 VALUES (1, 10),(2, 5);

create view v1 as
SELECT t2.b, t2.c FROM t2 UNION SELECT t2.b, t2.c FROM t2
  ORDER BY (SELECT * FROM t1)
;

select * from v1
;

DROP VIEW v1;
DROP TABLE t1,t2;

Suggested fix:
in st_select_lex_unit::prepare()

if (thd->lex->sql_command == SQLCOM_CREATE_VIEW)
      can_skip_order_by= FALSE;

so that we prepare the subquery in the ORDER BY clause.

In mysql_register_view()
also fix the DBUG_PRINT"info", ("View: ....
so that it actually prints the view text which is written to the .frm file.

Verified also with 5.1.50:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.50-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)) engine=MyISAM;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE t2 (b INT, c INT);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t2 VALUES (1, 10),(2, 5);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create view v1 as
    -> SELECT t2.b, t2.c FROM t2 UNION SELECT t2.b, t2.c FROM t2
    ->   ORDER BY (SELECT * FROM t1)
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from v1;
ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or f
unction(s) or definer/invoker of view lack rights to use them

No longer repeatable in 5.7.