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

Regression: SELECT ... UNION ... with inconsistent column names fails

    XMLWordPrintable

Details

    Description

      A SELECT from a subquery containing a UNION with inconsistent result column names fails with an ill-fitting error message, but only when used with a WHERE clause.
      This is a heavily striped down version of a query that fails:

      select * from (
          select 1 x
          union all
          select sum(1) y
        ) t where x>0
      

      yields: ERROR 1054 (42S22): Unknown column 'x' in 'order clause'
      which is strange, because there is no order clause.

      This slightly different statement, however, works fine:

      select * from (
          select 1 x
          union all
          select 2 y
        ) t  where x>0
      

      (Note: second branch of UNION has no aggregate function)
      and yields, as expected:

      +---+
      | x |
      +---+
      | 1 |
      | 2 |
      +---+
      

      Omitting the WHERE clause from the first query allows it to succeed, too.

      This seems to be a regression. My original query (much longer) used to work with an older 10.x version, but I cannot reconstruct any more which version that was.
      On an old MySQL server 5.7.9, the problem does NOT come up. SQLite also has NO problems with the query, so I assume the query is legal as such, even given the inconsistent naming of columns.

      Of course, a workaround is easy: use consistent aliases:

      select * from (
          select 1 x
          union all
          select sum(1) x
        ) t where x>0
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              gwselke Gisbert W. Selke
              Votes:
              4 Vote for this issue
              Watchers:
              11 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.