Better tests for BINARY

4 files changed, 95 insertions, 0 deletions

diff --git a/mysql-test/r/binary.result b/mysql-test/r/binary.result
index 9c3174ec606..f6ad190b05a 100644
--- a/mysql-test/r/binary.result
+++ b/mysql-test/r/binary.result
@@ -79,3 +79,34 @@ NULL
 select b from t1 having binary b like '';
 b
 drop table t1;
+create table t1 (a char(15) binary, b binary(15));
+insert into t1 values ('aaa','bbb'),('AAA','BBB');
+select upper(a),upper(b) from t1;
+upper(a)	upper(b)
+AAA	bbb
+AAA	BBB
+select lower(a),lower(b) from t1;
+lower(a)	lower(b)
+aaa	bbb
+aaa	BBB
+select * from t1 where upper(a)='AAA';
+a	b
+aaa	bbb
+AAA	BBB
+select * from t1 where lower(a)='aaa';
+a	b
+aaa	bbb
+AAA	BBB
+select * from t1 where upper(b)='BBB';
+a	b
+AAA	BBB
+select * from t1 where lower(b)='bbb';
+a	b
+aaa	bbb
+select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1;
+charset(a)	charset(b)	charset(binary 'ccc')
+latin1	binary	latin1
+select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1;
+collation(a)	collation(b)	collation(binary 'ccc')
+latin1_bin	binary	latin1_bin
+drop table t1;
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index a1f860e1f83..2a59f976156 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -1,4 +1,5 @@
 drop table if exists t1;
+SET NAMES cp1251;
 create table t1 (a varchar(10) not null);
 insert into t1 values ("a"),("ab"),("abc");
 select * from t1;
@@ -22,3 +23,34 @@ a
 b
 c
 drop table t1;
+create table t1 (a char(15) binary, b binary(15));
+insert into t1 values ('aaa','bbb'),('AAA','BBB');
+select upper(a),upper(b) from t1;
+upper(a)	upper(b)
+AAA	bbb
+AAA	BBB
+select lower(a),lower(b) from t1;
+lower(a)	lower(b)
+aaa	bbb
+aaa	BBB
+select * from t1 where upper(a)='AAA';
+a	b
+aaa	bbb
+AAA	BBB
+select * from t1 where lower(a)='aaa';
+a	b
+aaa	bbb
+AAA	BBB
+select * from t1 where upper(b)='BBB';
+a	b
+AAA	BBB
+select * from t1 where lower(b)='bbb';
+a	b
+aaa	bbb
+select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1;
+charset(a)	charset(b)	charset(binary 'ccc')
+cp1251	binary	cp1251
+select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1;
+collation(a)	collation(b)	collation(binary 'ccc')
+cp1251_bin	binary	cp1251_bin
+drop table t1;
diff --git a/mysql-test/t/binary.test b/mysql-test/t/binary.test
index 48912a390ed..20a047e0b26 100644
--- a/mysql-test/t/binary.test
+++ b/mysql-test/t/binary.test
@@ -51,3 +51,18 @@ select b from t1 where binary b like '';
 select b from t1 group by binary b like '';
 select b from t1 having binary b like '';
 drop table t1;
+
+#
+# Test of binary and upper/lower
+#
+create table t1 (a char(15) binary, b binary(15));
+insert into t1 values ('aaa','bbb'),('AAA','BBB');
+select upper(a),upper(b) from t1;
+select lower(a),lower(b) from t1;
+select * from t1 where upper(a)='AAA';
+select * from t1 where lower(a)='aaa';
+select * from t1 where upper(b)='BBB';
+select * from t1 where lower(b)='bbb';
+select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1;
+select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1;
+drop table t1;
diff --git a/mysql-test/t/ctype_cp1251.test b/mysql-test/t/ctype_cp1251.test
index fffade35389..66a8a5aa909 100644
--- a/mysql-test/t/ctype_cp1251.test
+++ b/mysql-test/t/ctype_cp1251.test
@@ -4,6 +4,8 @@
 drop table if exists t1;
 --enable_warnings
 
+SET NAMES cp1251;
+
 #
 # Test problem with LEFT() (Bug #514)
 #
@@ -15,3 +17,18 @@ select a, left(a,1) as b from t1;
 select a, left(a,1) as b from t1 group by a;
 SELECT DISTINCT RIGHT(a,1) from t1;
 drop table t1;
+
+#
+# Test of binary and upper/lower
+#
+create table t1 (a char(15) binary, b binary(15));
+insert into t1 values ('aaa','bbb'),('AAA','BBB');
+select upper(a),upper(b) from t1;
+select lower(a),lower(b) from t1;
+select * from t1 where upper(a)='AAA';
+select * from t1 where lower(a)='aaa';
+select * from t1 where upper(b)='BBB';
+select * from t1 where lower(b)='bbb';
+select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1;
+select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1;
+drop table t1;