- commits - lists.mariadb.org

[Commits] 880a22e5c4a: MDEV-15306: Wrong/Unexpected result with the value optimizer_use_condition_selectivity set to 4
by Varun 27 Aug '18

27 Aug '18

revision-id: 880a22e5c4aa7239b3244ba1b83854955295e849 (mariadb-10.0.36-11-g880a22e5c4a) parent(s): 5fb251642e19c59306c32333dbba669ce160457c author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-27 20:41:35 +0530 message: MDEV-15306: Wrong/Unexpected result with the value optimizer_use_condition_selectivity set to 4 Currently for selectivity calculation we perform range analysis for a column even when we don't have any statistics(EITS). This makes less sense but is used to catch contradiction for WHERE condition. So the solution is to not perform range analysis for selectivity calculation for columns that do not have statistics. --- mysql-test/r/selectivity.result | 44 ++++++++++++++++++++++++++++++++-- mysql-test/r/selectivity_innodb.result | 44 ++++++++++++++++++++++++++++++++-- mysql-test/t/selectivity.test | 36 ++++++++++++++++++++++++++++ sql/opt_range.cc | 10 ++++++-- sql/sql_statistics.h | 29 ++++++++++++++++++---- 5 files changed, 152 insertions(+), 11 deletions(-) diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 3e8fb8e2e41..6af4f9a9ace 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -782,9 +782,9 @@ set optimizer_use_condition_selectivity=3; explain extended select * from t1 where a < 1 and a > 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` < 1) and (`test`.`t1`.`a` > 7)) select * from t1 where a < 1 and a > 7; a drop table t1; @@ -1600,3 +1600,43 @@ drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-15306: Wrong/Unexpected result with the value +# optimizer_use_condition_selectivity set to 4 +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +SET @cnt := @cnt + 1; +RETURN 1; +END;| +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +1 +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +2 +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +drop function f1; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 748ef0cb6ca..e0ed2865f13 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -789,9 +789,9 @@ set optimizer_use_condition_selectivity=3; explain extended select * from t1 where a < 1 and a > 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` < 1) and (`test`.`t1`.`a` > 7)) select * from t1 where a < 1 and a > 7; a drop table t1; @@ -1604,6 +1604,46 @@ drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-15306: Wrong/Unexpected result with the value +# optimizer_use_condition_selectivity set to 4 +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +SET @cnt := @cnt + 1; +RETURN 1; +END;| +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +1 +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +2 +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +drop function f1; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; set @tmp_oucs= @@optimizer_use_condition_selectivity; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index afaa937c360..557a4e92bca 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1064,3 +1064,39 @@ drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-15306: Wrong/Unexpected result with the value +--echo # optimizer_use_condition_selectivity set to 4 +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +delimiter |; +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN + SET @cnt := @cnt + 1; + RETURN 1; +END;| +delimiter ;| +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +SELECT @cnt; + +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +SELECT @cnt; +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +drop function f1; + diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 734adfbc9d1..ceabac2b744 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3326,13 +3326,18 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param, for (field_ptr= table->field; *field_ptr; field_ptr++) { - if (bitmap_is_set(used_fields, (*field_ptr)->field_index)) + Column_statistics* col_stats= (*field_ptr)->read_stats; + if (bitmap_is_set(used_fields, (*field_ptr)->field_index) + && col_stats && !col_stats->no_stat_values_provided()) parts++; } KEY_PART *key_part; uint keys= 0; + if (!parts) + return TRUE; + if (!(key_part= (KEY_PART *) alloc_root(param->mem_root, sizeof(KEY_PART) * parts))) return TRUE; @@ -3628,7 +3633,8 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) */ if (thd->variables.optimizer_use_condition_selectivity > 2 && - !bitmap_is_clear_all(used_fields)) + !bitmap_is_clear_all(used_fields) && + thd->variables.use_stat_tables > 0) { PARAM param; MEM_ROOT alloc; diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 6a43e42ab96..a43e0caf0fe 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -342,12 +342,17 @@ class Column_statistics public: Histogram histogram; + + uint32 default_value() + { + return + ((1 << (COLUMN_STAT_HISTOGRAM-COLUMN_STAT_COLUMN_NAME))-1) << + (COLUMN_STAT_COLUMN_NAME+1); + } void set_all_nulls() { - column_stat_nulls= - ((1 << (COLUMN_STAT_HISTOGRAM-COLUMN_STAT_COLUMN_NAME))-1) << - (COLUMN_STAT_COLUMN_NAME+1); + column_stat_nulls= default_value(); } void set_not_null(uint stat_field_no) @@ -393,8 +398,22 @@ class Column_statistics bool min_max_values_are_provided() { return !is_null(COLUMN_STAT_MIN_VALUE) && - !is_null(COLUMN_STAT_MIN_VALUE); - } + !is_null(COLUMN_STAT_MAX_VALUE); + } + /* + This function checks whether the values for the fields of the statistical + tables that were NULL by DEFAULT for a column have changed or not. + + @retval + TRUE: Statistics are not present for a column + FALSE: Statisitics are present for a column + */ + bool no_stat_values_provided() + { + if (column_stat_nulls == default_value()) + return true; + return false; + } };

