MySQL Bugs: #53335: With sha1 method in query sql, the result set does not display the sha-ed value

Bug #53335	With sha1 method in query sql, the result set does not display the sha-ed value
Submitted:	30 Apr 2010 21:32	Modified:	13 May 2010 12:55
Reporter:	Blake Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.20 Beta	OS:	MacOS (10.6.3)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any

Description:
When you run a simple sha1 on a value (existing table data or just a string), such as:

select sha1('hello');

The result set shows nothing for the output (for that column in the result set).

How to repeat:
Run

  select sha1('hello');

View result set

Suggested fix:
Display the sha'ed value.

Thank you for the problem report. Looks like Workbench consider the result as a BLOB, while it is just string with BINARY collation:

77-52-4-109:5.1 openxs$ bin/mysql --column-type-info -uroot testReading 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.1.47-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select sha1('hello');
Field   1:  `sha1('hello')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     40
Max_length: 40
Decimals:   31
Flags:      NOT_NULL BINARY 

+------------------------------------------+
| sha1('hello')                            |
+------------------------------------------+
| aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d |
+------------------------------------------+
1 row in set (0.00 sec)

As a workaround, click right mouse button, use Open Value in Viewer from menu, and you can see it as text.

Ok, that works great.  Thanks.

Added the BLOB indicators in Mac

1) sha1 function returns a value of binary string, not string with binary collation. These two are different types, details here: http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html

2) WB doesn't represent text equivalent of binary strings and BLOB values in grid, but only in a dedicated BLOB editor, which is accessible via cell's context menu. Doing otherwise would cause either data misrepresentation (see bug #52954 for example) or performance hit caused by special processing of zero bytes in the values. If you still need to get binary strings shown in the grid do use cast or convert functions, e.g.: select cast(sha1('hello') as char(40))

The issue of the bug report supposed to be missing overlay BLOB icon, which is now fixed. Hence passing over to QA.

fix confirmed in repository. the "BLOB" and "NULL" indicators have been added in mac version

An entry has been added to the 5.2.21 changelog:

In the Mac OS X version of MySQL Workbench, the overlay text for NULL and BLOB fields was missing from the results grid in the SQL editor. This meant that those fields appeared empty if a query was executed that returned a BLOB, such as select sha1('hello');.