| 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: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S3 (Non-critical) |
| Version: | 5.2.38 | OS: | Windows (Microsoft Windows Vista Enterprise Edition Service Pack 2 (build 6002), 32-bit) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | WBBugReporter | ||
[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.
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),'-')