MySQL Bugs: #31202: mysqldump can't handle views with empty column names

Bug #31202	mysqldump can't handle views with empty column names
Submitted:	26 Sep 2007 9:45	Modified:	24 Oct 2007 8:59
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any
Assigned to:	Lars Thalmann	CPU Architecture:	Any
Tags:	mysqldump, VIEW

Description:
mysqldump produces a faulty script when dealing with a view containing an empty column name.
Empty column names are allowed in a view (see Bug#27695) but not in a table.

Creating a view with an empty column name works fine, until you try to dump it with mysqldump and load the script it produced.

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create view v1 as select id as ''  from t1;
Query OK, 0 rows affected (0.00 sec)

$ mysqldump test > t.sql
$ mysql test < t.sql
ERROR 1051 (42S02): Unknown table 'v2'

How to repeat:
use test;
create table t1 (id int);
create view v1 as select id as ''  from t1;

####
mysqldump test > t.sql
mysql test < t.sql

Suggested fix:
The reason for the failure is that mysqldump creates an intermediate table, which is immediately dropped. The usefulness of this intermediate step is not immediately visible. This step could be skipped or made more fault tolerant.

--
-- Temporary table structure for view `v1`
--

DROP TABLE IF EXISTS `v1`;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 CREATE TABLE `v1` (
  `` int(11)
) */;

--
-- Final view structure for view `v1`
--

/*!50001 DROP TABLE `v2`*/;
/*!50001 DROP VIEW IF EXISTS `v1`*/;

mysqldump unable to produce a correct dump, p2

Views should not have all-space column names, so this is not a bug.

The real bug is BUG#27695.