MySQL Bugs: #13663: Incorrect datatype returned for declared datetime variable in stored procedure

Bug #13663	Incorrect datatype returned for declared datetime variable in stored procedure
Submitted:	30 Sep 2005 16:03	Modified:	1 Oct 2005 1:06
Reporter:	k self	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.14 BK source	OS:	Windows (Windows-XP/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The incorrect datatype is returned from a stored procedure call when a declared variable of type datetime is returned from the stored procedure in the resultset.

The datatype is correct if the datetime field originates from a table and not a declared variable.

How to repeat:
Create the following sp:

delimiter //
create procedure Test()
begin
  declare a datetime;
  set a = now();
  select a;
end

Using this program:

import java.sql.*;

public class Test 
{
 public static void main(String[] args) throws Exception 
 {
  try
  {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=test&password=test");

            Statement stmt  = conn.createStatement();
            ResultSet lrs = stmt.executeQuery("call Test();");

            ResultSetMetaData lrsMeta = lrs.getMetaData();
            
            if(lrsMeta == null)
            {
            	System.out.println("ERROR - metadata is null");
            }
            else
            {
            	for(int i = 1;i <= lrsMeta.getColumnCount();i++)
            	{
            		System.out.println(lrsMeta.getColumnName(i));
            		System.out.println(lrsMeta.getColumnType(i));
            		System.out.println(lrsMeta.getColumnClassName(i));
            	}
            }
    		lrs.close();
            conn.close();
  }
  catch(Exception e)
  {
   e.printStackTrace();
  }
 }
}

Note the data type returned as -3 (Varbinary).

Suggested fix:
None.

This problem also happens when using the boolean datatype. I suspect it may happen on others as well. Note that if you code the now() function in the select line, the correct datatype is returned in the resultset.

Not a JDBC driver issue, as it's just using whatever datatype the server is reporting to it (so thus category changed to server/stored procedures).

Same as http://bugs.mysql.com/bug.php?id=13675.