bk commit into 5.0 tree (andrey:1.2297) BUG#23037

[ahristov]

Below is the list of changes that have just been committed into a local
5.0 repository of andrey. When andrey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-10-23 13:12:30+02:00, andrey@stripped +3 -0
  Fix for bug#23037 Bug in field "Default" of query "SHOW COLUMNS FROM table"
    
  To the documentor: The DEFAULT value in SHOW COLUMNS/SELECT FROM I_S.COLUMNS
  was truncated to NAME_LEN (which effectively is 64) characters.

  mysql-test/r/information_schema.result@stripped, 2006-10-23 13:12:26+02:00,
andrey@stripped +14 -0
    Update result
    
    Because the length of I_S.COLUMNS.COLUMN_DEFAULT was increased to
    65535 the column changed it's type from varchar(64) to longtext
    The type is longtext if the length is > 65532 (the max varchar len)

  mysql-test/t/information_schema.test@stripped, 2006-10-23 13:12:26+02:00, andrey@stripped
+43 -0
    test case for bug#23037: Bug in field "Default" of
    query "SHOW COLUMNS FROM table"
    
    Both SHOW COLUMNS and I_S.COLUMNS is TESTED as well the
    value returned by both. Using --replace_regex is unfeasible
    as the repetition value cannot be > 255.
    --replace_regex /A{256}/VALUE/ doesn't work, not to talk about
      /A{65532}/VALUE/
    /(A{255}){255}/VALUE/ is awfully slow. Thus, simple function with
    cursor is used to extract the value.

  sql/sql_show.cc@stripped, 2006-10-23 13:12:27+02:00, andrey@stripped +1 -1
    Default value can have TIMESTAMP, CHAR, VARCHAR, ENUM
    VARCHAR can have the longest value, up to 65535. However, because
    of table handler limitations the actual limit is 65532 characters,
    latin1 charset. However, here is used MAX_FIELD_VARCHARLENGTH macro,
    because there could be a storage engine without such limitation.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	andrey
# Host:	example.com
# Root:	/work/bug23037/my50

--- 1.328/sql/sql_show.cc	2006-10-23 13:12:41 +02:00
+++ 1.329/sql/sql_show.cc	2006-10-23 13:12:41 +02:00
@@ -4044,7 +4044,7 @@ ST_FIELD_INFO columns_fields_info[]=
   {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
   {"COLUMN_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Field"},
   {"ORDINAL_POSITION", 21 , MYSQL_TYPE_LONG, 0, 0, 0},
-  {"COLUMN_DEFAULT", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, "Default"},
+  {"COLUMN_DEFAULT", MAX_FIELD_VARCHARLENGTH, MYSQL_TYPE_STRING, 0, 1, "Default"},
   {"IS_NULLABLE", 3, MYSQL_TYPE_STRING, 0, 0, "Null"},
   {"DATA_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
   {"CHARACTER_MAXIMUM_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, 0},

--- 1.114/mysql-test/r/information_schema.result	2006-10-23 13:12:41 +02:00
+++ 1.115/mysql-test/r/information_schema.result	2006-10-23 13:12:41 +02:00
@@ -737,6 +737,7 @@ select table_schema,table_name, column_n
 information_schema.columns 
 where data_type = 'longtext';
 table_schema	table_name	column_name
+information_schema	COLUMNS	COLUMN_DEFAULT
 information_schema	COLUMNS	COLUMN_TYPE
 information_schema	ROUTINES	ROUTINE_DEFINITION
 information_schema	ROUTINES	SQL_MODE
@@ -1240,3 +1241,16 @@ WHERE table_name=(SELECT MAX(table_name)
 FROM information_schema.tables);
 table_name
 VIEWS
+DROP TABLE IF EXISTS bug23037;
+DROP FUNCTION IF EXISTS get_value;
+SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
+COLUMN_NAME	MD5(COLUMN_DEFAULT)	LENGTH(COLUMN_DEFAULT)
+fld1	7cf7a6782be951a1f2464a350da926a5	65532
+SELECT MD5(get_value());
+MD5(get_value())
+7cf7a6782be951a1f2464a350da926a5
+SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT),
COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
+COLUMN_NAME	MD5(COLUMN_DEFAULT)	LENGTH(COLUMN_DEFAULT)	COLUMN_DEFAULT=get_value()
+fld1	7cf7a6782be951a1f2464a350da926a5	65532	1
+DROP TABLE bug23037;
+DROP FUNCTION get_value;

--- 1.85/mysql-test/t/information_schema.test	2006-10-23 13:12:42 +02:00
+++ 1.86/mysql-test/t/information_schema.test	2006-10-23 13:12:42 +02:00
@@ -930,4 +930,47 @@ SELECT table_name from information_schem
   WHERE table_name=(SELECT MAX(table_name)
                       FROM information_schema.tables);
 
+#
+# Bug #23037: Bug in field "Default" of query "SHOW COLUMNS FROM table"
+#
+# Note, MyISAM/InnoDB can't take more that 65532 chars, because the row
+# size is limited to 65535 bytes (BLOBs not counted)
+#
+--disable_warnings
+DROP TABLE IF EXISTS bug23037;
+DROP FUNCTION IF EXISTS get_value;
+--enable_warnings
+--disable_query_log
+DELIMITER |;
+CREATE FUNCTION get_value()
+  RETURNS TEXT
+  DETERMINISTIC
+BEGIN
+  DECLARE col1, col2, col3, col4, col6 CHAR(255);
+  DECLARE default_val VARCHAR(65532);
+  DECLARE done INT DEFAULT 0;
+  DECLARE cur1 CURSOR FOR SHOW COLUMNS FROM bug23037;
+  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
+  OPEN cur1;
+  FETCH cur1 INTO col1, col2, col3, col4, default_val, col6;
+  CLOSE cur1;
+  RETURN default_val;
+end|
+DELIMITER ;|
+
+let $body=`SELECT REPEAT('A', 65532)`;
+eval CREATE TABLE bug23037(fld1 VARCHAR(65532) CHARACTER SET latin1 DEFAULT "$body");
+--enable_query_log
+
+SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
+
+SELECT MD5(get_value());
+
+SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT),
COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
+
+DROP TABLE bug23037;
+DROP FUNCTION get_value;
+
+
+
 # End of 5.0 tests.