Bug #111363 If you use "UNION ALL" for items with Japanese double-byte characters in a subqu

• Submitted: 12 Jun 2023 9:24
• Modified: 12 Jun 2023 12:30
• Reporter: Yuya Matsuo
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Windows
• CPU Architecture: Any

[12 Jun 2023 9:24] Yuya Matsuo

Description:
If you use "UNION ALL" for items with Japanese double-byte characters in a subqu

How to repeat:
Prepare the table and data with the following SQL.
CREATE TABLE TEST01(A VARCHAR (4)) ;
INSERT INTO TEST01(A) VALUES ('あ'); 

Data cannot be extracted with the following SQL.
SELECT
    * 
FROM
    ( 
        SELECT
            A 
        FROM
            TEST01 
        UNION ALL 
        SELECT
            A 
        FROM
            TEST01
    ) SUB 
WHERE
    SUB.A = 'あ'
;

[12 Jun 2023 9:57] MySQL Verification Team

Hello Yuya Matsuo,

Thank you for the report and test case.
IMHO this is duplicate of Bug #109699, which is fixed in  MySQL Server 8.0.33. Please see Bug #109699 for more details.

regards,
Umesh

[12 Jun 2023 9:58] MySQL Verification Team

- fixed in 8.0.33

 ./mtr bug111363 --nocheck-testcases
Logging: ./mtr  bug111363 --nocheck-testcases
MySQL Version 8.0.33
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE TEST01(A VARCHAR (4)) ;
INSERT INTO TEST01(A) VALUES ('あ');
SELECT
*
FROM
(
SELECT
A
FROM
TEST01
UNION ALL
SELECT
A
FROM
TEST01
) SUB
WHERE
SUB.A = 'あ'
;
A
あ
あ

- 8.0.32 affected 

./mtr bug111363 --nocheck-testcases
Logging: ./mtr  bug111363 --nocheck-testcases
MySQL Version 8.0.32
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.32/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE TEST01(A VARCHAR (4)) ;
INSERT INTO TEST01(A) VALUES ('あ');
SELECT
*
FROM
(
SELECT
A
FROM
TEST01
UNION ALL
SELECT
A
FROM
TEST01
) SUB
WHERE
SUB.A = 'あ'
;
A
Warnings:
Warning 1300    Cannot convert string '\xE3\x81\x82' from utf8mb4 to binary
Warning 1300    Cannot convert string '\xE3\x81\x82' from utf8mb4 to binary

- 8.0.32 with workaround
 ./mtr bug111363 --nocheck-testcases
Logging: ./mtr  bug111363 --nocheck-testcases
MySQL Version 8.0.32
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.32/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
set optimizer_switch="derived_condition_pushdown=off";
CREATE TABLE TEST01(A VARCHAR (4)) ;
INSERT INTO TEST01(A) VALUES ('あ');
SELECT
*
FROM
(
SELECT
A
FROM
TEST01
UNION ALL
SELECT
A
FROM
TEST01
) SUB
WHERE
SUB.A = 'あ'
;
A
あ
あ

[12 Jun 2023 12:30] Yuya Matsuo

Thank you for your reply.
We have confirmed that the issue has been resolved by upgrading to "Ver: 8.0.33".