Bug #14651 MAX() returns -1 on UNSIGNED INT with maximum value
Submitted: 4 Nov 2005 18:30 Modified: 9 Dec 2005 18:54
Reporter: David Smith Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.11-nt OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[4 Nov 2005 18:30] David Smith
Description:
If the max() function is called on an unsigned int column containing the maximum allowable value in one of its records, the return result is -1.  The return  result should be the actual value that is retrieved.

How to repeat:
CREATE TABLE test (num INT UNSIGNED);
mysql> INSERT INTO test VALUES (10000000000000000);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+
| num        |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

mysql> select max(num) from test;
+----------+
| max(num) |
+----------+
|       -1 |
+----------+
1 row in set (0.00 sec)

Suggested fix:
Fix a possible overflow that is occurring in the MAX() function.
[5 Nov 2005 11:12] Valeriy Kravchuk
Thank you for a problem report. Please, send the correct version of MySQL server used (the results of SELECT version(); statement).
[7 Nov 2005 16:49] David Smith
The server version is 4.1.11-nt
[9 Nov 2005 18:54] Valeriy Kravchuk
I was not able to repeat on current 4.1.16-BK build on Linux:

mysql> CREATE TABLE test (num INT UNSIGNED);
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO test VALUES (10000000000000000);
Query OK, 1 row affected, 1 warning (0,01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1264 | Data truncated; out of range for column 'num' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0,01 sec)

mysql> select * from test;
+------------+
| num        |
+------------+
| 4294967295 |
+------------+
1 row in set (0,01 sec)

mysql> select max(num) from test;
+------------+
| max(num)   |
+------------+
| 4294967295 |
+------------+
1 row in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.16    |
+-----------+
1 row in set (0,00 sec)

So, please, try to use newer version (4.1.15) on Windows and inform if the bug is still there.
[10 Dec 2005 0:00] 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".