Bug #80564 | Saved procedure entered parameters ignored, zeros incorrectly entered by script | ||
---|---|---|---|
Submitted: | 29 Feb 2016 20:10 | Modified: | 24 May 2018 15:25 |
Reporter: | Walter Griffith | Email Updates: | |
Status: | QA review | Impact on me: | |
Category: | MySQL Workbench: SQL Editor | Severity: | S3 (Non-critical) |
Version: | 6.3.6 (Workbench) | OS: | MacOS (10.9.5 (mysql version 5.7.11-Community Edition)) |
Assigned to: | CPU Architecture: | Any | |
Tags: | initialization, stored procedure |
[29 Feb 2016 20:10]
Walter Griffith
[1 Mar 2016 7:34]
MySQL Verification Team
Hello Walter Griffith, Thank you for the report. Verified as described on Win7 with WB 6.3.6. Thanks, Umesh
[1 Mar 2016 7:36]
MySQL Verification Team
-- Create SP DELIMITER $$ CREATE PROCEDURE display_date(IN string_date VARCHAR(12)) BEGIN DECLARE the_date DATE; # Convert string date into DATE variable # This will cause an error when the procedure is 0-initialized since 00/00/00 is # not a legal date. SELECT STR_TO_DATE(string_date,'%M %d, %Y') INTO the_date; END$$ DELIMITER ; -- Input passed such as May 1, 2013, 'May 1, 2013' etc 13:03:27 set @string_date = '0' 0 row(s) affected 0.265 sec 13:03:28 call test.display_date(@string_date) Error Code: 1411. Incorrect datetime value: '0' for function str_to_date 0.062 sec
[30 Mar 2016 10:45]
MySQL Verification Team
Bug #80906 marked as duplicate of this
[11 Apr 2016 6:18]
MySQL Verification Team
Bug #81008 marked as duplicate of this
[25 May 2016 2:45]
Philip Olson
Posted by developer: No explanation was given, and MySQL Bug #80564 remains Verified, so setting status back to Verified.
[8 Mar 2017 14:46]
Brad Kent
Can we change the Severity of this bug to serious? DELIMITER $$ USE `test_db`$$ CREATE PROCEDURE `delete_stuff` (_min BIGINT) BEGIN DELETE FROM `important_table` WHERE 'some_value` >= _min; END$$ DELIMITER ; If I intend to delete stuff >= 1000000000 MySQL Workbench will instead delete stuff >= 0 Executing stored procedures with zeros for all the params is bound to lead to bad results. If you're lucky you'll get a "incorrect date value "0" for column `date` at row 1" type error Verified on Workbench 6.3.9 on OSX 10.11.7 Workbench 6.3.6 on Windows 10
[8 Mar 2017 21:06]
Walter Griffith
I agree the form reported by Mr. Kent is severe. I can report that the bug appears to be gone on macOS 10.12.3 and Workbench 6.3.9 Larry Griffith
[8 Mar 2017 22:54]
Brad Kent
Seems weird that the version of OSX would be the difference (10.11.7 vs 10.12.3) What version of MySQL? I'm experiencing the bug with 5.7.10.
[9 Mar 2017 5:49]
MySQL Verification Team
Bug #85355 marked as duplicate of this one
[10 Mar 2017 21:40]
Walter Griffith
My current mysql version is 5.7.17-Community Edition. Larry Griffith
[10 Mar 2017 21:50]
Brad Kent
just upgraded to 5.7.17 - no dice
[11 Mar 2017 15:00]
Walter Griffith
I don't know what else to suggest except upgrade Workbench and if that doesn't work upgrade the OS. The OS upgrade cured quite a few problems for me (not all mysql-related).
[12 Apr 2017 12:00]
Franc Drobnič
It is also happening on Windows 10. So OS upgrade is not really a solution. It is not critical for us because this feature is primarily used in development but it is annoying. I noticed one difference: if procedure parameters are declared using explicit IN for direction then this feature works correctly. Parameter values are merged directly into the procedure call (e.g. call sp_foo(1, 2); ). Only if the direction is ommited - specification only consists of parameter name and parameter type - this erratic behaviour occurs and the parameter values are prepared in a form of SET commands before the procedure call (e.g. SET @a = 0; SET @b = 0; call sp_foo(@a, @b); SELECT @a, @b; ).
[12 Apr 2017 14:49]
Walter Griffith
Re: the comment by Mr. Drobnic: using the IN direction did not solve the problem on Mac OS. Only the OS upgrade did. So the bug may be different under Windows and MacOS. Larry Griffith
[16 Apr 2018 5:12]
Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=90432 marked as duplicate of this one.