revision-id: ef25cbea8ec8cbd23f281890ad60136707db9608 (mariadb-10.1.34-44-gef25cbea8ec) parent(s): b9f0112248ee085199b2918f2c9d74ec7099a21e author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-14 19:02:57 +0530 message: MDEV-16921: Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3 In this issue we try to read values from the statistic table for a column even when we have the table mysql.column_stats doen not have any such statistics for this column. This leads to differnt query plans. So the solution here would be not to read values from stats table if we have no such statistics for a column. --- mysql-test/r/derived.result | 22 ++++++++++++++++++++++ mysql-test/t/derived.test | 18 ++++++++++++++++++ sql/sql_statistics.cc | 2 +- sql/sql_statistics.h | 6 ++++++ 4 files changed, 47 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 6f786e34a9a..2ae5fd24fdd 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1154,5 +1154,27 @@ a 5 DROP TABLE t1; # +# MDEV-16921: Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3 +# +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (1), (1); +CREATE TABLE t2 (b int) ; +INSERT INTO t2 VALUES (9), (NULL), (7); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +analyze SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 2.00 100.00 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 33.33 Using where; Using join buffer (flat, BNL join) +SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; +a b +1 NULL +1 NULL +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; +# # End of 10.1 tests # diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 778d141b80f..68209def125 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -989,6 +989,24 @@ SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1; SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1; DROP TABLE t1; +--echo # +--echo # MDEV-16921: Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3 +--echo # + +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (1), (1); +CREATE TABLE t2 (b int) ; +INSERT INTO t2 VALUES (9), (NULL), (7); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +analyze SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; +SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index a1c21421c59..8a59415d051 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3800,7 +3800,7 @@ double get_column_range_cardinality(Field *field, Column_statistics *col_stats= table->field[field->field_index]->read_stats; double tab_records= table->stat_records(); - if (!col_stats) + if (!col_stats || col_stats->is_all_nulls()) return tab_records; /* Use statistics for a table only when we have actually read diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 0611c021e88..d9f65664871 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -398,6 +398,12 @@ class Column_statistics return !is_null(COLUMN_STAT_MIN_VALUE) && !is_null(COLUMN_STAT_MIN_VALUE); } + bool is_all_nulls() + { + return column_stat_nulls == + ((1 << (COLUMN_STAT_HISTOGRAM-COLUMN_STAT_COLUMN_NAME))-1) << + (COLUMN_STAT_COLUMN_NAME+1); + } };