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

Wrong result (extra rows) on LEFT JOIN with a view and IS NULL in WHERE, condition_pushdown_for_derived=on

    XMLWordPrintable

Details

    • 10.2.4-2

    Description

      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (0),(2);
       
      CREATE TABLE t2 (b INT);
      INSERT INTO t2 VALUES (1),(2);
       
      CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
       
      SELECT * FROM t1 LEFT JOIN v2 ON a = b WHERE b IS NULL;
      

      Actual result, 10.2 82b974a1b6

      MariaDB [test]> SELECT * FROM t1 LEFT JOIN v2 ON a = b WHERE b IS NULL;
      +------+------+
      | a    | b    |
      +------+------+
      |    0 | NULL |
      |    2 | NULL |
      +------+------+
      2 rows in set (0.00 sec)
      

      Expected result

      MariaDB [test]> SELECT * FROM t1 LEFT JOIN v2 ON a = b WHERE b IS NULL;
      +------+------+
      | a    | b    |
      +------+------+
      |    0 | NULL |
      +------+------+
      1 row in set (0.00 sec)
      

      Attachments

        Activity

          People

            igor Igor Babaev
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.