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

Wrong result (missing row) with materialization+semijoin, HAVING

    Details

      Description

      The problem appeared in 5.3 tree with the following revision:

      revno: 3766
      revision-id: igor@askmonty.org-20140306215634-4gm5hv023ynkxfzq
      parent: igor@askmonty.org-20140221052733-ypg4dpfcmy5l0b3q
      committer: Igor Babaev <igor@askmonty.org>
      branch nick: maria-5.3
      timestamp: Thu 2014-03-06 13:56:34 -0800
      message:
        Fixed bug mdev-5686.
        The calls of the function remove_eq_conds() may change the and/or structure
        of the where conditions. So JOIN::equal_cond should be updated for non-recursive
        calls of remove_eq_conds(). 

      Test case 1 (with MyISAM)

      SET optimizer_switch = 'materialization=on,semijoin=on';
       
      CREATE TABLE t1 (f_key VARCHAR(1), f_nokey VARCHAR(1), INDEX(f_key)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('v','v'),('s','s');
       
      CREATE TABLE t2 (f_int INT, f_key VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
      INSERT INTO t2 VALUES 
      (4,'j'),(6,'v'),(3,'c'),(5,'m'),(3,'d'),(2,'d'),(2,'y'),
      (9,'t'),(3,'d'),(8,'s'),(1,'r'),(8,'m'),(8,'b'),(5,'x');
       
      SELECT t2.f_int FROM t1 INNER JOIN t2 ON (t2.f_key = t1.f_nokey) 
      WHERE t1.f_nokey IN ( 
        SELECT t1.f_key FROM t1, t2 WHERE t1.f_key = t2.f_key 
      ) HAVING t2.f_int >= 0 AND t2.f_int != 0;

      Result:

      f_int
      6

      Expected result:

      f_int
      6
      8

      EXPLAIN:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
      1	PRIMARY	t2	ref	f_key	f_key	4	test.t1.f_key	2	100.00	Using index condition
      2	MATERIALIZED	t1	index	f_key	f_key	4	NULL	2	100.00	Using where; Using index
      2	MATERIALIZED	t2	ref	f_key	f_key	4	test.t1.f_key	2	100.00	Using index
      Warnings:
      Note	1003	select `test`.`t2`.`f_int` AS `f_int` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`f_key` = `test`.`t1`.`f_key`) and (`test`.`t2`.`f_key` = `test`.`t1`.`f_nokey`)) having ((`test`.`t2`.`f_int` >= 0) and (`test`.`t2`.`f_int` <> 0))


      Test case 2 (with InnoDB)

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (f_key VARCHAR(1) NOT NULL, f_nokey VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
      INSERT INTO t1 VALUES 
      ('v','v'),('s','s'),('l','l'),('y','y'),('c','c'),('i','i'),('h','h'),('q','q');
       
      CREATE TABLE t2 (f_int INT, f_key VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
      INSERT INTO t2 VALUES 
      (4,'j'),(6,'v'),(3,'c'),(5,'m'),(3,'d'),(2,'d'),(2,'y'),(9,'t'),(3,'d'),(8,'s'),
      (1,'r'),(8,'m'),(8,'b'),(5,'x'),(7,'g'),(5,'p'),(1, 'q'),(6,'w'),(2,'d'),(9,'e');
       
      SELECT t2.f_int FROM t1 INNER JOIN t2 ON (t2.f_key = t1.f_nokey) 
      WHERE t1.f_nokey IN ( 
        SELECT t1.f_key FROM t2, t1 WHERE t1.f_key = t2.f_key  
      ) HAVING t2.f_int >= 0 AND t2.f_int != 0;

      Result:

      f_int
      2

      Expected result:

      f_int
      6
      8
      2
      3
      1


      Test case 3 (same as test case 2, only without NOT NULL):

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (f_key VARCHAR(1), f_nokey VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
      INSERT INTO t1 VALUES 
      ('v','v'),('s','s'),('l','l'),('y','y'),('c','c'),('i','i'),('h','h'),('q','q');
       
      CREATE TABLE t2 (f_int INT, f_key VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
      INSERT INTO t2 VALUES 
      (4,'j'),(6,'v'),(3,'c'),(5,'m'),(3,'d'),(2,'d'),(2,'y'),(9,'t'),(3,'d'),(8,'s'),
      (1,'r'),(8,'m'),(8,'b'),(5,'x'),(7,'g'),(5,'p'),(1, 'q'),(6,'w'),(2,'d'),(9,'e');
       
      SELECT t2.f_int FROM t1 INNER JOIN t2 ON (t2.f_key = t1.f_nokey) 
      WHERE t1.f_nokey IN ( 
        SELECT t1.f_key FROM t2, t1 WHERE t1.f_key = t2.f_key  
      ) HAVING t2.f_int >= 0 AND t2.f_int != 0;

      Result (empty set):

      f_int

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: