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:
None 
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
Description:
What I did: I created a stored procedure.  I ran it from the SQL editor by clicking the lightning bolt icon next to the name of the procedure, a dialog box asks me for the values of my five IN parameters.  I entered the correct values.

What happens: The dialog box closes and the generated script in the SQL editor ignores my values. Instead it tries incorrectly to initialize them as 0 or '0', depending on the data type.  When the script is run with correct initialization values it works correctly.

What I want to happen: I will be running this procedure about 50 times per week with different values.
Using the icon in Workbench is probably the most efficient way of doing that as I do not need to share
the code.  Doing it this week by editing the SQL code probably cost me about half an hour in typing time
and created about half a dozen data entry errors that using the dialog box probably would not have cost 
me.

How to repeat:
Create a saved procedure with at least one IN parameter and run it with the icon.  This procedure
is the simplest on I can think of that illustrates the problem:

Anticipated input: A date in string form, e.g. Jul 07, 2016)

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

Suggested fix:
Workbench should put the data values from the dialog box into the generated SQL code,
specifically into the SET statements that initialized the parameters in the CALL statement.
[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.