MySQL Bugs: #52511: MYSQL_FIELD::flags & BINARY

Bug #52511	MYSQL_FIELD::flags & BINARY_FLAG
Submitted:	31 Mar 2010 17:05	Modified:	31 Mar 2010 20:07
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	5.1.44	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Type shouldn't be string (char), it should be binary.

How to repeat:
mysql> create temporary table t ( f binary(9) );
Query OK, 0 rows affected (0.00 sec)

mysql> select f from t;
Field   1:  `f`
Catalog:    `def`
Database:   `xbt`
Table:      `t`
Org_table:  `t`
Type:       STRING
Collation:  binary (63)
Length:     9
Max_length: 0
Decimals:   0
Flags:      BINARY

Check http://dev.mysql.com/doc/refman/5.1/en/c-api-datatypes.html:

"To distinguish between binary and nonbinary data for string data types, check whether the charsetnr  value is 63. If so, the character set is binary, which indicates binary rather than nonbinary data. This enables you to distinguish BINARY from CHAR, VARBINARY from VARCHAR, and the BLOB types from the TEXT types."

There is no BINARY type at C API level. There is MYSQL_TYPE_STRING (for both CHAR and BINARY), BINARY flag, and binary collation to distinguish CHAR and BINARY. Look:

mysql> create table tb(c1 char(5) binary, c2 binary(5));
Query OK, 0 rows affected (0.32 sec)

mysql> select * from tb;
Field   1:  `c1`
Catalog:    `def`
Database:   `test`
Table:      `tb`
Org_table:  `tb`
Type:       STRING
Collation:  latin1_swedish_ci (8)
Length:     5
Max_length: 0
Decimals:   0
Flags:      BINARY 

Field   2:  `c2`
Catalog:    `def`
Database:   `test`
Table:      `tb`
Org_table:  `tb`
Type:       STRING
Collation:  binary (63)
Length:     5
Max_length: 0
Decimals:   0
Flags:      BINARY 

0 rows in set (0.00 sec)

Check http://dev.mysql.com/doc/refman/5.1/en/charset-binary-collations.html also.

This is intended and documented.

So the collation should be used to determine whether to display something as hex or as text?

Olaf, actually (and alas) yes... See my comment in Bug#52505.