| 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: | |
| 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 | |
[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.
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.