- commits - lists.mariadb.org

[Commits] 8fc778f3e9c: Make tests pass
by psergey 04 Sep '21

04 Sep '21

revision-id: 8fc778f3e9cbc339d14653e9e035163e52a582cd (mariadb-10.6.1-120-g8fc778f3e9c) parent(s): 6352d59d12cb5ab7e6530a74171413ddba5e2870 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-09-04 23:17:39 +0300 message: Make tests pass - Fix bad tests in statistics_json test: make them meaningful and make them work on windows - Fix analyze_debug.test: correctly handle errors during ANALYZE --- mysql-test/main/statistics_json.result | 18 ++++-------------- mysql-test/main/statistics_json.test | 6 ++---- sql/sql_admin.cc | 3 ++- 3 files changed, 8 insertions(+), 19 deletions(-) diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index 857b062ae47..aa7b7c08a31 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -3174,22 +3174,12 @@ Percentage 0.0 99.9 50 JSON_HB { "99.9" ] } -explain extended select * from Country where 'Code' between 'BBC' and 'GGG'; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country ALL NULL NULL NULL NULL 239 100.00 -Warnings: -Note 1003 select `world`.`Country`.`Code` AS `Code`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`Population` AS `Population`,`world`.`Country`.`Capital` AS `Capital` from `world`.`Country` where 1 -analyze select * from Country where 'Code' between 'BBC' and 'GGG'; +analyze select * from Country use index () where Code between 'BBC' and 'GGG'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE Country ALL NULL NULL NULL NULL 239 239.00 100.00 100.00 -explain extended select * from Country where 'Code' < 'BBC'; -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 -Warnings: -Note 1003 select `world`.`Country`.`Code` AS `Code`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`Population` AS `Population`,`world`.`Country`.`Capital` AS `Capital` from `world`.`Country` where 0 -analyze select * from Country where 'Code' < 'BBC'; +1 SIMPLE Country ALL NULL NULL NULL NULL 239 239.00 25.49 25.52 Using where +analyze select * from Country use index () where Code < 'BBC'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 SIMPLE Country ALL NULL NULL NULL NULL 239 239.00 5.88 7.11 Using where set histogram_type=@save_histogram_type; set histogram_size=@save_histogram_size; DROP SCHEMA world; diff --git a/mysql-test/main/statistics_json.test b/mysql-test/main/statistics_json.test index 99705aa38ae..be223f5e4a4 100644 --- a/mysql-test/main/statistics_json.test +++ b/mysql-test/main/statistics_json.test @@ -85,10 +85,8 @@ ANALYZE TABLE Country, City, CountryLanguage persistent for all; --enable_result_log SELECT column_name, min_value, max_value, hist_size, hist_type, histogram FROM mysql.column_stats; -explain extended select * from Country where 'Code' between 'BBC' and 'GGG'; -analyze select * from Country where 'Code' between 'BBC' and 'GGG'; -explain extended select * from Country where 'Code' < 'BBC'; -analyze select * from Country where 'Code' < 'BBC'; +analyze select * from Country use index () where Code between 'BBC' and 'GGG'; +analyze select * from Country use index () where Code < 'BBC'; set histogram_type=@save_histogram_type; set histogram_size=@save_histogram_size; diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index 6a1ea3d31fc..6c3e86bd54c 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -1025,7 +1025,8 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, else compl_result_code= HA_ADMIN_FAILED; - free_statistics_for_table(thd, table->table); + if (table->table) + free_statistics_for_table(thd, table->table); if (compl_result_code) result_code= HA_ADMIN_FAILED; else

1 0

[Commits] 6352d59d12c: Fix compilation on windows part #3
by psergey 04 Sep '21

04 Sep '21

revision-id: 6352d59d12cb5ab7e6530a74171413ddba5e2870 (mariadb-10.6.1-119-g6352d59d12c) parent(s): 95a3f59336a51b960f6d5249ba0635bdc08a9a56 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-09-04 18:18:19 +0300 message: Fix compilation on windows part #3 --- sql/opt_histogram_json.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc index cc20165b4db..5f4e0eca585 100644 --- a/sql/opt_histogram_json.cc +++ b/sql/opt_histogram_json.cc @@ -372,7 +372,7 @@ int Histogram_json_hb::find_bucket(Field *field, const uchar *lookup_val, bool equal_is_less) { int low= 0; - int high= histogram_bounds.size() - 1; + int high= (int)histogram_bounds.size() - 1; int middle; while (low + 1 < high)

1 0

[Commits] 95a3f59336a: Fix embedded to work
by psergey 04 Sep '21

04 Sep '21

revision-id: 95a3f59336a51b960f6d5249ba0635bdc08a9a56 (mariadb-10.6.1-118-g95a3f59336a) parent(s): 0e8e1bb308ea0e38f299e91baa5e45850562787a author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-09-04 18:00:21 +0300 message: Fix embedded to work --- libmysqld/CMakeLists.txt | 1 + 1 file changed, 1 insertion(+) diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt index efdf7277e5b..ef063311690 100644 --- a/libmysqld/CMakeLists.txt +++ b/libmysqld/CMakeLists.txt @@ -137,6 +137,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc ../sql/opt_trace.cc ../sql/xa.cc ../sql/json_table.cc + ../sql/opt_histogram_json.cc ${GEN_SOURCES} ${MYSYS_LIBWRAP_SOURCE} )

1 0

[Commits] 0e8e1bb308e: Fix compilation on windows part 2
by psergey 04 Sep '21

04 Sep '21

