Bug #7700 Server allows 'double unsigned' fields to contain negative numbers
Submitted: 6 Jan 2005 11:30 Modified: 15 Feb 2005 3:26
Reporter: Uzi Klein Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.22 OS:Linux (Red Hat Linux 9)
Assigned to: Jim Winstead CPU Architecture:Any

[6 Jan 2005 11:30] Uzi Klein
Description:
Whe one updates a double unsigned field with content of another field which is signed, the server allows the oporation.
normaly an unsigned field sould be 0 when trying to insert negative number.

How to repeat:
mysql> CREATE TABLE `TestDouble` (
    ->   `ID` int(10) unsigned NOT NULL auto_increment,
    ->   `DoubleField1` double default NULL,
    ->   `DoubleField2` double unsigned default NULL,
    ->   PRIMARY KEY  (`ID`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO TestDouble (DoubleField1, DoubleField2) VALUES ('-12345.123', '-12345.123');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM TestDouble;                                                                      
+----+--------------+--------------+
| ID | DoubleField1 | DoubleField2 |
+----+--------------+--------------+
|  1 |   -12345.123 |            0 |
+----+--------------+--------------+
1 row in set (0.00 sec)

mysql> UPDATE TestDouble SET DoubleField2=DoubleField1 WHERE ID=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM TestDouble;                                  
+----+--------------+--------------+
| ID | DoubleField1 | DoubleField2 |
+----+--------------+--------------+
|  1 |   -12345.123 |   -12345.123 |
+----+--------------+--------------+
1 row in set (0.01 sec)
[6 Jan 2005 11:40] Uzi Klein
When one updates a double unsigned field with content of another field which is
signed, the server allows the oporation.
normaly an unsigned field should be 0 when trying to insert negative number.
[6 Jan 2005 11:49] MySQL Verification Team
Verified with 4.0.24-debug-log
[18 Jan 2005 0:21] Jim Winstead
This was a side-effect of an optimization to directly copy values between fields that were thought to be of identical types.
[18 Jan 2005 23:07] Jim Winstead
Fixed in the 4.0 tree.
[15 Feb 2005 3:26] Paul DuBois
Mentioned in 4.0.24 change notes.