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

Wrong result (missing row) wih semijoin, join_cache_level > 2, LEFT JOIN, ORDER BY

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.0.4, 5.3.12, 5.5.33a
    • 5.5.34, 10.0.7, 5.3.13
    • None
    • None

    Description

      Test case:

      SET optimizer_switch = 'semijoin=on';
       
      # Any value > 2 will do
      SET join_cache_level = 3;
       
      CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,4),(2,5);
       
      CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1,'v'),(7,'s');
       
      CREATE TABLE t3 (pk3 INT PRIMARY KEY, i3 INT, c3 CHAR(1), INDEX(i3), INDEX(c3,i3)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (1,7,'g'),(2,4,'p'),(3,1,'q');
       
      CREATE TABLE t4 (i4 INT) ENGINE=MyISAM;
      INSERT INTO t4 VALUES (1);
       
      SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) 
      WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;

      Actual result:

      pk1	i1	i2	c2	pk3	i3	c3
      1	4	7	s	NULL	NULL	NULL

      Expected result:

      pk1	i1	i2	c2	pk3	i3	c3
      1	4	7	s	NULL	NULL	NULL
      1	4	1	v	NULL	NULL	NULL

      Reproducible on older versions down to 5.3.5 at least.

      EXPLAIN (from current 5.5):

      WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t4	system	NULL	NULL	NULL	NULL	1	100.00	Using filesort
      1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      1	PRIMARY	t3	hash_index	i3	#hash#i3:i3	5:5	test.t2.i2	3	66.67	Start temporary; End temporary; Using join buffer (flat, BNLH join)
      1	PRIMARY	t3	hash_ALL	c3	#hash#c3	2	test.t2.c2	3	33.33	Using where; Using join buffer (flat, BNLH join)
      Warnings:
      Note	1003	select 1 AS `pk1`,4 AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`c2` AS `c2`,`test`.`t3`.`pk3` AS `pk3`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t3`) join `test`.`t2` left join `test`.`t3` on(((`test`.`t3`.`c3` = `test`.`t2`.`c2`) and (`test`.`t2`.`c2` is not null))) where (`test`.`t3`.`i3` = `test`.`t2`.`i2`) order by 4,`test`.`t2`.`c2`

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.