MySQL Bugs: #54766: Impossible to use view after creation

Bug #54766	Impossible to use view after creation
Submitted:	24 Jun 2010 10:33	Modified:	24 Jun 2010 10:48
Reporter:	Sergei Glukhov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.92, 5.1.49, 5.5.5-m3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Sometimes it's impossible to use a view after creation

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

CREATE VIEW v1 AS SELECT * FROM t2 UNION SELECT * FROM t2
  ORDER BY (SELECT * FROM t1 WHERE MATCH `a` AGAINST ('+abc' IN BOOLEAN MODE));

SHOW CREATE VIEW v1;

# should work without errors
--error 1356
SELECT * FROM v1;

DROP VIEW v1;
DROP TABLES t1, t2;

Suggested fix:
do not enclose star in backticks in ORDER BY subselect

Verified just as described with current 5.1.49 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-debug Source distribution

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));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

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

mysql> CREATE VIEW v1 AS SELECT * FROM t2 UNION SELECT * FROM t2
    ->   ORDER BY (SELECT * FROM t1 WHERE MATCH `a` AGAINST ('+abc' IN BOOLEAN MODE));
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW CREATE VIEW v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`b` AS `b` from `t2` union select `t2`.`b` AS `b` from `t2` order by (select `*` from `t1` where (match `a` against ('+abc' in boolean mode)))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

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

5.0 is also affected, although view is created a bit differently:

mysql> SHOW CREATE VIEW v1\G
*************************** 1. row ***************************
       View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`b` AS `b` from `t2` union select `t2`.`b` AS `b` from `t2` order by (select `*` AS `*` from `t1` where (match `a` against (_latin1'+abc' in boolean mode)))
1 row in set (0.00 sec)

mysql>  SELECT * FROM v1;ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.92-debug | 
+--------------+
1 row in set (0.00 sec)

Same with current mysql-trunk:

mysql> SHOW CREATE VIEW v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`b` AS `b` from `t2` union select `t2`.`b` AS `b` from `t2` order by (select `*` from `t1` where (match `a` against ('+abc' in boolean mode)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> SELECT * FROM v1;
ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.5.5-m3-debug |
+----------------+
1 row in set (0.00 sec)

Still present in 5.6, fixed in 5.7, view will now be created as:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`b` AS `b` from `t2` union select `t2`.`b` AS `b` from `t2` order by (select `t1`.`a` from `t1` where (match `t1`.`a` against ('+abc' in boolean mode)))

listing the columns from table t1 explicitly instead of `*`