MySQL Bugs: #68157: mysqldump misinterprets a view definition

Bug #68157	mysqldump misinterprets a view definition
Submitted:	23 Jan 2013 17:21	Modified:	23 Jan 2013 18:40
Reporter:	Mikael Hakman	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S1 (Critical)
Version:	5.5.19	OS:	Windows (8)
Assigned to:		CPU Architecture:	Any

Description:
I backuped a database by:

mysqldump --user=root --password=xxxxxx tatdb > dump.sql

Then I restored it into a new database by:

mysql --user=root --password=xxxxxx --database=tatdbcpy < dump.sql

Then I received following error message:

ERROR 1166 (42000) at line 2851: Incorrect column name 'amount + (select -min(am
ount)
                                   from cbalance
                )'

Futher investigation shows that a view with following definition:

CREATE VIEW SUMBAL (SEQNO, "TYPE", AMOUNT) AS
select 1,
      'Size',
      -min(amount) as amount 
  from cbalance 
union 
select 2,
	  'Equity',
       sum(value) as amount 
  from portfolio 
union 
select 3,
      'Balance',
       amount + (select -min(amount) 
				   from cbalance
                ) 
  from cbalance 
 where tdate = (select max(tdate) 
                  from cbalance
               ) 
union 
select 4,
      'Sale',
       sum(samount) as amount 
 from  realsale 
union 
select 5,
      'Dividend',
       sum(amount) as amount 
from realdividend;

This was misinterpretted in the dump file as follows:

/*!50001 VIEW `sumbal` AS select 1 AS `SEQNO`,'Size' AS `TYPE`,-(min(`cbalance`.`AMOUNT`)) AS `AMOUNT` from `cbalance` union select 2 AS `2`,'Equity' AS `Equity`,sum(`portfolio`.`VALUE`) AS `amount` from `portfolio` union select 3 AS `3`,'Balance' AS `Balance`,(`cbalance`.`AMOUNT` + (select -(min(`cbalance`.`AMOUNT`)) from `cbalance`)) AS `amount + (select -min(amount) 
				   from cbalance
                )` from `cbalance` where (`cbalance`.`TDATE` = (select max(`cbalance`.`TDATE`) from `cbalance`)) union select 4 AS `4`,'Sale' AS `Sale`,sum(`realsale`.`SAMOUNT`) AS `amount` from `realsale` union select 5 AS `5`,'Dividend' AS `Dividend`,sum(`realdividend`.`AMOUNT`) AS `amount` from `realdividend` */;

Note the quotation mark around whole:

`amount + (select -min(amount) 
				   from cbalance
                )`

Obviously only the word 'amount' should be quoted. Thanks.
 

How to repeat:
I do not know how to repeat this besides running mysqldump and mysql again. I get this error only in this place out of many tables and views. Thans.

If I edit the dump fie and put quotation correctly as in:

`amount' + (select -min('amount')

Then mysql still interprets the whole expression as column name and gives me the same error. If you look at the original view definition and compare it to what dump file says then you discover that the whole definition has been misinterpreted. There is no AS in:

select 3,
      'Balance',
       amount + (select -min(amount) 
				   from cbalance
                ) 
  from cbalance 
 where tdate = (select max(tdate) 
                  from cbalance
               ) 

But in the dump file there is. I can send you the dump file if you need it. Thanks.

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.

This is duplicate of bug #65388. Please wait when version 5.7 is released.