[Commits] 9f862ce: MDEV#7383: engine-independent-stats column_stats has limited values for max/min values

Sergei Petrunia psergey at askmonty.org
Mon Nov 9 16:58:36 EET 2015


revision-id: 9f862ce026c9102695f01aaa3090d5e99ffd80a1
parent(s): 1694d813997198e86f6a8aa6aefffb687c9992dd
committer: Sergei Petrunia
branch nick: 10.0
timestamp: 2015-11-09 17:58:35 +0300
message:

MDEV#7383: engine-independent-stats column_stats has limited values for max/min values

Patch from Daniel Black:
- Change the charset of mysql.column_stats.{min_value, max_value} from
  utf8_bin varchar to varbinary
- Adjust the code that saves/reads the data accordingly.
- Also provide upgrade statement in mysql_system_tables_fix.sql

---
 mysql-test/r/statistics.result      |   19 +++++++++++++++++++
 mysql-test/t/statistics.test        |   16 ++++++++++++++++
 scripts/mysql_system_tables.sql     |    2 +-
 scripts/mysql_system_tables_fix.sql |    2 ++
 sql/sql_statistics.cc               |   16 ++++++++--------
 5 files changed, 46 insertions(+), 9 deletions(-)

diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result
index a2d3d39..bd6a084 100644
--- a/mysql-test/r/statistics.result
+++ b/mysql-test/r/statistics.result
@@ -1622,3 +1622,22 @@ test	t2	id	1	1024	0.0000	8.0000	63	SINGLE_PREC_HB	03070B0F13171B1F23272B2F33373B
 set histogram_size=default;
 drop table t1, t2;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug MDEV-7383: min/max value for a column not utf8 compatible
+#
+create table t1 (a varchar(100)) engine=MyISAM;
+insert into t1 values(unhex('D879626AF872675F73E662F8'));
+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
+show warnings;
+Level	Code	Message
+select db_name, table_name, column_name,
+HEX(min_value), HEX(max_value),
+nulls_ratio, avg_frequency,
+hist_size, hist_type, HEX(histogram)
+FROM mysql.column_stats;
+db_name	table_name	column_name	HEX(min_value)	HEX(max_value)	nulls_ratio	avg_frequency	hist_size	hist_type	HEX(histogram)
+test	t1	a	D879626AF872675F73E662F8	D879626AF872675F73E662F8	0.0000	1.0000	0	NULL	NULL
+drop table t1;
diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test
index 36e2c5a..2c8dec3 100644
--- a/mysql-test/t/statistics.test
+++ b/mysql-test/t/statistics.test
@@ -701,3 +701,19 @@ drop table t1, t2;
 
 set use_stat_tables=@save_use_stat_tables;
 
