MySQL Bugs: #59806: DATE_FORMAT over a NULL value causes connector to abort

Bug #59806	DATE_FORMAT over a NULL value causes connector to abort
Submitted:	28 Jan 2011 21:14	Modified:	2 Feb 2011 17:30
Reporter:	Felipe Castro	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.3.6	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	Connector/Net, date_format, SqlNullValueException

Description:
A SELECT DATE_FORMAT( field, '%m/%d/%Y' ) over a field that has a NULL value causes the connector to abort with this error when getting the value of the field into a C# variable:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
  at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull)
  at MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 i)

The same query with connector 1.0.7 works OK.  (I was trying to upgrade).

How to repeat:
Steps:

- Create a table with a date column.
- Enter a record that has a NULL value in the date field.
- With a C# program do a select that retrieves that specific record.
- Read the value into a string variable.

Specifically my code is something like this:

cmd.CommandText = " SELECT ..... DATE_FORMAT( EndDate, '%m/%d/%Y'), ....";
rdr = cmd.ExecuteReader();
...
String date = rdr.GetString(10);
...

I just noticed that it does not need to be the DATE_FORMAT function.

Any time that I attempt to do a reader.GetString(ix) on a date column that has a null value the connector aborts.

Unhandled Exception: System.Data.SqlTypes.SqlNullValueException: Data is Null. T
his method or property cannot be called on Null values.
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean
checkNull)
   at MySql.Data.MySqlClient.MySqlDataReader.GetDateTime(Int32 i)
   at Test1.Program.Main(String[] args) in C:\codetst\VisualStudio\Test1\Test1\P
rogram.cs:line 43

Felipe, I am sorry but I do not see a bug here... What are you actually asking for? Is it:
  a) a way to write NULL-safe SQL query (as this is possible)
  b) a way to check for NULL return before reading (as this is possible)
  c) or for us to override default framework behavior?

The behavior of MySQL server in such cases is governed by SQL_MODE variable. Start from there and work your way up through null-safe SQL command and null-check in code. If you still think it's a bug, please elaborate more.

I just noticed this happens with integers too so maybe specifications changed between the two versions of the connector.  I am moving from Net Connector 1.0.7 to 6.3.6 so this must be a big jump.

Please note that I am not seeing this as a problem in MySQL but in the connector itself.  It works fine from MySQL tools.

Given the following:
- A table with NULL in a field defined as "int(10) unsigned DEFAULT NULL".
- I can read the contents of the table without problem using MySQL tools like mysql.exe or MySQL Workbench and the field shows as "(NULL)", which is expected.
- A C# program cannot read the field directly in the way it was possible in the old connector.

C# code is like this:

MySqlConnection conn - new MySqlConnection(CONNECTION_STRING);
conn.open();
MySqlCommand cmd = new MySqlCommand("",conn);

cmd.CommandText = "select age from table";
cmd.CommandType = System.Data.CommandType.Text;

MySqlDataReader reader = cmd.ExecuteReader();

while(reader.Read()) {
  Console.WriteLine(reader.GetInt32(0));
}

In the old connector this would print a zero, but in the new connector it throws an exception:

Unhandled Exception: System.Data.SqlTypes.SqlNullValueException: Data is Null.
his method or property cannot be called on Null values.
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean
checkNull)
   at MySql.Data.MySqlClient.MySqlDataReader.GetInt32(Int32 i)
   at Test1.Program.Main(String[] args) in C:\codetst\VisualStudio\Test1\Test1\
rogram.cs:line 41

I just need to know if this is the expected behaviour for this level of the connector because it is different from the way it worked in the old one.  In that case i would just update the code to check for null.

If this is not the expected behaviour then it would be a bug.

Thanks for the help.

Felipe