1 0

[Commits] eb29f64: MDEV-17017 Explain for query using derived table specified with
by IgorBabaev 27 Aug '18

27 Aug '18

revision-id: eb29f64b998d3ab9817b525babb175c4f600da0d (mariadb-10.3.7-145-geb29f64) parent(s): 6c6ca907ee84e3f129bc33be8202c0fee2f29196 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-27 08:12:59 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows This is another attempt to fix this bug. The previous patch did not take into account that a transformation for ALL/ANY subqueries could be applied to the materialized table that wrapped the table value constructor used as a specification of the subselect used an ALL/ANY subquery. In this case the result of the derived table used a sink of the class select_subselect rather than of the class select_unit. Thus the previous fix could cause memory overwrites when running EXPLAIN for queries with table value constructors in ALL/ANY subselects. --- mysql-test/main/table_value_constr.test | 2 -- sql/sql_class.h | 5 ++--- sql/sql_lex.cc | 6 +++--- sql/table.cc | 2 +- 4 files changed, 6 insertions(+), 9 deletions(-) diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 5df40d10..0dd0a7a 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1123,5 +1123,3 @@ PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; - - diff --git a/sql/sql_class.h b/sql/sql_class.h index faae443..026ef4d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4956,7 +4956,8 @@ class select_result :public select_result_sink SELECT_LEX_UNIT *unit; /* Something used only by the parser: */ public: - select_result(THD *thd_arg): select_result_sink(thd_arg) {} + ha_rows est_records; /* estimated number of records in the result */ + select_result(THD *thd_arg): select_result_sink(thd_arg), est_records(0) {} void set_unit(SELECT_LEX_UNIT *unit_arg) { unit= unit_arg; } virtual ~select_result() {}; /** @@ -5528,7 +5529,6 @@ class select_unit :public select_result_interceptor TMP_TABLE_PARAM tmp_table_param; int write_err; /* Error code from the last send_data->ha_write_row call. */ TABLE *table; - ha_rows records; select_unit(THD *thd_arg): select_result_interceptor(thd_arg), @@ -5566,7 +5566,6 @@ class select_unit :public select_result_interceptor curr_sel= UINT_MAX; step= UNION_TYPE; write_err= 0; - records= 0; } void change_select(); }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 7d88a6c..1b1031d 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4678,18 +4678,18 @@ void SELECT_LEX::increase_derived_records(ha_rows records) return; } - select_unit *result= (select_unit*)unit->result; + select_result *result= unit->result; switch (linkage) { case INTERSECT_TYPE: // result of intersect can't be more then one of components - set_if_smaller(result->records, records); + set_if_smaller(result->est_records, records); case EXCEPT_TYPE: // in worse case none of record will be removed break; default: // usual UNION - result->records+= records; + result->est_records+= records; break; } } diff --git a/sql/table.cc b/sql/table.cc index 627f615..4aef9aa 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8334,7 +8334,7 @@ int TABLE_LIST::fetch_number_of_rows() } if (is_materialized_derived() && !fill_me) { - table->file->stats.records= ((select_unit*)(get_unit()->result))->records; + table->file->stats.records= get_unit()->result->est_records; set_if_bigger(table->file->stats.records, 2); table->used_stat_records= table->file->stats.records; }

1 0

[Commits] 321e6784546: cleanup
by jan 27 Aug '18

27 Aug '18

revision-id: 321e6784546e249f9ba106cc66b5e8d85e5173a9 (mariadb-10.3.7-145-g321e6784546) parent(s): 095de15d753807ee78e97c2a1ccfc688abb05ee7 author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-27 16:07:20 +0300 message: cleanup --- mysql-test/suite/galera/r/MW-336.result | 23 +++++++++++++++++++++++ mysql-test/suite/galera/t/MW-336.test | 11 +++++++++++ 2 files changed, 34 insertions(+) diff --git a/mysql-test/suite/galera/r/MW-336.result b/mysql-test/suite/galera/r/MW-336.result index fa5514dfc47..1b5d2984e39 100644 --- a/mysql-test/suite/galera/r/MW-336.result +++ b/mysql-test/suite/galera/r/MW-336.result @@ -74,4 +74,27 @@ INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); connection node_1; SET GLOBAL wsrep_slave_threads = 1; +connection node_2; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +connection node_1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MW-336.test b/mysql-test/suite/galera/t/MW-336.test index 7af039e577b..4bdbfb2ecff 100644 --- a/mysql-test/suite/galera/t/MW-336.test +++ b/mysql-test/suite/galera/t/MW-336.test @@ -66,4 +66,15 @@ while ($count) --source include/wait_condition.inc SET GLOBAL wsrep_slave_threads = 1; + +--connection node_2 +# Generate 21 replication events +--let $count = 21 +while ($count) +{ + INSERT INTO t1 VALUES (1); + --dec $count +} + +--connection node_1 DROP TABLE t1;

