Bug #60882 DECODE() and ENCODE() Require CAST
Submitted: 15 Apr 2011 14:12 Modified: 15 Apr 2011 15:40
Reporter: Larry D. Wilson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:5.2.33 OS:MacOS (10.6.7)
Assigned to: CPU Architecture:Any
Tags: BLOB, cast, decode, encode, workbench

[15 Apr 2011 14:12] Larry D. Wilson 
Description:
When running a SQL statement natively in Workbench that includes decode() or encode(), the result is returned as a blob which requires a cast to char to be visible. Since it doesn't work this way when run in MySQL, Workbench needs to return a char. 

How to repeat:
USE test;

DELIMITER $$
DROP TABLE IF EXISTS `pw_test`;
$$
CREATE TABLE `pw_test` (`test_id` INT UNSIGNED NOT NULL, `secret` VARCHAR(20) NOT NULL);
$$
INSERT INTO `pw_test`(`test_id`, `secret`) VALUES 
(1, ENCODE('hello', 'Yr%w3E')),
(2, ENCODE('second', 'Yr%w3E'));
$$
SELECT DECODE(`secret`, 'Yr%w3E'), CAST(DECODE(`secret`, 'Yr%w3E') AS CHAR) FROM `pw_test` WHERE `test_id` = 2;
$$

Suggested fix:
Selecting DECODE or ENCODE in a statement in Workbench should return a string, not a blob.
[15 Apr 2011 15:27] Valeriy Kravchuk 
Please, check in Preferences > SQL Editor if "Treat BINARY/VARBINARY as nonbinary character string" option is checked.
[15 Apr 2011 15:40] Larry D. Wilson 
Oops. Sorry about that.