~drizzle-trunk/drizzle/development

#

# Tests with the utf8 character set

#

--disable_warnings

drop table if exists t1,t2;

--enable_warnings

select left(0xD0B0D0B1D0B2,1);

select right(0xD0B0D0B2D0B2,1);

select locate('he','hello');

select locate('he','hello',2);

select locate('lo','hello',2);

select locate('HE','hello');

select locate('HE','hello',2);

select locate('LO','hello',2);

select locate('HE','hello' collate utf8_bin);

select locate('HE','hello' collate utf8_bin,2);

select locate('LO','hello' collate utf8_bin,2);

select locate(0xD0B1, 0xD0B0D0B1D0B2);

select locate(0xD091, 0xD0B0D0B1D0B2);

select locate(0xD0B1, 0xD0B0D091D0B2);

# TODO: Bug lp:314472

#select locate(0xD091, 0xD0B0D0B1D0B2 collate utf8_bin);

#select locate(0xD0B1, 0xD0B0D091D0B2 collate utf8_bin);

select length(0xD0B1), char_length(0xD0B1);

select 'a' like 'a';

select 'A' like 'a';

select 'A' like 'a' collate utf8_bin;

select 0xD0B0D0B1D0B2 like concat('%',0xD0B1,'%');

# Bug #6040: can't retrieve records with umlaut

# characters in case insensitive manner.

# Case insensitive search LIKE comparison

# was broken for multibyte characters:

select 'Günter André' like 'GÜNTER%';

#

# Check the following:

# "a"  == "a "

# "a\0" < "a"

# "a\0" < "a "

SELECT 'a' = 'a ';

SELECT 'a\0' < 'a';

SELECT 'a\0' < 'a ';

SELECT 'a\t' < 'a';

SELECT 'a\t' < 'a ';

#

# The same for binary collation

#

SELECT 'a' = 'a ' collate utf8_bin;

SELECT 'a\0' < 'a' collate utf8_bin;

SELECT 'a\0' < 'a ' collate utf8_bin;

SELECT 'a\t' < 'a' collate utf8_bin;

SELECT 'a\t' < 'a ' collate utf8_bin;

CREATE TABLE t1 (a char(10) not null);

INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');

SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;

DROP TABLE t1;

#

# Fix this, it should return 1:

#

#select 0xD0B0D0B1D0B2 like concat('%',0xD091,'%');

#

#

# Bug 2367: INSERT() behaviour is different for different charsets.

#

select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');

select insert("aa",100,1,"b"),insert("aa",1,3,"b");

#

# LELF() didn't work well with utf8 in some cases too.

#

select char_length(left(@a:='න ',5)), length(@a), @a;

#

# CREATE ... SELECT

#

create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d");

show create table t1;

select * from t1;

drop table t1;

# TODO: Bug lp:314474

#create table t1 (s1 tinytext);

#insert into t1 select repeat('a',300);

#insert into t1 select repeat('Ñ',300);

#insert into t1 select repeat('aÑ',300);

#insert into t1 select repeat('Ña',300);

#insert into t1 select repeat('ÑÑ',300);

#select hex(s1) from t1;

#select length(s1),char_length(s1) from t1;

#drop table t1;

#create table t1 (s1 text);

#insert into t1 select repeat('a',66000);

#insert into t1 select repeat('Ñ',66000);

#insert into t1 select repeat('aÑ',66000);

#insert into t1 select repeat('Ña',66000);

#insert into t1 select repeat('ÑÑ',66000);

#select length(s1),char_length(s1) from t1;

#drop table t1;

#

# Bug #2368 Multibyte charsets do not check that incoming data is well-formed

#

create table t1 (s1 char(10));

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1 values (0x41FF);

select hex(s1) from t1;

drop table t1;

create table t1 (s1 varchar(10));

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1 values (0x41FF);

select hex(s1) from t1;

drop table t1;

create table t1 (s1 text);

--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

insert into t1 values (0x41FF);

select hex(s1) from t1;

drop table t1;

#

# Bug 2699

# UTF8 breaks primary keys for cols > 333 characters

#

--error ER_TOO_LONG_KEY

create table t1 (a text, primary key(a(360))) engine=innodb;

#

# Bug 2959

# UTF8 charset breaks joins with mixed column/string constant

#

CREATE TABLE t1 ( a varchar(10) );

INSERT INTO t1 VALUES ( 'test' );

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a;

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test';

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test';

DROP TABLE t1;

create table t1 (a char(255));

insert into t1 values('b'),('b');

select * from t1 where a = 'b';

select * from t1 where a = 'b' and a = 'b';

select * from t1 where a = 'b' and a != 'b';

drop table t1;

#

# Bug #4555

# ALTER TABLE crashes mysqld with enum column collated utf8_unicode_ci

#

CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8_unicode_ci);

ALTER TABLE t1 ADD COLUMN b CHAR(20);

DROP TABLE t1;

# Customer Support Center issue # 3299 

# ENUM and SET multibyte fields computed their length wronly 

# when converted into a char field

# TODO: Bug lp:314502

#create table t1 (a enum('aaaa', '킱'));

#show create table t1;

#insert into t1 values ('킱');

#select * from t1;

#create table t2 select ifnull(a,a) from t1;

#show create table t2;

#select * from t2;

#drop table t1;

#drop table t2;

#

# Bug 4521: unique key prefix interacts poorly with utf8

# MYISAM: keys with prefix compression, case insensitive collation.

#

create table t1 (c varchar(30), unique(c(10)));

insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');

insert into t1 values ('aaaaaaaaaa');

--error ER_DUP_ENTRY

insert into t1 values ('aaaaaaaaaaa');

--error ER_DUP_ENTRY

insert into t1 values ('aaaaaaaaaaaa');

insert into t1 values (repeat('b',20));

select c c1 from t1 where c='1';

select c c2 from t1 where c='2';

select c c3 from t1 where c='3';

select c cx from t1 where c='x';

select c cy from t1 where c='y';

select c cz from t1 where c='z';

select c ca10 from t1 where c='aaaaaaaaaa';

select c cb20 from t1 where c=repeat('b',20);

drop table t1;

#

# Bug 4521: unique key prefix interacts poorly with utf8

# InnoDB: keys with prefix compression, case insensitive collation.

#

--disable_warnings

create table t1 (c varchar(30), unique(c(10))) engine=innodb;

--enable_warnings

insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');

insert into t1 values ('aaaaaaaaaa');

--error ER_DUP_ENTRY

insert into t1 values ('aaaaaaaaaaa');

--error ER_DUP_ENTRY

insert into t1 values ('aaaaaaaaaaaa');

insert into t1 values (repeat('b',20));

select c c1 from t1 where c='1';

select c c2 from t1 where c='2';

select c c3 from t1 where c='3';

select c cx from t1 where c='x';

select c cy from t1 where c='y';

select c cz from t1 where c='z';

select c ca10 from t1 where c='aaaaaaaaaa';

select c cb20 from t1 where c=repeat('b',20);

drop table t1;

#

# Bug 4521: unique key prefix interacts poorly with utf8

# MYISAM: fixed length keys, case insensitive collation

#

create TEMPORARY table t1 (c char(3), unique (c(2))) engine=myisam;

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

insert into t1 values ('a');

insert into t1 values ('aa');

--error ER_DUP_ENTRY

insert into t1 values ('aaa');

insert into t1 values ('b');

insert into t1 values ('bb');

--error ER_DUP_ENTRY

insert into t1 values ('bbb');

insert into t1 values ('킱');

insert into t1 values ('킱킱');

--error ER_DUP_ENTRY

insert into t1 values ('킱킱킱');

drop table t1;

#

# Bug 4521: unique key prefix interacts poorly with utf8

# InnoDB: fixed length keys, case insensitive collation

#

create table t1 (c char(3), unique (c(2))) engine=innodb;

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

insert into t1 values ('a');

insert into t1 values ('aa');

--error ER_DUP_ENTRY

insert into t1 values ('aaa');

insert into t1 values ('b');

insert into t1 values ('bb');

--error ER_DUP_ENTRY

insert into t1 values ('bbb');

insert into t1 values ('킱');

insert into t1 values ('킱킱');

--error ER_DUP_ENTRY

insert into t1 values ('킱킱킱');

drop table t1;

#

# Bug 4531: unique key prefix interacts poorly with utf8

# Check MEMORY+HASH, case insensitive collation

#

create TEMPORARY table t1 (

c char(10),

unique key a using hash (c(2))

) engine=MEMORY;

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

insert into t1 values ('a');

insert into t1 values ('aa');

--error ER_DUP_ENTRY

insert into t1 values ('aaa');

insert into t1 values ('b');

insert into t1 values ('bb');

--error ER_DUP_ENTRY

insert into t1 values ('bbb');

insert into t1 values ('킱');

insert into t1 values ('킱킱');

--error ER_DUP_ENTRY

insert into t1 values ('킱킱킱');

select c as c_all from t1 order by c;

select c as c_a from t1 where c='a';

select c as c_a from t1 where c='킱';

drop table t1;

#

# Bug 4531: unique key prefix interacts poorly with utf8

# Check MEMORY+BTREE, case insensitive collation

#

create TEMPORARY table t1 (

c char(10),

unique key a using btree (c(2))

) engine=MEMORY;

show create table t1;

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

insert into t1 values ('a');

insert into t1 values ('aa');

--error ER_DUP_ENTRY

