MySQL Bugs: #28306: VARCHAR-Fields with _bin-Collation results in binary

Bug #28306	VARCHAR-Fields with _bin-Collation results in binary
Submitted:	8 May 2007 16:54	Modified:	9 May 2007 16:43
Reporter:	Roland Volkmann
Status:	Duplicate
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	3.51.14, 3.51.15, 5.0.11	OS:	Microsoft Windows
Assigned to:	Tonci Grgin	Target Version:
Tags:	varchar, collation, BINARY

Description:
If you have table with varchar column using _bin collation e.g. latin1/latin1_bin
ODBC-connecter maps them to binary fields instead of text. This behaviour starts with
version 3.51.14, last correct working version is 3.51.12-2. So you can't access such
fields to select correct contents.

How to repeat:
mysql> create table test (info varchar(20)
    -> character set latin1 collate latin1_bin);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test (info) values ('Hello world');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------------+
| info        |
+-------------+
| Hello world |
+-------------+
1 row in set (0.00 sec)

mysql>

Now connect to this table using OpenOffice.org Base or Microsoft Access with
ODBC-Connector 3.51.15, and try to select the above info. OpenOffice.org Base will show
you "OBJECT" instead of "Hello world", and Microsoft Access will show you some binary
garbage.

Suggested fix:
handling as it was until version 3.51.12-2

Hi Roland and thanks for your report.

Hmm, where to start, this is in fact duplicate even if on newer version. Take you pick:
Bug#27030, Bug#26993, Bug#27409, Bug#27633, Bug#27790 ... but the main problem is in
Bug#10491. Seems that perfectly correct MyODBC patch will have to be rolled out.

In any case, if you start mysql cl client with -T option you'll see that your field has
BINARY flag set and Access "garbage" should in fact be 0x + your text.

Using generic MS client on your example I get correct result:

	Full Connect(Use Driver)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'myodbc1'.
SQLExecDirect:
				In:				hstmt = 0x008B1960, szSqlStr = "SELECT * FROM bug28306", 
										cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"info"
0xHello world
1 row fetched from 1 column.

Both ODBC connectors return result in form 0xHello... just as described in Bug#27633.

Hello Tonci,

thank you for reply. After having read the related bug reports, I agree that this one
might be similar.

Possible solution:

If you get Binary-Flag *and* collation 63 (binary) then it's correct to map it as binary
field to odbc application. But if you only get the binary flag with regular collation
(with my example on my machine I get collation = 8 = latin1), then this field must be
mapped to "text". I checked many charset/collation combinations of VARCHAR field in
Innodb Table using a test program with MySQL C API, and every time the mentioned solution
would be correct.

With best regards,
Roland.

Yes Roland and I couldn't have put it better myself... But there's always some small
*but*. This should and will be solved on server level rather than play this game of "Oh,
the server says it's a blob, but the charset isn't 63 so it's a TEXT" in every connector.
Maybe once we will rename "binary" collation too so that it's more apparent to all what it
means.

Also, for those of us who really *need* the latin1_bin collation, the suggested
work-around is not an option.
Our project is one such project. 
But we are in the process of migrating to utf (where we will use, guess what: utf8_bin
collation ) -- so hopefully our woes will be gone soon.

Maarten Jansonius

Reporting on the utf8_bin : it's broken, and in exactly the same way as latin1_bin. 

Just for the record, because some ppl seem to suggest that there should be a workaround
or something:  other than the sorting order,
a TEXT or VARCHAR field with charset latin1 and collation latin1_bin, should return
exactly the same result as a field with charset latin1 and collation latin1_swedish_ci --
or any other colaltion for that matter. Ditto for utf8 in various collations.

It just so happens that for our (multilingual) application we need to distinguish all
strings from eachother, so we really need the "_bin" collation...
 We've tried other collations, but with those, the unique key constraint on the string
field (which we really want to keep) prevents us from inserting, for example, both "él"
and "el" as separate entries.

Maarten, are you sure this is not a server bug? Have you tried 5.1.3? Is there a bug
report you posted on this, preferably with test case? If you're using *nix platform it
could be problem with DM too (iODBC / UnixODBC)...