[MDEV-8028] MySQL bug#65388 - some views cause mysqldump to emit invalid SQL for their definitions Created: 2015-04-20  Updated: 2015-09-05  Resolved: 2015-09-05

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 5.5.41
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Rich Assignee: Elena Stepanova
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

CentOS 7, x86_64, package from centos7 "base" repository

Attachments: HTML File showvars    

 Description   

I have a database from MySQL 5.0.33 which, on attempting to export/import to a more modern MySQL, emits an invalid definition of the form:

/*!50001 VIEW `FOO_VIEW` AS (select _latin1'FOO_N' AS `FOO_A`,`n`.`FOO_B` AS `FOO_B`,if(isnull(`m`.`FOO_C`),`n`.`FOO_D`,`m`.`FOO_C`) AS `FOO_D`,if((`n`.`FOO_J` = _latin1'NA'),_latin1'',`n`.`FOO_J`) AS `FOO_J`,`n`.`FOO_E` AS `FOO_E`,concat_ws(_latin1'-',substr(`n`.`FOO_E`,1,4),substr(`n`.`FOO_E`,5,2),substr(`n`.`FOO_E`,7,2)) AS `iso_date`,concat_ws(_latin1':',substr(`n`.`FOO_E`,9,2),substr(`n`.`FOO_E`,11,2)) AS `FOO_H`,`n`.`FOO_L` AS `FOO_L`,`n`.`FOO_O` AS `FOO_O`,_latin1'' AS `FOO_M`,_latin1'' AS `FOO_G` from (`FOO_K` `n` left join `FOO_F` `m` on((`m`.`FOO_D` = `n`.`FOO_D`))) where (`n`.`FOO_B` > (select (greatest(10000,(select max(`FOO_K`.`FOO_B`) AS `max(FOO_B)` from `FOO_K`)) - 10000) AS `greatest(10000, (select max(FOO_B) from FOO_K)) - 10000`))) union all (select _latin1'System' AS `FOO_A`,`s`.`FOO_B` AS `FOO_B`,if(isnull(`m`.`FOO_C`),`s`.`FOO_D`,`m`.`FOO_C`) AS `FOO_D`,if((`s`.`FOO_J` = _latin1'NA'),_latin1'',`s`.`FOO_J`) AS `FOO_J`,`s`.`FOO_E` AS `FOO_E`,concat_ws(_latin1'-',substr(`s`.`FOO_E`,1,4),substr(`s`.`FOO_E`,5,2),substr(`s`.`FOO_E`,7,2)) AS `iso_date`,concat_ws(_latin1':',substr(`s`.`FOO_E`,9,2),substr(`s`.`FOO_E`,11,2)) AS `FOO_H`,_latin1'' AS `FOO_L`,_latin1'' AS `FOO_O`,`s`.`FOO_M` AS `FOO_M`,`s`.`FOO_G` AS `FOO_G` from (`FOO_I` `s` left join `FOO_F` `m` on((`m`.`FOO_D` = `s`.`FOO_D`))) where (`s`.`FOO_B` > (select (greatest(10000,(select max(`FOO_I`.`FOO_B`) AS `max(FOO_B)` from `FOO_I`)) - 10000) AS `greatest(10000, (select max(FOO_B) from FOO_I)) - 10000`))) */;

which emits:
ERROR 1166 (42000) at line XXXX: Incorrect column name 'greatest(10000, (select max(FOO_B) from FOO_K)) - 10000'

This bombs the same way on MySQL 5.0.33, MariaDB 5.5.41, and Oracle MySQL 5.7.7-rc.

 Comments   
Comment by Rich [ 2015-04-20 ]

Same on MariaDB 10.0.17.

Comment by Rich [ 2015-04-20 ]

"show variables" on the MariaDB 10.0.17 instance.

Comment by Rich [ 2015-04-20 ]

I've reconstructed a query to generate this view, I'm going to try to turn this into a minimal reproducible test case and attach it...

Comment by Rich [ 2015-04-20 ]

It seems the problem is that, on MySQL 5.0.X (at least), constructing a view of a certain format results in the "query=" field in the frm being incorrect SQL, and even though the construction of this appears to have been fixed since (at least in MariaDB 10.0.17, haven't tested anything else yet), the query= is never regenerated if old datafiles are upgraded/imported, so "show create view FOO_VIEW" will still show the incorrect SQL.

I'm currently constructing a testcase from MySQL 5.0.X datafiles; when I'm done, I'll upload the SQL I used to generate it and the binary datafiles.

Comment by Elena Stepanova [ 2015-05-19 ]

Hi,

How did it go with the test case? Do you have any updates on this?

Comment by Elena Stepanova [ 2015-09-05 ]

Please comment to re-open if you have more information.

Generated at Thu Feb 08 07:24:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.