MySQL Bugs: #12185: Data type of aggregation is sometimes questionable

Bug #12185	Data type of aggregation is sometimes questionable
Submitted:	26 Jul 2005 19:44	Modified:	18 Sep 2006 8:23
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.11-beta-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Sometimes MySQL returns an apparently wrong
data type for the result of an aggregation, for
example combining a CHAR with a CHAR and
ending up with a VARCHAR. The rules should
be the same for any aggregation, including
UNION, CONCAT, MIN/MAX, LEAST, CASE.
But I will just describe how to repeat for UNION.

CREATE TABLE t1 (s1 INT, s2 INT);
CREATE TABLE t2 AS
  SELECT s1 FROM t1
  UNION
  SELECT s2 FROM t1;
SHOW CREATE TABLE t2;

With the above method, it's easy to see that
the result of the UNION of two INTs is INT,
or in my notation: "INT + INT -> INT". Fine.
But the following combinations are not fine:

char(1) + char(1) -> varchar(1). Should be CHAR!

varchar(1) character set utf8 collate utf8_general_ci + varchar(1) character set utf8)
-> varchar(1). Should be character set utf8!

binary(5) + binary(5) -> varbinary(5). Should be BINARY!

timestamp + timestamp -> datetime. Should be TIMESTAMP!

varchar(1) + text -> longtext. Should be TEXT!

null + char(1) -> varchar(4). Should be CHAR!

char + group_concat(char,'') -> longtext. Should be CHAR!
(this detail is also noted in bug#8568)

int zerofill + int zerofill -> int unsigned. Should be INT!
(this detail is also noted in bug#9707. i believe the rule
should be: since NOT NULL + NOT NULL -> NOT NULL,
and since UNSIGNED + UNSIGNED -> UNSIGNED, by
analogy it should be true that ZEROFILL + ZEROFILL
-> ZEROFILL. but it's okay to decide this detail is
"not a bug".)

How to repeat:
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 char(1), s2 char(1));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 as select s1 from t1 union select s1 from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+------------------------------------------------------+
| Table | Create Table                                         |
+-------+------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "s1" varchar(1) default NULL
) |
+-------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 varchar(1) character set utf8 collate utf8_general_ci, s2 varchar(1) character set utf8);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 as select s1 from t1 union select s1 from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+------------------------------------------------------+
| Table | Create Table                                         |
+-------+------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "s1" varchar(1) default NULL
) |
+-------+------------------------------------------------------+
1 row in set (0.01 sec)

mysql>
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 binary(5), s2 binary(5));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 as select s1 from t1 union select s1 from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "s1" varbinary(5) default NULL
) |
+-------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 timestamp, s2 timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 as select s1 from t1 union select s1 from t1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+------------------------------------------------------------------------------+
| Table | Create Table                                                                 |
+-------+------------------------------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "s1" datetime NOT NULL default '0000-00-00 00:00:00'
) |
+-------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 varchar(1), s2 text);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 as select s1 from t1 union select s1 from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+------------------------------------------------------+
| Table | Create Table                                         |
+-------+------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "s1" varchar(1) default NULL
) |
+-------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 char(1), s2 char(1));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 as select null from t1 union select s1 from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "NULL" varchar(4) default NULL
) |
+-------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 char(1), s2 char(1));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 as select group_concat(s1,'') from t1 union select s1 from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "group_concat(s1,'')" longtext
) |
+-------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 int zerofill, s2 int zerofill);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 as select s1 from t1 union select s1 from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+------------------------------------------------------------+
| Table | Create Table                                               |
+-------+------------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "s1" int(11) unsigned default NULL
) |
+-------+------------------------------------------------------------+
1 row in set (0.00 sec)

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/commits/9249

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/commits/9438

The Item::tmp_table_field_from_field_type() function creates Field_datetime
object instead of Field_timestamp object for timestamp field thus always
changing data type is a tmp table is used.
  
The Field_blob object constructor which is used in the 
Item::tmp_table_field_from_field_type() is always setting packlength field of
newly created blob to 4. This leads to changing fields data type for example
from the blob to the longblob if a temporary table is used.
  
The Item::make_string_field() function always converts Field_string objects 
to Field_varstring objects. This leads to changing data type from the 
char/binary to varchar/varbinary.

Fixed in 5.0.25, 5.1.12

Please check with other aggregations as well. Example:

mysql> create table t1 (s1 timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 as select case when s1 then s1 else s1 end from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `case when s1 then s1 else s1 end` varbinary(19) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Bug #16584 resolved as a sub-case of this one.

fixed by inno patchset merged into 5.0.25 (5.0-maint)