MySQL Bugs: #18763: ByteArray objects cannot be converted to strings

Bug #18763	ByteArray objects cannot be converted to strings
Submitted:	3 Apr 2006 22:40	Modified:	27 Dec 2007 16:14
Reporter:	Dale Marinello	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.21-BK, 4.1.9-nt	OS:	Linux (Linux, Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When using the jdbc:mysql url and com.mysql.jdbc.Driver driver class in ColdFusion odbc setup, the following query and output produces the above error.

How to repeat:
GameDate is a DateTime Field in the SchGames Table

<cfquery name="months" datasource="#request.dns#">
select distinct date_format(GameDate,'%b %Y') as yrmon from Schgames
</cfquery>

<cfoutput>#months.yrmon#</cfoutput>

Suggested fix:
Define odbc on the server and use socket odbc in ColdFusion.

The server is returning the incorrect metadata in this case. Debugging it with the JDBC driver, it's telling the client that date_format should be a BINARY _type_.

A workaround might be to use CAST to get the server to return it as a string:

select distinct CAST(date_format(GameDate,'%b %Y') AS CHAR)  as yrmon from Schgames

Verified on 4.1.21-BK build on Linux:

mysql> select distinct date_format(now(),'%b %Y') as yrmon from t2;
+----------+
| yrmon    |
+----------+
| May 2006 |
+----------+
1 row in set (0.00 sec)

mysql> create table tt2 as select distinct date_format(now(),'%b %Y') as yrmon from t2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc tt2;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| yrmon | binary(8) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

So, it is a server bug - wrong data type of column in results.

Fixed by the changeset for bug #22646. Not reproducible on fresh 4.1 and 5.0 trees.