Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8028

MySQL bug#65388 - some views cause mysqldump to emit invalid SQL for their definitions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Incomplete
    • Affects Version/s: 5.5.41
    • Fix Version/s: N/A
    • Component/s: Views
    • Labels:
    • Environment:
      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.

        Attachments

          Activity

            People

            Assignee:
            elenst Elena Stepanova
            Reporter:
            rercolani Rich
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.