Bug #8768 Functions: For any unsigned data type, -ve values can be passed and returned.
Submitted: 24 Feb 2005 8:56 Modified: 7 Dec 2005 20:10
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.13-BK OS:Any (any)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[24 Feb 2005 8:56] Disha 
Description:
For function parameters or return statements that are declared unsigned, we are able to associate a -ve value and this negative value is returned, which should not be case. 

Even if we declare variables within a function as unsigned, it is also able to hold -ve values.

Following are the repro steps to repeat in case tinyint is used.

How to repeat:
	1	Delimiter        //
	2	Create database test1//
	3 	Use test1 //
	4	Drop function if exists fn1//
 	5	Create function fn1 (a tinyint unsigned) Returns TINYINT
		Begin
			return a;
		End//
	6	Select fn1(-2500)//

Actual Results :
	It displays the negative value properly.
	+------------+
	| fn1(-2500)  |
	+------------+
	|      -2500    |
	+------------+

Suggested fix:
	It should either truncate the value or throw exception with error message 'value out of range'.
[24 Feb 2005 9:09] Disha 
Updated OS
[24 Feb 2005 9:27] Disha 
Updated the synopsis.
[29 Apr 2005 12:36] Per-Erik Martin 
It now truncates the value:
mysql> Select fn1(-2500)//
+------------+
| fn1(-2500) |
+------------+
|       -128 |
+------------+
1 row in set (0.00 sec)

I will however keep this open until we have decided if this is still a bug or an acceptable result. (Should perhaps be an error in some modes.)
[13 May 2005 15:43] Trudy Pelzer 
When sql_mode='traditional', the function should return an
error when the value is out-of-range for the specified data
type. For numeric data types, the error should be
ERROR 1264 SQLSTATE 22003 Out of range value ...
[14 Sep 2005 13:26] Valeriy Kravchuk 
The problem still exists in 5.0.13-BK:

mysql> create function fn2 (a tinyint unsigned) returns tinyint begin   return a
; end//
Query OK, 0 rows affected (0,01 sec)

mysql> select fn2(-2500)//
+------------+
| fn2(-2500) |
+------------+
|       -128 |
+------------+
1 row in set (0,03 sec)

mysql> create table ttt (c1 tinyint unsigned)//
Query OK, 0 rows affected (0,01 sec)

mysql> insert into ttt values(-2500)//
Query OK, 1 row affected, 1 warning (0,00 sec)

mysql> show warnings//
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'c1' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select * from ttt//
+------+
| c1   |
+------+
|    0 |
+------+
1 row in set (0,00 sec)

So, I think the behaviour cosistent with table's one is really needed.
[7 Dec 2005 20:10] Paul DuBois 
Note in 5.0.18 changelog.