Below is the list of changes that have just been committed into a local
5.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@stripped, 2006-09-18 23:34:39+04:00, evgen@stripped +9 -0
Fixed bug #18360: Type aggregation for IN and CASE may lead to a wrong
result
The IN function aggregates result types of all expressions. It uses that
type in comparison of left expression and expressions in right part.
This approach works in most cases. But let's consider the case when the
right part contains both strings and integers. In that case this approach may
cause wrong results because all strings which do not start with a digit are
evaluated as 0.
CASE uses the same approach when a CASE expression is given thus it's also
affected.
The idea behind this fix is to make IN function to compare expressions with
different result types differently. For example a string in the left
part will be compared as string with strings specified in right part and
will be converted to real for comparison to int or real items in the right
part.
A new function called collect_cmp_types() is added. It collects different
result types for comparison of first item in the provided list with each
other item in the list.
The Item_func_in class now can refer up to 5 cmp_item objects: 1 for each
result type for comparison purposes. cmp_item objects are allocated according
to found result types. The comparison of the left expression with any
right part expression is now based only on result types of these expressions.
The Item_func_case class is modified in the similar way when a CASE
expression is specified. Now it can allocate up to 5 cmp_item objects
to compare CASE expression with WHEN expressions of different types.
The comparison of the CASE expression with any WHEN expression now based only
on result types of these expressions.
mysql-test/r/func_in.result@stripped, 2006-09-18 23:30:56+04:00, evgen@stripped +12 -0
Added test case for bug#18360: Type aggregation for IN and CASE may lead to a wrong result
mysql-test/r/func_time.result@stripped, 2006-09-18 23:30:54+04:00, evgen@stripped +12 -13
A test case corrected after fix for bug#16377 removal
mysql-test/r/view.result@stripped, 2006-09-18 23:30:51+04:00, evgen@stripped +2 -2
A test case corrected after fix for bug#16377 removal
mysql-test/t/func_in.test@stripped, 2006-09-18 23:30:43+04:00, evgen@stripped +24 -0
Added test case for bug#18360: Type aggregation for IN and CASE may lead to a wrong
result
mysql-test/t/func_time.test@stripped, 2006-09-18 23:30:41+04:00, evgen@stripped +11 -11
A test case corrected after fix for bug#16377 removal
mysql-test/t/view.test@stripped, 2006-09-18 23:30:34+04:00, evgen@stripped +3 -3
A test case corrected after fix for bug#16377 removal
sql/item_cmpfunc.cc@stripped, 2006-09-18 23:34:02+04:00, evgen@stripped +246 -227
Fixed bug #18360: Type aggregation for IN and CASE may lead to a wrong result
A new function called collect_cmp_types() is added. It collects different
result types for comparison of first item in the provided list with each
other item in the list.
The Item_func_in class now can refer up to 5 cmp_item objects: 1 for each
result type for comparison purposes. cmp_item objects are allocated according
to found result types. The comparison of the left expression with any
right part expression is now based only on result types of these expressions.
The Item_func_case class is modified in the similar way when a CASE
expression is specified. Now it can allocate up to 5 cmp_item objects
to compare CASE expression with WHEN expressions of different types.
The comparison of the CASE expression with any WHEN expression now based only
on result types of these expressions.
sql/item_cmpfunc.h@stripped, 2006-09-18 23:33:57+04:00, evgen@stripped +117 -48
Fixed bug#18360: Type aggregation for IN and CASE may lead to a wrong result
The Item_func_in class now can refer up to 5 cmp_item objects.
The Item_func_case class is modified in the similar way.
sql/opt_range.cc@stripped, 2006-09-18 23:33:17+04:00, evgen@stripped +1 -1
Fixed bug #18360: Type aggregation for IN and CASE may lead to a wrong
resultSmall code changes due to changes in Item_func_in class.
# 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/18360-bug-5.1-opt-mysql
--- 1.210/sql/item_cmpfunc.cc 2006-09-18 23:34:44 +04:00
+++ 1.211/sql/item_cmpfunc.cc 2006-09-18 23:34:44 +04:00
@@ -66,134 +66,52 @@
/*
Aggregates result types from the array of items.
- SYNOPSIS:
+ 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
+ type [out] aggregated type
+ items array of items to aggregate the type from
+ nitems number of items in the array
DESCRIPTION
This function aggregates result types from the array of items. Found type
supposed to be used later for comparison of values of these items.
Aggregation itself is performed by the item_cmp_type() function.
-
- NOTES
- Aggregation rules:
- If there are 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 original
- 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.
-
- Implementation notes:
- The code is equivalent to:
- 1. Check the list for presence of a STRING field/function.
- Collect the is_const flag.
- 2. Get a Field* object to use for type coercion
- 3. Perform type conversion.
- 1 and 2 are implemented in 2 loops. The first searches for a DATE/TIME
- field/function and checks presence of a STRING field/function.
- The second loop works only if a DATE/TIME field/function is found.
- It checks presence of a STRING field/function in the rest of the list.
-
- TODO
- 1) The current implementation can produce false comparison results for
- expressions like:
- date_time_field BETWEEN string_field_with_dates AND string_constant
- if the string_constant will omit some of leading zeroes.
- In order to fully implement correct comparison of DATE/TIME the new
- DATETIME_RESULT result type should be introduced and agg_cmp_type()
- should return the DATE/TIME field used for the conversion. Later
- this field can be used by comparison functions like Item_func_between to
- convert string values to ints on the fly and thus return correct results.
- This modification will affect functions BETWEEN, IN and CASE.
-
- 2) If in the list a DATE field/function and a DATETIME field/function
- are present in the list then the first found field/function will be
- used for conversion. This may lead to wrong results and probably should
- be fixed.
*/
-static void agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems)
+static void agg_cmp_type(Item_result *type, Item **items, uint nitems)
{
uint i;
- Item::Type res= (Item::Type)0;
- /* Used only for date/time fields, max_length = 19 */
- char buff[20];
- uchar null_byte;
- Field *field= NULL;
+ type[0]= items[0]->result_type();
+ for (i= 1; i < nitems ; i++)
+ type[0]= item_cmp_type(type[0], items[i]->result_type());
+}
- /* 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 &&
- items[i]->result_type() != INT_RESULT)
- {
- 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, 0);
- if (field)
- field->move_field(buff, &null_byte, 0);
- break;
- }
- }
- if (field)
- {
- /* Check the rest of the list for presence 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())
- {
- if (res == Item::FUNC_ITEM)
- delete field;
- field= 0;
- break;
- }
- }
- }
- /*
- If the first item is a date/time function then its result should be
- compared as int
- */
- if (field)
- /* Suppose we are comparing dates */
- type[0]= INT_RESULT;
- else
- type[0]= items[0]->result_type();
- for (i= 0; i < nitems ; i++)
- {
- Item_result result= items[i]->result_type();
- /*
- Use INT_RESULT as result type for DATE/TIME fields/functions and
- for constants successfully converted to DATE/TIME
- */
- if (field &&
- ((!items[i]->const_item() && items[i]->result_as_longlong()) ||
- (items[i]->const_item() && convert_constant_item(thd, field,
- &items[i]))))
- result= INT_RESULT;
- type[0]= item_cmp_type(type[0], result);
- }
+/*
+ Collects different types for comparison of first item with each other items
+
+ SYNOPSIS
+ collect_cmp_types()
+ items array of items to collect types from
+ nitems number of items in the array
+ found_types [out] bitmap of collected types
- if (res == Item::FUNC_ITEM && field)
- delete field;
+ DESCRIPTION
+ This function collects different result types for comparison of the first
+ item in the list with each of the remaining items in the 'items' array.
+*/
+
+static uint collect_cmp_types(Item **items, uint nitems)
+{
+ uint i;
+ uint found_types;
+ Item_result left_result= items[0]->result_type();
+ DBUG_ASSERT(nitems > 1);
+ found_types= 0;
+ for (i= 1; i < nitems ; i++)
+ found_types|= 1<< (uint)item_cmp_type(left_result,
+ items[i]->result_type());
+ return found_types;
}
@@ -431,8 +349,8 @@
{
if (convert_constant_item(thd, field,&args[1]))
{
- cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
- INT_RESULT); // Works for all types.
+ // Works for all types.
+ cmp.set_cmp_func(this, tmp_arg, tmp_arg+1, INT_RESULT);
return;
}
}
@@ -445,8 +363,8 @@
{
if (convert_constant_item(thd, field,&args[0]))
{
- cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
- INT_RESULT); // Works for all types.
+ // Works for all types.
+ cmp.set_cmp_func(this, tmp_arg, tmp_arg+1, INT_RESULT);
return;
}
}
@@ -1214,19 +1132,41 @@
void Item_func_between::fix_length_and_dec()
{
- max_length= 1;
- THD *thd= current_thd;
-
+ max_length= 1;
+ THD *thd= current_thd;
/*
As some compare functions are generated after sql_yacc,
we have to check for out of memory conditions here
*/
if (!args[0] || !args[1] || !args[2])
return;
- agg_cmp_type(thd, &cmp_type, args, 3);
+ agg_cmp_type(&cmp_type, args, 3);
- if (cmp_type == STRING_RESULT)
- agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV);
+ 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 &&
+ thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
+ thd->lex->sql_command != SQLCOM_SHOW_CREATE)
+ {
+ 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.
+ }
+ }
}
@@ -1680,94 +1620,65 @@
return (null_value= (!cmp.compare() ? 1 : args[0]->null_value));
}
+
/*
- CASE expression
Return the matching ITEM or NULL if all compares (including else) failed
+
+ SYNOPSIS
+ find_item()
+ str - buffer string
+
+ DESCRIPTION
+ Finds and returns matching items for CASE or ELSE item if all compares
+ are failed or NULL if ELSE item isn't defined.
+
+ IMPLEMENTATION
+ In order to do correct comparisons of the CASE expression (the expression
+ between CASE and the first WHEN) with each WHEN expression several
+ comparators are used. One for each result type. CASE expression can be
+ evaluated up to # of different result types are used. To check whether
+ the CASE expression already was evaluated for a particular result type
+ a bit mapped variable value_added_map is used. Result types are mapped
+ to it according to their int values i.e. STRING_RESULT is mapped to bit
+ 0, REAL_RESULT to bit 1, so on.
+
+ RETURN
+ NULL - Nothing found and there is no ELSE expression defined
+ item - Found item or ELSE item if it is defined and all comparisons are
+ failed
*/
Item *Item_func_case::find_item(String *str)
{
- String *first_expr_str, *tmp;
- my_decimal *first_expr_dec, first_expr_dec_val;
- longlong first_expr_int;
- double first_expr_real;
- char buff[MAX_FIELD_WIDTH];
- String buff_str(buff,sizeof(buff),default_charset());
-
- /* These will be initialized later */
- LINT_INIT(first_expr_str);
- LINT_INIT(first_expr_int);
- LINT_INIT(first_expr_real);
- LINT_INIT(first_expr_dec);
+ uint value_added_map= 0;
- if (first_expr_num != -1)
+ if (first_expr_num == -1)
{
- switch (cmp_type)
- {
- case STRING_RESULT:
- // We can't use 'str' here as this may be overwritten
- if (!(first_expr_str= args[first_expr_num]->val_str(&buff_str)))
- return else_expr_num != -1 ? args[else_expr_num] : 0; // Impossible
- break;
- case INT_RESULT:
- first_expr_int= args[first_expr_num]->val_int();
- if (args[first_expr_num]->null_value)
- return else_expr_num != -1 ? args[else_expr_num] : 0;
- break;
- case REAL_RESULT:
- first_expr_real= args[first_expr_num]->val_real();
- if (args[first_expr_num]->null_value)
- return else_expr_num != -1 ? args[else_expr_num] : 0;
- break;
- case DECIMAL_RESULT:
- first_expr_dec= args[first_expr_num]->val_decimal(&first_expr_dec_val);
- if (args[first_expr_num]->null_value)
- return else_expr_num != -1 ? args[else_expr_num] : 0;
- break;
- case ROW_RESULT:
- default:
- // This case should never be chosen
- DBUG_ASSERT(0);
- break;
- }
- }
-
- // Compare every WHEN argument with it and return the first match
- for (uint i=0 ; i < ncases ; i+=2)
- {
- if (first_expr_num == -1)
+ for (uint i=0 ; i < ncases ; i+=2)
{
// No expression between CASE and the first WHEN
if (args[i]->val_bool())
return args[i+1];
continue;
}
- switch (cmp_type) {
- case STRING_RESULT:
- if ((tmp=args[i]->val_str(str))) // If not null
- if (sortcmp(tmp,first_expr_str,cmp_collation.collation)==0)
- return args[i+1];
- break;
- case INT_RESULT:
- if (args[i]->val_int()==first_expr_int && !args[i]->null_value)
- return args[i+1];
- break;
- case REAL_RESULT:
- if (args[i]->val_real() == first_expr_real && !args[i]->null_value)
- return args[i+1];
- break;
- case DECIMAL_RESULT:
+ }
+ else
+ {
+ // Compare every WHEN argument with it and return the first match
+ for (uint i=0 ; i < ncases ; i+=2)
{
- my_decimal value;
- if (my_decimal_cmp(args[i]->val_decimal(&value), first_expr_dec) == 0)
- return args[i+1];
- break;
- }
- case ROW_RESULT:
- default:
- // This case should never be chosen
- DBUG_ASSERT(0);
- break;
+ cmp_type= item_cmp_type(left_result_type, args[i]->result_type());
+ DBUG_ASSERT(cmp_type != ROW_RESULT);
+ DBUG_ASSERT(cmp_items[(uint)cmp_type]);
+ if (!(value_added_map & (1<<(uint)cmp_type)))
+ {
+ cmp_items[(uint)cmp_type]->store_value(args[first_expr_num]);
+ if ((null_value=args[first_expr_num]->null_value))
+ return else_expr_num != -1 ? args[else_expr_num] : 0;
+ value_added_map|= 1<<(uint)cmp_type;
+ }
+ if (!cmp_items[(uint)cmp_type]->cmp(args[i]) && !args[i]->null_value)
+ return args[i + 1];
}
}
// No, WHEN clauses all missed, return ELSE expression
@@ -1874,7 +1785,7 @@
Item **agg;
uint nagg;
THD *thd= current_thd;
-
+ uint found_types= 0;
if (!(agg= (Item**) sql_alloc(sizeof(Item*)*(ncases+1))))
return;
@@ -1901,14 +1812,46 @@
*/
if (first_expr_num != -1)
{
+ uint i;
agg[0]= args[first_expr_num];
+ left_result_type= agg[0]->result_type();
+
for (nagg= 0; nagg < ncases/2 ; nagg++)
agg[nagg+1]= args[nagg*2];
nagg++;
- agg_cmp_type(thd, &cmp_type, agg, nagg);
- if ((cmp_type == STRING_RESULT) &&
- agg_arg_charsets(cmp_collation, agg, nagg, MY_COLL_CMP_CONV))
- return;
+ found_types= collect_cmp_types(agg, nagg);
+
+ for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
+ {
+ if (found_types & (1 << i) && !cmp_items[i])
+ {
+ switch ((Item_result)i)
+ {
+ case STRING_RESULT:
+ if (agg_arg_charsets(cmp_collation, agg, nagg, MY_COLL_CMP_CONV) ||
+ !(cmp_items[i]=
+ new cmp_item_sort_string(cmp_collation.collation)))
+ return;
+ break;
+ case INT_RESULT:
+ if (!(cmp_items[i]= new cmp_item_int()))
+ return;
+ break;
+ case REAL_RESULT:
+ if (!(cmp_items[i]= new cmp_item_real()))
+ return;
+ break;
+ case DECIMAL_RESULT:
+ if (!(cmp_items[i]= new cmp_item_decimal()))
+ return;
+ break;
+ case ROW_RESULT:
+ default:
+ DBUG_ASSERT(0);
+ }
+
+ }
+ }
}
if (else_expr_num == -1 || args[else_expr_num]->maybe_null)
@@ -2495,16 +2438,14 @@
void Item_func_in::fix_length_and_dec()
{
Item **arg, **arg_end;
- uint const_itm= 1;
+ bool const_itm= 1;
THD *thd= current_thd;
-
- 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))
- return;
+ uint found_types= 0;
+ uint type_cnt= 0, i;
+ left_result_type= args[0]->result_type();
+ found_types= collect_cmp_types(args, arg_count);
- for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
+ for (arg= args + 1, arg_end= args + arg_count; arg != arg_end ; arg++)
{
if (!arg[0]->const_item())
{
@@ -2512,26 +2453,39 @@
break;
}
}
-
+ for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
+ {
+ if (found_types & 1 << i)
+ (type_cnt)++;
+ }
/*
- Row item with NULLs inside can return NULL or FALSE =>
+ Row item with NULLs inside can return NULL or FALSE =>
they can't be processed as static
*/
- if (const_itm && !nulls_in_row())
+ if (type_cnt == 1 && const_itm && !nulls_in_row())
{
+ uint tmp_type;
+ Item_result cmp_type;
+ /* Only one cmp type was found. Extract it here */
+ for (tmp_type= 0; found_types - 1; found_types>>= 1)
+ tmp_type++;
+ cmp_type= (Item_result)tmp_type;
+
switch (cmp_type) {
case STRING_RESULT:
- array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in,
+ if (agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV))
+ return;
+ array=new in_string(arg_count - 1,(qsort2_cmp) srtcmp_in,
cmp_collation.collation);
break;
case INT_RESULT:
- array= new in_longlong(arg_count-1);
+ array= new in_longlong(arg_count - 1);
break;
case REAL_RESULT:
- array= new in_double(arg_count-1);
+ array= new in_double(arg_count - 1);
break;
case ROW_RESULT:
- array= new in_row(arg_count-1, args[0]);
+ array= new in_row(arg_count - 1, args[0]);
break;
case DECIMAL_RESULT:
array= new in_decimal(arg_count - 1);
@@ -2551,15 +2505,44 @@
else
have_null= 1;
}
- if ((array->used_count=j))
+ if ((array->used_count= j))
array->sort();
}
}
else
{
- in_item= cmp_item::get_comparator(cmp_type, cmp_collation.collation);
- if (cmp_type == STRING_RESULT)
- in_item->cmp_charset= cmp_collation.collation;
+ for (i= 0; i <= (uint) DECIMAL_RESULT; i++)
+ {
+ if (found_types & (1 << i) && !cmp_items[i])
+ {
+ switch ((Item_result)i)
+ {
+ case STRING_RESULT:
+ if (agg_arg_charsets(cmp_collation, args, arg_count,
+ MY_COLL_CMP_CONV) ||
+ !(cmp_items[i]=
+ new cmp_item_sort_string(cmp_collation.collation)))
+ return;
+ break;
+ case INT_RESULT:
+ if (!(cmp_items[i]= new cmp_item_int()))
+ return;
+ break;
+ case REAL_RESULT:
+ if (!(cmp_items[i]= new cmp_item_real()))
+ return;
+ break;
+ case DECIMAL_RESULT:
+ if (!(cmp_items[i]= new cmp_item_decimal()))
+ return;
+ break;
+ case ROW_RESULT:
+ if (!(cmp_items[i]= new cmp_item_row()))
+ return;
+ break;
+ }
+ }
+ }
}
maybe_null= args[0]->maybe_null;
max_length= 1;
@@ -2578,25 +2561,61 @@
}
+/*
+ Evaluates the function and return it's value.
+
+ SYNOPSIS
+ fix_fields()
+
+ DESCRIPTION
+ Evaluates the function and returns it's value.
+
+ IMPLEMENTATION
+ If the array object is defined then the value of the function is
+ calculated by it.
+ Otherwise several cmp_item objects are used for correct comparison of
+ left expression and an expression from the values list. One cmp_item
+ object for each used comparison type. Left expression can be evaluated
+ up to # of different used comparison types. To check whether the left
+ expression already was evaluated for a particular result type a bit
+ mapped variable value_added_map is used. Result types are mapped to
+ it according to their int values i.e. STRING_RESULT is mapped to bit
+ 0, REAL_RESULT to bit 1, so on.
+
+ RETURN
+ Value of the function
+*/
+
longlong Item_func_in::val_int()
{
+ cmp_item *in_item;
DBUG_ASSERT(fixed == 1);
+ uint value_added_map= 0;
if (array)
{
int tmp=array->find(args[0]);
null_value=args[0]->null_value || (!tmp && have_null);
return (longlong) (!null_value && tmp != negated);
}
- in_item->store_value(args[0]);
- if ((null_value=args[0]->null_value))
- return 0;
- have_null= 0;
- for (uint i=1 ; i < arg_count ; i++)
+
+ for (uint i= 1 ; i < arg_count ; i++)
{
+ Item_result cmp_type= item_cmp_type(left_result_type, args[i]->result_type());
+ in_item= cmp_items[(uint)cmp_type];
+ DBUG_ASSERT(in_item);
+ if (!(value_added_map & (1 << (uint)cmp_type)))
+ {
+ in_item->store_value(args[0]);
+ if ((null_value=args[0]->null_value))
+ return 0;
+ have_null= 0;
+ value_added_map|= 1 << (uint)cmp_type;
+ }
if (!in_item->cmp(args[i]) && !args[i]->null_value)
return (longlong) (!negated);
have_null|= args[i]->null_value;
}
+
null_value= have_null;
return (longlong) (!null_value && negated);
}
--- 1.128/sql/item_cmpfunc.h 2006-09-18 23:34:44 +04:00
+++ 1.129/sql/item_cmpfunc.h 2006-09-18 23:34:44 +04:00
@@ -586,49 +586,6 @@
};
-class Item_func_case :public Item_func
-{
- int first_expr_num, else_expr_num;
- enum Item_result cached_result_type;
- String tmp_value;
- uint ncases;
- Item_result cmp_type;
- DTCollation cmp_collation;
-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),
- cached_result_type(INT_RESULT)
- {
- ncases= list.elements;
- if (first_expr_arg)
- {
- first_expr_num= list.elements;
- list.push_back(first_expr_arg);
- }
- if (else_expr_arg)
- {
- else_expr_num= list.elements;
- list.push_back(else_expr_arg);
- }
- set_arguments(list);
- }
- double val_real();
- longlong val_int();
- String *val_str(String *);
- my_decimal *val_decimal(my_decimal *);
- bool fix_fields(THD *thd, Item **ref);
- void fix_length_and_dec();
- uint decimal_precision() const;
- table_map not_null_tables() const { return 0; }
- enum Item_result result_type () const { return cached_result_type; }
- const char *func_name() const { return "case"; }
- void print(String *str);
- Item *find_item(String *str);
- CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
- bool check_partition_func_processor(byte *bool_arg) { return 0;}
-};
-
-
/* Functions to handle the optimized IN */
@@ -683,6 +640,7 @@
{
return test(compare(collation, base + pos1*size, base + pos2*size));
}
+ virtual Item_result result_type()= 0;
};
class in_string :public in_vector
@@ -704,6 +662,7 @@
Item_string *to= (Item_string*)item;
to->str_value= *str;
}
+ Item_result result_type() { return STRING_RESULT; }
};
class in_longlong :public in_vector
@@ -726,6 +685,7 @@
{
((Item_int*)item)->value= ((longlong*)base)[pos];
}
+ Item_result result_type() { return INT_RESULT; }
};
class in_double :public in_vector
@@ -743,6 +703,7 @@
{
((Item_float*)item)->value= ((double*) base)[pos];
}
+ Item_result result_type() { return REAL_RESULT; }
};
@@ -763,6 +724,8 @@
Item_decimal *item_dec= (Item_decimal*)item;
item_dec->set_decimal_value(dec);
}
+ Item_result result_type() { return DECIMAL_RESULT; }
+
};
@@ -793,7 +756,9 @@
protected:
String *value_res;
public:
+ cmp_item_string () {}
cmp_item_string (CHARSET_INFO *cs) { cmp_charset= cs; }
+ void set_charset(CHARSET_INFO *cs) { cmp_charset= cs; }
friend class cmp_item_sort_string;
friend class cmp_item_sort_string_in_static;
};
@@ -804,6 +769,8 @@
char value_buff[STRING_BUFFER_USUAL_SIZE];
String value;
public:
+ cmp_item_sort_string():
+ cmp_item_string() {}
cmp_item_sort_string(CHARSET_INFO *cs):
cmp_item_string(cs),
value(value_buff, sizeof(value_buff), cs) {}
@@ -825,6 +792,11 @@
return sortcmp(value_res, cmp->value_res, cmp_charset);
}
cmp_item *make_same();
+ void set_charset(CHARSET_INFO *cs)
+ {
+ cmp_charset= cs;
+ value.set_quick(value_buff, sizeof(value_buff), cs);
+ }
};
class cmp_item_int :public cmp_item
@@ -905,6 +877,7 @@
~in_row();
void set(uint pos,Item *item);
byte *get_value(Item *item);
+ Item_result result_type() { return ROW_RESULT; }
};
/*
@@ -940,18 +913,110 @@
}
};
+
+/*
+ The class Item_func_case is the CASE ... WHEN ... THEN ... END function
+ implementation.
+
+ When there is no expression between CASE and the first WHEN
+ (the CASE expression) then this function simple checks all WHEN expressions
+ one after another. When some WHEN expression evaluated to TRUE then the
+ value of the corresponding THEN expression is returned.
+
+ When the CASE expression is specified then it is compared to each WHEN
+ expression individually. When an equal WHEN expression is found
+ corresponding THEN expression is returned.
+ In order to do correct comparisons several comparators are used. One for
+ each result type. Different result types that are used in particular
+ CASE ... END expression are collected in the fix_length_and_dec() member
+ function and only comparators for there result types are used.
+*/
+
+class Item_func_case :public Item_func
+{
+ int first_expr_num, else_expr_num;
+ enum Item_result cached_result_type, left_result_type;
+ String tmp_value;
+ uint ncases;
+ Item_result cmp_type;
+ DTCollation cmp_collation;
+ cmp_item *cmp_items[5]; /* For all result types */
+ cmp_item *case_item;
+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),
+ cached_result_type(INT_RESULT), left_result_type(INT_RESULT), case_item(0)
+ {
+ ncases= list.elements;
+ if (first_expr_arg)
+ {
+ first_expr_num= list.elements;
+ list.push_back(first_expr_arg);
+ }
+ if (else_expr_arg)
+ {
+ else_expr_num= list.elements;
+ list.push_back(else_expr_arg);
+ }
+ set_arguments(list);
+ bzero(&cmp_items, sizeof(cmp_items));
+ }
+ double val_real();
+ longlong val_int();
+ String *val_str(String *);
+ my_decimal *val_decimal(my_decimal *);
+ bool fix_fields(THD *thd, Item **ref);
+ void fix_length_and_dec();
+ uint decimal_precision() const;
+ table_map not_null_tables() const { return 0; }
+ enum Item_result result_type () const { return cached_result_type; }
+ const char *func_name() const { return "case"; }
+ void print(String *str);
+ Item *find_item(String *str);
+ CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
+ bool check_partition_func_processor(byte *bool_arg) { return 0;}
+ void cleanup()
+ {
+ uint i;
+ DBUG_ENTER("Item_func_case::cleanup");
+ Item_func::cleanup();
+ for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
+ {
+ delete cmp_items[i];
+ cmp_items[i]= 0;
+ }
+ DBUG_VOID_RETURN;
+ }
+};
+
+/*
+ The Item_func_in class implements the in_expr IN(values_list) function.
+
+ The current implementation distinguishes 2 cases:
+ 1) all items in the value_list are constants and have the same
+ result type. This case is handled by in_vector class.
+ 2) items in the value_list have different result types or there is some
+ non-constant items.
+ In this case Item_func_in employs several cmp_item objects to performs
+ comparisons of in_expr and an item from the values_list. One cmp_item
+ object for each result type. Different result types are collected in the
+ fix_length_and_dec() member function by means of collect_cmp_types()
+ function.
+*/
class Item_func_in :public Item_func_opt_neg
{
public:
- Item_result cmp_type;
+// Item_result cmp_type;
in_vector *array;
- cmp_item *in_item;
bool have_null;
+ Item_result left_result_type;
+ cmp_item *cmp_items[5]; /* One cmp_item for each result type */
DTCollation cmp_collation;
Item_func_in(List<Item> &list)
- :Item_func_opt_neg(list), array(0), in_item(0), have_null(0)
+ :Item_func_opt_neg(list), array(0), have_null(0)
{
+ bzero(&cmp_items, sizeof(cmp_items));
allowed_arg_cols= 0; // Fetch this value from first argument
}
longlong val_int();
@@ -960,12 +1025,16 @@
uint decimal_precision() const { return 1; }
void cleanup()
{
+ uint i;
DBUG_ENTER("Item_func_in::cleanup");
Item_int_func::cleanup();
delete array;
- delete in_item;
array= 0;
- in_item= 0;
+ for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
+ {
+ delete cmp_items[i];
+ cmp_items[i]= 0;
+ }
DBUG_VOID_RETURN;
}
optimize_type select_optimize() const
--- 1.223/sql/opt_range.cc 2006-09-18 23:34:44 +04:00
+++ 1.224/sql/opt_range.cc 2006-09-18 23:34:44 +04:00
@@ -4752,7 +4752,7 @@
if (inv)
{
- if (func->array && func->cmp_type != ROW_RESULT)
+ if (func->array && func->array->result_type() != ROW_RESULT)
{
/*
We get here for conditions in form "t.key NOT IN (c1, c2, ...)"
--- 1.166/mysql-test/r/view.result 2006-09-18 23:34:44 +04:00
+++ 1.167/mysql-test/r/view.result 2006-09-18 23:34:44 +04:00
@@ -2586,13 +2586,13 @@
(4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'),
(7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06');
CREATE VIEW v1 AS SELECT * FROM t1;
-SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
id td
2 2005-01-02
3 2005-01-02
4 2005-01-03
5 2005-01-04
-SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
id td
2 2005-01-02
3 2005-01-02
--- 1.151/mysql-test/t/view.test 2006-09-18 23:34:44 +04:00
+++ 1.152/mysql-test/t/view.test 2006-09-18 23:34:44 +04:00
@@ -2443,7 +2443,7 @@
#
# Bug #16069: VIEW does return the same results as underlying SELECT
# with WHERE condition containing BETWEEN over dates
-
+# Dates as strings should be casted to date type
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY,
td date DEFAULT NULL, KEY idx(td));
@@ -2454,8 +2454,8 @@
CREATE VIEW v1 AS SELECT * FROM t1;
-SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
-SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
+SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
DROP VIEW v1;
DROP TABLE t1;
--- 1.28/mysql-test/r/func_in.result 2006-09-18 23:34:44 +04:00
+++ 1.29/mysql-test/r/func_in.result 2006-09-18 23:34:44 +04:00
@@ -343,3 +343,15 @@
1
2
drop table t1;
+create table t1(f1 char(1));
+insert into t1 values ('a'),('b'),('1');
+select f1 from t1 where f1 in ('a',1);
+f1
+a
+1
+select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
+f1 case f1 when 'a' then '+' when 1 then '-' end
+a +
+b NULL
+1 -
+drop table t1;
--- 1.60/mysql-test/r/func_time.result 2006-09-18 23:34:44 +04:00
+++ 1.61/mysql-test/r/func_time.result 2006-09-18 23:34:44 +04:00
@@ -755,39 +755,38 @@
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 cast("2006-1-1" as date) and cast(20060101 as date);
f1
2006-01-01
-select f1 from t1 where f1 between "2006-1-1" and "2006.1.1";
+select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
f1
2006-01-01
-select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1";
+select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
f1
2006-01-01
-select f2 from t1 where f2 between "12:1:2" and "12:2:2";
+select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);
f2
12:01:02
-select f2 from t1 where time(f2) between "12:1:2" and "12:2:2";
+select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);
f2
12:01:02
-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 f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
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";
+select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
f3
2006-01-01 12:01:01
-select f1 from t1 where "2006-1-1" between f1 and f3;
+select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
f1
2006-01-01
-select f1 from t1 where "2006-1-1" between date(f1) and date(f3);
+select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
f1
2006-01-01
-select f1 from t1 where "2006-1-1" between f1 and 'zzz';
+select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);
f1
Warnings:
-Warning 1292 Incorrect date value: 'zzz' for column 'f1' at row 1
-Warning 1292 Truncated incorrect DOUBLE value: 'zzz'
-Warning 1292 Truncated incorrect DOUBLE value: 'zzz'
+Warning 1292 Incorrect datetime value: 'zzz'
+Warning 1292 Incorrect datetime value: 'zzz'
select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
f1
2006-01-01
--- 1.22/mysql-test/t/func_in.test 2006-09-18 23:34:44 +04:00
+++ 1.23/mysql-test/t/func_in.test 2006-09-18 23:34:44 +04:00
@@ -232,3 +232,27 @@
select some_id from t1 where some_id not in(-4,-1,-4);
select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
drop table t1;
+
+#
+# Bug#18360: Type aggregation for IN and CASE may lead to a wrong result
+#
+create table t1(f1 char(1));
+insert into t1 values ('a'),('b'),('1');
+select f1 from t1 where f1 in ('a',1);
+select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
+create index t1f1_idx on t1(f1);
+select f1 from t1 where f1 in ('a',1);
+explain select f1 from t1 where f1 in ('a',1);
+select f1 from t1 where f1 in ('a','b');
+explain select f1 from t1 where f1 in ('a','b');
+select f1 from t1 where f1 in (2,1);
+explain select f1 from t1 where f1 in (2,1);
+create table t2(f2 int, index t2f2(f2));
+insert into t2 values(0),(1),(2);
+select f2 from t2 where f2 in ('a',2);
+explain select f2 from t2 where f2 in ('a',2);
+select f2 from t2 where f2 in ('a','b');
+explain select f2 from t2 where f2 in ('a','b');
+select f2 from t2 where f2 in (1,'b');
+explain select f2 from t2 where f2 in (1,'b');
+drop table t1, t2;
--- 1.48/mysql-test/t/func_time.test 2006-09-18 23:34:44 +04:00
+++ 1.49/mysql-test/t/func_time.test 2006-09-18 23:34:44 +04:00
@@ -374,20 +374,20 @@
#
# Bug#16377 result of DATE/TIME functions were compared as strings which
# can lead to a wrong result.
-#
+# Date/time in strings should be casted to date/time type
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 "2006-1-1" between f1 and 'zzz';
+select f1 from t1 where f1 between cast("2006-1-1" as date) and cast(20060101 as date);
+select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
+select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
+select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);
+select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);
+select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
+select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
+select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
+select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
+select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);
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;
| Thread |
|---|
| • bk commit into 5.1 tree (evgen:1.2247) BUG#18360 | eugene | 18 Sep |
