MySQL Bugs: #49853: Create view sometimes creates an invalid view

Bug #49853	Create view sometimes creates an invalid view
Submitted:	21 Dec 2009 18:04	Modified:	8 Aug 2012 12:31
Reporter:	Andrew Garner	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.88,5.1.41, next-mr	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Creating a view will sometimes rewrite an SQL query incorrectly - creating the view succesfully but preventing actually using the view or a successful mysqldump afterwards.  This affects both 5.0.88 and 5.1.41.

How to repeat:
DDL that breaks:

DROP VIEW IF EXISTS `v`;
DROP TABLE IF EXISTS `base`;

CREATE TABLE `base` (
    `id` int(11) NOT NULL,
    PRIMARY KEY  (`id`)
);

CREATE VIEW `v` AS (
SELECT
CONCAT('group: ', b.id) `Group Name`
FROM `base` b
GROUP BY b.id
WITH ROLLUP
);

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.88 MySQL Community Server (GPL)

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

mysql [localhost] {msandbox} (test) > SHOW CREATE VIEW v\G
*************************** 1. row ***************************
       View: v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`%` SQL SECURITY DEFINER VIEW `v` AS (select concat(_latin1'group: ',``) AS `Group Name` from `base` `b` group by `b`.`id` with rollup)
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1356
Message: View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1 row in set (0.00 sec)

# mysqldump test
mysqldump: Couldn't execute 'SHOW FIELDS FROM `v`': View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

Suggested fix:
In the real view in production we worked around this by not using a table alias.  The following DDL (without a table alias) works fine:

DROP VIEW IF EXISTS `v`;
DROP TABLE IF EXISTS `base`;

CREATE TABLE `base` (
    `id` int(11) NOT NULL,
    PRIMARY KEY  (`id`)
);

CREATE VIEW `v` AS (
SELECT
CONCAT('group: ', base.id) `Group Name`
FROM `base`
GROUP BY base.id
WITH ROLLUP
);

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.88 MySQL Community Server (GPL)

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

mysql [localhost] {msandbox} (test) > show create table v\G
*************************** 1. row ***************************
       View: v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`%` SQL SECURITY DEFINER VIEW `v` AS (select concat(_latin1'group: ',`base`.`id`) AS `Group Name` from `base` group by `base`.`id` with rollup)
1 row in set (0.00 sec)

I can verify the same error on MySQL 5.0.88, quite an annoying issue.
In our situation, we had a more complex query using table aliases, and only *1* alias would not work correctly.

Thank you for the report.

Verified as described.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Investigation has shown that this bug has the same reason as bug #60295
and is fixed by the same patch.
Therefore closing this bug as a duplicate of bug #60295.

This bug is not repeatable with current versions of MySQL.