~linuxjedi/drizzle/trunk-bug-667053

drop table if exists t1;
SET NAMES latin1;
CREATE TABLE t1 (a char(1) character set latin1);
INSERT INTO t1 VALUES (0x00),(0x01),(0x02),(0x03),(0x04),(0x05),(0x06),(0x07);
INSERT INTO t1 VALUES (0x08),(0x09),(0x0A),(0x0B),(0x0C),(0x0D),(0x0E),(0x0F);
INSERT INTO t1 VALUES (0x10),(0x11),(0x12),(0x13),(0x14),(0x15),(0x16),(0x17);
INSERT INTO t1 VALUES (0x18),(0x19),(0x1A),(0x1B),(0x1C),(0x1D),(0x1E),(0x1F);
INSERT INTO t1 VALUES (0x20),(0x21),(0x22),(0x23),(0x24),(0x25),(0x26),(0x27);
INSERT INTO t1 VALUES (0x28),(0x29),(0x2A),(0x2B),(0x2C),(0x2D),(0x2E),(0x2F);
INSERT INTO t1 VALUES (0x30),(0x31),(0x32),(0x33),(0x34),(0x35),(0x36),(0x37);
INSERT INTO t1 VALUES (0x38),(0x39),(0x3A),(0x3B),(0x3C),(0x3D),(0x3E),(0x3F);
INSERT INTO t1 VALUES (0x40),(0x41),(0x42),(0x43),(0x44),(0x45),(0x46),(0x47);
INSERT INTO t1 VALUES (0x48),(0x49),(0x4A),(0x4B),(0x4C),(0x4D),(0x4E),(0x4F);
INSERT INTO t1 VALUES (0x50),(0x51),(0x52),(0x53),(0x54),(0x55),(0x56),(0x57);
INSERT INTO t1 VALUES (0x58),(0x59),(0x5A),(0x5B),(0x5C),(0x5D),(0x5E),(0x5F);
INSERT INTO t1 VALUES (0x60),(0x61),(0x62),(0x63),(0x64),(0x65),(0x66),(0x67);
INSERT INTO t1 VALUES (0x68),(0x69),(0x6A),(0x6B),(0x6C),(0x6D),(0x6E),(0x6F);
INSERT INTO t1 VALUES (0x70),(0x71),(0x72),(0x73),(0x74),(0x75),(0x76),(0x77);
INSERT INTO t1 VALUES (0x78),(0x79),(0x7A),(0x7B),(0x7C),(0x7D),(0x7E),(0x7F);
INSERT INTO t1 VALUES (0x80),(0x81),(0x82),(0x83),(0x84),(0x85),(0x86),(0x87);
INSERT INTO t1 VALUES (0x88),(0x89),(0x8A),(0x8B),(0x8C),(0x8D),(0x8E),(0x8F);
INSERT INTO t1 VALUES (0x90),(0x91),(0x92),(0x93),(0x94),(0x95),(0x96),(0x97);
INSERT INTO t1 VALUES (0x98),(0x99),(0x9A),(0x9B),(0x9C),(0x9D),(0x9E),(0x9F);
INSERT INTO t1 VALUES (0xA0),(0xA1),(0xA2),(0xA3),(0xA4),(0xA5),(0xA6),(0xA7);
INSERT INTO t1 VALUES (0xA8),(0xA9),(0xAA),(0xAB),(0xAC),(0xAD),(0xAE),(0xAF);
INSERT INTO t1 VALUES (0xB0),(0xB1),(0xB2),(0xB3),(0xB4),(0xB5),(0xB6),(0xB7);
INSERT INTO t1 VALUES (0xB8),(0xB9),(0xBA),(0xBB),(0xBC),(0xBD),(0xBE),(0xBF);
INSERT INTO t1 VALUES (0xC0),(0xC1),(0xC2),(0xC3),(0xC4),(0xC5),(0xC6),(0xC7);
INSERT INTO t1 VALUES (0xC8),(0xC9),(0xCA),(0xCB),(0xCC),(0xCD),(0xCE),(0xCF);
INSERT INTO t1 VALUES (0xD0),(0xD1),(0xD2),(0xD3),(0xD4),(0xD5),(0xD6),(0xD7);
INSERT INTO t1 VALUES (0xD8),(0xD9),(0xDA),(0xDB),(0xDC),(0xDD),(0xDE),(0xDF);
INSERT INTO t1 VALUES (0xE0),(0xE1),(0xE2),(0xE3),(0xE4),(0xE5),(0xE6),(0xE7);
INSERT INTO t1 VALUES (0xE8),(0xE9),(0xEA),(0xEB),(0xEC),(0xED),(0xEE),(0xEF);
INSERT INTO t1 VALUES (0xF0),(0xF1),(0xF2),(0xF3),(0xF4),(0xF5),(0xF6),(0xF7);
INSERT INTO t1 VALUES (0xF8),(0xF9),(0xFA),(0xFB),(0xFC),(0xFD),(0xFE),(0xFF);
SELECT 
hex(a), 
hex(@u:=convert(a using utf8)),
hex(@l:=convert(@u using latin1)),
a=@l FROM t1;
hex(a)	hex(@u:=convert(a using utf8))	hex(@l:=convert(@u using latin1))	a=@l
00	00	00	1
01	01	01	1
02	02	02	1
03	03	03	1
04	04	04	1
05	05	05	1
06	06	06	1
07	07	07	1
08	08	08	1
09	09	09	1
0A	0A	0A	1
0B	0B	0B	1
0C	0C	0C	1
0D	0D	0D	1
0E	0E	0E	1
0F	0F	0F	1
10	10	10	1
11	11	11	1
12	12	12	1
13	13	13	1
14	14	14	1
15	15	15	1
16	16	16	1
17	17	17	1
18	18	18	1
19	19	19	1
1A	1A	1A	1
1B	1B	1B	1
1C	1C	1C	1
1D	1D	1D	1
1E	1E	1E	1
1F	1F	1F	1
			1
