MySQL Bugs: #38808: Views defined by UNION SELECT statements have null type/cause mysqldump to fail

Bug #38808	Views defined by UNION SELECT statements have null type/cause mysqldump to fail
Submitted:	14 Aug 2008 18:44	Modified:	15 Aug 2008 14:53
Reporter:	David Derr	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.45-log	OS:	Linux (i686 redhat-linux-gnu)
Assigned to:		CPU Architecture:	Any
Tags:	column, mysqldump, null, type, UNION, Views

Description:
The column in a view that is created when two NULL columns are UNIONed in the SELECT statement for a view has no type.

This causes problems in mysqldump, and possibly elsewhere, as there is no column type for the create table command that happens before the actual view is defined.

How to repeat:
CREATE OR REPLACE VIEW vw_null_test AS
(SELECT NULL AS col1, 1 AS col2)
UNION
(SELECT NULL AS col1, 2 AS col2);

DESCRIBE vw_null_test;

 Field     Type        Null     Key     Default     Extra    
 --------  ----------  -------  ------  ----------  -------- 
 col1      null        YES              (null)               
 col2      bigint(20)  NO               0                  

You'll see, in the results of the DESCRIBE, that the Type for col1 is null.  

mysqldump on this view returns the following:

<snip>

--
-- Temporary table structure for view `vw_null_test`
--

DROP TABLE IF EXISTS `vw_null_test`;
/*!50001 DROP VIEW IF EXISTS `vw_null_test`*/;
/*!50001 CREATE TABLE `vw_null_test` (
  `col1` null,
  `col2` bigint(20)
) */;

--
-- Final view structure for view `vw_null_test`
--

/*!50001 DROP TABLE IF EXISTS `vw_null_test`*/;
/*!50001 DROP VIEW IF EXISTS `vw_null_test`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`user`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_null_test` AS (select NULL AS `col1`,1 AS `col2`) union (select NULL AS `col1`,2 AS `col2`) */;

<snip>

When I try to rerun the mysqldump SQL, this error occurs:

ERROR 1064 (42000) at line 24: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null,
  `col2` bigint(20)
) */' at line 2

Suggested fix:
My current workaround is to remove the section of mysqldump that creates the tables before the views are created.  I don't have any views that depend on one another so for me they are unnecessary.  This is tedious though when dealing with very large mysqldump files.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67, and inform about the results.

Thanks, this is fixed in 5.0.67:

mysql> DESCRIBE vw_null_test;
--------------
DESCRIBE vw_null_test
--------------

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1  | binary(0)  | YES  |     | NULL    |       | 
| col2  | bigint(20) | NO   |     | 0       |       | 
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

-David

Closing according last comment.