MySQL Bugs: #62723: varbinary() field type when should be varchar()

Bug #62723	varbinary() field type when should be varchar()
Submitted:	13 Oct 2011 17:20	Modified:	14 Oct 2011 8:26
Reporter:	Jorge Bastos	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.58	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Howdy,

With this code:

drop table if exists tmp;
create temporary table tmp
select left(1111,1) as x;
select * from tmp;
describe tmp;

the field "x" is created as varbinary, but since I have in the connection string: 
FunctionsReturnString=true;

The resulting field type should be varchar, am I correct?
I think that in previous connector versions the resulting field type was created with varchar instead of varbinary.

How to repeat:
drop table if exists tmp;
create temporary table tmp
select left(1111,1) as x;
select * from tmp;
describe tmp;

What exact server version do you work with? I see this problem at server level with 5.1:

macbook-pro:5.1 openxs$ bin/mysql -uroot --column-type-info test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select left(1111,1) as x;
Field   1:  `x`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   31
Flags:      BINARY 

+------+
| x    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

Note BINARY flag and collation above. With 5.5.x this does not happen.

Looks like this is a (known) problem in older server versions, rather than a bug in Connector/Net.

Hi Valeriy,

I did forgot to mention that, my fault.
I'm on MySQL 5.1.58.

I was trying with a 5.5.15, and the same happens.
(you can change the bug type to the server and not the connector)

Sorry,
Forgot my glasses.

With 5.5x works fine.
Can this be reported to MySQL devs?

This is fixed since 5.5.3 and, as incompatible change in behavior, will not be fixed in 5.1.x. Read http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html for the details.

I understand, I'll upgrade.

Thanks!