MySQL Bugs: #34887: error while attempting to show concatenated field in datagridview

Bug #34887	error while attempting to show concatenated field in datagridview
Submitted:	27 Feb 2008 13:33	Modified:	1 Mar 2008 20:10
Reporter:	Jaques Duby	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.0	OS:	Windows (xp sp2)
Assigned to:		CPU Architecture:	Any
Tags:	Connector/Net, datagridview, sql sintax, Visual Basic

Description:
I have windows xp service pack 2, using visual studio 2005 version 8.0.50727.762 (SP.050727-7600) with .net framwork 2.0.50727 SP1. Languaje Visual Basic.
I have installed mysql-5.0.51a-win32

Error when using Connector/Net 5.2.0 to display date values with certain format in a datagridview control. Using Connector/Net 5.0.8 works fine. 

How to repeat:
This is the query:
SELECT field_name, field_phone, concat(substr(field_date,7,2),'/',substr(field_date,5,2),'/',substr(field_date,1,4)) as field_date FROM table WHERE mid(field_date,5,2)  = 2 ORDER BY right(field_date, 2) asc

the field "field_date" stores dates like the following: 20080227, that's yyyymmdd, and the query outputs 27/02/2008, that's dd/mm/yyyy

so a sample row will be:
John Smith, 555-1234, 15/09/1987

The Visual Basic code:
Dim objDataview As DataView
Dim objDataTable As DataTable

#Here I connect and fill the datatable

objDataview = New DataView(objDataTable)

# Begin: this line causes the error
DataGridViewX.DataSource = objDataview 
# End: this line causes the error

objDataTable = Nothing
objDataview = Nothing

When the datagridview control try to show the date column it produces an exception and have to stop the application.

If I hide the date column the error doesn´t happen.
If I don´t concatenate but do a substr the error happen
If I concatenate another field and still showing the comun the error doesn´t happen.

Image of error in: http://www.matias.com.ar/errorvbnet.png

Hi Jacques and thanks for your report.

I am a bit puzzled as for what the problem is so I must ask you to do the following:
 - start mysql.exe (cl client) with -T option (ie. mysql -uuser -hhost -p -T your_db). Then issue your query, "SELECT field_name, field_phone, concat(substr(field_date,7,2),'/',substr(field_date,5,2),'/',substr(field_date,1,4)) as field_date FROM table WHERE mid(field_date,5,2)  = 2 ORDER BY right(field_date, 2) asc", and paste output here. Only a row or two of data are enough, I am mostly interested in metadata returned.
 - Zip and attach complete test case together with data in form suitable for load into mysql server (or create table and data in code)

SQL:
DROP TABLE IF EXISTS `table`;
CREATE TABLE `table` (
  `field_id` int(10) unsigned NOT NULL,
  `field_name` varchar(20) default NULL,
  `field_last_name` varchar(20) default NULL,
  `field_phone` varchar(20) default NULL,
  `field_date` int(10) unsigned default NULL,
  PRIMARY KEY  (`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table` (`field_id`,`field_name`,`field_last_name`,`field_phone`,`field_date`) VALUES 
 (1,'Silvana','Alvarez','4246-6558',19950201);
 
This is what I've got:

Field 1: 'field_name'
Catalog: 'def'
Database: ''
Table: ''
Org_Table: ''
Type: VAR_STRING
Collation: utf8_general_ci (33)
Lenght: 123
Max_lenght: 15
Decimals: 31
Flags:

Field 2: 'field_phone'
Catalog: 'def'
Database: ''
Table: ''
Org_Table: ''
Type: VAR_STRING
Collation: utf8_general_ci (33)
Lenght: 60
Max_lenght: 9
Decimals: 0
Flags:

Field 3: 'field_date'
Catalog: 'def'
Database: ''
Table: ''
Org_Table: ''
Type: VAR_STRING
Collation: binary (63)
Lenght: 10
Max_lenght: 10
Decimals: 31
Flags: BINARY

Returned row:
+-----------------+---------------+------------+
|   field_name    |  field_phone  | field_date |
+-----------------+---------------+------------+
| Silvana Alvarez |   4246-6558   | 01/02/1995 |
+-----------------+---------------+------------+

Jacques, just as I thought, there's nothing worng with c/NET...
"concat(substr(field_date,7,2),'/',substr(field_date,5,2),'/',substr(field_date,1,4)) as field_date" produces field with obvious BINARY (or rather BINARY marked) context:
Field 3: 'field_date'
Catalog: 'def'
Database: ''
Table: ''
Org_Table: ''
Type: VAR_STRING
Collation: binary (63)
Lenght: 10
Max_lenght: 10
Decimals: 31
Flags: BINARY

The only 2 things you can do now is:
 1) Add CAST AS DATE or similar
and / or
 2) Upgrade to latest MySQL server 5.0.x

If you decide to update server, this "-T" test and BINARY flag (+ Collation: binary (63)) will tell you immediately that c/NET will treat the field as binary...

This is expected behavior of CONCAT which is documented in http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

I don't think this is a bug but I won't close it unless you agree.

doing cast(expr) solve the problem using connector/net 5.2.0
so the query would be:
SELECT field_name, field_phone,
cast(concat(substr(field_date,7,2),'/',substr(field_date,5,2),'/',substr(field_date,1,4)) as char) as
field_date FROM table WHERE mid(field_date,5,2)  = 2 ORDER BY right(field_date, 2) asc

do you know why connectot 5.0.8 works fine without the cast workaround?

I'm sorry to report it as a bug

Jacques, no problem, I see it can be confusing... The root of things is in Bug#10491 and similar reports... Connectors can't work around it, at least not reliably. It is almost impossible to distinguish between "SHOW CREATE TABLE", where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM some_table", where it really should be binary... So I guess Reggie just decided not to guess anymore but to rely on correct server behavior (as 10491 is fixed).

Thanks for your interest in MySQL.

Hi Tonci, so the connector has no problems....is the way the server returns data?
because a time ago I used conector 5.0.7 with server 5.0.24, after I formated my pc I decided to upgrade to newest version of both. And since I didn't think the server will introduce major updates against the new version of the conector I supposed the problem was in c/net
I decided to stay with server 5.0.51 and to keep c/net 5.0.8 so I don't have to change sql code querys in my project (a lot)
thanks for all the help!!!

Jaques

Tonci is correc when he says that I decided to start respecting the flags reported by the server.  However the 5.0 connector was technically not guessing.  Instead it simply treated all columns reported as VAR_STRING as strings whether or not the binary flags were set.  in 5.1, we support the new BINARY and VARBINARY types so it's important that we respect these flags.  I have just as many people complaining that binary columns are not returning as byte[].  :)

However, I added a connection string option that can help.  You can add 'Respect Binary Flags=false' to your connection string and it will mimic 5.0 behavior however the preferred approach is to recode your query to return the right data type of upgrade to a server that returns the right data type (if one is available).

ok!! now I really understand what the problem was
:)
thank you very much both for helping me with my problem