bzr commit into mysql-6.0 branch (chad:2845) Bug#35754

[Chad MILLER]

#At file:///Users/cmiller/work/mysqlbzr/6.0-bugteam/

 2845 Chad MILLER	2008-10-03 [merge]
      Merge fix for Bug#35754.
modified:
  mysql-test/r/join.result
  mysql-test/t/join.test
  scripts/mysql_system_tables_data.sql

=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result	2008-08-21 23:38:19 +0000
+++ b/mysql-test/r/join.result	2008-10-03 16:41:23 +0000
@@ -747,78 +747,13 @@ select t1.b from v1a;
 ERROR 42S22: Unknown column 't1.b' in 'field list'
 select * from v1a join v1b on t1.b = t2.b;
 ERROR 42S22: Unknown column 't1.b' in 'on clause'
-select * from information_schema.statistics join information_schema.columns
-using(table_name,column_name) where table_name='user';
-TABLE_NAME	user
-COLUMN_NAME	Host
-TABLE_CATALOG	NULL
-TABLE_SCHEMA	mysql
-NON_UNIQUE	0
-INDEX_SCHEMA	mysql
-INDEX_NAME	PRIMARY
-SEQ_IN_INDEX	1
-COLLATION	A
-CARDINALITY	#
-SUB_PART	NULL
-PACKED	NULL
-NULLABLE	
-INDEX_TYPE	BTREE
-COMMENT	
-INDEX_COMMENT	
-TABLE_CATALOG	NULL
-TABLE_SCHEMA	mysql
-ORDINAL_POSITION	1
-COLUMN_DEFAULT	
-IS_NULLABLE	NO
-DATA_TYPE	char
-CHARACTER_MAXIMUM_LENGTH	60
-CHARACTER_OCTET_LENGTH	240
-NUMERIC_PRECISION	NULL
-NUMERIC_SCALE	NULL
-CHARACTER_SET_NAME	utf8
-COLLATION_NAME	utf8_bin
-COLUMN_TYPE	char(60)
-COLUMN_KEY	PRI
-EXTRA	
-PRIVILEGES	#
-COLUMN_COMMENT	
-STORAGE	Default
-FORMAT	Default
-TABLE_NAME	user
-COLUMN_NAME	User
-TABLE_CATALOG	NULL
-TABLE_SCHEMA	mysql
-NON_UNIQUE	0
-INDEX_SCHEMA	mysql
-INDEX_NAME	PRIMARY
-SEQ_IN_INDEX	2
-COLLATION	A
-CARDINALITY	#
-SUB_PART	NULL
-PACKED	NULL
-NULLABLE	
-INDEX_TYPE	BTREE
-COMMENT	
-INDEX_COMMENT	
-TABLE_CATALOG	NULL
-TABLE_SCHEMA	mysql
-ORDINAL_POSITION	2
-COLUMN_DEFAULT	
-IS_NULLABLE	NO
-DATA_TYPE	char
-CHARACTER_MAXIMUM_LENGTH	16
-CHARACTER_OCTET_LENGTH	64
-NUMERIC_PRECISION	NULL
-NUMERIC_SCALE	NULL
-CHARACTER_SET_NAME	utf8
-COLLATION_NAME	utf8_bin
-COLUMN_TYPE	char(16)
-COLUMN_KEY	PRI
-EXTRA	
-PRIVILEGES	#
-COLUMN_COMMENT	
-STORAGE	Default
-FORMAT	Default
+select 
+statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.CARDINALITY, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, 
+columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_NULLABLE, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.CHARACTER_OCTET_LENGTH, columns.NUMERIC_PRECISION, columns.NUMERIC_SCALE, columns.CHARACTER_SET_NAME, columns.COLLATION_NAME, columns.COLUMN_TYPE, columns.COLUMN_KEY, columns.EXTRA, columns.COLUMN_COMMENT
+from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user';
+TABLE_NAME	COLUMN_NAME	TABLE_CATALOG	TABLE_SCHEMA	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	TABLE_CATALOG	TABLE_SCHEMA	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	COLUMN_COMMENT
+user	Host	NULL	mysql	0	mysql	PRIMARY	1	A	NULL	NULL	NULL		BTREE		NULL	mysql		NO	char	60	240	NULL	NULL	utf8	utf8_bin	char(60)	PRI		
+user	User	NULL	mysql	0	mysql	PRIMARY	2	A	3	NULL	NULL		BTREE		NULL	mysql		NO	char	16	64	NULL	NULL	utf8	utf8_bin	char(16)	PRI		
 drop table t1;
 drop table t2;
 drop table t3;

=== modified file 'mysql-test/t/join.test'
--- a/mysql-test/t/join.test	2008-08-21 23:38:19 +0000
+++ b/mysql-test/t/join.test	2008-10-03 16:41:23 +0000
@@ -546,11 +546,12 @@ select * from v1a join v1b on t1.b = t2.
 #
 # Bug #17523 natural join and information_schema
 #
-# We mask out the Privileges column because it differs with embedded server
---replace_column 32 # 10 #
-query_vertical 
-select * from information_schema.statistics join information_schema.columns
-              using(table_name,column_name) where table_name='user';
+# Omit columns.PRIVILIGES as it may vary with embedded server.
+# Omit columns.ORDINAL_POSITION as it may vary with hostname='localhost'.
+select 
+ statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.CARDINALITY, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, 
+ columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_NULLABLE, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.CHARACTER_OCTET_LENGTH, columns.NUMERIC_PRECISION, columns.NUMERIC_SCALE, columns.CHARACTER_SET_NAME, columns.COLLATION_NAME, columns.COLUMN_TYPE, columns.COLUMN_KEY, columns.EXTRA, columns.COLUMN_COMMENT
+ from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user';
 
 drop table t1;
 drop table t2;

=== modified file 'scripts/mysql_system_tables_data.sql'
--- a/scripts/mysql_system_tables_data.sql	2008-07-24 07:44:21 +0000
+++ b/scripts/mysql_system_tables_data.sql	2008-10-03 16:41:23 +0000
@@ -22,9 +22,9 @@ DROP TABLE tmp_db;
 CREATE TEMPORARY TABLE tmp_user LIKE user;
 set @current_hostname= @@hostname;
 INSERT INTO tmp_user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
-REPLACE INTO tmp_user VALUES (@current_hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
+REPLACE INTO tmp_user SELECT @current_hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0 FROM dual WHERE LOWER( @current_hostname) != 'localhost';
 REPLACE INTO tmp_user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
 INSERT INTO tmp_user (host,user) VALUES ('localhost','');
-INSERT INTO tmp_user (host,user) VALUES (@current_hostname,'');
+INSERT INTO tmp_user (host,user) SELECT @current_hostname,'' FROM dual WHERE LOWER(@current_hostname ) != 'localhost';
 INSERT INTO user SELECT * FROM tmp_user WHERE @had_user_table=0;
 DROP TABLE tmp_user;