~drizzle-trunk/drizzle/development : contents of tests/include/mix2

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

#
# Tests from mix2.inc which require ucs2 character sets should go here
#

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

eval 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 = $engine_type;
eval 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 = $engine_type;
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;

eval 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 = $engine_type;
eval 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 = $engine_type;
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;

eval 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 = $engine_type;
eval 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 = $engine_type;
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;

eval 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 = $engine_type;
eval 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 = $engine_type;
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
#
eval CREATE TABLE t1 (
  ind enum('0','1','2') NOT NULL default '0',
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=$engine_type DEFAULT CHARSET=utf8;
eval CREATE TABLE t2 (
  ind enum('0','1','2') NOT NULL default '0',
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=$engine_type 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;

eval CREATE TABLE t1 (
  ind set('0','1','2') NOT NULL default '0',
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=$engine_type DEFAULT CHARSET=utf8;
eval CREATE TABLE t2 (
  ind set('0','1','2') NOT NULL default '0',
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=$engine_type 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;

eval CREATE TABLE t1 (
  ind bit not null,
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=$engine_type DEFAULT CHARSET=utf8;
eval CREATE TABLE t2 (
  ind bit not null,
  string1 varchar(250) NOT NULL,
  PRIMARY KEY  (ind)
) ENGINE=$engine_type 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..'

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

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;
eval create table t2 (
  a int, b char(10), filler char(10), primary key(a, b(2))
) character set ucs2 engine = $engine_type;

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;

eval create table t1(a int not null, b char(110),primary key(a,b(100))) engine=$engine_type 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;
eval create table t1(a int not null, b text(110),primary key(a,b(100))) engine=$engine_type 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;


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