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

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(). 

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;

f_int

6

f_int

6

8

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))

--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;

f_int

2

f_int

6

8

2

3

1

--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;

f_int