Bug #37485 Using CONCAT in SQL query causes Connector to return System.Byte[]
Submitted: 18 Jun 2008 16:52 Modified: 19 Jun 2008 17:00
Reporter: Gauron Kolas Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:5.1.6 OS:Windows
Assigned to: CPU Architecture:Any
Tags: asp.net, concat, System.Byte[]

[18 Jun 2008 16:52] Gauron Kolas
Description:
After upgrading from the latest 5.0.x connector to 5.1.6, this problem appears. Using Visual Studio .NET 2005 on Windows XP, MySql server 5.0.51a on win2k3, and a simple query inside a SqlDataSource.

Strangely, the problem is only present when running on localhost with Visual Studio's built-in web browser. If I deploy it to a test area under IIS, the correct string values are displayed instead of System.Byte[].

How to repeat:
Install the .NET connector, version 5.1.6
Create a new Web Form (.aspx page)
Add a SqlDataSource and bind it to a simple control like a DropDownList to see the results:

<asp:DropDownList ID="TestDDL" runat="server" 
   AutoPostBack="True" 
   DataSourceID="MySqlDS"
   DataTextField="NewStringField" 
   DataValueField="MyPrimaryKeyID">
</asp:DropDownList>
<asp:SqlDataSource ID="MySqlDS" runat="server" 
   ConnectionString=" ... "
   ProviderName=" ... " 
   SelectCommand="SELECT MyPrimaryKeyID, Concat(MyVarCharField, ' (MyPrimaryKeyID ', MyPrimaryKeyID, ')') As NewStringField
                  FROM SomeTable WHERE SomeID = 25">
</asp:SqlDataSource>
[19 Jun 2008 8:43] Tonci Grgin
Hi Gauron and thanks for your report. This is not a bug but expected behavior and here's why:
  o) CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

  o) No BINARY flag is set (as per above):
mysql> SELECT CONCAT("Tonci", " ", "Grgin") AS Name;
Field   1:  `Name`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     11
Max_length: 11
Decimals:   31
Flags:      NOT_NULL

+-------------+
| Name        |
+-------------+
| Tonci Grgin |
+-------------+
1 row in set (0.01 sec)

  o) BINARY flag is set (this is where your problem is!):
mysql> SELECT CONCAT("Tonci ", 1, " Grgin") AS Name;
Field   1:  `Name`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     13
Max_length: 13
Decimals:   31
Flags:      NOT_NULL BINARY

+---------------+
| Name          |
+---------------+
| Tonci 1 Grgin |
+---------------+
1 row in set (0.00 sec)

Try setting "Respect Binary Flags" to false and read the c/NET documentation. Also, you can explicitly cast such queries to retrieve result as string (BINARY flag not set):
mysql> SELECT CAST(CONCAT("Tonci ", 1, " Grgin") AS CHAR) AS Name;
Field   1:  `Name`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     13
Max_length: 13
Decimals:   31
Flags:      NOT_NULL

+---------------+
| Name          |
+---------------+
| Tonci 1 Grgin |
+---------------+
1 row in set (0.00 sec)
[19 Jun 2008 17:00] Gauron Kolas
Thanks Tonci, 
I just inserted a CAST on the numeric argument to CHAR in the middle of the CONCAT(...) statement and it works as expected now. :)