Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1
-
None
Description
SET NAMES utf8 COLLATE utf8_german2_ci; |
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci); |
INSERT INTO t1 VALUES ('ae'),('ä'); |
SELECT * FROM t1 WHERE a='ae'; |
SELECT * FROM t1 WHERE a LIKE 'ä'; |
returns these results:
+------+
|
| a |
|
+------+
|
| ae |
|
| ä |
|
+------+
|
2 rows in set (0.01 sec)
|
|
|
+------+
|
| a |
|
+------+
|
| ä |
|
+------+
|
1 row in set (0.00 sec)
|
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.
Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä'; |
But in fact I get an empty set.
EXPLAIN EXTENDED returns "Impossible where", which is not correct:
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
After tracing the code, it seems that constant propagation was erroneously applied.