|
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:
Expected result:
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:
Expected result:
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):
|