- commits - lists.mariadb.org

[Commits] 8fb140b9f8f: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log
by Sergei Petrunia 19 Jan '22

19 Jan '22

revision-id: 8fb140b9f8f8b10c1d952247f499d87265fddac9 (mariadb-10.3.31-70-g8fb140b9f8f) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 23:14:00 +0300 message: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log The problem affected queries in form: SELECT FROM (SELECT where Split Materialized is applicable) WHERE 1=0 The problem was caused by this: - The select in derived table uses two-phase optimization (due to a possible Split Materialized). - The primary select has "Impossible where" and so it short-cuts its optimization. - The optimization for the SELECT in the derived table is never finished, and EXPLAIN data structure has a dangling pointer to select #2. Fixed with this: make JOIN::optimize_stage2() invoke optimization of derived tables when it is handing a degenerate JOIN with zero tables. We will not execute the derived tables but we need their query plans for [SHOW]EXPLAIN. --- mysql-test/main/explain_innodb.result | 20 ++++++++++++++++++++ mysql-test/main/explain_innodb.test | 20 ++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 3 files changed, 48 insertions(+) diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result new file mode 100644 index 00000000000..fe51e45e35d --- /dev/null +++ b/mysql-test/main/explain_innodb.result @@ -0,0 +1,20 @@ +# +# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +# +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +id +select 1; +1 +1 +explain +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DERIVED t1 index NULL id 53 NULL 1 Using index +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test new file mode 100644 index 00000000000..2c29a6e26da --- /dev/null +++ b/mysql-test/main/explain_innodb.test @@ -0,0 +1,20 @@ +--echo # +--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +--echo # + +--source include/have_innodb.inc + +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +select 1; + +explain +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; + +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fe02e7b44e4..8a0c649f523 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2732,6 +2732,14 @@ int JOIN::optimize_stage2() } if (make_aggr_tables_info()) DBUG_RETURN(1); + + /* + It could be that we've only done optimization stage 1 for + some of the derived tables, and never did stage 2. + Do it now, otherwise Explain data structure will not be complete. + */ + if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) + DBUG_RETURN(1); } /* Even with zero matching rows, subqueries in the HAVING clause may

1 0

[Commits] d5c412f0954: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log
by Sergei Petrunia 19 Jan '22

19 Jan '22

revision-id: d5c412f09547a232ba5ae42aefabd0c22d96d756 (mariadb-10.3.31-70-gd5c412f0954) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 23:11:12 +0300 message: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log The problem affected queries in form: SELECT FROM (SELECT where Split Materialized is applicable) WHERE 1=0 The problem was caused by this: - The select in derived table uses two-phase optimization (due to a possible LATERAL DERIVED). - The primary select has "Impossible where" and so it short-cuts its optimization. - The optimization for the SELECT in the derived table is never finished, and EXPLAIN data structure has a dangling pointer to select #2. Fixed with this: if the select has "Impossible where", do not add links to derived table subselects into the EXPLAIN data structure. We are not going to execute those anyway. --- mysql-test/main/explain_innodb.result | 20 ++++++++++++++++++++ mysql-test/main/explain_innodb.test | 20 ++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 3 files changed, 48 insertions(+) diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result new file mode 100644 index 00000000000..fe51e45e35d --- /dev/null +++ b/mysql-test/main/explain_innodb.result @@ -0,0 +1,20 @@ +# +# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +# +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +id +select 1; +1 +1 +explain +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DERIVED t1 index NULL id 53 NULL 1 Using index +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test new file mode 100644 index 00000000000..2c29a6e26da --- /dev/null +++ b/mysql-test/main/explain_innodb.test @@ -0,0 +1,20 @@ +--echo # +--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +--echo # + +--source include/have_innodb.inc + +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +select 1; + +explain +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; + +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fe02e7b44e4..8a0c649f523 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2732,6 +2732,14 @@ int JOIN::optimize_stage2() } if (make_aggr_tables_info()) DBUG_RETURN(1); + + /* + It could be that we've only done optimization stage 1 for + some of the derived tables, and never did stage 2. + Do it now, otherwise Explain data structure will not be complete. + */ + if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) + DBUG_RETURN(1); } /* Even with zero matching rows, subqueries in the HAVING clause may

1 0

[Commits] d3c6620b582: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log
by Sergei Petrunia 19 Jan '22

19 Jan '22

revision-id: d3c6620b58215664cbc2568243c164fa1b89a2c0 (mariadb-10.3.31-70-gd3c6620b582) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 22:45:11 +0300 message: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log The problem affected queries in form: SELECT FROM (SELECT where Split Materialized is applicable) WHERE 1=0 The problem was caused by this: - The select in derived table uses two-phase optimization (due to a possible LATERAL DERIVED). - The primary select has "Impossible where" and so it short-cuts its optimization. - The optimization for the SELECT in the derived table is never finished, and EXPLAIN data structure has a dangling pointer to select #2. Fixed with this: if the select has "Impossible where", do not add links to derived table subselects into the EXPLAIN data structure. We are not going to execute those anyway. --- mysql-test/main/explain_innodb.result | 20 ++++++++++++++++++++ mysql-test/main/explain_innodb.test | 20 ++++++++++++++++++++ sql/sql_select.cc | 7 +++++++ 3 files changed, 47 insertions(+) diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result new file mode 100644 index 00000000000..fe51e45e35d --- /dev/null +++ b/mysql-test/main/explain_innodb.result @@ -0,0 +1,20 @@ +# +# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +# +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +id +select 1; +1 +1 +explain +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DERIVED t1 index NULL id 53 NULL 1 Using index +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test new file mode 100644 index 00000000000..2c29a6e26da --- /dev/null +++ b/mysql-test/main/explain_innodb.test @@ -0,0 +1,20 @@ +--echo # +--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +--echo # + +--source include/have_innodb.inc + +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +select 1; + +explain +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; + +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fe02e7b44e4..0de7d940479 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2739,6 +2739,13 @@ int JOIN::optimize_stage2() */ if (optimize_unflattened_subqueries()) DBUG_RETURN(1); + + /* + We also need to finish optimization of derived tables that we are not using. + Just to show them in [SHOW]EXPLAIN. + */ + if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) + DBUG_RETURN(1); error= 0; derived_exit:

1 0

[Commits] ce4956f3229: Code cleanup
by psergey 19 Jan '22

19 Jan '22

