Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
Description
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin);
|
INSERT INTO t1 VALUES ('a'),('a ');
|
SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE CRC32(a)=3904355907;
|
returns one row:
+------+-----------+------------+
|
| a | LENGTH(a) | CRC32(a) |
|
+------+-----------+------------+
|
| a | 1 | 3904355907 |
|
+------+-----------+------------+
|
If I make the condition even stricter:
SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)=3904355907;
|
it erroneously returns two rows:
+------+-----------+------------+
|
| a | LENGTH(a) | CRC32(a) |
|
+------+-----------+------------+
|
| a | 1 | 3904355907 |
|
| a | 2 | 105998545 |
|
+------+-----------+------------+
|
This script demonstrates the same problem:
SET NAMES latin1;
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin);
|
INSERT INTO t1 VALUES ('a'),('a ');
|
SELECT a, HEX(a) FROM t1 WHERE HEX(a)='61';
|
It returns one row:
+------+--------+
|
| a | HEX(a) |
|
+------+--------+
|
| a | 61 |
|
+------+--------+
|
Now if I make the condition even stricter:
SELECT *,HEX(a) FROM t1 WHERE a='a' AND HEX(a)='61';
|
It returns two rows:
+------+--------+
|
| a | HEX(a) |
|
+------+--------+
|
| a | 61 |
|
| a | 6120 |
|
+------+--------+
|
A similar problem with trailing spaces:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin);
|
INSERT INTO t1 VALUES ('a ');
|
SELECT * FROM t1 WHERE a='a';
|
SELECT * FROM t1 WHERE LENGTH(a)=2;
|
SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2;
|
The first and the second query correctly return one row. The third query erroneously returns no rows.
