Bug #106099 Case sensitivity not recognised when adding unique index to re-collated column

• Submitted: 7 Jan 2022 13:35
• Modified: 7 Jan 2022 14:21
• Reporter: Ben Lydiard
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Windows (MS Windows 10 Home)
• CPU Architecture: Any
• Tags: case sensitivity, collate, unique index

[7 Jan 2022 13:35] Ben Lydiard

Description:
When I execute the following statements in MySQL Workbench 8.0 CE (with MySQL version 8.0.21) I get the following error message:
Error Code: 1062. Duplicate entry 'Foo-bar' for key 'testtable.idx'
Whereas both of these insertions should be seen as unique if col2 is case sensitive.

If I uncomment the OPTIMIZE TABLE line, there is no error and all statements run correctly (don't know why).

In any case, the issue does not occur when using the MySQL command line client, hence I am assuming it is a Workbench problem.

How to repeat:
DROP TABLE IF EXISTS testTable;
CREATE TABLE `testTable` (
   `col1` int,
   `col2` varchar(45) COLLATE utf8mb4_0900_ai_ci,
   `col3` varchar(45) COLLATE utf8mb4_0900_as_cs,
   PRIMARY KEY (`col1`)
 ) ENGINE=InnoDB;
 
ALTER TABLE testTable modify `col2` varchar(45) COLLATE utf8mb4_0900_as_cs; # Make col2 case-sensitive
CREATE UNIQUE INDEX idx ON testTable(col2, col3); 
#OPTIMIZE TABLE testTable;
INSERT INTO testTable(col1,col2,col3) VALUES (1,"foo", "bar");
INSERT INTO testTable(col1,col2,col3) VALUES (2,"Foo", "bar");

[7 Jan 2022 13:42] Ben Lydiard

Correction: Bug DOES occur when using MySQL Command Line Client, ie would appear to be a Server bug rather than Workbench bug.

[7 Jan 2022 14:21] MySQL Verification Team

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> USE test
Database changed
mysql> DROP TABLE IF EXISTS testTable;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `testTable` (
    ->    `col1` int,
    ->    `col2` varchar(45) COLLATE utf8mb4_0900_ai_ci,
    ->    `col3` varchar(45) COLLATE utf8mb4_0900_as_cs,
    ->    PRIMARY KEY (`col1`)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> ALTER TABLE testTable modify `col2` varchar(45) COLLATE utf8mb4_0900_as_cs; # Make col2 case-sensitive
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE UNIQUE INDEX idx ON testTable(col2, col3);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> #OPTIMIZE TABLE testTable;
mysql> INSERT INTO testTable(col1,col2,col3) VALUES (1,"foo", "bar");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO testTable(col1,col2,col3) VALUES (2,"Foo", "bar");
Query OK, 1 row affected (0.01 sec)

mysql>