| Bug #28076 | inconsistent binary/varbinary comparison | ||
|---|---|---|---|
| Submitted: | 24 Apr 2007 15:09 | Modified: | 19 Nov 2007 4:45 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.0,5.1, 6.0 | OS: | Linux (SUSE 10 64-bit) |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[24 Apr 2007 15:09]
Peter Gulutzan
[24 Apr 2007 16:52]
MySQL Verification Team
Thank you for the bug report. Verified as described on FC 6 32-bit.
[16 May 2007 16:05]
Kevin Lewis
Recent changesets for character set bugs fixed this bug also. I am in the process of pushing a testcase for this.
[24 Aug 2007 19:13]
Kevin Lewis
The testcase for this bug was pushed back on May 16, just as the previous comment indicates. It relects the original bug as reported by Peter and has been passing in pushbuild ever since.
[24 Aug 2007 19:57]
Peter Gulutzan
Here is another test case. mysql> create table t4 (s1 binary(5), s2 varbinary(5)) engine=falcon; Query OK, 0 rows affected (0.02 sec) mysql> insert into t4 values (0x41,0x41); Query OK, 1 row affected (0.00 sec) mysql> select * from t4 where s2 in (select s1 from t4); Empty set (0.00 sec) mysql> create index i1 on t4 (s1); Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> create index i2 on t4 (s2); Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t4 where s2 in (select s1 from t4); +-------+------+ | s1 | s2 | +-------+------+ | A | A | +-------+------+ 1 row in set (0.00 sec)
[24 Aug 2007 20:58]
Kevin Lewis
I integrated the second test case scenario into falcon_bug_28076 and it does fail. So I am re-opening the bug.
[30 Aug 2007 19:46]
Peter Gulutzan
InnoDB behaviour is similar, see Bug#30734.
[31 Aug 2007 1:08]
Kevin Lewis
After investigation, I must conclude that the problem is in the server. As a reference, the table contains this; CREATE TABLE t1 (s1 binary(5), s2 varbinary(5)); INSERT INTO t1 VALUES (0x41, 0x41); SELECT hex(s1),hex(s2) FROM t1; +------------+---------+ | hex(s1) | hex(s2) | +------------+---------+ | 4100000000 | 41 | +------------+---------+ The following chart shows what happens in Falcon StorageTable::compareKey() with several conditions. Note that StorageTable::compareKey() is used by Falcon to make the final determination whether a key value matches the record. Num WHERE Condition Field CompareKey Result SearchKey FieldValue 1 s1 IN (SELECT s2 FROM t1) s2 -4 None 0x4100000000 0x41 2 s1 = 0x41 s1 0 None 0x41 0x4100000000 3 s1 = 0x4100000000 s1 0 Match 0x4100000000 0x4100000000 4 s2 IN (SELECT s1 FROM t1) s1 0 Match 0x4100000000 0x4100000000 5 s2 = 0x41 s2 0 Match 0x41 0x41 6 s2 = 0x4100000000 s2 -4 None 0x4100000000 0x41 First, you can see from tests 1 and 4 that the field being used in Falcon is that of the subquery. This means that the server uses the field from the main query to extract a field value for each record and sends it into the subquery as a searchKey. In test 1, it reads s1 (0x4100000000) and sends it in that way for Falcon to use as a filter on the 'SELECT s2 FROM t1'. Since s3 is 0x41 and it is searching for 0x4100000000, it is not a match. But in test 2, the engine reads s2 (0x41) and turns it into 0x4100000000, length = 5, then calls Falcon to do 'SELECT s1 FROM t1'. Of course that is a match. I would think that if the server wanted Falcon not to match a varbinary of 0x41 to a binary of 0x4100000000 then it should not pad the search key with zeros. Another reason that this problem should be fixed in the server is that the same test fails on InnoDB.
[1 Sep 2007 16:01]
Heikki Tuuri
http://bugs.mysql.com/bug.php?id=30734 was marked as a duplicate of this.
[8 Nov 2007 22:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37375 ChangeSet@1.2607, 2007-11-09 02:12:14+04:00, gshchepa@gleb.loc +5 -0 Fixed bug #28076: inconsistent binary/varbinary comparison. After the adding of an index the <VARBINARY> IN (SELECT <BINARY> ...) clause returned a wrong result: the VARBINARY value was illegally padded with zero bytes to the length of the BINARY column for the index search.
[9 Nov 2007 7:11]
Gleb Shchepa
5.0 is affected too
[9 Nov 2007 23:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37504 ChangeSet@1.2564, 2007-11-10 03:43:29+04:00, gshchepa@gleb.loc +6 -0 Fixed bug #28076: inconsistent binary/varbinary comparison. After the adding of an index the <VARBINARY> IN (SELECT <BINARY> ...) clause returned a wrong result: the VARBINARY value was illegally padded with zero bytes to the length of the BINARY column for the index search. (<VARBINARY>, ...) IN (SELECT <BINARY>, ... ) clauses are affected too.
[10 Nov 2007 18:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37521 ChangeSet@1.2564, 2007-11-10 22:16:49+04:00, gshchepa@gleb.loc +9 -0 Fixed bug #28076: inconsistent binary/varbinary comparison. After the adding of an index the <VARBINARY> IN (SELECT <BINARY> ...) clause returned a wrong result: the VARBINARY value was illegally padded with zero bytes to the length of the BINARY column for the index search. (<VARBINARY>, ...) IN (SELECT <BINARY>, ... ) clauses are affected too.
[10 Nov 2007 19:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37526 ChangeSet@1.2564, 2007-11-10 23:44:48+04:00, gshchepa@gleb.loc +9 -0 Fixed bug #28076: inconsistent binary/varbinary comparison. After adding an index the <VARBINARY> IN (SELECT <BINARY> ...) clause returned a wrong result: the VARBINARY value was illegally padded with zero bytes to the length of the BINARY column for the index search. (<VARBINARY>, ...) IN (SELECT <BINARY>, ... ) clauses are affected too.
[16 Nov 2007 9:30]
Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33]
Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35]
Bugs System
Pushed into 6.0.4-alpha
[19 Nov 2007 4:45]
Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelog. The result of a comparison between VARBINARY and BINARY columns differed depending on whether the VARBINARY column was indexed.
