drop table if exists t1;
set names latin2;
select 'A' = 'a' collate latin2_czech_cs;
'A' = 'a' collate latin2_czech_cs
0
create table t1 (
id  int(5) not null,    
tt  char(255) not null
) character set latin2 collate latin2_czech_cs;
insert into t1 values (1,'Aa');
insert into t1 values (2,'Aas');
alter table t1 add primary key aaa(tt);
Warnings:
Warning	1280	Name 'aaa' ignored for PRIMARY key.
select * from t1 where tt like 'Aa%';
id	tt
1	Aa
2	Aas
select * from t1 ignore index (primary) where tt like 'Aa%';
id	tt
1	Aa
2	Aas
select * from t1 where tt like '%Aa%';
id	tt
1	Aa
2	Aas
select * from t1 where tt like 'AA%';
id	tt
select * from t1 ignore index (primary) where tt like 'AA%';
id	tt
select * from t1 where tt like '%AA%';
id	tt
drop table t1;
set names latin2 collate latin2_czech_cs;
SELECT strcmp('a','a '),   strcmp('a ','a');
strcmp('a','a ')	strcmp('a ','a')
0	0
SELECT strcmp('a\0','a' ), strcmp('a','a\0');
strcmp('a\0','a' )	strcmp('a','a\0')
1	-1
SELECT strcmp('a\0','a '), strcmp('a ','a\0');
strcmp('a\0','a ')	strcmp('a ','a\0')
1	-1
SELECT strcmp('a\t','a' ), strcmp('a', 'a\t');
strcmp('a\t','a' )	strcmp('a', 'a\t')
0	0
SELECT strcmp('a\t','a '), strcmp('a ', 'a\t');
strcmp('a\t','a ')	strcmp('a ', 'a\t')
0	0
#
# MDEV-7149 Constant condition propagation erroneously applied for LIKE
#
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET latin2 COLLATE latin2_czech_cs DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES ('a'),('a ');
SELECT * FROM t1 WHERE CONCAT(c1)='a';
c1
a
a 
SELECT * FROM t1 WHERE CONCAT(c1) LIKE 'a ';
c1
a 
SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a ';
c1
a 
EXPLAIN EXTENDED SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where concat(`test`.`t1`.`c1`) = 'a' and concat(`test`.`t1`.`c1`) like 'a '
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET latin2 COLLATE latin2_czech_cs DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES ('a'),('a ');
SELECT * FROM t1 WHERE 'a'=CONCAT(c1);
c1
a
a 
SELECT * FROM t1 WHERE 'a ' LIKE CONCAT(c1);
c1
a 
SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1);
c1
a 
EXPLAIN EXTENDED SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 'a' = concat(`test`.`t1`.`c1`) and 'a ' like concat(`test`.`t1`.`c1`)
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET latin2 COLLATE latin2_czech_cs DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES ('%'),('% ');
SELECT * FROM t1 WHERE '% '=CONCAT(c1);
c1
%
% 
SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1);
c1
%
SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1);
c1
%
EXPLAIN EXTENDED SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where '% ' = concat(`test`.`t1`.`c1`) and 'a' like concat(`test`.`t1`.`c1`)
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET latin2 COLLATE latin2_czech_cs DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES ('%'),('% ');
SELECT * FROM t1 WHERE '%'=CONCAT(c1);
c1
%
% 
SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1);
c1
%
SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1);
c1
%
EXPLAIN EXTENDED SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where '%' = concat(`test`.`t1`.`c1`) and 'a' like concat(`test`.`t1`.`c1`)
DROP TABLE t1;
#
# MDEV-8694 Wrong result for SELECT..WHERE a NOT LIKE 'a ' AND a='a'
#
CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) CHARACTER SET latin2 COLLATE latin2_czech_cs DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES ('a'),('a ');
SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ';
a	LENGTH(a)
a	1
SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a';
a	LENGTH(a)
a	1
EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a`  not like 'a '
DROP TABLE t1;
#
# End of MDEV-8694
#
create table t1 (
a varchar(2) character set latin2 collate latin2_czech_cs,
primary key(a)
);
insert into t1 set a=0x5ff;
insert into t1 set a=0xff;
select hex(a) from t1;
hex(a)
05FF
FF
drop table t1;
create table t1 (
ch varchar(1),
name varchar(64)
) character set latin2 collate latin2_czech_cs;
insert into t1 values (0x6F,'LATIN SMALL LETTER O');
insert into t1 values (0xF3,'LATIN SMALL LETTER O WITH ACUTE');
insert into t1 values (0xF4,'LATIN SMALL LETTER O WITH CIRCUMFLEX');
insert into t1 values (0xF6,'LATIN SMALL LETTER O WITH DIAERESIS');
insert into t1 values (0xF5,'LATIN SMALL LETTER O WITH DOUBLE ACUTE');
insert into t1 values (0x4F,'LATIN CAPITAL LETTER O');
insert into t1 values (0xD3,'LATIN CAPITAL LETTER O WITH ACUTE');
insert into t1 values (0xD4,'LATIN CAPITAL LETTER O WITH CURCUMFLEX');
insert into t1 values (0xD6,'LATIN CAPITAL LETTER O WITH DIAERESIS');
insert into t1 values (0xD5,'LATIN CAPITAL LETTER O WITH DOUBLE ACUTE');
insert into t1 values (0x75,'LATIN SMALL LETTER U');
insert into t1 values (0xFA,'LATIN SMALL LETTER U WITH ACUTE');
insert into t1 values (0xF9,'LATIN SMALL LETTER U WITH RING ABOVE');
insert into t1 values (0xFC,'LATIN SMALL LETTER U WITH DIAERESIS');
insert into t1 values (0xFB,'LATIN SMALL LETTER U WITH DOUBLE ACUTE');
insert into t1 values (0x55,'LATIN CAPITAL LETTER U');
insert into t1 values (0xDA,'LATIN CAPITAL LETTER U WITH ACUTE');
insert into t1 values (0xD9,'LATIN CAPITAL LETTER U WITH RING ABOVE');
insert into t1 values (0xDC,'LATIN CAPITAL LETTER U WITH DIAERESIS');
insert into t1 values (0xDB,'LATIN CAPITAL LETTER U WITH DOUBLE ACUTE');
select
hex(weight_string(ch level 1)) l1,
hex(weight_string(ch level 2)) l2,
hex(weight_string(ch level 3)) l3,
hex(weight_string(ch level 4)) l4,
name from t1 order by binary l1, binary l2, binary l3, binary l4;
l1	l2	l3	l4	name
1401	2701	4C01	4C00	LATIN SMALL LETTER O
1401	2701	4D01	4D00	LATIN CAPITAL LETTER O
1401	2801	4E01	4E00	LATIN SMALL LETTER O WITH ACUTE
1401	2801	4F01	4F00	LATIN CAPITAL LETTER O WITH ACUTE
1401	2901	5001	5000	LATIN SMALL LETTER O WITH CIRCUMFLEX
1401	2901	5101	5100	LATIN CAPITAL LETTER O WITH CURCUMFLEX
1401	2A01	5201	5200	LATIN SMALL LETTER O WITH DIAERESIS
1401	2A01	5301	5300	LATIN CAPITAL LETTER O WITH DIAERESIS
1401	2B01	5401	5400	LATIN SMALL LETTER O WITH DOUBLE ACUTE
1401	2B01	5501	5500	LATIN CAPITAL LETTER O WITH DOUBLE ACUTE
1C01	3801	6E01	6E00	LATIN SMALL LETTER U
1C01	3801	6F01	6F00	LATIN CAPITAL LETTER U
1C01	3901	7001	7000	LATIN SMALL LETTER U WITH ACUTE
1C01	3901	7101	7100	LATIN CAPITAL LETTER U WITH ACUTE
1C01	3A01	7201	7200	LATIN SMALL LETTER U WITH RING ABOVE
1C01	3A01	7301	7300	LATIN CAPITAL LETTER U WITH RING ABOVE
1C01	3B01	7401	7400	LATIN SMALL LETTER U WITH DIAERESIS
1C01	3B01	7501	7500	LATIN CAPITAL LETTER U WITH DIAERESIS
1C01	3C01	7601	7600	LATIN SMALL LETTER U WITH DOUBLE ACUTE
1C01	3C01	7701	7700	LATIN CAPITAL LETTER U WITH DOUBLE ACUTE
drop table t1;
set names utf8;
create table t1 (
ch varchar(1),
name varchar(64)
) character set latin2 collate latin2_czech_cs;
insert into t1 values (0x4F,'LATIN CAPITAL LETTER O');
insert into t1 values (0xD3,'LATIN CAPITAL LETTER O WITH ACUTE');
insert into t1 values (0xD4,'LATIN CAPITAL LETTER O WITH CURCUMFLEX');
insert into t1 values (0xD6,'LATIN CAPITAL LETTER O WITH DIAERESIS');
insert into t1 values (0xD5,'LATIN CAPITAL LETTER O WITH DOUBLE ACUTE');
insert into t1 values (0x75,'LATIN _SMALL_ LETTER U');
insert into t1 values (0xFA,'LATIN _SMALL_ LETTER U WITH ACUTE');
insert into t1 values (0xF9,'LATIN _SMALL_ LETTER U WITH RING ABOVE');
insert into t1 values (0xFC,'LATIN _SMALL_ LETTER U WITH DIAERESIS');
insert into t1 values (0xFB,'LATIN _SMALL_ LETTER U WITH DOUBLE ACUTE');
select ch,
hex(weight_string(ch level 1)) l1,
hex(weight_string(ch level 2)) l2,
hex(weight_string(ch level 3)) l3,
hex(weight_string(ch level 4)) l4,
name from t1 order by ch;
ch	l1	l2	l3	l4	name
O	1401	2701	4D01	4D00	LATIN CAPITAL LETTER O
Ó	1401	2801	4F01	4F00	LATIN CAPITAL LETTER O WITH ACUTE
Ô	1401	2901	5101	5100	LATIN CAPITAL LETTER O WITH CURCUMFLEX
Ö	1401	2A01	5301	5300	LATIN CAPITAL LETTER O WITH DIAERESIS
Ő	1401	2B01	5501	5500	LATIN CAPITAL LETTER O WITH DOUBLE ACUTE
u	1C01	3801	6E01	6E00	LATIN _SMALL_ LETTER U
ú	1C01	3901	7001	7000	LATIN _SMALL_ LETTER U WITH ACUTE
ů	1C01	3A01	7201	7200	LATIN _SMALL_ LETTER U WITH RING ABOVE
ü	1C01	3B01	7401	7400	LATIN _SMALL_ LETTER U WITH DIAERESIS
ű	1C01	3C01	7601	7600	LATIN _SMALL_ LETTER U WITH DOUBLE ACUTE
alter table t1 modify ch char(1), modify name char(64);
select ch,
hex(weight_string(ch level 1)) l1,
hex(weight_string(ch level 2)) l2,
hex(weight_string(ch level 3)) l3,
hex(weight_string(ch level 4)) l4,
name from t1 order by ch;
ch	l1	l2	l3	l4	name
O	1401	2701	4D01	4D00	LATIN CAPITAL LETTER O
Ó	1401	2801	4F01	4F00	LATIN CAPITAL LETTER O WITH ACUTE
Ô	1401	2901	5101	5100	LATIN CAPITAL LETTER O WITH CURCUMFLEX
Ö	1401	2A01	5301	5300	LATIN CAPITAL LETTER O WITH DIAERESIS
Ő	1401	2B01	5501	5500	LATIN CAPITAL LETTER O WITH DOUBLE ACUTE
u	1C01	3801	6E01	6E00	LATIN _SMALL_ LETTER U
ú	1C01	3901	7001	7000	LATIN _SMALL_ LETTER U WITH ACUTE
ů	1C01	3A01	7201	7200	LATIN _SMALL_ LETTER U WITH RING ABOVE
ü	1C01	3B01	7401	7400	LATIN _SMALL_ LETTER U WITH DIAERESIS
ű	1C01	3C01	7601	7600	LATIN _SMALL_ LETTER U WITH DOUBLE ACUTE
select ch,
hex(weight_string(ch level 1)) l1,
hex(weight_string(ch level 2)) l2,
hex(weight_string(ch level 3)) l3,
hex(weight_string(ch level 4)) l4,
name from t1 order by concat(ch);
ch	l1	l2	l3	l4	name
O	1401	2701	4D01	4D00	LATIN CAPITAL LETTER O
Ó	1401	2801	4F01	4F00	LATIN CAPITAL LETTER O WITH ACUTE
Ô	1401	2901	5101	5100	LATIN CAPITAL LETTER O WITH CURCUMFLEX
Ö	1401	2A01	5301	5300	LATIN CAPITAL LETTER O WITH DIAERESIS
Ő	1401	2B01	5501	5500	LATIN CAPITAL LETTER O WITH DOUBLE ACUTE
u	1C01	3801	6E01	6E00	LATIN _SMALL_ LETTER U
ú	1C01	3901	7001	7000	LATIN _SMALL_ LETTER U WITH ACUTE
ů	1C01	3A01	7201	7200	LATIN _SMALL_ LETTER U WITH RING ABOVE
ü	1C01	3B01	7401	7400	LATIN _SMALL_ LETTER U WITH DIAERESIS
ű	1C01	3C01	7601	7600	LATIN _SMALL_ LETTER U WITH DOUBLE ACUTE
drop table t1;
SET collation_connection=latin2_czech_cs;
CREATE TABLE t1 ENGINE=MYISAM AS SELECT repeat('a', 5) AS s1 LIMIT 0;
INSERT INTO t1 VALUES ('x'),('y'),('z'),('X'),('Y'),('Z');
SELECT * FROM t1 GROUP BY s1;
s1
x
X
y
Y
z
Z
SELECT * FROM t1 ORDER BY s1;
s1
x
X
y
Y
z
Z
CREATE INDEX i1 ON t1 (s1);
SELECT * FROM t1 GROUP BY s1;
s1
x
X
y
Y
z
Z
SELECT * FROM t1 ORDER BY s1;
s1
x
X
y
Y
z
Z
DROP TABLE t1;
set sql_mode="";
CREATE TABLE t1 ENGINE=INNODB AS SELECT repeat('a', 5) AS s1 LIMIT 0;
INSERT INTO t1 VALUES ('x'),('y'),('z'),('X'),('Y'),('Z');
SELECT * FROM t1 GROUP BY s1;
s1
x
X
y
Y
z
Z
SELECT * FROM t1 ORDER BY s1;
s1
x
X
y
Y
z
Z
CREATE INDEX i1 ON t1 (s1);
SELECT * FROM t1 GROUP BY s1;
s1
x
X
y
Y
z
Z
SELECT * FROM t1 ORDER BY s1;
s1
x
X
y
Y
z
Z
DROP TABLE t1;
SET sql_mode='';
CREATE TABLE t1 ENGINE=FALCON AS SELECT repeat('a', 5) AS s1 LIMIT 0;
SET sql_mode=DEFAULT;
INSERT INTO t1 VALUES ('x'),('y'),('z'),('X'),('Y'),('Z');
SELECT * FROM t1 GROUP BY s1;
s1
x
X
y
Y
z
Z
SELECT * FROM t1 ORDER BY s1;
s1
x
X
y
Y
z
Z
CREATE INDEX i1 ON t1 (s1);
SELECT * FROM t1 GROUP BY s1;
s1
x
X
y
Y
z
Z
SELECT * FROM t1 ORDER BY s1;
s1
x
X
y
Y
z
Z
DROP TABLE t1;
SET sql_mode='';
CREATE TABLE t1 ENGINE=MARIA AS SELECT repeat('a', 5) AS s1 LIMIT 0;
SET sql_mode=DEFAULT;
INSERT INTO t1 VALUES ('x'),('y'),('z'),('X'),('Y'),('Z');
SELECT * FROM t1 GROUP BY s1;
s1
x
X
y
Y
z
Z
SELECT * FROM t1 ORDER BY s1;
s1
x
X
y
Y
z
Z
CREATE INDEX i1 ON t1 (s1);
SELECT * FROM t1 GROUP BY s1;
s1
x
X
y
Y
z
Z
SELECT * FROM t1 ORDER BY s1;
s1
x
X
y
Y
z
Z
DROP TABLE t1;
SET NAMES latin2;
CREATE TABLE t2(colours SET('red','blue','yellow'))CHARACTER SET latin2 COLLATE
latin2_czech_cs;
CREATE TABLE t1(continent ENUM('Asia', 'Europe','Africa','Antartica'))CHARACTER SET latin2
COLLATE latin2_czech_cs;
INSERT INTO t1 VALUES('Asia');
INSERT INTO t2 VALUES('blue');
SELECT * FROM t1;
continent
Asia
SELECT * FROM t2;
colours
blue
DROP TABLE t1, t2;
CREATE TABLE `t1` (
`ID` smallint(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
`Post` enum('','B','O','Z','U') COLLATE latin2_czech_cs DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=135 DEFAULT CHARSET=latin2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `ID` smallint(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `Post` enum('','B','O','Z','U') CHARACTER SET latin2 COLLATE latin2_czech_cs DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=135 DEFAULT CHARSET=latin2
INSERT INTO t1 (ID,Post) VALUES (00041,'');
SELECT ID, Post, HEX(WEIGHT_STRING(Post)) FROM t1;
ID	Post	HEX(WEIGHT_STRING(Post))
00041		01010100
DROP TABLE t1;
#
# Start of 5.6 tests
#
#
# WL#3664 WEIGHT_STRING
#
set names latin2 collate latin2_czech_cs;
SELECT strcmp('a','a '),   strcmp('a ','a');
strcmp('a','a ')	strcmp('a ','a')
0	0
SELECT strcmp('a\0','a' ), strcmp('a','a\0');
strcmp('a\0','a' )	strcmp('a','a\0')
1	-1
SELECT strcmp('a\0','a '), strcmp('a ','a\0');
strcmp('a\0','a ')	strcmp('a ','a\0')
1	-1
SELECT strcmp('a\t','a' ), strcmp('a', 'a\t');
strcmp('a\t','a' )	strcmp('a', 'a\t')
0	0
SELECT strcmp('a\t','a '), strcmp('a ', 'a\t');
strcmp('a\t','a ')	strcmp('a ', 'a\t')
0	0
#
# Note:
# latin2_czech_cs does not support WEIGHT_STRING in full extent
#
select @@collation_connection;
@@collation_connection
latin2_czech_cs
CREATE TABLE t1 AS SELECT 'a' AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1) CHARACTER SET latin2 COLLATE latin2_czech_cs NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
0301030103010300
SELECT HEX(ws) FROM t2;
HEX(ws)
0301030103010300
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) CHARACTER SET latin2 COLLATE latin2_czech_cs DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(24) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
030303030301030303030301030303030301030303030300
SELECT HEX(ws) FROM t2;
HEX(ws)
030303030301030303030301030303030301030303030300
DROP TABLE t2;
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(24) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(3)))
030303030301030303030301030303030301030303030300
SELECT HEX(ws) FROM t2;
HEX(ws)
030303030301030303030301030303030301030303030300
DROP TABLE t2;
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(24) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
030303030301030303030301030303030301030303030300
SELECT HEX(ws) FROM t2;
HEX(ws)
030303030301030303030301030303030301030303030300
DROP TABLE t2;
DROP TABLE t1;
select hex(weight_string('a'));
hex(weight_string('a'))
0301030103010300
select hex(weight_string('A'));
hex(weight_string('A'))
0301030104010400
select hex(weight_string('abc'));
hex(weight_string('abc'))
0304050103090A01030F1101030F1100
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
0304050103090A01030F1101030F1100
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
0304050103090A01030F1101030F1100
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
0304050103090A01030F1101030F1100
select hex(weight_string('abc', 1, 2, 0xC0));
hex(weight_string('abc', 1, 2, 0xC0))
03
select hex(weight_string('abc', 2, 2, 0xC0));
hex(weight_string('abc', 2, 2, 0xC0))
0304
select hex(weight_string('abc', 3, 2, 0xC0));
hex(weight_string('abc', 3, 2, 0xC0))
030405
select hex(weight_string('abc', 4, 2, 0xC0));
hex(weight_string('abc', 4, 2, 0xC0))
03040501
select hex(weight_string('abc', 5, 2, 0xC0));
hex(weight_string('abc', 5, 2, 0xC0))
0304050103
select hex(weight_string('abc',25, 2, 0xC0));
hex(weight_string('abc',25, 2, 0xC0))
0304050103090A01030F1101030F1100202020202020202020
select hex(weight_string('abc', 1, 3, 0xC0));
hex(weight_string('abc', 1, 3, 0xC0))
03
select hex(weight_string('abc', 2, 3, 0xC0));
hex(weight_string('abc', 2, 3, 0xC0))
0304
select hex(weight_string('abc', 3, 3, 0xC0));
hex(weight_string('abc', 3, 3, 0xC0))
030405
select hex(weight_string('abc', 4, 3, 0xC0));
hex(weight_string('abc', 4, 3, 0xC0))
03040501
select hex(weight_string('abc', 5, 3, 0xC0));
hex(weight_string('abc', 5, 3, 0xC0))
0304050103
select hex(weight_string('abc',25, 3, 0xC0));
hex(weight_string('abc',25, 3, 0xC0))
0304050103090A01030F1101030F1100202020202020202020
select hex(weight_string('abc', 1, 4, 0xC0));
hex(weight_string('abc', 1, 4, 0xC0))
03
select hex(weight_string('abc', 2, 4, 0xC0));
hex(weight_string('abc', 2, 4, 0xC0))
0304
select hex(weight_string('abc', 3, 4, 0xC0));
hex(weight_string('abc', 3, 4, 0xC0))
030405
select hex(weight_string('abc', 4, 4, 0xC0));
hex(weight_string('abc', 4, 4, 0xC0))
03040501
select hex(weight_string('abc', 5, 4, 0xC0));
hex(weight_string('abc', 5, 4, 0xC0))
0304050103
select hex(weight_string('abc',25, 4, 0xC0));
hex(weight_string('abc',25, 4, 0xC0))
0304050103090A01030F1101030F1100202020202020202020
select @@collation_connection;
@@collation_connection
latin2_czech_cs
select collation(cast(_latin1 0xDF as char));
collation(cast(_latin1 0xDF as char))
latin2_czech_cs
select hex(weight_string('s'));
hex(weight_string('s'))
1901310160016000
select hex(weight_string(cast(_latin1 0xDF as char)));
hex(weight_string(cast(_latin1 0xDF as char)))
010101E200
select hex(weight_string(cast(_latin1 0xDF as char) as char(1)));
hex(weight_string(cast(_latin1 0xDF as char) as char(1)))
010101E200
select hex(weight_string('c'));
hex(weight_string('c'))
05010A0111011100
select hex(weight_string('h'));
hex(weight_string('h'))
0B0118012D012D00
select hex(weight_string('ch'));
hex(weight_string('ch'))
0C0119012F012F00
select hex(weight_string('i'));
hex(weight_string('i'))
0D011A0132013200
select hex(weight_string(cast(_latin1 0x6368DF as char)));
hex(weight_string(cast(_latin1 0x6368DF as char)))
0C0119012F012FE200
select hex(weight_string(cast(_latin1 0x6368DF as char) as char(1)));
hex(weight_string(cast(_latin1 0x6368DF as char) as char(1)))
0C0119012F012FE200
select hex(weight_string(cast(_latin1 0x6368DF as char) as char(2)));
hex(weight_string(cast(_latin1 0x6368DF as char) as char(2)))
0C0119012F012FE200
select hex(weight_string(cast(_latin1 0x6368DF as char) as char(3)));
hex(weight_string(cast(_latin1 0x6368DF as char) as char(3)))
0C0119012F012FE200
select hex(weight_string(cast(_latin1 0x6368DF as char) as char(4)));
hex(weight_string(cast(_latin1 0x6368DF as char) as char(4)))
0C0119012F012FE200
select hex(weight_string(cast(_latin1 0xDF6368 as char)));
hex(weight_string(cast(_latin1 0xDF6368 as char)))
0C0119012F01E22F00
select hex(weight_string(cast(_latin1 0xDF6368 as char) as char(1)));
hex(weight_string(cast(_latin1 0xDF6368 as char) as char(1)))
0C0119012F01E22F00
select hex(weight_string(cast(_latin1 0xDF6368 as char) as char(2)));
hex(weight_string(cast(_latin1 0xDF6368 as char) as char(2)))
0C0119012F01E22F00
select hex(weight_string(cast(_latin1 0xDF6368 as char) as char(3)));
hex(weight_string(cast(_latin1 0xDF6368 as char) as char(3)))
0C0119012F01E22F00
select hex(weight_string(cast(_latin1 0xDF6368 as char) as char(4)));
hex(weight_string(cast(_latin1 0xDF6368 as char) as char(4)))
0C0119012F01E22F00
select hex(weight_string(cast(_latin1 0x6368DF as char), 1, 2, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 1, 2, 0xC0))
0C
select hex(weight_string(cast(_latin1 0x6368DF as char), 2, 2, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 2, 2, 0xC0))
0C01
select hex(weight_string(cast(_latin1 0x6368DF as char), 3, 2, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 3, 2, 0xC0))
0C0119
select hex(weight_string(cast(_latin1 0x6368DF as char), 4, 2, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 4, 2, 0xC0))
0C011901
select hex(weight_string(cast(_latin1 0x6368DF as char),25, 2, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char),25, 2, 0xC0))
0C0119012F012FE20020202020202020202020202020202020
select hex(weight_string(cast(_latin1 0x6368DF as char), 1, 3, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 1, 3, 0xC0))
0C
select hex(weight_string(cast(_latin1 0x6368DF as char), 2, 3, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 2, 3, 0xC0))
0C01
select hex(weight_string(cast(_latin1 0x6368DF as char), 3, 3, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 3, 3, 0xC0))
0C0119
select hex(weight_string(cast(_latin1 0x6368DF as char), 4, 3, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 4, 3, 0xC0))
0C011901
select hex(weight_string(cast(_latin1 0x6368DF as char),25, 3, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char),25, 3, 0xC0))
0C0119012F012FE20020202020202020202020202020202020
select hex(weight_string(cast(_latin1 0x6368DF as char), 1, 4, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 1, 4, 0xC0))
0C
select hex(weight_string(cast(_latin1 0x6368DF as char), 2, 4, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 2, 4, 0xC0))
0C01
select hex(weight_string(cast(_latin1 0x6368DF as char), 3, 4, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 3, 4, 0xC0))
0C0119
select hex(weight_string(cast(_latin1 0x6368DF as char), 4, 4, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char), 4, 4, 0xC0))
0C011901
select hex(weight_string(cast(_latin1 0x6368DF as char),25, 4, 0xC0));
hex(weight_string(cast(_latin1 0x6368DF as char),25, 4, 0xC0))
0C0119012F012FE20020202020202020202020202020202020
select hex(weight_string(cast(_latin1 0xDF6368 as char), 1, 2,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 1, 2,0xC0))
0C
select hex(weight_string(cast(_latin1 0xDF6368 as char), 2, 2,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 2, 2,0xC0))
0C01
select hex(weight_string(cast(_latin1 0xDF6368 as char), 3, 2,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 3, 2,0xC0))
0C0119
select hex(weight_string(cast(_latin1 0xDF6368 as char), 4, 2,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 4, 2,0xC0))
0C011901
select hex(weight_string(cast(_latin1 0xDF6368 as char),25, 2,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char),25, 2,0xC0))
0C0119012F01E22F0020202020202020202020202020202020
select hex(weight_string(cast(_latin1 0xDF6368 as char), 1, 3,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 1, 3,0xC0))
0C
select hex(weight_string(cast(_latin1 0xDF6368 as char), 2, 3,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 2, 3,0xC0))
0C01
select hex(weight_string(cast(_latin1 0xDF6368 as char), 3, 3,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 3, 3,0xC0))
0C0119
select hex(weight_string(cast(_latin1 0xDF6368 as char), 4, 3,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 4, 3,0xC0))
0C011901
select hex(weight_string(cast(_latin1 0xDF6368 as char),25, 3,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char),25, 3,0xC0))
0C0119012F01E22F0020202020202020202020202020202020
select hex(weight_string(cast(_latin1 0xDF6368 as char), 1, 4,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 1, 4,0xC0))
0C
select hex(weight_string(cast(_latin1 0xDF6368 as char), 2, 4,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 2, 4,0xC0))
0C01
select hex(weight_string(cast(_latin1 0xDF6368 as char), 3, 4,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 3, 4,0xC0))
0C0119
select hex(weight_string(cast(_latin1 0xDF6368 as char), 4, 4,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char), 4, 4,0xC0))
0C011901
select hex(weight_string(cast(_latin1 0xDF6368 as char),25, 4,0xC0));
hex(weight_string(cast(_latin1 0xDF6368 as char),25, 4,0xC0))
0C0119012F01E22F0020202020202020202020202020202020
select @@collation_connection;
@@collation_connection
latin2_czech_cs
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
0301
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
0301
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
03040501
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
03040501
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
03040501
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
03040501
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
03040501
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
03040501
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
03040501
select @@collation_connection;
@@collation_connection
latin2_czech_cs
select hex(weight_string('a' LEVEL 2));
hex(weight_string('a' LEVEL 2))
0301
select hex(weight_string('A' LEVEL 2));
hex(weight_string('A' LEVEL 2))
0301
select hex(weight_string('abc' LEVEL 2));
hex(weight_string('abc' LEVEL 2))
03090A01
select hex(weight_string('abc' as char(2) LEVEL 2));
hex(weight_string('abc' as char(2) LEVEL 2))
03090A01
select hex(weight_string('abc' as char(3) LEVEL 2));
hex(weight_string('abc' as char(3) LEVEL 2))
03090A01
select hex(weight_string('abc' as char(5) LEVEL 2));
hex(weight_string('abc' as char(5) LEVEL 2))
03090A01
select @@collation_connection;
@@collation_connection
latin2_czech_cs
select hex(weight_string('a' LEVEL 3));
hex(weight_string('a' LEVEL 3))
0301
select hex(weight_string('A' LEVEL 3));
hex(weight_string('A' LEVEL 3))
0401
select hex(weight_string('abc' LEVEL 3));
hex(weight_string('abc' LEVEL 3))
030F1101
select hex(weight_string('abc' as char(2) LEVEL 3));
hex(weight_string('abc' as char(2) LEVEL 3))
030F1101
select hex(weight_string('abc' as char(3) LEVEL 3));
hex(weight_string('abc' as char(3) LEVEL 3))
030F1101
select hex(weight_string('Abc' as char(5) LEVEL 3));
hex(weight_string('Abc' as char(5) LEVEL 3))
040F1101
select hex(weight_string('Abc' as char(5) LEVEL 3 REVERSE));
hex(weight_string('Abc' as char(5) LEVEL 3 REVERSE))
040F1101
select hex(weight_string('Abc' as char(5) LEVEL 3 DESC));
hex(weight_string('Abc' as char(5) LEVEL 3 DESC))
040F1101
select hex(weight_string('Abc' as char(5) LEVEL 3 DESC REVERSE));
hex(weight_string('Abc' as char(5) LEVEL 3 DESC REVERSE))
040F1101
select @@collation_connection;
@@collation_connection
latin2_czech_cs
select hex(weight_string('a' LEVEL 4));
hex(weight_string('a' LEVEL 4))
0300
select hex(weight_string('A' LEVEL 4));
hex(weight_string('A' LEVEL 4))
0400
select hex(weight_string('abc' LEVEL 4));
hex(weight_string('abc' LEVEL 4))
030F1100
select hex(weight_string('abc' as char(2) LEVEL 4));
hex(weight_string('abc' as char(2) LEVEL 4))
030F1100
select hex(weight_string('abc' as char(3) LEVEL 4));
hex(weight_string('abc' as char(3) LEVEL 4))
030F1100
select hex(weight_string('abc' as char(5) LEVEL 4));
hex(weight_string('abc' as char(5) LEVEL 4))
030F1100
select hex(weight_string('abc' as char(5) LEVEL 4 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 4 REVERSE))
030F1100
select hex(weight_string('abc' as char(5) LEVEL 4 DESC));
hex(weight_string('abc' as char(5) LEVEL 4 DESC))
030F1100
select hex(weight_string('abc' as char(5) LEVEL 4 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 4 DESC REVERSE))
030F1100
select @@collation_connection;
@@collation_connection
latin2_czech_cs
select hex(weight_string('a' LEVEL 1,2));
hex(weight_string('a' LEVEL 1,2))
03010301
select hex(weight_string('a' LEVEL 1-2));
hex(weight_string('a' LEVEL 1-2))
03010301
select hex(weight_string('A' LEVEL 1,2));
hex(weight_string('A' LEVEL 1,2))
03010301
select hex(weight_string('A' LEVEL 1-2));
hex(weight_string('A' LEVEL 1-2))
03010301
select @@collation_connection;
@@collation_connection
latin2_czech_cs
select hex(weight_string('a' LEVEL 1,3));
hex(weight_string('a' LEVEL 1,3))
03010301
select hex(weight_string('a' LEVEL 1-3));
hex(weight_string('a' LEVEL 1-3))
030103010301
select hex(weight_string('A' LEVEL 1,3));
hex(weight_string('A' LEVEL 1,3))
03010401
select hex(weight_string('A' LEVEL 1-3));
hex(weight_string('A' LEVEL 1-3))
030103010401
select hex(weight_string('a' LEVEL 1,4));
hex(weight_string('a' LEVEL 1,4))
03010300
select hex(weight_string('a' LEVEL 1-4));
hex(weight_string('a' LEVEL 1-4))
0301030103010300
select hex(weight_string('A' LEVEL 1,4));
hex(weight_string('A' LEVEL 1,4))
03010400
select hex(weight_string('A' LEVEL 1-4));
hex(weight_string('A' LEVEL 1-4))
0301030104010400
select hex(weight_string('a' LEVEL 2,3));
hex(weight_string('a' LEVEL 2,3))
03010301
select hex(weight_string('a' LEVEL 2-3));
hex(weight_string('a' LEVEL 2-3))
03010301
select hex(weight_string('A' LEVEL 2,3));
hex(weight_string('A' LEVEL 2,3))
03010401
select hex(weight_string('A' LEVEL 2-3));
hex(weight_string('A' LEVEL 2-3))
03010401
select hex(weight_string('a' LEVEL 2,4));
hex(weight_string('a' LEVEL 2,4))
03010300
select hex(weight_string('a' LEVEL 2-4));
hex(weight_string('a' LEVEL 2-4))
030103010300
select hex(weight_string('A' LEVEL 2,4));
hex(weight_string('A' LEVEL 2,4))
03010400
select hex(weight_string('A' LEVEL 2-4));
hex(weight_string('A' LEVEL 2-4))
030104010400
select hex(weight_string('a' LEVEL 3,4));
hex(weight_string('a' LEVEL 3,4))
03010300
select hex(weight_string('a' LEVEL 3-4));
hex(weight_string('a' LEVEL 3-4))
03010300
select hex(weight_string('A' LEVEL 3,4));
hex(weight_string('A' LEVEL 3,4))
04010400
select hex(weight_string('A' LEVEL 3-4));
hex(weight_string('A' LEVEL 3-4))
04010400
select hex(weight_string('a' LEVEL 1,2,3,4));
hex(weight_string('a' LEVEL 1,2,3,4))
0301030103010300
select hex(weight_string('a' LEVEL 2,3,4));
hex(weight_string('a' LEVEL 2,3,4))
030103010300
select hex(weight_string('a' LEVEL 1,3,4));
hex(weight_string('a' LEVEL 1,3,4))
030103010300
select hex(weight_string('a' LEVEL 1,2,3));
hex(weight_string('a' LEVEL 1,2,3))
030103010301
select hex(weight_string('a' LEVEL 0));
hex(weight_string('a' LEVEL 0))
0301
select hex(weight_string('a' LEVEL 8));
hex(weight_string('a' LEVEL 8))
0300
select hex(weight_string('a' LEVEL 1,8));
hex(weight_string('a' LEVEL 1,8))
03010300
#
# End of 5.6 tests
#
