Bug #110384 LIKE query on union result with Unicode characters returns no results
Submitted: 15 Mar 2023 21:55 Modified: 16 Mar 2023 13:53
Reporter: Stephen Nickels Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.32 OS:Windows
Assigned to: CPU Architecture:x86

[15 Mar 2023 21:55] Stephen Nickels 
Description:
Executing a LIKE query on the results of a UNION statement, when the LIKE query search string contains Unicode characters, returns an empty result.

This appears to be a change in behavior, as the query returns expected results in version 8.0.28.

How to repeat:
CREATE TABLE `t1` (`Name` VARCHAR(32) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci');
CREATE TABLE `t2` (`Name` VARCHAR(32) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci');
INSERT INTO `t1` (`Name`) VALUES ('valueテスト');
INSERT INTO `t2` (`Name`) VALUES ('テストvalue');
SELECT `Name` FROM (
    SELECT `Name` FROM `t1`
    UNION
    SELECT `Name` FROM `t2`
) `derived`
WHERE `Name` LIKE '%テスト%';

This sequence of statements returns the inserted values in version 8.0.28, but an empty result in version 8.0.32.
[15 Mar 2023 21:59] Stephen Nickels 
Note that using a non-Unicode search string returns the inserted values in both versions.

CREATE TABLE `t1` (`Name` VARCHAR(32) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci');
CREATE TABLE `t2` (`Name` VARCHAR(32) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci');
INSERT INTO `t1` (`Name`) VALUES ('valueテスト');
INSERT INTO `t2` (`Name`) VALUES ('テストvalue');
SELECT `Name` FROM (
    SELECT `Name` FROM `t1`
    UNION
    SELECT `Name` FROM `t2`
) `derived`
WHERE `Name` LIKE '%value%';
[16 Mar 2023 13:23] MySQL Verification Team 
Hi Mr. Nickels,

Thank you for your bug report.

However, your bug report is a duplicate of the following bug:

https://bugs.mysql.com/bug.php?id=109699

That bug is fixed in 8.0.33, so all you have to do is to wait on 8.0.33 to be released. Nobody knows yet when will it be out. Just check our download pages on dev.mysql.com.

Duplicate.
[16 Mar 2023 14:20] MySQL Verification Team 
Yes, it is reproducible with that set ....