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

Wrong result (NULLs instead of real values) with RIGHT JOIN in a FROM subquery and derived_merge=on

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.27, 5.3.9
    • 5.5.28, 5.3.10
    • None
    • None

    Description

      Assigning it to Sanja because it's a regression introduced with a bugfix for MDEV-486 (revno 3574 on maria/5.3 and revno 3526 on maria/5.5).

      The following test case

      SET optimizer_switch = 'derived_merge=on';
       
      CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (4),(6);
       
      CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (7),(8);
       
      SELECT * FROM (  
        SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2
      ) AS alias;
       

      returns

      f1	f2
      NULL	NULL
      NULL	NULL

      on the affected versions, and

      f1	f2
      NULL	7
      NULL	8

      on unaffected versions. The latter is correct.

      Reproducible with MyISAM, Aria, InnoDB.
      Reproducible starting from revno 3574 on maria/5.3 from revno 3526 on maria/5.5, and up to current ones (3584 and 3544, correspondingly).
      Not reproducible if I replace the subquery with a view.
      Not reproducible if I replace RIGHT JOIN with LEFT JOIN.
      The subquery itself, executed separately, produces the expected result.

      Reproducible with the default optimizer switch as well as with optimizer_switch='derived_merge=on' only.

      EXPLAIN (with the default optimizer_switch):

      id      select_type     table   type    possible_keys   key     key_len ref     rows  filtered Extra
      1       SIMPLE  t2      ALL     NULL    NULL    NULL    NULL    2       100.00
      1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note    1003    select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`f1` = `test`.`t2`.`f2`)) where 1

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.