Changed the type of all double columns in the system statistical tables

mysql.column_stat, mysql.table_stat for the type DECIMAL(12,4).
When cached the values from these columns are multiplied by factor 10^5
and stored as ulong numbers now. 

1 files changed, 32 insertions, 48 deletions

diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result
index 4e1a5a6733c..99d3c04312b 100644
--- a/mysql-test/r/statistics.result
+++ b/mysql-test/r/statistics.result
@@ -1,18 +1,5 @@
 drop table if exists t1,t2;
 set @save_use_stat_tables=@@use_stat_tables;
-CREATE VIEW table_stat AS
-SELECT * FROM mysql.table_stat;
-CREATE VIEW column_stat AS
-SELECT db_name, table_name, column_name,
-min_value, max_value,
-CAST(nulls_ratio AS decimal(12,4)) AS 'nulls_ratio',
-CAST(avg_length AS decimal(12,4)) AS 'avg_length',
-CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency'
-    FROM mysql.column_stat;
-CREATE VIEW index_stat AS
-SELECT db_name, table_name, index_name, prefix_arity,
-CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency'
-    FROM mysql.index_stat;
 DELETE FROM mysql.table_stat;
 DELETE FROM mysql.column_stat;
 DELETE FROM mysql.index_stat;
@@ -73,10 +60,10 @@ INSERT INTO t1 VALUES
 ANALYZE TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
-SELECT * FROM table_stat;
+SELECT * FROM mysql.table_stat;
 db_name	table_name	cardinality
 test	t1	40
-SELECT * FROM column_stat;
+SELECT * FROM mysql.column_stat;
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	a	0	49	0.0000	4.0000	1.0000
 test	t1	b	vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	17.1250	6.4000
@@ -84,7 +71,7 @@ test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
 test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
 test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
 test	t1	f	1	5	0.2000	1.0000	6.4000
-SELECT * FROM index_stat;
+SELECT * FROM mysql.index_stat;
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	PRIMARY	1	1.0000
 test	t1	idx1	1	6.4000
@@ -98,7 +85,7 @@ test	t1	idx4	3	1.1304
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 40
-SELECT * FROM column_stat
+SELECT * FROM mysql.column_stat
 WHERE db_name='test' AND table_name='t1' AND column_name='a';
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	a	0	49	0.0000	4.0000	1.0000
@@ -110,7 +97,7 @@ SELECT MIN(t1.a), MAX(t1.a),
 FROM t1;
 MIN(t1.a)	MAX(t1.a)	NULLS_RATIO(t1.a)	AVG_FREQUENCY(t1.a)
 0	49	0.2000	1.0000
-SELECT * FROM column_stat
+SELECT * FROM mysql.column_stat
 WHERE db_name='test' AND table_name='t1' AND column_name='b';
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	b	vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	17.1250	6.4000
@@ -122,7 +109,7 @@ SELECT MIN(t1.b), MAX(t1.b),
 FROM t1;
 MIN(t1.b)	MAX(t1.b)	NULLS_RATIO(t1.b)	AVG_FREQUENCY(t1.b)
 vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	6.4000
-SELECT * FROM column_stat 
+SELECT * FROM mysql.column_stat 
 WHERE db_name='test' AND table_name='t1' AND column_name='c';
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
@@ -134,7 +121,7 @@ SELECT MIN(t1.c), MAX(t1.c),
 FROM t1;
 MIN(t1.c)	MAX(t1.c)	NULLS_RATIO(t1.c)	AVG_FREQUENCY(t1.c)
 aaaa	dddddddd	0.1250	7.0000
-SELECT * FROM column_stat
+SELECT * FROM mysql.column_stat
 WHERE db_name='test' AND table_name='t1' AND column_name='d';
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
@@ -146,7 +133,7 @@ SELECT MIN(t1.d), MAX(t1.d),
 FROM t1;
 MIN(t1.d)	MAX(t1.d)	NULLS_RATIO(t1.d)	AVG_FREQUENCY(t1.d)
 1989-03-12	1999-07-23	0.1500	8.5000
-SELECT * FROM column_stat
+SELECT * FROM mysql.column_stat
 WHERE db_name='test' AND table_name='t1' AND column_name='e';
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
@@ -158,7 +145,7 @@ SELECT MIN(t1.e), MAX(t1.e),
 FROM t1;
 MIN(t1.e)	MAX(t1.e)	NULLS_RATIO(t1.e)	AVG_FREQUENCY(t1.e)
 0.01	0.112	0.2250	6.2000
