| Bug #116762 | Optimizer gives 'Impossible where' without considering field collation | ||
|---|---|---|---|
| Submitted: | 24 Nov 2024 9:01 | Modified: | 3 Dec 2024 22:44 |
| Reporter: | Brian Yue (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.36, 8.0.40, 8.4.3 | OS: | Any (Redhat 7.4) |
| Assigned to: | CPU Architecture: | Any (Intel) | |
| Tags: | collation_connection, impossible where, Optimizer | ||
[26 Nov 2024 6:25]
MySQL Verification Team
Hello Brian Yue, Thank you for the report and test case. Verified as described. regards, Umesh
[26 Nov 2024 6:25]
MySQL Verification Team
-- 8.0.40 ./mtr --nocheck-testcases bug116762 Logging: ./mtr --nocheck-testcases bug116762 MySQL Version 8.0.40 Checking supported features Using 'all' suites Collecting tests Checking leftover processes Removing old var directory Creating var directory '/export/home/tmp/ushastry/mysql-8.0.40/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ CREATE TABLE t_varchar ( id int NOT NULL AUTO_INCREMENT, c1 varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; insert into t_varchar (c1) values ('abc'); insert into t_varchar (c1) values ('abc '); insert into t_varchar (c1) values ('abc '); set names utf8mb4 collate utf8mb4_0900_ai_ci; select *,length(c1) from t_varchar where c1 = 'abc'; id c1 length(c1) 7 abc 3 8 abc 4 9 abc 5 select *,length(c1) from t_varchar where c1 = 'abc '; id c1 length(c1) 7 abc 3 8 abc 4 9 abc 5 select *,length(c1) from t_varchar where c1 = 'abc' and c1 = 'abc '; id c1 length(c1) EXPLAIn select *,length(c1) from t_varchar where c1 = 'abc' and c1 = 'abc '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 /* select#1 */ select `test`.`t_varchar`.`id` AS `id`,`test`.`t_varchar`.`c1` AS `c1`,length(`test`.`t_varchar`.`c1`) AS `length(c1)` from `test`.`t_varchar` where false [ 50%] main.bug116762 [ pass ] 95 [100%] shutdown_report [ pass ]
[26 Nov 2024 6:26]
MySQL Verification Team
-- 8.4.3 ./mtr --nocheck-testcases bug116762 Logging: ./mtr --nocheck-testcases bug116762 MySQL Version 8.4.3 Checking supported features Using 'all' suites Collecting tests Checking leftover processes Removing old var directory Creating var directory '/export/home/tmp/ushastry/mysql-8.4.3/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ CREATE TABLE t_varchar ( id int NOT NULL AUTO_INCREMENT, c1 varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; insert into t_varchar (c1) values ('abc'); insert into t_varchar (c1) values ('abc '); insert into t_varchar (c1) values ('abc '); set names utf8mb4 collate utf8mb4_0900_ai_ci; select *,length(c1) from t_varchar where c1 = 'abc'; id c1 length(c1) 7 abc 3 8 abc 4 9 abc 5 select *,length(c1) from t_varchar where c1 = 'abc '; id c1 length(c1) 7 abc 3 8 abc 4 9 abc 5 select *,length(c1) from t_varchar where c1 = 'abc' and c1 = 'abc '; id c1 length(c1) EXPLAIn select *,length(c1) from t_varchar where c1 = 'abc' and c1 = 'abc '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 /* select#1 */ select `test`.`t_varchar`.`id` AS `id`,`test`.`t_varchar`.`c1` AS `c1`,length(`test`.`t_varchar`.`c1`) AS `length(c1)` from `test`.`t_varchar` where false [ 50%] main.bug116762 [ pass ] 34 [100%] shutdown_report [ pass ]
[28 Nov 2024 11:17]
Roy Lyseng
Posted by developer:
This problem was fixed in 9.0 by the following patch:
commit bac50a0a4812375a07a2dc8763f8050b8a98f1f9
Bug#36137690: Item_func_conv_charset::eq not correct
Part 2: Remove the binary_cmp argument to Item::eq()
[3 Dec 2024 22:44]
Jon Stephens
Fixed in MySQL 9.0.0 by BUG#113506. Closed.
Description: Hello, Recently we find a case about select query. Under certain connection collation, when query with `c1 = 'abc'` we can get resultset, when query with `a='abc '` we can also get resultset, but when query with `a='abc' and a = 'abc '` we get empty resultset, which is unreasonable. How to repeat: [yxxdb_8036@localhost ~]$ mysql -uroot -p'db1x@NJ+1' -S ~/bin/mysql1.sock test mysql: [Warning] Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.36 Source distribution Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `t_varchar` ( -> `id` int NOT NULL AUTO_INCREMENT, -> `c1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; Query OK, 0 rows affected (0.01 sec) mysql> insert into t_varchar (c1) values ('abc'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_varchar (c1) values ('abc '); Query OK, 1 row affected (0.00 sec) mysql> insert into t_varchar (c1) values ('abc '); Query OK, 1 row affected (0.01 sec) mysql> set names utf8mb4 collate utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.00 sec) mysql> select *,length(c1) from t_varchar where c1 = 'abc'; +----+-------+------------+ | id | c1 | length(c1) | +----+-------+------------+ | 7 | abc | 3 | | 8 | abc | 4 | | 9 | abc | 5 | +----+-------+------------+ 3 rows in set (0.00 sec) mysql> select *,length(c1) from t_varchar where c1 = 'abc '; +----+-------+------------+ | id | c1 | length(c1) | +----+-------+------------+ | 7 | abc | 3 | | 8 | abc | 4 | | 9 | abc | 5 | +----+-------+------------+ 3 rows in set (0.00 sec) # 3 rows of resultset expected, actually empty mysql> select *,length(c1) from t_varchar where c1 = 'abc' and c1 = 'abc '; Empty set (0.00 sec) mysql>