Details
Major
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
"show variables" on the MariaDB 10.0.17 instance.
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...
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.
Hi,
How did it go with the test case? Do you have any updates on this?
Please comment to re-open if you have more information.
Same on MariaDB 10.0.17.