-SELECT * FROM index_stat
+SELECT * FROM mysql.index_stat
 WHERE db_name='test' AND table_name='t1' AND index_name='idx1';
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	idx1	1	6.4000
@@ -173,7 +160,7 @@ WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL)
 AS 'ARITY 2';
 ARITY 1	ARITY 2
 6.4000	1.6875
-SELECT * FROM index_stat
+SELECT * FROM mysql.index_stat
 WHERE db_name='test' AND table_name='t1' AND index_name='idx2';
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	idx2	1	7.0000
@@ -188,7 +175,7 @@ WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL)
 AS 'ARITY 2';
 ARITY 1	ARITY 2
 7.0000	2.3846
-SELECT * FROM index_stat
+SELECT * FROM mysql.index_stat
 WHERE db_name='test' AND table_name='t1' AND index_name='idx3';
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	idx3	1	8.5000
@@ -198,7 +185,7 @@ SELECT
 AS 'ARITY 1';
 ARITY 1
 8.5000
-SELECT * FROM index_stat
+SELECT * FROM mysql.index_stat
 WHERE db_name='test' AND table_name='t1' AND index_name='idx4';
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	idx4	1	6.2000
@@ -225,25 +212,25 @@ DELETE FROM mysql.index_stat;
 ANALYZE TABLE t1 PERSISTENT FOR COLUMNS() INDEXES();
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
-SELECT * FROM table_stat;
+SELECT * FROM mysql.table_stat;
 db_name	table_name	cardinality
 test	t1	40
-SELECT * FROM column_stat;
+SELECT * FROM mysql.column_stat;
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
-SELECT * FROM index_stat;
+SELECT * FROM mysql.index_stat;
 db_name	table_name	index_name	prefix_arity	avg_frequency
 ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(c,e,b) INDEXES(idx2,idx4);
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
-SELECT * FROM table_stat;
+SELECT * FROM mysql.table_stat;
 db_name	table_name	cardinality
 test	t1	40
-SELECT * FROM column_stat;
+SELECT * FROM mysql.column_stat;
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	b	vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	17.1250	6.4000
 test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
 test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
-SELECT * FROM index_stat;
+SELECT * FROM mysql.index_stat;
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	idx2	1	7.0000
 test	t1	idx2	2	2.3846
@@ -256,10 +243,10 @@ DELETE FROM mysql.index_stat;
 ANALYZE TABLE t1 PERSISTENT FOR COLUMNS ALL INDEXES ALL;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
-SELECT * FROM table_stat;
+SELECT * FROM mysql.table_stat;
 db_name	table_name	cardinality
 test	t1	40
-SELECT * FROM column_stat;
+SELECT * FROM mysql.column_stat;
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	a	0	49	0.0000	4.0000	1.0000
 test	t1	b	vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	17.1250	6.4000
@@ -267,7 +254,7 @@ test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
 test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
 test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
 test	t1	f	1	5	0.2000	1.0000	6.4000
-SELECT * FROM index_stat;
+SELECT * FROM mysql.index_stat;
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	PRIMARY	1	1.0000
 test	t1	idx1	1	6.4000
@@ -284,11 +271,11 @@ INSERT INTO t2 SELECT * FROM t1;
 ANALYZE TABLE t2;
 Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	OK
-SELECT * FROM table_stat;
+SELECT * FROM mysql.table_stat;
 db_name	table_name	cardinality
 test	t1	40
 test	t2	40
-SELECT * FROM column_stat ORDER BY column_name;
+SELECT * FROM mysql.column_stat ORDER BY column_name;
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	a	0	49	0.0000	4.0000	1.0000
 test	t2	a	0	49	0.0000	4.0000	1.0000
@@ -302,7 +289,7 @@ test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
 test	t2	e	0.01	0.112	0.2250	8.0000	6.2000
 test	t1	f	1	5	0.2000	1.0000	6.4000
 test	t2	f	1	5	0.2000	1.0000	6.4000
-SELECT * FROM index_stat ORDER BY index_name, prefix_arity, table_name;
+SELECT * FROM mysql.index_stat ORDER BY index_name, prefix_arity, table_name;
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	PRIMARY	1	1.0000
 test	t2	PRIMARY	1	1.0000
@@ -335,7 +322,7 @@ ADD INDEX idx4 (e, b(4), d);
 ANALYZE TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
