Details
Description
This script:
SET NAMES latin1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(20) COLLATE latin1_bin);
INSERT INTO t1 VALUES ('a');
SELECT * FROM t1 WHERE a='A';
SELECT * FROM t1 WHERE a='A' AND a=_latin1'a';
correctly returns empty set for both SELECT queries.
If I now change the order of the two conditions in the second SELECT query:
SELECT * FROM t1 WHERE a=_latin1'a' AND a='A';
it erroneously returns one row:
+------+ | a | +------+ | a | +------+
The same problem is repeatable with character set introducers
(SET NAMES latin1 is not needed in this case):
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(20) COLLATE latin1_bin);
INSERT INTO t1 VALUES ('a');
SELECT * FROM t1 WHERE a=_latin1'A';
SELECT * FROM t1 WHERE a=_latin1'A' AND a=_latin1'a';
SELECT * FROM t1 WHERE a=_latin1'a' AND a=_latin1'A';
The problem happens in Item_equal::add_const(), in this code:
else { Item_func_eq *func= new (thd->mem_root) Item_func_eq(thd, c, const_item); if (func->set_cmp_func()) { /* Setting a comparison function fails when trying to compare incompatible charsets. Charset compatibility is checked earlier, except for constant subqueries where we may do it here. */ return; } func->quick_fix_field(); cond_false= !func->val_int(); }
It does not take into account the column collation (latin1_bin) and compares the two constants as latin1_swedish_ci, which gives TRUE. The second part of the AND is then eliminated from the query.
So:
SELECT * FROM t1 WHERE a=_latin1'A' AND a=_latin1'a';
gets rewritten to:
SELECT * FROM t1 WHERE a=_latin1'A';
which returns no rows, while:
SELECT * FROM t1 WHERE a=_latin1'a' AND a=_latin1'A';
gets rewritten as
SELECT * FROM t1 WHERE a=_latin1'a';
which returns one row.
Gliffy Diagrams
Attachments
Issue Links
- duplicates
-
MDEV-8705 Wrong result for SELECT..WHERE latin1_bin_column='a' AND latin1_bin_column='A'
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions