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

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

          sebres Serg G. Brester added a comment - - edited

          Thinking about that a bit, I guess the behavior is correct - the column id exists in context of whole select (it simply means the id of main), so it simply quasi the same as this:

          - where id in (select id from xxx)
          + where id in (select id /* "twice" */)
          # or basically this as a result:
          + where id in (1, 1, 2, 2, 3, 3) 
          

          and has nothing to do with a with-statement "conjunction" that I thought existed here.

          So basically it is weird constellation but fully correct result for that statement. I was just confused by a large statement on that I working before what after all resulted to a wrong simplification and incorrect assumption.

          Thus can be closed as incorrect.
          Sorry for noise.

          sebres Serg G. Brester added a comment - - edited Thinking about that a bit, I guess the behavior is correct - the column id exists in context of whole select (it simply means the id of main), so it simply quasi the same as this: - where id in (select id from xxx) + where id in (select id /* "twice" */) # or basically this as a result: + where id in (1, 1, 2, 2, 3, 3) and has nothing to do with a with-statement "conjunction" that I thought existed here. So basically it is weird constellation but fully correct result for that statement. I was just confused by a large statement on that I working before what after all resulted to a wrong simplification and incorrect assumption. Thus can be closed as incorrect. Sorry for noise.

          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.