Fix for bug#11057 information_schema: columns table has some questionable contents

  fixed BLOB, TEXT(wrong maximum length), BIT and integer types(wrong numeric_precision value)

3 files changed, 59 insertions, 6 deletions

diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index a0ad743ac2a..2b84dee18c1 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -153,7 +153,7 @@ c	varchar(64)	utf8_general_ci	NO				select,insert,update,references
 select * from information_schema.COLUMNS where table_name="t1"
 and column_name= "a";
 TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT
-NULL	mysqltest	t1	a	1	NULL	YES	int	NULL	NULL	11	0	NULL	NULL	int(11)			select,insert,update,references	
+NULL	mysqltest	t1	a	1	NULL	YES	int	NULL	NULL	10	NULL	NULL	NULL	int(11)			select,insert,update,references	
 show columns from mysqltest.t1 where field like "%a%";
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
@@ -523,7 +523,7 @@ c	float(5,2)	NULL	NULL	5	2
 d	decimal(6,4)	NULL	NULL	6	4
 e	float	NULL	NULL	12	NULL
 f	decimal(6,3)	NULL	NULL	6	3
-g	int(11)	NULL	NULL	11	0
+g	int(11)	NULL	NULL	10	NULL
 h	double(10,3)	NULL	NULL	10	3
 i	double	NULL	NULL	22	NULL
 drop table t1;
@@ -844,3 +844,26 @@ drop procedure p2;
 show create database information_schema;
 Database	Create Database
 information_schema	CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */
+create table t1(f1 LONGBLOB, f2 LONGTEXT);
+select column_name,data_type,CHARACTER_OCTET_LENGTH,
+CHARACTER_MAXIMUM_LENGTH
+from information_schema.columns
+where table_name='t1';
+column_name	data_type	CHARACTER_OCTET_LENGTH	CHARACTER_MAXIMUM_LENGTH
+f1	longblob	4294967295	4294967295
+f2	longtext	4294967295	4294967295
+drop table t1;
+create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int,
+f5 BIGINT, f6 BIT, f7 bit(64));
+select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
+from information_schema.columns
+where table_name='t1';
+column_name	NUMERIC_PRECISION	NUMERIC_SCALE
+f1	3	NULL
+f2	5	NULL
+f3	7	NULL
+f4	10	NULL
+f5	19	NULL
+f6	1	NULL
+f7	64	NULL
+drop table t1;
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index 0ac68c37d15..44c69e3c143 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -571,3 +571,19 @@ drop procedure p2;
 # Bug #9434 SHOW CREATE DATABASE information_schema;
 #
 show create database information_schema;
+
+#
+# Bug #11057 information_schema: columns table has some questionable contents
+#
+create table t1(f1 LONGBLOB, f2 LONGTEXT);
+select column_name,data_type,CHARACTER_OCTET_LENGTH,
+       CHARACTER_MAXIMUM_LENGTH
+from information_schema.columns
+where table_name='t1';
+drop table t1;
+create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int,
+                f5 BIGINT, f6 BIT, f7 bit(64));
+select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
+from information_schema.columns
+where table_name='t1';
+drop table t1;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 1608999eaef..80e6bbdf388 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2377,6 +2377,7 @@ static int get_schema_column_record(THD *thd, struct st_table_list *tables,
     {
       const char *tmp_buff;
       byte *pos;
+      bool is_blob;
       uint flags=field->flags;
       char tmp[MAX_FIELD_WIDTH];
       char tmp1[MAX_FIELD_WIDTH];
@@ -2455,12 +2456,14 @@ static int get_schema_column_record(THD *thd, struct st_table_list *tables,
                    "NO" : "YES");
       table->field[6]->store((const char*) pos,
                              strlen((const char*) pos), cs);
-      if (field->has_charset())
+      is_blob= (field->type() == FIELD_TYPE_BLOB);
+      if (field->has_charset() || is_blob)
       {
-        table->field[8]->store((longlong) field->field_length/
-                               field->charset()->mbmaxlen);
+        longlong c_octet_len= is_blob ? (longlong) field->max_length() :
+          (longlong) field->max_length()/field->charset()->mbmaxlen;
+        table->field[8]->store(c_octet_len);
         table->field[8]->set_notnull();
-        table->field[9]->store((longlong) field->field_length);
+        table->field[9]->store((longlong) field->max_length());
         table->field[9]->set_notnull();
       }
 
@@ -2488,6 +2491,17 @@ static int get_schema_column_record(THD *thd, struct st_table_list *tables,
         case FIELD_TYPE_LONG:
         case FIELD_TYPE_LONGLONG:
         case FIELD_TYPE_INT24:
+        {
+          table->field[10]->store((longlong) field->max_length() - 1);
+          table->field[10]->set_notnull();
+          break;
+        }
+        case FIELD_TYPE_BIT:
+        {
+          table->field[10]->store((longlong) field->max_length());
+          table->field[10]->set_notnull();
+          break;
+        }
         case FIELD_TYPE_FLOAT:  
         case FIELD_TYPE_DOUBLE:
         {