insert into t1 values ('aaa');

insert into t1 values ('b');

insert into t1 values ('bb');

--error ER_DUP_ENTRY

insert into t1 values ('bbb');

insert into t1 values ('킱');

insert into t1 values ('킱킱');

--error ER_DUP_ENTRY

insert into t1 values ('킱킱킱');

select c as c_all from t1 order by c;

select c as c_a from t1 where c='a';

select c as c_a from t1 where c='킱';

drop table t1;

# Bug#4594: column index make = failed for gbk, but like works

# Check MYISAM

#

create TEMPORARY table t1 (

  str varchar(255) not null,

  key str  (str(2))

) engine=myisam;

INSERT INTO t1 VALUES ('str');

INSERT INTO t1 VALUES ('str2');

select * from t1 where str='str';

drop table t1;

# Bug#4594: column index make = failed for gbk, but like works

# Check InnoDB

#

--disable_warnings

create table t1 (

  str varchar(255) not null,

  key str  (str(2))

) engine=innodb;

--enable_warnings

INSERT INTO t1 VALUES ('str');

INSERT INTO t1 VALUES ('str2');

select * from t1 where str='str';

drop table t1;

# the same for MEMORY+BTREE

#

create temporary table t1 (

  str varchar(255) not null,

  key str using btree (str(2))

) engine=MEMORY;

INSERT INTO t1 VALUES ('str');

INSERT INTO t1 VALUES ('str2');

select * from t1 where str='str';

drop table t1;

# the same for MEMORY+HASH

#

create temporary table t1 (

  str varchar(255) not null,

  key str using hash (str(2))

) engine=MEMORY;

INSERT INTO t1 VALUES ('str');

INSERT INTO t1 VALUES ('str2');

select * from t1 where str='str';

drop table t1;

# the same for BDB

#

--disable_warnings

create table t1 (

  str varchar(255) not null,

  key str (str(2))

) engine=innodb;

--enable_warnings

INSERT INTO t1 VALUES ('str');

INSERT INTO t1 VALUES ('str2');

select * from t1 where str='str';

drop table t1;

#

# Bug #5397: Crash with varchar binary and LIKE

#

CREATE TABLE t1 (a varchar(32));

INSERT INTO t1 VALUES ('test');

SELECT a FROM t1 WHERE a LIKE '%te';

DROP TABLE t1;

#

# Bug #5723: length(<varchar utf8 field>) returns varying results

#

--disable_warnings

CREATE TABLE t1 (

  subject varchar(255) collate utf8_unicode_ci,

  p varchar(15)

) ENGINE=InnoDB;

--enable_warnings

INSERT INTO t1 values ('킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱','abcdefghijklmno');

INSERT INTO t1 VALUES ('aaa','bbb');

SELECT length(subject) FROM t1;

SELECT length(subject) FROM t1 ORDER BY 1;

DROP TABLE t1;

#

# Bug #5832 SELECT doesn't return records in some cases

#

CREATE TEMPORARY TABLE t1 (

    id       int NOT NULL auto_increment,

    list_id  int NOT NULL,

    term     TEXT NOT NULL,

    PRIMARY KEY(id),

    INDEX(list_id, term(4))

) ENGINE=MYISAM;

INSERT INTO t1 SET list_id = 1, term = "letterc";

INSERT INTO t1 SET list_id = 1, term = "letterb";

INSERT INTO t1 SET list_id = 1, term = "lettera";

INSERT INTO t1 SET list_id = 1, term = "letterd";

SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");

SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");

SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");

SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");

DROP TABLE t1;

#

# Bug #6043 erratic searching for diacriticals in indexed MyISAM UTF-8 table

#

CREATE TEMPORARY TABLE t1 (

    id int NOT NULL auto_increment,

    list_id int NOT NULL,

    term text NOT NULL,

    PRIMARY KEY(id),

    INDEX(list_id, term(19))

) ENGINE=MyISAM;

INSERT INTO t1 set list_id = 1, term = "testétest";

INSERT INTO t1 set list_id = 1, term = "testetest";

INSERT INTO t1 set list_id = 1, term = "testètest";

SELECT id, term FROM t1 where (list_id = 1) AND (term = "testétest");

SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest");

SELECT id, term FROM t1 where (list_id = 1) AND (term = "testètest");

DROP TABLE t1;

#

# Bug #6019 SELECT tries to use too short prefix index on utf8 data

#

--disable_warnings

create table t1 (

  a int primary key,

  b varchar(6),

  index b3(b(3))

) engine=innodb;

--enable_warnings

insert into t1 values(1,'foo'),(2,'foobar');

select * from t1 where b like 'foob%';

--disable_warnings

alter table t1 engine=innodb;

--enable_warnings

select * from t1 where b like 'foob%';

drop table t1;

#

