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

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

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          elenst Elena Stepanova made changes -
          igor Igor Babaev (Inactive) made changes -
          Assignee Sergei Petrunia [ psergey ] Igor Babaev [ igor ]
          igor Igor Babaev (Inactive) made changes -
          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:
          {code:sql}
          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 );
          {code}

          Expected result (actual result is empty):
          {noformat}
          i1
          7
          {noformat}

          EXPLAIN:
          {noformat}
          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`))
          {noformat}

          		 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:
          {code:sql}
          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 );
          {code}

          Expected result (actual result is empty):
          {noformat}
          i1
          7
          {noformat}

          EXPLAIN:
          {noformat}
          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`))
          {noformat}

          1
          igor Igor Babaev (Inactive) made changes -
          Priority Minor [ 4 ] Major [ 3 ]
          igor Igor Babaev (Inactive) made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 26225 ] MariaDB v2 [ 45692 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 45692 ] MariaDB v3 [ 66392 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 66392 ] MariaDB v4 [ 146435 ]

          People

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