Bug #85282 inconsistent SUB_PART for SHOW INDEXES
Submitted: 2 Mar 2017 16:06 Modified: 24 Apr 2017 18:48
Reporter: Tor Didriksen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S3 (Non-critical)
Version:8.0.1 OS:Any
Assigned to: CPU Architecture:Any

[2 Mar 2017 16:06] Tor Didriksen
Description:
SHOW INDEXES output is inconsistent, the output of SUB_PART depends on character set in use.

How to repeat:
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) charset latin1;
CREATE TABLE t2 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) charset utf8mb4;

INSERT INTO t1 VALUES('Some data', 'for full-text search');
ANALYZE TABLE t1;
INSERT INTO t2 VALUES('Some data', 'for full-text search');
ANALYZE TABLE t2;

SHOW INDEXES FROM t1;
SHOW INDEXES FROM t2;

mysql> SHOW INDEXES FROM t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t1    | 1          | a        |            1 | a           | NULL      |           1 | NULL     |   NULL | YES  | FULLTEXT   |         |               | YES     |
| t1    | 1          | a        |            2 | b           | NULL      |           1 | 1        |   NULL | YES  | FULLTEXT   |         |               | YES     |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0,01 sec)

mysql> SHOW INDEXES FROM t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t2    | 1          | a        |            1 | a           | NULL      |           1 | NULL     |   NULL | YES  | FULLTEXT   |         |               | YES     |
| t2    | 1          | a        |            2 | b           | NULL      |           1 | 0        |   NULL | YES  | FULLTEXT   |         |               | YES     |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0,01 sec)

For the 'b' part of the index, Sub_part is true for latin1, false for utf8mb4
AND: should the value be NULL for the 'a' part of the index?
[3 Mar 2017 8:35] Tor Didriksen
Posted by developer:
 
5.7 returns NULL for Sub_part for both 'a' and 'b'
[24 Apr 2017 18:48] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.2 release, and here's the changelog entry:

SHOW INDEX output was inconsistent for a FULLTEXT index defined on
multiple columns.