Bug #16436 out of range error

• Submitted: 12 Jan 2006 4:37
• Modified: 25 Feb 2006 13:43
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: mysql 5.0.18-essential
• OS: Windows (windows XP SP 2)
• CPU Architecture: Any

[12 Jan 2006 4:37] [ name withheld ]

Description:
ERROR 1264 (22003): Out of range value adjusted for column 'ab' at row 2

I checked the database to see if this bug has been reported. I saw similar ones reported but not an exact match and as its a fundamental bug (which didn't exist in 4.1.15) and dates back some time i felt it was necessary to raise it here. N.B. I'm using full releases (see below) on WinXP SP2. 

now see below

How to repeat:
I checked the database to see if this bug has been reported. I saw similar ones reported but not an exact match and as its a fundamental bug (which didn't exist in 4.1.15) and dates back some time i felt it was necessary to raise it here. N.B. I'm using full releases (see below) on WinXP SP2. 

using 4.1.15 essential... ()

use test;
create table differror (a tinyint unsigned,b tinyint unsigned,ab tinyint signed);
insert into differror values(42,21,0),(6,77,0);
update differror set ab = a-b;
select * from differror;

+------+------+------+
| a    | b    | ab   |
+------+------+------+
|   42 |   21 |   21 |
|    6 |   77 |  -71 |
+------+------+------+
2 rows in set (0.01 sec)

EVERYTHING WORKS FINE - NOW IN MYSQL 5.0.18 ESSENTIAL...

using 5.0.18 essential...

use test;
create table differror (a tinyint unsigned,b tinyint unsigned,ab tinyint signed);
insert into differror values(42,21,0),(6,77,0);
update differror set ab = a-b;

ERROR 1264 (22003): Out of range value adjusted for column 'ab' at row 2

The only 'get around' is to MODIFY all the fields to SIGNED, then the update takes place BUT your range of values is now halved.As a result I've had to update my database fields to SMALLINT which somewhat defeats the issue of keeping fields to their minimum size!

Any ideas as to how this bug has been introduced now ?!!

Suggested fix:
bug doesn't exist in 4.1.15 (see above) so look at the source code  here

[12 Jan 2006 8:53] Valeriy Kravchuk

Thank you for a problem report. Please, send the results of 

show variables like 'sql_m%';
show create table differror;

from your 4.1.15 and 5.0.18. 

You may find the difference and read the manual about it yourself also, because I believe it is not a bug, but the result of default sql_mode change on Windows...

[13 Feb 2006 0:02] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[25 Feb 2006 13:43] MySQL Verification Team

try using set sql_mode='NO_UNSIGNED_SUBTRACTION';