bk commit into 5.0 tree (gluh:1.2524) BUG#27747

[gluh]

Below is the list of changes that have just been committed into a local
5.0 repository of gluh. When gluh 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, 2007-09-12 12:21:11+05:00, gluh@stripped +17 -0
  Bug#27747 database metadata doesn't return sufficient column default info
  added get_field_default_value() function which obtains default value from the field
  (used in store_create_info() & get_schema_column_record() functions) 

  mysql-test/r/alter_table.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +1 -1
    result fix

  mysql-test/r/create.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +1 -1
    result fix

  mysql-test/r/ctype_collate.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +1 -1
    result fix

  mysql-test/r/ctype_recoding.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +3 -3
    result fix

  mysql-test/r/default.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +1 -1
    result fix

  mysql-test/r/gis.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +9 -9
    result fix

  mysql-test/r/grant.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +1 -1
    result fix

  mysql-test/r/information_schema.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped
+32 -1
    result fix

  mysql-test/r/key.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +11 -11
    result fix

  mysql-test/r/mysql.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +2 -2
    result fix

  mysql-test/r/ps_1general.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +3 -3
    result fix

  mysql-test/r/show_check.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +2 -2
    result fix

  mysql-test/r/sp.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +2 -2
    result fix

  mysql-test/r/type_enum.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +1 -1
    result fix

  mysql-test/r/type_ranges.result@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +11 -11
    result fix

  mysql-test/t/information_schema.test@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +17
-0
    test case

  sql/sql_show.cc@stripped, 2007-09-12 12:21:09+05:00, gluh@stripped +64 -64
    added get_field_default_value() function which obtains default value from the field
    (used in store_create_info() & get_schema_column_record() functions) 

diff -Nrup a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
--- a/mysql-test/r/alter_table.result	2007-07-27 14:30:24 +05:00
+++ b/mysql-test/r/alter_table.result	2007-09-12 12:21:09 +05:00
@@ -54,7 +54,7 @@ SHOW FULL COLUMNS FROM t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 GROUP_ID	int(10) unsigned	NULL	NO	PRI	0		#	
 LANG_ID	smallint(5) unsigned	NULL	NO	PRI	0		#	
-NAME	char(80)	latin1_swedish_ci	NO	MUL			#	
+NAME	char(80)	latin1_swedish_ci	NO	MUL	NULL		#	
 DROP TABLE t1;
 create table t1 (n int);
 insert into t1 values(9),(3),(12),(10);
diff -Nrup a/mysql-test/r/create.result b/mysql-test/r/create.result
--- a/mysql-test/r/create.result	2007-07-26 03:33:41 +05:00
+++ b/mysql-test/r/create.result	2007-09-12 12:21:09 +05:00
@@ -430,7 +430,7 @@ d	date	YES		NULL	
 e	varchar(1)	NO			
 f	datetime	YES		NULL	
 g	time	YES		NULL	
-h	longblob	NO			
+h	longblob	NO		NULL	
 dd	time	YES		NULL	
 select * from t2;
 a	b	c	d	e	f	g	h	dd
diff -Nrup a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result
--- a/mysql-test/r/ctype_collate.result	2007-07-09 02:21:22 +05:00
+++ b/mysql-test/r/ctype_collate.result	2007-09-12 12:21:09 +05:00
@@ -488,7 +488,7 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SHOW FIELDS FROM  t1;
 Field	Type	Null	Key	Default	Extra
-latin1_f	char(32)	NO			
+latin1_f	char(32)	NO		NULL	
 ALTER TABLE t1 CHANGE latin1_f 
 latin1_f CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin;
 SHOW CREATE TABLE t1;
diff -Nrup a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result
--- a/mysql-test/r/ctype_recoding.result	2007-08-07 19:25:43 +05:00
+++ b/mysql-test/r/ctype_recoding.result	2007-09-12 12:21:09 +05:00
@@ -54,7 +54,7 @@ Table	Create Table
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='ËÏÍÍÅÎÔÁÒÉÊ ÔÁÂÌÉÃÙ'
 SHOW FIELDS FROM ÔÁÂÌÉÃÁ;
 Field	Type	Null	Key	Default	Extra
-ÐÏÌÅ	char(32)	NO			
+ÐÏÌÅ	char(32)	NO		NULL	
 SET CHARACTER SET cp1251;
 SHOW TABLES;
 Tables_in_test
@@ -66,7 +66,7 @@ Table	Create Table
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='êîììåíòàðèé òàáëèöû'
 SHOW FIELDS FROM òàáëèöà;
 Field	Type	Null	Key	Default	Extra
-ïîëå	char(32)	NO			
+ïîëå	char(32)	NO		NULL	
 SET CHARACTER SET utf8;
 SHOW TABLES;
 Tables_in_test
@@ -78,7 +78,7 @@ Table	Create Table
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='комментарий таблицы'
 SHOW FIELDS FROM таблица;
 Field	Type	Null	Key	Default	Extra
-поле	char(32)	NO			
+поле	char(32)	NO		NULL	
 SET CHARACTER SET koi8r;
 DROP TABLE ÔÁÂÌÉÃÁ;
 SET CHARACTER SET default;
diff -Nrup a/mysql-test/r/default.result b/mysql-test/r/default.result
--- a/mysql-test/r/default.result	2007-02-12 15:41:34 +04:00
+++ b/mysql-test/r/default.result	2007-09-12 12:21:09 +05:00
@@ -115,7 +115,7 @@ Warning	1364	Field 'd' doesn't have a de
 desc bug20691;
 Field	Type	Null	Key	Default	Extra
 i	int(11)	YES		NULL	
-d	datetime	NO			
+d	datetime	NO		NULL	
 dn	datetime	NO		0000-00-00 00:00:00	
 insert into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (3, DEFAULT, DEFAULT);
 Warnings:
diff -Nrup a/mysql-test/r/gis.result b/mysql-test/r/gis.result
--- a/mysql-test/r/gis.result	2007-07-05 20:24:46 +05:00
+++ b/mysql-test/r/gis.result	2007-09-12 12:21:09 +05:00
@@ -9,35 +9,35 @@ CREATE TABLE gis_geometrycollection  (fi
 CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
 SHOW FIELDS FROM gis_point;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)	NO	PRI		
+fid	int(11)	NO	PRI	NULL	
 g	point	YES		NULL	
 SHOW FIELDS FROM gis_line;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)	NO	PRI		
+fid	int(11)	NO	PRI	NULL	
 g	linestring	YES		NULL	
 SHOW FIELDS FROM gis_polygon;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)	NO	PRI		
+fid	int(11)	NO	PRI	NULL	
 g	polygon	YES		NULL	
 SHOW FIELDS FROM gis_multi_point;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)	NO	PRI		
+fid	int(11)	NO	PRI	NULL	
 g	multipoint	YES		NULL	
 SHOW FIELDS FROM gis_multi_line;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)	NO	PRI		
+fid	int(11)	NO	PRI	NULL	
 g	multilinestring	YES		NULL	
 SHOW FIELDS FROM gis_multi_polygon;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)	NO	PRI		
+fid	int(11)	NO	PRI	NULL	
 g	multipolygon	YES		NULL	
 SHOW FIELDS FROM gis_geometrycollection;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)	NO	PRI		
+fid	int(11)	NO	PRI	NULL	
 g	geometrycollection	YES		NULL	
 SHOW FIELDS FROM gis_geometry;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)	NO	PRI		
+fid	int(11)	NO	PRI	NULL	
 g	geometry	YES		NULL	
 INSERT INTO gis_point VALUES 
 (101, PointFromText('POINT(10 10)')),
@@ -430,7 +430,7 @@ mln	multilinestring	YES		NULL	
 mpg	multipolygon	YES		NULL	
 gc	geometrycollection	YES		NULL	
 gm	geometry	YES		NULL	
-fid	int(11)	NO			
+fid	int(11)	NO		NULL	
 DROP TABLE t1;
 SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
 AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))
diff -Nrup a/mysql-test/r/grant.result b/mysql-test/r/grant.result
--- a/mysql-test/r/grant.result	2007-05-12 01:45:44 +05:00
+++ b/mysql-test/r/grant.result	2007-09-12 12:21:09 +05:00
@@ -526,7 +526,7 @@ Db	char(64)	NO	PRI		
 User	char(16)	NO	PRI		
 Table_name	char(64)	NO	PRI		
 Grantor	char(77)	NO	MUL		
-Timestamp	timestamp	NO		CURRENT_TIMESTAMP	
+Timestamp	timestamp	NO		0000-00-00 00:00:00	
 Table_priv	set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create
View','Show view')	NO			
 Column_priv	set('Select','Insert','Update','References')	NO			
 use test;
diff -Nrup a/mysql-test/r/information_schema.result
b/mysql-test/r/information_schema.result
--- a/mysql-test/r/information_schema.result	2007-08-20 11:23:06 +05:00
+++ b/mysql-test/r/information_schema.result	2007-09-12 12:21:09 +05:00
@@ -989,7 +989,7 @@ b	NULL
 use test;
 show columns from t1;
 Field	Type	Null	Key	Default	Extra
-a	int(11)	NO			
+a	int(11)	NO		NULL	
 b	int(11)	YES		NULL	
 drop table t1;
 CREATE TABLE t1 (a int);
@@ -1354,4 +1354,35 @@ where event_object_table='t1';
 trigger_name
 drop user mysqltest_1@localhost;
 drop database mysqltest;
+create table t1 (
+f1 varchar(50),
+f2 varchar(50) not null,
+f3 varchar(50) default '',
+f4 varchar(50) default NULL,
+f5 bigint not null,
+f6 bigint not null default 10,
+f7 datetime not null,
+f8 datetime default '2006-01-01'
+);
+select column_default from information_schema.columns where table_name= 't1';
+column_default
+NULL
+NULL
+
+NULL
+NULL
+10
+NULL
[phone]:00:00
+show columns from t1;
+Field	Type	Null	Key	Default	Extra
+f1	varchar(50)	YES		NULL	
+f2	varchar(50)	NO		NULL	
+f3	varchar(50)	YES			
+f4	varchar(50)	YES		NULL	
+f5	bigint(20)	NO		NULL	
+f6	bigint(20)	NO		10	
+f7	datetime	NO		NULL	
+f8	datetime	YES		2006-01-01 00:00:00	
+drop table t1;
 End of 5.0 tests.
diff -Nrup a/mysql-test/r/key.result b/mysql-test/r/key.result
--- a/mysql-test/r/key.result	2007-05-30 17:04:02 +05:00
+++ b/mysql-test/r/key.result	2007-09-12 12:21:09 +05:00
@@ -336,8 +336,8 @@ UNIQUE i1idx (i1),
 UNIQUE i2idx (i2));
 desc t1;
 Field	Type	Null	Key	Default	Extra
-i1	int(11)	NO	PRI		
-i2	int(11)	NO	UNI		
+i1	int(11)	NO	PRI	NULL	
+i2	int(11)	NO	UNI	NULL	
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -392,27 +392,27 @@ drop table t1;
 create table t1 (a int not null primary key, b varchar(20) not null unique);
 desc t1;
 Field	Type	Null	Key	Default	Extra
-a	int(11)	NO	PRI		
-b	varchar(20)	NO	UNI		
+a	int(11)	NO	PRI	NULL	
+b	varchar(20)	NO	UNI	NULL	
 drop table t1;
 create table t1 (a int not null primary key, b int not null unique);
 desc t1;
 Field	Type	Null	Key	Default	Extra
-a	int(11)	NO	PRI		
-b	int(11)	NO	UNI		
+a	int(11)	NO	PRI	NULL	
+b	int(11)	NO	UNI	NULL	
 drop table t1;
 create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10)));
 desc t1;
 Field	Type	Null	Key	Default	Extra
-a	int(11)	NO	PRI		
-b	varchar(20)	NO	UNI		
+a	int(11)	NO	PRI	NULL	
+b	varchar(20)	NO	UNI	NULL	
 drop table t1;
 create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not
null, unique(b(10),c(10)));
 desc t1;
 Field	Type	Null	Key	Default	Extra
-a	int(11)	NO	PRI		
-b	varchar(20)	NO	MUL		
-c	varchar(20)	NO			
+a	int(11)	NO	PRI	NULL	
+b	varchar(20)	NO	MUL	NULL	
+c	varchar(20)	NO		NULL	
 drop table t1;
 CREATE TABLE t1 (
 a INTEGER auto_increment PRIMARY KEY,
diff -Nrup a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result
--- a/mysql-test/r/mysql.result	2007-08-30 13:53:20 +05:00
+++ b/mysql-test/r/mysql.result	2007-09-12 12:21:09 +05:00
@@ -91,7 +91,7 @@ i	j	k
 NULL	1	NULL
 Field	Type	Null	Key	Default	Extra
 i	int(11)	YES		NULL	
-j	int(11)	NO			
+j	int(11)	NO		NULL	
 k	int(11)	YES		NULL	
 +------+---+------+
 | i    | j | k    |
@@ -102,7 +102,7 @@ k	int(11)	YES		NULL	
 | Field | Type    | Null | Key | Default | Extra |
 +-------+---------+------+-----+---------+-------+
 | i     | int(11) | YES  |     | NULL    |       | 
-| j     | int(11) | NO   |     |         |       | 
+| j     | int(11) | NO   |     | NULL    |       | 
 | k     | int(11) | YES  |     | NULL    |       | 
 +-------+---------+------+-----+---------+-------+
 i	s1
diff -Nrup a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
--- a/mysql-test/r/ps_1general.result	2007-06-29 18:37:15 +05:00
+++ b/mysql-test/r/ps_1general.result	2007-09-12 12:21:09 +05:00
@@ -269,7 +269,7 @@ prepare stmt4 from ' show columns from t
 SET @arg00="a";
 execute stmt4 using @arg00;
 Field	Type	Null	Key	Default	Extra
-a	int(11)	NO	PRI		
+a	int(11)	NO	PRI	NULL	
 SET @arg00="b";
 execute stmt4 using @arg00;
 Field	Type	Null	Key	Default	Extra
@@ -280,7 +280,7 @@ Field	Type	Null	Key	Default	Extra
 prepare stmt4 from ' show columns from t2 from test like ''a%'' ';
 execute stmt4;
 Field	Type	Null	Key	Default	Extra
-a	int(11)	NO	PRI		
+a	int(11)	NO	PRI	NULL	
 create index t2_idx on t2(b);
 prepare stmt4 from ' show index from t2 from test ';
 execute stmt4;
@@ -409,7 +409,7 @@ drop database mysqltest ;
 prepare stmt3 from ' describe t2 ';
 execute stmt3;
 Field	Type	Null	Key	Default	Extra
-a	int(11)	NO	PRI		
+a	int(11)	NO	PRI	NULL	
 b	char(10)	YES	MUL	NULL	
 drop table t2 ;
 execute stmt3;
diff -Nrup a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
--- a/mysql-test/r/show_check.result	2007-08-02 18:23:14 +05:00
+++ b/mysql-test/r/show_check.result	2007-09-12 12:21:09 +05:00
@@ -228,7 +228,7 @@ show full columns from t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 test_set	set('val1','val2','val3')	latin1_swedish_ci	NO				select,insert,update,references	
 name	char(20)	latin1_swedish_ci	YES		O'Brien		select,insert,update,references	O'Brien as
default
-c	int(11)	NULL	NO				select,insert,update,references	int column
+c	int(11)	NULL	NO		NULL		select,insert,update,references	int column
 c-b	int(11)	NULL	YES		NULL		select,insert,update,references	name with a minus
 space 2	int(11)	NULL	YES		NULL		select,insert,update,references	name with a space
 drop table t1;
@@ -901,7 +901,7 @@ def			COLUMNS	COLUMN_KEY	Key	253	9	3	N	1
 def			COLUMNS	COLUMN_DEFAULT	Default	252	589815	0	Y	16	0	33
 def			COLUMNS	EXTRA	Extra	253	60	0	N	1	0	33
 Field	Type	Null	Key	Default	Extra
-c	int(11)	NO	PRI		
+c	int(11)	NO	PRI	NULL	
 ----------------------------------------------------------------
 SHOW TRIGGERS LIKE 't1';
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max
length	Is_null	Flags	Decimals	Charsetnr
diff -Nrup a/mysql-test/r/sp.result b/mysql-test/r/sp.result
--- a/mysql-test/r/sp.result	2007-09-05 01:38:23 +05:00
+++ b/mysql-test/r/sp.result	2007-09-12 12:21:09 +05:00
@@ -2468,7 +2468,7 @@ Database (foo)
 Level	Code	Message
 Field	Type	Null	Key	Default	Extra
 id	char(16)	NO			
-data	int(11)	NO			
+data	int(11)	NO		NULL	
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 Database	Table	In_use	Name_locked
 Privilege	Context	Comment
@@ -2520,7 +2520,7 @@ Database (foo)
 Level	Code	Message
 Field	Type	Null	Key	Default	Extra
 id	char(16)	NO			
-data	int(11)	NO			
+data	int(11)	NO		NULL	
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 Database	Table	In_use	Name_locked
 Privilege	Context	Comment
diff -Nrup a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
--- a/mysql-test/r/type_enum.result	2007-07-23 06:26:03 +05:00
+++ b/mysql-test/r/type_enum.result	2007-09-12 12:21:09 +05:00
@@ -1675,7 +1675,7 @@ t1	CREATE TABLE `t1` (
 show columns from t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		1	
-b	enum('value','öäü_value','ÊÃÕ')	NO			
+b	enum('value','öäü_value','ÊÃÕ')	NO		NULL	
 drop table t1;
 CREATE TABLE t1 (c enum('a', 'A') BINARY);
 INSERT INTO t1 VALUES ('a'),('A');
diff -Nrup a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
--- a/mysql-test/r/type_ranges.result	2007-07-12 00:02:53 +05:00
+++ b/mysql-test/r/type_ranges.result	2007-09-12 12:21:09 +05:00
@@ -63,9 +63,9 @@ time_field	time	NULL	YES		NULL		#	
 date_time	datetime	NULL	YES		NULL		#	
 blob_col	blob	NULL	YES		NULL		#	
 tinyblob_col	tinyblob	NULL	YES		NULL		#	
-mediumblob_col	mediumblob	NULL	NO				#	
-longblob_col	longblob	NULL	NO				#	
-options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL			#	
+mediumblob_col	mediumblob	NULL	NO		NULL		#	
+longblob_col	longblob	NULL	NO		NULL		#	
+options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	NULL		#	
 flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
 show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
@@ -214,7 +214,7 @@ Field	Type	Collation	Null	Key	Default	Ex
 auto	int(5) unsigned	NULL	NO	MUL	NULL	auto_increment	#	
 string	char(10)	latin1_swedish_ci	YES		newdefault		#	
 tiny	tinyint(4)	NULL	NO	MUL	0		#	
-short	smallint(6)	NULL	NO	MUL			#	
+short	smallint(6)	NULL	NO	MUL	NULL		#	
 medium	mediumint(8)	NULL	NO	MUL	0		#	
 long_int	int(11)	NULL	NO		0		#	
 longlong	bigint(13)	NULL	NO	MUL	0		#	
@@ -231,8 +231,8 @@ time_field	time	NULL	YES		NULL		#	
 date_time	datetime	NULL	YES		NULL		#	
 new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#	
 tinyblob_col	tinyblob	NULL	YES		NULL		#	
-mediumblob_col	mediumblob	NULL	NO				#	
-options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL			#	
+mediumblob_col	mediumblob	NULL	NO		NULL		#	
+options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	NULL		#	
 flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
 new_field	char(10)	latin1_swedish_ci	NO		new		#	
 show full columns from t2;
@@ -240,7 +240,7 @@ Field	Type	Collation	Null	Key	Default	Ex
 auto	int(5) unsigned	NULL	NO		0		#	
 string	char(10)	latin1_swedish_ci	YES		newdefault		#	
 tiny	tinyint(4)	NULL	NO		0		#	
-short	smallint(6)	NULL	NO				#	
+short	smallint(6)	NULL	NO		NULL		#	
 medium	mediumint(8)	NULL	NO		0		#	
 long_int	int(11)	NULL	NO		0		#	
 longlong	bigint(13)	NULL	NO		0		#	
@@ -257,8 +257,8 @@ time_field	time	NULL	YES		NULL		#	
 date_time	datetime	NULL	YES		NULL		#	
 new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#	
 tinyblob_col	tinyblob	NULL	YES		NULL		#	
-mediumblob_col	mediumblob	NULL	NO				#	
-options	enum('one','two','tree')	latin1_swedish_ci	NO				#	
+mediumblob_col	mediumblob	NULL	NO		NULL		#	
+options	enum('one','two','tree')	latin1_swedish_ci	NO		NULL		#	
 flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
 new_field	char(10)	latin1_swedish_ci	NO		new		#	
 select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string
and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and
(t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short
is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is
not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int
is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and
(t1.longlong is not null or t2.longlong is not null)) or
(t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is
not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or
t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or
t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or
t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or
t2.umedium is not null)) or (t1.ulong<>t2.ulong
  and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong
and (t1.ulonglong is not null or t2.ulonglong is not null)) or
(t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is
not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or
t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is
not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and
(t1.date_time is not null or t2.date_time is not null)) or
(t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or
t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and
(t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or
(t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or
t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not
null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null
or t2.flags is not n
 ull)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field
is not null)));
@@ -276,8 +276,8 @@ t1	int(1)	NULL	NO		0		#	
 t2	varchar(1)	latin1_swedish_ci	NO				#	
 t3	varchar(256)	latin1_swedish_ci	NO				#	
 t4	varbinary(256)	NULL	NO				#	
-t5	longtext	latin1_swedish_ci	NO				#	
-t6	longblob	NULL	NO				#	
+t5	longtext	latin1_swedish_ci	NO		NULL		#	
+t6	longblob	NULL	NO		NULL		#	
 t7	char(0)	latin1_swedish_ci	NO				#	
 t8	binary(0)	NULL	NO				#	
 select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
diff -Nrup a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
--- a/mysql-test/t/information_schema.test	2007-08-20 11:23:06 +05:00
+++ b/mysql-test/t/information_schema.test	2007-09-12 12:21:09 +05:00
@@ -1071,4 +1071,21 @@ connection default;
 drop user mysqltest_1@localhost;
 drop database mysqltest;
 
+#
+# Bug#27747 database metadata doesn't return sufficient column default info
+#
+create table t1 (
+  f1 varchar(50),
+  f2 varchar(50) not null,
+  f3 varchar(50) default '',
+  f4 varchar(50) default NULL,
+  f5 bigint not null,
+  f6 bigint not null default 10,
+  f7 datetime not null,
+  f8 datetime default '2006-01-01'
+);
+select column_default from information_schema.columns where table_name= 't1';
+show columns from t1;
+drop table t1;
+
 --echo End of 5.0 tests.
diff -Nrup a/sql/sql_show.cc b/sql/sql_show.cc
--- a/sql/sql_show.cc	2007-08-28 05:19:53 +05:00
+++ b/sql/sql_show.cc	2007-09-12 12:21:09 +05:00
@@ -791,13 +791,70 @@ static void append_directory(THD *thd, S
 
 #define LIST_PROCESS_HOST_LEN 64
 
+
+static bool get_field_default_value(THD *thd, TABLE *table,
+                                    Field *field, String *def_value,
+                                    bool quoted)
+{
+  bool has_default;
+  bool has_now_default;
+
+  /* 
+     We are using CURRENT_TIMESTAMP instead of NOW because it is
+     more standard
+  */
+  has_now_default= table->timestamp_field == field && 
+    field->unireg_check != Field::TIMESTAMP_UN_FIELD;
+    
+  has_default= (field->type() != FIELD_TYPE_BLOB &&
+                !(field->flags & NO_DEFAULT_VALUE_FLAG) &&
+                field->unireg_check != Field::NEXT_NUMBER &&
+                !((thd->variables.sql_mode & (MODE_MYSQL323 | MODE_MYSQL40))
+                  && has_now_default));
+
+  def_value->length(0);
+  if (has_default)
+  {
+    if (has_now_default)
+      def_value->append(STRING_WITH_LEN("CURRENT_TIMESTAMP"));
+    else if (!field->is_null())
+    {                                             // Not null by default
+      char tmp[MAX_FIELD_WIDTH];
+      String type(tmp, sizeof(tmp), field->charset());
+      field->val_str(&type);
+      if (type.length())
+      {
+        String def_val;
+        uint dummy_errors;
+        /* convert to system_charset_info == utf8 */
+        def_val.copy(type.ptr(), type.length(), field->charset(),
+                     system_charset_info, &dummy_errors);
+        if (quoted)
+          append_unescaped(def_value, def_val.ptr(), def_val.length());
+        else
+          def_value->append(def_val.ptr(), def_val.length());
+      }
+      else if (quoted)
+        def_value->append(STRING_WITH_LEN("''"));
+    }
+    else if (field->maybe_null() && quoted)
+      def_value->append(STRING_WITH_LEN("NULL"));    // Null as default
+    else
+      return 0;
+
+  }
+  return has_default;
+}
+
+
 static int
 store_create_info(THD *thd, TABLE_LIST *table_list, String *packet)
 {
   List<Item> field_list;
-  char tmp[MAX_FIELD_WIDTH], *for_str, buff[128];
+  char tmp[MAX_FIELD_WIDTH], *for_str, buff[128], def_value_buf[MAX_FIELD_WIDTH];
   const char *alias;
   String type(tmp, sizeof(tmp), system_charset_info);
+  String def_value(def_value_buf, sizeof(def_value_buf), system_charset_info);
   Field **ptr,*field;
   uint primary_key;
   KEY *key_info;
@@ -833,8 +890,6 @@ store_create_info(THD *thd, TABLE_LIST *
 
   for (ptr=table->field ; (field= *ptr); ptr++)
   {
-    bool has_default;
-    bool has_now_default;
     uint flags = field->flags;
 
     if (ptr != table->field)
@@ -882,44 +937,10 @@ store_create_info(THD *thd, TABLE_LIST *
       packet->append(STRING_WITH_LEN(" NULL"));
     }
 
-    /* 
-      Again we are using CURRENT_TIMESTAMP instead of NOW because it is
-      more standard 
-    */
-    has_now_default= table->timestamp_field == field && 
-                     field->unireg_check != Field::TIMESTAMP_UN_FIELD;
-    
-    has_default= (field->type() != FIELD_TYPE_BLOB &&
-                  !(field->flags & NO_DEFAULT_VALUE_FLAG) &&
-		  field->unireg_check != Field::NEXT_NUMBER &&
-                  !((thd->variables.sql_mode & (MODE_MYSQL323 | MODE_MYSQL40))
-		    && has_now_default));
-
-    if (has_default)
+    if (get_field_default_value(thd, table, field, &def_value, 1))
     {
       packet->append(STRING_WITH_LEN(" default "));
-      if (has_now_default)
-        packet->append(STRING_WITH_LEN("CURRENT_TIMESTAMP"));
-      else if (!field->is_null())
-      {                                             // Not null by default
-        type.set(tmp, sizeof(tmp), field->charset());
-        field->val_str(&type);
-	if (type.length())
-	{
-	  String def_val;
-          uint dummy_errors;
-	  /* convert to system_charset_info == utf8 */
-	  def_val.copy(type.ptr(), type.length(), field->charset(),
-		       system_charset_info, &dummy_errors);
-          append_unescaped(packet, def_val.ptr(), def_val.length());
-	}
-        else
-	  packet->append(STRING_WITH_LEN("''"));
-      }
-      else if (field->maybe_null())
-        packet->append(STRING_WITH_LEN("NULL"));    // Null as default
-      else
-        packet->append(tmp);
+      packet->append(def_value.ptr(), def_value.length(), system_charset_info);
     }
 
     if (!limited_mysql_mode && table->timestamp_field == field && 
@@ -2664,7 +2685,6 @@ static int get_schema_column_record(THD 
     bool is_blob;
     uint flags=field->flags;
     char tmp[MAX_FIELD_WIDTH];
-    char tmp1[MAX_FIELD_WIDTH];
     String type(tmp,sizeof(tmp), system_charset_info);
     char *end;
     int decimals, field_length;
@@ -2710,33 +2730,13 @@ static int get_schema_column_record(THD 
     table->field[7]->store(type.ptr(),
                            (tmp_buff ? tmp_buff - type.ptr() :
                             type.length()), cs);
-    if (show_table->timestamp_field == field &&
-        field->unireg_check != Field::TIMESTAMP_UN_FIELD)
-    {
-      table->field[5]->store(STRING_WITH_LEN("CURRENT_TIMESTAMP"), cs);
-      table->field[5]->set_notnull();
-    }
-    else if (field->unireg_check != Field::NEXT_NUMBER &&
-             !field->is_null() &&
-             !(field->flags & NO_DEFAULT_VALUE_FLAG))
-    {
-      String def(tmp1,sizeof(tmp1), cs);
-      type.set(tmp, sizeof(tmp), field->charset());
-      field->val_str(&type);
-      uint dummy_errors;
-      def.copy(type.ptr(), type.length(), type.charset(), cs, &dummy_errors);
-      table->field[5]->store(def.ptr(), def.length(), def.charset());
-      table->field[5]->set_notnull();
-    }
-    else if (field->unireg_check == Field::NEXT_NUMBER ||
-             lex->orig_sql_command != SQLCOM_SHOW_FIELDS ||
-             field->maybe_null())
-      table->field[5]->set_null();                // Null as default
-    else
+
+    if (get_field_default_value(thd, show_table, field, &type, 0))
     {
-      table->field[5]->store("",0, cs);
+      table->field[5]->store(type.ptr(), type.length(), cs);
       table->field[5]->set_notnull();
     }
+
     pos=(byte*) ((flags & NOT_NULL_FLAG) ?  "NO" : "YES");
     table->field[6]->store((const char*) pos,
                            strlen((const char*) pos), cs);