Bug #111176 View including union does not work when searching for multi-byte values.

Description:
When searching for multi-byte values for a column within a view including "union", it does not work as expected. In 8.0.31, there was no problem. And in 8.0.33, it does not show this bug.

How to repeat:
1. show variables like 'char%';

Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8mb4
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8mb4
character_set_system	utf8mb3
character_sets_dir	C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\

select version();
+-----------------------+
|version()              |
+-----------------------+
|8.0.32                 |
+-----------------------+

2. Create a view including 'union'.
create table test1 (name varchar(50));
create table test2 (name varchar(50));
create view test_view10 as select name from test1 union select name from test2;

3.
insert into test1 (name) values('あいうえおa');
insert into test2 (name) values('あかさたなa');

4. There is no problem so far.

select * from test_view10;
+-----------------------+
|name                   |
+-----------------------+
|あいうえおa            |
+-----------------------+
|かきくけこa           |
+-----------------------+

5. The bugs are as follows:

select * from test_view10 where name like 'あ%';
+-----------------------+
|name                   |
+-----------------------+
|(No result)            |
+-----------------------+

The above query should show two results;

select * from test_view10 where name not like 'あ%';
+-----------------------+
|name                   |
+-----------------------+
|あいうえおa            |
+-----------------------+
|かきくけこa           |
+-----------------------+

Instead, the above query should show no result;

select * from test_view10 where name like '%a';
+-----------------------+
|name                   |
+-----------------------+
|あいうえおa            |
+-----------------------+
|かきくけこa           |
+-----------------------+

ASCII values are not susceptible to this bug.

select hex(name) from test_view10;
+--------------------------------+
|name                            |
+--------------------------------+
|E38182E38184E38186E38188E3818A61|
+--------------------------------+
|E38182E3818BE38195E3819FE381AA61|
+--------------------------------+

select * from test_view10 where name = 0xE38182E38184E38186E38188E3818A61;
+-----------------------+
|name                   |
+-----------------------+
|あいうえおa            |
+-----------------------+

When designating values as hex values for a query, it shows no problem at all.

6. (For your information) windows locale
C:\>chcp
Active code page: 932

Suggested fix:
According to changes in 8.0.33, it says:

When preparing a view query, the operation used the system character set (instead of the character set stored in data dictionary) and then reported an invalid character-string error. (Bug #34800905)

Is the above bug fix related to this bug as well?

Hello Ryotaro Shimazu,

Thank you for the report and test case.
This is most likely fixed after Bug #109699, please see Bug #109699

- 8.0.33 - not affected

- 8.0.33

./mtr bug111176 --nocheck-testcases
Logging: ./mtr  bug111176 --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 test1 (name varchar(50));
create table test2 (name varchar(50));
create view test_view10 as select name from test1 union select name from test2;
insert into test1 (name) values('あいうえおa');
insert into test2 (name) values('あかさたなa');
select * from test_view10;
name
あいうえおa
あかさたなa
select * from test_view10 where name like 'あ%';
name
あいうえおa
あかさたなa
select * from test_view10 where name not like 'あ%';
name
select * from test_view10 where name like '%a';
name
あいうえおa
あかさたなa
select hex(name) from test_view10;
hex(name)
E38182E38184E38186E38188E3818A61
E38182E3818BE38195E3819FE381AA61
select * from test_view10 where name = 0xE38182E38184E38186E38188E3818A61;
name
あいうえおa

- 8.0.32 - affected

- 8.0.32

 ./mtr bug111176 --nocheck-testcases
Logging: ./mtr  bug111176 --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 test1 (name varchar(50));
create table test2 (name varchar(50));
create view test_view10 as select name from test1 union select name from test2;
insert into test1 (name) values('あいうえおa');
insert into test2 (name) values('あかさたなa');
select * from test_view10;
name
あいうえおa
あかさたなa
select * from test_view10 where name like 'あ%';
name
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
select * from test_view10 where name not like 'あ%';
name
あいうえお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
select * from test_view10 where name like '%a';
name
あいうえおa
あかさたなa
select hex(name) from test_view10;
hex(name)
E38182E38184E38186E38188E3818A61
E38182E3818BE38195E3819FE381AA61
select * from test_view10 where name = 0xE38182E38184E38186E38188E3818A61;
name
あいうえおa

- 8.0.32 - with workaround - set optimizer_switch="derived_condition_pushdown=off";

 ./mtr bug111176 --nocheck-testcases
Logging: ./mtr  bug111176 --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 test1 (name varchar(50));
create table test2 (name varchar(50));
create view test_view10 as select name from test1 union select name from test2;
insert into test1 (name) values('あいうえおa');
insert into test2 (name) values('あかさたなa');
select * from test_view10;
name
あいうえおa
あかさたなa
select * from test_view10 where name like 'あ%';
name
あいうえおa
あかさたなa
select * from test_view10 where name not like 'あ%';
name
select * from test_view10 where name like '%a';
name
あいうえおa
あかさたなa
select hex(name) from test_view10;
hex(name)
E38182E38184E38186E38188E3818A61
E38182E3818BE38195E3819FE381AA61
select * from test_view10 where name = 0xE38182E38184E38186E38188E3818A61;
name
あいうえおa

I suggest you to upgrade to 8.0.33 as we don't fix bugs in old versions, don't backport. Thank you.

regards,
Umesh