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:Microsoft Windows (Win XP/Linux)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[6 Oct 2005 15:34] Roland Bouman
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.
[7 Dec 2005 20:42] Paul Dubois
Noted in 5.0.18 changelog.
[18 Apr 2012 13:16] Edward Crichton
Hi. I've just tripped over this bug in 5.0.88 on linux.
[18 Apr 2012 13:23] Edward Crichton
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 ;
[18 Apr 2012 16:10] Alexander Nozdrin
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)
[18 Apr 2012 19:29] Roland Bouman
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.
[18 Apr 2012 19:41] Alexander Nozdrin
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 ;)
[18 Apr 2012 20:01] Roland Bouman
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.