revision-id: ce4956f3229a8b2c26a4913fdbc190b5c822cb8e (mariadb-10.6.1-364-gce4956f3229) parent(s): f7e49c98e6c1d86471cdc6da142a3cbf0fed9565 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 18:14:07 +0300 message: Code cleanup --- include/json_lib.h | 9 --------- mysql-test/include/json_hb_histogram.inc | 2 +- sql/opt_histogram_json.cc | 2 +- sql/opt_histogram_json.h | 7 +++---- sql/sql_statistics.cc | 6 ++---- sql/sql_statistics.h | 6 ++---- strings/json_lib.c | 10 +++++----- 7 files changed, 14 insertions(+), 28 deletions(-) diff --git a/include/json_lib.h b/include/json_lib.h index 34385bd8217..853c4a6f7fa 100644 --- a/include/json_lib.h +++ b/include/json_lib.h @@ -282,15 +282,6 @@ int json_key_matches(json_engine_t *je, json_string_t *k); */ int json_read_value(json_engine_t *j); -/* - json_smart_read_value() reads a JSON value. Pointer to value is stored in - *value and its length in *value_len. - - if the value is non a scalar, it returns pointers to its JSON - representation. - The function should only be called when je->state==JST_VALUE. -*/ -enum json_types json_smart_read_value(json_engine_t *je, const char **value, int *value_len); /* json_skip_key() makes parser skip the content of the current diff --git a/mysql-test/include/json_hb_histogram.inc b/mysql-test/include/json_hb_histogram.inc index 0805a7f0e53..0820fb5fccb 100644 --- a/mysql-test/include/json_hb_histogram.inc +++ b/mysql-test/include/json_hb_histogram.inc @@ -1,3 +1,3 @@ -# The time on ANALYSE FORMAT=JSON is rather variable +# Remove non-deterministic parts of JSON_HB histogram --replace_regex /("(collected_at|collected_by)": )"[^"]*"/\1"REPLACED"/ diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc index 55848c58e2d..f2be4e5c0fb 100644 --- a/sql/opt_histogram_json.cc +++ b/sql/opt_histogram_json.cc @@ -914,7 +914,7 @@ double position_in_interval(Field *field, const uchar *key, uint key_len, double Histogram_json_hb::point_selectivity(Field *field, key_range *endpoint, - double avg_sel, double total_rows) + double avg_sel) { const uchar *key = endpoint->key; if (field->real_maybe_null()) diff --git a/sql/opt_histogram_json.h b/sql/opt_histogram_json.h index 48e9a29c8a5..e9b69869f4b 100644 --- a/sql/opt_histogram_json.h +++ b/sql/opt_histogram_json.h @@ -41,8 +41,8 @@ ] } - The histogram is an object with single member named Histogram_json_hb:: - JSON_NAME. The value of that member is an array of buckets. + Histogram is a JSON object. It has some global properties and "histogram_hb" + member whose value is a JSON array of histogram buckets. Each bucket is an object with these members: "start" - the first value in the bucket. @@ -126,8 +126,7 @@ class Histogram_json_hb : public Histogram_base ulonglong size) override; double point_selectivity(Field *field, key_range *endpoint, - double avg_selection, - double total_rows) override; + double avg_sel) override; double range_selectivity(Field *field, key_range *min_endp, key_range *max_endp, double avg_sel) override; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index fc8e38173c7..84d0902193b 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3894,8 +3894,7 @@ double get_column_range_cardinality(Field *field, { res= col_non_nulls * hist->point_selectivity(field, min_endp, - avg_frequency / col_non_nulls, - tab_records); + avg_frequency / col_non_nulls); } } else if (avg_frequency == 0.0) @@ -3989,8 +3988,7 @@ double get_column_range_cardinality(Field *field, */ double Histogram_binary::point_selectivity(Field *field, key_range *endpoint, - double avg_sel, - double total_records) + double avg_sel) { double sel; Column_statistics *col_stats= field->read_stats; diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 1950f3268a7..c0df15ea4ad 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -188,8 +188,7 @@ class Histogram_base virtual double point_selectivity(Field *field, key_range *endpoint, - double avg_selectivity, - double total_rows)=0; + double avg_sel)=0; virtual double range_selectivity(Field *field, key_range *min_endp, key_range *max_endp, double avg_sel)=0; @@ -359,8 +358,7 @@ class Histogram_binary : public Histogram_base Estimate selectivity of "col=const" using a histogram */ double point_selectivity(Field *field, key_range *endpoint, - double avg_sel, - double total_rows) override; + double avg_sel) override; }; diff --git a/strings/json_lib.c b/strings/json_lib.c index 0e7611528e1..836255bdf28 100644 --- a/strings/json_lib.c +++ b/strings/json_lib.c @@ -1868,8 +1868,8 @@ int json_path_compare(const json_path_t *a, const json_path_t *b, } -enum json_types json_smart_read_value(json_engine_t *je, - const char **value, int *value_len) +static enum json_types smart_read_value(json_engine_t *je, + const char **value, int *value_len) { if (json_read_value(je)) goto err_return; @@ -1909,7 +1909,7 @@ enum json_types json_type(const char *js, const char *js_end, json_scan_start(&je, &my_charset_utf8mb4_bin,(const uchar *) js, (const uchar *) js_end); - return json_smart_read_value(&je, value, value_len); + return smart_read_value(&je, value, value_len); } @@ -1933,7 +1933,7 @@ enum json_types json_get_array_item(const char *js, const char *js_end, { case JST_VALUE: if (c_item == n_item) - return json_smart_read_value(&je, value, value_len); + return smart_read_value(&je, value, value_len); if (json_skip_key(&je)) goto err_return; @@ -1998,7 +1998,7 @@ enum json_types json_get_object_key(const char *js, const char *js_end, json_string_set_str(&key_name, (const uchar *) key, (const uchar *) key_end); if (json_key_matches(&je, &key_name)) - return json_smart_read_value(&je, value, value_len); + return smart_read_value(&je, value, value_len); if (json_skip_key(&je)) goto err_return;

1 0

[Commits] 2b38ffdeb2f: Code cleanup
by psergey 19 Jan '22

19 Jan '22

revision-id: 2b38ffdeb2fb276ad9ec9d0968eedcfabd74fa2c (mariadb-10.6.1-349-g2b38ffdeb2f) parent(s): 840afb3b376b853041ea04768846c5f62564bd6c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 18:02:40 +0300 message: Code cleanup --- mysql-test/include/json_hb_histogram.inc | 2 +- sql/opt_histogram_json.cc | 2 +- sql/opt_histogram_json.h | 7 +++---- sql/sql_statistics.cc | 6 ++---- sql/sql_statistics.h | 6 ++---- 5 files changed, 9 insertions(+), 14 deletions(-) diff --git a/mysql-test/include/json_hb_histogram.inc b/mysql-test/include/json_hb_histogram.inc index 0805a7f0e53..0820fb5fccb 100644 --- a/mysql-test/include/json_hb_histogram.inc +++ b/mysql-test/include/json_hb_histogram.inc @@ -1,3 +1,3 @@ -# The time on ANALYSE FORMAT=JSON is rather variable +# Remove non-deterministic parts of JSON_HB histogram --replace_regex /("(collected_at|collected_by)": )"[^"]*"/\1"REPLACED"/ diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc index 55848c58e2d..f2be4e5c0fb 100644 --- a/sql/opt_histogram_json.cc +++ b/sql/opt_histogram_json.cc @@ -914,7 +914,7 @@ double position_in_interval(Field *field, const uchar *key, uint key_len, double Histogram_json_hb::point_selectivity(Field *field, key_range *endpoint, - double avg_sel, double total_rows) + double avg_sel) { const uchar *key = endpoint->key; if (field->real_maybe_null()) diff --git a/sql/opt_histogram_json.h b/sql/opt_histogram_json.h index 48e9a29c8a5..e9b69869f4b 100644 --- a/sql/opt_histogram_json.h +++ b/sql/opt_histogram_json.h @@ -41,8 +41,8 @@ ] } - The histogram is an object with single member named Histogram_json_hb:: - JSON_NAME. The value of that member is an array of buckets. + Histogram is a JSON object. It has some global properties and "histogram_hb" + member whose value is a JSON array of histogram buckets. Each bucket is an object with these members: "start" - the first value in the bucket. @@ -126,8 +126,7 @@ class Histogram_json_hb : public Histogram_base ulonglong size) override; double point_selectivity(Field *field, key_range *endpoint, - double avg_selection, - double total_rows) override; + double avg_sel) override; double range_selectivity(Field *field, key_range *min_endp, key_range *max_endp, double avg_sel) override; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index fc8e38173c7..84d0902193b 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3894,8 +3894,7 @@ double get_column_range_cardinality(Field *field, { res= col_non_nulls * hist->point_selectivity(field, min_endp, - avg_frequency / col_non_nulls, - tab_records); + avg_frequency / col_non_nulls); } } else if (avg_frequency == 0.0) @@ -3989,8 +3988,7 @@ double get_column_range_cardinality(Field *field, */ double Histogram_binary::point_selectivity(Field *field, key_range *endpoint, - double avg_sel, - double total_records) + double avg_sel) { double sel; Column_statistics *col_stats= field->read_stats; diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 1950f3268a7..c0df15ea4ad 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -188,8 +188,7 @@ class Histogram_base virtual double point_selectivity(Field *field, key_range *endpoint, - double avg_selectivity, - double total_rows)=0; + double avg_sel)=0; virtual double range_selectivity(Field *field, key_range *min_endp, key_range *max_endp, double avg_sel)=0; @@ -359,8 +358,7 @@ class Histogram_binary : public Histogram_base Estimate selectivity of "col=const" using a histogram */ double point_selectivity(Field *field, key_range *endpoint, - double avg_sel, - double total_rows) override; + double avg_sel) override; };

1 0

[Commits] b4f67f5eb3f: Switch the default histogram_type to still be DOUBLE_PREC_HB
by psergey 19 Jan '22

19 Jan '22

revision-id: b4f67f5eb3f51560e083f01f1fc6426666f65185 (mariadb-10.6.1-354-gb4f67f5eb3f) parent(s): 746fc9e5c91a5fc0b8eb6f71d7b29363a6883203 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 15:54:38 +0300 message: Switch the default histogram_type to still be DOUBLE_PREC_HB MTR still uses JSON_HB as the default. --- mysql-test/include/default_mysqld.cnf | 1 + mysql-test/main/mysqld--help.result | 2 +- mysql-test/suite/sys_vars/r/histogram_type_basic.result | 2 +- sql/sys_vars.cc | 2 +- 4 files changed, 4 insertions(+), 3 deletions(-) diff --git a/mysql-test/include/default_mysqld.cnf b/mysql-test/include/default_mysqld.cnf index 4788fbb7ff6..49fb03ecc57 100644 --- a/mysql-test/include/default_mysqld.cnf +++ b/mysql-test/include/default_mysqld.cnf @@ -118,6 +118,7 @@ binlog-direct-non-transactional-updates default-storage-engine=myisam use_stat_tables=preferably +histogram_type=json_hb loose-ssl-ca=(a)ENV.MYSQL_TEST_DIR/std_data/cacert.pem loose-ssl-cert=(a)ENV.MYSQL_TEST_DIR/std_data/server-cert.pem diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index a1c67544cac..d2799d6e009 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1551,7 +1551,7 @@ gtid-pos-auto-engines gtid-strict-mode FALSE help TRUE histogram-size 254 -histogram-type JSON_HB +histogram-type DOUBLE_PREC_HB host-cache-size 279 idle-readonly-transaction-timeout 0 idle-transaction-timeout 0 diff --git a/mysql-test/suite/sys_vars/r/histogram_type_basic.result b/mysql-test/suite/sys_vars/r/histogram_type_basic.result index 8dbd32512f8..c24192002aa 100644 --- a/mysql-test/suite/sys_vars/r/histogram_type_basic.result +++ b/mysql-test/suite/sys_vars/r/histogram_type_basic.result @@ -10,7 +10,7 @@ SET @@global.histogram_type = 1; SET @@global.histogram_type = DEFAULT; SELECT @@global.histogram_type; @@global.histogram_type -JSON_HB +DOUBLE_PREC_HB SET @@global.histogram_type = 0; SELECT @@global.histogram_type; @@global.histogram_type diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 690c3533dad..447c1f07310 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -6499,7 +6499,7 @@ static Sys_var_enum Sys_histogram_type( "DOUBLE_PREC_HB - double precision height-balanced, " "JSON_HB - height-balanced, stored as JSON.", SESSION_VAR(histogram_type), CMD_LINE(REQUIRED_ARG), - histogram_types, DEFAULT(2)); + histogram_types, DEFAULT(1)); static Sys_var_mybool Sys_no_thread_alarm( "debug_no_thread_alarm",

1 0

[Commits] 2fd6cfe120b: MDEV-27382: OFFSET is ignored when combined with DISTINCT
by psergey 19 Jan '22

19 Jan '22

revision-id: 2fd6cfe120b9f09a31e06db27acae993b4c3e55c (mariadb-10.5.13-47-g2fd6cfe120b) parent(s): 6831b3f2a0fd656fb41dd9df5f141431988448f1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 10:47:48 +0300 message: MDEV-27382: OFFSET is ignored when combined with DISTINCT A query in form SELECT DISTINCT expr_that_is_inferred_to_be_const LIMIT 0 OFFSET n produces one row when it should produce none. The issue was in JOIN_TAB::remove_duplicates() in the piece of logic that tried to avoid duplicate removal for such cases but didn't account for possible "LIMIT 0". Fixed by making Select_limit_counters::set_limit() change OFFSET to 0 when LIMIT is 0. --- mysql-test/main/distinct.result | 36 ++++++++++++++++++++++++++++++++++++ mysql-test/main/distinct.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_limit.h | 2 ++ 3 files changed, 76 insertions(+) diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result index 2062ff0091d..0e31a174be7 100644 --- a/mysql-test/main/distinct.result +++ b/mysql-test/main/distinct.result @@ -1070,3 +1070,39 @@ UNION 1 drop table t1; End of 5.5 tests +# +# MDEV-27382: OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN +# +CREATE TABLE t1 ( +id int(7) NOT NULL AUTO_INCREMENT, +name varchar(50) DEFAULT NULL, +primary key (id) +); +INSERT INTO t1 VALUES (1, 'Reed'), (10, 'no-child'); +CREATE TABLE t2 ( +id int(11) NOT NULL AUTO_INCREMENT, +parent_id int(7) NOT NULL, +name varchar(100) DEFAULT NULL, +primary key (id), +key(parent_id) +); +INSERT INTO t2 VALUES (1, 1,'John'), (2, 2,'no-parent'); +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0; +id +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0 offset 5; +id +# Test the second part of the fix: just check that "LIMIT 0 OFFSET n" is +# handled in the same way as "LIMIT 0" +explain select * from t1 limit 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +explain select * from t1 limit 0 offset 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +drop table t1, t2; diff --git a/mysql-test/main/distinct.test b/mysql-test/main/distinct.test index da12c7273b2..32e189da98a 100644 --- a/mysql-test/main/distinct.test +++ b/mysql-test/main/distinct.test @@ -818,3 +818,41 @@ UNION drop table t1; --echo End of 5.5 tests + +--echo # +--echo # MDEV-27382: OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN +--echo # +CREATE TABLE t1 ( + id int(7) NOT NULL AUTO_INCREMENT, + name varchar(50) DEFAULT NULL, + primary key (id) +); +INSERT INTO t1 VALUES (1, 'Reed'), (10, 'no-child'); + +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + parent_id int(7) NOT NULL, + name varchar(100) DEFAULT NULL, + primary key (id), + key(parent_id) +); + +INSERT INTO t2 VALUES (1, 1,'John'), (2, 2,'no-parent'); + +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0; + +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0 offset 5; + +--echo # Test the second part of the fix: just check that "LIMIT 0 OFFSET n" is +--echo # handled in the same way as "LIMIT 0" + +explain select * from t1 limit 0; +explain select * from t1 limit 0 offset 10; + +drop table t1, t2; diff --git a/sql/sql_limit.h b/sql/sql_limit.h index a4fcedac14a..19c1ce57e99 100644 --- a/sql/sql_limit.h +++ b/sql/sql_limit.h @@ -35,6 +35,8 @@ class Select_limit_counters void set_limit(ha_rows limit, ha_rows offset) { + if (limit == 0) + offset= 0; offset_limit_cnt= offset; select_limit_cnt= limit; if (select_limit_cnt + offset_limit_cnt >=

1 0

[Commits] c10e10c693c: MDEV-27529: Wrong result upon query using index_merge with DESC key (#2)
by psergey 19 Jan '22

19 Jan '22

revision-id: c10e10c693c58dd1ce488945fa8fd5b4ff998b18 (mariadb-10.6.1-282-gc10e10c693c) parent(s): 8c004b8f1d8f2579b73bbd62f6f5417c4a356733 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 10:05:50 +0300 message: MDEV-27529: Wrong result upon query using index_merge with DESC key (#2) ROR-index_merge relies on Rowid-ordered-retrieval property: a ROR scan, e.g. a scan on equality range tbl.key=const should return rows ordered by their Rowid. Also, handler->cmp_ref() should compare rowids according to the Rowid ordering. When the table's primary key uses DESC keyparts, ROR scans return rows according to the PK's ordering. But ha_innobase::cmp_ref() compared rowids as if PK used ASC keyparts. This caused wrong query results with index_merge. Fixed this by making ha_innobase::cmp_ref() compare according to the PK defintion, including keypart's DESC property. --- mysql-test/main/desc_index_range.result | 25 ++++++++++++++++++++++++- mysql-test/main/desc_index_range.test | 27 +++++++++++++++++++++++++++ storage/innobase/handler/ha_innodb.cc | 3 ++- 3 files changed, 53 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result index edf13010829..94176270dbf 100644 --- a/mysql-test/main/desc_index_range.result +++ b/mysql-test/main/desc_index_range.result @@ -186,6 +186,29 @@ CREATE OR REPLACE TABLE t1 (pk INT, a INT, b int, KEY(a), PRIMARY KEY(pk DESC)) INSERT INTO t1 VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8); SELECT * FROM t1 WHERE pk > 10 OR a > 0; pk a b -1 4 5 2 9 6 +1 4 5 DROP TABLE t1; +# +# MDEV-27529: Wrong result upon query using index_merge with DESC key (#2) +# +create table t1 ( +pk int, +a int, +b int, +primary key(pk desc), +key(a), +key(b) +) engine=innodb; +insert into t1 values (0, 111111, 255); +insert into t1 select seq+50000, NULL, seq+1000 from seq_1_to_260; +insert into t1 values (10000, NULL, 255); +insert into t1 select seq+20000, seq+20000, seq+20000 from seq_1_to_1500; +# Must use ROR-intersect: +explain select * from t1 where b = 255 AND a IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b b,a 5,5 NULL 1 Using intersect(b,a); Using where; Using index +select * from t1 where b = 255 AND a IS NULL; +pk a b +10000 NULL 255 +drop table t1; diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test index 2f3d36c8305..db59922e177 100644 --- a/mysql-test/main/desc_index_range.test +++ b/mysql-test/main/desc_index_range.test @@ -96,3 +96,30 @@ INSERT INTO t1 VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8); SELECT * FROM t1 WHERE pk > 10 OR a > 0; DROP TABLE t1; + + +--echo # +--echo # MDEV-27529: Wrong result upon query using index_merge with DESC key (#2) +--echo # + +create table t1 ( + pk int, + a int, + b int, + primary key(pk desc), + key(a), + key(b) +) engine=innodb; + +insert into t1 values (0, 111111, 255); + +insert into t1 select seq+50000, NULL, seq+1000 from seq_1_to_260; + +insert into t1 values (10000, NULL, 255); +insert into t1 select seq+20000, seq+20000, seq+20000 from seq_1_to_1500; + +--echo # Must use ROR-intersect: +explain select * from t1 where b = 255 AND a IS NULL; +select * from t1 where b = 255 AND a IS NULL; + +drop table t1; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index d19c8cfa2a7..1eba9478b74 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -16812,7 +16812,8 @@ ha_innobase::cmp_ref( } if (result) { - + if (key_part->key_part_flag & HA_REVERSE_SORT) + result = -result; return(result); }

1 0

[Commits] 746fc9e5c91: Make tests stable
by psergey 16 Jan '22

16 Jan '22

revision-id: 746fc9e5c91a5fc0b8eb6f71d7b29363a6883203 (mariadb-10.6.1-353-g746fc9e5c91) parent(s): 348d3788a0043c437e9c2fc92cd621dc32b6845a author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-16 20:38:35 +0300 message: Make tests stable --- mysql-test/main/statistics_json.result | 4 ++-- mysql-test/main/statistics_json.test | 1 + 2 files changed, 3 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index 40b2e1bb73d..a9740056777 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -8300,8 +8300,8 @@ from mysql.column_stats where table_name='t1' and db_name=database(); histogram { "target_histogram_size": 254, - "collected_at": "2022-01-14 20:01:53 MSK", - "collected_by": "10.8.0-MariaDB-debug-log", + "collected_at": "REPLACED", + "collected_by": "REPLACED", "histogram_hb": [ { "start_hex": "0000000000000001", diff --git a/mysql-test/main/statistics_json.test b/mysql-test/main/statistics_json.test index ea017c97989..10583620cda 100644 --- a/mysql-test/main/statistics_json.test +++ b/mysql-test/main/statistics_json.test @@ -454,6 +454,7 @@ insert into t1 values (x'01'),(x'10'),(x'BE562B1A99001918'); set histogram_type= JSON_HB; analyze table t1 persistent for all; +--source include/json_hb_histogram.inc select histogram from mysql.column_stats where table_name='t1' and db_name=database();

1 0

[Commits] 348d3788a00: JSON_HB histogram: represent values of BIT() columns in hex always
by psergey 14 Jan '22

14 Jan '22

revision-id: 348d3788a0043c437e9c2fc92cd621dc32b6845a (mariadb-10.6.1-352-g348d3788a00) parent(s): 21a810253c6d120e75c421267afa913b5c21be3b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-14 20:04:19 +0300 message: JSON_HB histogram: represent values of BIT() columns in hex always --- mysql-test/main/statistics_json.result | 60 +++++++++++++++++++++++++++------- mysql-test/main/statistics_json.test | 14 ++++++++ sql/opt_histogram_json.cc | 22 ++++++++++--- 3 files changed, 80 insertions(+), 16 deletions(-) diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index 9eb8cf87c44..40b2e1bb73d 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -374,23 +374,23 @@ test t1 f 1 5 0.2000 6.4000 4 JSON_HB { "collected_by": "REPLACED", "histogram_hb": [ { - "start": "\u0001", + "start_hex": "01", "size": 0.28125, "ndv": 2 }, { - "start": "\u0002", + "start_hex": "02", "size": 0.28125, "ndv": 2 }, { - "start": "\u0004", + "start_hex": "04", "size": 0.3125, "ndv": 1 }, { - "start": "\u0005", - "end": "\u0005", + "start_hex": "05", + "end_hex": "05", "size": 0.125, "ndv": 1 } @@ -586,28 +586,28 @@ test t1 f 1 5 0.2000 6.4000 5 JSON_HB { "collected_by": "REPLACED", "histogram_hb": [ { - "start": "\u0001", + "start_hex": "01", "size": 0.125, "ndv": 1 }, { - "start": "\u0002", + "start_hex": "02", "size": 0.25, "ndv": 1 }, { - "start": "\u0003", + "start_hex": "03", "size": 0.1875, "ndv": 1 }, { - "start": "\u0004", + "start_hex": "04", "size": 0.3125, "ndv": 1 }, { - "start": "\u0005", - "end": "\u0005", + "start_hex": "05", + "end_hex": "05", "size": 0.125, "ndv": 1 } @@ -8284,3 +8284,41 @@ analyze select f from t1 where f in (77, 1, 144, 73, 14, 12); 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 20 20.00 10.00 10.00 Using where drop table t1; +# +# Test that histograms over BIT fields use hex +# +create table t1 (a BIT(64)); +insert into t1 values +(x'01'),(x'10'),(x'BE562B1A99001918'); +set histogram_type= JSON_HB; +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 +select histogram +from mysql.column_stats where table_name='t1' and db_name=database(); +histogram +{ + "target_histogram_size": 254, + "collected_at": "2022-01-14 20:01:53 MSK", + "collected_by": "10.8.0-MariaDB-debug-log", + "histogram_hb": [ + { + "start_hex": "0000000000000001", + "size": 0.333333333, + "ndv": 1 + }, + { + "start_hex": "0000000000000010", + "size": 0.333333333, + "ndv": 1 + }, + { + "start_hex": "BE562B1A99001918", + "end_hex": "BE562B1A99001918", + "size": 0.333333333, + "ndv": 1 + } + ] +} +drop table t1; diff --git a/mysql-test/main/statistics_json.test b/mysql-test/main/statistics_json.test index 0a1b886f8fd..ea017c97989 100644 --- a/mysql-test/main/statistics_json.test +++ b/mysql-test/main/statistics_json.test @@ -445,3 +445,17 @@ analyze table t1 persistent for all; analyze select f from t1 where f in (77, 1, 144, 73, 14, 12); drop table t1; + +--echo # +--echo # Test that histograms over BIT fields use hex +--echo # +create table t1 (a BIT(64)); +insert into t1 values + (x'01'),(x'10'),(x'BE562B1A99001918'); +set histogram_type= JSON_HB; +analyze table t1 persistent for all; +select histogram +from mysql.column_stats where table_name='t1' and db_name=database(); + +drop table t1; + diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc index 73284025511..55848c58e2d 100644 --- a/sql/opt_histogram_json.cc +++ b/sql/opt_histogram_json.cc @@ -122,6 +122,12 @@ class Histogram_json_builder : public Histogram_builder /* Number of the buckets already collected */ uint n_buckets_collected; + /* + TRUE means do not try to represent values as UTF-8 text in histogram + storage. Use start_hex/end_hex for all values. + */ + bool force_binary; + /* Data about the bucket we are filling now */ struct CurBucket { @@ -135,6 +141,7 @@ class Histogram_json_builder : public Histogram_builder /* Used to create the JSON representation of the histogram. */ Json_writer writer; + public: Histogram_json_builder(Histogram_json_hb *hist, Field *col, uint col_len, @@ -155,6 +162,7 @@ class Histogram_json_builder : public Histogram_builder n_buckets_collected= 0; bucket.ndv= 0; bucket.size= 0; + force_binary= (col->type() == MYSQL_TYPE_BIT); writer.start_object(); append_histogram_params(); @@ -244,12 +252,16 @@ class Histogram_json_builder : public Histogram_builder // Escape the value for JSON StringBuffer<MAX_FIELD_WIDTH> escaped_val; - int rc= json_escape_to_string(str, &escaped_val); - if (!rc) + int rc= JSON_ERROR_ILLEGAL_SYMBOL; + if (!force_binary) { - writer.add_member(is_start? "start": "end"); - writer.add_str(escaped_val.c_ptr_safe()); - return false; + rc= json_escape_to_string(str, &escaped_val); + if (!rc) + { + writer.add_member(is_start? "start": "end"); + writer.add_str(escaped_val.c_ptr_safe()); + return false; + } } if (rc == JSON_ERROR_ILLEGAL_SYMBOL) {

1 0