diff options
author | Sergey Petrunya <psergey@askmonty.org> | 2013-11-13 07:40:46 +0400 |
---|---|---|
committer | Sergey Petrunya <psergey@askmonty.org> | 2013-11-13 07:40:46 +0400 |
commit | 229aa1d4bfe32cc77d6e87dcdd31eecf78b4c9e2 (patch) | |
tree | 86b1b035f5281bb0d946275276e616cafd4dc666 /mysql-test/r | |
parent | ea78785b8b6a27ed0d8e71e64d058e331f08ed36 (diff) | |
download | mariadb-git-229aa1d4bfe32cc77d6e87dcdd31eecf78b4c9e2.tar.gz |
MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries
Apply fix suggested by Igor:
- When eliminate_item_equal() generates pair-wise equalities from a
multi-equality, do generate a "bridge" equality between the first
field inside SJM nest and the field that's first in the overall multi-equality.
Diffstat (limited to 'mysql-test/r')
-rw-r--r-- | mysql-test/r/subselect_mat.result | 21 | ||||
-rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 21 |
2 files changed, 42 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index e1c9df3b00b..62ec0955e88 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2038,6 +2038,27 @@ SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); pk a b DROP TABLE t1; # End of 5.3 tests +# +# MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries +# +set @tmp_mdev5056=@@join_cache_level; +SET join_cache_level = 2; +CREATE TABLE t1 ( c1 VARCHAR(2), c2 VARCHAR(2), INDEX(c1) ) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('JP','OM'),('VA','JP'),('CA','ML'),('ML','EG'),('DK','CA'), +('DK','QA'),('YE','PL'),('TR','ZW'),('DK','SK'),('SK','DK'), +('RO','ML'),('ML','BG'),('BG','ZW'),('ZW','GE'),('GE','JP'), +('PL','EG'),('QA','YE'),('WF','DK'),('DK','JP'),('EG','OM'); +CREATE TABLE t2 ( c3 VARCHAR(2), c4 VARCHAR(2) ) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('CA','ML'),('IN','HU'),('HU','IN'); +SELECT * FROM t1 AS alias1, t1 AS alias2 +WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( SELECT c2 FROM t1 ); +c1 c2 c1 c2 +CA ML CA ML +CA ML RO ML +DROP TABLE t1,t2; +set join_cache_level=@tmp_mdev5056; +# End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index bcdd82b790c..38ab07882de 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2078,3 +2078,24 @@ SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); pk a b DROP TABLE t1; # End of 5.3 tests +# +# MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries +# +set @tmp_mdev5056=@@join_cache_level; +SET join_cache_level = 2; +CREATE TABLE t1 ( c1 VARCHAR(2), c2 VARCHAR(2), INDEX(c1) ) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('JP','OM'),('VA','JP'),('CA','ML'),('ML','EG'),('DK','CA'), +('DK','QA'),('YE','PL'),('TR','ZW'),('DK','SK'),('SK','DK'), +('RO','ML'),('ML','BG'),('BG','ZW'),('ZW','GE'),('GE','JP'), +('PL','EG'),('QA','YE'),('WF','DK'),('DK','JP'),('EG','OM'); +CREATE TABLE t2 ( c3 VARCHAR(2), c4 VARCHAR(2) ) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('CA','ML'),('IN','HU'),('HU','IN'); +SELECT * FROM t1 AS alias1, t1 AS alias2 +WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( SELECT c2 FROM t1 ); +c1 c2 c1 c2 +CA ML CA ML +CA ML RO ML +DROP TABLE t1,t2; +set join_cache_level=@tmp_mdev5056; +# End of 5.5 tests |