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

Query with UNION fails in Oracle sql_mode with ER_BAD_FIELD_ERROR/ER_UNKNOWN_TABLE

    XMLWordPrintable

Details

    • Unexpected results
    • In Oracle mode all set operators share one priority and bind left-to-right, so wrapping the accumulated prefix on each operator change enforces it
    • Q3/2026 Server Maintenance

    Description

      After porting database (by script) from 11.4.2 into fresh new 11.8.6 database some of views were not recreated. That was because new system found an error in select while the old version runs the query fine. I've reduced the query to the minimum:

      SELECT * from DP j
      		 
      WHERE 
        not exists (
      		 
          select 0 from src
      		 
                where src.dp_id = j.dp_id 
          union all
      		 
          select 0 from dev
      		 
                where j.dp_id = src.dp_id
      		 
      )

      and it still output:

      SQL Error (1054): Unknown column 'j.dp_id' in 'WHERE'
      The essential is 'union all' construct. It runs without error If only the first or the second part in not exists subquery left.
      That worked in Oracle, that worked in 11.4.2, not working in 11.8.6. Does it mean that such SQL statement isn't legal anymore?

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-37325 Incorrect results for INTERSECT ALL in ORACLE mode

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40405 Regression since 10.11.16: ORACLE mode fails to resolve outer table alias in a correlated UNION ALL subquery

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30979 mariadb gives error for unknown column in subquery but mysql runs fine

          • Major - Major loss of function.
          • Confirmed

          Activity

            People

              alice Alice Sherepa
              balta Tadas Balaišis
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0d
                  0d
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 1h
                  1h

                  Git Integration

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