MySQL Bugs: #27216: coalesce returns varbinary for dates

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	CPU Architecture:	Any
Tags:	colesce, date, types, varbinary

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

Example should return date.

Thank you for the report.

Verified as described.

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.

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.

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.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

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.

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;