MySQL Bugs: #27153: create table from subquery with IF statements is varbinary rather than datetime

Bug #27153	create table from subquery with IF statements is varbinary rather than datetime
Submitted:	14 Mar 2007 20:37	Modified:	21 Nov 2007 10:40
Reporter:	Brian Duggan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.38-BK, 5.0.26	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When creating a table with a subquery that involves an IF() expression, the column type of the table isn't assigned to be "datetime" even if the expression always evaluates as a datetime.

How to repeat:
create table foo_one (x datetime);

create table foo_two select IF(1,x,x) from foo_one;

describe foo_two;

+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| IF(1,x,x) | varbinary(19) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

Suggested fix:
Not a fix, but a workaround is to use STR_TO_DATE :

create table foo_one (x datetime);

create table foo_two select STR_TO_DATE(IF(1,x,x),'%Y-%m-%d %H:%i:%S') from foo_one;

describe foo_two;

+--------------------------------------------+----------+------+-----+---------+-------+
| Field                                      | Type     | Null | Key | Default | Extra |
+--------------------------------------------+----------+------+-----+---------+-------+
| STR_TO_DATE(IF(1,x,x),'%Y-%m-%d %H:%i:%S') | datetime | YES  |     | NULL    |       |
+--------------------------------------------+----------+------+-----+---------+-------+

I think a fix would be to parse the possible return types of the IF statement and smartly assign the column type based on this parsing.

thanks

Thank you for a bug report. Verified just as described with latest 5.0.38-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table foo_one (x datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> create table foo_two select IF(1,x,x) from foo_one;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table foo_two\G
*************************** 1. row ***************************
       Table: foo_two
Create Table: CREATE TABLE `foo_two` (
  `IF(1,x,x)` varbinary(19) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

See also 

Bug#20765: Subselect causes datatype to be changed erroneously.
Bug#27216: coalesce returns varbinary for dates.

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/31813

ChangeSet@1.2498, 2007-07-30 14:08:54+02:00, mhansson@linux-st28.site +5 -0
  Bug #27153: create table from subquery with IF statements is varbinary rather than datetime
  
  The result field type of the IF() function was determined based on the
  aggregation of the argument fields' result types. As this type is always
  either STRING for if, the type is not preserved.
  
  Fixed by doing aggregation on the field types of the arguments instead, and by
  adding the field cached_field_type to Item_func_if, and return this from
  field_type(). This field is initialized in fix_length_and_dec.
  
  See also bug#27216.

This bug was fixed along with Bug#27216. It was also documented along with that bug. Here is the change set comment for that bug:

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.