[MDEV-7152] Wrong result set for WHERE a='oe' COLLATE utf8_german2_ci AND a='oe' Created: 2014-11-20  Updated: 2015-01-19  Resolved: 2015-01-18

Status: Closed
Project: MariaDB Server
Component/s: Character Sets
Affects Version/s: 5.5.40, 10.0.14, 10.1.1
Fix Version/s: 10.0.16

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: 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.
Generated at Thu Feb 08 07:17:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.