ctype_recoding.test

 #Get deafult engine value
--let $DEFAULT_ENGINE = `select @@global.default_storage_engine`

SET CHARACTER SET koi8r;

--disable_warnings
DROP TABLE IF EXISTS ÔÁÂÌÉÃÁ, t1, t2;
--enable_warnings

SET CHARACTER SET koi8r;
CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp1251) SELECT _koi8r'ÐÒÏÂÁ' AS a;
CREATE TABLE t2 (a CHAR(10) CHARACTER SET utf8);

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE t1;
SELECT a FROM t1;
SELECT HEX(a) FROM t1;
INSERT t2 SELECT * FROM t1;
SELECT HEX(a) FROM t2;
DROP TABLE t1, t2;


#
# Check that long strings conversion does not fail (bug#2218)
#
CREATE TABLE t1 (description text character set cp1250 NOT NULL);
INSERT INTO t1 (description) VALUES (_latin2'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaasssssssssssaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddde');
SELECT description FROM t1;
DROP TABLE t1;

# same with TEXT
CREATE TABLE t1 (a TEXT CHARACTER SET cp1251) SELECT _koi8r'ÐÒÏÂÁ' AS a;
CREATE TABLE t2 (a TEXT CHARACTER SET utf8);

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE t1;
SELECT HEX(a) FROM t1;
INSERT t2 SELECT * FROM t1;
SELECT HEX(a) FROM t2;
DROP TABLE t1, t2;

CREATE TABLE `ÔÁÂÌÉÃÁ`
(
  ÐÏÌÅ CHAR(32) CHARACTER SET koi8r NOT NULL COMMENT "ËÏÍÍÅÎÔÁÒÉÊ ÐÏÌÑ"
) COMMENT "ËÏÍÍÅÎÔÁÒÉÊ ÔÁÂÌÉÃÙ";

SHOW TABLES;

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE ÔÁÂÌÉÃÁ;
SHOW FIELDS FROM ÔÁÂÌÉÃÁ;

SET CHARACTER SET cp1251;
SHOW TABLES;

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE òàáëèöà;
SHOW FIELDS FROM òàáëèöà;


SET CHARACTER SET utf8;
SHOW TABLES;

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE таблица;
SHOW FIELDS FROM таблица;

SET CHARACTER SET koi8r;
DROP TABLE ÔÁÂÌÉÃÁ;
SET CHARACTER SET default;

# Test for Item_func_conv_charset::fix_fields (bug #3704)
SET NAMES UTF8;
CREATE TABLE t1 (t text) DEFAULT CHARSET UTF8;
INSERT INTO t1 (t) VALUES ('x');
SELECT 1 FROM t1 WHERE CONCAT(_latin1'x') = t;
DROP TABLE t1;

SET CHARACTER SET koi8r;
CREATE DATABASE ÔÅÓÔ;
USE ÔÅÓÔ;
SHOW TABLES;
SHOW TABLES IN ÔÅÓÔ;
SET CHARACTER SET cp1251;
SHOW TABLES;
SHOW TABLES IN òåñò;
SET CHARACTER SET koi8r;
DROP DATABASE ÔÅÓÔ;

SET NAMES koi8r;
SELECT hex('ÔÅÓÔ');
SET character_set_connection=cp1251;
SELECT hex('ÔÅÓÔ');

USE test;

# Bug#4417
# Check that identifiers and strings are not converted 
# when the client character set is binary.

SET NAMES binary;
CREATE TABLE `тест` (`тест` int);

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE `тест`;
SET NAMES utf8;

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE `тест`;
DROP TABLE `тест`;
SET NAMES binary;
SET character_set_connection=utf8;
SELECT 'тест' as s;
SET NAMES utf8;
SET character_set_connection=binary;
SELECT 'тест' as s;

# Bug#4417, another aspect:

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
# Check that both "SHOW CREATE TABLE" and "SHOW COLUMNS"
# return column names and default values in UTF8 after "SET NAMES BINARY"

SET NAMES latin1;
CREATE TABLE t1 (`ä` CHAR(128) DEFAULT 'ä', `ä1` ENUM('ä1','ä2') DEFAULT 'ä2');

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE t1;
SHOW COLUMNS FROM t1;
SET NAMES binary;

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE t1;
SHOW COLUMNS FROM t1;
DROP TABLE t1;


#
# Test that we allow only well-formed UTF8 identitiers
#
SET NAMES binary;
--error 1300
CREATE TABLE `goodÐÌÏÈÏ` (a int);
SET NAMES utf8;
--error 1300
CREATE TABLE `goodÐÌÏÈÏ` (a int);


#
# Test that we produce a warnign when conversion loses data.
#
set names latin1;
create table t1 (a char(10) character set koi8r, b text character set koi8r);
insert into t1 values ('test','test');
insert ignore into t1 values ('ÊÃÕË','ÊÃÕË');
drop table t1;

#
# Try to  apply an automatic conversion in some cases:
# E.g. when mixing a column to a string, the string
# is converted into the column character set.
# If conversion loses data, then error. Otherwise,
# the string is replaced by its converted representation
#
set names koi8r;
create table t1 (a char(10) character set cp1251);
insert into t1 values (_koi8r'×ÁÓÑ');
# this is possible:
select * from t1 where a=_koi8r'×ÁÓÑ';
# this is possible, because we have a function with constant arguments:
select * from t1 where a=concat(_koi8r'×ÁÓÑ');
# this is not posible, cannot convert _latin1'×ÁÓÑ' into cp1251:
--error 1267
select * from t1 where a=_latin1'×ÁÓÑ';
drop table t1;
set names latin1;

#
# Test the same with ascii
#
set names ascii;
create table t1 (a char(1) character set latin1);
insert into t1 values ('a');
select * from t1 where a='a';
drop table t1;
set names latin1;

#
# Bug#10446 Illegal mix of collations
#
create table t1 (a char(10) character set utf8 collate utf8_bin);
insert into t1 values ('       xxx');
select * from t1 where a=lpad('xxx',10,' ');
drop table t1;

#
# Check more automatic conversion
#
set names koi8r;
create table t1 (c1 char(10) character set cp1251);
insert into t1 values ('ß');
select c1 from t1 where c1 between 'ß' and 'ß';
select ifnull(c1,'ß'), ifnull(null,c1) from t1;
select if(1,c1,'ö'), if(0,c1,'ö') from t1;
select coalesce('ö',c1), coalesce(null,c1) from t1;
select least(c1,'ö'), greatest(c1,'ö') from t1;
select locate(c1,'ß'), locate('ß',c1) from t1;
select field(c1,'ß'),field('ß',c1) from t1;
select concat(c1,'ö'), concat('ö',c1) from t1;
select concat_ws(c1,'ö','ß'), concat_ws('ö',c1,'ß') from t1;
select replace(c1,'ß','ö'), replace('ß',c1,'ö') from t1;
select substring_index(c1,'öößß',2) from t1;
select elt(1,c1,'ö'),elt(1,'ö',c1) from t1;
select make_set(3,c1,'ö'), make_set(3,'ö',c1) from t1;
select insert(c1,1,2,'ö'),insert('ö',1,2,c1) from t1;
select trim(c1 from 'ß'),trim('ß' from c1) from t1;
select lpad(c1,3,'ö'), lpad('ö',3,c1) from t1;
select rpad(c1,3,'ö'), rpad('ö',3,c1) from t1;
# TODO
#select case c1 when 'ß' then 'ß' when 'ö' then 'ö' else 'c' end from t1;
#select export_set(5,c1,'ö'), export_set(5,'ö',c1) from t1;
drop table t1;
 
#
# Bug 20695: problem with field default value's character set
#

set names koi8r;
create table t1(a char character set cp1251 default _koi8r 0xFF);

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
show create table t1;
drop table t1;
--error 1067
create table t1(a char character set latin1 default _cp1251 0xFF);

--echo End of 4.1 tests