From 576a2b153320ab1fbfd4df06a30bf1f52e569cba Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 29 Aug 2013 21:02:42 -0700 Subject: Fixed bug mdev-4971. The function propagate_new_equalities() did not updated properly the references to inherited multiple equalities. --- mysql-test/r/select.result | 25 +++++++++++++++++++++++++ mysql-test/r/select_jcl6.result | 25 +++++++++++++++++++++++++ mysql-test/r/select_pkeycache.result | 25 +++++++++++++++++++++++++ mysql-test/r/subselect_sj2_mat.result | 2 +- mysql-test/t/select.test | 23 +++++++++++++++++++++++ 5 files changed, 99 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index f5744c8e3d0..f0de9622368 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -5254,4 +5254,29 @@ SELECT * FROM t1, t2 WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); i1 j1 i2 DROP TABLE t1,t2; +# +# Bug mdev-4971: equality propagation after merging degenerate +# disjunction into embedding AND level +# +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or (1 > 4))) +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +3 1 6 1 1 +DROP TABLE t1,t2; End of 5.3 tests diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index eea9d318f04..3c3d6188301 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -5265,6 +5265,31 @@ SELECT * FROM t1, t2 WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); i1 j1 i2 DROP TABLE t1,t2; +# +# Bug mdev-4971: equality propagation after merging degenerate +# disjunction into embedding AND level +# +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or (1 > 4))) +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +3 1 6 1 1 +DROP TABLE t1,t2; End of 5.3 tests set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index f5744c8e3d0..f0de9622368 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -5254,4 +5254,29 @@ SELECT * FROM t1, t2 WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); i1 j1 i2 DROP TABLE t1,t2; +# +# Bug mdev-4971: equality propagation after merging degenerate +# disjunction into embedding AND level +# +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or (1 > 4))) +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +3 1 6 1 1 +DROP TABLE t1,t2; End of 5.3 tests diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index bf5b95d2d8f..18e3f6ddd34 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`.`t3`.`i3` > 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`.`t2`.`i2` > 0)) SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); i1 diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index bb5a6b9c2fc..9081b93dbf8 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4408,4 +4408,27 @@ SELECT * FROM t1, t2 DROP TABLE t1,t2; +--echo # +--echo # Bug mdev-4971: equality propagation after merging degenerate +--echo # disjunction into embedding AND level +--echo # + +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; + +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); + +SELECT * FROM t1, t2 + WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 + WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); + +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 + WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); + +DROP TABLE t1,t2; + --echo End of 5.3 tests -- cgit v1.2.1