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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.27, 5.3.9
    • Fix Version/s: 5.5.28, 5.3.10
    • Component/s: None
    • Labels:
      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

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: