Don't display 'usage' privilege in TABLE_PRIVILEGES if we have columns privileges

mysqldump skips information_schema db
'use' now can use information_schema db
changed value of column 'Null' to 'NO' if column is not nullable


client/mysqldump.c:
  mysqldump skips information_schema db
mysql-test/r/alter_table.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/create.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/ctype_collate.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/ctype_recoding.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/ctype_ujis.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/drop.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/func_sapdb.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/func_time.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/gis.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/information_schema.result:
  Added couple of tests
mysql-test/r/information_schema_inno.result:
  Removed coulmn 'CONTRAINT_METOD' from TABLE_CONSTRAINTS
  Added column 'POSITION_IN_UNIQUE_CONSTRAINT' to KEY_COLUMN_USAGE
mysql-test/r/innodb.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/ndb_autodiscover.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/ps_1general.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/rpl000009.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/rpl_create_database.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/schema.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/select.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/show_check.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/sp.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/type_enum.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/r/type_ranges.result:
  changed value of column 'Null' to 'NO' if column is not nullable
mysql-test/t/information_schema.test:
  Added couple of tests
sql/sql_acl.cc:
  Don't display 'usage' privilege in TABLE_PRIVILEGES if we have columns privileges
sql/sql_db.cc:
  'use' now can use information_schema db
sql/sql_show.cc:
  code cleanup
  informaton_schema(IS) db now contains data about IS itself
sql/sql_yacc.yy:
  A fix(wrong behavour of 'SHOW COLUMNS, SHOW KEYS' with 'where condition')

23 files changed, 233 insertions, 157 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index e0d484312e7..37f5d0aa26a 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -52,9 +52,9 @@ KEY NAME (NAME));
 ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
 SHOW FULL COLUMNS FROM t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-GROUP_ID	int(10) unsigned	NULL		PRI	0		select,insert,update,references	
-LANG_ID	smallint(5) unsigned	NULL		PRI	0		select,insert,update,references	
-NAME	char(80)	latin1_swedish_ci		MUL			select,insert,update,references	
+GROUP_ID	int(10) unsigned	NULL	NO	PRI	0		select,insert,update,references	
+LANG_ID	smallint(5) unsigned	NULL	NO	PRI	0		select,insert,update,references	
+NAME	char(80)	latin1_swedish_ci	NO	MUL			select,insert,update,references	
 DROP TABLE t1;
 create table t1 (n int);
 insert into t1 values(9),(3),(12),(10);
@@ -187,7 +187,7 @@ alter table t1 rename t2;
 alter table t2 rename t1, add c char(10) comment "no comment";
 show columns from t1;
 Field	Type	Null	Key	Default	Extra
-i	int(10) unsigned		PRI	NULL	auto_increment
+i	int(10) unsigned	NO	PRI	NULL	auto_increment
 c	char(10)	YES		NULL	
 drop table t1;
 create table t1 (a int, b int);
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index ce4515f900b..f2e91c36f75 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -100,12 +100,12 @@ drop table t2;
 create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;
 describe t2;
 Field	Type	Null	Key	Default	Extra
-a	datetime			0000-00-00 00:00:00	
-b	time			00:00:00	
-c	date			0000-00-00	
-d	bigint(17)			0	
-e	double(18,1)			0.0	
-f	bigint(17)			0	
+a	datetime	NO		0000-00-00 00:00:00	
+b	time	NO		00:00:00	
+c	date	NO		0000-00-00	
+d	bigint(17)	NO		0	
+e	double(18,1)	NO		0.0	
+f	bigint(17)	NO		0	
 drop table t2;
 create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("20:45:11" AS TIME) as t, CAST("2001-12-29  20:45:11" AS DATETIME) as dt;
 describe t2;
@@ -412,13 +412,13 @@ from t1;
 explain t2;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	bigint(11)			0	
-c	bigint(10)			0	
+b	bigint(11)	NO		0	
+c	bigint(10)	NO		0	
 d	date	YES		NULL	
-e	varchar(1)				
+e	varchar(1)	NO			
 f	datetime	YES		NULL	
 g	time	YES		NULL	
-h	longblob				
+h	longblob	NO			
 dd	time	YES		NULL	
 select * from t2;
 a	b	c	d	e	f	g	h	dd
diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result
index aebf8b4637b..201e1c6de08 100644
--- a/mysql-test/r/ctype_collate.result
+++ b/mysql-test/r/ctype_collate.result
@@ -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)				
+latin1_f	char(32)	NO			
 ALTER TABLE t1 CHANGE latin1_f 
 latin1_f CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin;
 SHOW CREATE TABLE t1;
diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result
index 7d5f9d5b59a..da0007fdfbe 100644
--- a/mysql-test/r/ctype_recoding.result
+++ b/mysql-test/r/ctype_recoding.result
@@ -54,7 +54,7 @@ Table	Create Table
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='ËÏÍÍÅÎÔÁÒÉÊ ÔÁÂÌÉÃÙ'
 SHOW FIELDS FROM ÔÁÂÌÉÃÁ;
 Field	Type	Null	Key	Default	Extra
-ÐÏÌÅ	char(32)				
+ÐÏÌÅ	char(32)	NO			
 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)				
+ïîëå	char(32)	NO			
 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)				
+поле	char(32)	NO			
 SET CHARACTER SET koi8r;
 DROP TABLE ÔÁÂÌÉÃÁ;
 SET CHARACTER SET default;
diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result
index d02ac0062f8..c9d37fb845a 100644
--- a/mysql-test/r/ctype_ujis.result
+++ b/mysql-test/r/ctype_ujis.result
@@ -123,7 +123,7 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=ujis
 SHOW COLUMNS FROM t1;
 Field	Type	Null	Key	Default	Extra
-a	char(1)				
+a	char(1)	NO			
 b	enum('¤¢','¤¤')	YES		NULL	
 DROP TABLE t1;
 CREATE TABLE t1
diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result
index 223ceb003b3..901871f437e 100644
--- a/mysql-test/r/drop.result
+++ b/mysql-test/r/drop.result
@@ -32,6 +32,7 @@ unlock tables;
 create database mysqltest;
 show databases;
 Database
+information_schema
 mysql
 mysqltest
 test
@@ -42,6 +43,7 @@ unlock tables;
 drop database mysqltest;
 show databases;
 Database
+information_schema
 mysql
 test
 drop database mysqltest;
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index fb344855e83..f80b0281dd8 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -174,12 +174,12 @@ date("1997-12-31 23:59:59.000001") as f8,
 time("1997-12-31 23:59:59.000001") as f9;
 describe t1;
 Field	Type	Null	Key	Default	Extra
-f1	date			0000-00-00	
+f1	date	NO		0000-00-00	
 f2	datetime	YES		NULL	
 f3	time	YES		NULL	
-f4	time			00:00:00	
-f5	time			00:00:00	
-f6	time			00:00:00	
+f4	time	NO		00:00:00	
+f5	time	NO		00:00:00	
+f6	time	NO		00:00:00	
 f7	datetime	YES		NULL	
 f8	date	YES		NULL	
 f9	time	YES		NULL	
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 131496f3d9f..32c80bb330e 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -611,7 +611,7 @@ create table t1 select last_day('2000-02-05') as a,
 from_days(to_days("960101")) as b;
 describe t1;
 Field	Type	Null	Key	Default	Extra
-a	date			0000-00-00	
+a	date	NO		0000-00-00	
 b	date	YES		NULL	
 select * from t1;
 a	b
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index ee0a30e27d0..3b196a60d68 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -9,35 +9,35 @@ CREATE TABLE gis_geometrycollection  (fid INTEGER NOT NULL PRIMARY KEY, g GEOMET
 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)		PRI		
+fid	int(11)	NO	PRI		
 g	point	YES		NULL	
 SHOW FIELDS FROM gis_line;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)		PRI		
+fid	int(11)	NO	PRI		
 g	linestring	YES		NULL	
 SHOW FIELDS FROM gis_polygon;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)		PRI		
+fid	int(11)	NO	PRI		
 g	polygon	YES		NULL	
 SHOW FIELDS FROM gis_multi_point;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)		PRI		
+fid	int(11)	NO	PRI		
 g	multipoint	YES		NULL	
 SHOW FIELDS FROM gis_multi_line;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)		PRI		
+fid	int(11)	NO	PRI		
 g	multilinestring	YES		NULL	
 SHOW FIELDS FROM gis_multi_polygon;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)		PRI		
+fid	int(11)	NO	PRI		
 g	multipolygon	YES		NULL	
 SHOW FIELDS FROM gis_geometrycollection;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)		PRI		
+fid	int(11)	NO	PRI		
 g	geometrycollection	YES		NULL	
 SHOW FIELDS FROM gis_geometry;
 Field	Type	Null	Key	Default	Extra
-fid	int(11)		PRI		
+fid	int(11)	NO	PRI		
 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)				
+fid	int(11)	NO			
 DROP TABLE t1;
 SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
 AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index f716d90a073..ab6e180e6b7 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -5,10 +5,12 @@ NULL	mysql	latin1	NULL
 NULL	test	latin1	NULL
 select schema_name from information_schema.schemata;
 schema_name
+information_schema
 mysql
 test
 show databases *;
 CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	SQL_PATH
+NULL	information_schema	utf8	NULL
 NULL	mysql	latin1	NULL
 NULL	test	latin1	NULL
 show databases like 't%';
@@ -16,6 +18,7 @@ Database (t%)
 test
 show databases;
 Database
+information_schema
 mysql
 test
 show databases * where schema_name like 't%';
@@ -31,6 +34,22 @@ create table testtets.t4(a int);
 create view v1 (c) as select table_name from information_schema.TABLES;
 select * from v1;
 c
+SCHEMATA
+TABLES
+COLUMNS
+CHARACTER_SETS
+COLLATIONS
+COLLATION_CHARACTER_SET_APPLICABILITY
+ROUTINES
+STATISTICS
+VIEWS
+USER_PRIVILEGES
+SCHEMA_PRIVILEGES
+TABLE_PRIVILEGES
+COLUMN_PRIVILEGES
+TABLE_CONSTRAINTS
+KEY_COLUMN_USAGE
+TABLE_NAMES
 columns_priv
 db
 func
@@ -56,6 +75,10 @@ select c,table_name from v1
 left join information_schema.TABLES v2 on (v1.c=v2.table_name)
 where v1.c like "t%";
 c	table_name
+TABLES	TABLES
+TABLE_PRIVILEGES	TABLE_PRIVILEGES
+TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
+TABLE_NAMES	TABLE_NAMES
 tables_priv	tables_priv
 time_zone	time_zone
 time_zone_leap_second	time_zone_leap_second
@@ -70,6 +93,10 @@ select c, v2.table_name from v1
 right join information_schema.TABLES v2 on (v1.c=v2.table_name)
 where v1.c like "t%";
 c	table_name
+TABLES	TABLES
+TABLE_PRIVILEGES	TABLE_PRIVILEGES
+TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
+TABLE_NAMES	TABLE_NAMES
 tables_priv	tables_priv
 time_zone	time_zone
 time_zone_leap_second	time_zone_leap_second
@@ -88,11 +115,10 @@ t4
 select * from information_schema.STATISTICS where TABLE_SCHEMA = "testtets";
 TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT
 NULL	testtets	t1	1	testtets	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE	
-show keys * where TABLE_SCHEMA Like "test%";
+show keys * from t3 where TABLE_SCHEMA Like "test%";
 TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT
 NULL	test	t3	1	test	a_data	1	a	A	NULL	NULL	NULL	YES	BTREE	
-NULL	testtets	t1	1	testtets	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE	
-show keys where INDEX_NAME = "a_data";
+show keys from t3 where INDEX_NAME = "a_data";
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t3	1	a_data	1	a	A	NULL	NULL	NULL	YES	BTREE	
 show tables like 't%';
@@ -113,15 +139,15 @@ Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 a	int(11)	NULL	YES	MUL	NULL		select,insert,update,references	
 show full columns from mysql.db like "Insert%";
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-Insert_priv	enum('N','Y')	utf8_bin			N		select,insert,update,references	
+Insert_priv	enum('N','Y')	utf8_bin	NO		N		select,insert,update,references	
 show full columns from v1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-c	varchar(64)	utf8_general_ci					select,insert,update,references	
+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	testtets	t1	a	1	NULL	YES	int	11	11	11	0	NULL	NULL	int(11)			select,insert,update,references	
-show columns * where table_name = "t1";
+show columns * from testtets.t1 where table_name = "t1";
 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	testtets	t1	a	1	NULL	YES	int	11	11	11	0	NULL	NULL	int(11)			select,insert,update,references	
 NULL	testtets	t1	b	2	NULL	YES	varchar	30	30	NULL	NULL	latin1	latin1_swedish_ci	varchar(30)	MUL		select,insert,update,references	
@@ -255,6 +281,7 @@ count(*)
 create view v0 (c) as select schema_name from information_schema.schemata;
 select * from v0;
 c
+information_schema
 mysql
 test
 explain select * from v0;
@@ -351,18 +378,18 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from information_schema.TABLE_CONSTRAINTS where
 TABLE_SCHEMA= "test";
-CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_METHOD
-NULL	test	PRIMARY	test	t1	PRIMARY KEY	NULL
-NULL	test	constraint_1	test	t1	UNIQUE	NULL
-NULL	test	key_1	test	t1	UNIQUE	NULL
-NULL	test	key_2	test	t1	UNIQUE	NULL
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
+NULL	test	PRIMARY	test	t1	PRIMARY KEY
+NULL	test	constraint_1	test	t1	UNIQUE
+NULL	test	key_1	test	t1	UNIQUE
+NULL	test	key_2	test	t1	UNIQUE
 select * from information_schema.KEY_COLUMN_USAGE where
 TABLE_SCHEMA= "test";
-CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
-NULL	test	PRIMARY	NULL	test	t1	a	1	NULL	NULL	NULL
-NULL	test	constraint_1	NULL	test	t1	a	1	NULL	NULL	NULL
-NULL	test	key_1	NULL	test	t1	a	1	NULL	NULL	NULL
-NULL	test	key_2	NULL	test	t1	a	1	NULL	NULL	NULL
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
+NULL	test	PRIMARY	NULL	test	t1	a	1	NULL	NULL	NULL	NULL
+NULL	test	constraint_1	NULL	test	t1	a	1	NULL	NULL	NULL	NULL
+NULL	test	key_1	NULL	test	t1	a	1	NULL	NULL	NULL	NULL
+NULL	test	key_2	NULL	test	t1	a	1	NULL	NULL	NULL	NULL
 select table_name from information_schema.TABLES where table_schema like "test%";
 table_name
 t1
@@ -392,7 +419,6 @@ GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRAN
 'joe'@'localhost'	NULL	test	t1	a	SELECT	YES
 select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES;
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
-'joe'@'localhost'	NULL	test	t1	USAGE	YES
 drop view v1, v2, v3;
 drop table t1;
 delete from mysql.user where user='joe';
@@ -404,7 +430,7 @@ create procedure px5 ()
 begin
 declare v int;
 declare c cursor for select version from
-information_schema.tables;
+information_schema.tables where table_schema <> 'information_schema';
 open c;
 fetch c into v;
 select v;
@@ -432,6 +458,7 @@ select s1 from t1 where s1 in (select version from
 information_schema.tables) union select version from
 information_schema.tables;
 s1
+0
 9
 10
 drop table t1;
@@ -508,11 +535,6 @@ proc	modified	timestamp
 proc	sql_mode	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO')
 proc	comment	char(64)
 drop table t115;
-create view vk as select count(*) from information_schema.tables a;
-select * from vk;
-count(*)
-17
-drop view vk;
 create procedure p108 () begin declare c cursor for select data_type
 from information_schema.columns;  open c; open c; end;//
 call p108()//
@@ -529,8 +551,37 @@ show index from vo;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 select * from information_schema.TABLE_CONSTRAINTS where
 TABLE_NAME= "vo";
-CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_METHOD
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
 select * from information_schema.KEY_COLUMN_USAGE where
 TABLE_NAME= "vo";
-CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
 drop view vo;
+select TABLE_NAME,TABLE_TYPE,ENGINE
+from information_schema.tables 
+where table_schema='information_schema' limit 2;
+TABLE_NAME	TABLE_TYPE	ENGINE
+SCHEMATA	TEMPORARY	MyISAM
+TABLES	TEMPORARY	MyISAM
+show tables from information_schema like "t%";
+Tables_in_information_schema (t%)
+create database information_schema;
+ERROR HY000: Can't create database 'information_schema'; database exists
+use information_schema;
+show full tables like "T%";
+Tables_in_information_schema (T%)	Table_type
+TABLES	TEMPORARY
+TABLE_PRIVILEGES	TEMPORARY
+TABLE_CONSTRAINTS	TEMPORARY
+TABLE_NAMES	TEMPORARY
+create table t1(a int);
+ERROR 42S02: Unknown table 't1' in information_schema
+use test;
+show tables;
+Tables_in_test
+use information_schema;
+show tables like "T%";
+Tables_in_information_schema (T%)
+TABLES
+TABLE_PRIVILEGES
+TABLE_CONSTRAINTS
+TABLE_NAMES
diff --git a/mysql-test/r/information_schema_inno.result b/mysql-test/r/information_schema_inno.result
index e6dcda2c15d..cdbdda5fd43 100644
--- a/mysql-test/r/information_schema_inno.result
+++ b/mysql-test/r/information_schema_inno.result
@@ -4,16 +4,16 @@ FOREIGN KEY (t1_id) REFERENCES t1(id)  ON DELETE CASCADE,
 FOREIGN KEY (t1_id) REFERENCES t1(id)  ON UPDATE CASCADE) ENGINE=INNODB;
 select * from information_schema.TABLE_CONSTRAINTS where
 TABLE_SCHEMA= "test";
-CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_METHOD
-NULL	test	PRIMARY	test	t1	PRIMARY KEY	NULL
-NULL	test	PRIMARY	test	t2	PRIMARY KEY	NULL
-NULL	test	t2_ibfk_1	test	t2	FOREIGN KEY	ON DELETE CASCADE
-NULL	test	t2_ibfk_2	test	t2	FOREIGN KEY	ON UPDATE CASCADE
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
+NULL	test	PRIMARY	test	t1	PRIMARY KEY
+NULL	test	PRIMARY	test	t2	PRIMARY KEY
+NULL	test	t2_ibfk_1	test	t2	FOREIGN KEY
+NULL	test	t2_ibfk_2	test	t2	FOREIGN KEY
 select * from information_schema.KEY_COLUMN_USAGE where
 TABLE_SCHEMA= "test";
-CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
-NULL	test	PRIMARY	NULL	test	t1	id	1	NULL	NULL	NULL
-NULL	test	PRIMARY	NULL	test	t2	id	1	NULL	NULL	NULL
-NULL	test	t2_ibfk_1	NULL	test	t2	t1_id	1	NULL		id
-NULL	test	t2_ibfk_2	NULL	test	t2	t1_id	1	NULL		id
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
+NULL	test	PRIMARY	NULL	test	t1	id	1	NULL	NULL	NULL	NULL
+NULL	test	PRIMARY	NULL	test	t2	id	1	NULL	NULL	NULL	NULL
+NULL	test	t2_ibfk_1	NULL	test	t2	t1_id	1	1	test	t1	id
+NULL	test	t2_ibfk_2	NULL	test	t2	t1_id	1	1	test	t1	id
 drop table t2, t1;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 60028763b17..9a6c69b7bea 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -931,7 +931,7 @@ drop table t1;
 create table t1 (t int not null default 1, key (t)) engine=innodb;
 desc t1;
 Field	Type	Null	Key	Default	Extra
