Bug #65121 Inconsistent results for select on INFORMATION_SCHEMA.STATISTICS
Submitted: 26 Apr 2012 18:54 Modified: 13 Oct 2016 15:37
Reporter: Andre Widhani Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1, 5.5 OS:Linux (RHEL 5, RHEL 6)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[26 Apr 2012 18:54] Andre Widhani
Description:
select on INFORMATION_SCHEMA.STATISTICS returns different results depending on column list - "select * ..." and "select COLLATION ..." return different values for column COLLATION.

This might happen with other tables or columns as well, haven't tried. I noticed it with the queries below.

I rated this bug as "non-critical" assuming this is a special case for that particular table and column. Obviously the severity needs to be increased if this inconsistency could happen un user tables as well.

How to repeat:
mysql> select * from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = 'mysql' and TABLE_NAME = 'user' and column_name = 'Host'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: mysql
   TABLE_NAME: user
   NON_UNIQUE: 0
 INDEX_SCHEMA: mysql
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: Host
    COLLATION: A
  CARDINALITY: NULL
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: 
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT: 
1 row in set (0.00 sec)

mysql> select COLLATION from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = 'mysql' and TABLE_NAME = 'user' and column_name = 'Host'\G
*************************** 1. row ***************************
COLLATION: NULL
1 row in set (0.00 sec)

Suggested fix:
Both queries should return the same value for column COLLATION.
[26 Apr 2012 19:01] Valeriy Kravchuk
Thank you for the bug report. Verified just as described:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = 'mysql' and TABLE_NAME = 'user' and column_name = 'Host'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: mysql
   TABLE_NAME: user
   NON_UNIQUE: 0
 INDEX_SCHEMA: mysql
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: Host
    COLLATION: A
  CARDINALITY: NULL
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: 
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT: 
1 row in set (0.07 sec)

mysql> select COLLATION from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = 'mysql' and TABLE_NAME = 'user' and column_name = 'Host'\G
*************************** 1. row ***************************
COLLATION: NULL
1 row in set (0.00 sec)
[13 Oct 2016 15:37] Paul Dubois
Posted by developer:
 
Noted in 8.0.0 changelog.

Queries on INFORMATION_SCHEMA.STATISTICS could return different
results depending on the order of columns in the select list.