Bug#27580 SPACE() function collation bug?

Problem: when character_set_connection=utf8,
mixing SPACE() with a non-Unicode column (e.g. for concat)
produced "illegal mix of collations" error.
Fix: Item_string() corresponding to space character
is now created using "ASCII" repertoire. Previously
it was incorrectly created using "UNICODE" repertoure, which
didn't allow to convert results of SPACE() to a non-Unicode
character set.


mysql-test/include/ctype_common.inc:
  - Adding test for bug#27580
  - Restoring previous values of character_set_client and character_set_results,
  because ctype_common.inc now changes them when doing "set names utf8"
  in the test for bug#27580
mysql-test/r/ctype_big5.result:
  Adding test
mysql-test/r/ctype_cp1250_ch.result:
  Adding test
mysql-test/r/ctype_euckr.result:
  Adding test
mysql-test/r/ctype_gb2312.result:
  Adding test
mysql-test/r/ctype_gbk.result:
  Adding test
mysql-test/r/ctype_uca.result:
  Adding test
mysql-test/r/ctype_ucs.result:
  Adding test
mysql-test/t/ctype_cp1250_ch.test:
  Adding test
mysql-test/t/ctype_ucs.test:
  Adding test
sql/item_create.cc:
  Item for SQL function SPACE() is now created with ASCII repertoire,
  to allow automatic conversion from UTF8 to column's character
  set e.g. for CONCAT().

10 files changed, 353 insertions, 0 deletions

diff --git a/mysql-test/include/ctype_common.inc b/mysql-test/include/ctype_common.inc
index 202c508a9c9..b9bd7869967 100644
--- a/mysql-test/include/ctype_common.inc
+++ b/mysql-test/include/ctype_common.inc
@@ -13,6 +13,8 @@
 
 SET @safe_character_set_server= @@character_set_server;
 SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
 SET character_set_server= @test_character_set;
 SET collation_server= @test_collation;
 CREATE DATABASE d1;
@@ -51,8 +53,22 @@ SELECT c1 as want1result  from t1 where c1 like 'locatio%';
 SELECT c1 as want1result  from t1 where c1 like 'location%';
 DROP TABLE t1;
 
+#
+# Bug#27580 SPACE() function collation bug?
+#
+set names utf8;
+create table t1 (
+  name varchar(10),
+  level smallint unsigned);
+show create table t1;
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+drop table t1;
+
 DROP DATABASE d1;
 # Restore settings
 USE test;
 SET character_set_server= @safe_character_set_server;
 SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
diff --git a/mysql-test/r/ctype_big5.result b/mysql-test/r/ctype_big5.result
index 6d318a445f5..75b231dbe60 100644
--- a/mysql-test/r/ctype_big5.result
+++ b/mysql-test/r/ctype_big5.result
@@ -3,6 +3,8 @@ SET @test_character_set= 'big5';
 SET @test_collation= 'big5_chinese_ci';
 SET @safe_character_set_server= @@character_set_server;
 SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
 SET character_set_server= @test_character_set;
 SET collation_server= @test_collation;
 CREATE DATABASE d1;
@@ -52,10 +54,27 @@ SELECT c1 as want1result  from t1 where c1 like 'location%';
 want1result
 location
 DROP TABLE t1;
+set names utf8;
+create table t1 (
+name varchar(10),
+level smallint unsigned);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `name` varchar(10) default NULL,
+  `level` smallint(5) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=big5
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+concat(name,space(level))	concat(name, repeat(' ',level))
+string 	string 
+drop table t1;
 DROP DATABASE d1;
 USE test;
 SET character_set_server= @safe_character_set_server;
 SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
 SET NAMES big5;
 SET collation_connection='big5_chinese_ci';
 create table t1 select repeat('a',4000) a;