1 0

[Commits] 40d84f31c81: c
by Oleksandr Byelkin 27 Aug '18

27 Aug '18

revision-id: 40d84f31c81496f59e48cb3fa318cb0eec580bfb (mariadb-10.2.16-102-g40d84f31c81) parent(s): c634fd0cbf36605b1ec49d1ec7f613d2834e6333 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-27 13:01:02 +0200 message: c --- mysql-test/r/func_default.result | 2 +- mysql-test/r/func_time.result | 33 +++++++++++++++++++++++++++++++-- mysql-test/t/func_time.test | 15 +++++++++++++-- sql/field.cc | 8 ++------ sql/item.cc | 6 ++---- 5 files changed, 49 insertions(+), 15 deletions(-) diff --git a/mysql-test/r/func_default.result b/mysql-test/r/func_default.result index 1f331af287c..3927c387680 100644 --- a/mysql-test/r/func_default.result +++ b/mysql-test/r/func_default.result @@ -8,7 +8,7 @@ explain extended select default(str), default(strnull), default(intg), default(r id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select default(`str`) AS `default(str)`,default(`strnull`) AS `default(strnull)`,default(`intg`) AS `default(intg)`,default(`rel`) AS `default(rel)` from dual +Note 1003 select default(`test`.`t1`.`str`) AS `default(str)`,default(`test`.`t1`.`strnull`) AS `default(strnull)`,default(`test`.`t1`.`intg`) AS `default(intg)`,default(`test`.`t1`.`rel`) AS `default(rel)` from dual select * from t1 where str <> default(str); str strnull intg rel 0 0 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 5b9286323a7..1407a04e0ce 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -3288,8 +3288,37 @@ NULL NULL Warnings: Warning 1292 Incorrect datetime value: '18446744073709551615' -DROP VIEW v1; -DROP TABLE t1; +CREATE TABLE t2 (pk int default 1, a1 date); +INSERT INTO t2 VALUES (4,NULL); +CREATE view v2 as SELECT default(t1.pk), default(t2.pk), t1.pk from t1,t2; +select * from v2; +default(t1.pk) default(t2.pk) pk +0 1 1 +0 1 2 +0 1 3 +0 1 4 +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select default(`t1`.`pk`) AS `default(t1.pk)`,default(`t2`.`pk`) AS `default(t2.pk)`,`t1`.`pk` AS `pk` from (`t1` join `t2`) latin1 latin1_swedish_ci +CREATE view v3 as SELECT default(pk) from t2; +select * from v3; +default(pk) +1 +explain extended select * from v3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select default(`test`.`t2`.`pk`) AS `default(pk)` from dual +explain extended select default(pk) from t2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select default(`test`.`t2`.`pk`) AS `default(pk)` from dual +show create view v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select default(`t2`.`pk`) AS `default(pk)` from `t2` latin1 latin1_swedish_ci +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; # # End of 10.2 tests # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 0253a2d548e..0805063c395 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1876,8 +1876,19 @@ SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1; SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1; SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1; -DROP VIEW v1; -DROP TABLE t1; +CREATE TABLE t2 (pk int default 1, a1 date); +INSERT INTO t2 VALUES (4,NULL); +CREATE view v2 as SELECT default(t1.pk), default(t2.pk), t1.pk from t1,t2; +select * from v2; +show create view v2; +CREATE view v3 as SELECT default(pk) from t2; +select * from v3; +explain extended select * from v3; +explain extended select default(pk) from t2; +show create view v3; + +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; --echo # --echo # End of 10.2 tests diff --git a/sql/field.cc b/sql/field.cc index e12adbf47b3..80553cca015 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -76,18 +76,14 @@ const char field_separator=','; (!table->read_set || \ bitmap_is_set(table->read_set, field_index) || \ (!(ptr >= table->record[0] && \ - ptr < table->record[0] + table->s->reclength) && \ - !(ptr >= table->record[1] && \ - ptr < table->record[1] + table->s->reclength)))) + ptr < table->record[0] + table->s->reclength)))) #define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED \ DBUG_ASSERT(is_stat_field || !table || \ (!table->write_set || \ bitmap_is_set(table->write_set, field_index) || \ (!(ptr >= table->record[0] && \ - ptr < table->record[0] + table->s->reclength) && \ - !(ptr >= table->record[1] && \ - ptr < table->record[1] + table->s->reclength))) || \ + ptr < table->record[0] + table->s->reclength))) || \ (table->vcol_set && bitmap_is_set(table->vcol_set, field_index))) #define FLAGSTR(S,F) ((S) & (F) ? #F " " : "") diff --git a/sql/item.cc b/sql/item.cc index 70fea097d36..ab26f619768 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -8961,7 +8961,7 @@ bool Item_default_value::fix_fields(THD *thd, Item **items) */ fix_session_vcol_expr_for_read(thd, def_field, def_field->default_value); if (thd->mark_used_columns != MARK_COLUMNS_NONE) - def_field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0); + def_field->default_value->expr->update_used_tables(); def_field->move_field(newptr+1, def_field->maybe_null() ? newptr : 0, 1); } else @@ -8990,9 +8990,7 @@ void Item_default_value::print(String *str, enum_query_type query_type) tables but its name so we set QT_NO_DATA_EXPANSION (as we print for table definition, also we do not need table and database name) */ - query_type= (enum_query_type) (query_type | QT_NO_DATA_EXPANSION | - QT_ITEM_IDENT_SKIP_DB_NAMES | - QT_ITEM_IDENT_SKIP_TABLE_NAMES); + query_type= (enum_query_type) (query_type | QT_NO_DATA_EXPANSION); arg->print(str, query_type); str->append(')'); }

1 0

[Commits] e13108c7fcf: MDEV-15306: Wrong/Unexpected result with the value optimizer_use_condition_selectivity set to 4
by varunraiko1803＠gmail.com 27 Aug '18

27 Aug '18

revision-id: e13108c7fcf472faa7eaba1a7083d80c9bad4f55 (mariadb-10.3.0-696-ge13108c7fcf) parent(s): 87ee85634cae8538b922b6eb3d275a5f4343dbb1 author: Varun Gupta committer: Varun Gupta timestamp: 2018-03-30 04:50:47 +0530 message: MDEV-15306: Wrong/Unexpected result with the value optimizer_use_condition_selectivity set to 4 Currently we perform range analysis for a column even when we don't have any statistics(EITS). This makes less sense but is used to catch contradiction for WHERE condition. So the solution is to not perform range analysis for columns that do not have statistics that can be used by the optimizer. --- mysql-test/main/selectivity.result | 48 ++++++++++++++++++++++++++++--- mysql-test/main/selectivity.test | 37 ++++++++++++++++++++++++ mysql-test/main/selectivity_innodb.result | 48 ++++++++++++++++++++++++++++--- sql/opt_range.cc | 10 +++++-- sql/sql_statistics.h | 17 ++++++++++- 5 files changed, 149 insertions(+), 11 deletions(-) diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 9cb6ee3e9bf..cda2c28fa16 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -782,9 +782,9 @@ set optimizer_use_condition_selectivity=3; explain extended select * from t1 where a < 1 and a > 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select 7 AS `a` from `test`.`t1` where 0 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1 and `test`.`t1`.`a` > 7 select * from t1 where a < 1 and a > 7; a drop table t1; @@ -1506,9 +1506,9 @@ col1 explain extended select * from t2 where col1 < 'b' and col1 > 'd'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select 'd' AS `col1` from `test`.`t2` where 0 +Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` < 'b' and `test`.`t2`.`col1` > 'd' drop table t1,t2; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; @@ -1533,3 +1533,43 @@ DROP TABLE t1; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-15306: Wrong/Unexpected result with the value +# optimizer_use_condition_selectivity set to 4 +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +SET @cnt := @cnt + 1; +RETURN 1; +END;| +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +1 +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +2 +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +drop function f1; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index 3e60f242083..77343beadce 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1048,3 +1048,40 @@ DROP TABLE t1; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-15306: Wrong/Unexpected result with the value +--echo # optimizer_use_condition_selectivity set to 4 +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +delimiter |; +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +SET @cnt := @cnt + 1; +RETURN 1; +END;| +delimiter ;| + +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +SELECT @cnt; + +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +SELECT @cnt; +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +drop table t1; +drop function f1; diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 236647c6091..01783b978ff 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -789,9 +789,9 @@ set optimizer_use_condition_selectivity=3; explain extended select * from t1 where a < 1 and a > 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select 7 AS `a` from `test`.`t1` where 0 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1 and `test`.`t1`.`a` > 7 select * from t1 where a < 1 and a > 7; a drop table t1; @@ -1516,9 +1516,9 @@ col1 explain extended select * from t2 where col1 < 'b' and col1 > 'd'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select 'd' AS `col1` from `test`.`t2` where 0 +Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` < 'b' and `test`.`t2`.`col1` > 'd' drop table t1,t2; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; @@ -1543,6 +1543,46 @@ DROP TABLE t1; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-15306: Wrong/Unexpected result with the value +# optimizer_use_condition_selectivity set to 4 +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +SET @cnt := @cnt + 1; +RETURN 1; +END;| +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +1 +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +2 +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +drop function f1; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; set @tmp_oucs= @@optimizer_use_condition_selectivity; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 38dbed92a22..269d4af667d 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2733,13 +2733,18 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param, for (field_ptr= table->field; *field_ptr; field_ptr++) { - if (bitmap_is_set(used_fields, (*field_ptr)->field_index)) + Column_statistics* col_stats= (*field_ptr)->read_stats; + if (col_stats && !col_stats->check_all_values_are_default() + && bitmap_is_set(used_fields, (*field_ptr)->field_index)) parts++; } KEY_PART *key_part; uint keys= 0; + if(!parts) + return TRUE; + if (!(key_part= (KEY_PART *) alloc_root(param->mem_root, sizeof(KEY_PART) * parts))) return TRUE; @@ -3025,7 +3030,8 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) */ if (thd->variables.optimizer_use_condition_selectivity > 2 && - !bitmap_is_clear_all(used_fields)) + !bitmap_is_clear_all(used_fields) && + thd->variables.use_stat_tables >0) { PARAM param; MEM_ROOT alloc; diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 7ec742b70db..4a7b0df0caf 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -383,7 +383,22 @@ class Column_statistics bool min_max_values_are_provided() { return !is_null(COLUMN_STAT_MIN_VALUE) && - !is_null(COLUMN_STAT_MIN_VALUE); + !is_null(COLUMN_STAT_MAX_VALUE); + } + + /* + This function checks whether the values for the fields of the statistical + tables that were NULL by DEFAULT for a column have changed or not. + + @retval + TRUE: Statistics are not present for a column + FALSE: Statisitics are present for a column + */ + bool check_all_values_are_default() + { + return !(column_stat_nulls ^ + ((1 << (COLUMN_STAT_HISTOGRAM-COLUMN_STAT_COLUMN_NAME))-1) << + (COLUMN_STAT_COLUMN_NAME+1)); } };

2 1

[Commits] 095de15d753: MDEV-17062: Test failure on galera.MW-336
by jan 27 Aug '18

27 Aug '18

revision-id: 095de15d753807ee78e97c2a1ccfc688abb05ee7 (mariadb-10.3.7-144-g095de15d753) parent(s): 2c76653849c1729bedc213b233089bf361c82e12 author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-27 12:03:02 +0300 message: MDEV-17062: Test failure on galera.MW-336 MDEV-17058: Test failure on wsrep.variables MDEV-17060: Test failure on galera.galera_var_slave_threads Fix incorrect calculation of increased applier (slave) threads. Note that increase change takes effect "immediately" but we should use proper wait condition to wait it. Reducing the number of slave threads is not immediate as thread will only exit after a replication event. --- mysql-test/suite/galera/r/MW-336.result | 77 ++++++++++++++++++++++++--------- mysql-test/suite/galera/t/MW-336.test | 54 ++++++++++++----------- mysql-test/suite/wsrep/t/variables.test | 9 +++- sql/wsrep_var.cc | 4 +- 4 files changed, 95 insertions(+), 49 deletions(-) diff --git a/mysql-test/suite/galera/r/MW-336.result b/mysql-test/suite/galera/r/MW-336.result index 0bf8d9d3909..fa5514dfc47 100644 --- a/mysql-test/suite/galera/r/MW-336.result +++ b/mysql-test/suite/galera/r/MW-336.result @@ -2,39 +2,76 @@ CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; connection node_1; SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 1; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); +COUNT(*) +11 +SHOW STATUS LIKE 'wsrep_thread_count'; +Variable_name Value +wsrep_thread_count 11 connection node_2; INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); connection node_1; SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 20; SET GLOBAL wsrep_slave_threads = 1; connection node_2; INSERT INTO t1 VALUES (1); -INSERT INTO t1 VALUES (2); -INSERT INTO t1 VALUES (3); -INSERT INTO t1 VALUES (4); -INSERT INTO t1 VALUES (5); -INSERT INTO t1 VALUES (6); -INSERT INTO t1 VALUES (7); -INSERT INTO t1 VALUES (8); -INSERT INTO t1 VALUES (9); -connection node_1; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 0; Warnings: Warning 1292 Truncated incorrect wsrep_slave_threads value: '0' connection node_2; -INSERT INTO t1 VALUES (10); -INSERT INTO t1 VALUES (11); -INSERT INTO t1 VALUES (12); -INSERT INTO t1 VALUES (13); -INSERT INTO t1 VALUES (14); -INSERT INTO t1 VALUES (15); -INSERT INTO t1 VALUES (16); -INSERT INTO t1 VALUES (17); -INSERT INTO t1 VALUES (18); -INSERT INTO t1 VALUES (19); -INSERT INTO t1 VALUES (20); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); connection node_1; SET GLOBAL wsrep_slave_threads = 1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MW-336.test b/mysql-test/suite/galera/t/MW-336.test index 8cd363aa019..7af039e577b 100644 --- a/mysql-test/suite/galera/t/MW-336.test +++ b/mysql-test/suite/galera/t/MW-336.test @@ -8,15 +8,28 @@ CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; --connection node_1 + SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 1; + +--let $wait_timeout=600 --let $wait_condition = SELECT COUNT(*) = 11 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); --source include/wait_condition.inc +SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); +SHOW STATUS LIKE 'wsrep_thread_count'; + --connection node_2 -INSERT INTO t1 VALUES (1); +# Generate 11 replication events +--let $count = 11 +while ($count) +{ + INSERT INTO t1 VALUES (1); + --dec $count +} --connection node_1 + SET GLOBAL wsrep_slave_threads = 10; --let $wait_condition = SELECT COUNT(*) = 11 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); --source include/wait_condition.inc @@ -28,36 +41,25 @@ SET GLOBAL wsrep_slave_threads = 20; SET GLOBAL wsrep_slave_threads = 1; --connection node_2 -INSERT INTO t1 VALUES (1); -INSERT INTO t1 VALUES (2); -INSERT INTO t1 VALUES (3); -INSERT INTO t1 VALUES (4); -INSERT INTO t1 VALUES (5); -INSERT INTO t1 VALUES (6); -INSERT INTO t1 VALUES (7); -INSERT INTO t1 VALUES (8); -INSERT INTO t1 VALUES (9); - - ---connection node_1 ---let $wait_condition = SELECT COUNT(*) = 12 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); ---source include/wait_condition.inc +# Generate 21 replication events +--let $count = 21 +while ($count) +{ + INSERT INTO t1 VALUES (1); + --dec $count +} SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 0; --connection node_2 -INSERT INTO t1 VALUES (10); -INSERT INTO t1 VALUES (11); -INSERT INTO t1 VALUES (12); -INSERT INTO t1 VALUES (13); -INSERT INTO t1 VALUES (14); -INSERT INTO t1 VALUES (15); -INSERT INTO t1 VALUES (16); -INSERT INTO t1 VALUES (17); -INSERT INTO t1 VALUES (18); -INSERT INTO t1 VALUES (19); -INSERT INTO t1 VALUES (20); +# Generate 21 replication events +--let $count = 21 +while ($count) +{ + INSERT INTO t1 VALUES (1); + --dec $count +} --connection node_1 --let $wait_condition = SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); diff --git a/mysql-test/suite/wsrep/t/variables.test b/mysql-test/suite/wsrep/t/variables.test index 1315f090d5c..40b869422ff 100644 --- a/mysql-test/suite/wsrep/t/variables.test +++ b/mysql-test/suite/wsrep/t/variables.test @@ -101,8 +101,10 @@ SHOW STATUS LIKE 'wsrep_thread_count'; --echo # Setting wsrep_cluster_address triggers the creation of --echo # applier/rollbacker threads. SET GLOBAL wsrep_cluster_address= 'gcomm://'; + --echo # Wait for applier threads to get created. -sleep 3; +--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_thread_count'; +--source include/wait_condition.inc --replace_regex /.*libgalera_smm.*/libgalera_smm.so/ SELECT @@global.wsrep_provider; @@ -113,8 +115,11 @@ SHOW STATUS LIKE 'wsrep_thread_count'; SET @wsrep_slave_threads_saved= @@global.wsrep_slave_threads; SET GLOBAL wsrep_slave_threads= 10; + --echo # Wait for applier threads to get created. -sleep 3; +--let $wait_condition = SELECT VARIABLE_VALUE = 11 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_thread_count'; +--source include/wait_condition.inc + SHOW STATUS LIKE 'threads_connected'; SHOW STATUS LIKE 'wsrep_thread_count'; diff --git a/sql/wsrep_var.cc b/sql/wsrep_var.cc index 7fe120beb8e..aaae487b2e2 100644 --- a/sql/wsrep_var.cc +++ b/sql/wsrep_var.cc @@ -594,7 +594,9 @@ void wsrep_node_address_init (const char* value) static void wsrep_slave_count_change_update () { - wsrep_slave_count_change += (wsrep_slave_threads - wsrep_prev_slave_threads); + wsrep_slave_count_change = (wsrep_slave_threads - wsrep_prev_slave_threads); + WSREP_DEBUG("Change on slave threads: New %lu old %lu difference %lu", + wsrep_slave_threads, wsrep_prev_slave_threads, wsrep_slave_count_change); wsrep_prev_slave_threads = wsrep_slave_threads; }

