MySQL Bugs: #59132: min() and max() remove unsignedness

Bug #59132	min() and max() remove unsignedness
Submitted:	23 Dec 2010 14:15	Modified:	19 Apr 2011 17:05
Reporter:	Ralf Neubauer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.51, 5.1.54, 5.1.55	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
The result of min(bigint unsigned) and max(bigint unsigned) seems to be interpreted as bigint signed, if used as a function argument. In other cases it is still unsigned.

I can't find this behaviour documented (maybe I'm just looking at the wrong places), but it was very surprising to see the result change from 20 to 15, when I changed max(length(col)) to the more optimizable length(max(col)) in a statement, these should be equivalent for positive numbers, I thought.

mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.1.51-enterprise-gpl-advanced |
+--------------------------------+
1 row in set (0.00 sec)

mysql> create temporary table a ( a bigint unsigned );
Query OK, 0 rows affected (0.00 sec)

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

mysql> select max(length(a)), length(max(a)), min(a), max(a), concat(min(a)), concat(max(a)), concat(sum(a)), concat(avg(a)) from a;
+----------------+----------------+----------------------+----------------------+-----------------+-----------------+----------------------+---------------------------+
| max(length(a)) | length(max(a)) | min(a)               | max(a)               | concat(min(a))  | concat(max(a))  | concat(sum(a))       | concat(avg(a))            |
+----------------+----------------+----------------------+----------------------+-----------------+-----------------+----------------------+---------------------------+
|             20 |             15 | 18446668621106209655 | 18446668621106209655 | -75452603341961 | -75452603341961 | 18446668621106209655 | 18446668621106209655.0000 |
+----------------+----------------+----------------------+----------------------+-----------------+-----------------+----------------------+---------------------------+
1 row in set (0.00 sec)

mysql> select length(0+max(a)), concat(0+max(a)), concat(max(0+a)) from a;
+------------------+----------------------+------------------+
| length(0+max(a)) | concat(0+max(a))     | concat(max(0+a)) |
+------------------+----------------------+------------------+
|               20 | 18446668621106209655 | -75452603341961  |
+------------------+----------------------+------------------+
1 row in set (0.00 sec)

mysql> select length(a), a, length(cast(a as signed)), cast(a as signed) from a;
+-----------+----------------------+---------------------------+-------------------+
| length(a) | a                    | length(cast(a as signed)) | cast(a as signed) |
+-----------+----------------------+---------------------------+-------------------+
|        20 | 18446668621106209655 |                        15 |   -75452603341961 |
+-----------+----------------------+---------------------------+-------------------+
1 row in set (0.00 sec)

mysql>

How to repeat:
select version();
create temporary table a ( a bigint unsigned );
insert into a(a) values (18446668621106209655);
select max(length(a)), length(max(a)), min(a), max(a), concat(min(a)), concat(max(a)), concat(sum(a)), concat(avg(a)) from a;
select length(0+max(a)), concat(0+max(a)), concat(max(0+a)) from a;
select length(a), a, length(cast(a as signed)), cast(a as signed) from a;

Verified with 5.1.54:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 --column-
type-info test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create temporary table a ( a bigint unsigned );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into a(a) values (18446668621106209655);
Query OK, 1 row affected (0.03 sec)

mysql> select max(a), a, concat(max(a)), concat(a) from a;
Field   1:  `max(a)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 20
Decimals:   0
Flags:      UNSIGNED BINARY NUM

Field   2:  `a`
Catalog:    `def`
Database:   `test`
Table:      `a`
Org_table:  `a`
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 20
Decimals:   0
Flags:      UNSIGNED NUM

Field   3:  `concat(max(a))`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     20
Max_length: 15
Decimals:   31
Flags:      BINARY

Field   4:  `concat(a)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     20
Max_length: 20
Decimals:   31
Flags:      BINARY

+----------------------+----------------------+-----------------+---------------
-------+
| max(a)               | a                    | concat(max(a))  | concat(a)
       |
+----------------------+----------------------+-----------------+---------------
-------+
| 18446668621106209655 | 18446668621106209655 | -75452603341961 | 18446668621106
209655 |
+----------------------+----------------------+-----------------+---------------
-------+
1 row in set (0.02 sec)

I do not see this (why concat(max(a)) may produce results different from concat(a)) documented at http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat in any way.

This also happens under linux:

Server version: 5.1.49-3 (Debian)

mysql> select max(a), a, concat(max(a)), concat(a) from ( select 18446668621106209655 a ) a;
+----------------------+----------------------+-----------------+----------------------+
| max(a)               | a                    | concat(max(a))  | concat(a)            |
+----------------------+----------------------+-----------------+----------------------+
| 18446668621106209655 | 18446668621106209655 | -75452603341961 | 18446668621106209655 |
+----------------------+----------------------+-----------------+----------------------+

and even:

mysql> select concat(18446668621106209655), concat(max(18446668621106209655));
+------------------------------+-----------------------------------+
| concat(18446668621106209655) | concat(max(18446668621106209655)) |
+------------------------------+-----------------------------------+
| 18446668621106209655         | -75452603341961                   |
+------------------------------+-----------------------------------+
1 row in set (0.00 sec)

Noted in 5.1.57, 5.5.12, 5.6.3 changelogs.

In string context, the MIN() and MAX() functions did not take into
account the unsignedness of a BIGINT UNSIGNED argument.

CHANGESET - http://lists.mysql.com/commits/134400