MySQL Bugs: #56642: Returning BLOB

Bug #56642	Returning BLOB
Submitted:	8 Sep 2010 12:59	Modified:	5 Sep 2012 23:48
Reporter:	Moacir Braga	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.26 CE	OS:	Windows (Win 7 x32)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
I did a routine with Navicat and returned success, but when I execute this in Workbench, column on the result returned BLOB, blue image with BLOB on the center this.
Then I tested the two programs until I find the possible error.
Every time I write CONCAT(1), CONCAT(2), 3, 4, 5.... any CONCAT with a number as arg, it return is BLOB, but on the Navicat, normal.
Ex.: CONCAT('He has ', 7)
Navicat Return: He has 7
Workbench Return: BLOB

How to repeat:
only write on the SQL Editor:
CONCAT('string', number)
CONCAT(number)

any CONCAT with number as arg

Binary strings are shown as BLOB values on purpose to avoid misrepresentation of the value and avoid data losses. It's possible to enforce visualization of binary string values in grid via preferences settings: View -> Edit -> Preferences -> SQL Editor -> Treat BINARY/VARBINARY as nonbinary character string.

Bug #56157 is a duplicate, although with a different test case

Marked bug #56967 as a duplicate

Is there a similar preference/switch on the database engines that will cause implicit data typing of a mixed string/numeric function or expression to return, depending on context, a numeric or string value instead of a numeric or binary value? 

I have a big problem trying to migrate a company from MySQL 3.23 to MySQL 5x because 3.23 does the implicit conversion to a string value for CONCAT functions and 5x converts to a binary value (makes no sense to me for an implicit conversion to convert to a third data type). I have no solution for the 700+ queries that have been written relying on correct implicit conversion. The Microsoft ADO code that reads the recordset values returns "Incorrect Type" in thousands of instances. Even if rewriting the queries using the CAST function was an option, CAST does not work in the ODBC drivers and produces a MySQL syntax error. Fubar... mike@cannonresearch.com

Fixed as of the upcoming Workbench 5.2.43, and here's the changelog entry:

 When a routine contained "CONCAT" with a number as an argument,
 MySQL Workbench would return it as a "BLOB". 

Thank you for the report.