~drizzle-trunk/drizzle/development : contents of mysql-test/t/innodb-ucs2.test at revision 27

~drizzle-trunk/drizzle/development : (revision 27)

-- source include/have_innodb.inc
-- source include/have_ucs2.inc

--disable_warnings
drop table if exists t1, t2;
--enable_warnings

#
# BUG 14056 Column prefix index on UTF-8 primary key column causes: Can't find record..
#

create table t1 (
  a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
) character set utf8 engine = innodb;
create table t2 (
  a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
) character set ucs2 engine = innodb;
insert into t1 values (1,'abcdefg','abcdefg','one');
insert into t1 values (2,'ijkilmn','ijkilmn','two');
insert into t1 values (3,'qrstuvw','qrstuvw','three');
insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
insert into t2 values (1,'abcdefg','abcdefg','one');
insert into t2 values (2,'ijkilmn','ijkilmn','two');
insert into t2 values (3,'qrstuvw','qrstuvw','three');
insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
update t1 set filler = 'boo' where a = 1;
update t2 set filler ='email' where a = 4;
select a,hex(b),hex(c),filler from t1 order by filler;
select a,hex(b),hex(c),filler from t2 order by filler;
drop table t1;
drop table t2;

create table t1 (
  a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2))
) character set utf8 engine = innodb;
create table t2 (
  a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2))
) character set ucs2 engine = innodb;
insert into t1 values (1,'abcdefg','abcdefg','one');
insert into t1 values (2,'ijkilmn','ijkilmn','two');
insert into t1 values (3,'qrstuvw','qrstuvw','three');
insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
insert into t2 values (1,'abcdefg','abcdefg','one');
insert into t2 values (2,'ijkilmn','ijkilmn','two');
insert into t2 values (3,'qrstuvw','qrstuvw','three');
insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
update t1 set filler = 'boo' where a = 1;
update t2 set filler ='email' where a = 4;
select a,hex(b),hex(c),filler from t1 order by filler;
select a,hex(b),hex(c),filler from t2 order by filler;
drop table t1;
drop table t2;

create table t1 (
  a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2))
) character set utf8 engine = innodb;
create table t2 (
  a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2))
) character set ucs2 engine = innodb;
insert into t1 values (1,'abcdefg','abcdefg','one');
insert into t1 values (2,'ijkilmn','ijkilmn','two');
insert into t1 values (3,'qrstuvw','qrstuvw','three');
insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
insert into t2 values (1,'abcdefg','abcdefg','one');
insert into t2 values (2,'ijkilmn','ijkilmn','two');
insert into t2 values (3,'qrstuvw','qrstuvw','three');
insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
update t1 set filler = 'boo' where a = 1;
update t2 set filler ='email' where a = 4;
select a,hex(b),hex(c),filler from t1 order by filler;
select a,hex(b),hex(c),filler from t2 order by filler;
drop table t1;
drop table t2;

create table t1 (
  a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2))
) character set utf8 engine = innodb;
create table t2 (
  a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2))
) character set ucs2 engine = innodb;
insert into t1 values (1,'abcdefg','abcdefg','one');
insert into t1 values (2,'ijkilmn','ijkilmn','two');
insert into t1 values (3,'qrstuvw','qrstuvw','three');
insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
insert into t2 values (1,'abcdefg','abcdefg','one');
insert into t2 values (2,'ijkilmn','ijkilmn','two');
insert into t2 values (3,'qrstuvw','qrstuvw','three');
insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
update t1 set filler = 'boo' where a = 1;
update t2 set filler ='email' where a = 4;
select a,hex(b),hex(c),filler from t1 order by filler;
select a,hex(b),hex(c),filler from t2 order by filler;
drop table t1;
drop table t2;
commit;

