MySQL Bugs: #27551: SQL query returns byte array instead of result in appropriate datatype.

Bug #27551	SQL query returns byte array instead of result in appropriate datatype.
Submitted:	30 Mar 2007 15:23	Modified:	4 Apr 2007 17:57
Reporter:	Robert Hood	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.6	OS:	Windows (XP Pro)
Assigned to:		CPU Architecture:	Any
Tags:	byte array

Description:
When the following query is run against MySQL, a byte array is returned instead of the correct results.

SELECT
CASE '5Day' WHEN '5Day' THEN
CONCAT('BT', DATE_FORMAT(NOW(), '%m%d%y'))
ELSE
NULL
END

When we adjust the query to look like this, we receive the results in the correct format:

SELECT
CASE '5Day' WHEN '5Day' THEN
CAST(CONCAT('BT', DATE_FORMAT(NOW(), '%m%d%y')) AS CHAR)
ELSE
NULL
END

We receive this behavior when going against a variety of MySql server versions, including 5.0.20a and 4.1.13.  This query has worked in the past, and still executes correctly when run through SQLYog.

How to repeat:
Create a MySqlDataAdapter, and for the select command enter the above queries.  Then, do a MySqlDataAdapter.Fill on a data table.  Use the Visual Studio Data Visualizer to view the contents of the table after the query is run.

Robert

I can't reproduce it.  Here's my test case.

MySqlDataAdapter da = new MySqlDataAdapter(@"SELECT
   CASE '5Day' WHEN '5Day' THEN
      CONCAT('BT', DATE_FORMAT(NOW(), '%m%d%y'))
   ELSE
      NULL
   END", conn);
DataTable dt = new DataTable();
da.Fill(dt);

dt.Rows[0][0] is a string when this is done.  I'm using 5.0.6 against a MySQL 5.0 server.

Also, we use NUNit for all of our unit testing. Every confirmed bug report gets turned into a test case.

I just attached a couple screen shots that should help prove that I'm seeing the error.  The database I'm going against is a version 4.1.13 database

This is something that we can't really fix.  Let me explain.

MySQL has several issues when it comes to reporting whether a result if binary or not.  This was _very_ bad on MySQL versions prior to 5.0 but it's still a problem even today.  The SQL you reported is returned by MySQL as binary when it obviously is not.  The connector can't know for sure.  With 5.0.5 and 5.0.6, we tried to make a "best guess" but that code caused more problems than it solved, so with 5.0.7 we have rolled it out.    Your SQL will return string properly with 5.0.7, but that doesn't mean it's fixed.  In fact, it returns string because we are ignoring the binary flag so that means you could generate valid SQL that should return binary and 5.0.7 will return string.

Until the server is fixed, the connector just can't _always_ do the right thing.  I hope this has cleared it up somewhat.

Robert

It's not binary data in general.  You can certainly use blobs without trouble.  The issue comes when you use some SQL that returns data as binary or when you use a binary introducer.  One example would be "SELECT __binary 'boo'"

Also, the comments and images are private (i.e.  visible only to MySQL employees).  Is this sufficient?

We're experiencing a number of difficulties at my work, primarily from using DATE_FORMAT(). Is there a version of the MySQL Server 5.0 that corrects this error? Is there a fix pending?