1 0

[Commits] 7d8d37c31d9: MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4
by Varun 26 Aug '18

26 Aug '18

revision-id: 7d8d37c31d9cabaf31576eb3edeae6580ffb16b4 (mariadb-10.0.36-8-g7d8d37c31d9) parent(s): bcc677bb7264db08b22284998706b44c377ed8ec author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-23 16:01:58 +0530 message: MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 and use_stat_tables= PREFERABLY Currently the code that calculates selectivity for a table does not take into account the case when we can have GROUP BY optimization (looses index scan). --- mysql-test/r/group_min_max.result | 28 ++++++++++++++++++++++++++++ mysql-test/t/group_min_max.test | 17 +++++++++++++++++ sql/opt_range.cc | 13 ++++++++++++- 3 files changed, 57 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 25cd4a25279..34e1e256faa 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -3733,5 +3733,33 @@ id MIN(a) MAX(a) 4 2001-01-04 2001-01-04 DROP TABLE t1; # +# Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +# and use_stat_tables= PREFERABLY +# +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`) +drop table t1; +# # End of 10.0 tests # diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 8c9be0ca8db..72a556c9bc2 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1519,6 +1519,23 @@ ALTER TABLE t1 ADD KEY(id,a); SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id; DROP TABLE t1; +--echo # +--echo # Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +--echo # and use_stat_tables= PREFERABLY +--echo # + +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +drop table t1; --echo # --echo # End of 10.0 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f1d84e5c623..734adfbc9d1 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3501,7 +3501,18 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) table->cond_selectivity= 1.0; - if (!cond || table_records == 0) + if (table_records == 0) + DBUG_RETURN(FALSE); + + QUICK_SELECT_I *quick; + if ((quick=table->reginfo.join_tab->quick) && + quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) + { + table->cond_selectivity*= (quick->records/table_records); + DBUG_RETURN(FALSE); + } + + if (!cond) DBUG_RETURN(FALSE); if (table->pos_in_table_list->schema_table)

