| 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: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.14-rc | OS: | Linux (linux) |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
| Tags: | qc | ||
[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');
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.