-t	int(11)		MUL	1	
+t	int(11)	NO	MUL	1	
 drop table t1;
 CREATE TABLE t1 (
 number bigint(20) NOT NULL default '0',
diff --git a/mysql-test/r/ndb_autodiscover.result b/mysql-test/r/ndb_autodiscover.result
index 82ff4072378..ba7bcd05673 100644
--- a/mysql-test/r/ndb_autodiscover.result
+++ b/mysql-test/r/ndb_autodiscover.result
@@ -355,6 +355,7 @@ drop table t2;
 drop database test2;
 show databases;
 Database
+information_schema
 mysql
 test
 use test;
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index 0868ec3a364..ef399b6662d 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -254,6 +254,7 @@ a int primary key, b char(10)
 prepare stmt4 from ' show databases ';
 execute stmt4;
 Database
+information_schema
 mysql
 test
 prepare stmt4 from ' show tables from test like ''t2%'' ';
@@ -263,7 +264,7 @@ t2
 prepare stmt4 from ' show columns from t2 from test like ''a%'' ';
 execute stmt4;
 Field	Type	Null	Key	Default	Extra
-a	int(11)		PRI		
+a	int(11)	NO	PRI		
 create index t2_idx on t2(b);
 prepare stmt4 from ' show index from t2 from test ';
 execute stmt4;
@@ -410,7 +411,7 @@ drop user drop_user@localhost;
 prepare stmt3 from ' describe t2 ';
 execute stmt3;
 Field	Type	Null	Key	Default	Extra
-a	int(11)		PRI		
+a	int(11)	NO	PRI		
 b	char(10)	YES	MUL	NULL	
 drop table t2 ;
 execute stmt3;
diff --git a/mysql-test/r/rpl000009.result b/mysql-test/r/rpl000009.result
index bb82dcb1e6a..a4dbf54f39b 100644
--- a/mysql-test/r/rpl000009.result
+++ b/mysql-test/r/rpl000009.result
@@ -32,6 +32,7 @@ create database mysqltest2;
 create database mysqltest;
 show databases;
 Database
+information_schema
 mysql
 mysqltest
 mysqltest2
@@ -48,6 +49,7 @@ insert into mysqltest.t2 values (11, 'eleven test'), (12, 'twelve test'),
 set sql_log_bin = 1;
 show databases;
 Database
+information_schema
 mysql
 test
 create database mysqltest2;
@@ -66,6 +68,7 @@ insert into mysqltest.t3 values (1, 'original bar.t3');
 load data from master;
 show databases;
 Database
+information_schema
 mysql
 mysqltest
 mysqltest2
diff --git a/mysql-test/r/rpl_create_database.result b/mysql-test/r/rpl_create_database.result
index 86282ce3cc5..ca4585d0d8d 100644
--- a/mysql-test/r/rpl_create_database.result
+++ b/mysql-test/r/rpl_create_database.result
@@ -22,6 +22,7 @@ USE mysqltest_sisyfos;
 ALTER DATABASE mysqltest_bob CHARACTER SET latin1;
 SHOW DATABASES;
 Database
+information_schema
 mysql
 mysqltest_bob
 mysqltest_prometheus
@@ -29,6 +30,7 @@ mysqltest_sisyfos
 test
 SHOW DATABASES;
 Database
+information_schema
 mysql
 mysqltest_prometheus
 mysqltest_sisyfos
@@ -57,6 +59,7 @@ master-bin.000001	#	Query	1	#	CREATE DATABASE mysqltest_sisyfos
 master-bin.000001	#	Query	1	#	use `mysqltest_sisyfos`; CREATE TABLE t2 (a INT)
 SHOW DATABASES;
 Database
+information_schema
 mysql
 mysqltest_bob
 mysqltest_prometheus
@@ -64,6 +67,7 @@ mysqltest_sisyfos
 test
 SHOW DATABASES;
 Database
+information_schema
 mysql
 mysqltest_prometheus
 mysqltest_sisyfos
diff --git a/mysql-test/r/schema.result b/mysql-test/r/schema.result
index d7bd6fef655..48e6ebcfad2 100644
--- a/mysql-test/r/schema.result
+++ b/mysql-test/r/schema.result
@@ -4,6 +4,7 @@ Database	Create Database
 foo	CREATE DATABASE `foo` /*!40100 DEFAULT CHARACTER SET latin1 */
 show schemas;
 Database
+information_schema
 foo
 mysql
 test
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index e4232a759b1..ac0157fcfd1 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2034,20 +2034,20 @@ show tables from test like "t?";
 Tables_in_test (t?)
 show full columns from t2;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-auto	int(11)	NULL		PRI	NULL	auto_increment	select,insert,update,references	
-fld1	int(6) unsigned zerofill	NULL		UNI	000000		select,insert,update,references	
-companynr	tinyint(2) unsigned zerofill	NULL			00		select,insert,update,references	
-fld3	char(30)	latin1_swedish_ci		MUL			select,insert,update,references	
-fld4	char(35)	latin1_swedish_ci					select,insert,update,references	
-fld5	char(35)	latin1_swedish_ci					select,insert,update,references	
-fld6	char(4)	latin1_swedish_ci					select,insert,update,references	
+auto	int(11)	NULL	NO	PRI	NULL	auto_increment	select,insert,update,references	
+fld1	int(6) unsigned zerofill	NULL	NO	UNI	000000		select,insert,update,references	
+companynr	tinyint(2) unsigned zerofill	NULL	NO		00		select,insert,update,references	
+fld3	char(30)	latin1_swedish_ci	NO	MUL			select,insert,update,references	
+fld4	char(35)	latin1_swedish_ci	NO				select,insert,update,references	
+fld5	char(35)	latin1_swedish_ci	NO				select,insert,update,references	
+fld6	char(4)	latin1_swedish_ci	NO				select,insert,update,references	
 show full columns from t2 from test like 'f%';
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-fld1	int(6) unsigned zerofill	NULL		UNI	000000		select,insert,update,references	
-fld3	char(30)	latin1_swedish_ci		MUL			select,insert,update,references	
-fld4	char(35)	latin1_swedish_ci					select,insert,update,references	
-fld5	char(35)	latin1_swedish_ci					select,insert,update,references	
-fld6	char(4)	latin1_swedish_ci					select,insert,update,references	
+fld1	int(6) unsigned zerofill	NULL	NO	UNI	000000		select,insert,update,references	
+fld3	char(30)	latin1_swedish_ci	NO	MUL			select,insert,update,references	
+fld4	char(35)	latin1_swedish_ci	NO				select,insert,update,references	
+fld5	char(35)	latin1_swedish_ci	NO				select,insert,update,references	
+fld6	char(4)	latin1_swedish_ci	NO				select,insert,update,references	
 show full columns from t2 from test like 's%';
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 show keys from t2;
diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index 931fb8b20b9..dab35262e0a 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -50,6 +50,7 @@ show table status from test like "this_doesn't_exists%";
 Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
 show databases;
 Database
+information_schema
 mysql
 test
 show databases like "test%";
@@ -118,9 +119,9 @@ t1	CREATE TABLE t1 (
 set sql_quote_show_create=1;
 show full columns from t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-test_set	set('val1','val2','val3')	latin1_swedish_ci					select,insert,update,references	
+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					select,insert,update,references	int column
+c	int(11)	NULL	NO				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;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 8745a274851..0d7f9f7d50c 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1646,8 +1646,8 @@ test	CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
 Database (foo)
 Level	Code	Message
 Field	Type	Null	Key	Default	Extra
-id	char(16)				
-data	int(11)				
+id	char(16)	NO			
+data	int(11)	NO			
 Grants for root@localhost
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
@@ -1696,8 +1696,8 @@ test	CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
 Database (foo)
 Level	Code	Message
 Field	Type	Null	Key	Default	Extra
-id	char(16)				
-data	int(11)				
+id	char(16)	NO			
+data	int(11)	NO			
 Grants for root@localhost
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
index 2ccf32367fb..6e92cb7b54c 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -1654,7 +1654,7 @@ set names latin1;
 create table t1 (a enum(0xE4, '1', '2') not null default 0xE4);
 show columns from t1;
 Field	Type	Null	Key	Default	Extra
-a	enum('ä','1','2')			ä	
+a	enum('ä','1','2')	NO		ä	
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -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','ÊÃÕ')			value	
+b	enum('value','öäü_value','ÊÃÕ')	NO		value	
 drop table t1;
 CREATE TABLE t1 (c enum('a', 'A') BINARY);
 INSERT INTO t1 VALUES ('a'),('A');
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index ef4f65eda1f..1a66f0d91d2 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -40,30 +40,30 @@ KEY (options,flags)
 );
 show full fields from t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-auto	int(5) unsigned	NULL		PRI	NULL	auto_increment	select,insert,update,references	
+auto	int(5) unsigned	NULL	NO	PRI	NULL	auto_increment	select,insert,update,references	
 string	char(10)	latin1_swedish_ci	YES		hello		select,insert,update,references	
-tiny	tinyint(4)	NULL		MUL	0		select,insert,update,references	
-short	smallint(6)	NULL		MUL	1		select,insert,update,references	
-medium	mediumint(8)	NULL		MUL	0		select,insert,update,references	
-long_int	int(11)	NULL			0		select,insert,update,references	
-longlong	bigint(13)	NULL		MUL	0		select,insert,update,references	
-real_float	float(13,1)	NULL		MUL	0.0		select,insert,update,references	
+tiny	tinyint(4)	NULL	NO	MUL	0		select,insert,update,references	
+short	smallint(6)	NULL	NO	MUL	1		select,insert,update,references	
+medium	mediumint(8)	NULL	NO	MUL	0		select,insert,update,references	
+long_int	int(11)	NULL	NO		0		select,insert,update,references	
+longlong	bigint(13)	NULL	NO	MUL	0		select,insert,update,references	
+real_float	float(13,1)	NULL	NO	MUL	0.0		select,insert,update,references	
 real_double	double(16,4)	NULL	YES		NULL		select,insert,update,references	
-utiny	tinyint(3) unsigned	NULL		MUL	0		select,insert,update,references	
-ushort	smallint(5) unsigned zerofill	NULL		MUL	00000		select,insert,update,references	
-umedium	mediumint(8) unsigned	NULL		MUL	0		select,insert,update,references	
-ulong	int(11) unsigned	NULL		MUL	0		select,insert,update,references	
-ulonglong	bigint(13) unsigned	NULL		MUL	0		select,insert,update,references	
+utiny	tinyint(3) unsigned	NULL	NO	MUL	0		select,insert,update,references	
+ushort	smallint(5) unsigned zerofill	NULL	NO	MUL	00000		select,insert,update,references	
+umedium	mediumint(8) unsigned	NULL	NO	MUL	0		select,insert,update,references	
+ulong	int(11) unsigned	NULL	NO	MUL	0		select,insert,update,references	
+ulonglong	bigint(13) unsigned	NULL	NO	MUL	0		select,insert,update,references	
 time_stamp	timestamp	NULL	YES		CURRENT_TIMESTAMP		select,insert,update,references	
 date_field	date	NULL	YES		NULL		select,insert,update,references	
 time_field	time	NULL	YES		NULL		select,insert,update,references	
 date_time	datetime	NULL	YES		NULL		select,insert,update,references	
 blob_col	blob	NULL	YES		NULL		select,insert,update,references	
 tinyblob_col	tinyblob	NULL	YES		NULL		select,insert,update,references	
-mediumblob_col	mediumblob	NULL					select,insert,update,references	
-longblob_col	longblob	NULL					select,insert,update,references	
-options	enum('one','two','tree')	latin1_swedish_ci		MUL	one		select,insert,update,references	
-flags	set('one','two','tree')	latin1_swedish_ci					select,insert,update,references	
+mediumblob_col	mediumblob	NULL	NO				select,insert,update,references	
+longblob_col	longblob	NULL	NO				select,insert,update,references	
+options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	one		select,insert,update,references	
+flags	set('one','two','tree')	latin1_swedish_ci	NO				select,insert,update,references	
 show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t1	0	PRIMARY	1	auto	A	0	NULL	NULL		BTREE	
@@ -208,56 +208,56 @@ Warning	1265	Data truncated for column 'options' at row 6
 update t2 set string="changed" where auto=16;
 show full columns from t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-auto	int(5) unsigned	NULL		MUL	NULL	auto_increment	select,insert,update,references	
+auto	int(5) unsigned	NULL	NO	MUL	NULL	auto_increment	select,insert,update,references	
 string	char(10)	latin1_swedish_ci	YES		new defaul		select,insert,update,references	
-tiny	tinyint(4)	NULL		MUL	0		select,insert,update,references	
-short	smallint(6)	NULL		MUL	0		select,insert,update,references	
-medium	mediumint(8)	NULL		MUL	0		select,insert,update,references	
-long_int	int(11)	NULL			0		select,insert,update,references	
-longlong	bigint(13)	NULL		MUL	0		select,insert,update,references	
-real_float	float(13,1)	NULL		MUL	0.0		select,insert,update,references	
+tiny	tinyint(4)	NULL	NO	MUL	0		select,insert,update,references	
+short	smallint(6)	NULL	NO	MUL	0		select,insert,update,references	
+medium	mediumint(8)	NULL	NO	MUL	0		select,insert,update,references	
+long_int	int(11)	NULL	NO		0		select,insert,update,references	
+longlong	bigint(13)	NULL	NO	MUL	0		select,insert,update,references	
+real_float	float(13,1)	NULL	NO	MUL	0.0		select,insert,update,references	
 real_double	double(16,4)	NULL	YES		NULL		select,insert,update,references	
-utiny	tinyint(3) unsigned	NULL			0		select,insert,update,references	
-ushort	smallint(5) unsigned zerofill	NULL			00000		select,insert,update,references	
-umedium	mediumint(8) unsigned	NULL		MUL	0		select,insert,update,references	
-ulong	int(11) unsigned	NULL		MUL	0		select,insert,update,references	
-ulonglong	bigint(13) unsigned	NULL		MUL	0		select,insert,update,references	
+utiny	tinyint(3) unsigned	NULL	NO		0		select,insert,update,references	
+ushort	smallint(5) unsigned zerofill	NULL	NO		00000		select,insert,update,references	
+umedium	mediumint(8) unsigned	NULL	NO	MUL	0		select,insert,update,references	
+ulong	int(11) unsigned	NULL	NO	MUL	0		select,insert,update,references	
+ulonglong	bigint(13) unsigned	NULL	NO	MUL	0		select,insert,update,references	
 time_stamp	timestamp	NULL	YES		CURRENT_TIMESTAMP		select,insert,update,references	
 date_field	char(10)	latin1_swedish_ci	YES		NULL		select,insert,update,references	
 time_field	time	NULL	YES		NULL		select,insert,update,references	
 date_time	datetime	NULL	YES		NULL		select,insert,update,references	
 new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		select,insert,update,references	
 tinyblob_col	tinyblob	NULL	YES		NULL		select,insert,update,references	
-mediumblob_col	mediumblob	NULL					select,insert,update,references	
-options	enum('one','two','tree')	latin1_swedish_ci		MUL	one		select,insert,update,references	
-flags	set('one','two','tree')	latin1_swedish_ci					select,insert,update,references	
-new_field	char(10)	latin1_swedish_ci			new		select,insert,update,references	
+mediumblob_col	mediumblob	NULL	NO				select,insert,update,references	
+options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	one		select,insert,update,references	
+flags	set('one','two','tree')	latin1_swedish_ci	NO				select,insert,update,references	
+new_field	char(10)	latin1_swedish_ci	NO		new		select,insert,update,references	
 show full columns from t2;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-auto	int(5) unsigned	NULL			0		select,insert,update,references	
+auto	int(5) unsigned	NULL	NO		0		select,insert,update,references	
 string	char(10)	latin1_swedish_ci	YES		new defaul		select,insert,update,references	
-tiny	tinyint(4)	NULL			0		select,insert,update,references	
-short	smallint(6)	NULL			0		select,insert,update,references	
-medium	mediumint(8)	NULL			0		select,insert,update,references	
-long_int	int(11)	NULL			0		select,insert,update,references	
-longlong	bigint(13)	NULL			0		select,insert,update,references	
-real_float	float(13,1)	NULL			0.0		select,insert,update,references	
+tiny	tinyint(4)	NULL	NO		0		select,insert,update,references	
+short	smallint(6)	NULL	NO		0		select,insert,update,references	
+medium	mediumint(8)	NULL	NO		0		select,insert,update,references	
+long_int	int(11)	NULL	NO		0		select,insert,update,references	
+longlong	bigint(13)	NULL	NO		0		select,insert,update,references	
+real_float	float(13,1)	NULL	NO		0.0		select,insert,update,references	
 real_double	double(16,4)	NULL	YES		NULL		select,insert,update,references	
-utiny	tinyint(3) unsigned	NULL			0		select,insert,update,references	
-ushort	smallint(5) unsigned zerofill	NULL			00000		select,insert,update,references	
-umedium	mediumint(8) unsigned	NULL			0		select,insert,update,references	
-ulong	int(11) unsigned	NULL			0		select,insert,update,references	
-ulonglong	bigint(13) unsigned	NULL			0		select,insert,update,references	
+utiny	tinyint(3) unsigned	NULL	NO		0		select,insert,update,references	
+ushort	smallint(5) unsigned zerofill	NULL	NO		00000		select,insert,update,references	
+umedium	mediumint(8) unsigned	NULL	NO		0		select,insert,update,references	
+ulong	int(11) unsigned	NULL	NO		0		select,insert,update,references	
+ulonglong	bigint(13) unsigned	NULL	NO		0		select,insert,update,references	
 time_stamp	timestamp	NULL	YES		0000-00-00 00:00:00		select,insert,update,references	
 date_field	char(10)	latin1_swedish_ci	YES		NULL		select,insert,update,references	
 time_field	time	NULL	YES		NULL		select,insert,update,references	
 date_time	datetime	NULL	YES		NULL		select,insert,update,references	
 new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		select,insert,update,references	
 tinyblob_col	tinyblob	NULL	YES		NULL		select,insert,update,references	
-mediumblob_col	mediumblob	NULL					select,insert,update,references	
-options	enum('one','two','tree')	latin1_swedish_ci			one		select,insert,update,references	
-flags	set('one','two','tree')	latin1_swedish_ci					select,insert,update,references	
-new_field	char(10)	latin1_swedish_ci			new		select,insert,update,references	
+mediumblob_col	mediumblob	NULL	NO				select,insert,update,references	
+options	enum('one','two','tree')	latin1_swedish_ci	NO		one		select,insert,update,references	
+flags	set('one','two','tree')	latin1_swedish_ci	NO				select,insert,update,references	
+new_field	char(10)	latin1_swedish_ci	NO		new		select,insert,update,references	
 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 null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null)));
 auto	auto
 16	16
