Bug #27216 coalesce returns varbinary for dates
Submitted: 16 Mar 2007 14:16 Modified: 30 Oct 2007 23:42
Reporter: Hans Ginzel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26-community-nt, 5.1, 5.2-falcon, 4.1 OS:Linux (Linux, MS Windows XP)
Assigned to: Evgeny Potemkin
Tags: colesce, date, types, varbinary

[16 Mar 2007 14:16] Hans Ginzel
Description:
COALESCE(date, date) returns varbinary

How to repeat:
mysql> create table aa (c date, d date);
mysql> create table bb as select coalesce(c,d) as e from aa;
mysql> desc bb;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| e     | varbinary(10) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

Suggested fix:
proper type checking
[16 Mar 2007 14:17] Hans Ginzel
Example should return date.
[16 Mar 2007 14:25] Sveta Smirnova
Thank you for the report.

Verified as described.
[15 May 2007 13:35] 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/commits/26695

ChangeSet@1.2482, 2007-05-15 17:32:52+04:00, evgen@moonbone.local +7 -0
  Bug#27216: coalesce() returns wrong result type for dates arguments.
  
  The result field type of the COALESCE() function was determined based on
  the aggregated result type of its arguments. As all DATE/DATETIME
  fields/functions have the STRING result type the result field type
  of the COALESCE() function was always STRING for DATE/DATETIME arguments.
  
  Now the COALESCE() function aggregates field types of its arguments rather
  than result types and returns the result of aggregation as its result field
  type.
  The cached_field_type member variable is added to the Item_func_coalesce class.
  The str_to_date() function's result field type now defaults to the
  MYSQL_TYPE_DATETIME.
  The agg_field_type() function is added. It aggregates field types with help
  of the Field::field_type_merge() function.
  The create_table_from_items() function now uses the 
  item->tmp_table_field_from_field_type() function to get the proper field
  when the item is a function with the STRING result type.
[20 Sep 2007 16:36] 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/commits/34445

ChangeSet@1.2529, 2007-09-20 18:35:16+04:00, evgen@sunlight.local +11 -0
  Bug#27216: functions with parameters of different date types may return wrong
  type of the result.
  
  There is several functions that accepts parameters of a different types.
  The result field type of such functions was determined based on
  the aggregated result type of its arguments. As all DATE/DATETIME
  fields/functions have the STRING result type the result field type
  of affected functions was always STRING for DATE/DATETIME arguments.
  Affected functions are COALESCE, IF, IFNULL, CASE, LEAST/GREATEST, CASE.
  
  Now affected functions aggregate field types of their arguments rather
  than result types and return the result of aggregation as their result field
  type.
  The cached_field_type member variable is added to the number of classes to
  hold the aggregated result field type.
  The str_to_date() function's result field type now defaults to the
  MYSQL_TYPE_DATETIME.
  The agg_field_type() function is added. It aggregates field types with help
  of the Field::field_type_merge() function.
  The create_table_from_items() function now uses the 
  item->tmp_table_field_from_field_type() function to get the proper field
  when the item is a function with the STRING result type.
[22 Sep 2007 9:52] 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/commits/34484

ChangeSet@1.2529, 2007-09-22 11:49:27+04:00, evgen@sunlight.local +12 -0
  Bug#27216: functions with parameters of different date types may return wrong
  type of the result.
  
  There are several functions that accept parameters of different types.
  The result field type of such functions was determined based on
  the aggregated result type of its arguments. As the DATE and the DATETIME
  types are represented by the STRING type, the result field type
  of the affected functions was always STRING for DATE/DATETIME arguments.
  The affected functions are COALESCE, IF, IFNULL, CASE, LEAST/GREATEST, CASE.
  
  Now the affected functions aggregate the field types of their arguments rather
  than their result types and return the result of aggregation as their result
  field type.
  The cached_field_type member variable is added to the number of classes to
  hold the aggregated result field type.
  The str_to_date() function's result field type now defaults to the
  MYSQL_TYPE_DATETIME.
  The agg_field_type() function is added. It aggregates field types with help
  of the Field::field_type_merge() function.
  The create_table_from_items() function now uses the 
  item->tmp_table_field_from_field_type() function to get the proper field
  when the item is a function with a STRING result type.
[29 Oct 2007 8:42] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:45] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:48] Bugs System
Pushed into 6.0.4-alpha
[30 Oct 2007 23:42] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

Several functions and operators returned an incorrect result type
(string) when given DATE parameters: COALESCE(), IF(), IFNULL(),
LEAST(), GREATEST(), CASE. These now aggregate DATE (or DATETIME)
parameters to produce a DATE (or DATETIME result. In addition, the
result type of the STR_TO_DATE() function is now DATETIME by default.
[19 Mar 2009 21:19] Vaibogam Shanmugam
Here is the workaround for this issue. Just cast the output of coalesce or case  to DATE.

create table bb as select date(coalesce(c,d)) as e from aa;

or

create table bb as select cast(coalesce(c,d) as date) as e from aa;