MySQL Bugs: #13808: ENUM type stored procedure parameter accepts non-enumerated data

Bug #13808	ENUM type stored procedure parameter accepts non-enumerated data
Submitted:	6 Oct 2005 15:34	Modified:	18 Apr 2012 20:01
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.13/5.0 BK source	OS:	Windows (Win XP/Linux)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
A stored procedure can be defined with an ENUM datatype parameter. When calling such a procedure, passing a value that is not enumerated in the ENUM type definition is permitted. The procedure executes without warnings or errors.

This behaviour is quite unlike that of ENUM datatype columns in base tables. An insert of a non-enumerated value into such a column will result in an error. 

It was expected that the behaviour of ENUM type stored procedure parameters would be similar to that of ENUM type columns in base tables, that is, it was expected that the procedure call would fail with an error indicating that the data is not valid for the parameter..

How to repeat:
delimiter go

use test 
go

create procedure enump(
    p enum(
        'a'
    ,   'b'
    )
)
select p;
go

call enump('c')
go

+---+
| p |
+---+
| c |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

create table enumt(
    c enum(
        'a'
    ,   'b'
    )
)
go

insert 
into    enumt 
values ('c')
go

ERROR 1265 (01000): Data truncated for column 'c' at row 1

Suggested fix:
Have the data passed to ENUM type stored procedure parameters checked in a similar way data is checked for ENUM type table columns.

Noted in 5.0.18 changelog.

Hi. I've just tripped over this bug in 5.0.88 on linux.

You can do something like this though:

DROP PROCEDURE IF EXISTS test;

delimiter //

create procedure test(
	IN p enum('a','b')
	)
main:BEGIN

	IF NOT p IN ('a',',b')
	THEN
		LEAVE main;
	END IF;

	select 'got through';

END//

delimiter ;

Hello,

I'm not sure what you're complaining about.
Here is what I get from the latest 5.0 (5.0.96):

[db1]> create procedure p1( IN p enum('a','b') )   SELECT p, p = '';
Query OK, 0 rows affected (0.00 sec)

[db1]> call p1('a');
+------+--------+
| p    | p = '' |
+------+--------+
| a    |      0 | 
+------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[db1]> call p1('c');
+------+--------+
| p    | p = '' |
+------+--------+
|      |      1 | 
+------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

[db1]> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'p' at row 1 | 
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

Akexander, this was reported in 5.0.13, a little less than 7 years ago. Maybe it was fixed in the mean while, but the bug was never closed? 

If you like, you can see for yourself what the complaint was about if you're willing to go to the trouble to resurrect a 5.0.13 for it.

Roland,

> this was reported in 5.0.13, a little less than 7 years ago.
> Maybe it was fixed in the mean while, but the bug was never closed? 

It is pretty late here, but I clearly see "Status: Closed".
What makes you believe the bug was never closed?

Also, there is a comment from Paul [7 Dec 2005 20:42]
saying "Noted in 5.0.18 changelog.", which I guess,
means, the bug was fixed in 5.0.18.

> If you like, you can see for yourself what the complaint
> was about if you're willing to go to the trouble
> to resurrect a 5.0.13 for it.

I hope, you've seen my results for the latest 5.0.

And I hope, you don't want to have this bug fixed
exactly in 5.0.13 ;)

Hi Alexander,

so indeed - it was closed, probably some time ago. Sorry - I saw the post coming in and I assumed this was because it was still open. 

Every now and then I do see bugs that I reported waaay back but remained open suddenly receive some love and attention - I assumed that was the case here, but apparenlty you were commenting on Edwards remarks that he could still see it in 5.0.88. 

Anyway, thanks for clearing it up, sorry for my premature and wrong assumptions.