Bug #8769 Functions: For Int datatypes, out of range values can be passed and returned.

• Submitted: 24 Feb 2005 9:23
• Modified: 7 Dec 2005 20:16
• Reporter: Disha
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.2
• OS: Linux (Linux, Windows)
• Assigned to: Alexander Nozdrin
• CPU Architecture: Any

[24 Feb 2005 9:23] Disha

Description:
Functions: For Int datatypes like TinyInt, SmallInt, MediumInt, out of range values can be passed as parameters and also returned.

Tiny INT datatype should support values between -128  and 127 only.
SmallINT datatype should support values between -32768 and 32767 only.
MediumInt able to hold values greater than 8388607 and also less than -8388608.

Following are the steps to repeat the issue in case on mediumint.

How to repeat:
	1	Delimiter        //
	2	Create database test1//
	3 	Use test1 //
	4	Drop function if exists fn1//
	5	Create function fn1 (a mediumint) Returns MEDIUMINT
		Begin
			return a;
		End//
	6	Select fn1(8388699)//

ACTUAL RESULTS at step 6:
	It displays the value properly.
	+--------------+
	| fn1(8388699) |
	+--------------+
	|      8388699 |
	+--------------+
	1 row in set (0.00 sec)

Suggested fix:
It should either truncate the value or throw exception with error message 'Value out of range'.

[24 Feb 2005 10:48] MySQL Verification Team

Verified with 5.0.3-alpha-log

[29 Apr 2005 12:38] Per-Erik Martin

The result is now:

mysql> Select fn1(8388699)//
+--------------+
| fn1(8388699) |
+--------------+
|      8388607 |
+--------------+
1 row in set (0.00 sec)

Still possibly a bug though. (See BUG#87698)

[13 May 2005 15:45] Trudy Pelzer

See bug#8768.
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 ...

[15 Sep 2005 10:18] Valeriy Kravchuk

Still the same erronious behaviour in 5.012-beta-nt:

mysql> select version()//
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

mysql> Create function fn1 (a mediumint) Returns MEDIUMINT
    ->          Begin
    ->                  return a;
    ->          End//
Query OK, 0 rows affected (0.12 sec)

mysql> select fn1(8388699)//
+--------------+
| fn1(8388699) |
+--------------+
|      8388607 |
+--------------+
1 row in set (0.09 sec)

mysql> set sql_mode='traditional'//
Query OK, 0 rows affected (0.04 sec)

mysql> select fn1(8388699)//
+--------------+
| fn1(8388699) |
+--------------+
|      8388607 |
+--------------+
1 row in set (0.00 sec)

mysql> create table t8769 (c1 mediumint);
    -> //
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t8769 values(8388699)//
ERROR 1264 (22003): Out of range value adjusted for column 'c1' at row 1

[7 Dec 2005 20:16] Paul DuBois

Noted in 5.0.18 changelog.