Bug#9509: Optimizer: wrong result after AND with latin1_german2_ci comparisons

Fixing part2 of this problem: AND didn't work well 
with utf8_czech_ci and utf8_lithianian_ci in some cases.

The problem was because when during condition optimization
field was replaced with a constant, the constant's collation
and collation derivation was used later for comparison instead
of the field collation and derivation, which led to non-equal
new condition in some cases.

This patch copies collation and derivation from the field being removed
to the new constant, which makes comparison work using the same collation
with the one which would be used if no condition optimization were done.

In other words:

  where s1 < 'K' and s1 = 'Y';

was rewritten to:

  where 'Y' < 'K' and s1 = 'Y';

Now it's rewritten to:

  where 'Y' collate collation_of_s1 < 'K' and s1 = 'Y'

  (using derivation of s1)


Note, the first problem of this bug (with latin1_german2_ci) was fixed
earlier in 5.0 tree, in a separate changeset.


mysql-test/r/ctype_utf8.result:
  Adding test case
mysql-test/t/ctype_utf8.test:
  Adding test case
sql/sql_select.cc:
  Set proper collation of the new item

1 files changed, 4 insertions, 0 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 91fc808058f..caaace13c6f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4456,6 +4456,8 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list,
        left_item->collation.collation == value->collation.collation))
   {
     Item *tmp=value->new_item();
+    tmp->collation.set(right_item->collation);
+    
     if (tmp)
     {
       thd->change_item_tree(args + 1, tmp);
@@ -4477,6 +4479,8 @@ change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list,
             right_item->collation.collation == value->collation.collation))
   {
     Item *tmp=value->new_item();
+    tmp->collation.set(left_item->collation);
+    
     if (tmp)
     {
       thd->change_item_tree(args, tmp);