MySQL Bugs: #9298: Wrong handling of int4 unsigned columns in GROUP functions

Bug #9298	Wrong handling of int4 unsigned columns in GROUP functions
Submitted:	19 Mar 2005 18:24	Modified:	28 Apr 2005 23:54
Reporter:	Nicolas Moldavsky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.10a	OS:	Linux (RHEL 3)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
When doing a SELECT max()  or SELECT min() on an int4 unsigned column, MySQL returns negative numbers if there are values greater than 2^^31.

Tested it on SMP and non-SMP Linux x86 systems, using precompiled binaries from MySQL both in versions 4.1.9 and 4.1.10a.

Also tested it using the perl DBD::mysql module to ensure that the bug was not in the mysql command line client.

The problem can be found issuing a simple query like 
select min(col) from table.  
However if one issues 
select min(col+0) from table
the value returned is correct.
The value returned is also correct on
select min(col)+0 from table;

How to repeat:
[root@rolle mysql]# mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 103 to server version: 4.1.10a-standard-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> create table a (b int4 unsigned not null);
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into a values(3000000000);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from a;
+------------+
| b          |
+------------+
| 3000000000 |
+------------+
1 row in set (0.00 sec)
 
mysql> select max(b) from a;
+-------------+
| max(b)      |
+-------------+
| -1294967296 |
+-------------+
1 row in set (0.01 sec)
 
mysql> select min(b) from a;
+-------------+
| min(b)      |
+-------------+
| -1294967296 |
+-------------+
1 row in set (0.00 sec)
mysql> select max(b+0) from a;
+------------+
| max(b+0)   |
+------------+
| 3000000000 |
+------------+
1 row in set (0.00 sec)

This appears to be a problem within group-by processing, so reassigning to the optimizer team.

Internally, the Item_sum_max object appears to have its unsigned_flag member set properly. In the case of SUM(a) vs. SUM(a+0), the first is set to a type of MYSQL_TYPE_LONG, and the second to MYSQL_TYPE_LONGLONG.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24096

Approved by Sergei

Pushed into 4.1.12 and 5.0.6 trees

Noted in 4.1.12, 5.0.6 changelogs.