COALESCE now aggregates its argument types in this way:

if some of the arguments is STRING_RESULT the STRING_RESULT
else if some of the arguments is REAL_RESULT then REAL_RESULT
else INT_RESULT 

2 files changed, 43 insertions, 0 deletions

diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result
index 914e05efa7a..04627a7a493 100644
--- a/mysql-test/r/case.result
+++ b/mysql-test/r/case.result
@@ -71,3 +71,24 @@ orange
 yellow
 green
 drop table t1;
+SET NAMES latin1;
+CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'coalesce'
+CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin);
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
+CREATE TABLE t1 SELECT 
+COALESCE(1), COALESCE(1.0),COALESCE('a'),
+COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
+COALESCE('a' COLLATE latin1_bin,'b');
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `COALESCE(1)` int(1) NOT NULL default '0',
+  `COALESCE(1.0)` double(3,1) NOT NULL default '0.0',
+  `COALESCE('a')` char(1) NOT NULL default '',
+  `COALESCE(1,1.0)` double(3,1) NOT NULL default '0.0',
+  `COALESCE(1,'1')` char(1) NOT NULL default '',
+  `COALESCE(1.1,'1')` char(3) NOT NULL default '',
+  `COALESCE('a' COLLATE latin1_bin,'b')` char(1) character set latin1 collate latin1_bin NOT NULL default ''
+) TYPE=MyISAM CHARSET=latin1
+DROP TABLE t1;
diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test
index 6131d1500de..0249a8eefba 100644
--- a/mysql-test/t/case.test
+++ b/mysql-test/t/case.test
@@ -41,3 +41,25 @@ create table t1 (row int not null, col int not null, val varchar(255) not null);
 insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small');
 select max(case col when 1 then val else null end) as color from t1 group by row;
 drop table t1;
+
+#
+# COALESCE is a CASE abbrevation:
+#
+# COALESCE(v1,v2) == CASE WHEN v1 IS NOT NULL THEN v1 ELSE v2 END
+#
+# COALESCE(V1, V2, . . . ,Vn ) =  
+#     CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,Vn) END
+#
+# Check COALESCE argument types aggregation
+
+SET NAMES latin1;
+--error 1265
+CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
+--error 1265
+CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin);
+CREATE TABLE t1 SELECT 
+ COALESCE(1), COALESCE(1.0),COALESCE('a'),
+ COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
+ COALESCE('a' COLLATE latin1_bin,'b');
+SHOW CREATE TABLE t1;
+DROP TABLE t1;