Bug #8702 Stored Procedures: No Error/Warning shown for inappropriate data type matching.
Submitted: 22 Feb 2005 17:52 Modified: 7 Dec 2005 20:13
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.2, 5.0.14-rc OS:Windows (Windows 2003/Linux)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[22 Feb 2005 17:52] Disha 
Description:
Usage of SELECT...INTO statement that retrieves column values with inappropriate data types for the matching variables in its variable list should be rejected with an appropriate error message, but it doesn't display any error and just makes the value of the variable as '0'.

How to repeat:
1 delimiter //  
2 Create and Execute following SQL statement: 
	Create procedure aproc( )
	BEGIN
	DECLARE x integer;
	DECLARE y integer;

	SELECT sal,lname INTO x,y FROM Employees LIMIT 1;
	set @x=x;
	set @y=y;
	END
	//
    Following Message is correctly displayed:
    Query OK, 0 rows affected (0.00 sec)

3 call aproc();//
   Following Message is correctly displayed:
   Query OK, 0 rows affected (0.05 sec)

4 select @x;//
    
5 select @y;//

-----------------------------------------------------------------
Expected Results:
The procedure is trying to insert an integer to a string type variable. An appropriate error/warning should be displayed. For e.g. 'Inappropriate data type match'
	  
Actual Results:
A 0 is displayed as follows.
	+------+
	| @y   |
	+------+
	| 0    |
	+------+
1 row in set (0.00 sec)
[22 Feb 2005 20:36] MySQL Verification Team 
Thank you for the bug report.
[24 Feb 2005 9:37] Disha 
Updated the synopsis.
[9 Aug 2005 14:34] Mark Matthews 
Please retest to determine platform-specificness.
[13 Aug 2005 7:07] Vasily Kishkin 
I tested the bug both on Windows 2003 and Linux SUSE 9.3 and got same result:

mysql> select @y;//
+------+
| @y   |
+------+
| 0    |
+------+
1 row in set (0.00 sec)

There is not warnings or errors.
Mysql is 5.0.11
[16 Sep 2005 16:50] Valeriy Kravchuk 
The same result on 5.0.14-rc (ChangeSet@1.1965.2.1, 2005-09-16 09:15:45+03:00, bell@sanja.is.com.ua)
[7 Dec 2005 20:13] Paul DuBois 
Noted in 5.0.18 changelog.