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