Derivation attribute was not processed correctly

by MAX/MIN in some cases:
SELECT coercibility(max(s1)) from t1;

Subselect collation and derivation was not processed
correctly:

create table a select (select s1 from t1);
select * from t1 where s1 = (select s2 from t1);

7 files changed, 40 insertions, 3 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 26ef391024e..8fad9d16dc2 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -612,3 +612,9 @@ select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID =
 Case When Count(*) < MAX_REQ Then 1 Else 0 End
 1
 drop table t1;
+create table t1 (a char(10));
+insert into t1 values ('a'),('b'),('c');
+select coercibility(max(a)) from t1;
+coercibility(max(a))
+3
+drop table t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index bba86ff8891..7f2cb29dd4c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1225,3 +1225,16 @@ a	(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 an
 2	2
 1	2
 drop table t1,t2,t3;
+create table t1 (a char(10) character set koi8r collate koi8r_bin);
+create table t2 select (select a from t1);
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `(select a from t1)` char(10) character set koi8r collate koi8r_bin default NULL
+) TYPE=MyISAM CHARSET=latin1
+drop table t1,t2;
+CREATE TABLE t1 
+(s1 CHAR(5) COLLATE latin1_german1_ci,
+s2 CHAR(5) COLLATE latin1_swedish_ci);
+SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
+ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index fed19cbe07c..8a43716d854 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -355,3 +355,8 @@ insert into t1 values (1, 3);
 select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ;
 select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
 drop table t1;
+
+create table t1 (a char(10));
+insert into t1 values ('a'),('b'),('c');
+select coercibility(max(a)) from t1;
+drop table t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 66d8dd2bc32..e7c63098cab 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -817,4 +817,15 @@ insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
 insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
 insert into t3 values (3,3), (2,2), (1,1); 
 select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3;
-drop table t1,t2,t3;s
+drop table t1,t2,t3;
+
+create table t1 (a char(10) character set koi8r collate koi8r_bin);
+create table t2 select (select a from t1);
+show create table t2;
+drop table t1,t2;
+
+CREATE TABLE t1 
+(s1 CHAR(5) COLLATE latin1_german1_ci,
+ s2 CHAR(5) COLLATE latin1_swedish_ci);
+--error 1265
+SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
diff --git a/sql/item.cc b/sql/item.cc
index 072dec4e6a6..ee8b77881cb 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1442,7 +1442,7 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
   max_length= (*ref)->max_length;
   maybe_null= (*ref)->maybe_null;
   decimals=   (*ref)->decimals;
-  set_charset((*ref)->charset());
+  collation.set((*ref)->collation);
   with_sum_func= (*ref)->with_sum_func;
   fixed= 1;
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index cd78edfee7b..26f8313159f 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -759,6 +759,7 @@ static Item_result set_row(SELECT_LEX *select_lex, Item * item,
       if (!(row[i]= Item_cache::get_cache(res_type)))
 	return STRING_RESULT; // we should return something
       row[i]->set_len_n_dec(sel_item->max_length, sel_item->decimals);
+      row[i]->collation.set(sel_item->collation);
     }
   }
   if (select_lex->item_list.elements > 1)
@@ -770,6 +771,7 @@ void subselect_single_select_engine::fix_length_and_dec(Item_cache **row)
 {
   DBUG_ASSERT(row || select_lex->item_list.elements==1);
   res_type= set_row(select_lex, item, row, &maybe_null);
+  item->collation.set(row[0]->collation);
   if (cols() != 1)
     maybe_null= 0;
 }
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 8d3d0de466a..aa146926eb5 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -199,7 +199,7 @@ Item_sum_hybrid::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
   decimals=item->decimals;
   maybe_null=item->maybe_null;
   unsigned_flag=item->unsigned_flag;
-  set_charset(item->charset());
+  collation.set(item->collation);
   result_field=0;
   null_value=1;
   fix_length_and_dec();