diff options
Diffstat (limited to 'mysql-test/r/selectivity_innodb.result')
| -rw-r--r-- | mysql-test/r/selectivity_innodb.result | 39 |
1 files changed, 38 insertions, 1 deletions
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 9607bb98765..9bd92d7c7c8 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -798,7 +798,7 @@ explain extended select * from t1 where a in ( select b from t2 ) AND ( a > 3 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 0 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 0.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 3)) select * from t1 where a in ( select b from t2 ) AND ( a > 3 ); @@ -1136,6 +1136,43 @@ 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; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; SET SESSION STORAGE_ENGINE=DEFAULT; |