2 1

[Commits] 65c2610: MDEV-17017 Explain for query using derived table specified with
by IgorBabaev 26 Aug '18

26 Aug '18

revision-id: 65c26107395c7cbd9b5a4e5c97f48d64382a799c (mariadb-10.3.7-145-g65c2610) parent(s): 6c6ca907ee84e3f129bc33be8202c0fee2f29196 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-26 11:24:00 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows This is another attempt to fix this bug. The previous patch did not take into account that a transformation for ALL/ANY subqueries could be applied to the materialized table that wrapped the table value constructor used as a specification of the subselect used an ALL/ANY subquery. In this case the result of the derived table used a sink of the class select_subselect rather than of the class select_unit. Thus the previous fix could cause memory overwrites when running EXPLAIN for queries with table value constructors in ALL/ANY subselects. --- mysql-test/main/table_value_constr.test | 2 -- sql/sql_class.h | 5 ++--- sql/sql_lex.cc | 6 +++--- sql/table.cc | 2 +- 4 files changed, 6 insertions(+), 9 deletions(-) diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 5df40d10..0dd0a7a 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1123,5 +1123,3 @@ PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; - - diff --git a/sql/sql_class.h b/sql/sql_class.h index faae443..026ef4d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4956,7 +4956,8 @@ class select_result :public select_result_sink SELECT_LEX_UNIT *unit; /* Something used only by the parser: */ public: - select_result(THD *thd_arg): select_result_sink(thd_arg) {} + ha_rows est_records; /* estimated number of records in the result */ + select_result(THD *thd_arg): select_result_sink(thd_arg), est_records(0) {} void set_unit(SELECT_LEX_UNIT *unit_arg) { unit= unit_arg; } virtual ~select_result() {}; /** @@ -5528,7 +5529,6 @@ class select_unit :public select_result_interceptor TMP_TABLE_PARAM tmp_table_param; int write_err; /* Error code from the last send_data->ha_write_row call. */ TABLE *table; - ha_rows records; select_unit(THD *thd_arg): select_result_interceptor(thd_arg), @@ -5566,7 +5566,6 @@ class select_unit :public select_result_interceptor curr_sel= UINT_MAX; step= UNION_TYPE; write_err= 0; - records= 0; } void change_select(); }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 7d88a6c..1b1031d 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4678,18 +4678,18 @@ void SELECT_LEX::increase_derived_records(ha_rows records) return; } - select_unit *result= (select_unit*)unit->result; + select_result *result= unit->result; switch (linkage) { case INTERSECT_TYPE: // result of intersect can't be more then one of components - set_if_smaller(result->records, records); + set_if_smaller(result->est_records, records); case EXCEPT_TYPE: // in worse case none of record will be removed break; default: // usual UNION - result->records+= records; + result->est_records+= records; break; } } diff --git a/sql/table.cc b/sql/table.cc index 627f615..4aef9aa 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8334,7 +8334,7 @@ int TABLE_LIST::fetch_number_of_rows() } if (is_materialized_derived() && !fill_me) { - table->file->stats.records= ((select_unit*)(get_unit()->result))->records; + table->file->stats.records= get_unit()->result->est_records; set_if_bigger(table->file->stats.records, 2); table->used_stat_records= table->file->stats.records; }

1 0

[Commits] d3e0dbb: MDEV-17017 Explain for query using derived table specified with
by IgorBabaev 26 Aug '18

26 Aug '18

revision-id: d3e0dbbd37131cd5da553244980ecb2490bbee63 (mariadb-10.3.7-145-gd3e0dbb) parent(s): 6c6ca907ee84e3f129bc33be8202c0fee2f29196 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-26 10:49:31 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows This is another attempt to fix this bug. The previous patch did not take into account that a transformation for ALL/ANY subqueries could be applied to the materialized table that wrapped the table value constructor used as a specification of the subselect used an ALL/ANY subquery. In this case the result of the derived table used a sink of the class select_subselect rather than of the class select_unit. Thus the previous fix could cause memory overwrites when running EXPLAIN for queries with table value constructors in ALL/ANY subselects. --- sql/sql_class.h | 5 ++--- sql/sql_lex.cc | 6 +++--- sql/table.cc | 2 +- 3 files changed, 6 insertions(+), 7 deletions(-) diff --git a/sql/sql_class.h b/sql/sql_class.h index faae443..32520b6 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4956,7 +4956,8 @@ class select_result :public select_result_sink SELECT_LEX_UNIT *unit; /* Something used only by the parser: */ public: - select_result(THD *thd_arg): select_result_sink(thd_arg) {} + ha_rows est_records; + select_result(THD *thd_arg): select_result_sink(thd_arg), est_records(0) {} void set_unit(SELECT_LEX_UNIT *unit_arg) { unit= unit_arg; } virtual ~select_result() {}; /** @@ -5528,7 +5529,6 @@ class select_unit :public select_result_interceptor TMP_TABLE_PARAM tmp_table_param; int write_err; /* Error code from the last send_data->ha_write_row call. */ TABLE *table; - ha_rows records; select_unit(THD *thd_arg): select_result_interceptor(thd_arg), @@ -5566,7 +5566,6 @@ class select_unit :public select_result_interceptor curr_sel= UINT_MAX; step= UNION_TYPE; write_err= 0; - records= 0; } void change_select(); }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 7d88a6c..1b1031d 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4678,18 +4678,18 @@ void SELECT_LEX::increase_derived_records(ha_rows records) return; } - select_unit *result= (select_unit*)unit->result; + select_result *result= unit->result; switch (linkage) { case INTERSECT_TYPE: // result of intersect can't be more then one of components - set_if_smaller(result->records, records); + set_if_smaller(result->est_records, records); case EXCEPT_TYPE: // in worse case none of record will be removed break; default: // usual UNION - result->records+= records; + result->est_records+= records; break; } } diff --git a/sql/table.cc b/sql/table.cc index 627f615..4aef9aa 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8334,7 +8334,7 @@ int TABLE_LIST::fetch_number_of_rows() } if (is_materialized_derived() && !fill_me) { - table->file->stats.records= ((select_unit*)(get_unit()->result))->records; + table->file->stats.records= get_unit()->result->est_records; set_if_bigger(table->file->stats.records, 2); table->used_stat_records= table->file->stats.records; }

1 0

[Commits] 3e7f9495082: MDEV-16695: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with
by Varun 26 Aug '18

26 Aug '18

revision-id: 3e7f949508210b5cc308468d5775b5e2c7063ded (mariadb-10.0.36-10-g3e7f9495082) parent(s): 0cafc1316410d118c0b6298daf4d923b3258c502 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-24 13:50:16 +0530 message: MDEV-16695: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with optimizer_use_condition_selectivity>=3 Selectivity analysis should be disabled for Geometrical columns. --- mysql-test/r/gis.result | 16 ++++++++++++++++ mysql-test/t/gis.test | 15 +++++++++++++++ sql/opt_range.cc | 9 ++++++++- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index f77cd4d14bf..40d3117de38 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1659,5 +1659,21 @@ c2 DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; # +# MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +# optimizer_use_condition_selectivity>=3 +# +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +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; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +COUNT(*) +0 +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; +# # End 10.0 tests # diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 4192a3284b7..e3b264b8194 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1516,6 +1516,21 @@ SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FR DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +--echo # optimizer_use_condition_selectivity>=3 +--echo # + +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +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; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; --echo # --echo # End 10.0 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 734adfbc9d1..303f92f9bb6 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3326,13 +3326,17 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param, for (field_ptr= table->field; *field_ptr; field_ptr++) { - if (bitmap_is_set(used_fields, (*field_ptr)->field_index)) + if (bitmap_is_set(used_fields, (*field_ptr)->field_index) && + !((*field_ptr)->type() == MYSQL_TYPE_GEOMETRY)) parts++; } KEY_PART *key_part; uint keys= 0; + if (!parts) + return TRUE; + if (!(key_part= (KEY_PART *) alloc_root(param->mem_root, sizeof(KEY_PART) * parts))) return TRUE; @@ -3344,6 +3348,9 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param, if (bitmap_is_set(used_fields, (*field_ptr)->field_index)) { Field *field= *field_ptr; + if (field->type() == MYSQL_TYPE_GEOMETRY) + continue; + uint16 store_length; uint16 max_key_part_length= (uint16) table->file->max_key_part_length(); key_part->key= keys;

2 1