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

Wrong result (missing row) with join_cache_level>2, IN subquery after executing EXPLAIN for a different query

    XMLWordPrintable

Details

    Description

      The following test case does not return any rows when it's executed with join_cache_level > 2. The expected result is 1 row.
      Please note that the seemingly unrelated EXPLAIN before the query is important, somehow it affects execution of the query in question (execution plan is different).

      Test case:

      SET join_cache_level=3;
       
      CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(7);
       
      CREATE TABLE t2 (i2 INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (7),(5);
       
      CREATE TABLE t3 (i3 INT) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (7),(2);
       
      EXPLAIN SELECT * FROM t2 STRAIGHT_JOIN t1 ON (i2 = i1);
       
      SELECT * FROM t1 WHERE i1 IN ( SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 0 );

      Expected result (actual result is empty):

      i1
      7

      EXPLAIN:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
      1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	2	100.00	Using where; Using join buffer (flat, BNLH join)
      2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      2	MATERIALIZED	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	2	100.00	Using where; Using join buffer (flat, BNLH join)
      Warnings:
      Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t3`.`i3` = `test`.`t2`.`i2`))

      1

      Attachments

        Activity

          People

            igor Igor Babaev
            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.