# Test for calculate_interval_lengths() function

#

create table t1 (

  a enum('☃','☄','킱') not null default '킱'

);

create table t2 select concat(a,'') as a from t1;

show create table t2;

drop table t2;

drop table t1;

#

# Bug #6787 LIKE not working properly with _ and utf8 data

#

select 'c' like '\_' as want0; 

#

# SUBSTR with negative offset didn't work with multi-byte strings

#

SELECT SUBSTR('☃☄킱',-2);

-- source include/ctype_filesort.inc

-- source include/ctype_like_escape.inc

-- source include/ctype_german.inc

-- source include/ctype_filesort.inc

-- source include/ctype_like_escape.inc

#

# Bugs#5980: NULL requires a characterset in a union

#

create table t1 (a varchar(10));

insert into t1 values ('test');

select ifnull(a,'') from t1;

drop table t1;

select repeat('+',3) as h union select NULL;

select ifnull(NULL, 'string');

#

# Bug#9509 Optimizer: wrong result after AND with comparisons

#

create table t1 (s1 char(5) collate utf8_lithuanian_ci);

insert into t1 values ('I'),('K'),('Y');

select * from t1 where s1 < 'K' and s1 = 'Y';

select * from t1 where 'K' > s1 and s1 = 'Y';

drop table t1;

create table t1 (s1 char(5) collate utf8_czech_ci);

insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i');

select * from t1 where s1 > 'd' and s1 = 'CH';

select * from t1 where 'd' < s1 and s1 = 'CH';

select * from t1 where s1 = 'cH' and s1 <> 'ch';

select * from t1 where 'cH' = s1 and s1 <> 'ch';

drop table t1;

#

# Bug#10714: Inserting double value into utf8 column crashes server

#

create table t1 (a varchar(255));

insert into t1 values (1.0);

drop table t1;

#

# Bug#10253 compound index length and utf8 char set

# produces invalid query results

#

create table t1 (

 id int not null,

 city varchar(20) not null,

 key (city(7),id)

);

insert into t1 values (1,'Durban North');

insert into t1 values (2,'Durban');

select * from t1 where city = 'Durban';

select * from t1 where city = 'Durban ';

drop table t1;

#

#

# Test for bug #11167: join for utf8 varchar value longer than 255 bytes 

#

CREATE TEMPORARY TABLE t1 (

  `id` int NOT NULL auto_increment,

  `country` varchar(100) NOT NULL default '',

  `shortcode` varchar(100) NOT NULL default '',

  `operator` varchar(100) NOT NULL default '',

  `momid` varchar(30) NOT NULL default '',

  `keyword` varchar(160) NOT NULL default '',

  `content` varchar(160) NOT NULL default '',

  `second_token` varchar(160) default NULL,

  `gateway_id` int NOT NULL default '0',

  `created` datetime NULL,

  `msisdn` varchar(15) NOT NULL default '',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `MSCCSPK_20030521130957121` (`momid`),

  KEY `IX_mobile_originated_message_keyword` (`keyword`),

  KEY `IX_mobile_originated_message_created` (`created`),

  KEY `IX_mobile_originated_message_support` (`msisdn`,`momid`,`keyword`,`gateway_id`,`created`)

) ENGINE=MyISAM;

INSERT INTO t1 VALUES 

(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱','G', 3,'2005-06-01 17:30:43','1234567890'),

(2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890');

--disable_warnings

CREATE TABLE t2 (

  `msisdn` varchar(15) NOT NULL default '',

  `operator_id` int NOT NULL default '0',

  `created` datetime NULL,

  UNIQUE KEY `PK_user` (`msisdn`)

) ENGINE=InnoDB;

--enable_warnings

INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25');

SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890';

DROP TABLE t1,t2;

#

# Bug#11591: CHAR column with utf8 does not work properly

# (more chars than expected)

#

create table t1 (a char(20));

insert into t1 values ('12'),('킱킲');

alter table t1 modify a char(2);

select char_length(a), length(a), a from t1 order by a;

drop table t1;

#

# Bugs#12611

# ESCAPE + LIKE do not work when the escape char is a multibyte one

#

select 'andre%' like 'andre킱%' escape '킱';

#

# Bugs#11754: SET NAMES utf8 followed by SELECT "A\\" LIKE "A\\" returns 0

#

select 'a\\' like 'a\\';

select 'aa\\' like 'a%\\';

create table t1 (a char(10), key(a));

insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");

select * from t1 where a like "abc%";

select * from t1 where a like concat("abc","%");

select * from t1 where a like "ABC%";

select * from t1 where a like "test%";

select * from t1 where a like "te_t";

select * from t1 where a like "%a%";

select * from t1 where a like "%abcd%";

select * from t1 where a like "%abc\d%";

drop table t1;