MySQL Bugs: #41021: DateTime format incorrect

Bug #41021	DateTime format incorrect
Submitted:	25 Nov 2008 17:10	Modified:	9 Dec 2008 15:11
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
see how to repeat

How to repeat:
1) start mysqld with the general log enabled

2) Compile and execute the following c# program:

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace Tests{
  public class DateTest{

    static void Main(){
      MySqlConnection connection = null;
      MySqlCommand command = null;

      try{
        using (IDbConnection conn = new MySql.Data.MySqlClient. MySqlConnection ( "server=localhost;database=test;user id=root;password=" )) {
          conn.Open();
          string strSQL = "SELECT  dt from dtest where DATE_FORMAT(DATE(dt), GET_FORMAT(DATETIME, 'ISO'))=?DATEFILTER";
          using (IDbCommand comm = conn.CreateCommand()) {
            comm.CommandText = strSQL;
            IDbDataParameter param=comm.CreateParameter();
            param.ParameterName= "?DATEFILTER";
            param.Value= new DateTime (2009, 11, 25);
            param.DbType = DbType .DateTime;
            comm.Parameters.Add(param);

            using (IDataReader dr = comm.ExecuteReader()) { }
          }
        }
      }catch( MySqlException ex ){
        String errorstr = null;
        switch( ex.Number ){
          case 0:
            errorstr = "Cannot connect to server."; break;

         case 1045:
            errorstr = "Invalid username/password, " +
                       "please try again"; break;
          default:
            errorstr = ex.Message; break;
        }
        Console.WriteLine( errorstr );
      }catch( Exception ex ){
        Console.WriteLine( ex.Message );
      }

      if( command != null )
        command.Dispose();
      if( connection != null )
        connection.Close();
    }

  }
}

3) Check mysqld general log and you'll notice that the DATETIME format has
_two_ spaces which then causes the query to always return 0 rows:
081125 11:57:59	      1 Connect     root@ as anonymous on test
		      1 Query       SHOW VARIABLES
		      1 Query       SHOW COLLATION
		      1 Query       SET character_set_results=NULL
		      1 Init DB     test
		      1 Query       SELECT  dt from dtest where DATE_FORMAT(DATE(dt), GET_FORMAT(DATETIME, 'ISO'))='2009-11-25  00:00:00'
		      1 Quit  

Suggested fix:
We need to find out where the extra space is coming from and correct it.

I took a look at the 5.2.5 source code and I found that the "mistake" is on line 259 of MySQLDateTime.cs, in SerializeText method
I replaced

val = String.Format("{0}  {1:00}:{2:00}:{3:00}", val,
    value.Hour, value.Minute, value.Second);

with

val = String.Format("{0} {1:00}:{2:00}:{3:00}", val,
    value.Hour, value.Minute, value.Second);

I tested it and it seems to work. I'll make some more test, I hope it helps.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60520

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60521

fixed in 5.1.8, 5.2.6+

An entry was added to both the 5.1.8 and 5.2.6 changelogs:

The DATETIME format contained an erroneous space.