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

[2 Sep 2010 6:55] Tor Didriksen
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.
[2 Sep 2010 7:41] Valeriy Kravchuk
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
[10 Mar 2015 15:45] Erlend Dahl
No longer repeatable in 5.7.