Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1
Description
This script:
SET NAMES latin1;
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin);
|
INSERT INTO t1 VALUES ('a'),('A');
|
SELECT * FROM t1 WHERE a='a' AND a='A';
|
erroneously returns one row:
+------+
|
| a |
|
+------+
|
| a |
|
+------+
|
The expected result is empty set.
If I change the order in WHERE:
SELECT * FROM t1 WHERE a='A' AND a='a';
|
it erroneously returns one row again, but a different row:
+------+
|
| a |
|
+------+
|
| A |
|
+------+
|
The problem happens in Item_equal::add_const(). It does not take into account collation of the field and compares the two string literals using the current session collation, which is latin1_swedish_ci.
A related problem:
SET NAMES utf8 COLLATE utf8_german2_ci;
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin);
|
INSERT INTO t1 VALUES ('a');
|
SELECT * FROM t1 WHERE a='a';
|
SELECT * FROM t1 WHERE a=_utf8'a';
|
SELECT * FROM t1 WHERE a='a' AND a=_utf8'a';
|
The first and the second SELECT queries correctly return one row.
The third query returns error:
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (utf8_german2_ci,COERCIBLE) for operation '='
|
It should return one row.
