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

    • Sprint:
      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

            Assignee:
            igor Igor Babaev
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: