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

Wrong result (missing row, NULL instead of a field value) with semijoin=on, LEFT JOIN in the outer query and INNER JOIN in EXISTS subquery

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.1, 5.5.29
    • 10.0.2, 5.5.30, 5.3.13
    • None
    • None

    Description

      The following test case

       
      set optimizer_switch='exists_to_in=on';
       
      CREATE TABLE t1 (i1 INT, c1 VARCHAR(1)) ENGINE=MyISAM;
      INSERT t1 VALUES (7,'v'),(3,'y');
       
      CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES ('y'),('y');
       
      CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES 
        ('j'),('v'),('c'),('m'),('d'),
        ('d'),('y'),('t'),('d'),('s');
       
      SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') 
      WHERE EXISTS ( SELECT 1 FROM t1, t3 WHERE c3 = c1 AND c1 = outer_t1.c1 );
       

      Produces 2 rows when it's executed with exists_to_in=on:

      i1	c1	c2
      7	v	NULL
      3	y	NULL

      and 3 rows without exists_to_in:

      i1	c1	c2
      3	y	y
      3	y	y
      7	v	NULL

      The latter result is correct.

      EXPLAIN with exists_to_in=on, otherwise default optimizer_switch:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	outer_t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
      2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note	1276	Field or reference 'test.outer_t1.c1' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` semi join (`test`.`t1` join `test`.`t3`) left join `test`.`t2` on((((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and (`test`.`t2`.`c2` = `test`.`outer_t1`.`c1`)) or (`test`.`outer_t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t1`.`c1`)

      revision-id: sanja@askmonty.org-20130213131832-b1sk3puofj29jubr
      revno: 3502
      branch-nick: 10.0-base-exists2in

      Attachments

        Activity

          People

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