revision-id: 2facb2d39b1ad46f3a739be5043e13ef94592dd4 (mariadb-10.2.2-869-g2facb2d39b1) parent(s): 0febf64e75bcc746a00764e681d87e6aa10bd486 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-09-16 14:15:00 +0300 message: MDEV-20109: Optimizer ignores distinct key created for materialized... (Backported to 10.2-compatiblity, build 22257) Adjust the testcase according to the review input --- mysql-test/r/subselect_sj.result | 2 +- mysql-test/r/subselect_sj_jcl6.result | 2 +- mysql-test/t/subselect_sj.test | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index ad6af0340ed..bd81d5cfa4a 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -3184,7 +3184,7 @@ insert into t4 select floor(rand()*1000) from t2 limit 500; # | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | # # Instead, it should use eq_ref on the materialized table. -explain select * from t3 where a in (select a from t4 group by a); +explain select * from t3 where a in (select a from t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 10000 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 785c687402c..ba6f0c5a13e 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3197,7 +3197,7 @@ insert into t4 select floor(rand()*1000) from t2 limit 500; # | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | # # Instead, it should use eq_ref on the materialized table. -explain select * from t3 where a in (select a from t4 group by a); +explain select * from t3 where a in (select a from t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 10000 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index d29dd998d23..49093190b24 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2867,7 +2867,7 @@ insert into t4 select floor(rand()*1000) from t2 limit 500; --echo # --echo # Instead, it should use eq_ref on the materialized table. -explain select * from t3 where a in (select a from t4 group by a); +explain select * from t3 where a in (select a from t4); drop table t1, t2, t3, t4;