-SELECT * FROM column_stat;
+SELECT * FROM mysql.column_stat;
 db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 test	t1	a	0	49	0.0000	4.0000	1.0000
 test	t1	b	NULL	NULL	0.2000	17.1250	NULL
@@ -343,7 +330,7 @@ test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
 test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
 test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
 test	t1	f	1	5	0.2000	1.0000	6.4000
-SELECT * FROM index_stat;
+SELECT * FROM mysql.index_stat;
 db_name	table_name	index_name	prefix_arity	avg_frequency
 test	t1	PRIMARY	1	1.0000
 test	t1	idx2	1	7.0000
@@ -390,14 +377,14 @@ INDEX (Percentage)
 set use_stat_tables='preferably';
 ANALYZE TABLE Country, City, CountryLanguage;
 SELECT UPPER(db_name), UPPER(table_name), cardinality
-FROM test.table_stat;
+FROM mysql.table_stat;
 UPPER(db_name)	UPPER(table_name)	cardinality
 WORLD	COUNTRY	239
 WORLD	CITY	4079
 WORLD	COUNTRYLANGUAGE	984
 SELECT UPPER(db_name), UPPER(table_name), 
 column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
-FROM test.column_stat;
+FROM mysql.column_stat;
 UPPER(db_name)	UPPER(table_name)	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 WORLD	COUNTRY	Code	ABW	ZWE	0.0000	3.0000	1.0000
 WORLD	COUNTRY	Name	Afghanistan	Zimbabwe	0.0000	10.1088	1.0000
@@ -413,7 +400,7 @@ WORLD	COUNTRYLANGUAGE	Language	Abhyasi	[South]Mande	0.0000	7.1778	2.1532
 WORLD	COUNTRYLANGUAGE	Percentage	0.0	99.9	0.0000	4.0000	2.7640
 SELECT UPPER(db_name), UPPER(table_name),
 index_name, prefix_arity, avg_frequency
-FROM test.index_stat;
+FROM mysql.index_stat;
 UPPER(db_name)	UPPER(table_name)	index_name	prefix_arity	avg_frequency
 WORLD	COUNTRY	PRIMARY	1	1.0000
 WORLD	COUNTRY	Name	1	1.0000
@@ -458,7 +445,7 @@ ALTER TABLE CountryLanguage ENGINE=InnoDB;
 set use_stat_tables='preferably';
 ANALYZE TABLE Country, City, CountryLanguage;
 SELECT UPPER(db_name), UPPER(table_name), cardinality
-FROM test.table_stat;
+FROM mysql.table_stat;
 UPPER(db_name)	UPPER(table_name)	cardinality
 WORLD	COUNTRY	239
 WORLD	CITY	4079
@@ -468,7 +455,7 @@ WORLD_INNODB	CITY	4079
 WORLD_INNODB	COUNTRYLANGUAGE	984
 SELECT UPPER(db_name), UPPER(table_name), 
 column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
-FROM test.column_stat;
+FROM mysql.column_stat;
 UPPER(db_name)	UPPER(table_name)	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
 WORLD	COUNTRY	Code	ABW	ZWE	0.0000	3.0000	1.0000
 WORLD	COUNTRY	Name	Afghanistan	Zimbabwe	0.0000	10.1088	1.0000
@@ -496,7 +483,7 @@ WORLD_INNODB	COUNTRYLANGUAGE	Language	Abhyasi	[South]Mande	0.0000	7.1778	2.1532
 WORLD_INNODB	COUNTRYLANGUAGE	Percentage	0.0	99.9	0.0000	4.0000	2.7640
 SELECT UPPER(db_name), UPPER(table_name),
 index_name, prefix_arity, avg_frequency
-FROM test.index_stat;
+FROM mysql.index_stat;
 UPPER(db_name)	UPPER(table_name)	index_name	prefix_arity	avg_frequency
 WORLD	COUNTRY	PRIMARY	1	1.0000
 WORLD	COUNTRY	Name	1	1.0000
@@ -520,7 +507,4 @@ DROP DATABASE world_innodb;
 DELETE FROM mysql.table_stat;
 DELETE FROM mysql.column_stat;
 DELETE FROM mysql.index_stat;
-DROP VIEW test.table_stat;
-DROP VIEW test.column_stat;
-DROP VIEW test.index_stat;
 set use_stat_tables=@save_use_stat_tables;