MySQL Bugs: #49003: union converts unsigned mediumint and bigint to signed

Bug #49003	union converts unsigned mediumint and bigint to signed
Submitted:	23 Nov 2009 22:04	Modified:	18 Jan 2013 1:58
Reporter:	jianing hu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.45, 5.0.67, 5.0.89, 5.1.42	OS:	Linux (CentOS 5.2)
Assigned to:		CPU Architecture:	Any

Description:
In a union select that involves subqueries and constant rows, unsigned mediumint and bigint columns are converted into signed values.

Unsigned tinyint, smallint, and int don't seem to have this problem.

How to repeat:
create table test (
  id mediumint unsigned
) engine=InnoDB

insert into test values (8388608);

(select 1)  union (select max(id)  from test ) union  (select * from (select max(t.id)  from test t ) as t1 );
+----------+
| 1        |
+----------+
|        1 |
|  8388607 |
| -8388608 |
+----------+

Results from the first query got converted to signed mediumint and truncated.
Results from the second query got converted to signed mediumint but kept all its bits.

Actually, result depends on order of SELECTs in UNION:

mysql> create table test (
    ->   id mediumint unsigned
    -> ) engine=InnoDB
    -> ;
Query OK, 0 rows affected (0.04 sec)

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

mysql>  (select max(id)  from test ) union  (select * from (select max(t.id)  from test t ) as t1 ) union (select 1);
+---------+
| max(id) |
+---------+
| 8388608 | 
|       1 | 
+---------+
2 rows in set (0.01 sec)

mysql> (select 1)  union (select max(id)  from test ) union  (select * from (select max(t.id)  from test t ) as t1 );
+----------+
| 1        |
+----------+
|        1 | 
|  8388607 | 
| -8388608 | 
+----------+
3 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.89-debug | 
+--------------+
1 row in set (0.00 sec)

While manual (http://dev.mysql.com/doc/refman/5.0/en/union.html) just says:

"If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements."

I found the most probable bug. There is a small error in merging types in the field.cc, in the array field_types_merge_rules.

There, mixing LONG INT and MEDIUM INT leads to MEDIUM INT. It should lead to LONG INT>

It is good in the vector where INT24 is converted to LONG, but wrong where LONG is converted to INT24.

This patch effectively solves this problem. All tests pass:

=== modified file 'sql/field.cc'
--- sql/field.cc        2009-09-29 10:58:42 +0000
+++ sql/field.cc        2010-02-12 17:20:19 +0000
@@ -183,7 +183,7 @@
   //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
     MYSQL_TYPE_LONG,         MYSQL_TYPE_VARCHAR,
   //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
-    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_INT24,
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_LONG,
   //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
     MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
   //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR

There are no regressions !!

There are more conversion errors as well:
FLOAT    + INT24 gives INT24
DOUBLE   + INT24 gives INT24
LONGLONG + INT24 gives LONG

And why does <numeric type> + YEAR give <numeric type>, while all other datetime types are:
<numeric type> + <datetime> gives VARCHAR

Another anomaly:
FLOAT + DECIMAL gives DOUBLE
FLOAT + TINY    gives FLOAT
FLOAT + SHORT   gives FLOAT
FLOAT + LONG    gives DOUBLE

It would be more consistent if all these results were DOUBLE.

Another:

LONGLONG + NEWDATE gives NEWDATE

while

SHORT + NEWDATE gives VARCHAR.

Noted in 5.1.68, 5.5.30, 5.6.11, 5.7.1 changelogs.

UNION type conversion could incorrectly turn unsigned values into
signed values.