MySQL Bugs: #41999: SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list

Bug #41999	SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list
Submitted:	9 Jan 2009 15:30	Modified:	17 Mar 2009 18:26
Reporter:	Marcus Krantz	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.67, 5.0.74, 5.1.30	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We have version 5.0.45 installed on our servers and we use mysqldump to dump our ddl.

It works fine to import the dump in 5.0.45 but it doesn't in 5.0.67.

If the dump was created by 5.0.67's version of the mysqldump command the generated file cannot be imported anymore.

We upgraded the database to 5.0.67 with the command mysql_upgrade and started the 5.0.67 server instance and then generated a new dump with the mysqldump command.

This file cannot be imported to a 5.0.67 database.

The problem is a view defintion created from the mysqldump command. The output from the dump command looks like:
==================================
/*!50001 DROP TABLE `organization`*/;
/*!50001 DROP VIEW IF EXISTS `organization`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `statistics`.`organization` AS select `o`.`companyID` AS `organization_id`,`o`.`companyName` AS `company_name`,`o`.`status` AS `status`,`o`.`group_id` AS `group_id`,`n`.`code` AS `iso_country`,(select (case cast(`o`.`product` as char charset binary) when -(1) then _utf8'NONE' when 0 then _utf8'STANDARD' when 1 then _utf8'PREMIUM' when 2 then _utf8'ENTERPRISE' when 3 then _utf8'ENTERPRISE_PLUS' end) AS `case BINARY o.product when -1 then 'NONE' when 0 then 'STANDARD' when 1 then 'PREMIUM' when 2 then 'ENTERPRISE' when 3 then 'ENTERPRISE_PLUS' end`) AS `product` from (((`organization` `o` left join `organization_contact_info` `oci` on((`o`.`companyID` = `oci`.`companyID`))) left join `contact_info` `ci` on((`oci`.`contact_info_id` = `ci`.`contact_info_id`))) left join `nation` `n` on((`ci`.`nation_id` = `n`.`nation_id`))) group by `o`.`companyID` order by `o`.`companyID`,`oci`.`contact_info_type` */;
==================================

The view was created with the following statement:
==================================
create or replace view statistics.organization as
select o.companyID as organization_id, o.companyName as company_name, o.status, o.group_id
, n.code as iso_country, (SELECT case BINARY o.product
    when -1 then 'NONE'
    when 0 then 'STANDARD'
    when 1 then 'PREMIUM'
    when 2 then 'ENTERPRISE'
    when 3 then 'ENTERPRISE_PLUS' end)
    AS 'product' 
from organization as o
left outer join organization_contact_info as oci on o.companyID = oci.companyID
left outer join contact_info as ci on oci.contact_info_id = ci.contact_info_id
left outer join nation as n on ci.nation_id = n.nation_id
group by organization_id
order by organization_id asc, contact_info_type asc
;
==================================

The error I receive when I try to import the generated dump is:
==================================
ERROR 1166 (42000) at line 5720: Incorrect column name 'case BINARY o.product when -1 then 'NONE' when 0 then 'STANDARD' when 1 then 'PREMIUM' when 2 then ''

