Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.40, 10.0.14, 10.1.1
-
None
Description
SET NAMES utf8 COLLATE utf8_german2_ci;
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a CHAR(10) CHARACTER SET utf8);
|
INSERT INTO t1 VALUES ('ö'),('oe');
|
SELECT * FROM t1 WHERE a='oe' AND a='oe' COLLATE utf8_german2_ci;
|
SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
|
The first SELECT query returns one row:
+------+
|
| a |
|
+------+
|
| oe |
|
+------+
|
The second SELECT query returns two rows:
+------+
|
| a |
|
+------+
|
| ö |
|
| oe |
|
+------+
|
The result for the second query is wrong.
Both SELECT queries should return the same result with one row.
The WHERE condition is logically the same in the two queries, the only different is the order of the AND operands.
EXPLAIN for the second query:
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
|
SHOW WARNINGS;
|
returns:
+-------+------+-------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
|
+-------+------+-------------------------------------------------------------------------------+
|
It seems the condition was simplified in a wrong way. It should not have been simplified because the two equality predicates use different collations.