Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge 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-08-29 15:51:38+03:00, gkodinov@stripped +7 -0
Bug #13311: IN + binary lead to incorrect SELECT results
It is not correct to substitute column references with constants
in comparisons, IN and BETWEEN if the collations used in the
equality comparison and comparison/IN/BETWEEN don't match.
Extended the compare context of an item to contain collation.
Extended the check for different contexts in const propagation
to check that collations match.
mysql-test/r/binary.result@stripped, 2006-08-29 15:51:25+03:00, gkodinov@stripped +41 -0
Bug #13311: IN + binary lead to incorrect SELECT results
- test case
mysql-test/t/binary.test@stripped, 2006-08-29 15:51:26+03:00, gkodinov@stripped +21 -0
Bug #13311: IN + binary lead to incorrect SELECT results
- test case
sql/item.cc@stripped, 2006-08-29 15:51:27+03:00, gkodinov@stripped +7 -3
Bug #13311: IN + binary lead to incorrect SELECT results
- extended the compare context of an item to cover collation.
- added the nessesary checks
sql/item.h@stripped, 2006-08-29 15:51:28+03:00, gkodinov@stripped +2 -1
Bug #13311: IN + binary lead to incorrect SELECT results
- extended the compare context of an item to cover collation.
sql/item_cmpfunc.cc@stripped, 2006-08-29 15:51:28+03:00, gkodinov@stripped +9 -6
Bug #13311: IN + binary lead to incorrect SELECT results
- extended the compare context of an item to cover collation.
sql/item_func.cc@stripped, 2006-08-29 15:51:29+03:00, gkodinov@stripped +14 -0
Bug #13311: IN + binary lead to incorrect SELECT results
- extended the compare context of an item to cover collation.
sql/item_func.h@stripped, 2006-08-29 15:51:30+03:00, gkodinov@stripped +1 -0
Bug #13311: IN + binary lead to incorrect SELECT results
- extended the compare context of an item to cover collation.
# 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: gkodinov
# Host: macbook.gmz
# Root: /Users/kgeorge/mysql/work/B13311-5.0-opt
--- 1.232/sql/item.cc 2006-08-29 15:51:58 +03:00
+++ 1.233/sql/item.cc 2006-08-29 15:51:58 +03:00
@@ -299,7 +299,8 @@ longlong Item::val_int_from_decimal()
Item::Item():
rsize(0), name(0), orig_name(0), name_length(0), fixed(0),
is_autogenerated_name(TRUE),
- collation(&my_charset_bin, DERIVATION_COERCIBLE)
+ collation(&my_charset_bin, DERIVATION_COERCIBLE),
+ cmp_collation(NULL)
{
marker= 0;
maybe_null=null_value=with_sum_func=unsigned_flag=0;
@@ -345,7 +346,8 @@ Item::Item(THD *thd, Item *item):
with_sum_func(item->with_sum_func),
fixed(item->fixed),
collation(item->collation),
- cmp_context(item->cmp_context)
+ cmp_context(item->cmp_context),
+ cmp_collation(NULL)
{
next= thd->free_list; // Put in free list
thd->free_list= this;
@@ -3791,7 +3793,9 @@ Item *Item_field::equal_fields_propagato
DATE/TIME represented as an int and as a string.
*/
if (!item ||
- (cmp_context != (Item_result)-1 && item->cmp_context != cmp_context))
+ (cmp_context != (Item_result)-1 && item->cmp_context != cmp_context) ||
+ (cmp_context == STRING_RESULT &&
+ cmp_collation && cmp_collation != item->cmp_collation))
item= this;
return item;
}
--- 1.206/sql/item.h 2006-08-29 15:51:59 +03:00
+++ 1.207/sql/item.h 2006-08-29 15:51:59 +03:00
@@ -466,6 +466,7 @@ public:
of its arguments is or contains a
subselect */
Item_result cmp_context; /* Comparison context */
+ CHARSET_INFO *cmp_collation; /* Comparison charset */
// alloc & destruct is done as start of select using sql_alloc
Item();
/*
@@ -727,7 +728,7 @@ public:
virtual Item *get_tmp_table_item(THD *thd) { return copy_or_same(thd); }
static CHARSET_INFO *default_charset();
- virtual CHARSET_INFO *compare_collation() { return NULL; }
+ virtual CHARSET_INFO *compare_collation() { return cmp_collation; }
virtual bool walk(Item_processor processor, byte *arg)
{
--- 1.216/sql/item_cmpfunc.cc 2006-08-29 15:51:59 +03:00
+++ 1.217/sql/item_cmpfunc.cc 2006-08-29 15:51:59 +03:00
@@ -404,9 +404,10 @@ void Item_bool_func2::fix_length_and_dec
args[1]->result_type() == STRING_RESULT &&
agg_arg_charsets(coll, args, 2, MY_COLL_CMP_CONV, 1))
return;
-
- args[0]->cmp_context= args[1]->cmp_context=
- item_cmp_type(args[0]->result_type(), args[1]->result_type());
+
+ set_args_cmp_context (
+ item_cmp_type(args[0]->result_type(), args[1]->result_type()),
+ coll.collation);
// Make a special case of compare with fields to get nicer DATE comparisons
if (functype() == LIKE_FUNC) // Disable conversion in case of LIKE function.
@@ -427,7 +428,7 @@ void Item_bool_func2::fix_length_and_dec
{
cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
INT_RESULT); // Works for all types.
- args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;
+ set_args_cmp_context(INT_RESULT, NULL);
return;
}
}
@@ -442,7 +443,7 @@ void Item_bool_func2::fix_length_and_dec
{
cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
INT_RESULT); // Works for all types.
- args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;
+ set_args_cmp_context(INT_RESULT, NULL);
return;
}
}
@@ -1220,10 +1221,11 @@ void Item_func_between::fix_length_and_d
if (!args[0] || !args[1] || !args[2])
return;
agg_cmp_type(thd, &cmp_type, args, 3);
- args[0]->cmp_context= args[1]->cmp_context= args[2]->cmp_context= cmp_type;
if (cmp_type == STRING_RESULT)
agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV, 1);
+
+ set_args_cmp_context(cmp_type, cmp_collation.collation);
}
@@ -2501,6 +2503,7 @@ void Item_func_in::fix_length_and_dec()
agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV, 1))
return;
+ set_args_cmp_context(cmp_type, cmp_collation.collation);
for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
{
if (!arg[0]->const_item())
--- 1.299/sql/item_func.cc 2006-08-29 15:51:59 +03:00
+++ 1.300/sql/item_func.cc 2006-08-29 15:51:59 +03:00
@@ -553,6 +553,20 @@ Item *Item_func::get_tmp_table_item(THD
return copy_or_same(thd);
}
+
+void Item_func::set_args_cmp_context(Item_result cmp_type,
+ CHARSET_INFO *cmp_collation)
+{
+ Item **arg, **arg_end;
+ for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
+ {
+ arg[0]->cmp_context= cmp_type;
+ if (cmp_type == STRING_RESULT)
+ arg[0]->cmp_collation= cmp_collation;
+ }
+}
+
+
String *Item_int_func::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
--- 1.148/sql/item_func.h 2006-08-29 15:51:59 +03:00
+++ 1.149/sql/item_func.h 2006-08-29 15:51:59 +03:00
@@ -191,6 +191,7 @@ public:
void * arg, traverse_order order);
bool is_expensive_processor(byte *arg);
virtual bool is_expensive() { return 0; }
+ void set_args_cmp_context(Item_result res_type, CHARSET_INFO *collation);
};
--- 1.22/mysql-test/r/binary.result 2006-08-29 15:51:59 +03:00
+++ 1.23/mysql-test/r/binary.result 2006-08-29 15:51:59 +03:00
@@ -160,3 +160,44 @@ hex(col1)
62000000000000000000
62200000000000000000
drop table t1;
+create table t1( a char(20), b char(20));
+insert into t1 values ("john","doe"),("John","Doe");
+select * from t1 where a='John' and a in ( binary 'john');
+a b
+john doe
+select * from t1 where a='john' and a in ( binary 'John');
+a b
+John Doe
+select * from t1 where a='John' and a in ( binary 'john', binary 'john');
+a b
+john doe
+select * from t1 where a='john' and a in ( binary 'John', binary 'John');
+a b
+John Doe
+select * from t1 where a='John' and a in ( binary 'john', 'doe');
+a b
+john doe
+select * from t1 where a='john' and a in ( binary 'John', 'Doe');
+a b
+John Doe
+select * from t1 where a='John' and binary 'john' in (a);
+a b
+john doe
+select * from t1 where a='john' and binary 'John' in (a);
+a b
+John Doe
+select * from t1 where a='John' and binary 'john' in (a,b);
+a b
+john doe
+select * from t1 where a='john' and binary 'John' in (a,b);
+a b
+John Doe
+select * from t1 where a='John' and a in ('john', 'john');
+a b
+john doe
+John Doe
+select * from t1 where a='John' and a in ('john', 'doe');
+a b
+john doe
+John Doe
+drop table t1;
--- 1.17/mysql-test/t/binary.test 2006-08-29 15:51:59 +03:00
+++ 1.18/mysql-test/t/binary.test 2006-08-29 15:51:59 +03:00
@@ -101,3 +101,24 @@ select hex(col1) from t1;
insert into t1 values ('b'),('b ');
select hex(col1) from t1;
drop table t1;
+
+#
+# Bug #13311: IN + binary lead to incorrect SELECT results
+#
+create table t1( a char(20), b char(20));
+insert into t1 values ("john","doe"),("John","Doe");
+
+select * from t1 where a='John' and a in ( binary 'john');
+select * from t1 where a='john' and a in ( binary 'John');
+select * from t1 where a='John' and a in ( binary 'john', binary 'john');
+select * from t1 where a='john' and a in ( binary 'John', binary 'John');
+select * from t1 where a='John' and a in ( binary 'john', 'doe');
+select * from t1 where a='john' and a in ( binary 'John', 'Doe');
+select * from t1 where a='John' and binary 'john' in (a);
+select * from t1 where a='john' and binary 'John' in (a);
+select * from t1 where a='John' and binary 'john' in (a,b);
+select * from t1 where a='john' and binary 'John' in (a,b);
+
+select * from t1 where a='John' and a in ('john', 'john');
+select * from t1 where a='John' and a in ('john', 'doe');
+drop table t1;
| Thread |
|---|
| • bk commit into 5.0 tree (gkodinov:1.2253) BUG#13311 | kgeorge | 29 Aug |
