[MDEV-8661] Wrong result for SELECT..WHERE a='a' AND a='a' COLLATE latin1_bin Created: 2015-08-21  Updated: 2015-09-12  Resolved: 2015-08-26

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.1.5

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: propagation, upstream

Issue Links:
Blocks
blocks MDEV-8728 Fix a number of problems in equal fie... Closed

 Description   

This bug is similar to http://bugs.mysql.com/bug.php?id=5134
Note, the patch for MySQL bug#5134 fixed only a particular case of the problem when the BINARY keyword is used. The problem is in fact more general.

This script:

SET NAMES latin1;
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a CHAR(10));
 
INSERT INTO t1 VALUES ('a'),('A');
 
SELECT * FROM t1 WHERE a = 'a' COLLATE latin1_bin;

correctly returns one row:

+------+
 
| a    |
 
+------+
 
| a    |
 
+------+

Now if I add an extra part into the condition:

SELECT * FROM t1 WHERE a='a' AND a='a' COLLATE latin1_bin;

it returns two rows:

+------+
 
| a    |
 
+------+
 
| a    |
 
| A    |
 
+------+

The expected result is to return one row in both cases.

The problem happens because "AND a='a' COLLATE latin1_bin" gets erroneously replaced to "AND 'a'='a' COLLATE latin1_bin" which is further evaluates to TRUE and gets removed from the WHERE condition. So, the query gets rewritten to:

SELECT * FROM t1 WHERE a='a';

The method which actually replaces the field to the constant is Item_field::equal_fields_propagator() in item.cc.

This condition is not strict enough:

  if (!item || !has_compatible_context(item))
 
    item= this;

It should also take into account the collations of the two operations that the field "a" appears in.

 Comments   
Comment by Alexander Barkov [ 2015-08-25 ]

Another example:

SET NAMES latin1;
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci);
 
INSERT INTO t1 VALUES ('a'),('A');
 
SELECT * FROM t1 WHERE a='a' COLLATE latin1_bin;
 
SELECT * FROM t1 WHERE a='A' COLLATE latin1_swedish_ci;

The above script returns one row in the first SELECT and two rows in the second SELECT:

+------+
 
| a    |
 
+------+
 
| a    |
 
+------+
 
1 row in set (0.00 sec)
 
 
 
+------+
 
| a    |
 
+------+
 
| a    |
 
| A    |
 
+------+
 
2 rows in set (0.00 sec)

Now if I join the above two condition using AND:

SELECT * FROM t1 WHERE a='a' COLLATE latin1_bin AND a='A' COLLATE latin1_swedish_ci;

I get empty set. This is wrong. The expected result is to return one row, the same result as in the first SELECT.

Comment by Alexander Barkov [ 2015-09-12 ]

The problem is repeatable in MySQL-5.7.8

Generated at Thu Feb 08 07:28:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.