MySQL when a = 0, b = 0, but a <> b (binary)

Question

Any idea of why this is so,

CREATE TABLE f AS
  SELECT b'000000001' AS a, x'01' AS b;

This creates two columns of

`a` varbinary(2) NOT NULL,
`b` varbinary(1) NOT NULL

However, when I run,

SELECT a=0, b=0, a=b, a<>b FROM f;
+-----+-----+-----+------+
| a=0 | b=0 | a=b | a<>b |
+-----+-----+-----+------+
|   1 |   1 |   0 |    1 |
+-----+-----+-----+------+

What's going on here? From the docs on The BINARY and VARBINARY Types,

All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 bytes and spaces are different in comparisons, with 0x00 < space.

That explains why a<>b, but why does a=0, or b=0 there?

score 8 · Accepted Answer · edited Jun 21 '19 at 14:28

BINARY is a misnomer. It really refers to a string of bytes; it does not refer to binary numbers, and it isn't a unique datatype.

Also, = and <> adapt to the datatype(s) involved:

SELECT a=0, b=0,    -- numeric comparisons
       a=b, a<>b    -- BLOB comparisons; length matters
    FROM f;

It is a combination of the Operator and the Datatypes that controls what string vs numeric operations.

Some info on datatypes:

BINARY / VARBINARY / BLOB can be manipulated with "bit" operators | & ~ ^ -- not to be confused with logical operators AND (&&) and OR (||). BLOB-like columns are used for non-character strings, such as images. MySQL 8.0 stretches these operators beyond a 64-bit limit. INT can also be manipulated with bit operators.

The BIT(m) datatype is prefixed with leading zeros if needed to fill out to full byte(s). (Limit 64 bits.) This datatype is almost never used.

BINARY(1) and BINARY(22) are 1- and 22-byte datatypes. CAST(1 AS BINARY(1) converts the first 1 to a string "1", then stuffs in hex 31 (ascii code for "1"). Try SELECT HEX(CAST(1 AS binary(1))), HEX(CAST("1" AS BINARY(1))); -- both yield 31.

score 2 · Answer 2 · answered May 03 '18 at 02:11

it is no warranty - which part of x = y would be converted with auto type convertion

SELECT
    a = 0,
    a = cast( 0 AS BINARY ),
    cast(a as SIGNED),
    cast(a as SIGNED) = 0,
    b,
    b = 0,
    b = cast( 0 AS BINARY ),
    cast(b as SIGNED),
    b = cast(b as SIGNED) = 0,
    a = b,
    a <> b 
FROM
    f;

this is give You more information

MySQL when a = 0, b = 0, but a <> b (binary)

2 Answers2