From 2160a25adc7e0ed8ef59dd4426a884610a32802f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 12 Oct 2011 13:19:37 +0400 Subject: BUG#869001: Wrong result with semijoin + materialization + firstmatch + multipart key - Make advance_sj_state() not to attempt building duplicate removal strategies when we're doing optimization of an SJM-nest. --- mysql-test/r/subselect_sj2_jcl6.result | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 7b7e83e9af5..5b22a2fa3f0 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -833,6 +833,32 @@ x x g x x g set optimizer_switch= @tmp_869012; DROP TABLE t1,t2,t3; +# +# BUG#869001: Wrong result with semijoin + materialization + firstmatch + multipart key +# +set @tmp869001_jcl= @@join_cache_level; +set @tmp869001_os= @@optimizer_switch; +SET join_cache_level=0; +SET optimizer_switch='materialization=on,semijoin=on,firstmatch=on,loosescan=off'; +CREATE TABLE t1 ( f2 int, f3 varchar(1), KEY (f3,f2)) engine=innodb; +INSERT INTO t1 VALUES (8,'x'),(NULL,'x'),(8,'c'); +CREATE TABLE t2 ( f4 varchar(1)) engine=innodb; +INSERT INTO t2 VALUES ('x'); +CREATE TABLE t3 ( f1 int) engine=innodb; +INSERT INTO t3 VALUES (8),(6),(2),(9),(6); +CREATE TABLE t4 ( f3 varchar(1)) engine=innodb; +INSERT INTO t4 VALUES ('p'),('j'),('c'); +SELECT * +FROM t1 JOIN t2 ON (t2.f4 = t1.f3 ) +WHERE ( 8 ) IN ( +SELECT t3.f1 FROM t3 , t4 +); +f2 f3 f4 +NULL x x +8 x x +DROP TABLE t1, t2, t3, t4; +set join_cache_level= @tmp869001_jcl; +set optimizer_switch= @tmp869001_os; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; set join_cache_level=default; -- cgit v1.2.1