Description:
Table "CountryIndexed" with utf8mb4 charset and indexed column "Name" fails WHERE Name LIKE @Name.
@Name is latin7 charset.
If table has no index on column "Name", like in table "CountryRaw", everything works as expected.
Example contains tables with few values, and 4 SELECT's with "=" or "LIKE" conditions
All SELECT's expected to give result, but 2nd is missing result.
How to repeat:
SET names utf8mb4;
DROP DATABASE IF EXISTS TestCharset;
CREATE DATABASE TestCharset;
USE TestCharset;
CREATE TABLE CountryIndexed (Name CHAR(255), INDEX LK_Name (Name)) ENGINE=InnoDB CHARSET utf8mb4;
INSERT INTO CountryIndexed VALUES ('India'), ('Šri Lanka');
CREATE TABLE CountryRaw (Name CHAR(255)) ENGINE=InnoDB CHARSET utf8mb4;
INSERT INTO CountryRaw VALUES ('India'), ('Šri Lanka');
SET @Name = CONVERT('Šri Lanka' USING latin7);
SELECT CHARSET(@Name), COLLATION(@Name);
SELECT * FROM CountryIndexed WHERE Name = @Name;
#THIS ONE FAILS, THE RESULT IS EXPECTED, NONE IS GIVEN
SELECT * FROM CountryIndexed WHERE Name LIKE @Name;
SELECT * FROM CountryRaw WHERE Name = @Name;
SELECT * FROM CountryRaw WHERE Name LIKE @Name;
Description: Table "CountryIndexed" with utf8mb4 charset and indexed column "Name" fails WHERE Name LIKE @Name. @Name is latin7 charset. If table has no index on column "Name", like in table "CountryRaw", everything works as expected. Example contains tables with few values, and 4 SELECT's with "=" or "LIKE" conditions All SELECT's expected to give result, but 2nd is missing result. How to repeat: SET names utf8mb4; DROP DATABASE IF EXISTS TestCharset; CREATE DATABASE TestCharset; USE TestCharset; CREATE TABLE CountryIndexed (Name CHAR(255), INDEX LK_Name (Name)) ENGINE=InnoDB CHARSET utf8mb4; INSERT INTO CountryIndexed VALUES ('India'), ('Šri Lanka'); CREATE TABLE CountryRaw (Name CHAR(255)) ENGINE=InnoDB CHARSET utf8mb4; INSERT INTO CountryRaw VALUES ('India'), ('Šri Lanka'); SET @Name = CONVERT('Šri Lanka' USING latin7); SELECT CHARSET(@Name), COLLATION(@Name); SELECT * FROM CountryIndexed WHERE Name = @Name; #THIS ONE FAILS, THE RESULT IS EXPECTED, NONE IS GIVEN SELECT * FROM CountryIndexed WHERE Name LIKE @Name; SELECT * FROM CountryRaw WHERE Name = @Name; SELECT * FROM CountryRaw WHERE Name LIKE @Name;