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

Wrong Result on Left Outer Join with Subquery right on true and WHERE filter afterwards

    XMLWordPrintable

Details

    Description

      Summary: The following query returns an entry ('1'), even if there is no matching columns.

      CREATE SCHEMA A ;
      CREATE TABLE A.B  ( C SMALLINT) ;
      INSERT INTO A.B VALUES (2);
      CREATE TABLE A.D  ( E SMALLINT) ;
      SELECT 1 FROM (A.B LEFT OUTER JOIN (SELECT 2 AS G FROM A.D) I ON TRUE ) WHERE B.C = I.G ;
      

      I expect that an empty result set is returned because no query matches the given Condition. Postgres 11 doesn't return anything, which is correct.

      MySQL 8.0.16 is also affected, I'm not sure about other mariadb or mysql versions.

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              Alicen -
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.