Bug #30734 InnoDB: inconsistent binary/varbinary comparison

• Submitted: 30 Aug 2007 19:45
• Modified: 1 Sep 2007 16:00
• Reporter: Peter Gulutzan
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.1.23-beta-debug
• OS: Linux (SUSE 10 64-bit)
• Assigned to: Assigned Account
• CPU Architecture: Any

[30 Aug 2007 19:45] Peter Gulutzan

Description:
I create an InnoDB 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 zero hits.
I create indexes.
I compare the two columns. I get one hit.

Falcon also is inconsistent, as in the test case for
Bug#28076 Falcon: inconsistent binary/varbinary comparison
But if engine=myisam or engine=memory, there's no inconsistency.
So I've guessed that Falcon's similar behaviour is a coincidence.

How to repeat:
mysql>       SET storage_engine = InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>       DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.03 sec)

mysql>       CREATE TABLE t1 (s1 binary(5), s2 varbinary(5));
Query OK, 0 rows affected (0.01 sec)

mysql>       INSERT INTO t1 VALUES (0x41, 0x41);
Query OK, 1 row affected (0.00 sec)

mysql>       SELECT hex(s1),hex(s2) FROM t1;
+------------+---------+
| hex(s1)    | hex(s2) |
+------------+---------+
| 4100000000 | 41      |
+------------+---------+
1 row in set (0.00 sec)

mysql>       SELECT hex(s1),hex(s2) FROM t1 WHERE s1 IN (SELECT s2 FROM t1);
Empty set (0.03 sec)

mysql>       SELECT hex(s1),hex(s2) FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
Empty set (0.01 sec)

mysql>       CREATE INDEX i1 on t1 (s1);
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>       CREATE INDEX i2 on t1 (s2);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>       SELECT hex(s1),hex(s2) FROM t1 WHERE s1 IN (SELECT s2 FROM t1);
Empty set (0.01 sec)

mysql>       SELECT hex(s1),hex(s2) FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+------------+---------+
| hex(s1)    | hex(s2) |
+------------+---------+
| 4100000000 | 41      |
+------------+---------+
1 row in set (0.00 sec)

[1 Sep 2007 16:00] Heikki Tuuri

What is the correct behavior? Below we see that in 5.1, InnoDB does not pad BINARY/VARBINARY in comparisons.

The comments in bug #28076 suggest that the bug is in MySQL that it passes the search key value sometimes padded with 0x00 though it apparently should not pad it.

I am marking this bug report a duplicate of Bug#28076.

data0type.ic in 5.1:

/*************************************************************************
Gets the padding character code for a type. */
UNIV_INLINE
ulint
dtype_get_pad_char(
/*===============*/
                                /* out: padding character code, or
                                ULINT_UNDEFINED if no padding specified */
        ulint   mtype,          /* in: main type */
        ulint   prtype)         /* in: precise type */
{
        switch (mtype) {
        case DATA_FIXBINARY:
        case DATA_BINARY:
                if (UNIV_UNLIKELY(dtype_get_charset_coll(prtype)
                                  == DATA_MYSQL_BINARY_CHARSET_COLL)) {
                        /* Starting from 5.0.18, do not pad
                        VARBINARY or BINARY columns. */
                        return(ULINT_UNDEFINED);
                }
                /* Fall through */
        case DATA_CHAR:
        case DATA_VARCHAR:
        case DATA_MYSQL:
        case DATA_VARMYSQL:
                /* Space is the padding character for all char and binary
                strings, and starting from 5.0.3, also for TEXT strings. */

                return(0x20);
        case DATA_BLOB:
                if (!(prtype & DATA_BINARY_TYPE)) {
                        return(0x20);
                }
                /* Fall through */
        default:
                /* No padding specified */
                return(ULINT_UNDEFINED);
        }
}