Bug #56156 Output of the datetime type were shown as blob type in case statement
Submitted: 20 Aug 2010 19:24 Modified: 21 Aug 2010 0:55
Reporter: Igor Shevtsov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:Workbench 5.2.26 CE OS:Windows
Assigned to: CPU Architecture:Any

[20 Aug 2010 19:24] Igor Shevtsov
Description:
I just start using Workbench 5.2.26 CE 
on Windows 7 Pro 32-bit connecting through ssh tunnel to remote machine. 
When I try running a query with a case statement, columns with datetime Type are shown as "BLOB" in output window. 
To see the output data I have to right click inside of the cell, choose "Open Value in Viewer" and see text. 
This is an example of a case statement I use as part of the query: 

case 
when cancelled_on is null then '' 
when cancelled_on is not null then cancelled_on 
end as 'Cancelled On'

Should produce cells with a date of cancelled operation, but it returns blue "blob" icon instead of the date '2010-08-20'. 
If I try to Export data as CSV file, the fileds with 'blob' icons are empty. 
The code works nicely in MySQL monitor or PhPMyAdmin with properly formated fields and CSV export file has datetime fields return with proper date.

How to repeat:
This is my table:
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| reference_number | varchar(8)       | YES  |     | NULL    |                |
| created_on       | datetime         | YES  |     | NULL    |                |
| cancelled_on     | datetime         | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

Data:

+----+------------------+---------------------+---------------------+
| id | reference_number | created_on          | cancelled_on        |
+----+------------------+---------------------+---------------------+
|  1 | ref12345         | 2010-06-10 00:00:00 | 2010-08-20 00:00:00 |
|  2 | ref12346         | 2010-06-11 00:00:00 | 2010-08-20 00:00:00 |
|  3 | ref12347         | 2010-06-12 00:00:00 | NULL                |
+----+------------------+---------------------+---------------------+

Query:

select 
reference_number 'Reference Number',  
created_on 'Created On',
case 
when cancelled_on is null then '' 
when cancelled_on is not null then cancelled_on 
end as 'Cancelled On
from
test1;

Returns as expected:
+------------------+---------------------+---------------------+
| Reference Number | Created On          | Cancelled On        |
+------------------+---------------------+---------------------+
| ref12345         | 2010-06-10 00:00:00 | 2010-08-20 00:00:00 |
| ref12346         | 2010-06-11 00:00:00 | 2010-08-20 00:00:00 |
| ref12347         | 2010-06-12 00:00:00 |                     |
+------------------+---------------------+---------------------+

But if I run this in Workbench SQL Editor,
Returns:
+------------------+---------------------+---------------------+
| Reference Number | Created On          | Cancelled On        |
+------------------+---------------------+---------------------+
| ref12345         | 2010-06-10 00:00:00 | blob                |
| ref12346         | 2010-06-11 00:00:00 | blob                |
| ref12347         | 2010-06-12 00:00:00 |                     |
+------------------+---------------------+---------------------+
If I export output as CSV file I have no output data in Cancelled On column.
[21 Aug 2010 0:55] MySQL Verification Team
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of: http://bugs.mysql.com/bug.php?id=56157.