Bug #13675 DATETIME/DATE type in store proc param seems to be converted as varbinary
Submitted: 1 Oct 2005 0:08 Modified: 8 Oct 2007 13:24
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.14-rc OS:Linux (linux)
Assigned to: Alexander Nozdrin
Tags: qc

[1 Oct 2005 0:08] jocelyn fournier
Description:
Hi,

If (IN param DATETIME) is used as the param type of a store proc, I expect check / conversion to be done (as it seems to work with INT type).
Instead it seems the param is always converted to varbinary type.

Regards,
  Jocelyn

How to repeat:
delimiter |
CREATE PROCEDURE test (IN param DATETIME) BEGIN DROP TABLE IF EXISTS t1; CREATE TABLE t1 SELECT param; END |
CALL test(NOW())|
Query OK, 0 rows affected (0.02 sec)

=> no warning

SHOW CREATE TABLE t1|
+-------+-------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                    |
+-------+-------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `param` varbinary(19) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

=> varbinary

CALL test('test')|
Query OK, 0 rows affected (0.31 sec)

=> still no warning

SHOW CREATE TABLE t1|
+-------+------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                   |
+-------+------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `param` varbinary(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

=> no check has been done

DROP PROCEDURE test|
CREATE PROCEDURE test (IN param INT) BEGIN DROP TABLE IF EXISTS t1; CREATE TABLE t1 SELECT param; END |

CALL test('test')|
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> SHOW WARNINGS|
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'test' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

=> check is properly done

Suggested fix:
Check / conversion should be done according to the given param type defined in the stored proc.
[29 Aug 2007 8:42] Alexander Nozdrin
Can't repeat with MySQL 5.0.50 (BK).
[29 Aug 2007 11:00] Bugs System
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/33289

ChangeSet@1.2505, 2007-08-29 14:57:59+04:00, anozdrin@ibm. +2 -0
  Test case for Bug#13675: DATETIME/DATE type in store proc param
  seems to be converted as varbinary.
  
  The bug has been already fixed. This CS just adds a test case for it.
[7 Sep 2007 8:09] Bugs System
Pushed into 5.1.23-beta
[7 Sep 2007 8:10] Bugs System
Pushed into 5.0.50
[8 Oct 2007 13:24] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in 5.0.50 and 5.1.23 changelogs as:

          Parameters of type <literal>DATETIME</literal> or
          <literal>DATE</literal> in stored procedures were silently
          converted to <literal>VARBINARY</literal>. (Bug #13675)
[25 Oct 2007 19:49] Jared S
What should the expected behaviour be for following line?

Call teset('test');