Details
Major
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.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions