MySQL Bugs: #30580: max() is broken with unsigned data types

Bug #30580	max() is broken with unsigned data types
Submitted:	22 Aug 2007 23:26	Modified:	23 Aug 2007 20:42
Reporter:	dennis guba	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	4.1.11-Debian_4-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Debian, MAX, UNSIGNED

Description:
max() is broken with unsigned data types.  Only tested with unsigned integer.  Happens with both innodb and myisam.

Work around is to select max(col + 0).

How to repeat:
mysql> create table foo (id integer unsigned) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into foo values (2147483647);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values (4294967295);
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;max() is broken with unsigned data types
+------------+
| id         |
+------------+
| 2147483647 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)

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

mysql> select max(id + 0) from foo;
+-------------+
| max(id + 0) |
+-------------+
|  4294967295 |
+-------------+
1 row in set (0.00 sec)

mysql> create table bar (id integer unsigned) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bar values (2147483647);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bar values (4294967295);
Query OK, 1 row affected (0.00 sec)

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

Thank you for the bug report. I can't repeat the behavior reported with
current source server:

[miguel@skybr 4.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table foo (id integer unsigned) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo values (2147483647);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values (4294967295);
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo;
+------------+
| id         |
+------------+
| 2147483647 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)

mysql> select max(id) from foo;
+------------+
| max(id)    |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

mysql> select max(id + 0) from foo;
+-------------+
| max(id + 0) |
+-------------+
|  4294967295 |
+-------------+
1 row in set (0.00 sec)

mysql> create table bar (id integer unsigned) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bar values (2147483647);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bar values (4294967295);
Query OK, 1 row affected (0.00 sec)

mysql> select max(id) from bar;
+------------+
| max(id)    |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

mysql>

MySQL version I am using is 4.1.11-Debian_4-log.  I have repeated the bug on several installations.

MySQL version I am using is 4.1.11-Debian_4-log.  I have repeated the bug on several instalations of this version.

I tried to repeat the bug on 4.1.22-standard-log 64 bit, but I could not reproduce the bug.  Also tested on 4.1.15-Debian_1ubuntu5-log 32 bit, but this worked properly as well.

I have only been able to reproduce the bug on 4.1.11-Debian_4-log (which is kind of old I know)  We are upgrading to 4.1.22-standard-log 64 bit soon, so it will not be an issue after that.