How to repeat:
Create following tables in 5.0.45
=================================
CREATE TABLE `organization` (
  `companyID` int(5) NOT NULL auto_increment,
  `companyName` varchar(127) NOT NULL default '',
  `status` int(1) NOT NULL default '0',
  `code` varchar(255) default NULL,
  `group_id` bigint(20) unsigned NOT NULL default '0',
  `worksWithSystemCerts` int(1) NOT NULL default '1',
  `created` bigint(20) unsigned NOT NULL default '0',
  `modified` bigint(20) unsigned NOT NULL default '0',
  `version` int(10) NOT NULL default '0',
  `commission_rate` float(5,3) NOT NULL default '0.000',
  `product` smallint(1) NOT NULL default '-1',
  `officeHoursStart` int(2) NOT NULL default '0',
  `officeHoursEnd` int(2) NOT NULL default '24',
  `textMessage` text NOT NULL,
  PRIMARY KEY  (`companyID`),
  UNIQUE KEY `companyID` (`companyID`),
  UNIQUE KEY `companyName` (`companyName`),
  UNIQUE KEY `code` (`code`),
  KEY `fk_principal_group` (`group_id`),
  KEY `organization_idx1` (`modified`),
  CONSTRAINT `fk_principal_group` FOREIGN KEY (`group_id`) REFERENCES `principal_group` (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1849 DEFAULT CHARSET=utf8

CREATE TABLE `organization_contact_info` (
  `contact_info_id` bigint(20) unsigned NOT NULL default '0',
  `companyID` int(5) NOT NULL default '0',
  `contact_info_type` int(1) NOT NULL default '0',
  `created` bigint(20) unsigned NOT NULL default '0',
  `modified` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`companyID`,`contact_info_id`),
  UNIQUE KEY `organization_contact_info_u1` (`companyID`,`contact_info_type`),
  KEY `organization_contact_info_fk2` (`contact_info_id`),
  KEY `organization_contact_info_idx1` (`modified`),
  CONSTRAINT `organization_contact_info_fk1` FOREIGN KEY (`companyID`) REFERENCES `organization` (`companyID`) ON DELETE CASCADE,
  CONSTRAINT `organization_contact_info_fk2` FOREIGN KEY (`contact_info_id`) REFERENCES `contact_info` (`contact_info_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `contact_info` (
  `contact_info_id` bigint(20) unsigned NOT NULL auto_increment,
  `nation_id` bigint(20) unsigned default NULL,
  `street` varchar(128) NOT NULL default '',
  `postBox` varchar(128) NOT NULL default '',
  `postalCode` varchar(128) NOT NULL default '',
  `phone1` varchar(128) NOT NULL default '',
  `phone2` varchar(128) NOT NULL default '',
  `fax` varchar(128) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  `city` varchar(128) NOT NULL default '',
  `province` varchar(128) NOT NULL default '',
  `created` bigint(20) unsigned NOT NULL default '0',
  `modified` bigint(20) unsigned NOT NULL default '0',
  `flavor` int(1) NOT NULL default '0',
  `name` varchar(128) NOT NULL default '',
  `title` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`contact_info_id`),
  KEY `contact_info_fk1` (`nation_id`),
  KEY `contact_info_idx1` (`modified`),
  CONSTRAINT `contact_info_fk1` FOREIGN KEY (`nation_id`) REFERENCES `nation` (`nation_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=71922 DEFAULT CHARSET=utf8

CREATE TABLE `nation` (
  `nation_id` bigint(20) unsigned NOT NULL auto_increment,
  `code` varchar(2) NOT NULL default '',
  `iso3` varchar(3) default NULL,
  PRIMARY KEY  (`nation_id`),
  UNIQUE KEY `nation_u1` (`code`),
  UNIQUE KEY `nation_u2` (`iso3`)
) ENGINE=InnoDB AUTO_INCREMENT=241 DEFAULT CHARSET=utf8

create or replace view statistics.organization as
select o.companyID as organization_id, o.companyName as company_name, o.status, o.group_id
, n.code as iso_country, (SELECT case BINARY o.product
    when -1 then 'NONE'
    when 0 then 'STANDARD'
    when 1 then 'PREMIUM'
    when 2 then 'ENTERPRISE'
    when 3 then 'ENTERPRISE_PLUS' end)
    AS 'product' 
from organization as o
left outer join organization_contact_info as oci on o.companyID = oci.companyID
left outer join contact_info as ci on oci.contact_info_id = ci.contact_info_id
left outer join nation as n on ci.nation_id = n.nation_id
group by organization_id
order by organization_id asc, contact_info_type asc

========================================================

* Now, make a dump and try to import it on a 5.0.67. It will fail.

* Upgrade the database to 5.0.67 using mysql_upgrade

* Create a new dump with mysql_dump (v 5.0.67)

* Clear out the existing databases and try to import it

This is a server bug. Verified with 5.0.74 and much simpler test case:

mysql> create view vvv as select 1 as a, (select case user
    -> when 'user' then 'a'
    -> when 'user2' then 'b'
    -> when 'user3' then 'c' end from mysql.user limit 1) as b;
Query OK, 0 rows affected (0.52 sec)

mysql> show create view vvv\G
*************************** 1. row ***************************
       View: vvv
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `test`.`vvv` AS select 1 AS `a`,(select (case `mysql`.`user`.`User`
 when _utf8'user' then _utf8'a' when _utf8'user2' then _utf8'b' when _utf8'user3
' then _utf8'c' end) AS `case user
when 'user' then 'a'
when 'user2' then 'b'
when 'user3' then 'c' end` from `mysql`.`user` limit 1) AS `b`
1 row in set (0.11 sec)

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.74-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.02 sec)

SHOW CREATE VIEW returns wrong result that surely can NOT be used to re-create the view.

Looks like a variation of http://bugs.mysql.com/bug.php?id=40277, maybe.

Duplicate of bug #40277.

Simplified CREATE VIEW query for the test case:

CREATE VIEW v1 AS SELECT (SELECT
'12345678901234567890123456789012345678901234567890123456789012345')