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

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.1, 10.2, 10.3, 10.4
    • Component/s: Optimizer
    • Labels:
      None

      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

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: