diff options
| author | Sergey Petrunya <psergey@askmonty.org> | 2011-10-12 13:19:37 +0400 |
|---|---|---|
| committer | Sergey Petrunya <psergey@askmonty.org> | 2011-10-12 13:19:37 +0400 |
| commit | 2160a25adc7e0ed8ef59dd4426a884610a32802f (patch) | |
| tree | 6532c59568a91ba076a9d5928649b2cea3db951f /mysql-test/r/subselect_sj2.result | |
| parent | 74497ff26535beba56d02392d0645e9e787b3467 (diff) | |
| download | mariadb-git-2160a25adc7e0ed8ef59dd4426a884610a32802f.tar.gz | |
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.
Diffstat (limited to 'mysql-test/r/subselect_sj2.result')
| -rw-r--r-- | mysql-test/r/subselect_sj2.result | 26 |
1 files changed, 26 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 6fe4e7720bf..c3d9ef0cf92 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -822,5 +822,31 @@ 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; |