21	21	21	1
22	22	22	1
23	23	23	1
24	24	24	1
25	25	25	1
26	26	26	1
27	27	27	1
28	28	28	1
29	29	29	1
2A	2A	2A	1
2B	2B	2B	1
2C	2C	2C	1
2D	2D	2D	1
2E	2E	2E	1
2F	2F	2F	1
30	30	30	1
31	31	31	1
32	32	32	1
33	33	33	1
34	34	34	1
35	35	35	1
36	36	36	1
37	37	37	1
38	38	38	1
39	39	39	1
3A	3A	3A	1
3B	3B	3B	1
3C	3C	3C	1
3D	3D	3D	1
3E	3E	3E	1
3F	3F	3F	1
40	40	40	1
41	41	41	1
42	42	42	1
43	43	43	1
44	44	44	1
45	45	45	1
46	46	46	1
47	47	47	1
48	48	48	1
49	49	49	1
4A	4A	4A	1
4B	4B	4B	1
4C	4C	4C	1
4D	4D	4D	1
4E	4E	4E	1
4F	4F	4F	1
50	50	50	1
51	51	51	1
52	52	52	1
53	53	53	1
54	54	54	1
55	55	55	1
56	56	56	1
57	57	57	1
58	58	58	1
59	59	59	1
5A	5A	5A	1
5B	5B	5B	1
5C	5C	5C	1
5D	5D	5D	1
5E	5E	5E	1
5F	5F	5F	1
60	60	60	1
61	61	61	1
62	62	62	1
63	63	63	1
64	64	64	1
65	65	65	1
66	66	66	1
67	67	67	1
68	68	68	1
69	69	69	1
6A	6A	6A	1
6B	6B	6B	1
6C	6C	6C	1
6D	6D	6D	1
6E	6E	6E	1
6F	6F	6F	1
70	70	70	1
71	71	71	1
72	72	72	1
73	73	73	1
74	74	74	1
75	75	75	1
76	76	76	1
77	77	77	1
78	78	78	1
79	79	79	1
7A	7A	7A	1
7B	7B	7B	1
7C	7C	7C	1
7D	7D	7D	1
7E	7E	7E	1
7F	7F	7F	1
80	E282AC	80	1
81	C281	81	1
82	E2809A	82	1
83	C692	83	1
84	E2809E	84	1
85	E280A6	85	1
86	E280A0	86	1
87	E280A1	87	1
88	CB86	88	1
89	E280B0	89	1
8A	C5A0	8A	1
8B	E280B9	8B	1
8C	C592	8C	1
8D	C28D	8D	1
8E	C5BD	8E	1
8F	C28F	8F	1
90	C290	90	1
91	E28098	91	1
92	E28099	92	1
93	E2809C	93	1
94	E2809D	94	1
95	E280A2	95	1
96	E28093	96	1
97	E28094	97	1
98	CB9C	98	1
99	E284A2	99	1
9A	C5A1	9A	1
9B	E280BA	9B	1
9C	C593	9C	1
9D	C29D	9D	1
9E	C5BE	9E	1
9F	C5B8	9F	1
A0	C2A0	A0	1
A1	C2A1	A1	1
A2	C2A2	A2	1
A3	C2A3	A3	1
A4	C2A4	A4	1
A5	C2A5	A5	1
A6	C2A6	A6	1
A7	C2A7	A7	1
A8	C2A8	A8	1
A9	C2A9	A9	1
AA	C2AA	AA	1
AB	C2AB	AB	1
AC	C2AC	AC	1
AD	C2AD	AD	1
AE	C2AE	AE	1
AF	C2AF	AF	1
B0	C2B0	B0	1
B1	C2B1	B1	1
B2	C2B2	B2	1
B3	C2B3	B3	1
B4	C2B4	B4	1
B5	C2B5	B5	1
B6	C2B6	B6	1
B7	C2B7	B7	1
B8	C2B8	B8	1
B9	C2B9	B9	1
BA	C2BA	BA	1
BB	C2BB	BB	1
BC	C2BC	BC	1
BD	C2BD	BD	1
BE	C2BE	BE	1
BF	C2BF	BF	1
C0	C380	C0	1
C1	C381	C1	1
C2	C382	C2	1
C3	C383	C3	1
C4	C384	C4	1
C5	C385	C5	1
C6	C386	C6	1
C7	C387	C7	1
C8	C388	C8	1
C9	C389	C9	1
CA	C38A	CA	1
CB	C38B	CB	1
CC	C38C	CC	1
CD	C38D	CD	1
CE	C38E	CE	1
CF	C38F	CF	1
D0	C390	D0	1
D1	C391	D1	1
D2	C392	D2	1
D3	C393	D3	1
D4	C394	D4	1
D5	C395	D5	1
D6	C396	D6	1
D7	C397	D7	1
D8	C398	D8	1
D9	C399	D9	1
DA	C39A	DA	1
DB	C39B	DB	1
DC	C39C	DC	1
DD	C39D	DD	1
DE	C39E	DE	1
DF	C39F	DF	1
E0	C3A0	E0	1
E1	C3A1	E1	1
E2	C3A2	E2	1
E3	C3A3	E3	1
E4	C3A4	E4	1
E5	C3A5	E5	1
E6	C3A6	E6	1
E7	C3A7	E7	1
E8	C3A8	E8	1
E9	C3A9	E9	1
EA	C3AA	EA	1
EB	C3AB	EB	1
EC	C3AC	EC	1
ED	C3AD	ED	1
EE	C3AE	EE	1
EF	C3AF	EF	1
F0	C3B0	F0	1
F1	C3B1	F1	1
F2	C3B2	F2	1
F3	C3B3	F3	1
F4	C3B4	F4	1
F5	C3B5	F5	1
F6	C3B6	F6	1
F7	C3B7	F7	1
F8	C3B8	F8	1
F9	C3B9	F9	1
FA	C3BA	FA	1
FB	C3BB	FB	1
FC	C3BC	FC	1
FD	C3BD	FD	1
FE	C3BE	FE	1
FF	C3BF	FF	1
DROP TABLE t1;
SELECT 1 as ƒ, 2 as Š, 3 as Œ, 4 as Ž, 5 as š, 6 as œ, 7 as ž, 8 as Ÿ;
ƒ	Š	Œ	Ž	š	œ	ž	Ÿ
1	2	3	4	5	6	7	8
select 'a' regexp 'A' collate latin1_general_ci;
'a' regexp 'A' collate latin1_general_ci
1
select 'a' regexp 'A' collate latin1_general_cs;
'a' regexp 'A' collate latin1_general_cs
0
select 'a' regexp 'A' collate latin1_bin;
'a' regexp 'A' collate latin1_bin
0
SET collation_connection='latin1_swedish_ci';
create table t1 select repeat('a',4000) a;
delete from t1;
insert into t1 values ('a'), ('a '), ('a\t');
select collation(a),hex(a) from t1 order by a;
collation(a)	hex(a)
latin1_swedish_ci	6109
latin1_swedish_ci	61
latin1_swedish_ci	6120
drop table t1;
select @@collation_connection;
@@collation_connection
latin1_swedish_ci
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
insert into t1 values('abcdef');
insert into t1 values('_bcdef');
insert into t1 values('a_cdef');
insert into t1 values('ab_def');
insert into t1 values('abc_ef');
insert into t1 values('abcd_f');
insert into t1 values('abcde_');
select c1 as c1u from t1 where c1 like 'ab\_def';
c1u
ab_def
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
c2h
ab_def
drop table t1;
SET collation_connection='latin1_bin';
create table t1 select repeat('a',4000) a;
delete from t1;
insert into t1 values ('a'), ('a '), ('a\t');
select collation(a),hex(a) from t1 order by a;
collation(a)	hex(a)
latin1_bin	6109
latin1_bin	61
latin1_bin	6120
drop table t1;
select @@collation_connection;
@@collation_connection
latin1_bin
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
insert into t1 values('abcdef');
insert into t1 values('_bcdef');
insert into t1 values('a_cdef');
insert into t1 values('ab_def');
insert into t1 values('abc_ef');
insert into t1 values('abcd_f');
insert into t1 values('abcde_');
select c1 as c1u from t1 where c1 like 'ab\_def';
c1u
ab_def
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
c2h
ab_def
drop table t1;
CREATE TABLE „a (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '„a (a int)' at line 1
SELECT '„a' as str;
str
„a
set @str= _latin1 'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc';
SELECT convert(@str collate latin1_bin using utf8);
convert(@str collate latin1_bin using utf8)
ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
SELECT convert(@str collate latin1_general_ci using utf8);
convert(@str collate latin1_general_ci using utf8)
ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
SELECT convert(@str collate latin1_german1_ci using utf8);
convert(@str collate latin1_german1_ci using utf8)
ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
SELECT convert(@str collate latin1_danish_ci using utf8);
convert(@str collate latin1_danish_ci using utf8)
ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
SELECT convert(@str collate latin1_spanish_ci using utf8);
convert(@str collate latin1_spanish_ci using utf8)
ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
SELECT convert(@str collate latin1_german2_ci using utf8);
convert(@str collate latin1_german2_ci using utf8)
ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
SELECT convert(@str collate latin1_swedish_ci using utf8);
convert(@str collate latin1_swedish_ci using utf8)
ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
SET NAMES latin1;
DROP TABLE IF EXISTS `abcÿdef`;
CREATE TABLE `abcÿdef` (i int);
INSERT INTO `abcÿdef` VALUES (1);
INSERT INTO abcÿdef VALUES (2);
SELECT * FROM `abcÿdef`;
i
1
2
SELECT * FROM abcÿdef;
i
1
2
DROP TABLE `abcÿdef`;
select hex(cast(_ascii 0x7f as char(1) character set latin1));
hex(cast(_ascii 0x7f as char(1) character set latin1))
7F
End of 5.0 tests
End of 5.1 tests
set @@collation_connection=latin1_swedish_ci;
select @@collation_connection;
@@collation_connection
latin1_swedish_ci
select hex(weight_string('a'));
hex(weight_string('a'))
41
select hex(weight_string('A'));
hex(weight_string('A'))
41
select hex(weight_string('abc'));
hex(weight_string('abc'))
414243
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
4142
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
414243
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
4142432020
select @@collation_connection;
@@collation_connection
latin1_swedish_ci
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
41
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
41
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
414243
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
4142
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
414243
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
4142432020
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
2020434241
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
BEBDBCDFDF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
DFDFBCBDBE
set @@collation_connection=latin1_bin;
select @@collation_connection;
@@collation_connection
latin1_bin
select hex(weight_string('a'));
hex(weight_string('a'))
61
select hex(weight_string('A'));
hex(weight_string('A'))
41
select hex(weight_string('abc'));
hex(weight_string('abc'))
616263
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
6162
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
616263
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
6162632020
select @@collation_connection;
@@collation_connection
latin1_bin
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
61
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
41
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
616263
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
6162
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
616263
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
6162632020
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
2020636261
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
9E9D9CDFDF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
DFDF9C9D9E
set @@collation_connection=latin1_general_cs;
select @@collation_connection;
@@collation_connection
latin1_general_cs
select hex(weight_string('a'));
hex(weight_string('a'))
42
select hex(weight_string('A'));
hex(weight_string('A'))
41
select hex(weight_string('abc'));
hex(weight_string('abc'))
425254
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
4252
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
425254
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
4252542020
select @@collation_connection;
@@collation_connection
latin1_general_cs
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
42
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
41
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
425254
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
4252
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
425254
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
4252542020
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
2020545242
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
BDADABDFDF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
DFDFABADBD
set @@collation_connection=binary;
select @@collation_connection;
@@collation_connection
binary
select hex(weight_string('a'));
hex(weight_string('a'))
61
select hex(weight_string('A'));
hex(weight_string('A'))
41
select hex(weight_string('abc'));
hex(weight_string('abc'))
616263
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
6162
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
616263
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
6162630000
select @@collation_connection;
@@collation_connection
binary
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
61
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
41
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
616263
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
6162
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
616263
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
6162630000
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
0000636261
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
9E9D9CFFFF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
FFFF9C9D9E
set names latin1;