+--echo #
+--echo # Bug MDEV-7383: min/max value for a column not utf8 compatible
+--echo #
+
+create table t1 (a varchar(100)) engine=MyISAM;
+insert into t1 values(unhex('D879626AF872675F73E662F8'));
+analyze table t1 persistent for all;
+show warnings;
+
+select db_name, table_name, column_name,
+       HEX(min_value), HEX(max_value),
+       nulls_ratio, avg_frequency,
+       hist_size, hist_type, HEX(histogram)
+  FROM mysql.column_stats;
+
+drop table t1;
diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
index bc89ae8..14b59bb 100644
--- a/scripts/mysql_system_tables.sql
+++ b/scripts/mysql_system_tables.sql
@@ -220,7 +220,7 @@ set @had_proxies_priv_table= @@warning_count != 0;
 
 CREATE TABLE IF NOT EXISTS table_stats (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables';
 
-CREATE TABLE IF NOT EXISTS column_stats (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(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=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns';
+CREATE TABLE IF NOT EXISTS column_stats (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=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns';
 
 CREATE TABLE IF NOT EXISTS index_stats (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency decimal(12,4) DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes';
 
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
index 33b4306..f034af4 100644
--- a/scripts/mysql_system_tables_fix.sql
+++ b/scripts/mysql_system_tables_fix.sql
@@ -711,3 +711,5 @@ flush privileges;
 ALTER TABLE help_category MODIFY url TEXT NOT NULL;
 ALTER TABLE help_topic MODIFY url TEXT NOT NULL;
 
+# MDEV-7383 - varbinary on mix/max of column_stats
+alter table column_stats modify min_value varbinary(255) DEFAULT NULL, modify max_value varbinary(255) DEFAULT NULL;
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index 4ce1f3e..c2150ba 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -888,7 +888,7 @@ class Column_stat: public Stat_table
 
     @note
     A value from the field min_value/max_value is always converted
-    into a utf8 string. If the length of the column 'min_value'/'max_value'
+    into a varbinary string. If the length of the column 'min_value'/'max_value'
     is less than the length of the string the string is trimmed to fit the
     length of the column. 
   */    
@@ -896,7 +896,7 @@ class Column_stat: public Stat_table
   void store_stat_fields()
   {
     char buff[MAX_FIELD_WIDTH];
-    String val(buff, sizeof(buff), &my_charset_utf8_bin);
+    String val(buff, sizeof(buff), &my_charset_bin);
 
     for (uint i= COLUMN_STAT_MIN_VALUE; i <= COLUMN_STAT_HISTOGRAM; i++)
     {  
@@ -913,7 +913,7 @@ class Column_stat: public Stat_table
           else
           {
             table_field->collected_stats->min_value->val_str(&val);
-            stat_field->store(val.ptr(), val.length(), &my_charset_utf8_bin);
+            stat_field->store(val.ptr(), val.length(), &my_charset_bin);
           }
           break;
         case COLUMN_STAT_MAX_VALUE:
@@ -922,7 +922,7 @@ class Column_stat: public Stat_table
           else
           {
             table_field->collected_stats->max_value->val_str(&val);
-            stat_field->store(val.ptr(), val.length(), &my_charset_utf8_bin);
+            stat_field->store(val.ptr(), val.length(), &my_charset_bin);
           }
           break;
         case COLUMN_STAT_NULLS_RATIO:
@@ -983,7 +983,7 @@ class Column_stat: public Stat_table
     if (find_stat())
     {
       char buff[MAX_FIELD_WIDTH];
-      String val(buff, sizeof(buff), &my_charset_utf8_bin);
+      String val(buff, sizeof(buff), &my_charset_bin);
 
       for (uint i= COLUMN_STAT_MIN_VALUE; i <= COLUMN_STAT_HIST_TYPE; i++)
       {  
@@ -1002,12 +1002,12 @@ class Column_stat: public Stat_table
           case COLUMN_STAT_MIN_VALUE:
             stat_field->val_str(&val);
             table_field->read_stats->min_value->store(val.ptr(), val.length(),
-                                                      &my_charset_utf8_bin);
+                                                      &my_charset_bin);
             break;
           case COLUMN_STAT_MAX_VALUE:
             stat_field->val_str(&val);
             table_field->read_stats->max_value->store(val.ptr(), val.length(),
-                                                      &my_charset_utf8_bin);
+                                                      &my_charset_bin);
             break;
           case COLUMN_STAT_NULLS_RATIO:
             table_field->read_stats->set_nulls_ratio(stat_field->val_real());
@@ -1053,7 +1053,7 @@ class Column_stat: public Stat_table
     if (find_stat())
     {
       char buff[MAX_FIELD_WIDTH];
-      String val(buff, sizeof(buff), &my_charset_utf8_bin);
+      String val(buff, sizeof(buff), &my_charset_bin);
       uint fldno= COLUMN_STAT_HISTOGRAM;
       Field *stat_field= stat_table->field[fldno];
       table_field->read_stats->set_not_null(fldno);


More information about the commits mailing list