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

[19 Mar 2005 18:24] Nicolas Moldavsky
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)
[22 Mar 2005 20:54] Jim Winstead
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.
[18 Apr 2005 3:22] Bugs System
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
[27 Apr 2005 18:40] Sergey Petrunya
Approved by Sergei
[28 Apr 2005 17:12] Sergey Petrunya
Pushed into 4.1.12 and 5.0.6 trees
[28 Apr 2005 23:54] Paul Dubois
Noted in 4.1.12, 5.0.6 changelogs.