| Bug #111176 | View including union does not work when searching for multi-byte values. | ||
|---|---|---|---|
| Submitted: | 28 May 2023 11:56 | Modified: | 29 May 2023 9:19 |
| Reporter: | Ryotaro Shimazu | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | UNION problem, VIEW | ||
[29 May 2023 9:19]
MySQL Verification Team
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
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?