diff --git a/mysql-test/r/ctype_cp1250_ch.result b/mysql-test/r/ctype_cp1250_ch.result
index 73f415732cd..3f22933e260 100644
--- a/mysql-test/r/ctype_cp1250_ch.result
+++ b/mysql-test/r/ctype_cp1250_ch.result
@@ -2,6 +2,158 @@ DROP TABLE IF EXISTS t1;
 SHOW COLLATION LIKE 'cp1250_czech_cs';
 Collation	Charset	Id	Default	Compiled	Sortlen
 cp1250_czech_cs	cp1250	34		Yes	2
+SET @test_character_set= 'cp1250';
+SET @test_collation= 'cp1250_general_ci';
+SET @safe_character_set_server= @@character_set_server;
+SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
+SET character_set_server= @test_character_set;
+SET collation_server= @test_collation;
+CREATE DATABASE d1;
+USE d1;
+CREATE TABLE t1 (c CHAR(10), KEY(c));
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+c	char(10)	cp1250_general_ci	YES	MUL	NULL			
+INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa');
+SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%';
+want3results
+aaa
+aaaa
+aaaaa
+DROP TABLE t1;
+CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2)));
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+c1	varchar(15)	cp1250_general_ci	YES	MUL	NULL			
+INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab');
+SELECT c1 as want3results from t1 where c1 like 'l%';
+want3results
+location
+loberge
+lotre
+SELECT c1 as want3results from t1 where c1 like 'lo%';
+want3results
+location
+loberge
+lotre
+SELECT c1 as want1result  from t1 where c1 like 'loc%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'loca%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locat%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locati%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locatio%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'location%';
+want1result
+location
+DROP TABLE t1;
+set names utf8;
+create table t1 (
+name varchar(10),
+level smallint unsigned);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `name` varchar(10) default NULL,
+  `level` smallint(5) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=cp1250
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+concat(name,space(level))	concat(name, repeat(' ',level))
+string 	string 
+drop table t1;
+DROP DATABASE d1;
+USE test;
+SET character_set_server= @safe_character_set_server;
+SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
+SET @test_character_set= 'cp1250';
+SET @test_collation= 'cp1250_czech_cs';
+SET @safe_character_set_server= @@character_set_server;
+SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
+SET character_set_server= @test_character_set;
+SET collation_server= @test_collation;
+CREATE DATABASE d1;
+USE d1;
+CREATE TABLE t1 (c CHAR(10), KEY(c));
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+c	char(10)	cp1250_czech_cs	YES	MUL	NULL			
+INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa');
+SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%';
+want3results
+aaa
+aaaa
+aaaaa
+DROP TABLE t1;
+CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2)));
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+c1	varchar(15)	cp1250_czech_cs	YES	MUL	NULL			
+INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab');
+SELECT c1 as want3results from t1 where c1 like 'l%';
+want3results
+location
+loberge
+lotre
+SELECT c1 as want3results from t1 where c1 like 'lo%';
+want3results
+location
+loberge
+lotre
+SELECT c1 as want1result  from t1 where c1 like 'loc%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'loca%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locat%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locati%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locatio%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'location%';
+want1result
+location
+DROP TABLE t1;
+set names utf8;
+create table t1 (
+name varchar(10),
+level smallint unsigned);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `name` varchar(10) collate cp1250_czech_cs default NULL,
+  `level` smallint(5) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+concat(name,space(level))	concat(name, repeat(' ',level))
+string 	string 
+drop table t1;
+DROP DATABASE d1;
+USE test;
+SET character_set_server= @safe_character_set_server;
+SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
 CREATE TABLE t1 (a char(16)) character set cp1250 collate cp1250_czech_cs;
 INSERT INTO t1 VALUES ('');
 SELECT a, length(a), a='', a=' ', a='  ' FROM t1;
diff --git a/mysql-test/r/ctype_euckr.result b/mysql-test/r/ctype_euckr.result
index 57e3e2ed8f8..3409c278847 100644
--- a/mysql-test/r/ctype_euckr.result
+++ b/mysql-test/r/ctype_euckr.result
@@ -3,6 +3,8 @@ SET @test_character_set= 'euckr';
 SET @test_collation= 'euckr_korean_ci';
 SET @safe_character_set_server= @@character_set_server;
 SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
 SET character_set_server= @test_character_set;
 SET collation_server= @test_collation;
 CREATE DATABASE d1;
