MySQL Bugs: #35959: selecting explicit binary value gives non-binary result

Bug #35959	selecting explicit binary value gives non-binary result
Submitted:	10 Apr 2008 11:44	Modified:	25 Apr 2008 3:45
Reporter:	d di (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	BINARY, cast, character set, charset, qc

Description:
Either of these SELECTs should give a 1-row by 1-column result set with a single field containing binary data:
=============
SELECT _binary 0x555a170099
SELECT CAST(0x555a170099 AS BINARY)
SELECT CAST(_binary 0x555a170099 AS BINARY)

It seems that the server neglects to tell the client that the data is binary, instead presenting it as being encoded in whatever character set the client has chosen for the connection.

How to repeat:
Wireshark's protocol dissector does not support the current incarnation of MySQL wire chit-chat, unfortunately.  Instead, use this query in Connector/NET:

string sql = "SELECT CAST(_binary 0x555a170099 AS BINARY) AS Fjabbe";

then load the above into a DataSet with Fill().
Try to retrieve the field's value from the DataSet with:

byte[] result = ds.Tables[0].Rows[0]["Fjabbe"];

And notice the exception which says that Connector/NET has chosen Unicode string rather than byte array as the data type for Fjabbe.

Suggested fix:
Fix server to tell client that data is binary.

Thank you for the bug report. Could you please see using the mysql command
client starting it with option -T i.e: mysql -uroot -T if actually the
server not returns a binary result. Thanks in advance.

Very useful debugging option, thanks :-).

mysql> SELECT CAST(_binary 0x4669747465 AS BINARY);
Field   1:  `CAST(_binary 0x4669747465 AS BINARY)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     5
Max_length: 5
Decimals:   31
Flags:      NOT_NULL BINARY

There's a binary flag, but the type is VAR_STRING.
No clue what that means - is it perhaps documented somewhere?

What exact Connector/NET version do you use?

As you can see, server clearly returns data as binary.

> What exact Connector/NET version do you use?

I used version 5.0.8.1.  But it seems to be across the board, which is why I'm guessing at a server issue.  For example, I've just reproduced this using the MySQL Query Browser (version 1.2.11); selecting a BLOB field such as x509_subject from mysql.user gives you a field with a BLOB icon, whereas running "SELECT _binary 0xdeadbeef" does not.

> As you can see, server clearly returns data as binary.

Yeah, well, whatever flags we find in a packet trace is ultimately irrelevant when the practical consequence is that every connector on the planet regards the data as being non-binary :-).

The -T output does look reasonable though.  That's why I asked for documentation.  A specification of what the combination of VAR_STRING and BINARY flags are supposed to accomplish would be very useful when deciding whether this is a client or a server problem.

Forget BINARY flag, the important field is "collation". If it's "binary" it means that data is binary, a sequence of bytes, not characters.

Connectors (/Net and /J) intentionally ignore it and report data as a string, as a workaround for the following: when a number is converted to a string implicitly it gets binary charset, that is the result of CONCAT("The number is ", 5) will be binary.

The server behaves correctly and consistently, the above is documented. But as it may be unexpected and undesirable, certain connectors try to offset that by presenting binary data as strings. We're going to change the charset of a result in an implicit number-to-string conversion in the server which will resolve the whole issue.

Super, thanks for the info!

David,

when I understand this right then your problem is solved.
Can we close this bug report?

Uhm.  This issue will be implicitly resolved when WL #2649 and issue #31384 is resolved.  If you can mark dependencies in Eventum, I'd set this item to "waiting for #31384".

If there are no dependencies, possibly "duplicate of #31384" would be appropriate.

Otherwise, please do whatever you feel is best :-).

Thank you for the feedback.

Closed as duplicate of bug #31384