#
# Test cases for bug #15308 Problem of Order with Enum Column in Primary Key
#
CREATE TABLE t1 (
  ind enum('0','1','2') NOT NULL default '0',
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE t2 (
  ind enum('0','1','2') NOT NULL default '0',
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

INSERT INTO t1 VALUES ('1', ''),('2', '');
INSERT INTO t2 VALUES ('1', ''),('2', '');
SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1;
SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1;
drop table t1,t2;

CREATE TABLE t1 (
  ind set('0','1','2') NOT NULL default '0',
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE t2 (
  ind set('0','1','2') NOT NULL default '0',
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

INSERT INTO t1 VALUES ('1', ''),('2', '');
INSERT INTO t2 VALUES ('1', ''),('2', '');
SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1;
SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1;
drop table t1,t2;

CREATE TABLE t1 (
  ind bit not null,
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE t2 (
  ind bit not null,
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;
insert into t1 values(0,''),(1,'');
insert into t2 values(0,''),(1,'');
select hex(ind),hex(string1) from t1 order by string1;
select hex(ind),hex(string1) from t2 order by string1;
drop table t1,t2;

# tests for bug #14056 Column prefix index on UTF-8 primary key column causes 'Can't find record..'

create table t2 (
  a int, b char(10), filler char(10), primary key(a, b(2)) 
) character set utf8 engine = innodb;

insert into t2 values (1,'abcdefg','one');
insert into t2 values (2,'ijkilmn','two');
insert into t2 values (3, 'qrstuvw','three');
update t2 set a=5, filler='booo' where a=1;
drop table t2;
create table t2 (
  a int, b char(10), filler char(10), primary key(a, b(2)) 
) character set ucs2 engine = innodb;

insert into t2 values (1,'abcdefg','one');
insert into t2 values (2,'ijkilmn','two');
insert into t2 values (3, 'qrstuvw','three');
update t2 set a=5, filler='booo' where a=1;
drop table t2;

create table t1(a int not null, b char(110),primary key(a,b(100))) engine=innodb default charset=utf8;
insert into t1 values(1,'abcdefg'),(2,'defghijk');
insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1);
insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2);
select a,hex(b) from t1 order by b;
update t1 set b = 'three' where a = 6;
drop table t1;
create table t1(a int not null, b text(110),primary key(a,b(100))) engine=innodb default charset=utf8;
insert into t1 values(1,'abcdefg'),(2,'defghijk');
insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1);
insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2);
select a,hex(b) from t1 order by b;
update t1 set b = 'three' where a = 6;
drop table t1;

--echo End of 5.0 tests

1 by brian clean slate	1	-- source include/have_innodb.inc
	2	-- source include/have_ucs2.inc
	3
	4	--disable_warnings
	5	drop table if exists t1, t2;
	6	--enable_warnings
	7
	8	#
	9	# BUG 14056 Column prefix index on UTF-8 primary key column causes: Can't find record..
	10	#
	11
	12	create table t1 (
	13	a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
	14	) character set utf8 engine = innodb;
	15	create table t2 (
	16	a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
	17	) character set ucs2 engine = innodb;
	18	insert into t1 values (1,'abcdefg','abcdefg','one');
	19	insert into t1 values (2,'ijkilmn','ijkilmn','two');
	20	insert into t1 values (3,'qrstuvw','qrstuvw','three');
	21	insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
	22	insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
	23	insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
	24	insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
	25	insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
	26	insert into t2 values (1,'abcdefg','abcdefg','one');
	27	insert into t2 values (2,'ijkilmn','ijkilmn','two');
	28	insert into t2 values (3,'qrstuvw','qrstuvw','three');
	29	insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
	30	insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
	31	insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
	32	insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
	33	insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
	34	insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
	35	insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
	36	insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
	37	insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
	38	update t1 set filler = 'boo' where a = 1;
	39	update t2 set filler ='email' where a = 4;
	40	select a,hex(b),hex(c),filler from t1 order by filler;
	41	select a,hex(b),hex(c),filler from t2 order by filler;
	42	drop table t1;
	43	drop table t2;
	44
	45	create table t1 (
	46	a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2))
	47	) character set utf8 engine = innodb;
	48	create table t2 (
	49	a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2))
	50	) character set ucs2 engine = innodb;
	51	insert into t1 values (1,'abcdefg','abcdefg','one');
	52	insert into t1 values (2,'ijkilmn','ijkilmn','two');
	53	insert into t1 values (3,'qrstuvw','qrstuvw','three');
	54	insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
	55	insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
	56	insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
	57	insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
	58	insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
	59	insert into t2 values (1,'abcdefg','abcdefg','one');
	60	insert into t2 values (2,'ijkilmn','ijkilmn','two');
	61	insert into t2 values (3,'qrstuvw','qrstuvw','three');
	62	insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
	63	insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
	64	insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
65	insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
66	insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
67	insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
68	insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
69	insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
70	insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
71	update t1 set filler = 'boo' where a = 1;
72	update t2 set filler ='email' where a = 4;
73	select a,hex(b),hex(c),filler from t1 order by filler;
74	select a,hex(b),hex(c),filler from t2 order by filler;
75	drop table t1;
76	drop table t2;
77
78	create table t1 (
79	a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2))
80	) character set utf8 engine = innodb;
81	create table t2 (
82	a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2))
83	) character set ucs2 engine = innodb;
84	insert into t1 values (1,'abcdefg','abcdefg','one');
85	insert into t1 values (2,'ijkilmn','ijkilmn','two');
86	insert into t1 values (3,'qrstuvw','qrstuvw','three');
87	insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
88	insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
89	insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
90	insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
91	insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
92	insert into t2 values (1,'abcdefg','abcdefg','one');
93	insert into t2 values (2,'ijkilmn','ijkilmn','two');
94	insert into t2 values (3,'qrstuvw','qrstuvw','three');
95	insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
96	insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
97	insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
98	insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
99	insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
100	insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
101	insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
102	insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
103	insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
104	update t1 set filler = 'boo' where a = 1;
105	update t2 set filler ='email' where a = 4;
106	select a,hex(b),hex(c),filler from t1 order by filler;
107	select a,hex(b),hex(c),filler from t2 order by filler;
108	drop table t1;
109	drop table t2;
110
111	create table t1 (
112	a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2))
113	) character set utf8 engine = innodb;
114	create table t2 (
115	a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2))
116	) character set ucs2 engine = innodb;
117	insert into t1 values (1,'abcdefg','abcdefg','one');
118	insert into t1 values (2,'ijkilmn','ijkilmn','two');
119	insert into t1 values (3,'qrstuvw','qrstuvw','three');
120	insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
121	insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
122	insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
123	insert into t2 values (1,'abcdefg','abcdefg','one');
124	insert into t2 values (2,'ijkilmn','ijkilmn','two');
125	insert into t2 values (3,'qrstuvw','qrstuvw','three');
126	insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
127	insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
128	insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
129	insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
130	insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
131	insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
132	insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
133	update t1 set filler = 'boo' where a = 1;
134	update t2 set filler ='email' where a = 4;
135	select a,hex(b),hex(c),filler from t1 order by filler;
136	select a,hex(b),hex(c),filler from t2 order by filler;
137	drop table t1;
138	drop table t2;
139	commit;
140
141	#
142	# Test cases for bug #15308 Problem of Order with Enum Column in Primary Key
143	#
144	CREATE TABLE t1 (
145	ind enum('0','1','2') NOT NULL default '0',
146	string1 varchar(250) NOT NULL,
147	PRIMARY KEY (ind)
148	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
149	CREATE TABLE t2 (
150	ind enum('0','1','2') NOT NULL default '0',
151	string1 varchar(250) NOT NULL,
152	PRIMARY KEY (ind)
153	) ENGINE=InnoDB DEFAULT CHARSET=ucs2;
154
155	INSERT INTO t1 VALUES ('1', ''),('2', '');
156	INSERT INTO t2 VALUES ('1', ''),('2', '');
157	SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1;
158	SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1;
159	drop table t1,t2;
160
161	CREATE TABLE t1 (
162	ind set('0','1','2') NOT NULL default '0',
163	string1 varchar(250) NOT NULL,
164	PRIMARY KEY (ind)
165	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
166	CREATE TABLE t2 (
167	ind set('0','1','2') NOT NULL default '0',
168	string1 varchar(250) NOT NULL,
169	PRIMARY KEY (ind)
170	) ENGINE=InnoDB DEFAULT CHARSET=ucs2;
171
172	INSERT INTO t1 VALUES ('1', ''),('2', '');
173	INSERT INTO t2 VALUES ('1', ''),('2', '');
174	SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1;
175	SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1;
176	drop table t1,t2;
177
178	CREATE TABLE t1 (
179	ind bit not null,
180	string1 varchar(250) NOT NULL,
181	PRIMARY KEY (ind)
182	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
183	CREATE TABLE t2 (
184	ind bit not null,
185	string1 varchar(250) NOT NULL,
186	PRIMARY KEY (ind)
187	) ENGINE=InnoDB DEFAULT CHARSET=ucs2;
188	insert into t1 values(0,''),(1,'');
189	insert into t2 values(0,''),(1,'');
190	select hex(ind),hex(string1) from t1 order by string1;
191	select hex(ind),hex(string1) from t2 order by string1;
192	drop table t1,t2;
193
194	# tests for bug #14056 Column prefix index on UTF-8 primary key column causes 'Can't find record..'
195
196	create table t2 (
197	a int, b char(10), filler char(10), primary key(a, b(2))
198	) character set utf8 engine = innodb;
199
200	insert into t2 values (1,'abcdefg','one');
201	insert into t2 values (2,'ijkilmn','two');
202	insert into t2 values (3, 'qrstuvw','three');
203	update t2 set a=5, filler='booo' where a=1;
204	drop table t2;
205	create table t2 (
206	a int, b char(10), filler char(10), primary key(a, b(2))
207	) character set ucs2 engine = innodb;
208
209	insert into t2 values (1,'abcdefg','one');
210	insert into t2 values (2,'ijkilmn','two');
211	insert into t2 values (3, 'qrstuvw','three');
212	update t2 set a=5, filler='booo' where a=1;
213	drop table t2;
214
215	create table t1(a int not null, b char(110),primary key(a,b(100))) engine=innodb default charset=utf8;
216	insert into t1 values(1,'abcdefg'),(2,'defghijk');
217	insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1);
218	insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2);
219	select a,hex(b) from t1 order by b;
220	update t1 set b = 'three' where a = 6;
221	drop table t1;
222	create table t1(a int not null, b text(110),primary key(a,b(100))) engine=innodb default charset=utf8;
223	insert into t1 values(1,'abcdefg'),(2,'defghijk');
224	insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1);
225	insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2);
226	select a,hex(b) from t1 order by b;
227	update t1 set b = 'three' where a = 6;
228	drop table t1;
229
230	--echo End of 5.0 tests