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

missing column name restricted by a sub-select results to wrong dataset instead of error

    XMLWordPrintable

Details

    Description

      Trying to follow an obscure statement, I created another one that provided weird incorrect result. Simplified variant looks like:

      with 
      xxx as (
        select 2 /*as id*/, 'row 2'
        union all
        select 3, 'row 3'
      ),
      tab as (
        select 1 as id, 'row 1' as txt
        union all
        select * from xxx
      )
      select * from tab
      where id in (select id from xxx)}}
      

      that "mistakenly" resulting to:

      id	txt
      1	row 1
      2	row 2
      3	row 3
      

      however initially expected this (row 1 was unexpected):

      id	txt
      2	row 2
      3	row 3
      

      however after attentive view the expectation is an error:
      Unknown column 'id' in 'field list'
      (in sub-select, because xxx really misses it).

      Although the reason for that was a "mistake" from my side: forgotten the commented part as id that provides a column id to the xxx-statement, the wrong result is totally unexpected here.

      By the way it looks like the union-ing in the second with-statement tab plays an important role by the issue, because somehow it seems to "follow" the column name id from this second statement (after union) in a sub-select to the first one (just doing it wrong).

      If one rewrite it like this (with a column col instead of id which then completely missed in with-statements:

      ...
      - where id in (select id from xxx)}}
      + where id in (select col from xxx)}}
      

      it would fail as expected with an error:
      Unknown column 'col' in 'field list'

      So a conjunction to id by a with-statement + union seems to arise there internally.

      Attachments

        Activity

          People

            Unassigned Unassigned
            sebres Serg G. Brester
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.