revision-id: 0e8e1bb308ea0e38f299e91baa5e45850562787a (mariadb-10.6.1-117-g0e8e1bb308e) parent(s): 2308bd5fbe3f63f93879e9c490ed8154c8de528b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-09-04 17:54:07 +0300 message: Fix compilation on windows part 2 --- sql/opt_histogram_json.h | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/opt_histogram_json.h b/sql/opt_histogram_json.h index 4cb61e95293..a83e88a88c4 100644 --- a/sql/opt_histogram_json.h +++ b/sql/opt_histogram_json.h @@ -61,7 +61,7 @@ class Histogram_json_hb : public Histogram_base */ uint get_size() override { - return size; + return (uint)size; } void init_for_collection(MEM_ROOT *mem_root, Histogram_type htype_arg,

1 0

[Commits] 2308bd5fbe3: Rollback a change from previous commit
by psergey 04 Sep '21

04 Sep '21

revision-id: 2308bd5fbe3f63f93879e9c490ed8154c8de528b (mariadb-10.6.1-116-g2308bd5fbe3) parent(s): 57cc13d177067979dad47307dac8ea41621899c7 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-09-04 17:37:24 +0300 message: Rollback a change from previous commit --- sql/opt_histogram_json.h | 8 +++----- 1 file changed, 3 insertions(+), 5 deletions(-) diff --git a/sql/opt_histogram_json.h b/sql/opt_histogram_json.h index 3fbba8814dd..4cb61e95293 100644 --- a/sql/opt_histogram_json.h +++ b/sql/opt_histogram_json.h @@ -56,14 +56,12 @@ class Histogram_json_hb : public Histogram_base /* @brief - Legacy: this returns the size of the histogram on disk. - - @detail - This is only called at collection time when json_text is non-empty. + This used to be the size of the histogram on disk, which was redundant + (one can check the size directly). Return the number of buckets instead. */ uint get_size() override { - return json_text.size(); + return size; } void init_for_collection(MEM_ROOT *mem_root, Histogram_type htype_arg,

1 0

[Commits] 57cc13d1770: More code cleanups
by psergey 04 Sep '21

04 Sep '21

revision-id: 57cc13d177067979dad47307dac8ea41621899c7 (mariadb-10.6.1-115-g57cc13d1770) parent(s): 034f232e182a172847d534554cb8ed56b56c8222 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-09-04 17:24:47 +0300 message: More code cleanups Remove Histogram_*::is_available(), it is not applicable anymore. Fix compilation on Windows --- sql/opt_histogram_json.cc | 2 +- sql/opt_histogram_json.h | 10 +--------- sql/sql_statistics.cc | 2 +- sql/sql_statistics.h | 23 ++++++++--------------- 4 files changed, 11 insertions(+), 26 deletions(-) diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc index 196ee6f2737..cc20165b4db 100644 --- a/sql/opt_histogram_json.cc +++ b/sql/opt_histogram_json.cc @@ -126,7 +126,7 @@ void Histogram_json_hb::init_for_collection(MEM_ROOT *mem_root, ulonglong size_arg) { DBUG_ASSERT(htype_arg == JSON_HB); - size= (uint8) size_arg; + size= (size_t)size_arg; } diff --git a/sql/opt_histogram_json.h b/sql/opt_histogram_json.h index c5b31c273ad..3fbba8814dd 100644 --- a/sql/opt_histogram_json.h +++ b/sql/opt_histogram_json.h @@ -69,14 +69,6 @@ class Histogram_json_hb : public Histogram_base void init_for_collection(MEM_ROOT *mem_root, Histogram_type htype_arg, ulonglong size) override; - bool is_available() override {return true; } - - bool is_usable(THD *thd) override - { - return thd->variables.optimizer_use_condition_selectivity > 3 && - is_available(); - } - double point_selectivity(Field *field, key_range *endpoint, double avg_selection) override; double range_selectivity(Field *field, key_range *min_endp, @@ -84,7 +76,7 @@ class Histogram_json_hb : public Histogram_base void set_json_text(ulonglong sz, uchar *json_text_arg) { - size = (uint8) sz; + size= (size_t) sz; json_text.assign((const char*)json_text_arg, strlen((const char*)json_text_arg)); } diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index dd79cc16d59..ca9fb8ae194 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -1271,7 +1271,7 @@ void Histogram_binary::init_for_collection(MEM_ROOT *mem_root, ulonglong size_arg) { type= htype_arg; - values = (uchar*)alloc_root(mem_root, size_arg); + values= (uchar*)alloc_root(mem_root, (size_t)size_arg); size= (uint8) size_arg; } diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 7ac315f48e2..601c5bf907f 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -175,9 +175,15 @@ class Histogram_base : public Sql_alloc virtual Histogram_builder *create_builder(Field *col, uint col_len, ha_rows rows)=0; - virtual bool is_available()=0; + /* + This function checks that histograms should be usable only when + 1) the level of optimizer_use_condition_selectivity > 3 + */ + bool is_usable(THD *thd) + { + return thd->variables.optimizer_use_condition_selectivity > 3; + } - virtual bool is_usable(THD *thd)=0; virtual double point_selectivity(Field *field, key_range *endpoint, double avg_selection)=0; @@ -312,19 +318,6 @@ class Histogram_binary : public Histogram_base Histogram_builder *create_builder(Field *col, uint col_len, ha_rows rows) override; - bool is_available() override { return (values!=NULL); } - - /* - This function checks that histograms should be usable only when - 1) the level of optimizer_use_condition_selectivity > 3 - 2) histograms have been collected - */ - bool is_usable(THD *thd) override - { - return thd->variables.optimizer_use_condition_selectivity > 3 && - is_available(); - } - void set_value(uint i, double val) { switch (type) {

1 0

[Commits] 034f232e182: Move JSON histograms code into its own files
by psergey 04 Sep '21

04 Sep '21

revision-id: 034f232e182a172847d534554cb8ed56b56c8222 (mariadb-10.6.1-114-g034f232e182) parent(s): b7b3b6b44a5048ae26599131a7608f9f6579eb87 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-09-04 17:11:16 +0300 message: Move JSON histograms code into its own files --- sql/CMakeLists.txt | 1 + sql/opt_histogram_json.cc | 391 +++++++++++++++++++++++++++++++++++++++++ sql/opt_histogram_json.h | 95 ++++++++++ sql/sql_statistics.cc | 435 +--------------------------------------------- sql/sql_statistics.h | 127 +++++++------- 5 files changed, 559 insertions(+), 490 deletions(-) diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index 906c9d30bc9..ff05a8b2eae 100644 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -147,6 +147,7 @@ SET (SQL_SOURCE sql_analyze_stmt.cc sql_join_cache.cc create_options.cc multi_range_read.cc + opt_histogram_json.cc opt_index_cond_pushdown.cc opt_subselect.cc opt_table_elimination.cc sql_expression_cache.cc gcalc_slicescan.cc gcalc_tools.cc diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc new file mode 100644 index 00000000000..196ee6f2737 --- /dev/null +++ b/sql/opt_histogram_json.cc @@ -0,0 +1,391 @@ +/* + Copyright (c) 2021, MariaDB Corporation. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */ + +#include "mariadb.h" +#include "sql_base.h" +#include "my_json_writer.h" +#include "sql_statistics.h" +#include "opt_histogram_json.h" + +class Histogram_json_builder : public Histogram_builder +{ + Histogram_json_hb *histogram; + uint hist_width; /* the number of points in the histogram */ + double bucket_capacity; /* number of rows in a bucket of the histogram */ + uint curr_bucket; /* number of the current bucket to be built */ + + std::vector<std::string> bucket_bounds; + bool first_value= true; +public: + + Histogram_json_builder(Histogram_json_hb *hist, Field *col, uint col_len, + ha_rows rows) + : Histogram_builder(col, col_len, rows), histogram(hist) + { + bucket_capacity= (double)records / histogram->get_width(); + hist_width= histogram->get_width(); + curr_bucket= 0; + } + + ~Histogram_json_builder() override = default; + + /* + @brief + Add data to the histogram. This call adds elem_cnt rows, each + of which has value of *elem. + + @detail + Subsequent next() calls will add values that are greater than *elem. + */ + int next(void *elem, element_count elem_cnt) override + { + counters.next(elem, elem_cnt); + ulonglong count= counters.get_count(); + + if (curr_bucket == hist_width) + return 0; + if (first_value) + { + first_value= false; + column->store_field_value((uchar*) elem, col_length); + StringBuffer<MAX_FIELD_WIDTH> val; + column->val_str(&val); + bucket_bounds.push_back(std::string(val.ptr(), val.length())); + } + + if (count > bucket_capacity * (curr_bucket + 1)) + { + column->store_field_value((uchar*) elem, col_length); + StringBuffer<MAX_FIELD_WIDTH> val; + column->val_str(&val); + bucket_bounds.emplace_back(val.ptr(), val.length()); + + curr_bucket++; + while (curr_bucket != hist_width && + count > bucket_capacity * (curr_bucket + 1)) + { + bucket_bounds.push_back(std::string(val.ptr(), val.length())); + curr_bucket++; + } + } + + if (records == count && bucket_bounds.size() == hist_width) + { + column->store_field_value((uchar*) elem, col_length); + StringBuffer<MAX_FIELD_WIDTH> val; + column->val_str(&val); + bucket_bounds.push_back(std::string(val.ptr(), val.length())); + } + return 0; + } + + /* + @brief + Finalize the creation of histogram + */ + void finalize() override + { + Json_writer writer; + writer.start_object(); + writer.add_member(Histogram_json_hb::JSON_NAME).start_array(); + + for(auto& value: bucket_bounds) { + writer.add_str(value.c_str()); + } + writer.end_array(); + writer.end_object(); + Binary_string *json_string= (Binary_string *) writer.output.get_string(); + histogram->set_json_text(bucket_bounds.size()-1, + (uchar *) json_string->c_ptr()); + } +}; + + +Histogram_builder *Histogram_json_hb::create_builder(Field *col, uint col_len, + ha_rows rows) +{ + return new Histogram_json_builder(this, col, col_len, rows); +} + + +void Histogram_json_hb::init_for_collection(MEM_ROOT *mem_root, + Histogram_type htype_arg, + ulonglong size_arg) +{ + DBUG_ASSERT(htype_arg == JSON_HB); + size= (uint8) size_arg; +} + + +/* + @brief + Parse the histogram from its on-disk representation + + @return + false OK + True Error +*/ + +bool Histogram_json_hb::parse(MEM_ROOT *mem_root, Field *field, + Histogram_type type_arg, const char *hist_data, + size_t hist_data_len) +{ + DBUG_ENTER("Histogram_json_hb::parse"); + DBUG_ASSERT(type_arg == JSON_HB); + const char *err; + json_engine_t je; + json_string_t key_name; + + json_scan_start(&je, &my_charset_utf8mb4_bin, + (const uchar*)hist_data, + (const uchar*)hist_data+hist_data_len); + + if (json_read_value(&je) || je.value_type != JSON_VALUE_OBJECT) + { + err= "Root JSON element must be a JSON object"; + goto error; + } + + json_string_set_str(&key_name, (const uchar*)JSON_NAME, + (const uchar*)JSON_NAME + strlen(JSON_NAME)); + json_string_set_cs(&key_name, system_charset_info); + + if (json_scan_next(&je) || je.state != JST_KEY || + !json_key_matches(&je, &key_name)) + { + err= "The first key in the object must be histogram_hb_v1"; + goto error; + } + + // The value must be a JSON array + if (json_read_value(&je) || (je.value_type != JSON_VALUE_ARRAY)) + { + err= "A JSON array expected"; + goto error; + } + + // Read the array + while (!json_scan_next(&je)) + { + switch(je.state) + { + case JST_VALUE: + { + const char *val; + int val_len; + json_smart_read_value(&je, &val, &val_len); + if (je.value_type != JSON_VALUE_STRING && + je.value_type != JSON_VALUE_NUMBER && + je.value_type != JSON_VALUE_TRUE && + je.value_type != JSON_VALUE_FALSE) + { + err= "Scalar value expected"; + goto error; + } + uchar buf[MAX_KEY_LENGTH]; + uint len_to_copy= field->key_length(); + field->store_text(val, val_len, &my_charset_bin); + uint bytes= field->get_key_image(buf, len_to_copy, Field::itRAW); + histogram_bounds.push_back(std::string((char*)buf, bytes)); + // TODO: Should we also compare this endpoint with the previous + // to verify that the ordering is right? + break; + } + case JST_ARRAY_END: + break; + } + } + // n_buckets = n_bounds - 1 : + size= histogram_bounds.size()-1; + DBUG_RETURN(false); + +error: + my_error(ER_JSON_HISTOGRAM_PARSE_FAILED, MYF(0), err, + je.s.c_str - (const uchar*)hist_data); + DBUG_RETURN(true); +} + + +static +void store_key_image_to_rec_no_null(Field *field, const uchar *ptr) +{ + MY_BITMAP *old_map= dbug_tmp_use_all_columns(field->table, + &field->table->write_set); + field->set_key_image(ptr, field->key_length()); + dbug_tmp_restore_column_map(&field->table->write_set, old_map); +} + + +static +double position_in_interval(Field *field, const uchar *key, + const std::string& left, const std::string& right) +{ + double res; + if (field->pos_through_val_str()) + { + uint32 min_len= uint2korr(left.data()); + uint32 max_len= uint2korr(right.data()); + uint32 midp_len= uint2korr(key); + + res= pos_in_interval_for_string(field->charset(), + key + HA_KEY_BLOB_LENGTH, + midp_len, + (const uchar*)left.data() + HA_KEY_BLOB_LENGTH, + min_len, + (const uchar*)right.data() + HA_KEY_BLOB_LENGTH, + max_len); + } + else + { + store_key_image_to_rec_no_null(field, (const uchar*)left.data()); + double min_val_real= field->val_real(); + + store_key_image_to_rec_no_null(field, (const uchar*)right.data()); + double max_val_real= field->val_real(); + + store_key_image_to_rec_no_null(field, key); + double midp_val_real= field->val_real(); + + res= pos_in_interval_for_double(midp_val_real, min_val_real, max_val_real); + } + return res; +} + + +double Histogram_json_hb::point_selectivity(Field *field, key_range *endpoint, + double avg_sel) +{ + double sel; + store_key_image_to_rec(field, (uchar *) endpoint->key, + field->key_length()); + const uchar *min_key = endpoint->key; + if (field->real_maybe_null()) + min_key++; + uint min_idx= find_bucket(field, min_key, false); + + uint max_idx= find_bucket(field, min_key, true); +#if 0 + // find how many buckets this value occupies + while ((max_idx + 1 < get_width() ) && + (field->key_cmp((uchar *)histogram_bounds[max_idx + 1].data(), min_key) == 0)) { + max_idx++; + } +#endif + if (max_idx > min_idx) + { + // value spans multiple buckets + double bucket_sel= 1.0/(get_width() + 1); + sel= bucket_sel * (max_idx - min_idx + 1); + } + else + { + // the value fits within a single bucket + sel = MY_MIN(avg_sel, 1.0/get_width()); + } + return sel; +} + + +/* + @param field The table field histogram is for. We don't care about the + field's current value, we only need its virtual functions to + perform various operations + + @param min_endp Left endpoint, or NULL if there is none + @param max_endp Right endpoint, or NULL if there is none +*/ + +double Histogram_json_hb::range_selectivity(Field *field, key_range *min_endp, + key_range *max_endp) +{ + double min, max; + double width= 1.0 / histogram_bounds.size(); + + if (min_endp && !(field->null_ptr && min_endp->key[0])) + { + bool exclusive_endp= (min_endp->flag == HA_READ_AFTER_KEY)? true: false; + const uchar *min_key= min_endp->key; + if (field->real_maybe_null()) + min_key++; + + // Find the leftmost bucket that contains the lookup value. + // (If the lookup value is to the left of all buckets, find bucket #0) + int idx= find_bucket(field, min_key, exclusive_endp); + double min_sel= position_in_interval(field, (const uchar*)min_key, + histogram_bounds[idx], + histogram_bounds[idx+1]); + min= idx*width + min_sel*width; + } + else + min= 0.0; + + if (max_endp) + { + // The right endpoint cannot be NULL + DBUG_ASSERT(!(field->null_ptr && max_endp->key[0])); + bool inclusive_endp= (max_endp->flag == HA_READ_AFTER_KEY)? true: false; + const uchar *max_key= max_endp->key; + if (field->real_maybe_null()) + max_key++; + + int idx= find_bucket(field, max_key, inclusive_endp); + double max_sel= position_in_interval(field, (const uchar*)max_key, + histogram_bounds[idx], + histogram_bounds[idx+1]); + max= idx*width + max_sel*width; + } + else + max= 1.0; + + double sel = max - min; + return sel; +} + + +void Histogram_json_hb::serialize(Field *field) +{ + field->store(json_text.data(), json_text.size(), &my_charset_bin); +} + + +/* + Find the histogram bucket that contains the value. + + @param equal_is_less Controls what to do if a histogram bound is equal to the + lookup_val. +*/ + +int Histogram_json_hb::find_bucket(Field *field, const uchar *lookup_val, + bool equal_is_less) +{ + int low= 0; + int high= histogram_bounds.size() - 1; + int middle; + + while (low + 1 < high) + { + middle= (low + high) / 2; + int res= field->key_cmp((uchar*)histogram_bounds[middle].data(), lookup_val); + if (!res) + res= equal_is_less? -1: 1; + if (res < 0) + low= middle; + else //res > 0 + high= middle; + } + + return low; +} diff --git a/sql/opt_histogram_json.h b/sql/opt_histogram_json.h new file mode 100644 index 00000000000..c5b31c273ad --- /dev/null +++ b/sql/opt_histogram_json.h @@ -0,0 +1,95 @@ +/* + Copyright (c) 2021, MariaDB Corporation. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */ + +#include "sql_statistics.h" + +/* + An equi-height histogram which stores real values for bucket bounds. + + Handles @@histogram_type=JSON_HB +*/ + +class Histogram_json_hb : public Histogram_base +{ + size_t size; /* Number of elements in the histogram */ + + /* Collection-time only: collected histogram in the JSON form. */ + std::string json_text; + + // Array of histogram bucket endpoints in KeyTupleFormat. + std::vector<std::string> histogram_bounds; + +public: + static constexpr const char* JSON_NAME="histogram_hb_v1"; + + bool parse(MEM_ROOT *mem_root, Field *field, Histogram_type type_arg, + const char *hist_data, size_t hist_data_len) override; + + void serialize(Field *field) override; + + Histogram_builder *create_builder(Field *col, uint col_len, + ha_rows rows) override; + + // returns number of buckets in the histogram + uint get_width() override + { + return (uint)size; + } + + Histogram_type get_type() override + { + return JSON_HB; + } + + /* + @brief + Legacy: this returns the size of the histogram on disk. + + @detail + This is only called at collection time when json_text is non-empty. + */ + uint get_size() override + { + return json_text.size(); + } + + void init_for_collection(MEM_ROOT *mem_root, Histogram_type htype_arg, + ulonglong size) override; + + bool is_available() override {return true; } + + bool is_usable(THD *thd) override + { + return thd->variables.optimizer_use_condition_selectivity > 3 && + is_available(); + } + + double point_selectivity(Field *field, key_range *endpoint, + double avg_selection) override; + double range_selectivity(Field *field, key_range *min_endp, + key_range *max_endp) override; + + void set_json_text(ulonglong sz, uchar *json_text_arg) + { + size = (uint8) sz; + json_text.assign((const char*)json_text_arg, + strlen((const char*)json_text_arg)); + } + +private: + int find_bucket(Field *field, const uchar *lookup_val, bool equal_is_less); +}; + diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index e6988150304..dd79cc16d59 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -28,11 +28,11 @@ #include "sql_base.h" #include "key.h" #include "sql_statistics.h" +#include "opt_histogram_json.h" #include "opt_range.h" #include "uniques.h" #include "sql_show.h" #include "sql_partition.h" -#include "my_json_writer.h" #include <vector> #include <string> @@ -1267,8 +1267,8 @@ void Histogram_binary::serialize(Field *field) } void Histogram_binary::init_for_collection(MEM_ROOT *mem_root, - Histogram_type htype_arg, - ulonglong size_arg) + Histogram_type htype_arg, + ulonglong size_arg) { type= htype_arg; values = (uchar*)alloc_root(mem_root, size_arg); @@ -1276,273 +1276,6 @@ void Histogram_binary::init_for_collection(MEM_ROOT *mem_root, } -void Histogram_json_hb::init_for_collection(MEM_ROOT *mem_root, - Histogram_type htype_arg, - ulonglong size_arg) -{ - DBUG_ASSERT(htype_arg == JSON_HB); - size= (uint8) size_arg; -} - - -/* - @brief - Parse the histogram from its on-disk representation - - @return - false OK - True Error -*/ - -bool Histogram_json_hb::parse(MEM_ROOT *mem_root, Field *field, - Histogram_type type_arg, const char *hist_data, - size_t hist_data_len) -{ - DBUG_ENTER("Histogram_json_hb::parse"); - DBUG_ASSERT(type_arg == JSON_HB); - const char *err; - json_engine_t je; - json_string_t key_name; - - json_scan_start(&je, &my_charset_utf8mb4_bin, - (const uchar*)hist_data, - (const uchar*)hist_data+hist_data_len); - - if (json_read_value(&je) || je.value_type != JSON_VALUE_OBJECT) - { - err= "Root JSON element must be a JSON object"; - goto error; - } - - json_string_set_str(&key_name, (const uchar*)JSON_NAME, - (const uchar*)JSON_NAME + strlen(JSON_NAME)); - json_string_set_cs(&key_name, system_charset_info); - - if (json_scan_next(&je) || je.state != JST_KEY || - !json_key_matches(&je, &key_name)) - { - err= "The first key in the object must be histogram_hb_v1"; - goto error; - } - - // The value must be a JSON array - if (json_read_value(&je) || (je.value_type != JSON_VALUE_ARRAY)) - { - err= "A JSON array expected"; - goto error; - } - - // Read the array - while (!json_scan_next(&je)) - { - switch(je.state) - { - case JST_VALUE: - { - const char *val; - int val_len; - json_smart_read_value(&je, &val, &val_len); - if (je.value_type != JSON_VALUE_STRING && - je.value_type != JSON_VALUE_NUMBER && - je.value_type != JSON_VALUE_TRUE && - je.value_type != JSON_VALUE_FALSE) - { - err= "Scalar value expected"; - goto error; - } - uchar buf[MAX_KEY_LENGTH]; - uint len_to_copy= field->key_length(); - field->store_text(val, val_len, &my_charset_bin); - uint bytes= field->get_key_image(buf, len_to_copy, Field::itRAW); - histogram_bounds.push_back(std::string((char*)buf, bytes)); - // TODO: Should we also compare this endpoint with the previous - // to verify that the ordering is right? - break; - } - case JST_ARRAY_END: - break; - } - } - // n_buckets = n_bounds - 1 : - size= histogram_bounds.size()-1; - DBUG_RETURN(false); - -error: - my_error(ER_JSON_HISTOGRAM_PARSE_FAILED, MYF(0), err, - je.s.c_str - (const uchar*)hist_data); - DBUG_RETURN(true); -} - - -double Histogram_json_hb::point_selectivity(Field *field, key_range *endpoint, - double avg_sel) -{ - double sel; - store_key_image_to_rec(field, (uchar *) endpoint->key, - field->key_length()); - const uchar *min_key = endpoint->key; - if (field->real_maybe_null()) - min_key++; - uint min_idx= find_bucket(field, min_key, false); - - uint max_idx= find_bucket(field, min_key, true); -#if 0 - // find how many buckets this value occupies - while ((max_idx + 1 < get_width() ) && - (field->key_cmp((uchar *)histogram_bounds[max_idx + 1].data(), min_key) == 0)) { - max_idx++; - } -#endif - if (max_idx > min_idx) - { - // value spans multiple buckets - double bucket_sel= 1.0/(get_width() + 1); - sel= bucket_sel * (max_idx - min_idx + 1); - } - else - { - // the value fits within a single bucket - sel = MY_MIN(avg_sel, 1.0/get_width()); - } - return sel; -} - - -static -void store_key_image_to_rec_no_null(Field *field, const uchar *ptr) -{ - MY_BITMAP *old_map= dbug_tmp_use_all_columns(field->table, - &field->table->write_set); - field->set_key_image(ptr, field->key_length()); - dbug_tmp_restore_column_map(&field->table->write_set, old_map); -} - - -static -double position_in_interval(Field *field, const uchar *key, - const std::string& left, const std::string& right) -{ - double res; - if (field->pos_through_val_str()) - { - uint32 min_len= uint2korr(left.data()); - uint32 max_len= uint2korr(right.data()); - uint32 midp_len= uint2korr(key); - - res= pos_in_interval_for_string(field->charset(), - key + HA_KEY_BLOB_LENGTH, - midp_len, - (const uchar*)left.data() + HA_KEY_BLOB_LENGTH, - min_len, - (const uchar*)right.data() + HA_KEY_BLOB_LENGTH, - max_len); - } - else - { - store_key_image_to_rec_no_null(field, (const uchar*)left.data()); - double min_val_real= field->val_real(); - - store_key_image_to_rec_no_null(field, (const uchar*)right.data()); - double max_val_real= field->val_real(); - - store_key_image_to_rec_no_null(field, key); - double midp_val_real= field->val_real(); - - res= pos_in_interval_for_double(midp_val_real, min_val_real, max_val_real); - } - return res; -} - -/* - @param field The table field histogram is for. We don't care about the - field's current value, we only need its virtual functions to - perform various operations - - @param min_endp Left endpoint, or NULL if there is none - @param max_endp Right endpoint, or NULL if there is none -*/ -double Histogram_json_hb::range_selectivity(Field *field, key_range *min_endp, - key_range *max_endp) -{ - double min, max; - double width= 1.0 / histogram_bounds.size(); - - if (min_endp && !(field->null_ptr && min_endp->key[0])) - { - bool exclusive_endp= (min_endp->flag == HA_READ_AFTER_KEY)? true: false; - const uchar *min_key= min_endp->key; - if (field->real_maybe_null()) - min_key++; - - // Find the leftmost bucket that contains the lookup value. - // (If the lookup value is to the left of all buckets, find bucket #0) - int idx= find_bucket(field, min_key, exclusive_endp); - double min_sel= position_in_interval(field, (const uchar*)min_key, - histogram_bounds[idx], - histogram_bounds[idx+1]); - min= idx*width + min_sel*width; - } - else - min= 0.0; - - if (max_endp) - { - // The right endpoint cannot be NULL - DBUG_ASSERT(!(field->null_ptr && max_endp->key[0])); - bool inclusive_endp= (max_endp->flag == HA_READ_AFTER_KEY)? true: false; - const uchar *max_key= max_endp->key; - if (field->real_maybe_null()) - max_key++; - - int idx= find_bucket(field, max_key, inclusive_endp); - double max_sel= position_in_interval(field, (const uchar*)max_key, - histogram_bounds[idx], - histogram_bounds[idx+1]); - max= idx*width + max_sel*width; - } - else - max= 1.0; - - double sel = max - min; - return sel; -} - - -void Histogram_json_hb::serialize(Field *field) -{ - field->store(json_text.data(), json_text.size(), &my_charset_bin); -} - - -/* - Find the histogram bucket that contains the value. - - @param equal_is_less Controls what to do if a histogram bound is equal to the - lookup_val. -*/ - -int Histogram_json_hb::find_bucket(Field *field, const uchar *lookup_val, - bool equal_is_less) -{ - int low= 0; - int high= histogram_bounds.size() - 1; - int middle; - - while (low + 1 < high) - { - middle= (low + high) / 2; - int res= field->key_cmp((uchar*)histogram_bounds[middle].data(), lookup_val); - if (!res) - res= equal_is_less? -1: 1; - if (res < 0) - low= middle; - else //res > 0 - high= middle; - } - - return low; -} - /* An object of the class Index_stat is created to read statistical data on tables from the statistical table table_stat, to update @@ -1853,73 +1586,6 @@ class Stat_table_write_iter } }; - -/* - This is used to collect the the basic statistics from a Unique object: - - count of values - - count of distinct values - - count of distinct values that have occurred only once -*/ - -class Basic_stats_collector -{ - ulonglong count; /* number of values retrieved */ - ulonglong count_distinct; /* number of distinct values retrieved */ - /* number of distinct values that occured only once */ - ulonglong count_distinct_single_occurence; - -public: - Basic_stats_collector() - { - count= 0; - count_distinct= 0; - count_distinct_single_occurence= 0; - } - - ulonglong get_count_distinct() const { return count_distinct; } - ulonglong get_count_single_occurence() const - { - return count_distinct_single_occurence; - } - ulonglong get_count() const { return count; } - - void next(void *elem, element_count elem_cnt) - { - count_distinct++; - if (elem_cnt == 1) - count_distinct_single_occurence++; - count+= elem_cnt; - } -}; - -/* - Histogram_builder is a helper class that is used to build histograms - for columns. - - Do not create directly, call Histogram->get_builder(...); -*/ - -class Histogram_builder -{ -protected: - Field *column; /* table field for which the histogram is built */ - uint col_length; /* size of this field */ - ha_rows records; /* number of records the histogram is built for */ - - Histogram_builder(Field *col, uint col_len, ha_rows rows) : - column(col), col_length(col_len), records(rows) - {} - -public: - // A histogram builder will also collect the counters - Basic_stats_collector counters; - - virtual int next(void *elem, element_count elem_cnt)=0; - virtual void finalize()=0; - virtual ~Histogram_builder(){} -}; - - class Histogram_binary_builder : public Histogram_builder { Field *min_value; /* pointer to the minimal value for the field */ @@ -1974,101 +1640,6 @@ Histogram_builder *Histogram_binary::create_builder(Field *col, uint col_len, } -class Histogram_json_builder : public Histogram_builder -{ - Histogram_json_hb *histogram; - uint hist_width; /* the number of points in the histogram */ - double bucket_capacity; /* number of rows in a bucket of the histogram */ - uint curr_bucket; /* number of the current bucket to be built */ - - std::vector<std::string> bucket_bounds; - bool first_value= true; -public: - Histogram_json_builder(Field *col, uint col_len, ha_rows rows) - : Histogram_builder(col, col_len, rows) - { - histogram= (Histogram_json_hb*)col->collected_stats->histogram; - bucket_capacity= (double)records / histogram->get_width(); - hist_width= histogram->get_width(); - curr_bucket= 0; - } - - ~Histogram_json_builder() override = default; - - /* - Add data to the histogram. Adding Element elem which encountered elem_cnt - times. - */ - int next(void *elem, element_count elem_cnt) override - { - counters.next(elem, elem_cnt); - ulonglong count= counters.get_count(); - - if (curr_bucket == hist_width) - return 0; - if (first_value) - { - first_value= false; - column->store_field_value((uchar*) elem, col_length); - StringBuffer<MAX_FIELD_WIDTH> val; - column->val_str(&val); - bucket_bounds.push_back(std::string(val.ptr(), val.length())); - } - - if (count > bucket_capacity * (curr_bucket + 1)) - { - column->store_field_value((uchar*) elem, col_length); - StringBuffer<MAX_FIELD_WIDTH> val; - column->val_str(&val); - bucket_bounds.emplace_back(val.ptr(), val.length()); - - curr_bucket++; - while (curr_bucket != hist_width && - count > bucket_capacity * (curr_bucket + 1)) - { - bucket_bounds.push_back(std::string(val.ptr(), val.length())); - curr_bucket++; - } - } - - if (records == count && bucket_bounds.size() == hist_width) - { - column->store_field_value((uchar*) elem, col_length); - StringBuffer<MAX_FIELD_WIDTH> val; - column->val_str(&val); - bucket_bounds.push_back(std::string(val.ptr(), val.length())); - } - return 0; - } - - /* - Finalize the creation of histogram - */ - void finalize() override - { - Json_writer writer; - writer.start_object(); - writer.add_member(Histogram_json_hb::JSON_NAME).start_array(); - - for(auto& value: bucket_bounds) { - writer.add_str(value.c_str()); - } - writer.end_array(); - writer.end_object(); - Binary_string *json_string = (Binary_string *) writer.output.get_string(); - histogram->set_json_text(bucket_bounds.size()-1, - (uchar *) json_string->c_ptr()); - } -}; - - -Histogram_builder *Histogram_json_hb::create_builder(Field *col, uint col_len, - ha_rows rows) -{ - return new Histogram_json_builder(col, col_len, rows); -} - - Histogram_base *create_histogram(MEM_ROOT *mem_root, Histogram_type hist_type, THD *owner) { diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 0b1b310941f..7ac315f48e2 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -162,11 +162,18 @@ class Histogram_base : public Sql_alloc virtual uint get_width()=0; - virtual Histogram_builder *create_builder(Field *col, uint col_len, - ha_rows rows)=0; - + /* + The creation-time workflow is: + * create a histogram + * init_for_collection() + * create_builder() + * feed the data to the builder + * serialize(); + */ virtual void init_for_collection(MEM_ROOT *mem_root, Histogram_type htype_arg, ulonglong size)=0; + virtual Histogram_builder *create_builder(Field *col, uint col_len, + ha_rows rows)=0; virtual bool is_available()=0; @@ -177,19 +184,26 @@ class Histogram_base : public Sql_alloc virtual double range_selectivity(Field *field, key_range *min_endp, key_range *max_endp)=0; - // Legacy: return the size of the histogram on disk. - // This will be stored in mysql.column_stats.hist_size column. - // Newer, JSON-based histograms may return 0. + /* + Legacy: return the size of the histogram on disk. + + This will be stored in mysql.column_stats.hist_size column. + The value is not really needed as one can look at + LENGTH(mysql.column_stats.histogram) directly. + */ virtual uint get_size()=0; virtual ~Histogram_base()= default; - Histogram_base() : owner(NULL) {} + + /* + Memory management: a histogram may be (exclusively) "owned" by a particular + thread (done for histograms that are being collected). By default, a + histogram has owner==NULL and is not owned by any particular thread. + */ THD *get_owner() { return owner; } void set_owner(THD *thd) { owner=thd; } private: - // Owner is a thread that *exclusively* owns this histogram (and so can - // delete it at any time) THD *owner; }; @@ -353,75 +367,72 @@ class Histogram_binary : public Histogram_base /* - An equi-height histogram which stores real values for bucket bounds. - - Handles @@histogram_type=JSON_HB + This is used to collect the the basic statistics from a Unique object: + - count of values + - count of distinct values + - count of distinct values that have occurred only once */ -class Histogram_json_hb : public Histogram_base +class Basic_stats_collector { -private: - size_t size; /* Number of elements in the histogram */ - - /* Collection-time only: collected histogram in the JSON form. */ - std::string json_text; - - // Array of histogram bucket endpoints in KeyTupleFormat. - std::vector<std::string> histogram_bounds; + ulonglong count; /* number of values retrieved */ + ulonglong count_distinct; /* number of distinct values retrieved */ + /* number of distinct values that occured only once */ + ulonglong count_distinct_single_occurence; public: - static constexpr const char* JSON_NAME="histogram_hb_v1"; - - bool parse(MEM_ROOT *mem_root, Field *field, Histogram_type type_arg, - const char *hist_data, size_t hist_data_len) override; - - void serialize(Field *field) override; - - Histogram_builder *create_builder(Field *col, uint col_len, - ha_rows rows) override; - - // returns number of buckets in the histogram - uint get_width() override + Basic_stats_collector() { - return (uint)size; + count= 0; + count_distinct= 0; + count_distinct_single_occurence= 0; } - Histogram_type get_type() override + ulonglong get_count_distinct() const { return count_distinct; } + ulonglong get_count_single_occurence() const { - return JSON_HB; + return count_distinct_single_occurence; } + ulonglong get_count() const { return count; } - void set_json_text(ulonglong sz, uchar *json_text_arg) + void next(void *elem, element_count elem_cnt) { - size = (uint8) sz; - json_text.assign((const char*)json_text_arg, - strlen((const char*)json_text_arg)); + count_distinct++; + if (elem_cnt == 1) + count_distinct_single_occurence++; + count+= elem_cnt; } +}; - uint get_size() override - { - return size; - } - void init_for_collection(MEM_ROOT *mem_root, Histogram_type htype_arg, - ulonglong size) override; +/* + Histogram_builder is a helper class that is used to build histograms + for columns. - bool is_available() override {return true; } + Do not create directly, call Histogram->get_builder(...); +*/ - bool is_usable(THD *thd) override - { - return thd->variables.optimizer_use_condition_selectivity > 3 && - is_available(); - } +class Histogram_builder +{ +protected: + Field *column; /* table field for which the histogram is built */ + uint col_length; /* size of this field */ + ha_rows records; /* number of records the histogram is built for */ - double point_selectivity(Field *field, key_range *endpoint, - double avg_selection) override; - double range_selectivity(Field *field, key_range *min_endp, - key_range *max_endp) override; -private: - int find_bucket(Field *field, const uchar *lookup_val, bool equal_is_less); + Histogram_builder(Field *col, uint col_len, ha_rows rows) : + column(col), col_length(col_len), records(rows) + {} + +public: + // A histogram builder will also collect the counters + Basic_stats_collector counters; + + virtual int next(void *elem, element_count elem_cnt)=0; + virtual void finalize()=0; + virtual ~Histogram_builder(){} }; + class Columns_statistics; class Index_statistics;

1 0

[Commits] b7b3b6b44a5: Histogram code cleanup and fixes
by psergey 04 Sep '21

04 Sep '21

revision-id: b7b3b6b44a5048ae26599131a7608f9f6579eb87 (mariadb-10.6.1-113-gb7b3b6b44a5) parent(s): 3ecc26029fc95b566c6267a934aa0f1c432f3069 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-09-04 16:28:10 +0300 message: Histogram code cleanup and fixes Factor the code that updates count, count_distinct, count_distinct_single_occurrence into class Basic_stats_collector Change from Histogram_builder and its descendant Histogram_builder_json to Histogram_builder (the interface), and Histogram_binary_builder, Histogram_json_builder. In Histogram_json_builder, do not forget to collect the right bound of the right-most bucket. --- mysql-test/main/statistics_json.result | 1740 ++++++++++++++++---------------- sql/sql_statistics.cc | 181 ++-- sql/sql_statistics.h | 3 +- 3 files changed, 1003 insertions(+), 921 deletions(-) diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index 4de1b768a1e..857b062ae47 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -232,12 +232,12 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a 0 49 0.0000 1.0000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2239222C20223139222C20223331222C20223430225D0A7D -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202276767676767676767676767676222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A2020202022797979222C0A20202020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A220A20205D0A7D -test t1 c aaaa dddddddd 0.1250 7.0000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2261616161222C2022626262626262222C2022636363636363636363222C20226464646464646464225D0A7D -test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B22313938392D30332D3132222C2022313939302D30352D3135222C2022313939302D30352D3135222C2022313939392D30372D3233225D0A7D -test t1 e 0.01 0.112 0.2250 6.2000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B22302E3031222C2022302E303132222C2022302E3035222C2022302E31225D0A7D -test t1 f 1 5 0.2000 6.4000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2202222C202203222C202204222C202204225D0A7D +test t1 a 0 49 0.0000 1.0000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2230222C20223131222C20223234222C20223338222C20223439225D0A7D +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202276767676767676767676767676222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A20202020227878787878787878787878787878787878787878787878787878222C0A2020202022797979222C0A20202020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A220A20205D0A7D +test t1 c aaaa dddddddd 0.1250 7.0000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2261616161222C202261616161222C2022636363636363636363222C20226464646464646464222C20226464646464646464225D0A7D +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022313938392D30332D3132222C0A2020202022313938392D30332D3132222C0A2020202022313939302D30352D3135222C0A2020202022313939382D30382D3238222C0A2020202022313939392D30372D3233220A20205D0A7D +test t1 e 0.01 0.112 0.2250 6.2000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B22302E3031222C2022302E3031222C2022302E3035222C2022302E31222C2022302E313132225D0A7D +test t1 f 1 5 0.2000 6.4000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2201222C202202222C202203222C202204222C202205225D0A7D DELETE FROM mysql.column_stats; set histogram_size=8; set histogram_type=@DOUBLE_PREC_TYPE; @@ -251,12 +251,12 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a 0 49 0.0000 1.0000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2234222C202239222C20223135222C20223231222C20223239222C20223333222C20223339222C20223433225D0A7D -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202276767676767676767676767676222C0A202020202276767676767676767676767676222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A20202020227878787878787878787878787878787878787878787878787878222C0A2020202022797979222C0A2020202022797979222C0A20202020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A220A20205D0A7D -test t1 c aaaa dddddddd 0.1250 7.0000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202261616161222C0A202020202261616161222C0A2020202022626262626262222C0A2020202022626262626262222C0A2020202022636363636363636363222C0A2020202022636363636363636363222C0A20202020226464646464646464222C0A20202020226464646464646464220A20205D0A7D -test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022313938392D30332D3132222C0A2020202022313938392D30332D3132222C0A2020202022313939302D30352D3135222C0A2020202022313939302D30352D3135222C0A2020202022313939302D30352D3135222C0A2020202022313939302D30352D3135222C0A2020202022313939392D30372D3233222C0A2020202022313939392D30372D3233220A20205D0A7D -test t1 e 0.01 0.112 0.2250 6.2000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022302E3031222C0A2020202022302E3031222C0A2020202022302E3031222C0A2020202022302E303132222C0A2020202022302E3035222C0A2020202022302E31222C0A2020202022302E31222C0A2020202022302E313132220A20205D0A7D -test t1 f 1 5 0.2000 6.4000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2201222C202202222C202202222C202203222C202203222C202204222C202204222C202205225D0A7D +test t1 a 0 49 0.0000 1.0000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2230222C202235222C20223131222C20223138222C20223234222C20223332222C20223338222C20223433222C20223439225D0A7D +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202276767676767676767676767676222C0A202020202276767676767676767676767676222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A20202020227878787878787878787878787878787878787878787878787878222C0A2020202022797979222C0A2020202022797979222C0A20202020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A222C0A20202020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A220A20205D0A7D +test t1 c aaaa dddddddd 0.1250 7.0000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202261616161222C0A202020202261616161222C0A202020202261616161222C0A2020202022626262626262222C0A2020202022636363636363636363222C0A2020202022636363636363636363222C0A20202020226464646464646464222C0A20202020226464646464646464222C0A20202020226464646464646464220A20205D0A7D +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022313938392D30332D3132222C0A2020202022313938392D30332D3132222C0A2020202022313938392D30332D3132222C0A2020202022313939302D30352D3135222C0A2020202022313939302D30352D3135222C0A2020202022313939302D30352D3135222C0A2020202022313939382D30382D3238222C0A2020202022313939392D30372D3233222C0A2020202022313939392D30372D3233220A20205D0A7D +test t1 e 0.01 0.112 0.2250 6.2000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022302E3031222C0A2020202022302E3031222C0A2020202022302E3031222C0A2020202022302E3031222C0A2020202022302E3035222C0A2020202022302E31222C0A2020202022302E31222C0A2020202022302E313132222C0A2020202022302E313132220A20205D0A7D +test t1 f 1 5 0.2000 6.4000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2201222C202202222C202202222C202203222C202203222C202204222C202204222C202205222C202205225D0A7D DELETE FROM mysql.column_stats; set histogram_size= 0; set histogram_type=@SINGLE_PREC_TYPE; @@ -1502,7 +1502,7 @@ avg_length 4.0000 avg_frequency 2.7640 hist_size 100 hist_type JSON_HB -hex(histogram) 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E31222C0A2020202022302E31222C0A2020202022302E32222C0A2020202022302E32222C0A2020202022302E33222C0A2020202022302E33222C0A2020202022302E34222C0A2020202022302E34222C0A2020202022302E34222C0A2020202022302E35222C0A2020202022302E35222C0A2020202022302E36222C0A2020202022302E36222C0A2020202022302E37222C0A2020202022302E37222C0A2020202022302E38222C0A2020202022302E39222C0A2020202022312E31222C0A2020202022312E32222C0A2020202022312E33222C0A2020202022312E34222C0A2020202022312E34222C0A2020202022312E36222C0A2020202022312E36222C0A2020202022312E37222C0A2020202022312E39222C0A2020202022322E30222C0A2020202022322E32222C0A2020202022322E32222C0A2020202022322E33222C0A2020202022322E35222C0A2020202022322E36222C0A2020202022322E38222C0A2020202022322E39222C0A2020202022332E31222C0A2020202022332E32222C0A20202020 22332E34222C0A2020202022332E36222C0A2020202022332E38222C0A2020202022342E30222C0A2020202022342E33222C0A2020202022342E35222C0A2020202022342E38222C0A2020202022352E31222C0A2020202022352E34222C0A2020202022352E37222C0A2020202022352E38222C0A2020202022362E31222C0A2020202022362E34222C0A2020202022362E38222C0A2020202022372E32222C0A2020202022372E35222C0A2020202022372E37222C0A2020202022382E31222C0A2020202022382E35222C0A2020202022382E38222C0A2020202022392E31222C0A2020202022392E35222C0A202020202231302E31222C0A202020202231302E38222C0A202020202231312E33222C0A202020202231322E30222C0A202020202231322E36222C0A202020202231332E35222C0A202020202231342E32222C0A202020202231362E31222C0A202020202231362E38222C0A202020202231382E31222C0A202020202232302E30222C0A202020202232312E36222C0A202020202232332E34222C0A202020202232382E33222C0A202020202233312E37222C0A202020202233342E31222C0A202020202233372E35222C0A202020202234312E39222C0A202020202234362E34222C0A202020202235302E37222C0A202020202235352E31222C0A202020202236302E3 4222C0A202020202236352E36222C0A202020202237322E36222C0A202020202237372E31222C0A202020202238312E32222C0A202020202238352E36222C0A202020202238372E37222C0A202020202238392E39222C0A202020202239322E31222C0A202020202239342E32222C0A202020202239352E39222C0A202020202239372E33222C0A202020202239382E31222C0A202020202239392E30222C0A202020202239392E39220A20205D0A7D +hex(histogram) 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E31222C0A2020202022302E31222C0A2020202022302E32222C0A2020202022302E32222C0A2020202022302E33222C0A2020202022302E33222C0A2020202022302E34222C0A2020202022302E34222C0A2020202022302E34222C0A2020202022302E35222C0A2020202022302E35222C0A2020202022302E36222C0A2020202022302E36222C0A2020202022302E37222C0A2020202022302E38222C0A2020202022302E39222C0A2020202022312E30222C0A2020202022312E31222C0A2020202022312E33222C0A2020202022312E33222C0A2020202022312E34222C0A2020202022312E35222C0A2020202022312E36222C0A2020202022312E37222C0A2020202022312E38222C0A2020202022312E39222C0A2020202022322E30222C0A2020202022322E32222C0A2020202022322E33222C0A2020202022322E34222C0A2020202022322E35222C0A2020202022322E37222C0A2020202022322E38222C0A2020202022332E30222C0A2020202022332E32222C0A20202020 22332E33222C0A2020202022332E35222C0A2020202022332E37222C0A2020202022332E38222C0A2020202022342E31222C0A2020202022342E33222C0A2020202022342E37222C0A2020202022342E39222C0A2020202022352E33222C0A2020202022352E35222C0A2020202022352E37222C0A2020202022352E39222C0A2020202022362E34222C0A2020202022362E37222C0A2020202022372E30222C0A2020202022372E34222C0A2020202022372E36222C0A2020202022372E38222C0A2020202022382E32222C0A2020202022382E37222C0A2020202022392E30222C0A2020202022392E33222C0A2020202022392E37222C0A202020202231302E35222C0A202020202231312E30222C0A202020202231312E38222C0A202020202231322E34222C0A202020202231332E32222C0A202020202231342E30222C0A202020202231352E38222C0A202020202231362E35222C0A202020202231382E30222C0A202020202231392E37222C0A202020202232312E32222C0A202020202232332E32222C0A202020202232372E38222C0A202020202233312E30222C0A202020202233322E39222C0A202020202233362E34222C0A202020202234312E36222C0A202020202234352E37222C0A202020202235302E32222C0A202020202235322E36222C0A202020202236302E332 22C0A202020202236352E33222C0A202020202237322E31222C0A202020202237362E37222C0A202020202238312E32222C0A202020202238352E36222C0A202020202238372E36222C0A202020202238392E37222C0A202020202239322E31222C0A202020202239342E31222C0A202020202239352E38222C0A202020202239372E32222C0A202020202239382E31222C0A202020202239392E30222C0A202020202239392E39222C0A202020202239392E39220A20205D0A7D decode_histogram(hist_type,histogram) { "histogram_hb_v1": [ "0.0", @@ -1511,6 +1511,7 @@ decode_histogram(hist_type,histogram) { "0.0", "0.0", "0.0", + "0.0", "0.1", "0.1", "0.2", @@ -1525,85 +1526,85 @@ decode_histogram(hist_type,histogram) { "0.6", "0.6", "0.7", - "0.7", "0.8", "0.9", + "1.0", "1.1", - "1.2", + "1.3", "1.3", "1.4", - "1.4", - "1.6", + "1.5", "1.6", "1.7", + "1.8", "1.9", "2.0", "2.2", - "2.2", "2.3", + "2.4", "2.5", - "2.6", + "2.7", "2.8", - "2.9", - "3.1", + "3.0", "3.2", - "3.4", - "3.6", + "3.3", + "3.5", + "3.7", "3.8", - "4.0", + "4.1", "4.3", - "4.5", - "4.8", - "5.1", - "5.4", + "4.7", + "4.9", + "5.3", + "5.5", "5.7", - "5.8", - "6.1", + "5.9", "6.4", - "6.8", - "7.2", - "7.5", - "7.7", - "8.1", - "8.5", - "8.8", - "9.1", - "9.5", - "10.1", - "10.8", - "11.3", - "12.0", - "12.6", - "13.5", - "14.2", - "16.1", - "16.8", - "18.1", - "20.0", - "21.6", - "23.4", - "28.3", - "31.7", - "34.1", - "37.5", - "41.9", - "46.4", - "50.7", - "55.1", - "60.4", - "65.6", - "72.6", - "77.1", + "6.7", + "7.0", + "7.4", + "7.6", + "7.8", + "8.2", + "8.7", + "9.0", + "9.3", + "9.7", + "10.5", + "11.0", + "11.8", + "12.4", + "13.2", + "14.0", + "15.8", + "16.5", + "18.0", + "19.7", + "21.2", + "23.2", + "27.8", + "31.0", + "32.9", + "36.4", + "41.6", + "45.7", + "50.2", + "52.6", + "60.3", + "65.3", + "72.1", + "76.7", "81.2", "85.6", - "87.7", - "89.9", + "87.6", + "89.7", "92.1", - "94.2", - "95.9", - "97.3", + "94.1", + "95.8", + "97.2", "98.1", "99.0", + "99.9", "99.9" ] } @@ -1618,263 +1619,264 @@ avg_length 4.0000 avg_frequency 1.0467 hist_size 254 hist_type JSON_HB -hex(histogram) 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202231343338222C0A202020202235303535222C0A20202020223133303030222C0A20202020223235383838222C0A20202020223530363939222C0A20202020223839303533222C0A20202020223839333838222C0A20202020223839383831222C0A20202020223930313131222C0A20202020223930363031222C0A20202020223930393531222C0A20202020223931323030222C0A20202020223931373737222C0A20202020223932323234222C0A20202020223932353833222C0A20202020223932393838222C0A20202020223933333030222C0A20202020223933383138222C0A20202020223934313030222C0A20202020223934363030222C0A20202020223934393334222C0A20202020223935343030222C0A20202020223935393333222C0A20202020223936323931222C0A20202020223936383030222C0A20202020223937313030222C0A20202020223937343531222C0A20202020223938303830222C0A20202020223938333432222C0A20202020223938373831222C0A20202020223939333637222C0A20202020223939373939222C0A2020202022313030313138222C0A2020202022313030343738222C0A2020202022313030383837222C0A20202020223130313230 35222C0A2020202022313031353734222C0A2020202022313031393834222C0A2020202022313032323934222C0A2020202022313032363831222C0A2020202022313033313731222C0A2020202022313033353434222C0A2020202022313033393834222C0A2020202022313034373030222C0A2020202022313035313139222C0A2020202022313035363930222C0A2020202022313036303031222C0A2020202022313036343134222C0A2020202022313037303030222C0A2020202022313037333239222C0A2020202022313037373631222C0A2020202022313038313030222C0A2020202022313038353734222C0A2020202022313039313231222C0A2020202022313039353030222C0A2020202022313039393635222C0A2020202022313130333838222C0A2020202022313131313030222C0A2020202022313131373532222C0A2020202022313132333735222C0A2020202022313133303830222C0A2020202022313133383030222C0A2020202022313134323333222C0A2020202022313134383736222C0A2020202022313135353332222C0A2020202022313136313738222C0A2020202022313136363935222C0A2020202022313137323237222C0A2020202022313137383635222C0A2020202022313138373138222C0A2020202022313139323833222C0A202020202 2313139373936222C0A2020202022313230323635222C0A2020202022313231303030222C0A2020202022313231363030222C0A2020202022313231393534222C0A2020202022313232373035222C0A2020202022313233333539222C0A2020202022313233383635222C0A2020202022313234323037222C0A2020202022313234373335222C0A2020202022313235323535222C0A2020202022313235373636222C0A2020202022313236323832222C0A2020202022313236383230222C0A2020202022313237323232222C0A2020202022313237383031222C0A2020202022313238333030222C0A2020202022313239333030222C0A2020202022313330303030222C0A2020202022313331303030222C0A2020202022313331373137222C0A2020202022313332343535222C0A2020202022313333313036222C0A2020202022313333363432222C0A2020202022313334303337222C0A2020202022313335303130222C0A2020202022313336323136222C0A2020202022313337303238222C0A2020202022313337373736222C0A2020202022313338343138222C0A2020202022313339323833222C0A2020202022313430303330222C0A2020202022313430383030222C0A2020202022313432303434222C0A2020202022313432363539222C0A2020202022313433373236222C 0A2020202022313434353832222C0A2020202022313435383030222C0A2020202022313436343339222C0A2020202022313437353233222C0A2020202022313438313035222C0A2020202022313439313436222C0A2020202022313530313030222C0A2020202022313531303630222C0A2020202022313532343432222C0A2020202022313533333634222C0A2020202022313534393830222C0A2020202022313535383030222C0A2020202022313537333030222C0A2020202022313538333335222C0A2020202022313539363332222C0A2020202022313631313631222C0A2020202022313632333030222C0A2020202022313633383439222C0A2020202022313634373437222C0A2020202022313636343637222C0A2020202022313637343631222C0A2020202022313639333030222C0A2020202022313730343238222C0A2020202022313731353332222C0A2020202022313732373031222C0A2020202022313733383935222C0A2020202022313734393835222C0A2020202022313736353736222C0A2020202022313738313832222C0A2020202022313739323038222C0A2020202022313830323130222C0A2020202022313831383035222C0A2020202022313833313030222C0A2020202022313834303631222C0A2020202022313835343031222C0A202020202231383 6393030222C0A2020202022313838333434222C0A2020202022313839353639222C0A2020202022313930393035222C0A2020202022313933303035222C0A2020202022313934333030222C0A2020202022313935353030222C0A2020202022313937323534222C0A2020202022313939303030222C0A2020202022323030393031222C0A2020202022323032313334222C0A2020202022323033373933222C0A2020202022323036313538222C0A2020202022323037353838222C0A2020202022323130303638222C0A2020202022323132393736222C0A2020202022323134393530222C0A2020202022323136373335222C0A2020202022323138343437222C0A2020202022323231303030222C0A2020202022323232353138222C0A2020202022323234383837222C0A2020202022323237363537222C0A2020202022323239343235222C0A2020202022323333303431222C0A2020202022323336303030222C0A2020202022323339313234222C0A2020202022323431363439222C0A2020202022323433373432222C0A2020202022323435373732222C0A2020202022323438323435222C0A2020202022323532333836222C0A2020202022323534383432222C0A2020202022323537383132222C0A2020202022323632303030222C0A2020202022323634303831222C0A2020 202022323636323831222C0A2020202022323730323531222C0A2020202022323732393638222C0A2020202022323736343231222C0A2020202022323739333430222C0A2020202022323832393431222C0A2020202022323836393030222C0A2020202022323931303030222C0A2020202022323934303536222C0A2020202022323938393030222C0A2020202022333031323736222C0A2020202022333034343737222C0A2020202022333039373530222C0A2020202022333133353330222C0A2020202022333137363030222C0A2020202022333232323637222C0A2020202022333236333939222C0A2020202022333330323736222C0A2020202022333334353633222C0A2020202022333339313331222C0A2020202022333432373338222C0A2020202022333439323436222C0A2020202022333533363332222C0A2020202022333539313437222C0A2020202022333632343730222C0A2020202022333636353439222C0A2020202022333732383430222C0A2020202022333830373535222C0A2020202022333835323031222C0A2020202022333932383330222C0A2020202022343030393937222C0A2020202022343039313030222C0A2020202022343136393838222C0A2020202022343231353839222C0A2020202022343238353232222C0A202020202234333539363 4222C0A2020202022343433373237222C0A2020202022343532393736222C0A2020202022343631303030222C0A2020202022343639373335222C0A2020202022343736383030222C0A2020202022343833313535222C0A2020202022343933343039222C0A2020202022353038383939222C0A2020202022353139373933222C0A2020202022353239393030222C0A2020202022353430383238222C0A2020202022353633363632222C0A2020202022353830303030222C0A2020202022353934353031222C0A2020202022363136373030222C0A2020202022363336373635222C0A2020202022363536393235222C0A2020202022363830333332222C0A2020202022373033353932222C0A2020202022373335313637222C0A2020202022373634393032222C0A2020202022373937373335222C0A2020202022383330303030222C0A2020202022383737323339222C0A2020202022393430353839222C0A2020202022393933343030222C0A202020202231303432373430222C0A202020202231313030303030222C0A202020202231313536313030222C0A202020202231323137383138222C0A202020202231333030393737222C0A202020202231333932383630222C0A202020202231353137353530222C0A202020202231363832303030222C0A2020202022313936393836 38222C0A202020202232313534333736222C0A202020202232353935363734222C0A202020202232393634363338222C0A202020202234323536333030222C0A202020202236373538383435220A20205D0A7D +hex(histogram) 7B0A202022686973746F6772616D5F68625F7631223A205B0A20202020223432222C0A202020202231353030222C0A202020202235323030222C0A20202020223133313534222C0A20202020223237303235222C0A20202020223531393639222C0A20202020223839303633222C0A20202020223839343030222C0A20202020223839393030222C0A20202020223930323030222C0A20202020223930363033222C0A20202020223930393539222C0A20202020223931323033222C0A20202020223931373739222C0A20202020223932323339222C0A20202020223932353833222C0A20202020223933303030222C0A20202020223933333436222C0A20202020223933393030222C0A20202020223934313933222C0A20202020223934363835222C0A20202020223935303030222C0A20202020223935343438222C0A20202020223936303030222C0A20202020223936333232222C0A20202020223936383030222C0A20202020223937313638222C0A20202020223937363030222C0A20202020223938313233222C0A20202020223938333939222C0A20202020223938393030222C0A20202020223939343030222C0A20202020223939383931222C0A2020202022313030313439222C0A2020202022313030353435222C0A2020202022313030393430222C0A 2020202022313031323935222C0A2020202022313031363536222C0A2020202022313032303532222C0A2020202022313032333631222C0A2020202022313032373136222C0A2020202022313033323737222C0A2020202022313033353739222C0A2020202022313034313436222C0A2020202022313034373635222C0A2020202022313035313939222C0A2020202022313035373030222C0A2020202022313036303738222C0A2020202022313036363030222C0A2020202022313037303036222C0A2020202022313037343030222C0A2020202022313037373939222C0A2020202022313038323737222C0A2020202022313038373234222C0A2020202022313039323235222C0A2020202022313039353736222C0A2020202022313130303136222C0A2020202022313130353331222C0A2020202022313131323538222C0A2020202022313131383832222C0A2020202022313132343530222C0A2020202022313133313335222C0A2020202022313133393538222C0A2020202022313134333935222C0A2020202022313134393830222C0A2020202022313135363938222C0A2020202022313136323536222C0A2020202022313136393239222C0A2020202022313137353339222C0A2020202022313138323030222C0A2020202022313138383538222C0A20202020223131393 43030222C0A2020202022313139393930222C0A2020202022313230363435222C0A2020202022313231303937222C0A2020202022313231373038222C0A2020202022313232313036222C0A2020202022313233313030222C0A2020202022313233343030222C0A2020202022313233393538222C0A2020202022313234323739222C0A2020202022313234393433222C0A2020202022313235343037222C0A2020202022313235393937222C0A2020202022313236333436222C0A2020202022313237303030222C0A2020202022313237343834222C0A2020202022313237393639222C0A2020202022313238373330222C0A2020202022313239373030222C0A2020202022313330323135222C0A2020202022313331313338222C0A2020202022313331383631222C0A2020202022313332373434222C0A2020202022313333333030222C0A2020202022313333383539222C0A2020202022313334343139222C0A2020202022313335363033222C0A2020202022313336363937222C0A2020202022313337323635222C0A2020202022313337393934222C0A2020202022313338393033222C0A2020202022313339363732222C0A2020202022313430343539222C0A2020202022313431343638222C0A2020202022313432333032222C0A2020202022313433303732222C0A202020 2022313434313538222C0A2020202022313435313530222C0A2020202022313436303836222C0A2020202022313437303030222C0A2020202022313437373434222C0A2020202022313438353833222C0A2020202022313439373032222C0A2020202022313530363234222C0A2020202022313531373538222C0A2020202022313532393833222C0A2020202022313534323937222C0A2020202022313535323434222C0A2020202022313536343638222C0A2020202022313538303030222C0A2020202022313539313033222C0A2020202022313630363339222C0A2020202022313631393130222C0A2020202022313633333531222C0A2020202022313634343633222C0A2020202022313635353833222C0A2020202022313637303531222C0A2020202022313638373032222C0A2020202022313639393330222C0A2020202022313731303536222C0A2020202022313732333537222C0A2020202022313733363030222C0A2020202022313734353030222C0A2020202022313735363631222C0A2020202022313737353232222C0A2020202022313738373438222C0A2020202022313739383138222C0A2020202022313831333339222C0A2020202022313832363339222C0A2020202022313833353030222C0A2020202022313834383539222C0A20202020223138363030302 22C0A2020202022313837363931222C0A2020202022313839303336222C0A2020202022313930313233222C0A2020202022313932323437222C0A2020202022313934303030222C0A2020202022313935333436222C0A2020202022313936353139222C0A2020202022313938313833222C0A2020202022323030313731222C0A2020202022323031353638222C0A2020202022323033323936222C0A2020202022323035353630222C0A2020202022323037313030222C0A2020202022323039303338222C0A2020202022323131373030222C0A2020202022323134323934222C0A2020202022323135353837222C0A2020202022323137383034222C0A2020202022323139373733222C0A2020202022323232303330222C0A2020202022323233383931222C0A2020202022323236353039222C0A2020202022323239303630222C0A2020202022323331373234222C0A2020202022323335303733222C0A2020202022323337373133222C0A2020202022323430393030222C0A2020202022323432383230222C0A2020202022323434393734222C0A2020202022323437343532222C0A2020202022323531333438222C0A2020202022323534333530222C0A2020202022323536343030222C0A2020202022323630353132222C0A2020202022323633323437222C0A202020202232 3635343530222C0A2020202022323639363030222C0A2020202022323732303538222C0A2020202022323735383037222C0A2020202022323738333137222C0A2020202022323832303730222C0A2020202022323836303030222C0A2020202022323839333736222C0A2020202022323933313035222C0A2020202022323937303030222C0A2020202022333030353030222C0A2020202022333033343437222C0A2020202022333038353731222C0A2020202022333132393237222C0A2020202022333136343338222C0A2020202022333230303935222C0A2020202022333235303030222C0A2020202022333239383530222C0A2020202022333333323030222C0A2020202022333337393737222C0A2020202022333432323030222C0A2020202022333438303730222C0A2020202022333533303436222C0A2020202022333537353532222C0A2020202022333631393538222C0A2020202022333635333832222C0A2020202022333731363031222C0A2020202022333739353230222C0A2020202022333834303030222C0A2020202022333931313730222C0A2020202022333939313735222C0A2020202022343037303138222C0A2020202022343136323839222C0A2020202022343230303030222C0A2020202022343237363532222C0A2020202022343334333734222C0A2 020202022343431393638222C0A2020202022343530323838222C0A2020202022343539383834222C0A2020202022343639303030222C0A2020202022343735363537222C0A2020202022343832333030222C0A2020202022343930353234222C0A2020202022353038303030222C0A2020202022353138323832222C0A2020202022353239303933222C0A2020202022353430333330222C0A2020202022353633323130222C0A2020202022353737333532222C0A2020202022353933333231222C0A2020202022363130303030222C0A2020202022363334303635222C0A2020202022363536353632222C0A2020202022363734313030222C0A2020202022373033303030222C0A2020202022373330303030222C0A2020202022373632303030222C0A2020202022373932383538222C0A2020202022383233333031222C0A2020202022383734303030222C0A2020202022393335333631222C0A2020202022393839393735222C0A202020202231303430303030222C0A202020202231303936383239222C0A202020202231313531323734222C0A202020202231323137333539222C0A202020202231323937353236222C0A202020202231333738303837222C0A202020202231353033343531222C0A202020202231363734303030222C0A202020202231393638343030222C0A 202020202232313339313235222C0A202020202232353539343234222C0A202020202232393430363233222C0A202020202234303137373333222C0A202020202236343634363933222C0A20202020223130353030303030220A20205D0A7D decode_histogram(hist_type,histogram) { "histogram_hb_v1": [ - "1438", - "5055", - "13000", - "25888", - "50699", - "89053", - "89388", - "89881", - "90111", - "90601", - "90951", - "91200", - "91777", - "92224", + "42", + "1500", + "5200", + "13154", + "27025", + "51969", + "89063", + "89400", + "89900", + "90200", + "90603", + "90959", + "91203", + "91779", + "92239", "92583", - "92988", - "93300", - "93818", - "94100", - "94600", - "94934", - "95400", - "95933", - "96291", + "93000", + "93346", + "93900", + "94193", + "94685", + "95000", + "95448", + "96000", + "96322", "96800", - "97100", - "97451", - "98080", - "98342", - "98781", - "99367", - "99799", - "100118", - "100478", - "100887", - "101205", - "101574", - "101984", - "102294", - "102681", - "103171", - "103544", - "103984", - "104700", - "105119", - "105690", - "106001", - "106414", - "107000", - "107329", - "107761", - "108100", - "108574", - "109121", - "109500", - "109965", - "110388", - "111100", - "111752", - "112375", - "113080", - "113800", - "114233", - "114876", - "115532", - "116178", - "116695", - "117227", - "117865", - "118718", - "119283", - "119796", - "120265", - "121000", - "121600", - "121954", - "122705", - "123359", - "123865", - "124207", - "124735", - "125255", - "125766", - "126282", - "126820", - "127222", - "127801", - "128300", - "129300", - "130000", - "131000", - "131717", - "132455", - "133106", - "133642", - "134037", - "135010", - "136216", - "137028", - "137776", - "138418", - "139283", - "140030", - "140800", - "142044", - "142659", - "143726", - "144582", - "145800", - "146439", - "147523", - "148105", - "149146", - "150100", - "151060", - "152442", - "153364", - "154980", - "155800", - "157300", - "158335", - "159632", - "161161", - "162300", - "163849", - "164747", - "166467", - "167461", - "169300", - "170428", - "171532", - "172701", - "173895", - "174985", - "176576", - "178182", - "179208", - "180210", - "181805", - "183100", - "184061", - "185401", - "186900", - "188344", - "189569", - "190905", - "193005", - "194300", - "195500", - "197254", - "199000", - "200901", - "202134", - "203793", - "206158", - "207588", - "210068", - "212976", - "214950", - "216735", - "218447", - "221000", - "222518", - "224887", - "227657", - "229425", - "233041", - "236000", - "239124", - "241649", - "243742", - "245772", - "248245", - "252386", - "254842", - "257812", - "262000", - "264081", - "266281", - "270251", - "272968", - "276421", - "279340", - "282941", - "286900", - "291000", - "294056", - "298900", - "301276", - "304477", - "309750", - "313530", - "317600", - "322267", - "326399", - "330276", - "334563", - "339131", - "342738", - "349246", - "353632", - "359147", - "362470", - "366549", - "372840", - "380755", - "385201", - "392830", - "400997", - "409100", - "416988", - "421589", - "428522", - "435964", - "443727", - "452976", - "461000", - "469735", - "476800", - "483155", - "493409", - "508899", - "519793", - "529900", - "540828", - "563662", - "580000", - "594501", - "616700", - "636765", - "656925", - "680332", - "703592", - "735167", - "764902", - "797735", - "830000", - "877239", - "940589", - "993400", - "1042740", - "1100000", - "1156100", - "1217818", - "1300977", - "1392860", - "1517550", - "1682000", - "1969868", - "2154376", - "2595674", - "2964638", - "4256300", - "6758845" + "97168", + "97600", + "98123", + "98399", + "98900", + "99400", + "99891", + "100149", + "100545", + "100940", + "101295", + "101656", + "102052", + "102361", + "102716", + "103277", + "103579", + "104146", + "104765", + "105199", + "105700", + "106078", + "106600", + "107006", + "107400", + "107799", + "108277", + "108724", + "109225", + "109576", + "110016", + "110531", + "111258", + "111882", + "112450", + "113135", + "113958", + "114395", + "114980", + "115698", + "116256", + "116929", + "117539", + "118200", + "118858", + "119400", + "119990", + "120645", + "121097", + "121708", + "122106", + "123100", + "123400", + "123958", + "124279", + "124943", + "125407", + "125997", + "126346", + "127000", + "127484", + "127969", + "128730", + "129700", + "130215", + "131138", + "131861", + "132744", + "133300", + "133859", + "134419", + "135603", + "136697", + "137265", + "137994", + "138903", + "139672", + "140459", + "141468", + "142302", + "143072", + "144158", + "145150", + "146086", + "147000", + "147744", + "148583", + "149702", + "150624", + "151758", + "152983", + "154297", + "155244", + "156468", + "158000", + "159103", + "160639", + "161910", + "163351", + "164463", + "165583", + "167051", + "168702", + "169930", + "171056", + "172357", + "173600", + "174500", + "175661", + "177522", + "178748", + "179818", + "181339", + "182639", + "183500", + "184859", + "186000", + "187691", + "189036", + "190123", + "192247", + "194000", + "195346", + "196519", + "198183", + "200171", + "201568", + "203296", + "205560", + "207100", + "209038", + "211700", + "214294", + "215587", + "217804", + "219773", + "222030", + "223891", + "226509", + "229060", + "231724", + "235073", + "237713", + "240900", + "242820", + "244974", + "247452", + "251348", + "254350", + "256400", + "260512", + "263247", + "265450", + "269600", + "272058", + "275807", + "278317", + "282070", + "286000", + "289376", + "293105", + "297000", + "300500", + "303447", + "308571", + "312927", + "316438", + "320095", + "325000", + "329850", + "333200", + "337977", + "342200", + "348070", + "353046", + "357552", + "361958", + "365382", + "371601", + "379520", + "384000", + "391170", + "399175", + "407018", + "416289", + "420000", + "427652", + "434374", + "441968", + "450288", + "459884", + "469000", + "475657", + "482300", + "490524", + "508000", + "518282", + "529093", + "540330", + "563210", + "577352", + "593321", + "610000", + "634065", + "656562", + "674100", + "703000", + "730000", + "762000", + "792858", + "823301", + "874000", + "935361", + "989975", + "1040000", + "1096829", + "1151274", + "1217359", + "1297526", + "1378087", + "1503451", + "1674000", + "1968400", + "2139125", + "2559424", + "2940623", + "4017733", + "6464693", + "10500000" ] } set histogram_type=@SINGLE_PREC_TYPE; @@ -1947,7 +1949,7 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a 1 3 0.0000 1.0000 10 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2231222C202231222C202231222C202232222C202232222C202232222C202232222C202233222C202233222C202233225D0A7D +test t1 a 1 3 0.0000 1.0000 10 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2231222C202231222C202231222C202231222C202232222C202232222C202232222C202233222C202233222C202233222C202233225D0A7D set histogram_size=default; drop table t1; # @@ -1972,7 +1974,7 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a 1 5 0.0000 1.0000 10 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2231222C202231222C202232222C202232222C202233222C202233222C202234222C202234222C202235222C202235225D0A7D +test t1 a 1 5 0.0000 1.0000 10 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2231222C202231222C202232222C202232222C202233222C202233222C202234222C202234222C202235222C202235222C202235225D0A7D set histogram_size=0; set histogram_type=@SINGLE_PREC_TYPE; drop table t1; @@ -2013,7 +2015,7 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t2 id 1 1024 0.0000 8.0000 63 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A20202020223137222C0A20202020223333222C0A20202020223439222C0A20202020223635222C0A20202020223831222C0A20202020223937222C0A2020202022313133222C0A2020202022313239222C0A2020202022313435222C0A2020202022313631222C0A2020202022313737222C0A2020202022313933222C0A2020202022323039222C0A2020202022323235222C0A2020202022323431222C0A2020202022323537222C0A2020202022323733222C0A2020202022323839222C0A2020202022333035222C0A2020202022333231222C0A2020202022333337222C0A2020202022333533222C0A2020202022333639222C0A2020202022333835222C0A2020202022343031222C0A2020202022343137222C0A2020202022343333222C0A2020202022343439222C0A2020202022343635222C0A2020202022343831222C0A2020202022343937222C0A2020202022353133222C0A2020202022353239222C0A2020202022353435222C0A2020202022353631222C0A2020202022353737222C0A2020202022353933222C0A2020202022363039222C0A2020202022363235222C0A2020202022363431222C0A2020202022363537222C0A20202020223637 33222C0A2020202022363839222C0A2020202022373035222C0A2020202022373231222C0A2020202022373337222C0A2020202022373533222C0A2020202022373639222C0A2020202022373835222C0A2020202022383031222C0A2020202022383137222C0A2020202022383333222C0A2020202022383439222C0A2020202022383635222C0A2020202022383831222C0A2020202022383937222C0A2020202022393133222C0A2020202022393239222C0A2020202022393435222C0A2020202022393631222C0A2020202022393737222C0A2020202022393933222C0A202020202231303039220A20205D0A7D +test t2 id 1 1024 0.0000 8.0000 63 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202231222C0A20202020223137222C0A20202020223333222C0A20202020223439222C0A20202020223636222C0A20202020223832222C0A20202020223938222C0A2020202022313134222C0A2020202022313331222C0A2020202022313437222C0A2020202022313633222C0A2020202022313739222C0A2020202022313936222C0A2020202022323132222C0A2020202022323238222C0A2020202022323434222C0A2020202022323631222C0A2020202022323737222C0A2020202022323933222C0A2020202022333039222C0A2020202022333236222C0A2020202022333432222C0A2020202022333538222C0A2020202022333734222C0A2020202022333931222C0A2020202022343037222C0A2020202022343233222C0A2020202022343339222C0A2020202022343536222C0A2020202022343732222C0A2020202022343838222C0A2020202022353034222C0A2020202022353231222C0A2020202022353337222C0A2020202022353533222C0A2020202022353639222C0A2020202022353836222C0A2020202022363032222C0A2020202022363138222C0A2020202022363334222C0A2020202022363531222C0A202020202236363722 2C0A2020202022363833222C0A2020202022363939222C0A2020202022373136222C0A2020202022373332222C0A2020202022373438222C0A2020202022373634222C0A2020202022373831222C0A2020202022373937222C0A2020202022383133222C0A2020202022383239222C0A2020202022383436222C0A2020202022383632222C0A2020202022383738222C0A2020202022383934222C0A2020202022393131222C0A2020202022393237222C0A2020202022393433222C0A2020202022393539222C0A2020202022393736222C0A2020202022393932222C0A202020202231303038222C0A202020202231303234220A20205D0A7D set histogram_size=0; drop table t1, t2; set use_stat_tables=@save_use_stat_tables; @@ -2175,16 +2177,17 @@ from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) t1 id 1 17384 0.0000 4.0000 14.0000 { "histogram_hb_v1": [ - "1490", - "2979", - "4469", - "5958", - "7448", - "9937", - "11427", - "12916", - "14406", - "15895" + "1", + "1639", + "3277", + "4916", + "6554", + "9193", + "10831", + "12469", + "14108", + "15746", + "17384" ] } set analyze_sample_percentage=0.1; @@ -2201,16 +2204,17 @@ from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) t1 id 111 17026 0.0000 4.0000 10.4739 { "histogram_hb_v1": [ - "832", - "2446", - "3422", - "5411", - "6687", - "9390", - "10738", - "12738", - "14365", - "15411" + "111", + "988", + "2490", + "4088", + "5743", + "7806", + "10217", + "12072", + "14178", + "15144", + "17026" ] } # @@ -2227,16 +2231,17 @@ from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) t1 id 1 17384 0.0000 4.0000 14.0401 { "histogram_hb_v1": [ - "1478", - "2954", - "4441", - "5894", - "7397", - "9888", - "11391", - "12895", - "14370", - "15880" + "1", + "1623", + "3252", + "4868", + "6483", + "8151", + "10789", + "12433", + "14077", + "15724", + "17384" ] } set analyze_sample_percentage=0; @@ -2253,16 +2258,17 @@ from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) t1 id 1 17384 0.0000 4.0000 13.9812 { "histogram_hb_v1": [ - "1500", - "3009", - "4501", - "5997", - "7493", - "9981", - "11456", - "12932", - "14408", - "15903" + "1", + "1651", + "3306", + "4948", + "6596", + "9239", + "10863", + "12495", + "14113", + "15757", + "17384" ] } # @@ -2285,16 +2291,17 @@ from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) t1 id 1 17384 0.0000 4.0000 14.0000 { "histogram_hb_v1": [ - "1490", - "2979", - "4469", - "5958", - "7448", - "9937", - "11427", - "12916", - "14406", - "15895" + "1", + "1639", + "3277", + "4916", + "6554", + "9193", + "10831", + "12469", + "14108", + "15746", + "17384" ] } explain select * from t1; @@ -2439,20 +2446,21 @@ test t1_json a a-0 a-9 0.0000 3.0000 1.0000 100 JSON_HB { "a-9", "a-9", "a-9", + "a-9", "a-9" ] } explain extended select * from t1_json where a between 'a-3a' and 'zzzzzzzzz'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 59.87 Using where +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 60.27 Using where Warnings: Note 1003 select `test`.`t1_json`.`a` AS `a` from `test`.`t1_json` where `test`.`t1_json`.`a` between 'a-3a' and 'zzzzzzzzz' analyze select * from t1_json where a between 'a-3a' and 'zzzzzzzzz'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 10.00 59.87 60.00 Using where +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 10.00 60.27 60.00 Using where explain extended select * from t1_json where a < 'b-1a'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 99.00 Using where +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 99.01 Using where Warnings: Note 1003 select `test`.`t1_json`.`a` AS `a` from `test`.`t1_json` where `test`.`t1_json`.`a` < 'b-1a' analyze select * from t1_json where a > 'zzzzzzzzz'; @@ -2476,12 +2484,12 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t2 where city = 'Moscow'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 101 96.08 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 98.04 Using where Warnings: Note 1003 select `test`.`t2`.`city` AS `city` from `test`.`t2` where `test`.`t2`.`city` = 'Moscow' analyze select * from t2 where city = 'Moscow'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 101 101.00 96.08 98.02 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 101.00 98.04 98.02 Using where explain extended select * from t2 where city = 'Helsinki'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 101 2.00 Using where @@ -2508,386 +2516,394 @@ SELECT column_name, min_value, max_value, hist_size, hist_type, histogram FROM m column_name min_value max_value hist_size hist_type histogram Code ABW ZWE 50 JSON_HB { "histogram_hb_v1": [ + "ABW", "ALB", "ARM", "AUS", - "BEL", + "BEN", "BHR", "BMU", - "BRN", - "CAN", - "CIV", - "COK", - "CUB", - "DEU", - "DOM", - "ESH", - "FJI", - "FSM", - "GIB", - "GNQ", - "GUF", - "HMD", - "IDN", - "IRQ", - "JAM", - "KGZ", - "KWT", - "LBY", - "LTU", - "MCO", - "MEX", - "MMR", - "MSR", - "MYS", - "NFK", - "NOR", - "PAK", - "PHL", - "PRK", - "QAT", - "RWA", - "SGS", - "SLV", - "STP", - "SWZ", - "TGO", + "BTN", + "CCK", + "CMR", + "COL", + "CXR", + "DJI", + "ECU", + "ESP", + "FLK", + "GBR", + "GLP", + "GRD", + "GUM", + "HRV", + "IOT", + "ISR", + "JPN", + "KIR", + "LBN", + "LKA", + "MAC", + "MDG", + "MLI", + "MOZ", + "MWI", + "NCL", + "NIU", + "NZL", + "PER", + "PRI", + "PSE", + "RUS", + "SGP", + "SLE", + "SPM", + "SWE", + "TCD", "TKM", "TUR", - "UKR", + "UGA", "UZB", "VIR", - "YEM" + "YEM", + "ZWE" ] } Name Afghanistan Zimbabwe 50 JSON_HB { "histogram_hb_v1": [ + "Afghanistan", "Andorra", "Argentina", "Azerbaijan", - "Barbados", + "Belarus", "Bermuda", "Bouvet Island", - "Bulgaria", - "Canada", - "Chile", - "Colombia", - "Costa Rica", - "Côte dIvoire", - "Dominican Republic", - "Equatorial Guinea", - "Faroe Islands", - "French Guiana", - "Georgia", - "Greenland", - "Guinea", - "Heard Island and McDonald Islands", - "Iceland", - "Ireland", - "Japan", - "Kuwait", - "Lesotho", - "Lithuania", - "Malawi", - "Marshall Islands", - "Mayotte", - "Mongolia", - "Namibia", - "Netherlands Antilles", - "Nigeria", - "Norway", - "Panama", - "Philippines", - "Qatar", - "Saint Helena", - "Saint Vincent and the Grenadines", - "Senegal", - "Slovenia", - "South Georgia and the South Sandwich Islands", - "Suriname", - "Syria", + "Burkina Faso", + "Cape Verde", + "China", + "Comoros", + "Croatia", + "Denmark", + "Ecuador", + "Eritrea", + "Fiji Islands", + "French Southern territories", + "Ghana", + "Guadeloupe", + "Guinea-Bissau", + "Honduras", + "Indonesia", + "Italy", + "Kazakstan", + "Laos", + "Libyan Arab Jamahiriya", + "Macedonia", + "Mali", + "Mauritania", + "Moldova", + "Mozambique", + "Netherlands", + "Nicaragua", + "North Korea", + "Palau", + "Peru", + "Puerto Rico", + "Rwanda", + "Saint Pierre and Miquelon", + "Saudi Arabia", + "Slovakia", + "South Africa", + "Sudan", + "Switzerland", "Thailand", "Tunisia", - "Uganda", + "Tuvalu", "United States", "Venezuela", - "Western Sahara" + "Western Sahara", + "Zimbabwe" ] } SurfaceArea 0.40 17075400.00 50 JSON_HB { "histogram_hb_v1": [ + "0.40", "14.00", "36.00", "78.00", - "151.00", + "160.00", "200.00", "264.00", - "344.00", + "347.00", "430.00", - "464.00", - "622.00", - "751.00", - "1399.00", - "2510.00", - "4033.00", - "8875.00", - "11000.00", - "14874.00", - "20256.00", - "25713.00", - "28051.00", - "30518.00", - "41526.00", - "48511.00", - "56785.00", - "69700.00", - "78866.00", - "90000.00", - "103000.00", - "111369.00", - "120538.00", - "147181.00", - "181035.00", - "214969.00", - "242900.00", - "274000.00", - "309500.00", - "331689.00", - "390757.00", - "447400.00", - "505992.00", - "581730.00", - "637657.00", - "774815.00", - "912050.00", + "468.00", + "650.00", + "800.00", + "1705.00", + "2831.00", + "5130.00", + "9251.00", + "12173.00", + "17818.00", + "21056.00", + "26338.00", + "28896.00", + "36125.00", + "45227.00", + "51100.00", + "64589.00", + "71740.00", + "86600.00", + "99434.00", + "110861.00", + "117600.00", + "143100.00", + "175016.00", + "199900.00", + "238533.00", + "267668.00", + "301316.00", + "329758.00", + "357022.00", + "446550.00", + "488100.00", + "580367.00", + "622984.00", + "756626.00", + "883749.00", "1098581.00", "1246700.00", - "1648195.00", + "1566500.00", "2149690.00", "2724900.00", - "9363520.00" + "9363520.00", + "17075400.00" ] } Population 0 1277558000 50 JSON_HB { "histogram_hb_v1": [ + "0", "0", "1000", "2500", - "8000", + "11000", "17000", "27000", - "38000", + "43000", "68000", - "78000", - "99000", - "149000", - "190000", - "241000", - "307000", - "428000", - "456000", - "638000", - "885000", - "1295000", - "1726000", - "2153000", - "2662000", - "3101000", - "3520000", - "3850000", - "4023000", - "4629000", - "5074000", - "5398700", - "6188000", - "6782000", - "7733000", - "8329000", - "9586000", - "10239000", - "10730000", - "11385000", - "14786000", - "15942000", - "18886000", - "22244000", - "23115000", - "25662000", - "31471000", + "83000", + "103000", + "154000", + "214000", + "279000", + "328000", + "435700", + "578000", + "754700", + "1158000", + "1305000", + "1987800", + "2441000", + "2856000", + "3282000", + "3615000", + "3869000", + "4473000", + "4854000", + "5171300", + "5605000", + "6485000", + "7651000", + "8190900", + "9169000", + "10097000", + "10640000", + "11234000", + "12646000", + "15864000", + "18827000", + "21778000", + "22720000", + "24318000", + "31147000", "39441700", "50456000", - "61399000", + "59623400", "68470000", "111506000", - "170115000" + "170115000", + "1277558000" ] } Capital 1 4074 50 JSON_HB { "histogram_hb_v1": [ + "1", "35", "63", "129", "150", - "187", + "191", "204", - "538", - "554", - "586", - "652", - "764", - "904", - "917", - "926", - "937", - "1365", - "1464", - "1532", - "1800", - "1864", - "2256", - "2317", - "2409", - "2434", - "2441", - "2454", - "2463", - "2507", - "2514", - "2696", - "2728", - "2754", - "2821", - "2884", - "2914", - "2973", - "3018", - "3064", - "3161", - "3172", - "3208", - "3225", - "3248", - "3315", + "539", + "583", + "587", + "653", + "766", + "905", + "919", + "927", + "938", + "1447", + "1522", + "1786", + "1822", + "1889", + "2295", + "2331", + "2429", + "2438", + "2447", + "2460", + "2484", + "2509", + "2690", + "2710", + "2734", + "2807", + "2882", + "2912", + "2972", + "3017", + "3063", + "3068", + "3171", + "3207", + "3217", + "3244", + "3306", "3334", - "3358", + "3349", "3425", "3499", "3537", - "3791" + "3791", + "4074" ] } ID 1 4079 50 JSON_HB { "histogram_hb_v1": [ - "80", - "160", - "240", - "320", - "400", - "480", - "560", - "640", - "720", - "800", - "880", - "960", - "1040", - "1120", - "1200", - "1280", - "1360", - "1440", - "1520", - "1600", - "1680", - "1760", - "1840", - "1920", - "2000", - "2080", - "2160", - "2240", - "2320", - "2400", - "2480", - "2560", - "2640", - "2720", - "2800", - "2880", - "2960", - "3040", - "3120", - "3200", - "3280", - "3360", - "3440", - "3520", - "3600", - "3680", - "3760", - "3840", - "3920", - "4000" + "1", + "82", + "164", + "245", + "327", + "408", + "490", + "572", + "653", + "735", + "816", + "898", + "979", + "1061", + "1143", + "1224", + "1306", + "1387", + "1469", + "1551", + "1632", + "1714", + "1795", + "1877", + "1958", + "2040", + "2122", + "2203", + "2285", + "2366", + "2448", + "2529", + "2611", + "2693", + "2774", + "2856", + "2937", + "3019", + "3101", + "3182", + "3264", + "3345", + "3427", + "3508", + "3590", + "3672", + "3753", + "3835", + "3916", + "3998", + "4079" ] } Name A Coruña (La Coruña) Ürgenc 50 JSON_HB { "histogram_hb_v1": [ - "Allentown", - "Araguari", - "Bahtim", - "Batangas", - "Bialystok", - "Brampton", - "Calama", - "Changchun", - "Ciomas", - "Cuautla", - "Detroit", - "Effon-Alaiye", - "Firozabad", - "Gebze", - "Guangyuan", - "Hangzhou", - "Hradec Králové", - "Inazawa", - "Jalib al-Shuyukh", - "Jubayl", - "Kassel", - "Kitakyushu", - "Kunshan", - "Le Mans", - "Longueuil", - "Malang", - "Mati", - "Mishan", - "Muroran", - "Natal", - "North York", - "Omsk", - "Palu", - "Phoenix", - "Poznan", - "Quezon", - "Rishra", - "Salem", - "Sancti-Spíritus", - "Sekondi-Takoradi", - "Silao", - "Stoke-on-Trent", - "Taegu", - "Teheran", - "Tomsk", - "Târgu Mures", - "Varginha", - "Weifang", - "Yangjiang", - "Zhaodong" + "A Coruña (La Coruña)", + "Almere", + "Araraquara", + "Bairiki", + "Bawshar", + "Bikaner", + "Brescia", + "Camaragibe", + "Charleroi", + "Ciudad de México", + "Córdoba", + "Dire Dawa", + "Elgin", + "Francisco Morato", + "Gijón", + "Guntur", + "Helsingborg", + "Hunjiang", + "Isesaki", + "Jevpatorija", + "Kalookan", + "Khandwa", + "Korla", + "La Rioja", + "Lilongwe", + "Lübeck", + "Maracanaú", + "Meru", + "Moradabad", + "Nakuru", + "Niihama", + "Ocumare del Tuy", + "Oxnard", + "Penza", + "Porbandar", + "Qarchak", + "Resende", + "Saint Helens", + "San Miguel", + "Saskatoon", + "Shomolu", + "Srinagar", + "São Paulo", + "Taubaté", + "Tokorozawa", + "Terepovets", + "Vanadzor", + "Waru", + "Yamoussoukro", + "Zhangjiang", + "Ürgenc" ] } Country ABW ZWE 50 JSON_HB { "histogram_hb_v1": [ - "ARM", - "BHS", + "ABW", + "ASM", + "BLR", "BRA", "BRA", "BRA", @@ -2896,208 +2912,211 @@ Country ABW ZWE 50 JSON_HB { "CHN", "CHN", "CHN", - "CHN", - "COL", + "CIV", + "CUB", "DEU", - "DZA", + "EGY", "ESP", - "FRA", "GBR", - "IDN", + "GHA", "IDN", "IND", "IND", "IND", "IND", "IRN", - "ITA", + "ISR", "JPN", "JPN", "JPN", + "KAZ", "KOR", - "LKA", + "MDV", "MEX", "MEX", - "MMR", "NGA", - "NZL", - "PER", + "NLD", + "PAK", "PHL", - "POL", - "QAT", + "PHL", + "PRK", "RUS", "RUS", "SAU", - "TCD", + "SYC", "TUR", "UKR", "USA", "USA", "USA", "USA", - "VNM" + "VNM", + "ZWE" ] } Population 42 10500000 50 JSON_HB { "histogram_hb_v1": [ - "50699", - "90601", - "92583", - "94600", - "96800", - "98781", - "100887", - "102681", - "105119", - "107329", - "109500", - "112375", - "115532", - "118718", - "121600", - "124207", - "126820", - "130000", - "133642", - "137776", - "142044", - "146439", - "151060", - "157300", - "163849", - "170428", - "176576", - "183100", - "189569", - "197254", - "206158", - "216735", - "227657", - "241649", - "254842", - "270251", - "286900", - "304477", - "326399", - "349246", - "372840", - "409100", - "443727", - "483155", - "540828", - "636765", - "764902", - "993400", - "1300977", - "2154376" + "42", + "55810", + "90646", + "92686", + "94784", + "96921", + "99155", + "101046", + "103104", + "105643", + "107761", + "110000", + "113126", + "116278", + "119503", + "122400", + "125070", + "127634", + "131480", + "135000", + "139283", + "143922", + "148362", + "154125", + "160510", + "167051", + "173627", + "179956", + "186332", + "194265", + "202134", + "212977", + "222859", + "237206", + "249263", + "265200", + "281928", + "300148", + "320296", + "342584", + "366549", + "401281", + "438638", + "479884", + "531800", + "623457", + "753778", + "977856", + "1266461", + "2130359", + "10500000" ] } Country ABW ZWE 50 JSON_HB { "histogram_hb_v1": [ + "ABW", "ALB", - "ASM", + "ATG", "AZE", "BFA", - "BHS", - "BRA", - "CAF", - "CHE", - "CIV", - "COD", - "CPV", - "CZE", - "DOM", - "EST", - "FRA", - "GBR", - "GIN", - "GRC", - "HKG", + "BIH", + "BRB", + "CAN", + "CHL", + "CMR", + "COG", + "CRI", + "DEU", + "ECU", + "ETH", + "FRO", + "GEO", + "GMB", + "GTM", + "HND", "IDN", - "IND", - "ISL", - "JPN", - "KGZ", - "LAO", - "LIE", - "LVA", - "MDA", - "MLI", - "MNG", - "MRT", - "MYS", - "NER", - "NIU", - "NRU", - "PAN", - "PLW", - "PYF", + "IRN", + "ITA", + "KAZ", + "KHM", + "LBR", + "LTU", + "MAR", + "MHL", + "MMR", + "MOZ", + "MUS", + "NAM", + "NGA", + "NPL", + "PAK", + "PHL", + "PRY", "RUS", "SDN", "SLE", - "SVN", - "TCD", + "SVK", + "TCA", "THA", - "TUR", + "TUN", "TZA", "UKR", "UZB", "VNM", - "ZAF" + "ZAF", + "ZWE" ] } Language Abhyasi [South]Mande 50 JSON_HB { "histogram_hb_v1": [ + "Abhyasi", "Amhara", "Arabic", - "Araucan", - "Bakhtyari", + "Arawakan", + "Balante", "Belorussian", - "Bubi", - "Cebuano", + "Bulgariana", + "Chakma", "Chinese", - "Comorian", + "Comorian-madagassi", "Creole French", - "Danish", - "Embera", + "Dinka", "English", "English", "English", + "Ewe", "French", - "French", - "Futuna", + "Ful", + "Garifuna", "German", - "Greek", - "Hakka", - "Hui", + "Gujarati", + "Herero", + "Hungarian", "Italian", - "Joruba", - "Kazakh", - "Kongo", - "Kurdish", - "Luchazi", - "Makua", - "Malinke", - "Marshallese", - "Mixed Languages", - "Nauru", - "Nung", - "Pangasinan", - "Polish", + "Kanuri", + "Khoekhoe", + "Kosrean", + "Lithuanian", + "Macedonian", + "Malay-English", + "Maori", + "Meru", + "Mossi", + "Norwegian", + "Ouaddai", + "Pilipino", "Portuguese", - "Romanian", + "Romani", "Russian", - "Sara", - "Shona", - "Songhai", + "San", + "Shambala", + "Somali", "Spanish", "Spanish", - "Tamashek", - "Thai", - "Tswana", - "Tuvalu", - "Urdu", - "Wolea" + "Tagalog", + "Teso", + "Tswa", + "Turkmenian", + "Ukrainian and Russian", + "Wolea", + "[South]Mande" ] } Percentage 0.0 99.9 50 JSON_HB { @@ -3105,6 +3124,7 @@ Percentage 0.0 99.9 50 JSON_HB { "0.0", "0.0", "0.0", + "0.0", "0.1", "0.2", "0.3", @@ -3112,46 +3132,46 @@ Percentage 0.0 99.9 50 JSON_HB { "0.5", "0.6", "0.7", - "0.8", + "0.9", "1.1", "1.3", - "1.4", - "1.6", - "1.8", - "2.1", - "2.3", - "2.5", - "2.9", - "3.2", - "3.5", - "3.8", - "4.4", - "4.9", - "5.5", - "5.9", - "6.6", - "7.4", - "7.8", - "8.6", - "9.2", - "10.3", - "11.5", - "12.9", - "14.6", - "17.1", - "20.3", - "23.8", - "31.8", - "39.4", - "47.5", - "55.1", - "66.5", - "77.2", - "86.0", - "89.9", - "94.3", - "97.3", - "99.0" + "1.5", + "1.7", + "1.9", + "2.2", + "2.4", + "2.7", + "3.0", + "3.3", + "3.7", + "4.1", + "4.7", + "5.3", + "5.7", + "6.4", + "7.0", + "7.6", + "8.2", + "9.0", + "9.7", + "11.0", + "12.4", + "14.0", + "16.5", + "19.7", + "23.2", + "31.0", + "36.4", + "45.7", + "52.6", + "65.3", + "76.7", + "85.6", + "89.7", + "94.1", + "97.2", + "99.0", + "99.9" ] } explain extended select * from Country where 'Code' between 'BBC' and 'GGG'; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index 37cb18d10e7..e6988150304 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -1363,7 +1363,8 @@ bool Histogram_json_hb::parse(MEM_ROOT *mem_root, Field *field, break; } } - size= histogram_bounds.size(); + // n_buckets = n_bounds - 1 : + size= histogram_bounds.size()-1; DBUG_RETURN(false); error: @@ -1852,6 +1853,45 @@ class Stat_table_write_iter } }; + +/* + This is used to collect the the basic statistics from a Unique object: + - count of values + - count of distinct values + - count of distinct values that have occurred only once +*/ + +class Basic_stats_collector +{ + ulonglong count; /* number of values retrieved */ + ulonglong count_distinct; /* number of distinct values retrieved */ + /* number of distinct values that occured only once */ + ulonglong count_distinct_single_occurence; + +public: + Basic_stats_collector() + { + count= 0; + count_distinct= 0; + count_distinct_single_occurence= 0; + } + + ulonglong get_count_distinct() const { return count_distinct; } + ulonglong get_count_single_occurence() const + { + return count_distinct_single_occurence; + } + ulonglong get_count() const { return count; } + + void next(void *elem, element_count elem_cnt) + { + count_distinct++; + if (elem_cnt == 1) + count_distinct_single_occurence++; + count+= elem_cnt; + } +}; + /* Histogram_builder is a helper class that is used to build histograms for columns. @@ -1865,87 +1905,95 @@ class Histogram_builder Field *column; /* table field for which the histogram is built */ uint col_length; /* size of this field */ ha_rows records; /* number of records the histogram is built for */ + + Histogram_builder(Field *col, uint col_len, ha_rows rows) : + column(col), col_length(col_len), records(rows) + {} + +public: + // A histogram builder will also collect the counters + Basic_stats_collector counters; + + virtual int next(void *elem, element_count elem_cnt)=0; + virtual void finalize()=0; + virtual ~Histogram_builder(){} +}; + + +class Histogram_binary_builder : public Histogram_builder +{ Field *min_value; /* pointer to the minimal value for the field */ Field *max_value; /* pointer to the maximal value for the field */ - Histogram_base *histogram; /* the histogram location */ + Histogram_binary *histogram; /* the histogram location */ uint hist_width; /* the number of points in the histogram */ double bucket_capacity; /* number of rows in a bucket of the histogram */ uint curr_bucket; /* number of the current bucket to be built */ - ulonglong count; /* number of values retrieved */ - ulonglong count_distinct; /* number of distinct values retrieved */ - /* number of distinct values that occured only once */ - ulonglong count_distinct_single_occurence; public: - Histogram_builder(Field *col, uint col_len, ha_rows rows) - : column(col), col_length(col_len), records(rows) + Histogram_binary_builder(Field *col, uint col_len, ha_rows rows) + : Histogram_builder(col, col_len, rows) { Column_statistics *col_stats= col->collected_stats; min_value= col_stats->min_value; max_value= col_stats->max_value; - histogram= col_stats->histogram; + histogram= (Histogram_binary*)col_stats->histogram; hist_width= histogram->get_width(); bucket_capacity= (double) records / (hist_width + 1); curr_bucket= 0; - count= 0; - count_distinct= 0; - count_distinct_single_occurence= 0; } - Histogram_builder() = default; - - virtual ~Histogram_builder() = default; - - ulonglong get_count_distinct() const { return count_distinct; } - ulonglong get_count_single_occurence() const - { - return count_distinct_single_occurence; - } - - virtual int next(void *elem, element_count elem_cnt) + int next(void *elem, element_count elem_cnt) override { - count_distinct++; - if (elem_cnt == 1) - count_distinct_single_occurence++; - count+= elem_cnt; + counters.next(elem, elem_cnt); + ulonglong count= counters.get_count(); if (curr_bucket == hist_width) return 0; if (count > bucket_capacity * (curr_bucket + 1)) { column->store_field_value((uchar *) elem, col_length); - ((Histogram_binary *)histogram)->set_value(curr_bucket, + histogram->set_value(curr_bucket, column->pos_in_interval(min_value, max_value)); curr_bucket++; while (curr_bucket != hist_width && count > bucket_capacity * (curr_bucket + 1)) { - ((Histogram_binary *)histogram)->set_prev_value(curr_bucket); + histogram->set_prev_value(curr_bucket); curr_bucket++; } } return 0; } - virtual void finalize(){} + void finalize() override {} }; Histogram_builder *Histogram_binary::create_builder(Field *col, uint col_len, ha_rows rows) { - return new Histogram_builder(col, col_len, rows); + return new Histogram_binary_builder(col, col_len, rows); } -class Histogram_builder_json : public Histogram_builder +class Histogram_json_builder : public Histogram_builder { - std::vector<std::string> bucket_bounds; - bool got_first_value = false; + Histogram_json_hb *histogram; + uint hist_width; /* the number of points in the histogram */ + double bucket_capacity; /* number of rows in a bucket of the histogram */ + uint curr_bucket; /* number of the current bucket to be built */ + std::vector<std::string> bucket_bounds; + bool first_value= true; public: - Histogram_builder_json(Field *col, uint col_len, ha_rows rows) - : Histogram_builder(col, col_len, rows) {} + Histogram_json_builder(Field *col, uint col_len, ha_rows rows) + : Histogram_builder(col, col_len, rows) + { + histogram= (Histogram_json_hb*)col->collected_stats->histogram; + bucket_capacity= (double)records / histogram->get_width(); + hist_width= histogram->get_width(); + curr_bucket= 0; + } - ~Histogram_builder_json() override = default; + ~Histogram_json_builder() override = default; /* Add data to the histogram. Adding Element elem which encountered elem_cnt @@ -1953,18 +2001,27 @@ class Histogram_builder_json : public Histogram_builder */ int next(void *elem, element_count elem_cnt) override { - count_distinct++; - if (elem_cnt == 1) - count_distinct_single_occurence++; - count+= elem_cnt; + counters.next(elem, elem_cnt); + ulonglong count= counters.get_count(); + if (curr_bucket == hist_width) return 0; - if (count > bucket_capacity * (curr_bucket + 1)) + if (first_value) { + first_value= false; column->store_field_value((uchar*) elem, col_length); StringBuffer<MAX_FIELD_WIDTH> val; column->val_str(&val); bucket_bounds.push_back(std::string(val.ptr(), val.length())); + } + + if (count > bucket_capacity * (curr_bucket + 1)) + { + column->store_field_value((uchar*) elem, col_length); + StringBuffer<MAX_FIELD_WIDTH> val; + column->val_str(&val); + bucket_bounds.emplace_back(val.ptr(), val.length()); + curr_bucket++; while (curr_bucket != hist_width && count > bucket_capacity * (curr_bucket + 1)) @@ -1973,6 +2030,14 @@ class Histogram_builder_json : public Histogram_builder curr_bucket++; } } + + if (records == count && bucket_bounds.size() == hist_width) + { + column->store_field_value((uchar*) elem, col_length); + StringBuffer<MAX_FIELD_WIDTH> val; + column->val_str(&val); + bucket_bounds.push_back(std::string(val.ptr(), val.length())); + } return 0; } @@ -1991,8 +2056,8 @@ class Histogram_builder_json : public Histogram_builder writer.end_array(); writer.end_object(); Binary_string *json_string = (Binary_string *) writer.output.get_string(); - Histogram_json_hb *hist= (Histogram_json_hb*)histogram; - hist->set_json_text(bucket_bounds.size(), (uchar *) json_string->c_ptr()); + histogram->set_json_text(bucket_bounds.size()-1, + (uchar *) json_string->c_ptr()); } }; @@ -2000,12 +2065,10 @@ class Histogram_builder_json : public Histogram_builder Histogram_builder *Histogram_json_hb::create_builder(Field *col, uint col_len, ha_rows rows) { - return new Histogram_builder_json(col, col_len, rows); + return new Histogram_json_builder(col, col_len, rows); } - - Histogram_base *create_histogram(MEM_ROOT *mem_root, Histogram_type hist_type, THD *owner) { @@ -2036,13 +2099,10 @@ static int histogram_build_walk(void *elem, element_count elem_cnt, void *arg) return hist_builder->next(elem, elem_cnt); } - -static int count_distinct_single_occurence_walk(void *elem, - element_count count, void *arg) +int basic_stats_collector_walk(void *elem, element_count count, + void *arg) { - ((ulonglong*)arg)[0]+= 1; - if (count == 1) - ((ulonglong*)arg)[1]+= 1; + ((Basic_stats_collector*)arg)->next(elem, count); return 0; } @@ -2127,11 +2187,11 @@ class Count_distinct_field: public Sql_alloc */ void walk_tree() { - ulonglong counts[2] = {0, 0}; - tree->walk(table_field->table, - count_distinct_single_occurence_walk, counts); - distincts= counts[0]; - distincts_single_occurence= counts[1]; + Basic_stats_collector stats_collector; + tree->walk(table_field->table, basic_stats_collector_walk, + (void*)&stats_collector ); + distincts= stats_collector.get_count_distinct(); + distincts_single_occurence= stats_collector.get_count_single_occurence(); } /* @@ -2147,8 +2207,9 @@ class Count_distinct_field: public Sql_alloc tree->walk(table_field->table, histogram_build_walk, (void *) hist_builder); hist_builder->finalize(); - distincts= hist_builder->get_count_distinct(); - distincts_single_occurence= hist_builder->get_count_single_occurence(); + distincts= hist_builder->counters.get_count_distinct(); + distincts_single_occurence= hist_builder->counters. + get_count_single_occurence(); delete hist_builder; } diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 0551d24f1c2..0b1b310941f 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -403,7 +403,8 @@ class Histogram_json_hb : public Histogram_base return size; } - void init_for_collection(MEM_ROOT *mem_root, Histogram_type htype_arg, ulonglong size) override; + void init_for_collection(MEM_ROOT *mem_root, Histogram_type htype_arg, + ulonglong size) override; bool is_available() override {return true; }

1 0

[Commits] 3ecc26029fc: Fix statistics_upgrade.test
by psergey 31 Aug '21

31 Aug '21

revision-id: 3ecc26029fc95b566c6267a934aa0f1c432f3069 (mariadb-10.6.1-112-g3ecc26029fc) parent(s): cf4f26f103729c6d95ddbd8e96e0ad666a941c44 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-08-31 23:58:03 +0300 message: Fix statistics_upgrade.test --- mysql-test/main/statistics_upgrade.result | 1 - mysql-test/main/statistics_upgrade.test | 1 - 2 files changed, 2 deletions(-) diff --git a/mysql-test/main/statistics_upgrade.result b/mysql-test/main/statistics_upgrade.result index 323a5132b05..5d4cb3323f8 100644 --- a/mysql-test/main/statistics_upgrade.result +++ b/mysql-test/main/statistics_upgrade.result @@ -33,7 +33,6 @@ insert into t4 select * from t3; drop table mysql.column_stats; alter table t3 rename mysql.column_stats; # Run mysql_upgrade -/optane/dev-git2/10.7-gsoc/client/mysql_upgrade --defaults-file=/optane/dev-git2/10.7-gsoc/mysql-test/var/my.cnf mysql.innodb_index_stats Error : Unknown storage engine 'InnoDB' error : Corrupt diff --git a/mysql-test/main/statistics_upgrade.test b/mysql-test/main/statistics_upgrade.test index 4d80518a171..705cf65e053 100644 --- a/mysql-test/main/statistics_upgrade.test +++ b/mysql-test/main/statistics_upgrade.test @@ -41,7 +41,6 @@ drop table mysql.column_stats; alter table t3 rename mysql.column_stats; --echo # Run mysql_upgrade ---echo $MYSQL_UPGRADE --exec $MYSQL_UPGRADE --upgrade-system-tables --force --silent 2>&1 let $MYSQLD_DATADIR= `select @@datadir`;

1 0

[Commits] cf4f26f1037: Handle upgrades
by psergey 31 Aug '21

31 Aug '21

revision-id: cf4f26f103729c6d95ddbd8e96e0ad666a941c44 (mariadb-10.6.1-111-gcf4f26f1037) parent(s): 1bd845eb1e54635f203c1e9f0638650b07a7771a author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-08-31 17:46:07 +0300 message: Handle upgrades --- mysql-test/main/statistics_upgrade.result | 88 +++++++++++++++++++++++++++++++ mysql-test/main/statistics_upgrade.test | 68 ++++++++++++++++++++++++ scripts/mysql_system_tables_fix.sql | 8 +++ 3 files changed, 164 insertions(+) diff --git a/mysql-test/main/statistics_upgrade.result b/mysql-test/main/statistics_upgrade.result new file mode 100644 index 00000000000..323a5132b05 --- /dev/null +++ b/mysql-test/main/statistics_upgrade.result @@ -0,0 +1,88 @@ +call mtr.add_suppression("Incorrect definition of table mysql.column_stats:.*"); +set histogram_type=single_prec_hb; +create table t1 (a int); +insert into t1 select seq from seq_1_to_100; +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 histogram_type=double_prec_hb, histogram_size=20; +create table t2 (a int); +insert into t2 select seq from seq_1_to_100; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +CREATE TABLE IF NOT EXISTS t3 ( +db_name varchar(64) NOT NULL, +table_name varchar(64) NOT NULL, +column_name varchar(64) NOT NULL, +min_value varbinary(255) DEFAULT NULL, +max_value varbinary(255) DEFAULT NULL, +nulls_ratio decimal(12,4) DEFAULT NULL, +avg_length decimal(12,4) DEFAULT NULL, +avg_frequency decimal(12,4) DEFAULT NULL, +hist_size tinyint unsigned, +hist_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB'), +histogram varbinary(255), +PRIMARY KEY (db_name,table_name,column_name) +) engine=Aria transactional=0 CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; +insert into t3 select * from mysql.column_stats; +create table t4 like t3; +insert into t4 select * from t3; +drop table mysql.column_stats; +alter table t3 rename mysql.column_stats; +# Run mysql_upgrade +/optane/dev-git2/10.7-gsoc/client/mysql_upgrade --defaults-file=/optane/dev-git2/10.7-gsoc/mysql-test/var/my.cnf +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.transaction_registry +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.transaction_registry +Error : Unknown storage engine 'InnoDB' +error : Corrupt +# +# Table definition after upgrade: +# +show create table mysql.column_stats; +Table Create Table +column_stats CREATE TABLE `column_stats` ( + `db_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, + `nulls_ratio` decimal(12,4) DEFAULT NULL, + `avg_length` decimal(12,4) DEFAULT NULL, + `avg_frequency` decimal(12,4) DEFAULT NULL, + `hist_size` tinyint(3) unsigned DEFAULT NULL, + `hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB') COLLATE utf8mb3_bin DEFAULT NULL, + `histogram` blob DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`column_name`) +) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='Statistics on Columns' +select +A.db_name, +A.table_name, +A.hist_type = B.hist_type as hist_type_equal, +A.histogram = B.histogram as histogram_equal +from +t4 A +left join +mysql.column_stats B on A.db_name=B.db_name and +A.table_name=B.table_name and +A.column_name=B.column_name; +db_name table_name hist_type_equal histogram_equal +test t1 1 1 +test t2 1 1 +drop table t1,t2,t4; diff --git a/mysql-test/main/statistics_upgrade.test b/mysql-test/main/statistics_upgrade.test new file mode 100644 index 00000000000..4d80518a171 --- /dev/null +++ b/mysql-test/main/statistics_upgrade.test @@ -0,0 +1,68 @@ +-- source include/mysql_upgrade_preparation.inc +--source include/have_sequence.inc + +call mtr.add_suppression("Incorrect definition of table mysql.column_stats:.*"); + +set histogram_type=single_prec_hb; +create table t1 (a int); +insert into t1 select seq from seq_1_to_100; +analyze table t1 persistent for all; + +set histogram_type=double_prec_hb, histogram_size=20; +create table t2 (a int); +insert into t2 select seq from seq_1_to_100; +analyze table t2 persistent for all; + +# Create a table with old definition +CREATE TABLE IF NOT EXISTS t3 ( + db_name varchar(64) NOT NULL, + table_name varchar(64) NOT NULL, + column_name varchar(64) NOT NULL, + min_value varbinary(255) DEFAULT NULL, + max_value varbinary(255) DEFAULT NULL, + nulls_ratio decimal(12,4) DEFAULT NULL, + avg_length decimal(12,4) DEFAULT NULL, + avg_frequency decimal(12,4) DEFAULT NULL, + hist_size tinyint unsigned, + hist_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB'), + histogram varbinary(255), + PRIMARY KEY (db_name,table_name,column_name) +) engine=Aria transactional=0 CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; + +# Copy the data there +insert into t3 select * from mysql.column_stats; + +create table t4 like t3; +insert into t4 select * from t3; + +# Put the old table in place of mysql.column_stats + +drop table mysql.column_stats; +alter table t3 rename mysql.column_stats; +--echo # Run mysql_upgrade + +--echo $MYSQL_UPGRADE +--exec $MYSQL_UPGRADE --upgrade-system-tables --force --silent 2>&1 + +let $MYSQLD_DATADIR= `select @@datadir`; +--file_exists $MYSQLD_DATADIR/mysql_upgrade_info +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--echo # +--echo # Table definition after upgrade: +--echo # +show create table mysql.column_stats; + +select + A.db_name, + A.table_name, + A.hist_type = B.hist_type as hist_type_equal, + A.histogram = B.histogram as histogram_equal +from + t4 A + left join + mysql.column_stats B on A.db_name=B.db_name and + A.table_name=B.table_name and + A.column_name=B.column_name; + +drop table t1,t2,t4; diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index ed89cc1fd6c..410e1db2670 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -830,3 +830,11 @@ IF 1 = (SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def END IF// DELIMITER ; + +-- +-- Upgrade mysql.column_stats table +-- + +ALTER TABLE column_stats + modify hist_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), + modify histogram blob;

1 0