@@ -268,15 +268,15 @@ drop table t2;
 create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;
 show full columns from t2;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-auto	bigint(17) unsigned	NULL		PRI	0		select,insert,update,references	
-t1	bigint(1)	NULL			0		select,insert,update,references	
-t2	varchar(1)	latin1_swedish_ci					select,insert,update,references	
-t3	varchar(256)	latin1_swedish_ci					select,insert,update,references	
-t4	varbinary(256)	NULL					select,insert,update,references	
-t5	longtext	latin1_swedish_ci					select,insert,update,references	
-t6	longblob	NULL					select,insert,update,references	
-t7	char(0)	latin1_swedish_ci					select,insert,update,references	
-t8	binary(0)	NULL					select,insert,update,references	
+auto	bigint(17) unsigned	NULL	NO	PRI	0		select,insert,update,references	
+t1	bigint(1)	NULL	NO		0		select,insert,update,references	
+t2	varchar(1)	latin1_swedish_ci	NO				select,insert,update,references	
+t3	varchar(256)	latin1_swedish_ci	NO				select,insert,update,references	
+t4	varbinary(256)	NULL	NO				select,insert,update,references	
+t5	longtext	latin1_swedish_ci	NO				select,insert,update,references	
+t6	longblob	NULL	NO				select,insert,update,references	
+t7	char(0)	latin1_swedish_ci	NO				select,insert,update,references	
+t8	binary(0)	NULL	NO				select,insert,update,references	
 select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
 t1	t2	length(t3)	length(t4)	length(t5)	length(t6)	t7	t8
 1	a	256	256	4096	4096		
@@ -297,7 +297,7 @@ show full columns from t3;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 c1	int(11)	NULL	YES		NULL		select,insert,update,references	
 c2	int(11)	NULL	YES		NULL		select,insert,update,references	
-const	bigint(1)	NULL			0		select,insert,update,references	
+const	bigint(1)	NULL	NO		0		select,insert,update,references	
 drop table t1,t2,t3;
 create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
 drop table t1;
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index 1f44ad49812..123967f1c4a 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -34,8 +34,8 @@ select table_name from information_schema.TABLES
 where table_schema = "testtets" and table_name like "t%";
 
 select * from information_schema.STATISTICS where TABLE_SCHEMA = "testtets";
-show keys * where TABLE_SCHEMA Like "test%";
-show keys where INDEX_NAME = "a_data";
+show keys * from t3 where TABLE_SCHEMA Like "test%";
+show keys from t3 where INDEX_NAME = "a_data";
 
 show tables like 't%';
 --replace_column 15 # 16 #
@@ -48,7 +48,7 @@ show full columns from mysql.db like "Insert%";
 show full columns from v1;
 select * from information_schema.COLUMNS where table_name="t1"
 and column_name= "a";
-show columns * where table_name = "t1";
+show columns * from testtets.t1 where table_name = "t1";
 
 drop view v1;
 drop tables testtets.t4, testtets.t1, t2, t3;
@@ -198,7 +198,7 @@ create procedure px5 ()
 begin
 declare v int;
 declare c cursor for select version from
-information_schema.tables;
+information_schema.tables where table_schema <> 'information_schema';
 open c;
 fetch c into v;
 select v;
@@ -250,10 +250,6 @@ from information_schema.columns where table_name = 'proc';
 select * from t115;
 drop table t115;
 
-create view vk as select count(*) from information_schema.tables a;
-select * from vk;
-drop view vk;
-
 delimiter //;
 create procedure p108 () begin declare c cursor for select data_type
 from information_schema.columns;  open c; open c; end;//
@@ -274,3 +270,19 @@ TABLE_NAME= "vo";
 select * from information_schema.KEY_COLUMN_USAGE where
 TABLE_NAME= "vo"; 
 drop view vo;
+
+select TABLE_NAME,TABLE_TYPE,ENGINE
+from information_schema.tables 
+where table_schema='information_schema' limit 2;
+show tables from information_schema like "t%";
+
+--error 1007
+create database information_schema;
+use information_schema;
+show full tables like "T%";
+--error 1109
+create table t1(a int);
+use test;
+show tables;
+use information_schema;
+show tables like "T%";