@@ -52,10 +54,27 @@ SELECT c1 as want1result  from t1 where c1 like 'location%';
 want1result
 location
 DROP TABLE t1;
+set names utf8;
+create table t1 (
+name varchar(10),
+level smallint unsigned);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `name` varchar(10) default NULL,
+  `level` smallint(5) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=euckr
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+concat(name,space(level))	concat(name, repeat(' ',level))
+string 	string 
+drop table t1;
 DROP DATABASE d1;
 USE test;
 SET character_set_server= @safe_character_set_server;
 SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
 SET NAMES euckr;
 SET collation_connection='euckr_korean_ci';
 create table t1 select repeat('a',4000) a;
diff --git a/mysql-test/r/ctype_gb2312.result b/mysql-test/r/ctype_gb2312.result
index 314c336bab9..234201f28d0 100644
--- a/mysql-test/r/ctype_gb2312.result
+++ b/mysql-test/r/ctype_gb2312.result
@@ -3,6 +3,8 @@ SET @test_character_set= 'gb2312';
 SET @test_collation= 'gb2312_chinese_ci';
 SET @safe_character_set_server= @@character_set_server;
 SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
 SET character_set_server= @test_character_set;
 SET collation_server= @test_collation;
 CREATE DATABASE d1;
@@ -52,10 +54,27 @@ SELECT c1 as want1result  from t1 where c1 like 'location%';
 want1result
 location
 DROP TABLE t1;
+set names utf8;
+create table t1 (
+name varchar(10),
+level smallint unsigned);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `name` varchar(10) default NULL,
+  `level` smallint(5) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=gb2312
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+concat(name,space(level))	concat(name, repeat(' ',level))
+string 	string 
+drop table t1;
 DROP DATABASE d1;
 USE test;
 SET character_set_server= @safe_character_set_server;
 SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
 SET NAMES gb2312;
 SET collation_connection='gb2312_chinese_ci';
 create table t1 select repeat('a',4000) a;
diff --git a/mysql-test/r/ctype_gbk.result b/mysql-test/r/ctype_gbk.result
index 3f5d8b0d8c6..d78e3dd3097 100644
--- a/mysql-test/r/ctype_gbk.result
+++ b/mysql-test/r/ctype_gbk.result
@@ -3,6 +3,8 @@ SET @test_character_set= 'gbk';
 SET @test_collation= 'gbk_chinese_ci';
 SET @safe_character_set_server= @@character_set_server;
 SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
 SET character_set_server= @test_character_set;
 SET collation_server= @test_collation;
 CREATE DATABASE d1;
@@ -52,10 +54,27 @@ SELECT c1 as want1result  from t1 where c1 like 'location%';
 want1result
 location
 DROP TABLE t1;
+set names utf8;
+create table t1 (
+name varchar(10),
+level smallint unsigned);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `name` varchar(10) default NULL,
+  `level` smallint(5) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=gbk
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+concat(name,space(level))	concat(name, repeat(' ',level))
+string 	string 
+drop table t1;
 DROP DATABASE d1;
 USE test;
 SET character_set_server= @safe_character_set_server;
 SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
 SET NAMES gbk;
 SET collation_connection='gbk_chinese_ci';
 create table t1 select repeat('a',4000) a;
diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result
index 889702e380c..4fe1eb29b19 100644
--- a/mysql-test/r/ctype_uca.result
+++ b/mysql-test/r/ctype_uca.result
@@ -2538,6 +2538,8 @@ SET @test_character_set= 'utf8';
 SET @test_collation= 'utf8_swedish_ci';
 SET @safe_character_set_server= @@character_set_server;
 SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
 SET character_set_server= @test_character_set;
 SET collation_server= @test_collation;
 CREATE DATABASE d1;
@@ -2587,10 +2589,27 @@ SELECT c1 as want1result  from t1 where c1 like 'location%';
 want1result
 location
 DROP TABLE t1;
