Description:
A functional key part stores the value of its expression as evaluated at write
time. When the expression contains a comparison against the indexed column, that
value depends on the column's collation.
ALTER TABLE ... MODIFY <col> ... COLLATE <new> rebuilds the table and its
ordinary indexes, but does not recompute the values held in a functional index
whose expression depends on the changed collation. Two consequences follow.
(1) With a UNIQUE functional index, the uniqueness validation that the same ALTER
performs for ordinary unique indexes is skipped. The ALTER is accepted with no
warning, and the index is left holding two rows that it considers equal. It
continues to enforce the constraint against new rows, so it is simultaneously
enforcing and violated. CHECK TABLE ... EXTENDED reports OK, ALTER TABLE ...
ENGINE=InnoDB does not detect or repair it, and mysqldump/restore fails.
(2) With a non-unique functional index, queries whose plan uses it return wrong
results.
The contrast within a single ALTER is the clearest statement of the problem: for
an ordinary unique index the server correctly refuses, and for a unique
functional index in the identical situation it accepts silently.
This is the same underlying issue as a separate report about timezone- and
locale-dependent expressions in generated columns and functional key parts: a
materialized expression value is not invalidated when the semantic context it was
computed under changes. Here the context is the base column's collation rather
than a session variable.
How to repeat:
--------------------------------------------------------------------------------
How to repeat, part 1: uniqueness validation skipped, table not restorable
--------------------------------------------------------------------------------
For an ordinary unique index the ALTER is correctly refused:
DROP DATABASE IF EXISTS b2; CREATE DATABASE b2; USE b2;
CREATE TABLE ord(id INT PRIMARY KEY, c VARCHAR(32) COLLATE utf8mb4_bin,
UNIQUE KEY u(c)) ENGINE=InnoDB;
INSERT INTO ord VALUES (1,'a'),(2,'A');
ALTER TABLE ord MODIFY c VARCHAR(32) COLLATE utf8mb4_0900_ai_ci;
ERROR 1062 (23000): Duplicate entry 'A' for key 'ord.u'
With a unique functional index, the identical situation is accepted:
CREATE TABLE ut(id INT PRIMARY KEY, c VARCHAR(32) COLLATE utf8mb4_bin,
UNIQUE KEY u((CONCAT(c,'')))) ENGINE=InnoDB;
INSERT INTO ut VALUES (1,'a'),(2,'A');
ALTER TABLE ut MODIFY c VARCHAR(32) COLLATE utf8mb4_0900_ai_ci;
SHOW WARNINGS;
-- Empty set
SELECT id, c FROM ut ORDER BY id;
+----+------+
| id | c |
+----+------+
| 1 | a |
| 2 | A |
+----+------+
Under utf8mb4_0900_ai_ci these two rows are equal, so the unique index is
violated by its own contents. It nevertheless still enforces the constraint
against new rows:
INSERT INTO ut VALUES (3,'A');
ERROR 1062 (23000): Duplicate entry 'A' for key 'ut.u'
Nothing reports or repairs it:
CHECK TABLE ut EXTENDED;
+-------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------+-------+----------+----------+
| b2.ut | check | status | OK |
+-------+-------+----------+----------+
ALTER TABLE ut ENGINE=InnoDB; -- succeeds
INSERT INTO ut VALUES (4,'A');
ERROR 1062 (23000): Duplicate entry 'A' for key 'ut.u'
SELECT id, c FROM ut ORDER BY id; -- 1,'a' and 2,'A' are both still present
The table cannot be restored from its own logical backup:
$ mysqldump --set-gtid-purged=OFF --skip-comments b2 ut | mysql ... b2r
ERROR 1062 (23000) at line 25: Duplicate entry 'A' for key 'ut.u'
mysql> SELECT COUNT(*) FROM b2r.ut;
0
The restore aborts and the target table is left empty.
The same is observed with UNIQUE KEY u((SUBSTRING(c,1))).
--------------------------------------------------------------------------------
How to repeat, part 2: wrong results with a non-unique functional index
--------------------------------------------------------------------------------
CREATE TABLE t (
id INT PRIMARY KEY,
c VARCHAR(32) COLLATE utf8mb4_bin,
INDEX f((CASE WHEN c='rare' THEN 1 ELSE 0 END))
) ENGINE=InnoDB;
INSERT INTO t VALUES (1,'rare'),(2,'RARE');
ALTER TABLE t MODIFY c VARCHAR(32) COLLATE utf8mb4_0900_ai_ci;
SHOW WARNINGS;
-- Empty set
Under the new collation both rows satisfy c='rare', so the expression is 1 for
both and the correct answer is {1,2}:
SELECT id, c, c='rare' AS eq FROM t;
+----+------+------+
| id | c | eq |
+----+------+------+
| 1 | rare | 1 |
| 2 | RARE | 1 |
+----+------+------+
SELECT id FROM t WHERE (CASE WHEN c='rare' THEN 1 ELSE 0 END)=1;
+----+
| id |
+----+
| 1 |
+----+
SELECT id FROM t IGNORE INDEX(f) WHERE (CASE WHEN c='rare' THEN 1 ELSE 0 END)=1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
EXPLAIN FORMAT=TREE SELECT id FROM t WHERE (CASE WHEN c='rare' THEN 1 ELSE 0 END)=1;
-> Index lookup on t using f ((case when (c = _utf8mb4'rare') then 1 else 0 end) = 1)
(cost=0.35 rows=1)
CHECK TABLE t EXTENDED; -- status OK
Forcing a rebuild restores the correct answer, confirming the index content is
the stale part:
ALTER TABLE t ENGINE=InnoDB;
SELECT id FROM t WHERE (CASE WHEN c='rare' THEN 1 ELSE 0 END)=1; -- 1, 2
Note that the rebuild repairs the non-unique case but not the unique case in
part 1, where the duplicate rows survive it.
Suggested fix:
When ALTER TABLE changes a column's collation, recompute every functional key
part and every generated column whose expression references that column, in the
same way ordinary indexes on the column are rebuilt, and apply the same
uniqueness validation that an ordinary UNIQUE index receives. Alternatively,
reject the ALTER with a message asking for an explicit rebuild, rather than
leaving an index whose contents no longer match the table data and whose
constraint no longer holds.
Description: A functional key part stores the value of its expression as evaluated at write time. When the expression contains a comparison against the indexed column, that value depends on the column's collation. ALTER TABLE ... MODIFY <col> ... COLLATE <new> rebuilds the table and its ordinary indexes, but does not recompute the values held in a functional index whose expression depends on the changed collation. Two consequences follow. (1) With a UNIQUE functional index, the uniqueness validation that the same ALTER performs for ordinary unique indexes is skipped. The ALTER is accepted with no warning, and the index is left holding two rows that it considers equal. It continues to enforce the constraint against new rows, so it is simultaneously enforcing and violated. CHECK TABLE ... EXTENDED reports OK, ALTER TABLE ... ENGINE=InnoDB does not detect or repair it, and mysqldump/restore fails. (2) With a non-unique functional index, queries whose plan uses it return wrong results. The contrast within a single ALTER is the clearest statement of the problem: for an ordinary unique index the server correctly refuses, and for a unique functional index in the identical situation it accepts silently. This is the same underlying issue as a separate report about timezone- and locale-dependent expressions in generated columns and functional key parts: a materialized expression value is not invalidated when the semantic context it was computed under changes. Here the context is the base column's collation rather than a session variable. How to repeat: -------------------------------------------------------------------------------- How to repeat, part 1: uniqueness validation skipped, table not restorable -------------------------------------------------------------------------------- For an ordinary unique index the ALTER is correctly refused: DROP DATABASE IF EXISTS b2; CREATE DATABASE b2; USE b2; CREATE TABLE ord(id INT PRIMARY KEY, c VARCHAR(32) COLLATE utf8mb4_bin, UNIQUE KEY u(c)) ENGINE=InnoDB; INSERT INTO ord VALUES (1,'a'),(2,'A'); ALTER TABLE ord MODIFY c VARCHAR(32) COLLATE utf8mb4_0900_ai_ci; ERROR 1062 (23000): Duplicate entry 'A' for key 'ord.u' With a unique functional index, the identical situation is accepted: CREATE TABLE ut(id INT PRIMARY KEY, c VARCHAR(32) COLLATE utf8mb4_bin, UNIQUE KEY u((CONCAT(c,'')))) ENGINE=InnoDB; INSERT INTO ut VALUES (1,'a'),(2,'A'); ALTER TABLE ut MODIFY c VARCHAR(32) COLLATE utf8mb4_0900_ai_ci; SHOW WARNINGS; -- Empty set SELECT id, c FROM ut ORDER BY id; +----+------+ | id | c | +----+------+ | 1 | a | | 2 | A | +----+------+ Under utf8mb4_0900_ai_ci these two rows are equal, so the unique index is violated by its own contents. It nevertheless still enforces the constraint against new rows: INSERT INTO ut VALUES (3,'A'); ERROR 1062 (23000): Duplicate entry 'A' for key 'ut.u' Nothing reports or repairs it: CHECK TABLE ut EXTENDED; +-------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------+-------+----------+----------+ | b2.ut | check | status | OK | +-------+-------+----------+----------+ ALTER TABLE ut ENGINE=InnoDB; -- succeeds INSERT INTO ut VALUES (4,'A'); ERROR 1062 (23000): Duplicate entry 'A' for key 'ut.u' SELECT id, c FROM ut ORDER BY id; -- 1,'a' and 2,'A' are both still present The table cannot be restored from its own logical backup: $ mysqldump --set-gtid-purged=OFF --skip-comments b2 ut | mysql ... b2r ERROR 1062 (23000) at line 25: Duplicate entry 'A' for key 'ut.u' mysql> SELECT COUNT(*) FROM b2r.ut; 0 The restore aborts and the target table is left empty. The same is observed with UNIQUE KEY u((SUBSTRING(c,1))). -------------------------------------------------------------------------------- How to repeat, part 2: wrong results with a non-unique functional index -------------------------------------------------------------------------------- CREATE TABLE t ( id INT PRIMARY KEY, c VARCHAR(32) COLLATE utf8mb4_bin, INDEX f((CASE WHEN c='rare' THEN 1 ELSE 0 END)) ) ENGINE=InnoDB; INSERT INTO t VALUES (1,'rare'),(2,'RARE'); ALTER TABLE t MODIFY c VARCHAR(32) COLLATE utf8mb4_0900_ai_ci; SHOW WARNINGS; -- Empty set Under the new collation both rows satisfy c='rare', so the expression is 1 for both and the correct answer is {1,2}: SELECT id, c, c='rare' AS eq FROM t; +----+------+------+ | id | c | eq | +----+------+------+ | 1 | rare | 1 | | 2 | RARE | 1 | +----+------+------+ SELECT id FROM t WHERE (CASE WHEN c='rare' THEN 1 ELSE 0 END)=1; +----+ | id | +----+ | 1 | +----+ SELECT id FROM t IGNORE INDEX(f) WHERE (CASE WHEN c='rare' THEN 1 ELSE 0 END)=1; +----+ | id | +----+ | 1 | | 2 | +----+ EXPLAIN FORMAT=TREE SELECT id FROM t WHERE (CASE WHEN c='rare' THEN 1 ELSE 0 END)=1; -> Index lookup on t using f ((case when (c = _utf8mb4'rare') then 1 else 0 end) = 1) (cost=0.35 rows=1) CHECK TABLE t EXTENDED; -- status OK Forcing a rebuild restores the correct answer, confirming the index content is the stale part: ALTER TABLE t ENGINE=InnoDB; SELECT id FROM t WHERE (CASE WHEN c='rare' THEN 1 ELSE 0 END)=1; -- 1, 2 Note that the rebuild repairs the non-unique case but not the unique case in part 1, where the duplicate rows survive it. Suggested fix: When ALTER TABLE changes a column's collation, recompute every functional key part and every generated column whose expression references that column, in the same way ordinary indexes on the column are rebuilt, and apply the same uniqueness validation that an ordinary UNIQUE index receives. Alternatively, reject the ALTER with a message asking for an explicit rebuild, rather than leaving an index whose contents no longer match the table data and whose constraint no longer holds.