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:
None 
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
Description:
I create a Falcon table with a BINARY and a VARBINARY column.
I insert 0x41, which will be padded with 0x00s for BINARY.
I compare the two columns. I get one hit.
I create indexes.
I compare the two columns. I get zero hits.

See also Bug#24861.

ChangeSet@1.2639, 2007-04-23

How to repeat:
mysql> create table t4 (s1 binary(5), s2 varbinary(5)) engine=falcon;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t4 values (0x41,0x41);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4 where s1 in (select s2 from t4);
Empty set (0.01 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.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t4 where s1 in (select s2 from t4);
+-------+------+
| s1    | s2   |
+-------+------+
| A     | A    |
+-------+------+
1 row in set (0.00 sec)
[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.