diff options
| -rw-r--r-- | mysql-test/r/selectivity.result | 22 | ||||
| -rw-r--r-- | mysql-test/r/selectivity_innodb.result | 22 | ||||
| -rw-r--r-- | mysql-test/t/selectivity.test | 28 | ||||
| -rw-r--r-- | sql/sql_statistics.cc | 1 |
4 files changed, 73 insertions, 0 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 902288ee220..8371b0eb0b6 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -989,4 +989,26 @@ a b set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1,t2,t3; set use_stat_tables=@save_use_stat_tables; +# +# Bug mdev-4389: join with degenerated range condition in WHERE +# when optimizer_use_condition_selectivity=3 +# +set use_stat_tables=PREFERABLY; +CREATE TABLE t1 (f1 VARCHAR(1)); +INSERT t1 VALUES ('p'),('q'); +CREATE TABLE t2 (f2 VARCHAR(1)); +INSERT INTO t2 VALUES +('o'),('v'),('f'),('f'),('e'),('l'),('j'),('p'),('r'),('j'), +('j'),('u'),('i'),('r'),('x'),('a'),('x'),('s'); +ANALYZE TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +FLUSH TABLES; +SET optimizer_use_condition_selectivity=3; +SELECT * FROM t1, t2 AS t2a, t2 AS t2b WHERE f1 <= 'a' AND t2a.f2 = f1; +f1 f2 f2 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +DROP TABLE t1,t2; +set use_stat_tables=@save_use_stat_tables; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 0f9ee9c5841..eba0ed2f32a 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -997,6 +997,28 @@ a b set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1,t2,t3; set use_stat_tables=@save_use_stat_tables; +# +# Bug mdev-4389: join with degenerated range condition in WHERE +# when optimizer_use_condition_selectivity=3 +# +set use_stat_tables=PREFERABLY; +CREATE TABLE t1 (f1 VARCHAR(1)); +INSERT t1 VALUES ('p'),('q'); +CREATE TABLE t2 (f2 VARCHAR(1)); +INSERT INTO t2 VALUES +('o'),('v'),('f'),('f'),('e'),('l'),('j'),('p'),('r'),('j'), +('j'),('u'),('i'),('r'),('x'),('a'),('x'),('s'); +ANALYZE TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +FLUSH TABLES; +SET optimizer_use_condition_selectivity=3; +SELECT * FROM t1, t2 AS t2a, t2 AS t2b WHERE f1 <= 'a' AND t2a.f2 = f1; +f1 f2 f2 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +DROP TABLE t1,t2; +set use_stat_tables=@save_use_stat_tables; set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 635d9d040a6..56908e50c65 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -591,5 +591,33 @@ DROP TABLE t1,t2,t3; set use_stat_tables=@save_use_stat_tables; +--echo # +--echo # Bug mdev-4389: join with degenerated range condition in WHERE +--echo # when optimizer_use_condition_selectivity=3 +--echo # + +set use_stat_tables=PREFERABLY; + +CREATE TABLE t1 (f1 VARCHAR(1)); +INSERT t1 VALUES ('p'),('q'); + +CREATE TABLE t2 (f2 VARCHAR(1)); +INSERT INTO t2 VALUES + ('o'),('v'),('f'),('f'),('e'),('l'),('j'),('p'),('r'),('j'), + ('j'),('u'),('i'),('r'),('x'),('a'),('x'),('s'); + +ANALYZE TABLE t1, t2; +FLUSH TABLES; + +SET optimizer_use_condition_selectivity=3; + +SELECT * FROM t1, t2 AS t2a, t2 AS t2b WHERE f1 <= 'a' AND t2a.f2 = f1; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +DROP TABLE t1,t2; + +set use_stat_tables=@save_use_stat_tables; + set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index efd5af7adbf..df86686f773 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3417,6 +3417,7 @@ double get_column_range_cardinality(Field *field, else sel= hist->range_selectivity(min_mp_pos, max_mp_pos); res= col_non_nulls * sel; + set_if_bigger(res, col_stats->get_avg_frequency()); } else res= col_non_nulls; |