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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.0, 5.5.28, 5.3.10
    • 10.0.1, 5.5.29, 5.3.12
    • None
    • None

    Description

      The following test case

      SET optimizer_switch = 'derived_merge=on';
       
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(2);
       
      CREATE TABLE t2 (b INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (3),(4);
       
      CREATE TABLE t3 (c INT) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (5),(6);
       
      SELECT * FROM ( SELECT c FROM ( t1 INNER JOIN t2 ) RIGHT JOIN t3 ON a = c ) AS alias;

      returns NULLs:

      c
      ------
      NULL
      NULL

      If the subquery is executed separately, it returns not null values, which is the correct result:

      c
      ------
      5
      6

      The problem was either introduced or made visible with the revision http://bazaar.launchpad.net/~maria-captains/maria/5.3/revision/3574:

      revno: 3574
      committer: sanja@montyprogram.com
      branch nick: work-maria-5.3-MDEV-486
      timestamp: Wed 2012-09-05 23:23:58 +0300
      message:
        MDEV-486 LP BUG#1010116 fix.
        
        Link view/derived table fields to a real table to check turning the table record to null row.
        
        Item_direct_view_ref wrapper now checks if table is turned to null row.

      Reproducible on current maria/5.3, maria/5.5, maria/10.0.
      MySQL 5.6 and MariaDB 5.2 return the correct result.

      Minimal optimizer_switch: derived_merge=on.
      Also reproducible with the default optimizer_switch:

      index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

      EXPLAIN with the default optimizer_switch (also wrong result):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t3	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)
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (incremental, BNL join)
      Warnings:
      Note	1003	select `test`.`t3`.`c` AS `c` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on((`test`.`t1`.`a` = `test`.`t3`.`c`)) where 1

      EXPLAIN with the minimal optimizer_switch (also wrong result):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1003	select `test`.`t3`.`c` AS `c` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on((`test`.`t1`.`a` = `test`.`t3`.`c`)) where 1

      Attachments

        Issue Links

          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.