MySQL Bugs: #92287: Inconsistent value querying information

Bug #92287	Inconsistent value querying information_schema.statistics collation column
Submitted:	4 Sep 2018 17:50	Modified:	5 Sep 2018 7:21
Reporter:	Paul Campbell	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.7.23, 5.6.41	OS:	Linux (ubuntu 17.10)
Assigned to:		CPU Architecture:	x86

Description:
Inconsistent representation of NULL in the information_schema.statistics

This column shows inconsistent values when queried as a named column, either separately or in conjunction with other columns, than when it is queried with the the '*' wildcard.

Notwithstanding that indexes always sort Ascending in version 5.7, this column displays NULL when queried as a named column but displays 'A' when queried with the wildcard '*' for all columns.

Definition for information_schema.statistics has his column as VARCHAR(1) and allows NULL, so I assumed that 'A' represented Ascending, however the documentation has the following

<snip>
A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.
</snip>

This suggests that nothing is stored which would be consistent with NULL in the column. 

I appreciate that sort order functionality was only added in 8.0.11 and that querying in that version does return consistent results, however I'm struggling to understand the inconsistency in 5.7 and wondering if this is likely to be fixed?

Version info
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Connection id:		3414
Current database:	test
Current user:		paul@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.23-0ubuntu0.18.04.1-log (Ubuntu)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			1 day 5 hours 56 min 58 sec

How to repeat:
-- using sakila database

mysql> select `collation` from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
collation: NULL
1 row in set (0.00 sec)

mysql> select `column_name`,`collation` from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
column_name: city_id
  collation: NULL
1 row in set (0.00 sec)

mysql> select * from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G 
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: sakila
   TABLE_NAME: address
   NON_UNIQUE: 1
 INDEX_SCHEMA: sakila
   INDEX_NAME: idx_fk_city_id
 SEQ_IN_INDEX: 1
  COLUMN_NAME: city_id
    COLLATION: A
  CARDINALITY: 599
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: 
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT: 
1 row in set (0.00 sec)

Suggested fix:
Fix the wildcard query or update documentation to mention inconsistent output when querying.

Hello Paul,

Thank you for the report!
Verified as described on 5.7.23/5.6.41.

regards,
Umesh

- 5.6.41

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.41: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 `collation` from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
collation: NULL
1 row in set (0.00 sec)

mysql> select `column_name`,`collation` from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
column_name: city_id
  collation: NULL
1 row in set (0.00 sec)

mysql> select * from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: sakila
   TABLE_NAME: address
   NON_UNIQUE: 1
 INDEX_SCHEMA: sakila
   INDEX_NAME: idx_fk_city_id
 SEQ_IN_INDEX: 1
  COLUMN_NAME: city_id
    COLLATION: A
  CARDINALITY: 603
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
1 row in set (0.00 sec)

-- 5.7.23

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.23: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 `collation` from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
collation: NULL
1 row in set (0.00 sec)

mysql> select `column_name`,`collation` from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
column_name: city_id
  collation: NULL
1 row in set (0.00 sec)

mysql> select * from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: sakila
   TABLE_NAME: address
   NON_UNIQUE: 1
 INDEX_SCHEMA: sakila
   INDEX_NAME: idx_fk_city_id
 SEQ_IN_INDEX: 1
  COLUMN_NAME: city_id
    COLLATION: A
  CARDINALITY: 599
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
1 row in set (0.00 sec)

mysql> \s
--------------
bin/mysql  Ver 14.14 Distrib 5.7.23, for el7 (x86_64) using  EditLine wrapper

Connection id:          5
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.23 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql_ushastry.sock
Uptime:                 26 min 22 sec

Threads: 1  Questions: 1129  Slow queries: 0  Opens: 145  Flush tables: 1  Open tables: 127  Queries per second avg: 0.713
--------------

# Issue not observed on 8.0.12

-- 8.0.12

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.12: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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 `collation` from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
COLLATION: A
1 row in set (0.00 sec)

mysql> select `column_name`,`collation` from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
COLUMN_NAME: city_id
  COLLATION: A
1 row in set (0.00 sec)

mysql> select * from information_schema.statistics where table_schema = 'sakila' and table_name = 'address' and index_name = 'idx_fk_city_id'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: sakila
   TABLE_NAME: address
   NON_UNIQUE: 1
 INDEX_SCHEMA: sakila
   INDEX_NAME: idx_fk_city_id
 SEQ_IN_INDEX: 1
  COLUMN_NAME: city_id
    COLLATION: A
  CARDINALITY: 599
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
   IS_VISIBLE: YES
1 row in set (0.02 sec)

Posted by developer:
 
Fixing this bug in an existing GA, might impact applications. So if this is to be fixed in 5.7 or lower versions, this must be carefully considered. The issue is fixed in 8.0, as stated in the report.