From f6b65232c77336dee9eabff050a01091b2501f57 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 30 Sep 2013 17:42:18 -0700 Subject: Fixed bug mdev-4429: fixed another place where selectivity == 0 requires a special handling. --- mysql-test/t/selectivity.test | 36 ++++++++++++++++++++++++++++++++++++ 1 file changed, 36 insertions(+) (limited to 'mysql-test/t/selectivity.test') diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 47f62365816..36dab789e72 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -708,4 +708,40 @@ drop table t1; set use_stat_tables=@save_use_stat_tables; +--echo # +--echo # Bug mdev-4429: join with range condition whose selectivity == 0 +--echo # when optimizer_use_condition_selectivity=3 +--echo # + +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; +FLUSH TABLES; +SET optimizer_use_condition_selectivity=3; + +SELECT * FROM language, country, continent + WHERE country_group = lang_group AND lang_group IS NULL; +EXPLAIN EXTENDED +SELECT * FROM language, country, continent + WHERE country_group = lang_group AND lang_group IS NULL; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +drop table language, country, continent; + set use_stat_tables=@save_use_stat_tables; + + -- cgit v1.2.1