Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
5.5.41
-
CentOS 7, x86_64, package from centos7 "base" repository
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.