Bug #64650 Using concat with a date returns BLOB
Submitted: 14 Mar 2012 15:59 Modified: 16 Mar 2012 8:55
Reporter: Daniel Silva Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:5.2.38 OS:Microsoft Windows (Microsoft Windows Vista Enterprise Edition Service Pack 2 (build 6002), 32-bit)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[14 Mar 2012 15:59] Daniel Silva
Description:
When using concat with a date always returns BLOB.
The byte size in the options makes no effect.

How to repeat:
select  concat(cast('2012-03-01' as date),'-')
[14 Mar 2012 16:51] Valeriy Kravchuk
What exact server version, x.y.z, are you working with? Can you try to connect to it with mysql client using --column-type-info option and check result set's metadata, like this:

macbook-pro:5.5 openxs$ bin/mysql --column-type-info -uroot 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.5.20-debug 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  concat(cast('2012-03-01' as date),'-');
Field   1:  `concat(cast('2012-03-01' as date),'-')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     33
Max_length: 11
Decimals:   31
Flags:      

+----------------------------------------+
| concat(cast('2012-03-01' as date),'-') |
+----------------------------------------+
| 2012-03-01-                            |
+----------------------------------------+
1 row in set (0.00 sec)

I want to check if Workbench just gets data as BLOB from server.
[14 Mar 2012 16:59] Daniel Silva
Its MySQL 5.1.47

mysql> select  concat(cast('2012-03-01' as date),'-');
Field   1:  `concat(cast('2012-03-01' as date),'-')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     11
Max_length: 11
Decimals:   31
Flags:      BINARY

+----------------------------------------+
| concat(cast('2012-03-01' as date),'-') |
+----------------------------------------+
| 2012-03-01-                            |
+----------------------------------------+
1 row in set (0.26 sec)
[14 Mar 2012 17:46] Valeriy Kravchuk
Looks like a server problem then:

macbook-pro:5.1 openxs$ bin/mysql --column-type-info -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61-debug 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  concat(cast('2012-03-01' as date),'-');
Field   1:  `concat(cast('2012-03-01' as date),'-')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     11
Max_length: 11
Decimals:   31
Flags:      BINARY 

+----------------------------------------+
| concat(cast('2012-03-01' as date),'-') |
+----------------------------------------+
| 2012-03-01-                            |
+----------------------------------------+
1 row in set (0.00 sec)

You can try to workaround it by setting "Treat BINARY/VARBINARY as nonbinary character string" check box at Options > SQL Editor tab.
[14 Mar 2012 20:00] Daniel Silva
I already tried the "Treat BINARY/VARBINARY as nonbinary character string" option, with no luck.
[15 Mar 2012 17:41] Valeriy Kravchuk
Had you tried to restart Workbench or at least reconnect after checking that BINARY-related option?
[15 Mar 2012 19:20] Peter Laursen
Excuse me for a comment from the 'sideline'.  But I think the discussion went 'off the track'.  Now *support* for WB has started ("could you try if this solves thw problem?").  This is a place for *bug reporting*

What we see is an inconsistent change of behaviour between 5.1 and 5.5 - and 5.1 behaviour contradicts documentaton.  That is the culprit and I think this should be marked as a bug with MySQL 5.1.

Consistent and correct metadata are important  - not at least for GUI clients.
[16 Mar 2012 7:13] Valeriy Kravchuk
Peter,

Please, check bug #34068 and WL mentioned there. The problem at server side is fixed, just not in 5.1...

I still want to get answer to my question about reconnecting or restarting Workbench. Let's concentrate on Workbench here, no matter is it support or bug identification.
[16 Mar 2012 8:25] Daniel Silva
I was sure I tested a restart to Workbench before submitting the bug.

But today is working fine, so maybe I didn't tested correctly.

Many thanks, you can close this.