summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--libmysql/libmysql.c2
-rw-r--r--mysql-test/r/date_formats.result2
-rw-r--r--mysql-test/r/type_datetime.result61
-rw-r--r--mysql-test/t/type_datetime.test38
-rw-r--r--sql/item_cmpfunc.cc40
-rw-r--r--sql/item_cmpfunc.h8
-rw-r--r--sql/item_func.cc1
-rw-r--r--sql/item_func.h4
-rw-r--r--sql/item_timefunc.cc2
-rw-r--r--sql/mysql_priv.h1
-rw-r--r--sql/protocol.cc1
-rw-r--r--sql/sql_insert.cc5
12 files changed, 156 insertions, 9 deletions
diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c
index 85c56a7ea40..45d68fbbe5c 100644
--- a/libmysql/libmysql.c
+++ b/libmysql/libmysql.c
@@ -4355,6 +4355,7 @@ static my_bool setup_one_fetch_function(MYSQL_BIND *param, MYSQL_FIELD *field)
case MYSQL_TYPE_STRING:
case MYSQL_TYPE_DECIMAL:
case MYSQL_TYPE_NEWDECIMAL:
+ case MYSQL_TYPE_NEWDATE:
DBUG_ASSERT(param->buffer_length != 0);
param->fetch_result= fetch_result_str;
break;
@@ -4427,6 +4428,7 @@ static my_bool setup_one_fetch_function(MYSQL_BIND *param, MYSQL_FIELD *field)
case MYSQL_TYPE_VAR_STRING:
case MYSQL_TYPE_STRING:
case MYSQL_TYPE_BIT:
+ case MYSQL_TYPE_NEWDATE:
param->skip_result= skip_result_string;
break;
default:
diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index d62c865bb3c..6833a7f1594 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -481,7 +481,7 @@ str_to_date(a,b)
create table t2 select str_to_date(a,b) from t1;
describe t2;
Field Type Null Key Default Extra
-str_to_date(a,b) binary(29) YES NULL
+str_to_date(a,b) datetime YES NULL
select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
str_to_date("2003-01-02", "%Y-%m-%d") as f3,
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 9e47b5da2b6..c58ce3401fb 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -427,3 +427,64 @@ f1
Warnings:
Warning 1292 Incorrect datetime value: '2007010100000' for column 'f1' at row 1
drop table t1;
+#
+# Bug#27216: functions with parameters of different date types may
+# return wrong type of the result.
+#
+create table t1 (f1 date, f2 datetime, f3 varchar(20));
+create table t2 as select coalesce(f1,f1) as f4 from t1;
+desc t2;
+Field Type Null Key Default Extra
+f4 date YES NULL
+create table t3 as select coalesce(f1,f2) as f4 from t1;
+desc t3;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t4 as select coalesce(f2,f2) as f4 from t1;
+desc t4;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t5 as select coalesce(f1,f3) as f4 from t1;
+desc t5;
+Field Type Null Key Default Extra
+f4 varbinary(20) YES NULL
+create table t6 as select coalesce(f2,f3) as f4 from t1;
+desc t6;
+Field Type Null Key Default Extra
+f4 varbinary(20) YES NULL
+create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;
+desc t7;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4
+from t1;
+desc t8;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t9 as select case when 1 then cast('01-01-01' as date)
+when 0 then cast('01-01-01' as date) end as f4 from t1;
+desc t9;
+Field Type Null Key Default Extra
+f4 date YES NULL
+create table t10 as select case when 1 then cast('01-01-01' as datetime)
+when 0 then cast('01-01-01' as datetime) end as f4 from t1;
+desc t10;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t11 as select if(1, cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t11;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t12 as select least(cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t12;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t13 as select ifnull(cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t13;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;
+###################################################################
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index ffda593f320..8d68d11c0d6 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -282,3 +282,41 @@ select * from t1 where f1 between 20020101 and 20070101000000;
select * from t1 where f1 between 2002010 and 20070101000000;
select * from t1 where f1 between 20020101 and 2007010100000;
drop table t1;
+
+--echo #
+--echo # Bug#27216: functions with parameters of different date types may
+--echo # return wrong type of the result.
+--echo #
+create table t1 (f1 date, f2 datetime, f3 varchar(20));
+create table t2 as select coalesce(f1,f1) as f4 from t1;
+desc t2;
+create table t3 as select coalesce(f1,f2) as f4 from t1;
+desc t3;
+create table t4 as select coalesce(f2,f2) as f4 from t1;
+desc t4;
+create table t5 as select coalesce(f1,f3) as f4 from t1;
+desc t5;
+create table t6 as select coalesce(f2,f3) as f4 from t1;
+desc t6;
+create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;
+desc t7;
+create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4
+ from t1;
+desc t8;
+create table t9 as select case when 1 then cast('01-01-01' as date)
+ when 0 then cast('01-01-01' as date) end as f4 from t1;
+desc t9;
+create table t10 as select case when 1 then cast('01-01-01' as datetime)
+ when 0 then cast('01-01-01' as datetime) end as f4 from t1;
+desc t10;
+create table t11 as select if(1, cast('01-01-01' as datetime),
+ cast('01-01-01' as date)) as f4 from t1;
+desc t11;
+create table t12 as select least(cast('01-01-01' as datetime),
+ cast('01-01-01' as date)) as f4 from t1;
+desc t12;
+create table t13 as select ifnull(cast('01-01-01' as datetime),
+ cast('01-01-01' as date)) as f4 from t1;
+desc t13;
+drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;
+--echo ###################################################################
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 86eb10d50b0..1599bcc1571 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -147,6 +147,36 @@ static int agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems)
}
+/**
+ @brief Aggregates field types from the array of items.
+
+ @param[in] items array of items to aggregate the type from
+ @paran[in] nitems number of items in the array
+
+ @details This function aggregates field types from the array of items.
+ Found type is supposed to be used later as the result field type
+ of a multi-argument function.
+ Aggregation itself is performed by the Field::field_type_merge()
+ function.
+
+ @note The term "aggregation" is used here in the sense of inferring the
+ result type of a function from its argument types.
+
+ @return aggregated field type.
+*/
+
+enum_field_types agg_field_type(Item **items, uint nitems)
+{
+ uint i;
+ if (!nitems || items[0]->result_type() == ROW_RESULT )
+ return (enum_field_types)-1;
+ enum_field_types res= items[0]->field_type();
+ for (i= 1 ; i < nitems ; i++)
+ res= Field::field_type_merge(res, items[i]->field_type());
+ return res;
+}
+
+
static void my_coll_agg_error(DTCollation &c1, DTCollation &c2,
const char *fname)
{
@@ -2009,9 +2039,7 @@ Item_func_ifnull::fix_length_and_dec()
default:
DBUG_ASSERT(0);
}
- cached_field_type= args[0]->field_type();
- if (cached_field_type != args[1]->field_type())
- cached_field_type= Item_func::field_type();
+ cached_field_type= agg_field_type(args, 2);
}
@@ -2159,11 +2187,13 @@ Item_func_if::fix_length_and_dec()
{
cached_result_type= arg2_type;
collation.set(args[2]->collation.collation);
+ cached_field_type= args[2]->field_type();
}
else if (null2)
{
cached_result_type= arg1_type;
collation.set(args[1]->collation.collation);
+ cached_field_type= args[1]->field_type();
}
else
{
@@ -2177,6 +2207,7 @@ Item_func_if::fix_length_and_dec()
{
collation.set(&my_charset_bin); // Number
}
+ cached_field_type= agg_field_type(args + 1, 2);
}
if ((cached_result_type == DECIMAL_RESULT )
@@ -2556,7 +2587,7 @@ void Item_func_case::fix_length_and_dec()
agg_arg_charsets(collation, agg, nagg, MY_COLL_ALLOW_CONV, 1))
return;
-
+ cached_field_type= agg_field_type(agg, nagg);
/*
Aggregate first expression and all THEN expression types
and collations when string comparison
@@ -2695,6 +2726,7 @@ my_decimal *Item_func_coalesce::decimal_op(my_decimal *decimal_value)
void Item_func_coalesce::fix_length_and_dec()
{
+ cached_field_type= agg_field_type(args, arg_count);
agg_result_type(&hybrid_type, args, arg_count);
switch (hybrid_type) {
case STRING_RESULT:
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 8410c66b034..4d3df7aebf9 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -640,6 +640,7 @@ public:
class Item_func_coalesce :public Item_func_numhybrid
{
protected:
+ enum_field_types cached_field_type;
Item_func_coalesce(Item *a, Item *b) :Item_func_numhybrid(a, b) {}
public:
Item_func_coalesce(List<Item> &list) :Item_func_numhybrid(list) {}
@@ -652,13 +653,13 @@ public:
enum Item_result result_type () const { return hybrid_type; }
const char *func_name() const { return "coalesce"; }
table_map not_null_tables() const { return 0; }
+ enum_field_types field_type() const { return cached_field_type; }
};
class Item_func_ifnull :public Item_func_coalesce
{
protected:
- enum_field_types cached_field_type;
bool field_type_defined;
public:
Item_func_ifnull(Item *a, Item *b) :Item_func_coalesce(a,b) {}
@@ -677,6 +678,7 @@ public:
class Item_func_if :public Item_func
{
enum Item_result cached_result_type;
+ enum_field_types cached_field_type;
public:
Item_func_if(Item *a,Item *b,Item *c)
:Item_func(a,b,c), cached_result_type(INT_RESULT)
@@ -686,6 +688,7 @@ public:
String *val_str(String *str);
my_decimal *val_decimal(my_decimal *);
enum Item_result result_type () const { return cached_result_type; }
+ enum_field_types field_type() const { return cached_field_type; }
bool fix_fields(THD *, Item **);
void fix_length_and_dec();
uint decimal_precision() const;
@@ -722,6 +725,7 @@ class Item_func_case :public Item_func
uint ncases;
Item_result cmp_type;
DTCollation cmp_collation;
+ enum_field_types cached_field_type;
public:
Item_func_case(List<Item> &list, Item *first_expr_arg, Item *else_expr_arg)
:Item_func(), first_expr_num(-1), else_expr_num(-1),
@@ -749,6 +753,7 @@ public:
uint decimal_precision() const;
table_map not_null_tables() const { return 0; }
enum Item_result result_type () const { return cached_result_type; }
+ enum_field_types field_type() const { return cached_field_type; }
const char *func_name() const { return "case"; }
void print(String *str);
Item *find_item(String *str);
@@ -1382,6 +1387,7 @@ public:
bool subst_argument_checker(byte **arg) { return TRUE; }
Item *compile(Item_analyzer analyzer, byte **arg_p,
Item_transformer transformer, byte *arg_t);
+ enum_field_types field_type() const { return MYSQL_TYPE_LONGLONG; }
};
diff --git a/sql/item_func.cc b/sql/item_func.cc
index d03d497dfd0..f1c519896b4 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2243,6 +2243,7 @@ void Item_func_min_max::fix_length_and_dec()
else if ((cmp_type == DECIMAL_RESULT) || (cmp_type == INT_RESULT))
max_length= my_decimal_precision_to_length(max_int_part+decimals, decimals,
unsigned_flag);
+ cached_field_type= agg_field_type(args, arg_count);
}
diff --git a/sql/item_func.h b/sql/item_func.h
index 56b5e75652c..57e33daf0c4 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -692,7 +692,8 @@ class Item_func_min_max :public Item_func
/* An item used for issuing warnings while string to DATETIME conversion. */
Item *datetime_item;
THD *thd;
-
+protected:
+ enum_field_types cached_field_type;
public:
Item_func_min_max(List<Item> &list,int cmp_sign_arg) :Item_func(list),
cmp_type(INT_RESULT), cmp_sign(cmp_sign_arg), compare_as_dates(FALSE),
@@ -705,6 +706,7 @@ public:
enum Item_result result_type () const { return cmp_type; }
bool result_as_longlong() { return compare_as_dates; };
uint cmp_datetimes(ulonglong *value);
+ enum_field_types field_type() const { return cached_field_type; }
};
class Item_func_min :public Item_func_min_max
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index ae18e4786d7..b7c9086c127 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -3310,7 +3310,7 @@ void Item_func_str_to_date::fix_length_and_dec()
String format_str(format_buff, sizeof(format_buff), &my_charset_bin), *format;
maybe_null= 1;
decimals=0;
- cached_field_type= MYSQL_TYPE_STRING;
+ cached_field_type= MYSQL_TYPE_DATETIME;
max_length= MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
cached_timestamp_type= MYSQL_TIMESTAMP_NONE;
format= args[1]->val_str(&format_str);
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 44eb5590a28..5bec94857f7 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1220,6 +1220,7 @@ my_bool mysql_rm_tmp_tables(void);
/* item_func.cc */
extern bool check_reserved_words(LEX_STRING *name);
+extern enum_field_types agg_field_type(Item **items, uint nitems);
/* strfunc.cc */
ulonglong find_set(TYPELIB *lib, const char *x, uint length, CHARSET_INFO *cs,
diff --git a/sql/protocol.cc b/sql/protocol.cc
index ced6d78519a..2bdbe83eea1 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -824,6 +824,7 @@ bool Protocol_simple::store(const char *from, uint length,
field_types[field_pos] == MYSQL_TYPE_DECIMAL ||
field_types[field_pos] == MYSQL_TYPE_BIT ||
field_types[field_pos] == MYSQL_TYPE_NEWDECIMAL ||
+ field_types[field_pos] == MYSQL_TYPE_NEWDATE ||
(field_types[field_pos] >= MYSQL_TYPE_ENUM &&
field_types[field_pos] <= MYSQL_TYPE_GEOMETRY));
field_pos++;
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index f07af393070..fc403132240 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -3129,7 +3129,10 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info,
create_field *cr_field;
Field *field, *def_field;
if (item->type() == Item::FUNC_ITEM)
- field= item->tmp_table_field(&tmp_table);
+ if (item->result_type() != STRING_RESULT)
+ field= item->tmp_table_field(&tmp_table);
+ else
+ field= item->tmp_table_field_from_field_type(&tmp_table);
else
field= create_tmp_field(thd, &tmp_table, item, item->type(),
(Item ***) 0, &tmp_field, &def_field, 0, 0, 0, 0,
View Lorry Controller Status