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

    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

              • Assignee:
                igor Igor Babaev
                Reporter:
                Alicen -
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: