From 02338228dd48752a48d611b03ce35efd35d143d7 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 6 Sep 2013 09:55:32 -0700 Subject: Fixed bug mdev-4996. The fix for bug mdev-4971 not always correctly set the pointers to inherited multiple equalities in objects of the Item_equal class. --- mysql-test/r/subselect2.result | 28 ++++++++++++++++++++++++++++ mysql-test/r/subselect_sj2_mat.result | 2 +- 2 files changed, 29 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index a7689c37a0a..2c482408c2b 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -198,4 +198,32 @@ a b 1 1 1 3 DROP TABLE t1, t2, t3; +# +# MDEV-4996: degenerate OR formula in WHERE of a subquery +# +CREATE TABLE t1 (a int, c1 varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (2,'x'), (8,'d'); +CREATE TABLE t2 (m int, n int, c2 varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (0, 5, 'x'), (1, 4,'p'); +SELECT * FROM t1 WHERE c1 NOT IN ( +SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c +WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND +( t2b.m != a OR t2b.m = t2a.m )); +a c1 +2 x +8 d +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE c1 NOT IN ( +SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c +WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND +( t2b.m != a OR t2b.m = t2a.m )); +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 Using where +2 DEPENDENT SUBQUERY t2a ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2c ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where (not(<`test`.`t1`.`c1`,`test`.`t1`.`a`>((`test`.`t1`.`c1`,(select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where (((`test`.`t2b`.`m` <> `test`.`t1`.`a`) or (`test`.`t2b`.`m` = `test`.`t2a`.`m`)) and trigcond((((`test`.`t1`.`c1`) = `test`.`t2a`.`c2`) or isnull(`test`.`t2a`.`c2`))) and (`test`.`t2c`.`c2` = `test`.`t2b`.`c2`) and (`test`.`t2b`.`n` = `test`.`t2a`.`m`)) having trigcond((`test`.`t2a`.`c2`))))))) +DROP TABLE t1,t2; set optimizer_switch=@subselect2_test_tmp; diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 18e3f6ddd34..bf5b95d2d8f 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1380,7 +1380,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t2`.`i2` > 0)) +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t3`.`i3` > 0)) SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); i1 -- cgit v1.2.1