summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/sp-vars.result4
-rw-r--r--mysql-test/r/type_datetime.result30
-rw-r--r--mysql-test/t/type_datetime.test26
-rw-r--r--sql/item.cc1
-rw-r--r--sql/item.h3
-rw-r--r--sql/item_cmpfunc.cc4
-rw-r--r--sql/item_func.cc90
-rw-r--r--sql/item_func.h10
-rw-r--r--sql/item_subselect.cc2
-rw-r--r--sql/mysql_priv.h2
-rw-r--r--sql/sql_select.cc4
11 files changed, 167 insertions, 9 deletions
diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result
index a9024156c6e..7d5b71cb67d 100644
--- a/mysql-test/r/sp-vars.result
+++ b/mysql-test/r/sp-vars.result
@@ -690,12 +690,12 @@ END|
CALL p1(NOW());
Table Create Table
t1 CREATE TABLE "t1" (
- "x" varbinary(19) DEFAULT NULL
+ "x" datetime default NULL
)
CALL p1('test');
Table Create Table
t1 CREATE TABLE "t1" (
- "x" varbinary(19) DEFAULT NULL
+ "x" datetime default NULL
)
Warnings:
Warning 1264 Out of range value for column 'x' at row 1
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index d90bf1779c1..b12d3cbde79 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -264,6 +264,36 @@ f2
SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
1
drop table t1;
+select least(cast('01-01-01' as date), '01-01-02');
+least(cast('01-01-01' as date), '01-01-02')
+2001-01-01
+select greatest(cast('01-01-01' as date), '01-01-02');
+greatest(cast('01-01-01' as date), '01-01-02')
+01-01-02
+select least(cast('01-01-01' as date), '01-01-02') + 0;
+least(cast('01-01-01' as date), '01-01-02') + 0
+20010101
+select greatest(cast('01-01-01' as date), '01-01-02') + 0;
+greatest(cast('01-01-01' as date), '01-01-02') + 0
+20010102
+select least(cast('01-01-01' as datetime), '01-01-02') + 0;
+least(cast('01-01-01' as datetime), '01-01-02') + 0
+20010101000000
+DROP PROCEDURE IF EXISTS test27759 ;
+CREATE PROCEDURE test27759()
+BEGIN
+declare v_a date default '2007-4-10';
+declare v_b date default '2007-4-11';
+declare v_c datetime default '2004-4-9 0:0:0';
+select v_a as a,v_b as b,
+least( v_a, v_b ) as a_then_b,
+least( v_b, v_a ) as b_then_a,
+least( v_c, v_a ) as c_then_a;
+END;|
+call test27759();
+a b a_then_b b_then_a c_then_a
+2007-04-10 2007-04-11 2007-04-10 2007-04-10 2004-04-09 00:00:00
+drop procedure test27759;
set @org_mode=@@sql_mode;
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
Warnings:
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index cdf0e1464dc..7a728a09831 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -180,6 +180,32 @@ SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRE
drop table t1;
#
+# Bug#27759: Wrong DATE/DATETIME comparison in LEAST()/GREATEST() functions.
+#
+select least(cast('01-01-01' as date), '01-01-02');
+select greatest(cast('01-01-01' as date), '01-01-02');
+select least(cast('01-01-01' as date), '01-01-02') + 0;
+select greatest(cast('01-01-01' as date), '01-01-02') + 0;
+select least(cast('01-01-01' as datetime), '01-01-02') + 0;
+--disable_warnings
+DROP PROCEDURE IF EXISTS test27759 ;
+--enable_warnings
+DELIMITER |;
+CREATE PROCEDURE test27759()
+BEGIN
+declare v_a date default '2007-4-10';
+declare v_b date default '2007-4-11';
+declare v_c datetime default '2004-4-9 0:0:0';
+select v_a as a,v_b as b,
+ least( v_a, v_b ) as a_then_b,
+ least( v_b, v_a ) as b_then_a,
+ least( v_c, v_a ) as c_then_a;
+END;|
+DELIMITER ;|
+call test27759();
+drop procedure test27759;
+
+#
# Test of storing datetime into date fields
#
diff --git a/sql/item.cc b/sql/item.cc
index f339bad78e4..e39a1199bae 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1057,6 +1057,7 @@ Item_splocal::Item_splocal(const LEX_STRING &sp_var_name,
maybe_null= TRUE;
m_type= sp_map_item_type(sp_var_type);
+ m_field_type= sp_var_type;
m_result_type= sp_map_result_type(sp_var_type);
}
diff --git a/sql/item.h b/sql/item.h
index 8b57f831cbd..957edbb8f0d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1041,7 +1041,7 @@ class Item_splocal :public Item_sp_variable,
Type m_type;
Item_result m_result_type;
-
+ enum_field_types m_field_type;
public:
/*
Position of this reference to SP variable in the statement (the
@@ -1073,6 +1073,7 @@ public:
inline enum Type type() const;
inline Item_result result_type() const;
+ inline enum_field_types field_type() const { return m_field_type; }
private:
bool set_value(THD *thd, sp_rcontext *ctx, Item **it);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index ff7b991448c..7b55abe8fc7 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -802,7 +802,7 @@ void Arg_comparator::set_datetime_cmp_func(Item **a1, Item **b1)
obtained value
*/
-static ulonglong
+ulonglong
get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
Item *warn_item, bool *is_null)
{
@@ -838,7 +838,7 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
}
- if (item->const_item())
+ if (item->const_item() && cache_arg)
{
Item_cache_int *cache= new Item_cache_int();
/* Mark the cache as non-const to prevent re-caching. */
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 9219c01ccd0..33521edbbd4 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2182,6 +2182,7 @@ double Item_func_units::val_real()
void Item_func_min_max::fix_length_and_dec()
{
int max_int_part=0;
+ bool datetime_found= FALSE;
decimals=0;
max_length=0;
maybe_null=0;
@@ -2195,18 +2196,88 @@ void Item_func_min_max::fix_length_and_dec()
if (args[i]->maybe_null)
maybe_null=1;
cmp_type=item_cmp_type(cmp_type,args[i]->result_type());
+ if (args[i]->result_type() != ROW_RESULT && args[i]->is_datetime())
+ {
+ datetime_found= TRUE;
+ if (!datetime_item || args[i]->field_type() == MYSQL_TYPE_DATETIME)
+ datetime_item= args[i];
+ }
}
if (cmp_type == STRING_RESULT)
+ {
agg_arg_charsets(collation, args, arg_count, MY_COLL_CMP_CONV, 1);
+ if (datetime_found)
+ {
+ thd= current_thd;
+ compare_as_dates= TRUE;
+ }
+ }
else if ((cmp_type == DECIMAL_RESULT) || (cmp_type == INT_RESULT))
max_length= my_decimal_precision_to_length(max_int_part+decimals, decimals,
unsigned_flag);
}
+/*
+ Compare item arguments in the DATETIME context.
+
+ SYNOPSIS
+ cmp_datetimes()
+ value [out] found least/greatest DATE/DATETIME value
+
+ DESCRIPTION
+ Compare item arguments as DATETIME values and return the index of the
+ least/greatest argument in the arguments array.
+ The correct integer DATE/DATETIME value of the found argument is
+ stored to the value pointer, if latter is provided.
+
+ RETURN
+ 0 If one of arguments is NULL
+ # index of the least/greatest argument
+*/
+
+uint Item_func_min_max::cmp_datetimes(ulonglong *value)
+{
+ ulonglong min_max;
+ uint min_max_idx= 0;
+ LINT_INIT(min_max);
+
+ for (uint i=0; i < arg_count ; i++)
+ {
+ Item **arg= args + i;
+ bool is_null;
+ ulonglong res= get_datetime_value(thd, &arg, 0, datetime_item, &is_null);
+ if ((null_value= args[i]->null_value))
+ return 0;
+ if (i == 0 || (res < min_max ? cmp_sign : -cmp_sign) > 0)
+ {
+ min_max= res;
+ min_max_idx= i;
+ }
+ }
+ if (value)
+ {
+ *value= min_max;
+ if (datetime_item->field_type() == MYSQL_TYPE_DATE)
+ *value/= 1000000L;
+ }
+ return min_max_idx;
+}
+
+
String *Item_func_min_max::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
+ if (compare_as_dates)
+ {
+ String *str_res;
+ uint min_max_idx= cmp_datetimes(NULL);
+ if (null_value)
+ return 0;
+ str_res= args[min_max_idx]->val_str(str);
+ str_res->set_charset(collation.collation);
+ return str_res;
+ }
switch (cmp_type) {
case INT_RESULT:
{
@@ -2271,6 +2342,12 @@ double Item_func_min_max::val_real()
{
DBUG_ASSERT(fixed == 1);
double value=0.0;
+ if (compare_as_dates)
+ {
+ ulonglong result;
+ (void)cmp_datetimes(&result);
+ return (double)result;
+ }
for (uint i=0; i < arg_count ; i++)
{
if (i == 0)
@@ -2292,6 +2369,12 @@ longlong Item_func_min_max::val_int()
{
DBUG_ASSERT(fixed == 1);
longlong value=0;
+ if (compare_as_dates)
+ {
+ ulonglong result;
+ (void)cmp_datetimes(&result);
+ return (longlong)result;
+ }
for (uint i=0; i < arg_count ; i++)
{
if (i == 0)
@@ -2315,6 +2398,13 @@ my_decimal *Item_func_min_max::val_decimal(my_decimal *dec)
my_decimal tmp_buf, *tmp, *res;
LINT_INIT(res);
+ if (compare_as_dates)
+ {
+ ulonglong value;
+ (void)cmp_datetimes(&value);
+ ulonglong2decimal(value, dec);
+ return dec;
+ }
for (uint i=0; i < arg_count ; i++)
{
if (i == 0)
diff --git a/sql/item_func.h b/sql/item_func.h
index 6457013b160..10464a408a7 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -708,9 +708,16 @@ class Item_func_min_max :public Item_func
Item_result cmp_type;
String tmp_value;
int cmp_sign;
+ /* TRUE <=> arguments should be compared in the DATETIME context. */
+ bool compare_as_dates;
+ /* An item used for issuing warnings while string to DATETIME conversion. */
+ Item *datetime_item;
+ THD *thd;
+
public:
Item_func_min_max(List<Item> &list,int cmp_sign_arg) :Item_func(list),
- cmp_type(INT_RESULT), cmp_sign(cmp_sign_arg) {}
+ cmp_type(INT_RESULT), cmp_sign(cmp_sign_arg), compare_as_dates(FALSE),
+ datetime_item(0) {}
double val_real();
longlong val_int();
String *val_str(String *);
@@ -718,6 +725,7 @@ public:
void fix_length_and_dec();
enum Item_result result_type () const { return cmp_type; }
bool check_partition_func_processor(byte *int_arg) {return FALSE;}
+ uint cmp_datetimes(ulonglong *value);
};
class Item_func_min :public Item_func_min_max
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 7d635ba444e..8f6503810fc 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1830,7 +1830,7 @@ int subselect_single_select_engine::exec()
select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
if (join->init_save_join_tab())
- DBUG_RETURN(1);
+ DBUG_RETURN(1); /* purecov: inspected */
}
if (item->engine_changed)
{
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 93f290b12e7..a26ff4c0887 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1875,6 +1875,8 @@ void make_date(const DATE_TIME_FORMAT *format, const MYSQL_TIME *l_time,
void make_time(const DATE_TIME_FORMAT *format, const MYSQL_TIME *l_time,
String *str);
int my_time_compare(MYSQL_TIME *a, MYSQL_TIME *b);
+ulonglong get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
+ Item *warn_item, bool *is_null);
int test_if_number(char *str,int *res,bool allow_wildcards);
void change_byte(byte *,uint,char,char);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8f4fa75be91..1cd57226b22 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1442,7 +1442,7 @@ JOIN::optimize()
*/
if (select_lex->uncacheable && !is_top_level_join() &&
init_save_join_tab())
- DBUG_RETURN(-1);
+ DBUG_RETURN(-1); /* purecov: inspected */
}
error= 0;
@@ -1519,7 +1519,7 @@ bool
JOIN::init_save_join_tab()
{
if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN))))
- return 1;
+ return 1; /* purecov: inspected */
error= 0; // Ensure that tmp_join.error= 0
restore_tmp();
return 0;
View Lorry Controller Status