Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0, 10.1
-
None
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.
Attachments
Issue Links
- duplicates
-
-
- Closed
-