Bug #17852 Incorrect query result on Information Schema tables
Submitted: 2 Mar 2006 9:25 Modified: 7 Jun 2006 13:38
Reporter: Timour Katchaounov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0.19 OS:Windows (Windows)
Assigned to: Sergei Glukhov CPU Architecture:Any

[2 Mar 2006 9:25] Timour Katchaounov
Description:
The following query over I_S tables returns different (incorrect) result
when executed under Windows:

select * from information_schema.statistics join information_schema.columns
using(table_name,column_name) where table_name='user';

The query is introduced in 'join.test' as part of the test for BUG#17523.

The result on Linux is:

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	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
  user	Host	NULL	mysql	0	mysql	PRIMARY	1	A	NULL	NULL	NULL		BTREE		NULL	mysql	1		NO	char	20	60	NULL	NULL	utf8	utf8_bin	char(60)	PRI		select,insert,update,references	
! user	User	NULL	mysql	0	mysql	PRIMARY	2	A	5	NULL	NULL		BTREE		NULL	mysql	2		NO	char	5	16	NULL	NULL	utf8	utf8_bin	char(16)	PRI		select,insert,update,references	

The result on Windows is:

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	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
  user	Host	NULL	mysql	0	mysql	PRIMARY	1	A	NULL	NULL	NULL		BTREE		NULL	mysql	1		NO	char	20	60	NULL	NULL	utf8	utf8_bin	char(60)	PRI		select,insert,update,references	
! user	User	NULL	mysql	0	mysql	PRIMARY	2	A	6	NULL	NULL		BTREE		NULL	mysql	2		NO	char	5	16	NULL	NULL	utf8	utf8_bin	char(16)	PRI		select,insert,update,references	

How to repeat:
Run 'join.test' under Windows.
[7 Jun 2006 13:38] Sergei Glukhov
can't repeat on latest 5.0 tree