+set names utf8;
+create table t1 (
+name varchar(10),
+level smallint unsigned);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `name` varchar(10) collate utf8_swedish_ci default NULL,
+  `level` smallint(5) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+concat(name,space(level))	concat(name, repeat(' ',level))
+string 	string 
+drop table t1;
 DROP DATABASE d1;
 USE test;
 SET character_set_server= @safe_character_set_server;
 SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
 create table t1 (a varchar(1)) character set utf8 collate utf8_estonian_ci;
 insert into t1 values ('A'),('B'),('C'),('a'),('b'),('c');
 select a, a regexp '[a]' from t1 order by binary a;
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 023267c227c..2b58aebeafe 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -1,4 +1,80 @@
 DROP TABLE IF EXISTS t1;
+SET @test_character_set= 'ucs2';
+SET @test_collation= 'ucs2_general_ci';
+SET @safe_character_set_server= @@character_set_server;
+SET @safe_collation_server= @@collation_server;
+SET @safe_character_set_client= @@character_set_client;
+SET @safe_character_set_results= @@character_set_results;
+SET character_set_server= @test_character_set;
+SET collation_server= @test_collation;
+CREATE DATABASE d1;
+USE d1;
+CREATE TABLE t1 (c CHAR(10), KEY(c));
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+c	char(10)	ucs2_general_ci	YES	MUL	NULL			
+INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa');
+SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%';
+want3results
+aaa
+aaaa
+aaaaa
+DROP TABLE t1;
+CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2)));
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+c1	varchar(15)	ucs2_general_ci	YES	MUL	NULL			
+INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab');
+SELECT c1 as want3results from t1 where c1 like 'l%';
+want3results
+location
+loberge
+lotre
+SELECT c1 as want3results from t1 where c1 like 'lo%';
+want3results
+location
+loberge
+lotre
+SELECT c1 as want1result  from t1 where c1 like 'loc%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'loca%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locat%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locati%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'locatio%';
+want1result
+location
+SELECT c1 as want1result  from t1 where c1 like 'location%';
+want1result
+location
+DROP TABLE t1;
+set names utf8;
+create table t1 (
+name varchar(10),
+level smallint unsigned);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `name` varchar(10) default NULL,
+  `level` smallint(5) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=ucs2
+insert into t1 values ('string',1);
+select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
+concat(name,space(level))	concat(name, repeat(' ',level))
+string 	string 
+drop table t1;
+DROP DATABASE d1;
+USE test;
+SET character_set_server= @safe_character_set_server;
+SET collation_server= @safe_collation_server;
+SET character_set_client= @safe_character_set_client;
+SET character_set_results= @safe_character_set_results;
 SET NAMES latin1;
 SET character_set_connection=ucs2;
 select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';
diff --git a/mysql-test/t/ctype_cp1250_ch.test b/mysql-test/t/ctype_cp1250_ch.test
index 86eb8c31d99..b3daa8a02a2 100644
--- a/mysql-test/t/ctype_cp1250_ch.test
+++ b/mysql-test/t/ctype_cp1250_ch.test
@@ -6,6 +6,16 @@ DROP TABLE IF EXISTS t1;
 
 SHOW COLLATION LIKE 'cp1250_czech_cs';
 
+SET @test_character_set= 'cp1250';
+SET @test_collation= 'cp1250_general_ci';
+-- source include/ctype_common.inc
+
+SET @test_character_set= 'cp1250';
+SET @test_collation= 'cp1250_czech_cs';
+-- source include/ctype_common.inc
+
+
+
 #
 # Bugs: #8840: Empty string comparison and character set 'cp1250'
 #
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index bca3a9c3a96..17d9f71e316 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -4,6 +4,10 @@
 DROP TABLE IF EXISTS t1;
 --enable_warnings
 
+SET @test_character_set= 'ucs2';
+SET @test_collation= 'ucs2_general_ci';
+-- source include/ctype_common.inc
+
 SET NAMES latin1;
 SET character_set_connection=ucs2;
 -- source include/endspace.inc