Below is the list of changes that have just been committed into a local
4.1 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.2485 06/06/01 00:59:34 evgen@stripped +8 -0
Fixed bug#16377: result of DATE/TIME functions were compared as strings which
can lead to a wrong result.
All date/time functions has the STRING result type thus their results are
compared as strings. The string date representation allows a user to skip
some of leading zeros. This can lead to wrong comparison result if a date/time
function result is compared to such a string constant.
The idea behind this bug fix is to compare results of date/time functions
and data/time constants as ints, because that date/time representation is
more exact. To achieve this the agg_cmp_type() is changed to take in the
account that a date/time field or an date/time item should be compared
as ints.
This bug fix is partially back ported from 5.0.
The agg_cmp_type() function now accepts THD as one of parameters.
In addition, it now checks if a date/time field/function is present in the
list. If so, it tries to coerce all constants to INT to make date/time
comparison return correct result. The field for the constant coercion is
taken from the Item_field or constructed from the Item_func. In latter case
the constructed field will be freed after conversion of all constant items.
Otherwise the result is same as before - aggregated with help of the
item_cmp_type() function.
From the Item_func_between::fix_length_and_dec() function removed the part
which was converting date/time constants to int if possible. Now this is
done by the agg_cmp_type() function.
The new function result_as_longlong() is added to the Item class.
It indicates that the item is a date/time item and result of it can be
compared as int. Such items are date/time fields/functions.
Correct val_int() methods are implemented for classes Item_date_typecast,
Item_func_makedate, Item_time_typecast, Item_datetime_typecast. All these
classes are derived from Item_str_func and Item_str_func::val_int() converts
its string value to int without regard to the date/time type of these items.
Arg_comparator::set_compare_func() and Arg_comparator::set_cmp_func()
functions are changed to substitute result type of an item with the INT_RESULT
if the item is a date/time item and another item is a constant. This is done
to get a correct result of comparisons like date_time_function() = string_constant.
sql/item_cmpfunc.cc
1.209 06/06/01 00:57:39 evgen@stripped +78 -32
Fixed bug#16377: result of DATE/TIME functions were compared as strings which
can lead to a wrong result.
The agg_cmp_type() function now accepts THD as one of parameters.
In addition, it now checks if a date/time field/function is present in the
list. If so, it tries to coerce all constants to INT to make date/time
comparison return correct result. The field for the constant coercion is
taken from the Item_field or constructed from the Item_func. In latter case
the constructed field will be freed after conversion of all constant items.
Otherwise the result is same as before - aggregated with help of the
item_cmp_type() function.
sql/item.h
1.194 06/06/01 00:32:14 evgen@stripped +15 -0
The new function result_as_longlong() is added to the Item class.
It indicates that the item is a date/time item and result of it can be
compared as int. Such items are date/time fields/functions.
sql/item_cmpfunc.h
1.114 06/06/01 00:31:38 evgen@stripped +10 -4
Fixed bug#16377: result of DATE/TIME functions were compared as strings which
can lead to a wrong result.
Arg_comparator::set_compare_func() and Arg_comparator::set_cmp_func()
functions are changed to substitute result type of an item with the INT_RESULT
if the item is a date/time item and another item is a constant.
sql/item_timefunc.cc
1.101 06/06/01 00:27:50 evgen@stripped +60 -0
Fixed bug#16377: result of DATE/TIME functions were compared as strings which
can lead to a wrong result.Correct val_int() methods are implemented for classes Item_date_typecast,
Item_func_makedate, Item_time_typecast, Item_datetime_typecast.
sql/item_timefunc.h
1.57 06/06/01 00:25:35 evgen@stripped +12 -0
Fixed bug#16377: result of DATE/TIME functions were compared as strings which
can lead to a wrong result.
The result_as_longlong() function is set to return TRUE for these classes:
Item_date, Item_date_func, Item_func_curtime, Item_func_sec_to_time,
Item_date_typecast, Item_time_typecast, Item_datetime_typecast,
Item_func_makedate.
mysql-test/r/cast.result
1.26 06/06/01 00:25:08 evgen@stripped +1 -1
Fixed wrong test case result after bug fix#16377.
mysql-test/r/func_time.result
1.39 06/06/01 00:24:49 evgen@stripped +37 -0
Added test case fot bug#16377: result of DATE/TIME functions were compared as strings which
can lead to a wrong result.
mysql-test/t/func_time.test
1.34 06/06/01 00:24:32 evgen@stripped +19 -0
Added test case fot bug#16377: result of DATE/TIME functions were compared as strings which
can lead to a wrong result.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: evgen
# Host: moonbone.local
# Root: /work/16377-bug-4.1-opt-mysql
--- 1.193/sql/item.h 2006-04-28 13:23:28 +04:00
+++ 1.194/sql/item.h 2006-06-01 00:32:14 +04:00
@@ -327,6 +327,13 @@
cleanup();
delete this;
}
+ /*
+ result_as_longlong function returns FALSE by default. The TRUE value
+ should be returned for these items the result of which can be
+ compared as int but their result type isn't INT_RESULT. Such functions
+ are all date/time functions and date/time Item_fields.
+ */
+ virtual const bool result_as_longlong() { return FALSE; }
};
@@ -450,6 +457,10 @@
Item *get_tmp_table_item(THD *thd);
void cleanup();
inline uint32 max_disp_length() { return field->max_length(); }
+ virtual const bool result_as_longlong()
+ {
+ return field->can_be_compared_as_longlong();
+ }
friend class Item_default_value;
friend class Item_insert_value;
friend class st_select_lex_unit;
@@ -973,6 +984,10 @@
}
Item *real_item() { return *ref; }
void print(String *str);
+ virtual const bool result_as_longlong()
+ {
+ return (*ref)->result_as_longlong();
+ }
};
--- 1.208/sql/item_cmpfunc.cc 2006-05-30 00:35:04 +04:00
+++ 1.209/sql/item_cmpfunc.cc 2006-06-01 00:57:39 +04:00
@@ -25,6 +25,8 @@
#include <m_ctype.h>
#include "sql_select.h"
+static bool convert_constant_item(THD *thd, Field *field, Item **item);
+
static Item_result item_store_type(Item_result a,Item_result b)
{
if (a == STRING_RESULT || b == STRING_RESULT)
@@ -64,6 +66,7 @@
SYNOPSIS:
agg_cmp_type()
+ thd thread handle
type [out] the aggregated type
items array of items to aggregate the type from
nitems number of items in the array
@@ -78,24 +81,89 @@
If all items are constants the type will be aggregated from all items.
If there are some non-constant items then only types of non-constant
items will be used for aggregation.
+ If there is a date/time fields/functions in the list and no string
+ fields/functions in the list then:
+ The INT_RESULT type will be used for aggregation instead of orginal
+ result type of any date/time field/function in the list
+ All constant items in the list will be converted to a date/time using
+ found field or result field of found function.
*/
-static void agg_cmp_type(Item_result *type, Item **items, uint nitems)
+
+static void agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems)
{
uint i;
- type[0]= items[0]->result_type();
+ Item::Type res;
+ char *buff;
+ uchar null;
+ Field *field= NULL;
+
+ /* Search for date/time fields/functions */
+ for (i= 0; i < nitems; i++)
+ {
+ if (!items[i]->result_as_longlong())
+ {
+ /* Do not convert anything if a string field/function is present */
+ if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT)
+ {
+ i= nitems;
+ break;
+ }
+ continue;
+ }
+ if ((res= items[i]->real_item()->type()) == Item::FIELD_ITEM)
+ {
+ field= ((Item_field *)items[i]->real_item())->field;
+ break;
+ }
+ else if (res == Item::FUNC_ITEM)
+ {
+ field= items[i]->tmp_table_field_from_field_type(0);
+ buff= alloc_root(thd->mem_root, field->max_length());
+ field->move_field(buff, &null, 0);
+ break;
+ }
+ }
+ if (field)
+ {
+ /* Check the rest of the list for presense of a string field/function. */
+ for (i++ ; i < nitems; i++)
+ {
+ if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT &&
+ !items[i]->result_as_longlong())
+ {
+ field= 0;
+ break;
+ }
+ }
+ }
/* Reset to 0 on first occurence of non-const item. 1 otherwise */
bool is_const= items[0]->const_item();
+ /*
+ If the first item is a date/time function then its result should be
+ compared as int
+ */
+ if (field)
+ {
+ /* Suppose we are comparing dates and some non-constant items are present. */
+ type[0]= INT_RESULT;
+ is_const= 0;
+ }
+ else
+ type[0]= items[0]->result_type();
- for (i= 1 ; i < nitems ; i++)
+ for (i= 0; i < nitems ; i++)
{
if (!items[i]->const_item())
{
- type[0]= is_const ? items[i]->result_type() :
- item_cmp_type(type[0], items[i]->result_type());
+ Item_result result= field && items[i]->result_as_longlong() ?
+ INT_RESULT : items[i]->result_type();
+ type[0]= is_const ? result : item_cmp_type(type[0], result);
is_const= 0;
}
else if (is_const)
type[0]= item_cmp_type(type[0], items[i]->result_type());
+ else if (field && convert_constant_item(thd, field, &items[i]))
+ type[0]= INT_RESULT;
}
}
@@ -929,31 +997,9 @@
*/
if (!args[0] || !args[1] || !args[2])
return;
- agg_cmp_type(&cmp_type, args, 3);
- if (cmp_type == STRING_RESULT &&
- agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV))
- return;
-
- /*
- Make a special case of compare with date/time and longlong fields.
- They are compared as integers, so for const item this time-consuming
- conversion can be done only once, not for every single comparison
- */
- if (args[0]->type() == FIELD_ITEM)
- {
- Field *field=((Item_field*) args[0])->field;
- if (field->can_be_compared_as_longlong())
- {
- /*
- The following can't be recoded with || as convert_constant_item
- changes the argument
- */
- if (convert_constant_item(thd, field,&args[1]))
- cmp_type=INT_RESULT; // Works for all types.
- if (convert_constant_item(thd, field,&args[2]))
- cmp_type=INT_RESULT; // Works for all types.
- }
- }
+ agg_cmp_type(thd, &cmp_type, args, 3);
+ if (cmp_type == STRING_RESULT)
+ agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV);
}
@@ -1477,7 +1523,7 @@
for (nagg= 0; nagg < ncases/2 ; nagg++)
agg[nagg+1]= args[nagg*2];
nagg++;
- agg_cmp_type(&cmp_type, agg, nagg);
+ agg_cmp_type(current_thd, &cmp_type, agg, nagg);
if ((cmp_type == STRING_RESULT) &&
agg_arg_charsets(cmp_collation, agg, nagg, MY_COLL_CMP_CONV))
return;
@@ -1958,7 +2004,7 @@
uint const_itm= 1;
THD *thd= current_thd;
- agg_cmp_type(&cmp_type, args, arg_count);
+ agg_cmp_type(thd, &cmp_type, args, arg_count);
if (cmp_type == STRING_RESULT &&
agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV))
--- 1.113/sql/item_cmpfunc.h 2006-01-14 04:55:01 +03:00
+++ 1.114/sql/item_cmpfunc.h 2006-06-01 00:31:38 +04:00
@@ -43,8 +43,11 @@
int set_compare_func(Item_bool_func2 *owner, Item_result type);
inline int set_compare_func(Item_bool_func2 *owner_arg)
{
- return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(),
- (*b)->result_type()));
+ Item_result ar= (*a)->result_as_longlong() && (*b)->const_item() ?
+ INT_RESULT : (*a)->result_type();
+ Item_result br= (*b)->result_as_longlong() && (*a)->const_item() ?
+ INT_RESULT : (*b)->result_type();
+ return set_compare_func(owner_arg, item_cmp_type(ar, br));
}
inline int set_cmp_func(Item_bool_func2 *owner_arg,
Item **a1, Item **a2,
@@ -57,8 +60,11 @@
inline int set_cmp_func(Item_bool_func2 *owner_arg,
Item **a1, Item **a2)
{
- return set_cmp_func(owner_arg, a1, a2, item_cmp_type((*a1)->result_type(),
- (*a2)->result_type()));
+ Item_result ar= (*a1)->result_as_longlong() && (*a2)->const_item() ?
+ INT_RESULT : (*a1)->result_type();
+ Item_result br= (*a2)->result_as_longlong() && (*a1)->const_item() ?
+ INT_RESULT : (*a2)->result_type();
+ return set_cmp_func(owner_arg, a1, a2, item_cmp_type(ar, br));
}
inline int compare() { return (this->*func)(); }
--- 1.100/sql/item_timefunc.cc 2006-05-10 12:27:43 +04:00
+++ 1.101/sql/item_timefunc.cc 2006-06-01 00:27:50 +04:00
@@ -2306,6 +2306,20 @@
}
+longlong Item_datetime_typecast::val_int()
+{
+ DBUG_ASSERT(fixed == 1);
+ TIME ltime;
+ if (get_arg0_date(<ime,1))
+ {
+ null_value= 1;
+ return 0;
+ }
+
+ return TIME_to_ulonglong_datetime(<ime);
+}
+
+
bool Item_time_typecast::get_time(TIME *ltime)
{
bool res= get_arg0_time(ltime);
@@ -2320,6 +2334,17 @@
}
+longlong Item_time_typecast::val_int()
+{
+ TIME ltime;
+ if (get_time(<ime))
+ {
+ null_value= 1;
+ return 0;
+ }
+ return ltime.hour * 10000L + ltime.minute * 100 + ltime.second;
+}
+
String *Item_time_typecast::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
@@ -2359,6 +2384,14 @@
return 0;
}
+longlong Item_date_typecast::val_int()
+{
+ DBUG_ASSERT(fixed == 1);
+ TIME ltime;
+ if (args[0]->get_date(<ime, TIME_FUZZY_DATE))
+ return 0;
+ return (longlong) (ltime.year * 10000L + ltime.month * 100 + ltime.day);
+}
/*
MAKEDATE(a,b) is a date function that creates a date value
@@ -2391,6 +2424,33 @@
err:
null_value=1;
+ return 0;
+}
+
+
+longlong Item_func_makedate::val_int()
+{
+ DBUG_ASSERT(fixed == 1);
+ TIME l_time;
+ long daynr= (long) args[1]->val_int();
+ long yearnr= (long) args[0]->val_int();
+ long days;
+
+ if (args[0]->null_value || args[1]->null_value ||
+ yearnr < 0 || daynr <= 0)
+ goto err;
+
+ days= calc_daynr(yearnr,1,1) + daynr - 1;
+ /* Day number from year 0 to 9999-12-31 */
+ if (days >= 0 && days < MAX_DAY_NUMBER)
+ {
+ null_value=0;
+ get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day);
+ return (longlong) (l_time.year * 10000L + l_time.month * 100 + l_time.day);
+ }
+
+err:
+ null_value= 1;
return 0;
}
--- 1.56/sql/item_timefunc.h 2006-04-13 09:55:41 +04:00
+++ 1.57/sql/item_timefunc.h 2006-06-01 00:25:35 +04:00
@@ -339,6 +339,7 @@
{
return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
}
+ virtual const bool result_as_longlong() { return TRUE; }
};
@@ -354,6 +355,7 @@
{
return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
}
+ virtual const bool result_as_longlong() { return TRUE; }
};
@@ -383,6 +385,7 @@
TIME representation using UTC-SYSTEM or per-thread time zone.
*/
virtual void store_now_in_TIME(TIME *now_time)=0;
+ virtual const bool result_as_longlong() { return TRUE; }
};
@@ -588,6 +591,7 @@
{
return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
}
+ virtual const bool result_as_longlong() { return TRUE; }
};
/*
@@ -715,6 +719,8 @@
max_length= 10;
maybe_null= 1;
}
+ virtual const bool result_as_longlong() { return TRUE; }
+ longlong val_int();
};
@@ -731,6 +737,8 @@
{
return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
}
+ virtual const bool result_as_longlong() { return TRUE; }
+ longlong val_int();
};
@@ -746,6 +754,8 @@
{
return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
}
+ virtual const bool result_as_longlong() { return TRUE; }
+ longlong val_int();
};
class Item_func_makedate :public Item_str_func
@@ -764,6 +774,8 @@
{
return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
}
+ virtual const bool result_as_longlong() { return TRUE; }
+ longlong val_int();
};
--- 1.38/mysql-test/r/func_time.result 2006-04-25 13:34:13 +04:00
+++ 1.39/mysql-test/r/func_time.result 2006-06-01 00:24:49 +04:00
@@ -630,3 +630,40 @@
monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));
monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m'))
NULL NULL January NULL
+create table t1(f1 date, f2 time, f3 datetime);
+insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
+insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
+select f1 from t1 where f1 between "2006-1-1" and 20060101;
+f1
+2006-01-01
+select f1 from t1 where f1 between "2006-1-1" and "2006.1.1";
+f1
+2006-01-01
+select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1";
+f1
+2006-01-01
+select f2 from t1 where f2 between "12:1:2" and "12:2:2";
+f2
+12:01:02
+select f2 from t1 where time(f2) between "12:1:2" and "12:2:2";
+f2
+12:01:02
+select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
+f3
+2006-01-01 12:01:01
+select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
+f3
+2006-01-01 12:01:01
+select f1 from t1 where "2006-1-1" between f1 and f3;
+f1
+2006-01-01
+select f1 from t1 where "2006-1-1" between date(f1) and date(f3);
+f1
+2006-01-01
+select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
+f1
+2006-01-01
+select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
+f1
+2006-01-02
+drop table t1;
--- 1.33/mysql-test/t/func_time.test 2006-04-25 13:34:13 +04:00
+++ 1.34/mysql-test/t/func_time.test 2006-06-01 00:24:32 +04:00
@@ -322,4 +322,23 @@
select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),
monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));
+#
+# Bug#16377 result of DATE/TIME functions were compared as strings which
+# can lead to a wrong result.
+#
+create table t1(f1 date, f2 time, f3 datetime);
+insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
+insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
+select f1 from t1 where f1 between "2006-1-1" and 20060101;
+select f1 from t1 where f1 between "2006-1-1" and "2006.1.1";
+select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1";
+select f2 from t1 where f2 between "12:1:2" and "12:2:2";
+select f2 from t1 where time(f2) between "12:1:2" and "12:2:2";
+select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
+select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
+select f1 from t1 where "2006-1-1" between f1 and f3;
+select f1 from t1 where "2006-1-1" between date(f1) and date(f3);
+select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
+select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
+drop table t1;
# End of 4.1 tests
--- 1.25/mysql-test/r/cast.result 2005-12-06 15:51:12 +03:00
+++ 1.26/mysql-test/r/cast.result 2006-06-01 00:25:08 +04:00
@@ -192,7 +192,7 @@
1
select cast("1:2:3" as TIME) = "1:02:03";
cast("1:2:3" as TIME) = "1:02:03"
-0
+1
select cast(NULL as DATE);
cast(NULL as DATE)
NULL
| Thread |
|---|
| • bk commit into 4.1 tree (evgen:1.2485) BUG#16377 | eugene | 31 May |
