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

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

                Dates

                Created:
                Updated:
                Resolved: