Fixed bug mdev-4429: fixed another place where selectivity == 0 requires

a special handling.

1 files changed, 37 insertions, 0 deletions

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index e59f4310431..837e13bf238 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1128,4 +1128,41 @@ Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-4429: join with  range condition whose selectivity == 0   
+#                when optimizer_use_condition_selectivity=3
+#
+CREATE TABLE language (lang_group INT, lang VARCHAR(16) PRIMARY KEY);
+INSERT INTO language VALUES 
+(1,'Chinese'),(6,'English'),(1,'French'),
+(1,'German'),(1,'Italian'),(0,'Japanese');
+CREATE TABLE country (code varchar(3) PRIMARY KEY,
+country_group INT DEFAULT NULL);
+INSERT INTO country VALUES ('USA',3),('FRA',5);
+CREATE TABLE continent (cont_group INT, cont varchar(16) PRIMARY KEY);
+INSERT INTO continent VALUES
+(1,'N.America'),(1,'S.America'),(3,'Australia'),
+(4,'Africa'),(5,'Antarctica'),(6,'Eurasia');
+SET use_stat_tables=PREFERABLY;
+ANALYZE TABLE country, language, continent;
+Table	Op	Msg_type	Msg_text
+test.country	analyze	status	OK
+test.language	analyze	status	OK
+test.continent	analyze	status	OK
+FLUSH TABLES;
+SET optimizer_use_condition_selectivity=3;
+SELECT * FROM language, country, continent
+WHERE country_group = lang_group AND lang_group IS NULL;
+lang_group	lang	code	country_group	cont_group	cont
+EXPLAIN EXTENDED
+SELECT * FROM language, country, continent
+WHERE country_group = lang_group AND lang_group IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	country	ALL	NULL	NULL	NULL	NULL	2	0.00	Using where
+1	SIMPLE	language	ALL	NULL	NULL	NULL	NULL	6	0.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	continent	ALL	NULL	NULL	NULL	NULL	6	100.00	Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where ((`test`.`language`.`lang_group` = `test`.`country`.`country_group`) and isnull(`test`.`country`.`country_group`))
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table language, country, continent;
 set use_stat_tables=@save_use_stat_tables;