Merge branch '10.0' into 10.1

1 files changed, 18 insertions, 0 deletions

diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index 3e60f242083..cf12bdaea21 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -1045,6 +1045,24 @@ SELECT * FROM (SELECT t FROM t1 WHERE d IS NULL) sq;
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-16374: filtered shows 0 for materilization scan for a semi join, which makes optimizer
+--echo # always pick materialization scan over materialization lookup
+--echo #
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int);
+insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),
+(11,11),(12,12),(13,13),(14,14),(15,15);
+set @@optimizer_use_condition_selectivity=2;
+explain extended select * from t1 where a in (select max(a) from t1 group by b);
+select * from t1 where a in (select max(a) from t1 group by b);
+set @@optimizer_use_condition_selectivity=1;
+explain extended select * from t1 where a in (select max(a) from t1 group by b);
+select * from t1 where a in (select max(a) from t1 group by b);
+drop table t1,t0;
+
 set histogram_size=@save_histogram_size;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;