MySQL Bugs: #34068: CURRENT_DATE() returns a binary string for non-binary data

Bug #34068	CURRENT_DATE() returns a binary string for non-binary data
Submitted:	25 Jan 2008 19:06	Modified:	30 Jun 2010 5:43
Reporter:	Jim Winstead	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S4 (Feature request)
Version:	5.0.54, any	OS:	Any
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
The CURRENT_DATE() function returns a binary result, even though the data is non-binary. This causes problems with Connector/J, Connector/Net, and Connector/ODBC, and anything else that does not blithely treat binary results as if they contained character data. See Bug #27419 for an example of where a user has been bitten by this.

How to repeat:
SELECT CURRENT_DATE()

Suggested fix:
Don't return a binary result from a function that clearly returns ASCII data.

Thank you for a bug report. Verified just as described:

mysql> select current_date();
Field   1:  `current_date()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      NOT_NULL BINARY

+----------------+
| current_date() |
+----------------+
| 2008-01-26     |
+----------------+
1 row in set (0.36 sec)

I do not consider above a bug, by the way, as result is of type DATE. But compare this:

mysql> select cast(current_date() as char);
Field   1:  `cast(current_date() as char)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     30
Max_length: 10
Decimals:   31
Flags:

+------------------------------+
| cast(current_date() as char) |
+------------------------------+
| 2008-01-26                   |
+------------------------------+
1 row in set (0.39 sec)

to this:

mysql> select concat(current_date() ,' ');
Field   1:  `concat(current_date() ,' ')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     11
Max_length: 11
Decimals:   31
Flags:      BINARY

+-----------------------------+
| concat(current_date() ,' ') |
+-----------------------------+
| 2008-01-26                  |
+-----------------------------+
1 row in set (0.34 sec)

BINARY above is a problem, I think.

Workaround: Use CAST(ANY_FUNCTION() AS CHAR) as a workaround

Hi Valeriy,

I don't understand why you set this to
"verified". Your comment suggests that
you don't think CURRENT_DATE is wrong.
If the objection is about CONCAT, then
(a) that seems to be a different matter,
(b) the manual seems to say that CONCAT
result should be binary if the input,
i.e. CURRENT_DATE, is binary.
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat

Setting back to "open" till it's clear.

It is simple: cast (current_date() as char), that is, explicit cast to char, gives us proper collation, utf8_general_ci. CONCAT (s1, s2) should work with char/varchar data types, so CONCAT(current_date(), ...) should implicitely cast current_date() to char/varchar. 

The bug is: why implicit cast gives BINARY collation, while explicit cast gives utf8_general_ci. I think, this is inconsistent.

current_date() itself is BINARY, but of DATE type! DATE was converted to VAR_STRING somehow, but without taking current collation into account.

This problem was fixed by WL#2649 Number-to-string conversion
http://forge.mysql.com/worklog/task.php?id=2649

mysql --column-type-info test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.99-m4-debug Source distribution

mysql> select concat(current_date(), ' ');
Field   1:  `concat(current_date(), ' ')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     33
Max_length: 11
Decimals:   31
Flags:      NOT_NULL 

+-----------------------------+
| concat(current_date(), ' ') |
+-----------------------------+
| 2010-06-30                  |
+-----------------------------+
1 row in set (0.00 sec)