diff options
| -rw-r--r-- | mysql-test/r/case.result | 21 | ||||
| -rw-r--r-- | mysql-test/t/case.test | 22 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 6 |
3 files changed, 49 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; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 326138d798d..ff805e05346 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1149,7 +1149,13 @@ void Item_func_coalesce::fix_length_and_dec() { set_if_bigger(max_length,args[i]->max_length); set_if_bigger(decimals,args[i]->decimals); + cached_result_type=item_store_type(cached_result_type, + args[i]->result_type()); } + if (cached_result_type == STRING_RESULT) + agg_arg_collations(collation, args, arg_count); + else if (cached_result_type != REAL_RESULT) + decimals= 0; } /**************************************************************************** |