3 files changed, 60 insertions, 0 deletions

diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 04229bd1882..346ad8fe80e 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -144,3 +144,30 @@ ALTER TABLE t1 DISABLE KEYS;
 INSERT DELAYED INTO t1 VALUES(1),(2),(3);
 ALTER TABLE t1 ENABLE KEYS;
 drop table t1;
+
+#
+# Test that data get converted when character set is changed
+# Test that data doesn't get converted when src or dst is BINARY/BLOB
+#
+set names koi8r;
+create table t1 (a char(10) character set koi8r);
+insert into t1 values ('ΤΕΣΤ');
+select a,hex(a) from t1;
+alter table t1 change a a char(10) character set cp1251;
+select a,hex(a) from t1;
+alter table t1 change a a char(10) binary;
+select a,hex(a) from t1;
+alter table t1 change a a char(10) character set cp1251;
+select a,hex(a) from t1;
+alter table t1 change a a char(10) character set koi8r;
+select a,hex(a) from t1;
+alter table t1 change a a varchar(10) character set cp1251;
+select a,hex(a) from t1;
+alter table t1 change a a char(10) character set koi8r;
+select a,hex(a) from t1;
+alter table t1 change a a text character set cp1251;
+select a,hex(a) from t1;
+alter table t1 change a a char(10) character set koi8r;
+select a,hex(a) from t1;
+
+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/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index c9e7b1a529d..1fabd997366 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -294,3 +294,14 @@ select
 ;
 show create table t1;
 drop table t1;
+
+#
+# test for SUBSTR
+#
+select SUBSTR('abcdefg',3,2);
+select SUBSTRING('abcdefg',3,2);
+select SUBSTR('abcdefg',-3,2) FROM DUAL;
+select SUBSTR('abcdefg',-1,5) FROM DUAL;
+select SUBSTR('abcdefg',0,0) FROM DUAL;
+select SUBSTR('abcdefg',-1,-1) FROM DUAL;
+select SUBSTR('abcdefg',1,-1) FROM DUAL;