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

Wrong result (extra rows and wrong values) with incremental BNLH

    XMLWordPrintable

Details

    Description

      CREATE TABLE t1 (a int) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(2);
       
      CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
      INSERT INTO t2 VALUES  (1,2),(3,4);
       
      CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM;
      ALTER TABLE t3 DISABLE KEYS;
      INSERT INTO t3 VALUES  (5),(6);
      ALTER TABLE t3 ENABLE KEYS;
       
      CREATE TABLE t4 (e int primary key) ENGINE=MyISAM;
      INSERT INTO t4 VALUES (1),(2);
       
      SET SESSION join_cache_level=2;
      SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
      SET SESSION join_cache_level=4;
      SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
      

      Result

      SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
      a	b	c	d	e
      SET SESSION join_cache_level=4;
      SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
      a	b	c	d	e
      1	NULL	NULL	NULL	NULL
      2	NULL	NULL	NULL	NULL
      

      The second result must be incorrect, because if the same query is executed without the WHERE clause, the result is (with both values of join_cache_level):

      SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
      a	b	c	d	e
      1	1	2	NULL	1
      2	1	2	NULL	1
      

      Reproducible with query_cache_level=4 or 5.
      Query plan in case of the wrong result:

      EXPLAIN EXTENDED
      SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e WHERE e IS NULL;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
      1	SIMPLE	t3	hash_index	d	#hash#d:d	5:5	test.t2.c	2	50.00	Using where; Using index; Using join buffer (incremental, BNLH join)
      1	SIMPLE	t4	hash_index	PRIMARY	#hash#PRIMARY:PRIMARY	4:4	test.t2.b	2	50.00	Using where; Using index; Not exists; Using join buffer (incremental, BNLH join)
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`e` AS `e` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(`test`.`t3`.`d` = `test`.`t2`.`c` and `test`.`t2`.`c` is not null) join `test`.`t4`) on(`test`.`t4`.`e` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where `test`.`t4`.`e` is null
      

      Reproducible with all of 5.5-10.5.

      Attachments

        Issue Links

          Activity

            People

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