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?
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?