MySQL Bugs: #52899: Conversion of BLOB to TEXT (implicit or explicit) yields BLOB

Bug #52899	Conversion of BLOB to TEXT (implicit or explicit) yields BLOB
Submitted:	16 Apr 2010 19:31	Modified:	23 Apr 2010 8:34
Reporter:	Yvan Rodrigues	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.3-m3, 5.0, 5.1	OS:	Any (Linux, FreeBSD 8.0-RELEASE-p2)
Assigned to:		CPU Architecture:	Any
Tags:	BLOB, conversion, date_format, FUNCTION, implicit, return, text

Description:
This is an undocumented change in behaviour when upgrading from 5.5.2 to 5.5.3.

Firstly, DATE_FORMAT appears so be returning BINARY. This appears to contradict the documentation at http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format: "DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters."

I had a function which took a date parameter and returned the return value of DATE_FORMAT. The function was written to return TEXT CHARSET utf8. After upgrading it returns BINARY, despite the implicit conversion.

How to repeat:
Create the following function:

CREATE DEFINER=`foo`@`%` FUNCTION `DATE_YMD`(pDate DATETIME) RETURNS TEXT CHARSET utf8
    DETERMINISTIC
BEGIN
  RETURN DATE_FORMAT(pDate, '%Y-%m-%d');
END $$

In 5.5.2, this function returns a UTF8 string with the date like 2010-04-01. In 5.5.3, it returns the output as 10 binary bytes. Explicitly casting or converting the value to TEXT before returning it does not fix the problem.

A workaround is to change the return type to CHAR e.g.

CREATE DEFINER=`foo`@`%` FUNCTION `DATE_YMD`(pDate DATETIME) RETURNS CHAR(10) CHARSET utf8
    DETERMINISTIC
BEGIN
  RETURN DATE_FORMAT(pDate, '%Y-%m-%d');
END $$

Suggested fix:
1. Ensure DATE_FORMAT returns string in connection charset.

2. Ensure that a binary string implicitly (via function return type) or explicitly (with CAST or CONVERT) converted to TEXT is non-binary.

Thank you for the report.

This has nothing to do with DATE_FORMAT: your function returns TEXT which converted to BLOB:

mysql> create FUNCTION `DATE_YMD`(pDate DATETIME) RETURNS TEXT CHARSET utf8
    ->     DETERMINISTIC
    -> BEGIN
    ->   RETURN DATE_FORMAT(pDate, '%Y-%m-%d');
    -> END $$
Query OK, 0 rows affected (0.42 sec)

mysql> \d ;
mysql> select DATE_YMD(now());
Field   1:  `DATE_YMD(now())`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BLOB
Collation:  utf8_general_ci (33)
Length:     196605
Max_length: 10
Decimals:   0
Flags:      BLOB 

+-----------------+
| DATE_YMD(now()) |
+-----------------+
| 2010-04-20      |
+-----------------+
1 row in set (0.18 sec)

mysql> select  DATE_FORMAT(now(), '%Y-%m-%d');
Field   1:  `DATE_FORMAT(now(), '%Y-%m-%d')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     30
Max_length: 10
Decimals:   0
Flags:      

+--------------------------------+
| DATE_FORMAT(now(), '%Y-%m-%d') |
+--------------------------------+
| 2010-04-20                     |
+--------------------------------+
1 row in set (0.00 sec)

Also I see same behavior in version 5.5.2. Please provide similar output with version 5.5.2 using --show-column-info option of mysql command line client

Specifically with this report I tried the function on another system running 5.5.1 and indeed it also returned BLOB:

mysql> SELECT DATE_YMD(NOW());
Field   1:  `DATE_YMD(NOW())`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BLOB
Collation:  ascii_general_ci (11)
Length:     65535
Max_length: 10
Decimals:   0
Flags:      BLOB

So in the case of my original report, it appears *something* changed when I upgraded 5.5.2 to 5.5.3, perhaps it is specific to the FreeBSD port or a variable that has been deprecated (though no errors appear in the log at startup).

I think I'm confused about under what circumstances a function with return type explicitly set to TEXT should return BLOB. I can't find reference to this in the documentation. Can you clarify this?

Thank you for the feedback.

Verified conversion of TEXT to BLOB if function returns TEXT.