File: ctype_ucs.result

package info (click to toggle)
mariadb-10.0 10.0.32-0%2Bdeb8u1
links: PTS, VCS
area: main
in suites: jessie
size: 476,064 kB
sloc: cpp: 1,400,131; ansic: 832,140; perl: 54,391; sh: 41,304; pascal: 32,365; yacc: 14,921; xml: 5,257; sql: 4,667; cs: 4,647; makefile: 4,555; ruby: 4,465; python: 2,292; lex: 1,427; java: 941; asm: 295; awk: 54; php: 22; sed: 16
file content (5573 lines) | stat: -rw-r--r-- 170,523 bytes
DROP TABLE IF EXISTS t1;
SET @test_character_set= 'ucs2';
SET @test_collation= 'ucs2_general_ci';
SET @safe_character_set_server= @@character_set_server;
SET @safe_collation_server= @@collation_server;
SET @safe_character_set_client= @@character_set_client;
SET @safe_character_set_results= @@character_set_results;
SET character_set_server= @test_character_set;
SET collation_server= @test_collation;
CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (c CHAR(10), KEY(c));
SHOW FULL COLUMNS FROM t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
c	char(10)	ucs2_general_ci	YES	MUL	NULL			
INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa');
SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%';
want3results
aaa
aaaa
aaaaa
DROP TABLE t1;
CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2)));
SHOW FULL COLUMNS FROM t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
c1	varchar(15)	ucs2_general_ci	YES	MUL	NULL			
INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab');
SELECT c1 as want3results from t1 where c1 like 'l%';
want3results
location
loberge
lotre
SELECT c1 as want3results from t1 where c1 like 'lo%';
want3results
location
loberge
lotre
SELECT c1 as want1result  from t1 where c1 like 'loc%';
want1result
location
SELECT c1 as want1result  from t1 where c1 like 'loca%';
want1result
location
SELECT c1 as want1result  from t1 where c1 like 'locat%';
want1result
location
SELECT c1 as want1result  from t1 where c1 like 'locati%';
want1result
location
SELECT c1 as want1result  from t1 where c1 like 'locatio%';
want1result
location
SELECT c1 as want1result  from t1 where c1 like 'location%';
want1result
location
DROP TABLE t1;
create table t1 (a set('a') not null);
insert into t1 values (),();
Warnings:
Warning	1364	Field 'a' doesn't have a default value
select cast(a as char(1)) from t1;
cast(a as char(1))


select a sounds like a from t1;
a sounds like a
1
1
select 1 from t1 order by cast(a as char(1));
1
1
1
drop table t1;
#
# MDEV-6134 SUBSTRING_INDEX returns wrong result for 8bit character sets when delimiter is not found
#
SET character_set_client=latin1;
SET character_set_connection= @test_character_set;
SET collation_connection= @test_collation;
SELECT COLLATION('.'), SUBSTRING_INDEX('.wwwmysqlcom', '.', -2) AS c1;
COLLATION('.')	c1
ucs2_general_ci	.wwwmysqlcom
set names utf8;
create table t1 (
name varchar(10),
level smallint unsigned);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `name` varchar(10) DEFAULT NULL,
  `level` smallint(5) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ucs2
insert into t1 values ('string',1);
select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
concat(name,space(level))	concat(name, repeat(' ',level))
string 	string 
drop table t1;
DROP DATABASE d1;
USE test;
SET character_set_server= @safe_character_set_server;
SET collation_server= @safe_collation_server;
SET character_set_client= @safe_character_set_client;
SET character_set_results= @safe_character_set_results;
SET NAMES latin1;
SET character_set_connection=ucs2;
select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';
'a' = 'a'	'a' = 'a '	'a ' = 'a'
1	1	1
select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a';
'a\0' = 'a'	'a\0' < 'a'	'a\0' > 'a'
0	1	0
select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0';
'a' = 'a\0'	'a' < 'a\0'	'a' > 'a\0'
0	0	1
select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a ';
'a\0' = 'a '	'a\0' < 'a '	'a\0' > 'a '
0	1	0
select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0';
'a ' = 'a\0'	'a ' < 'a\0'	'a ' > 'a\0'
0	0	1
select 'a  a' > 'a', 'a  \0' < 'a';
'a  a' > 'a'	'a  \0' < 'a'
1	1
select binary 'a  a' > 'a', binary 'a  \0' > 'a', binary 'a\0' > 'a';
binary 'a  a' > 'a'	binary 'a  \0' > 'a'	binary 'a\0' > 'a'
1	1	1
SET CHARACTER SET koi8r;
create table t1 (a varchar(2) character set ucs2 collate ucs2_bin, key(a));
insert into t1 values ('A'),('A'),('B'),('C'),('D'),('A\t');
insert into t1 values ('A\0'),('A\0'),('A\0'),('A\0'),('AZ');
select hex(a) from t1 where a like 'A_' order by a;
hex(a)
00410000
00410000
00410000
00410000
00410009
0041005A
select hex(a) from t1 ignore key(a) where a like 'A_' order by a;
hex(a)
00410000
00410000
00410000
00410000
00410009
0041005A
drop table t1;
CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2, word2 CHAR(64) CHARACTER SET ucs2);
INSERT INTO t1 VALUES (_koi8r'',_koi8r''), (X'2004',X'2004');
SELECT hex(word) FROM t1 ORDER BY word;
hex(word)
0420
2004
SELECT hex(word2) FROM t1 ORDER BY word2;
hex(word2)
0420
2004
DELETE FROM t1;
INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020');
SELECT hex(word) FROM t1 ORDER BY word;
hex(word)
042000200020
200400200020
SELECT hex(word2) FROM t1 ORDER BY word2;
hex(word2)
0420
2004
DROP TABLE t1;
SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'0421');
LPAD(_ucs2 X'0420',10,_ucs2 X'0421')

SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'04210422');
LPAD(_ucs2 X'0420',10,_ucs2 X'04210422')

SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'042104220423');
LPAD(_ucs2 X'0420',10,_ucs2 X'042104220423')

SELECT LPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
LPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423')

SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'0421');
RPAD(_ucs2 X'0420',10,_ucs2 X'0421')

SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'04210422');
RPAD(_ucs2 X'0420',10,_ucs2 X'04210422')

SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'042104220423');
RPAD(_ucs2 X'0420',10,_ucs2 X'042104220423')

SELECT RPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
RPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423')

CREATE TABLE t1 SELECT 
LPAD(_ucs2 X'0420',10,_ucs2 X'0421') l,
RPAD(_ucs2 X'0420',10,_ucs2 X'0421') r;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `l` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT '',
  `r` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
#
# Bug #51876 : crash/memory underrun when loading data with ucs2 
#   and reverse() function
#
# Problem # 1 (original report): wrong parsing of ucs2 data
SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp.txt';
CREATE TABLE t1(a INT);
LOAD DATA INFILE 'tmpp.txt' INTO TABLE t1 CHARACTER SET ucs2
(@b) SET a=REVERSE(@b);
# should return 2 zeroes (as the value is truncated)
SELECT * FROM t1;
a
0
1
DROP TABLE t1;
# Problem # 2 : if you write and read ucs2 data to a file they're lost
SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp2.txt' CHARACTER SET ucs2;
CREATE TABLE t1(a INT);
LOAD DATA INFILE 'tmpp2.txt' INTO TABLE t1 CHARACTER SET ucs2
(@b) SET a=REVERSE(@b);
# should return 0 and 1 (10 reversed)
SELECT * FROM t1;
a
0
1
DROP TABLE t1;
create table t2(f1 Char(30));
insert into t2 values ("103000"), ("22720000"), ("3401200"), ("78000");
select lpad(f1, 12, "-o-/") from t2;
lpad(f1, 12, "-o-/")
-o-/-o103000
-o-/22720000
-o-/-3401200
-o-/-o-78000
drop table t2;
SET NAMES koi8r;
SET character_set_connection=ucs2;
select @@collation_connection;
@@collation_connection
ucs2_general_ci
create table t1 as select repeat(' ',10) as a union select null;
alter table t1 add key(a);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) CHARACTER SET ucs2 DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
explain select * from t1 where a like 'abc%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	23	NULL	1	Using where; Using index
explain select * from t1 where a like concat('abc','%');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	23	NULL	1	Using where; Using index
select * from t1 where a like "abc%";
a
abc
abcd
select * from t1 where a like concat("abc","%");
a
abc
abcd
select * from t1 where a like "ABC%";
a
abc
abcd
select * from t1 where a like "test%";
a
test
select * from t1 where a like "te_t";
a
test
select * from t1 where a like "%a%";
a
a
abc
abcd
select * from t1 where a like "%abcd%";
a
abcd
select * from t1 where a like "%abc\d%";
a
abcd
drop table t1;
select 'AA' like 'AA';
'AA' like 'AA'
1
select 'AA' like 'A%A';
'AA' like 'A%A'
1
select 'AA' like 'A%%A';
'AA' like 'A%%A'
1
select 'AA' like 'AA%';
'AA' like 'AA%'
1
select 'AA' like '%AA%';
'AA' like '%AA%'
1
select 'AA' like '%A';
'AA' like '%A'
1
select 'AA' like '%AA';
'AA' like '%AA'
1
select 'AA' like 'A%A%';
'AA' like 'A%A%'
1
select 'AA' like '_%_%';
'AA' like '_%_%'
1
select 'AA' like '%A%A';
'AA' like '%A%A'
1
select 'AAA'like 'A%A%A';
'AAA'like 'A%A%A'
1
select 'AZ' like 'AZ';
'AZ' like 'AZ'
1
select 'AZ' like 'A%Z';
'AZ' like 'A%Z'
1
select 'AZ' like 'A%%Z';
'AZ' like 'A%%Z'
1
select 'AZ' like 'AZ%';
'AZ' like 'AZ%'
1
select 'AZ' like '%AZ%';
'AZ' like '%AZ%'
1
select 'AZ' like '%Z';
'AZ' like '%Z'
1
select 'AZ' like '%AZ';
'AZ' like '%AZ'
1
select 'AZ' like 'A%Z%';
'AZ' like 'A%Z%'
1
select 'AZ' like '_%_%';
'AZ' like '_%_%'
1
select 'AZ' like '%A%Z';
'AZ' like '%A%Z'
1
select 'AZ' like 'A_';
'AZ' like 'A_'
1
select 'AZ' like '_Z';
'AZ' like '_Z'
1
select 'AMZ'like 'A%M%Z';
'AMZ'like 'A%M%Z'
1
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ucs2);
INSERT INTO t1 VALUES (''),(''),(''),(''),(''),('');
INSERT INTO t1 VALUES (''),(''),(''),('');
INSERT INTO t1 VALUES (''),(''),(''),('');
INSERT INTO t1 VALUES (''),(''),(''),('');
SELECT * FROM t1 WHERE a LIKE '%%';
a


















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


















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


















SELECT * FROM t1 WHERE a LIKE '%' COLLATE ucs2_bin;
a


DROP TABLE t1;
CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word))
ENGINE=MyISAM CHARACTER SET ucs2 COLLATE ucs2_general_ci;
INSERT INTO t1 (word) VALUES ("cat");
SELECT * FROM t1 WHERE word LIKE "c%";
word
cat
SELECT * FROM t1 WHERE word LIKE "ca_";
word
cat
SELECT * FROM t1 WHERE word LIKE "cat";
word
cat
SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630025';
word
cat
SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630061005F';
word
cat
DROP TABLE t1;
select insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066);
insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066)
abc
select insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066);
insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066)
defc
SET NAMES latin1;
CREATE TABLE t1 (
word VARCHAR(64),
bar INT(11) default 0,
PRIMARY KEY (word))
ENGINE=MyISAM
CHARSET ucs2
COLLATE ucs2_general_ci ;
INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a");
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY word;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
SELECT * FROM t1 ORDER BY word;
word	bar
a	0
aar	0
aardvar	0
aardvara	0
aardvark	0
aardvarz	0
EXPLAIN SELECT word FROM t1 ORDER BY word;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	130	NULL	6	Using index
SELECT word FROM t1 ORDER by word;
word
a
aar
aardvar
aardvara
aardvark
aardvarz
DROP TABLE t1;
CREATE TABLE t1 (
word VARCHAR(64) ,
PRIMARY KEY (word))
ENGINE=MyISAM
CHARSET ucs2
COLLATE ucs2_general_ci;
INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a");
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY WORD;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	130	NULL	6	Using index
SELECT * FROM t1 ORDER BY word;
word
a
aar
aardvar
aardvara
aardvark
aardvarz
DROP TABLE t1;
CREATE TABLE t1 (
word TEXT,
bar INT(11) AUTO_INCREMENT,
PRIMARY KEY (bar))
ENGINE=MyISAM
CHARSET ucs2
COLLATE ucs2_general_ci ;
INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a" );
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY word;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
SELECT * FROM t1 ORDER BY word;
word	bar
a	2
aar	1
aardvar	3
aardvara	5
aardvark	4
aardvarz	6
EXPLAIN SELECT word FROM t1 ORDER BY word;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
SELECT word FROM t1 ORDER BY word;
word
a
aar
aardvar
aardvara
aardvark
aardvarz
DROP TABLE t1;
SELECT HEX(_ucs2 0x0);
HEX(_ucs2 0x0)
0000
SELECT HEX(_ucs2 0x01);
HEX(_ucs2 0x01)
0001
SELECT HEX(_ucs2 0x012);
HEX(_ucs2 0x012)
0012
SELECT HEX(_ucs2 0x0123);
HEX(_ucs2 0x0123)
0123
SELECT HEX(_ucs2 0x01234);
HEX(_ucs2 0x01234)
00001234
SELECT HEX(_ucs2 0x012345);
HEX(_ucs2 0x012345)
00012345
SELECT HEX(_ucs2 0x0123456);
HEX(_ucs2 0x0123456)
00123456
SELECT HEX(_ucs2 0x01234567);
HEX(_ucs2 0x01234567)
01234567
SELECT HEX(_ucs2 0x012345678);
HEX(_ucs2 0x012345678)
000012345678
SELECT HEX(_ucs2 0x0123456789);
HEX(_ucs2 0x0123456789)
000123456789
SELECT HEX(_ucs2 0x0123456789A);
HEX(_ucs2 0x0123456789A)
00123456789A
SELECT HEX(_ucs2 0x0123456789AB);
HEX(_ucs2 0x0123456789AB)
0123456789AB
SELECT HEX(_ucs2 0x0123456789ABC);
HEX(_ucs2 0x0123456789ABC)
0000123456789ABC
SELECT HEX(_ucs2 0x0123456789ABCD);
HEX(_ucs2 0x0123456789ABCD)
000123456789ABCD
SELECT HEX(_ucs2 0x0123456789ABCDE);
HEX(_ucs2 0x0123456789ABCDE)
00123456789ABCDE
SELECT HEX(_ucs2 0x0123456789ABCDEF);
HEX(_ucs2 0x0123456789ABCDEF)
0123456789ABCDEF
SELECT hex(cast(0xAA as char character set ucs2));
hex(cast(0xAA as char character set ucs2))
00AA
SELECT hex(convert(0xAA using ucs2));
hex(convert(0xAA using ucs2))
00AA
CREATE TABLE t1 (a char(10) character set ucs2);
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
SELECT HEX(a) FROM t1;
HEX(a)
000A
00AA
0AAA
AAAA
000AAAAA
DROP TABLE t1;
CREATE TABLE t1 (a varchar(10) character set ucs2);
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
SELECT HEX(a) FROM t1;
HEX(a)
000A
00AA
0AAA
AAAA
000AAAAA
DROP TABLE t1;
CREATE TABLE t1 (a text character set ucs2);
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
SELECT HEX(a) FROM t1;
HEX(a)
000A
00AA
0AAA
AAAA
000AAAAA
DROP TABLE t1;
CREATE TABLE t1 (a mediumtext character set ucs2);
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
SELECT HEX(a) FROM t1;
HEX(a)
000A
00AA
0AAA
AAAA
000AAAAA
DROP TABLE t1;
CREATE TABLE t1 (a longtext character set ucs2);
INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
SELECT HEX(a) FROM t1;
HEX(a)
000A
00AA
0AAA
AAAA
000AAAAA
DROP TABLE t1;
create table t1 (s1 char character set `ucs2` collate `ucs2_czech_ci`);
insert into t1 values ('0'),('1'),('2'),('a'),('b'),('c');
select s1 from t1 where s1 > 'a' order by s1;
s1
b
c
drop table t1;
create table t1(a char(1)) default charset = ucs2;
insert into t1 values ('a'),('b'),('c');
alter table t1 modify a char(5);
select a, hex(a) from t1;
a	hex(a)
a	0061
b	0062
c	0063
drop table t1;
set @ivar= 1234;
set @str1 = 'select ?';
set @str2 = convert(@str1 using ucs2);
prepare stmt1 from @str2;
execute stmt1 using @ivar;
?
1234
set names latin1;
create table t1 (a enum('x','y','z') character set ucs2);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` enum('x','y','z') CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('x');
insert into t1 values ('y');
insert into t1 values ('z');
select a, hex(a) from t1 order by a;
a	hex(a)
x	0078
y	0079
z	007A
alter table t1 change a a enum('x','y','z','d','e','','','') character set ucs2;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` enum('x','y','z','d','e','','','') CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('D');
insert into t1 values ('E ');
insert into t1 values ('');
insert into t1 values ('');
insert into t1 values ('');
select a, hex(a) from t1 order by a;
a	hex(a)
x	0078
y	0079
z	007A
d	0064
e	0065
	00E4
	00F6
	00FC
drop table t1;
create table t1 (a set ('x','y','z','','','') character set ucs2);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` set('x','y','z','','','') CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('x');
insert into t1 values ('y');
insert into t1 values ('z');
insert into t1 values ('x,y');
insert into t1 values ('x,y,z,,,');
select a, hex(a) from t1 order by a;
a	hex(a)
x	0078
y	0079
x,y	0078002C0079
z	007A
x,y,z,,,	0078002C0079002C007A002C00E4002C00F6002C00FC
drop table t1;
create table t1(a enum('a','b','c')) default character set ucs2;
insert into t1 values('a'),('b'),('c');
alter table t1 add b char(1);
show warnings;
Level	Code	Message
select * from t1 order by a;
a	b
a	NULL
b	NULL
c	NULL
drop table t1;
SET collation_connection='ucs2_general_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)
ucs2_general_ci	00610009
ucs2_general_ci	0061
ucs2_general_ci	00610020
drop table t1;
#
# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP
#
SELECT @@collation_connection;
@@collation_connection
ucs2_general_ci
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP;
i
1
1
SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP;
i
1
1
DROP TABLE t1;
#
# MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations
#
SELECT @@collation_connection;
@@collation_connection
ucs2_general_ci
CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0;
INSERT INTO t1 (a) VALUES ("a");
INSERT INTO t1 (a) VALUES ("b");
INSERT INTO t1 (a) VALUES ("c");
INSERT INTO t1 (a) VALUES ("d");
INSERT INTO t1 (a) VALUES ("e");
INSERT INTO t1 (a) VALUES ("f");
INSERT INTO t1 (a) VALUES ("g");
INSERT INTO t1 (a) VALUES ("h");
INSERT INTO t1 (a) VALUES ("i");
INSERT INTO t1 (a) VALUES ("j");
INSERT INTO t1 (a) VALUES ("k");
INSERT INTO t1 (a) VALUES ("l");
INSERT INTO t1 (a) VALUES ("m");
SELECT * FROM t1 ORDER BY LOWER(a);
a
a
b
c
d
e
f
g
h
i
j
k
l
m
SELECT * FROM t1 ORDER BY LOWER(a) DESC;
a
m
l
k
j
i
h
g
f
e
d
c
b
a
DROP TABLE t1;
select @@collation_connection;
@@collation_connection
ucs2_general_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;
"BEGIN ctype_german.inc"
drop table if exists t1;
create table t1 as select repeat(' ', 64) as s1;
select collation(s1) from t1;
collation(s1)
ucs2_general_ci
delete from t1;
INSERT INTO t1 VALUES ('ud'),('uf');
INSERT INTO t1 VALUES ('od'),('of');
INSERT INTO t1 VALUES ('e');
INSERT INTO t1 VALUES ('ad'),('af');
insert into t1 values ('a'),('ae'),(_latin1 0xE4);
insert into t1 values ('o'),('oe'),(_latin1 0xF6);
insert into t1 values ('s'),('ss'),(_latin1 0xDF);
insert into t1 values ('u'),('ue'),(_latin1 0xFC);
INSERT INTO t1 VALUES (_latin1 0xE6), (_latin1 0xC6);
INSERT INTO t1 VALUES (_latin1 0x9C), (_latin1 0x8C);
select s1, hex(s1) from t1 order by s1, binary s1;
s1	hex(s1)
a	0061
	00E4
ad	00610064
ae	00610065
af	00610066
e	0065
o	006F
	00F6
od	006F0064
oe	006F0065
of	006F0066
s	0073
	00DF
ss	00730073
u	0075
	00FC
ud	00750064
ue	00750065
uf	00750066
	00C6
	00E6
	0152
	0153
select group_concat(s1 order by binary s1) from t1 group by s1;
group_concat(s1 order by binary s1)
a,
ad
ae
af
e
o,
od
oe
of
s,
ss
u,
ud
ue
uf
,
,
SELECT s1, hex(s1), hex(weight_string(s1)) FROM t1 ORDER BY s1, BINARY(s1);
s1	hex(s1)	hex(weight_string(s1))
a	0061	0041
	00E4	0041
ad	00610064	00410044
ae	00610065	00410045
af	00610066	00410046
e	0065	0045
o	006F	004F
	00F6	004F
od	006F0064	004F0044
oe	006F0065	004F0045
of	006F0066	004F0046
s	0073	0053
	00DF	0053
ss	00730073	00530053
u	0075	0055
	00FC	0055
ud	00750064	00550044
ue	00750065	00550045
uf	00750066	00550046
	00C6	00C6
	00E6	00C6
	0152	0152
	0153	0152
SELECT s1, hex(s1) FROM t1 WHERE s1='ae' ORDER BY s1, BINARY(s1);
s1	hex(s1)
ae	00610065
drop table t1;
CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a, 1 AS b LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT '',
  `b` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES ('s',0),(_latin1 0xDF,1);
SELECT * FROM t1 ORDER BY a, b;
a	b
s	0
	1
SELECT * FROM t1 ORDER BY a DESC, b;
a	b
s	0
	1
SELECT * FROM t1 ORDER BY CONCAT(a), b;
a	b
s	0
	1
SELECT * FROM t1 ORDER BY CONCAT(a) DESC, b;
a	b
s	0
	1
DROP TABLE t1;
"END ctype_german.inc"
CREATE TABLE t1 AS
SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d;
ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b);
INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5));
INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10));
INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11));
INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12));
SELECT hex(concat(repeat(0xF1F2, 10), '%'));
hex(concat(repeat(0xF1F2, 10), '%'))
F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F20025
3 rows expected
SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');
a	hex(b)	c
2	F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2	
3	F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2	
4	F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2	
DROP TABLE t1;
#
# MDEV-4842 STR_TO_DATE does not work with UCS2/UTF16/UTF32
#
SELECT @@character_set_connection, HEX(CAST(_utf8'÷' AS CHAR));
@@character_set_connection	HEX(CAST(_utf8'÷' AS CHAR))
ucs2	00F7
SELECT STR_TO_DATE(CAST(_utf8'2001÷01÷01' AS CHAR),CAST(_utf8'%Y÷%m÷%d' AS CHAR));
STR_TO_DATE(CAST(_utf8'2001÷01÷01' AS CHAR),CAST(_utf8'%Y÷%m÷%d' AS CHAR))
2001-01-01
CREATE TABLE t1 AS SELECT REPEAT(' ', 64) AS subject, REPEAT(' ',64) AS pattern LIMIT 0;
SHOW COLUMNS FROM t1;
Field	Type	Null	Key	Default	Extra
subject	varchar(64)	NO			
pattern	varchar(64)	NO			
INSERT INTO t1 VALUES (_utf8'2001÷01÷01',_utf8'%Y÷%m÷%d');
SELECT HEX(subject),HEX(pattern),STR_TO_DATE(subject, pattern) FROM t1;
HEX(subject)	HEX(pattern)	STR_TO_DATE(subject, pattern)
003200300030003100F70030003100F700300031	0025005900F70025006D00F700250064	2001-01-01 00:00:00.000000
DROP TABLE t1;
SET NAMES latin1;
SET collation_connection='ucs2_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)
ucs2_bin	00610009
ucs2_bin	0061
ucs2_bin	00610020
drop table t1;
#
# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP
#
SELECT @@collation_connection;
@@collation_connection
ucs2_bin
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP;
i
1
1
SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP;
i
1
1
DROP TABLE t1;
#
# MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations
#
SELECT @@collation_connection;
@@collation_connection
ucs2_bin
CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0;
INSERT INTO t1 (a) VALUES ("a");
INSERT INTO t1 (a) VALUES ("b");
INSERT INTO t1 (a) VALUES ("c");
INSERT INTO t1 (a) VALUES ("d");
INSERT INTO t1 (a) VALUES ("e");
INSERT INTO t1 (a) VALUES ("f");
INSERT INTO t1 (a) VALUES ("g");
INSERT INTO t1 (a) VALUES ("h");
INSERT INTO t1 (a) VALUES ("i");
INSERT INTO t1 (a) VALUES ("j");
INSERT INTO t1 (a) VALUES ("k");
INSERT INTO t1 (a) VALUES ("l");
INSERT INTO t1 (a) VALUES ("m");
SELECT * FROM t1 ORDER BY LOWER(a);
a
a
b
c
d
e
f
g
h
i
j
k
l
m
SELECT * FROM t1 ORDER BY LOWER(a) DESC;
a
m
l
k
j
i
h
g
f
e
d
c
b
a
DROP TABLE t1;
select @@collation_connection;
@@collation_connection
ucs2_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 t1 AS
SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d;
ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b);
INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5));
INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10));
INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11));
INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12));
SELECT hex(concat(repeat(0xF1F2, 10), '%'));
hex(concat(repeat(0xF1F2, 10), '%'))
F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F20025
3 rows expected
SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');
a	hex(b)	c
2	F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2	
3	F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2	
4	F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2	
DROP TABLE t1;
select hex(substr(_ucs2 0x00e400e50068,1));
hex(substr(_ucs2 0x00e400e50068,1))
00E400E50068
select hex(substr(_ucs2 0x00e400e50068,2));
hex(substr(_ucs2 0x00e400e50068,2))
00E50068
select hex(substr(_ucs2 0x00e400e50068,3));
hex(substr(_ucs2 0x00e400e50068,3))
0068
select hex(substr(_ucs2 0x00e400e50068,-1));
hex(substr(_ucs2 0x00e400e50068,-1))
0068
select hex(substr(_ucs2 0x00e400e50068,-2));
hex(substr(_ucs2 0x00e400e50068,-2))
00E50068
select hex(substr(_ucs2 0x00e400e50068,-3));
hex(substr(_ucs2 0x00e400e50068,-3))
00E400E50068
SET NAMES latin1;
SET collation_connection='ucs2_swedish_ci';
CREATE TABLE t1 (Field1 int(10) default '0');
INSERT INTO t1 VALUES ('-1');
SELECT * FROM t1;
Field1
-1
DROP TABLE t1;
CREATE TABLE t1 (Field1 int(10) unsigned default '0');
INSERT INTO t1 VALUES ('-1');
Warnings:
Warning	1264	Out of range value for column 'Field1' at row 1
DROP TABLE t1;
SET NAMES latin1;
SELECT CONVERT(103, CHAR(50) UNICODE);
CONVERT(103, CHAR(50) UNICODE)
103
SELECT CONVERT(103.0, CHAR(50) UNICODE);
CONVERT(103.0, CHAR(50) UNICODE)
103.0
SELECT CONVERT(-103, CHAR(50) UNICODE);
CONVERT(-103, CHAR(50) UNICODE)
-103
SELECT CONVERT(-103.0, CHAR(50) UNICODE);
CONVERT(-103.0, CHAR(50) UNICODE)
-103.0
CREATE TABLE t1 (
a varchar(255) NOT NULL default '',
KEY a (a)
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE ucs2_general_ci;
insert into t1 values (0x803d);
insert into t1 values (0x005b);
select hex(a) from t1;
hex(a)
005B
803D
drop table t1;
create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB;
insert into t1 values('a');
create index t1f1 on t1(f1);
select f1 from t1 where f1 like 'a%';
f1
a
drop table t1;
create table t1 (utext varchar(20) character set ucs2);
insert into t1 values ("lily");
insert into t1 values ("river");
prepare stmt from 'select utext from t1 where utext like ?';
set @param1='%%';
execute stmt using @param1;
utext
lily
river
execute stmt using @param1;
utext
lily
river
select utext from t1 where utext like '%%';
utext
lily
river
drop table t1;
deallocate prepare stmt;
create table t1 (
a char(10) unicode not null, 
index a (a)
) engine=myisam;
insert into t1 values (repeat(0x201f, 10));
insert into t1 values (repeat(0x2020, 10));
insert into t1 values (repeat(0x2021, 10));
explain select hex(a) from t1 order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	20	NULL	3	Using index
select hex(a) from t1 order by a;
hex(a)
201F201F201F201F201F201F201F201F201F201F
2020202020202020202020202020202020202020
2021202120212021202120212021202120212021
alter table t1 drop index a;
select hex(a) from t1 order by a;
hex(a)
201F201F201F201F201F201F201F201F201F201F
2020202020202020202020202020202020202020
2021202120212021202120212021202120212021
drop table t1;
CREATE TABLE t1 (id int, s char(5) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci);
INSERT INTO t1 VALUES (1, 'ZZZZZ'), (1, 'ZZZ'), (2, 'ZZZ'), (2, 'ZZZZZ');
SELECT id, MIN(s) FROM t1 GROUP BY id;
id	MIN(s)
1	ZZZ
2	ZZZ
DROP TABLE t1;
drop table if exists bug20536;
set names latin1;
create table bug20536 (id bigint not null auto_increment primary key, name
varchar(255) character set ucs2 not null);
insert into `bug20536` (`id`,`name`) values (1, _latin1 x'7465737431'), (2, "'test\\_2'");
select md5(name) from bug20536;
md5(name)
f4b7ce8b45a20e3c4e84bef515d1525c
48d95db0d8305c2fe11548a3635c9385
select sha1(name) from bug20536;
sha1(name)
e0b52f38deddb9f9e8d5336b153592794cb49baf
677d4d505355eb5b0549b865fcae4b7f0c28aef5
select make_set(3, name, upper(name)) from bug20536;
make_set(3, name, upper(name))
test1,TEST1
'test\_2','TEST\_2'
select export_set(5, name, upper(name)) from bug20536;
export_set(5, name, upper(name))
test1,TEST1,test1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1,TEST1
'test\_2','TEST\_2','test\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2','TEST\_2'
select export_set(5, name, upper(name), ",", 5) from bug20536;
export_set(5, name, upper(name), ",", 5)
test1,TEST1,test1,TEST1,TEST1
'test\_2','TEST\_2','test\_2','TEST\_2','TEST\_2'
CREATE TABLE t1 (
status enum('active','passive') collate latin1_general_ci 
NOT NULL default 'passive'
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `status` enum('active','passive') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'passive'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE t1 ADD a int NOT NULL AFTER status;
CREATE TABLE t2 (
status enum('active','passive') collate ucs2_turkish_ci 
NOT NULL default 'passive'
);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `status` enum('active','passive') CHARACTER SET ucs2 COLLATE ucs2_turkish_ci NOT NULL DEFAULT 'passive'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE t2 ADD a int NOT NULL AFTER status;
DROP TABLE t1,t2;
select password(name) from bug20536;
password(name)
*286C12C0F32248BD65B30EE65F3ECFB2AA3F7849
*947A5674312754578F132655C74A11533B105FF6
select old_password(name) from bug20536;
old_password(name)
10e155cb44e2adb5
14e500b131773991
select quote(name) from bug20536;
quote(name)
'test1'
'\'test\\_2\''
drop table bug20536;
set names ucs2;
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
set names ucs2 collate ucs2_bin;
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
set character_set_client= ucs2;
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
set character_set_client= concat('ucs', substr('2', 1));
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci);
INSERT INTO t1 VALUES('abcd');
SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE);
a
abcd
DROP TABLE t1;
End of 4.1 tests
CREATE TABLE t1 (a varchar(64) character set ucs2, b decimal(10,3));
INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0);
update t1 set b=a;
SELECT * FROM t1;
a	b
1.1	1.100
2.1	2.100
DROP TABLE t1;
create table t1 (utext varchar(20) character set ucs2);
insert into t1 values ("lily");
insert into t1 values ("river");
prepare stmt from 'select utext from t1 where utext like ?';
set @param1='%%';
execute stmt using @param1;
utext
lily
river
execute stmt using @param1;
utext
lily
river
select utext from t1 where utext like '%%';
utext
lily
river
drop table t1;
deallocate prepare stmt;
set names latin1;
set character_set_connection=ucs2;
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
soundex('')	soundex('he')	soundex('hello all folks')	soundex('#3556 in bugdb')
	H000	H4142	I51231
select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb'));
hex(soundex(''))	hex(soundex('he'))	hex(soundex('hello all folks'))	hex(soundex('#3556 in bugdb'))
	0048003000300030	00480034003100340032	004900350031003200330031
select 'mood' sounds like 'mud';
'mood' sounds like 'mud'
1
select hex(soundex(_ucs2 0x041004110412));
hex(soundex(_ucs2 0x041004110412))
0410003000300030
select hex(soundex(_ucs2 0x00BF00C0));
hex(soundex(_ucs2 0x00BF00C0))
00C0003000300030
set names latin1;
create table t1(a blob, b text charset utf8, c text charset ucs2);
select data_type, character_octet_length, character_maximum_length
from information_schema.columns where table_name='t1';
data_type	character_octet_length	character_maximum_length
blob	65535	65535
text	65535	65535
text	65535	32767
drop table t1;
create table t1 (a char(1) character set ucs2);
insert into t1 values ('a'),('b'),('c');
select hex(group_concat(a)) from t1;
hex(group_concat(a))
0061002C0062002C0063
select collation(group_concat(a)) from t1;
collation(group_concat(a))
ucs2_general_ci
drop table t1;
set names latin1;
create table t1 (a char(1) character set latin1);
insert into t1 values ('a'),('b'),('c');
set character_set_connection=ucs2;
select hex(group_concat(a separator ',')) from t1;
hex(group_concat(a separator ','))
612C622C63
select collation(group_concat(a separator ',')) from t1;
collation(group_concat(a separator ','))
latin1_swedish_ci
drop table t1;
set names latin1;
create table t1 (s1 char(1) character set ascii, s2 char(1) character set ucs2);
insert into t1 (s1) values (0x7f);
update t1 set s2 = s1;
select hex(s2) from t1;
hex(s2)
007F
select hex(convert(s1 using latin1)) from t1;
hex(convert(s1 using latin1))
7F
drop table t1;
create table t1 (a varchar(15) character set ascii not null, b int);
insert into t1 values ('a',1);
select concat(a,if(b<10,_ucs2 0x0061,_ucs2 0x0062)) from t1;
concat(a,if(b<10,_ucs2 0x0061,_ucs2 0x0062))
aa
select concat(a,if(b>10,_ucs2 0x0061,_ucs2 0x0062)) from t1;
concat(a,if(b>10,_ucs2 0x0061,_ucs2 0x0062))
ab
select * from t1 where a=if(b<10,_ucs2 0x0061,_ucs2 0x0062);
a	b
a	1
select * from t1 where a=if(b>10,_ucs2 0x0061,_ucs2 0x0062);
a	b
select concat(a,if(b<10,_ucs2 0x00C0,_ucs2 0x0062)) from t1;
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat'
select concat(a,if(b>10,_ucs2 0x00C0,_ucs2 0x0062)) from t1;
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat'
select concat(a,if(b<10,_ucs2 0x0062,_ucs2 0x00C0)) from t1;
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat'
select concat(a,if(b>10,_ucs2 0x0062,_ucs2 0x00C0)) from t1;
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat'
select * from t1 where a=if(b<10,_ucs2 0x00C0,_ucs2 0x0062);
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '='
select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0);
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '='
drop table t1;
CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET UCS2);
INSERT INTO t1 VALUES ('a');
SET @@sql_mode=pad_char_to_full_length;
SELECT HEX(s1) FROM t1;
HEX(s1)
00610020002000200020
SET @@sql_mode=default;
SELECT HEX(s1) FROM t1;
HEX(s1)
0061
DROP TABLE t1;
set collation_connection=ucs2_general_ci;
drop table if exists t1;
create table t1 as
select repeat(' ', 64) as s1, repeat(' ',64) as s2
union
select null, null;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `s1` varchar(64) CHARACTER SET ucs2 DEFAULT NULL,
  `s2` varchar(64) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
delete from t1;
insert into t1 values('aaa','aaa');
insert into t1 values('aaa|qqq','qqq');
insert into t1 values('gheis','^[^a-dXYZ]+$');
insert into t1 values('aab','^aa?b');
insert into t1 values('Baaan','^Ba*n');
insert into t1 values('aaa','qqq|aaa');
insert into t1 values('qqq','qqq|aaa');
insert into t1 values('bbb','qqq|aaa');
insert into t1 values('bbb','qqq');
insert into t1 values('aaa','aba');
insert into t1 values(null,'abc');
insert into t1 values('def',null);
insert into t1 values(null,null);
insert into t1 values('ghi','ghi[');
select HIGH_PRIORITY s1 regexp s2 from t1;
s1 regexp s2
0
0
0
1
1
1
1
1
1
1
NULL
NULL
NULL
NULL
drop table t1;
set names latin1;
select hex(char(0x41 using ucs2));
hex(char(0x41 using ucs2))
0041
SET character_set_connection=ucs2;
SELECT CHARSET(DAYNAME(19700101));
CHARSET(DAYNAME(19700101))
ucs2
SELECT CHARSET(MONTHNAME(19700101));
CHARSET(MONTHNAME(19700101))
ucs2
SELECT LOWER(DAYNAME(19700101));
LOWER(DAYNAME(19700101))
thursday
SELECT LOWER(MONTHNAME(19700101));
LOWER(MONTHNAME(19700101))
january
SELECT UPPER(DAYNAME(19700101));
UPPER(DAYNAME(19700101))
THURSDAY
SELECT UPPER(MONTHNAME(19700101));
UPPER(MONTHNAME(19700101))
JANUARY
SELECT HEX(MONTHNAME(19700101));
HEX(MONTHNAME(19700101))
004A0061006E0075006100720079
SELECT HEX(DAYNAME(19700101));
HEX(DAYNAME(19700101))
00540068007500720073006400610079
SET LC_TIME_NAMES=ru_RU;
SET NAMES utf8;
SET character_set_connection=ucs2;
SELECT CHARSET(DAYNAME(19700101));
CHARSET(DAYNAME(19700101))
ucs2
SELECT CHARSET(MONTHNAME(19700101));
CHARSET(MONTHNAME(19700101))
ucs2
SELECT LOWER(DAYNAME(19700101));
LOWER(DAYNAME(19700101))
четверг
SELECT LOWER(MONTHNAME(19700101));
LOWER(MONTHNAME(19700101))
января
SELECT UPPER(DAYNAME(19700101));
UPPER(DAYNAME(19700101))
ЧЕТВЕРГ
SELECT UPPER(MONTHNAME(19700101));
UPPER(MONTHNAME(19700101))
ЯНВАРЯ
SELECT HEX(MONTHNAME(19700101));
HEX(MONTHNAME(19700101))
042F043D043204300440044F
SELECT HEX(DAYNAME(19700101));
HEX(DAYNAME(19700101))
0427043504420432043504400433
SET character_set_connection=latin1;
#
# Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817
#
CREATE TABLE t1 (a CHAR(1) CHARSET ascii, b CHAR(1) CHARSET latin1);
CREATE VIEW v1 AS SELECT 1 from t1
WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1'));
DROP VIEW v1;
DROP TABLE t1;
#
# Bug#59648 my_strtoll10_mb2: Assertion `(*endptr - s) % 2 == 0' failed.
#
SELECT HEX(CHAR(COALESCE(NULL, CHAR(COUNT('%s') USING ucs2), 1, @@global.license, NULL) USING cp850));
HEX(CHAR(COALESCE(NULL, CHAR(COUNT('%s') USING ucs2), 1, @@global.license, NULL) USING cp850))
00
SELECT CONVERT(QUOTE(CHAR(0xf5 using ucs2)), SIGNED);
CONVERT(QUOTE(CHAR(0xf5 using ucs2)), SIGNED)
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: ''õ''
End of 5.0 tests
#
# Start of 5.1 tests
#
SET NAMES utf8;
CREATE TABLE t1 (
a varchar(10) CHARACTER SET ucs2 COLLATE ucs2_czech_ci,
key(a)
);
INSERT INTO t1 VALUES
('aa'),('bb'),('cc'),('dd'),('ee'),('ff'),('gg'),('hh'),('ii'),
('jj'),('kk'),('ll'),('mm'),('nn'),('oo'),('pp'),('rr'),('ss'),
('tt'),('uu'),('vv'),('ww'),('xx'),('yy'),('zz');
INSERT INTO t1 VALUES ('ca'),('cz'),('ch');
INSERT INTO t1 VALUES ('da'),('dz'), (X'0064017E');
EXPLAIN SELECT * FROM t1 WHERE a LIKE 'b%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	23	NULL	1	Using where; Using index
EXPLAIN SELECT * FROM t1 WHERE a LIKE 'c%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	23	NULL	30	Using where; Using index
SELECT * FROM t1 WHERE a LIKE 'c%';
a
ca
cc
cz
ch
EXPLAIN SELECT * FROM t1 WHERE a LIKE 'ch%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	23	NULL	1	Using where; Using index
SELECT * FROM t1 WHERE a LIKE 'ch%';
a
ch
ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci;
EXPLAIN SELECT * FROM t1 WHERE a LIKE 'd%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	23	NULL	30	Using where; Using index
SELECT hex(concat('d',_ucs2 0x017E,'%'));
hex(concat('d',_ucs2 0x017E,'%'))
0064017E0025
EXPLAIN SELECT * FROM t1 WHERE a LIKE concat('d',_ucs2 0x017E,'%');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	23	NULL	1	Using where; Using index
SELECT hex(a) FROM t1 WHERE a LIKE concat('D',_ucs2 0x017E,'%');
hex(a)
0064017E
DROP TABLE t1;
#
# End of 5.1 tests
#
#
# Start of 5.5 tests
#
SET NAMES latin1;
SET collation_connection=ucs2_general_ci;
SET TIME_ZONE = _latin1 '+03:00';
#
# Start of WL#2649 Number-to-string conversions
#
select hex(concat(1));
hex(concat(1))
0031
create table t1 as select concat(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
0031
drop table t1;
select hex(concat(18446744073709551615));
hex(concat(18446744073709551615))
00310038003400340036003700340034003000370033003700300039003500350031003600310035
create table t1 as select concat(18446744073709551615) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(20) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
00310038003400340036003700340034003000370033003700300039003500350031003600310035
drop table t1;
select hex(concat(1.1));
hex(concat(1.1))
0031002E0031
create table t1 as select concat(1.1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
0031002E0031
drop table t1;
select hex(concat('a', 1+2)), charset(concat(1+2));
hex(concat('a', 1+2))	charset(concat(1+2))
00610033	ucs2
create table t1 as select concat(1+2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1-2));
hex(concat(1-2))
002D0031
create table t1 as select concat(1-2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1*2));
hex(concat(1*2))
0032
create table t1 as select concat(1*2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1/2));
hex(concat(1/2))
0030002E0035003000300030
create table t1 as select concat(1/2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(7) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1 div 2));
hex(concat(1 div 2))
0030
create table t1 as select concat(1 div 2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1 % 2));
hex(concat(1 % 2))
0031
create table t1 as select concat(1 % 2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(-1));
hex(concat(-1))
002D0031
create table t1 as select concat(-1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(-(1+2)));
hex(concat(-(1+2)))
002D0033
create table t1 as select concat(-(1+2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1|2));
hex(concat(1|2))
0033
create table t1 as select concat(1|2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1&2));
hex(concat(1&2))
0030
create table t1 as select concat(1&2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(bit_count(12)));
hex(concat(bit_count(12)))
0032
create table t1 as select concat(bit_count(12)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(2<<1));
hex(concat(2<<1))
0034
create table t1 as select concat(2<<1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(2>>1));
hex(concat(2>>1))
0031
create table t1 as select concat(2>>1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(~0));
hex(concat(~0))
00310038003400340036003700340034003000370033003700300039003500350031003600310035
create table t1 as select concat(~0) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(3^2));
hex(concat(3^2))
0031
create table t1 as select concat(3^2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(abs(-2)));
hex(concat(abs(-2)))
0032
create table t1 as select concat(abs(-2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(exp(2)),1));
hex(left(concat(exp(2)),1))
0037
create table t1 as select concat(exp(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log(2)),1));
hex(left(concat(log(2)),1))
0030
create table t1 as select concat(log(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log2(2)),1));
hex(left(concat(log2(2)),1))
0031
create table t1 as select concat(log2(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log10(2)),1));
hex(left(concat(log10(2)),1))
0030
create table t1 as select concat(log10(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(sqrt(2)),1));
hex(left(concat(sqrt(2)),1))
0031
create table t1 as select concat(sqrt(2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(pow(2,2)),1));
hex(left(concat(pow(2,2)),1))
0034
create table t1 as select concat(pow(2,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(acos(0.5)),1));
hex(left(concat(acos(0.5)),1))
0031
create table t1 as select concat(acos(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(asin(0.5)),1));
hex(left(concat(asin(0.5)),1))
0030
create table t1 as select concat(asin(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(atan(0.5)),1));
hex(left(concat(atan(0.5)),1))
0030
create table t1 as select concat(atan(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(cos(0.5)),1));
hex(left(concat(cos(0.5)),1))
0030
create table t1 as select concat(cos(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(sin(0.5)),1));
hex(left(concat(sin(0.5)),1))
0030
create table t1 as select concat(sin(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(tan(0.5)),1));
hex(left(concat(tan(0.5)),1))
0030
create table t1 as select concat(tan(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(degrees(0)));
hex(concat(degrees(0)))
0030
create table t1 as select concat(degrees(0)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(radians(0)));
hex(concat(radians(0)))
0030
create table t1 as select concat(radians(0)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ceiling(0.5)));
hex(concat(ceiling(0.5)))
0031
create table t1 as select concat(ceiling(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(floor(0.5)));
hex(concat(floor(0.5)))
0030
create table t1 as select concat(floor(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(round(0.5)));
hex(concat(round(0.5)))
0031
create table t1 as select concat(round(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sign(0.5)));
hex(concat(sign(0.5)))
0031
create table t1 as select concat(sign(0.5)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(rand()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(length('a')));
hex(concat(length('a')))
0032
create table t1 as select concat(length('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(char_length('a')));
hex(concat(char_length('a')))
0031
create table t1 as select concat(char_length('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(bit_length('a')));
hex(concat(bit_length('a')))
00310036
create table t1 as select concat(bit_length('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(coercibility('a')));
hex(concat(coercibility('a')))
0034
create table t1 as select concat(coercibility('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(locate('a','a')));
hex(concat(locate('a','a')))
0031
create table t1 as select concat(locate('a','a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(11) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(field('c','a','b','c')));
hex(concat(field('c','a','b','c')))
0033
create table t1 as select concat(field('c','a','b','c')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ascii(61)));
hex(concat(ascii(61)))
00350034
create table t1 as select concat(ascii(61)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ord(61)));
hex(concat(ord(61)))
00350034
create table t1 as select concat(ord(61)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(find_in_set('b','a,b,c,d')));
hex(concat(find_in_set('b','a,b,c,d')))
0032
create table t1 as select concat(find_in_set('b','a,b,c,d')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select md5('a'), hex(md5('a'));
md5('a')	hex(md5('a'))
760f753576f2955b0074758acb4d5fa6	00370036003000660037003500330035003700360066003200390035003500620030003000370034003700350038006100630062003400640035006600610036
create table t1 as select md5('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(32) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select old_password('a'), hex(old_password('a'));
old_password('a')	hex(old_password('a'))
0705298948d1f92f	0030003700300035003200390038003900340038006400310066003900320066
create table t1 as select old_password('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(16) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select password('a'), hex(password('a'));
password('a')	hex(password('a'))
*9A48A662559C49521B25C43077059DD109FBD84A	002A0039004100340038004100360036003200350035003900430034003900350032003100420032003500430034003300300037003700300035003900440044003100300039004600420044003800340041
create table t1 as select password('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(41) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select sha('a'), hex(sha('a'));
sha('a')	hex(sha('a'))
3106600e0327ca77371f2526df794ed84322585c	0033003100300036003600300030006500300033003200370063006100370037003300370031006600320035003200360064006600370039003400650064003800340033003200320035003800350063
create table t1 as select sha('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(40) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select sha1('a'), hex(sha1('a'));
sha1('a')	hex(sha1('a'))
3106600e0327ca77371f2526df794ed84322585c	0033003100300036003600300030006500300033003200370063006100370037003300370031006600320035003200360064006600370039003400650064003800340033003200320035003800350063
create table t1 as select sha1('a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(40) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('-1' as signed)));
hex(concat(cast('-1' as signed)))
002D0031
create table t1 as select concat(cast('-1' as signed)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('1' as unsigned)));
hex(concat(cast('1' as unsigned)))
0031
create table t1 as select concat(cast('1' as unsigned)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast(1/2 as decimal(5,5))));
hex(concat(cast(1/2 as decimal(5,5))))
0030002E00350030003000300030
create table t1 as select concat(cast(1/2 as decimal(5,5))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(7) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('2001-01-02 03:04:05' as date)));
hex(concat(cast('2001-01-02 03:04:05' as date)))
0032003000300031002D00300031002D00300032
create table t1 as select concat(cast('2001-01-02 03:04:05' as date)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
2001-01-02
drop table t1;
select hex(concat(cast('2001-01-02 03:04:05' as time)));
hex(concat(cast('2001-01-02 03:04:05' as time)))
00300033003A00300034003A00300035
create table t1 as select concat(cast('2001-01-02 03:04:05' as time)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
03:04:05
drop table t1;
select hex(concat(cast('2001-01-02' as datetime)));
hex(concat(cast('2001-01-02' as datetime)))
0032003000300031002D00300031002D00300032002000300030003A00300030003A00300030
create table t1 as select concat(cast('2001-01-02' as datetime)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
2001-01-02 00:00:00
drop table t1;
select hex(concat(least(1,2)));
hex(concat(least(1,2)))
0031
create table t1 as select concat(least(1,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(greatest(1,2)));
hex(concat(greatest(1,2)))
0032
create table t1 as select concat(greatest(1,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(case when 11 then 22 else 33 end));
hex(concat(case when 11 then 22 else 33 end))
00320032
create table t1 as select concat(case when 11 then 22 else 33 end) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(coalesce(1,2)));
hex(concat(coalesce(1,2)))
0031
create table t1 as select concat(coalesce(1,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat_ws(1,2,3));
hex(concat_ws(1,2,3))
003200310033
create table t1 as select concat_ws(1,2,3) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(group_concat(1,2,3));
hex(group_concat(1,2,3))
003100320033
create table t1 as select group_concat(1,2,3) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(512) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select 1 as c1 union select 'a';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select hex(c1) from t1 order by c1;
hex(c1)
0031
0061
drop table t1;
create table t1 as select concat(last_insert_id()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(benchmark(0,0)));
hex(concat(benchmark(0,0)))
0030
create table t1 as select concat(benchmark(0,0)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sleep(0)));
hex(concat(sleep(0)))
0030
create table t1 as select concat(sleep(0)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(is_free_lock('xxxx')));
hex(concat(is_free_lock('xxxx')))
0031
create table t1 as select concat(is_free_lock('xxxx')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(is_used_lock('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(release_lock('a')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(crc32('')));
hex(concat(crc32('')))
0030
create table t1 as select concat(crc32('')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(uncompressed_length('')));
hex(concat(uncompressed_length('')))
0030
create table t1 as select concat(uncompressed_length('')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(connection_id()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(inet_aton('127.1.1.1')));
hex(concat(inet_aton('127.1.1.1')))
0032003100330030003700370032003200320035
create table t1 as select concat(inet_aton('127.1.1.1')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(inet_ntoa(2130772225)));
hex(concat(inet_ntoa(2130772225)))
003100320037002E0031002E0031002E0031
create table t1 as select concat(inet_ntoa(2130772225)) as c1;
select * from t1;
c1
127.1.1.1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(31) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select 1;
1
1
select hex(concat(row_count()));
hex(concat(row_count()))
002D0031
create table t1 as select concat(row_count()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(found_rows()));
hex(concat(found_rows()))
0030
create table t1 as select concat(found_rows()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(uuid_short()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(uuid()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select coercibility(uuid()), coercibility(cast('a' as char character set latin1));
coercibility(uuid())	coercibility(cast('a' as char character set latin1))
4	2
select charset(concat(uuid(), cast('a' as char character set latin1)));
charset(concat(uuid(), cast('a' as char character set latin1)))
latin1
create table t1 as select concat(uuid(), cast('a' as char character set latin1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(37) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(master_pos_wait('non-existent',0,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=1));
hex(concat(@a1:=1))
0031
create table t1 as select concat(@a2:=2) as c1, @a3:=3 as c2;
select hex(c1) from t1;
hex(c1)
0032
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT '',
  `c2` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @a2=1;
select hex(concat(@a2));
hex(concat(@a2))
0031
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
0031
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(20) CHARACTER SET ucs2 DEFAULT NULL,
  `c2` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=sqrt(1)));
hex(concat(@a1:=sqrt(1)))
0031
create table t1 as select concat(@a2:=sqrt(1)) as c1, @a3:=sqrt(1) as c2;
select hex(c1) from t1;
hex(c1)
0031
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL,
  `c2` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @a2=sqrt(1);
select hex(concat(@a2));
hex(concat(@a2))
0031
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
0031
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL,
  `c2` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=1.1));
hex(concat(@a1:=1.1))
0031002E0031
create table t1 as select concat(@a2:=1.1) as c1, @a3:=1.1 as c2;
select hex(c1) from t1;
hex(c1)
0031002E0031
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 NOT NULL DEFAULT '',
  `c2` decimal(2,1) NOT NULL DEFAULT '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @a2=1.1;
select hex(concat(@a2));
hex(concat(@a2))
0031002E0031
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
0031002E0031
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(83) CHARACTER SET ucs2 DEFAULT NULL,
  `c2` decimal(65,30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@@ft_max_word_len));
hex(concat(@@ft_max_word_len))
00380034
create table t1 as select concat(@@ft_max_word_len) as c1;
select hex(c1) from t1;
hex(c1)
00380034
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a'='a' IS TRUE));
hex(concat('a'='a' IS TRUE))
0031
create table t1 as select concat('a'='a' IS TRUE) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a'='a' IS NOT TRUE));
hex(concat('a'='a' IS NOT TRUE))
0030
create table t1 as select concat('a'='a' IS NOT TRUE) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NOT 'a'='a'));
hex(concat(NOT 'a'='a'))
0030
create table t1 as select concat(NOT 'a'='a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' IS NULL));
hex(concat('a' IS NULL))
0030
create table t1 as select concat('a' IS NULL) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' IS NOT NULL));
hex(concat('a' IS NOT NULL))
0031
create table t1 as select concat('a' IS NOT NULL) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' rlike 'a'));
hex(concat('a' rlike 'a'))
0031
create table t1 as select concat('a' IS NOT NULL) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(strcmp('a','b')));
hex(concat(strcmp('a','b')))
002D0031
create table t1 as select concat(strcmp('a','b')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' like 'a'));
hex(concat('a' like 'a'))
0031
create table t1 as select concat('a' like 'b') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' between 'b' and 'c'));
hex(concat('a' between 'b' and 'c'))
0030
create table t1 as select concat('a' between 'b' and 'c') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' in ('a','b')));
hex(concat('a' in ('a','b')))
0031
create table t1 as select concat('a' in ('a','b')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(interval(23, 1, 15, 17, 30, 44, 200)));
hex(concat(interval(23, 1, 15, 17, 30, 44, 200)))
0033
create table t1 as select concat(interval(23, 1, 15, 17, 30, 44, 200)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a varchar(10), fulltext key(a));
insert into t1 values ('a');
select hex(concat(match (a) against ('a'))) from t1;
hex(concat(match (a) against ('a')))
0030
create table t2 as select concat(match (a) against ('a')) as a from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
select hex(ifnull(1,'a'));
hex(ifnull(1,'a'))
0031
create table t1 as select ifnull(1,'a') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ifnull(1,1)));
hex(concat(ifnull(1,1)))
0031
create table t1 as select concat(ifnull(1,1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ifnull(1.1,1.1)));
hex(concat(ifnull(1.1,1.1)))
0031002E0031
create table t1 as select concat(ifnull(1.1,1.1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(if(1,'b',1));
hex(if(1,'b',1))
0062
create table t1 as select if(1,'b',1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(if(1,1,'b'));
hex(if(1,1,'b'))
0031
create table t1 as select if(1,1,'b') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(if(1,1,1)));
hex(concat(if(1,1,1)))
0031
create table t1 as select concat(if(1,1,1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(nullif(1,2)));
hex(concat(nullif(1,2)))
0031
create table t1 as select concat(nullif(1,2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)'))));
hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)'))))
0031
create table t1 as select concat(Dimension(GeomFromText('LINSTRING(0 0,10 10)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))));
hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))))
0032
create table t1 as select concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))));
hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))))
0032
create table t1 as select concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))));
hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))))
0030
create table t1 as select concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))));
hex(concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))))
0031
create table t1 as select concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(IsEmpty(GeomFromText('POINT(1 1)'))));
hex(concat(IsEmpty(GeomFromText('POINT(1 1)'))))
0030
create table t1 as select concat(IsEmpty(GeomFromText('Point(1 1)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(IsSimple(GeomFromText('POINT(1 1)'))));
hex(concat(IsSimple(GeomFromText('POINT(1 1)'))))
0031
create table t1 as select concat(IsSimple(GeomFromText('Point(1 1)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))));
hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))))
0030
create table t1 as select concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))));
hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))))
0031
create table t1 as select concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))) as c1;
drop table t1;
select hex(concat(x(GeomFromText('Point(1 2)'))));
hex(concat(x(GeomFromText('Point(1 2)'))))
0031
create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(y(GeomFromText('Point(1 2)'))));
hex(concat(y(GeomFromText('Point(1 2)'))))
0032
create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(GLength(GeomFromText('LineString(1 2,2 2)'))));
hex(concat(GLength(GeomFromText('LineString(1 2,2 2)'))))
0031
create table t1 as select concat(GLength(GeomFromText('LineString(1 2, 2 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))));
hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))))
0031
create table t1 as select concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(GeometryType(GeomFromText('Point(1 2)'))));
hex(concat(GeometryType(GeomFromText('Point(1 2)'))))
0050004F0049004E0054
create table t1 as select concat(GeometryType(GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(20) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(AsText(GeomFromText('Point(1 2)'))));
hex(concat(AsText(GeomFromText('Point(1 2)'))))
0050004F0049004E005400280031002000320029
create table t1 as select concat(AsText(GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` longtext CHARACTER SET ucs2
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(period_add(200902, 2)));
hex(concat(period_add(200902, 2)))
003200300030003900300034
create table t1 as select concat(period_add(200902, 2)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(6) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(period_diff(200902, 200802)));
hex(concat(period_diff(200902, 200802)))
00310032
create table t1 as select concat(period_add(200902, 200802)) as c1;
Warnings:
Warning	1265	Data truncated for column 'c1' at row 1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(6) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(to_days(20090224)));
hex(concat(to_days(20090224)))
003700330033003800320037
create table t1 as select concat(to_days(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(6) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofmonth(20090224)));
hex(concat(dayofmonth(20090224)))
00320034
create table t1 as select concat(dayofmonth(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofyear(20090224)));
hex(concat(dayofyear(20090224)))
00350035
create table t1 as select concat(dayofyear(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(hour('10:11:12')));
hex(concat(hour('10:11:12')))
00310030
create table t1 as select concat(hour('10:11:12')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(minute('10:11:12')));
hex(concat(minute('10:11:12')))
00310031
create table t1 as select concat(minute('10:11:12')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(second('10:11:12')));
hex(concat(second('10:11:12')))
00310032
create table t1 as select concat(second('10:11:12')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(quarter(20090224)));
hex(concat(quarter(20090224)))
0031
create table t1 as select concat(quarter(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(week(20090224)));
hex(concat(week(20090224)))
0038
create table t1 as select concat(week(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(yearweek(20090224)));
hex(concat(yearweek(20090224)))
003200300030003900300038
create table t1 as select concat(yearweek(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(6) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(year(20090224)));
hex(concat(year(20090224)))
0032003000300039
create table t1 as select concat(year(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(weekday(20090224)));
hex(concat(weekday(20090224)))
0031
create table t1 as select concat(weekday(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofweek(20090224)));
hex(concat(dayofweek(20090224)))
0033
create table t1 as select concat(dayofweek(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(unix_timestamp(20090224)));
hex(concat(unix_timestamp(20090224)))
0031003200330035003400320032003800300030
create table t1 as select concat(unix_timestamp(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(time_to_sec('10:11:12')));
hex(concat(time_to_sec('10:11:12')))
00330036003600370032
create table t1 as select concat(time_to_sec('10:11:12')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(extract(year from 20090702)));
hex(concat(extract(year from 20090702)))
0032003000300039
create table t1 as select concat(extract(year from 20090702)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(microsecond('12:00:00.123456')));
hex(concat(microsecond('12:00:00.123456')))
003100320033003400350036
create table t1 as select concat(microsecond('12:00:00.123456')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(month(20090224)));
hex(concat(month(20090224)))
0032
create table t1 as select concat(month(20090224)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(last_day('2003-02-05')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select c1, hex(c1) from t1;
c1	hex(c1)
2003-02-28	0032003000300033002D00300032002D00320038
drop table t1;
create table t1 as select concat(from_days(730669)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select c1, hex(c1) from t1;
c1	hex(c1)
2000-07-03	0032003000300030002D00300037002D00300033
drop table t1;
create table t1 as select concat(curdate()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(utc_date()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(curtime()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select repeat('a',20) as c1 limit 0;
set timestamp=1216359724;
insert into t1 values (current_date);
insert into t1 values (current_time);
select c1, hex(c1) from t1;
c1	hex(c1)
2008-07-18	0032003000300038002D00300037002D00310038
08:42:04	00300038003A00340032003A00300034
drop table t1;
create table t1 as select concat(utc_time()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sec_to_time(2378)));
hex(concat(sec_to_time(2378)))
00300030003A00330039003A00330038
create table t1 as select concat(sec_to_time(2378)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')));
hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')))
00320034003A00300030003A00300030
create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(maketime(10,11,12)));
hex(concat(maketime(10,11,12)))
00310030003A00310031003A00310032
create table t1 as select concat(maketime(10,11,12)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(get_format(DATE,'USA'));
hex(get_format(DATE,'USA'))
0025006D002E00250064002E00250059
create table t1 as select get_format(DATE,'USA') as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(from_unixtime(1111885200)),4));
hex(left(concat(from_unixtime(1111885200)),4))
0032003000300035
create table t1 as select concat(from_unixtime(1111885200)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')));
hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')))
0032003000300033002D00310032002D00330031002000320030003A00300030003A00300030
create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day)));
hex(concat(date_add('2004-01-01 12:00:00', interval 1 day)))
0032003000300034002D00300031002D00300032002000310032003A00300030003A00300030
create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
2004-01-02 12:00:00
drop table t1;
select hex(concat(makedate(2009,1)));
hex(concat(makedate(2009,1)))
0032003000300039002D00300031002D00300031
create table t1 as select concat(makedate(2009,1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
2009-01-01
drop table t1;
create table t1 as select concat(now()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(19) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(utc_timestamp()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(19) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(sysdate()) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(19) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(addtime('00:00:00','11:22:33')));
hex(concat(addtime('00:00:00','11:22:33')))
00310031003A00320032003A00330033
create table t1 as select concat(addtime('00:00:00','11:22:33')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(subtime('23:59:59','11:22:33')));
hex(concat(subtime('23:59:59','11:22:33')))
00310032003A00330037003A00320036
create table t1 as select concat(subtime('23:59:59','11:22:33')) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(elt(1,2,3));
hex(elt(1,2,3))
0032
create table t1 as select elt(1,2,3) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(export_set(1,2,3,4,2));
hex(export_set(1,2,3,4,2))
003200340033
create table t1 as select export_set(1,2,3,4,2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(127) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(insert(1133,3,0,22));
hex(insert(1133,3,0,22))
003100310032003200330033
create table t1 as select insert(1133,3,0,22) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(6) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(lcase(123));
hex(lcase(123))
003100320033
create table t1 as select lcase(123) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(123,1));
hex(left(123,1))
0031
create table t1 as select left(123,1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(lower(123));
hex(lower(123))
003100320033
create table t1 as select lower(123) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(lpad(1,2,0));
hex(lpad(1,2,0))
00300031
create table t1 as select lpad(1,2,0) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(ltrim(1));
hex(ltrim(1))
0031
create table t1 as select ltrim(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(mid(1,1,1));
hex(mid(1,1,1))
0031
create table t1 as select mid(1,1,1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(repeat(1,2));
hex(repeat(1,2))
00310031
create table t1 as select repeat(1,2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(replace(1,1,2));
hex(replace(1,1,2))
0032
create table t1 as select replace(1,1,2) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(reverse(12));
hex(reverse(12))
00320031
create table t1 as select reverse(12) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(right(123,1));
hex(right(123,1))
0033
create table t1 as select right(123,1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(rpad(1,2,0));
hex(rpad(1,2,0))
00310030
create table t1 as select rpad(1,2,0) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(rtrim(1));
hex(rtrim(1))
0031
create table t1 as select rtrim(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(soundex(1));
hex(soundex(1))

create table t1 as select soundex(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(4) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(substring(1,1,1));
hex(substring(1,1,1))
0031
create table t1 as select substring(1,1,1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(trim(1));
hex(trim(1))
0031
create table t1 as select trim(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(ucase(1));
hex(ucase(1))
0031
create table t1 as select ucase(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(upper(1));
hex(upper(1))
0031
create table t1 as select upper(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select repeat(' ', 64) as a limit 0;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(64) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ("1.1"), ("2.1");
select a, hex(a) from t1;
a	hex(a)
1.1	0031002E0031
2.1	0032002E0031
update t1 set a= a + 0.1;
select a, hex(a) from t1;
a	hex(a)
1.2000000000000002	0031002E0032003000300030003000300030003000300030003000300030003000300032
2.2	0032002E0032
drop table t1;
create table t1 (a tinyint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(4) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a tinyint zerofill);
insert into t1 values (1), (10), (100);
select hex(concat(a)), a from t1;
hex(concat(a))	a
003000300031	001
003000310030	010
003100300030	100
drop table t1;
create table t1 (a tinyint(4) zerofill);
insert into t1 values (1), (10), (100);
select hex(concat(a)), a from t1;
hex(concat(a))	a
0030003000300031	0001
0030003000310030	0010
0030003100300030	0100
drop table t1;
create table t1 (a decimal(10,2));
insert into t1 values (123.45);
select hex(concat(a)) from t1;
hex(concat(a))
003100320033002E00340035
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(12) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a smallint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(6) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a smallint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a))	a
00300030003000300031	00001
00300030003000310030	00010
00300030003100300030	00100
00300031003000300030	01000
00310030003000300030	10000
drop table t1;
create table t1 (a mediumint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(9) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a mediumint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a))	a
00300030003000300030003000300031	00000001
00300030003000300030003000310030	00000010
00300030003000300030003100300030	00000100
00300030003000300031003000300030	00001000
00300030003000310030003000300030	00010000
drop table t1;
create table t1 (a int);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(11) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a int zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a))	a
0030003000300030003000300030003000300031	0000000001
0030003000300030003000300030003000310030	0000000010
0030003000300030003000300030003100300030	0000000100
0030003000300030003000300031003000300030	0000001000
0030003000300030003000310030003000300030	0000010000
drop table t1;
create table t1 (a bigint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(20) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a bigint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a))	a
00300030003000300030003000300030003000300030003000300030003000300030003000300031	00000000000000000001
00300030003000300030003000300030003000300030003000300030003000300030003000310030	00000000000000000010
00300030003000300030003000300030003000300030003000300030003000300030003100300030	00000000000000000100
00300030003000300030003000300030003000300030003000300030003000300031003000300030	00000000000000001000
00300030003000300030003000300030003000300030003000300030003000310030003000300030	00000000000000010000
drop table t1;
create table t1 (a float);
insert into t1 values (123.456);
select hex(concat(a)) from t1;
hex(concat(a))
003100320033002E003400350036
select concat(a) from t1;
concat(a)
123.456
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(12) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a float zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
select hex(concat(a)), a from t1;
hex(concat(a))	a
0030003000300030003000300030003000300031002E0031	0000000001.1
0030003000300030003000300030003000310030002E0031	0000000010.1
0030003000300030003000300030003100300030002E0031	0000000100.1
0030003000300030003000300031003000300030002E0031	0000001000.1
0030003000300030003000310030003000300030002E0031	0000010000.1
drop table t1;
create table t1 (a double);
insert into t1 values (123.456);
select hex(concat(a)) from t1;
hex(concat(a))
003100320033002E003400350036
select concat(a) from t1;
concat(a)
123.456
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(22) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a double zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
select hex(concat(a)), a from t1;
hex(concat(a))	a
00300030003000300030003000300030003000300030003000300030003000300030003000300031002E0031	00000000000000000001.1
00300030003000300030003000300030003000300030003000300030003000300030003000310030002E0031	00000000000000000010.1
00300030003000300030003000300030003000300030003000300030003000300030003100300030002E0031	00000000000000000100.1
00300030003000300030003000300030003000300030003000300030003000300031003000300030002E0031	00000000000000001000.1
00300030003000300030003000300030003000300030003000300030003000310030003000300030002E0031	00000000000000010000.1
drop table t1;
create table t1 (a year(2));
Warnings:
Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
00300031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(2) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a year);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0032003000300031
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(4) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a bit(64));
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0000000000000001
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varbinary(64) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a timestamp);
insert into t1 values (0);
insert into t1 values (20010203040506);
insert into t1 values (19800203040506);
insert into t1 values ('2001-02-03 04:05:06');
select hex(concat(a)) from t1;
hex(concat(a))
0030003000300030002D00300030002D00300030002000300030003A00300030003A00300030
0032003000300031002D00300032002D00300033002000300034003A00300035003A00300036
0031003900380030002D00300032002D00300033002000300034003A00300035003A00300036
0032003000300031002D00300032002D00300033002000300034003A00300035003A00300036
select concat(a) from t1;
concat(a)
0000-00-00 00:00:00
2001-02-03 04:05:06
1980-02-03 04:05:06
2001-02-03 04:05:06
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(19) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a date);
insert into t1 values ('2001-02-03');
insert into t1 values (20010203);
select hex(concat(a)) from t1;
hex(concat(a))
0032003000300031002D00300032002D00300033
0032003000300031002D00300032002D00300033
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a time);
insert into t1 values (1);
insert into t1 values ('01:02:03');
select hex(concat(a)) from t1;
hex(concat(a))
00300030003A00300030003A00300031
00300031003A00300032003A00300033
select concat(a) from t1;
concat(a)
00:00:01
01:02:03
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a datetime);
insert into t1 values ('2001-02-03 04:05:06');
insert into t1 values (20010203040506);
select hex(concat(a)) from t1;
hex(concat(a))
0032003000300031002D00300032002D00300033002000300034003A00300035003A00300036
0032003000300031002D00300032002D00300033002000300034003A00300035003A00300036
create table t2 as select concat(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `concat(a)` varchar(19) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a tinyint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(4)	YES		NULL	
select hex(a) from v1;
hex(a)
0031
drop table t1;
drop view v1;
create table t1 (a tinyint zerofill);
insert into t1 values (1), (10), (100);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(3)	YES		NULL	
select hex(a) from v1;
hex(a)
003000300031
003000310030
003100300030
drop table t1;
drop view v1;
create table t1 (a tinyint(30) zerofill);
insert into t1 values (1), (10), (100);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(30)	YES		NULL	
select hex(a) from v1;
hex(a)
003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300031
003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000310030
003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003100300030
drop table t1;
drop view v1;
create table t1 (a decimal(10,2));
insert into t1 values (123.45);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(12)	YES		NULL	
select hex(a) from v1;
hex(a)
003100320033002E00340035
drop table t1;
drop view v1;
create table t1 (a smallint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(6)	YES		NULL	
select hex(a) from v1;
hex(a)
0031
drop table t1;
drop view v1;
create table t1 (a smallint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(5)	YES		NULL	
select hex(a) from v1;
hex(a)
00300030003000300031
00300030003000310030
00300030003100300030
00300031003000300030
00310030003000300030
drop table t1;
drop view v1;
create table t1 (a mediumint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(9)	YES		NULL	
select hex(a) from v1;
hex(a)
0031
drop table t1;
drop view v1;
create table t1 (a mediumint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(8)	YES		NULL	
select hex(a) from v1;
hex(a)
00300030003000300030003000300031
00300030003000300030003000310030
00300030003000300030003100300030
00300030003000300031003000300030
00300030003000310030003000300030
drop table t1;
drop view v1;
create table t1 (a int);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(11)	YES		NULL	
select hex(a) from v1;
hex(a)
0031
drop table t1;
drop view v1;
create table t1 (a int zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(10)	YES		NULL	
select hex(a) from v1;
hex(a)
0030003000300030003000300030003000300031
0030003000300030003000300030003000310030
0030003000300030003000300030003100300030
0030003000300030003000300031003000300030
0030003000300030003000310030003000300030
drop table t1;
drop view v1;
create table t1 (a bigint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(20)	YES		NULL	
select hex(a) from v1;
hex(a)
0031
drop table t1;
drop view v1;
create table t1 (a bigint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(20)	YES		NULL	
select hex(a) from v1;
hex(a)
00300030003000300030003000300030003000300030003000300030003000300030003000300031
00300030003000300030003000300030003000300030003000300030003000300030003000310030
00300030003000300030003000300030003000300030003000300030003000300030003100300030
00300030003000300030003000300030003000300030003000300030003000300031003000300030
00300030003000300030003000300030003000300030003000300030003000310030003000300030
drop table t1;
drop view v1;
create table t1 (a float);
insert into t1 values (123.456);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(12)	YES		NULL	
select hex(a) from v1;
hex(a)
003100320033002E003400350036
drop table t1;
drop view v1;
create table t1 (a float zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(12)	YES		NULL	
select hex(a) from v1;
hex(a)
0030003000300030003000300030003000300031002E0031
0030003000300030003000300030003000310030002E0031
0030003000300030003000300030003100300030002E0031
0030003000300030003000300031003000300030002E0031
0030003000300030003000310030003000300030002E0031
drop table t1;
drop view v1;
create table t1 (a double);
insert into t1 values (123.456);
select concat(a) from t1;
concat(a)
123.456
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(22)	YES		NULL	
select hex(a) from v1;
hex(a)
003100320033002E003400350036
drop table t1;
drop view v1;
create table t1 (a double zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(22)	YES		NULL	
select hex(a) from v1;
hex(a)
00300030003000300030003000300030003000300030003000300030003000300030003000300031002E0031
00300030003000300030003000300030003000300030003000300030003000300030003000310030002E0031
00300030003000300030003000300030003000300030003000300030003000300030003100300030002E0031
00300030003000300030003000300030003000300030003000300030003000300031003000300030002E0031
00300030003000300030003000300030003000300030003000300030003000310030003000300030002E0031
drop table t1;
drop view v1;
create table t1 (a year(2));
Warnings:
Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(2)	YES		NULL	
select hex(a) from v1;
hex(a)
00300031
drop table t1;
drop view v1;
create table t1 (a year);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(4)	YES		NULL	
select hex(a) from v1;
hex(a)
0032003000300031
drop table t1;
drop view v1;
create table t1 (a bit(64));
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varbinary(64)	YES		NULL	
select hex(a) from v1;
hex(a)
0000000000000001
drop table t1;
drop view v1;
create table t1 (a timestamp);
insert into t1 values (0);
insert into t1 values (20010203040506);
insert into t1 values (19800203040506);
insert into t1 values ('2001-02-03 04:05:06');
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(19)	NO			
select hex(a) from v1;
hex(a)
0030003000300030002D00300030002D00300030002000300030003A00300030003A00300030
0032003000300031002D00300032002D00300033002000300034003A00300035003A00300036
0031003900380030002D00300032002D00300033002000300034003A00300035003A00300036
0032003000300031002D00300032002D00300033002000300034003A00300035003A00300036
drop table t1;
drop view v1;
create table t1 (a date);
insert into t1 values ('2001-02-03');
insert into t1 values (20010203);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(10)	YES		NULL	
select hex(a) from v1;
hex(a)
0032003000300031002D00300032002D00300033
0032003000300031002D00300032002D00300033
drop table t1;
drop view v1;
create table t1 (a time);
insert into t1 values (1);
insert into t1 values ('01:02:03');
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(10)	YES		NULL	
select hex(a) from v1;
hex(a)
00300030003A00300030003A00300031
00300031003A00300032003A00300033
drop table t1;
drop view v1;
create table t1 (a datetime);
insert into t1 values ('2001-02-03 04:05:06');
insert into t1 values (20010203040506);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	varchar(19)	YES		NULL	
select hex(a) from v1;
hex(a)
0032003000300031002D00300032002D00300033002000300034003A00300035003A00300036
0032003000300031002D00300032002D00300033002000300034003A00300035003A00300036
drop table t1;
drop view v1;
create function f1 (par1 int) returns int
begin
return concat(par1);
end|
set @a= f1(1);
select hex(@a);
hex(@a)
1
select hex(concat(f1(1)));
hex(concat(f1(1)))
31
create table t1 as select f1(1) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(1)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create view v1 as select concat(f1(1)) as c1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
c1	varchar(11)	YES		NULL	
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 decimal(18,2)) returns decimal(18,2)
begin
return concat(par1);
end|
set @a= f1(123.45);
select hex(@a);
hex(@a)
7B
select hex(concat(f1(123.45)));
hex(concat(f1(123.45)))
3132332E3435
create table t1 as select f1(123.45) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` decimal(18,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(123.45)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create view v1 as select concat(f1(123.45)) as c1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
c1	varchar(20)	YES		NULL	
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 float) returns float
begin
return concat(par1);
end|
set @a= f1(123.45);
select hex(@a);
hex(@a)
7B
select hex(concat(f1(123.45)));
hex(concat(f1(123.45)))
3132332E3435
create table t1 as select f1(123.45) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(123.45)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create view v1 as select concat(f1(123.45)) as c1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
c1	varchar(12)	YES		NULL	
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 date) returns date
begin
return concat(par1);
end|
set @a= f1(cast('2001-01-02' as date));
select hex(@a);
hex(@a)
323030312D30312D3032
select hex(concat(f1(cast('2001-01-02' as date))));
hex(concat(f1(cast('2001-01-02' as date))))
323030312D30312D3032
create table t1 as select f1(cast('2001-01-02' as date)) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(cast('2001-01-02' as date))) as c1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create view v1 as select concat(f1(cast('2001-01-02' as date))) as c1;
show columns from v1;
Field	Type	Null	Key	Default	Extra
c1	varchar(10)	YES		NULL	
drop table t1;
drop view v1;
drop function f1;
#
# End of WL#2649 Number-to-string conversions
#
#
# Bug#54668 User variable assignments get wrong type
#
SET @x=md5('a');
SELECT charset(@x), collation(@x);
charset(@x)	collation(@x)
ucs2	ucs2_general_ci
SET @x=old_password('a');
SELECT charset(@x), collation(@x);
charset(@x)	collation(@x)
ucs2	ucs2_general_ci
SET @x=password('a');
SELECT charset(@x), collation(@x);
charset(@x)	collation(@x)
ucs2	ucs2_general_ci
SET @x=sha('a');
SELECT charset(@x), collation(@x);
charset(@x)	collation(@x)
ucs2	ucs2_general_ci
SET @x=sha1('a');
SELECT charset(@x), collation(@x);
charset(@x)	collation(@x)
ucs2	ucs2_general_ci
SET @x=astext(point(1,2));
SELECT charset(@x), collation(@x);
charset(@x)	collation(@x)
ucs2	ucs2_general_ci
SET @x=aswkt(point(1,2));
SELECT charset(@x), collation(@x);
charset(@x)	collation(@x)
ucs2	ucs2_general_ci
#
# Bug#54916 GROUP_CONCAT + IFNULL truncates output
#
SELECT @@collation_connection;
@@collation_connection
ucs2_general_ci
CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM;
INSERT INTO t1 VALUES (1234567);
SELECT GROUP_CONCAT(IFNULL(a,'')) FROM t1;
GROUP_CONCAT(IFNULL(a,''))
1234567
SELECT GROUP_CONCAT(IF(a,a,'')) FROM t1;
GROUP_CONCAT(IF(a,a,''))
1234567
SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1;
GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END)
1234567
SELECT COALESCE(a,'') FROM t1 GROUP BY 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					COALESCE(a,'')	253	9	7	Y	0	31	8
COALESCE(a,'')
1234567
# All columns must be VARCHAR(9) with the same length:
CREATE TABLE t2 AS
SELECT
CONCAT(a),
IFNULL(a,''),
IF(a,a,''),
CASE WHEN a THEN a ELSE '' END,
COALESCE(a,'')
FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `CONCAT(a)` varchar(9) CHARACTER SET ucs2 DEFAULT NULL,
  `IFNULL(a,'')` varchar(9) CHARACTER SET ucs2 NOT NULL DEFAULT '',
  `IF(a,a,'')` varchar(9) CHARACTER SET ucs2 DEFAULT NULL,
  `CASE WHEN a THEN a ELSE '' END` varchar(9) CHARACTER SET ucs2 DEFAULT NULL,
  `COALESCE(a,'')` varchar(9) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `CONCAT_WS(1,2,3)` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `INSERT(1133,3,0,22)` varchar(6) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LCASE(a) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `LCASE(a)` varchar(9) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT UCASE(a) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `UCASE(a)` varchar(9) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT REPEAT(1,2) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `REPEAT(1,2)` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LEFT(123,2) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `LEFT(123,2)` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT RIGHT(123,2) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `RIGHT(123,2)` varchar(2) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LTRIM(123) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `LTRIM(123)` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT RTRIM(123) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `RTRIM(123)` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT ELT(1,111,222,333) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ELT(1,111,222,333)` varchar(3) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT REPLACE(111,2,3) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `REPLACE(111,2,3)` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT SUBSTRING_INDEX(111,111,1) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `SUBSTRING_INDEX(111,111,1)` varchar(3) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT MAKE_SET(111,222,3) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `MAKE_SET(111,222,3)` varchar(5) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT SOUNDEX(1) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `SOUNDEX(1)` varchar(4) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT EXPORT_SET(1,'Y','N','',8);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `EXPORT_SET(1,'Y','N','',8)` varchar(64) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
#
# End of Bug#54916
#
#
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
#
SELECT @@collation_connection;
@@collation_connection
ucs2_general_ci
CREATE TABLE t1 (
id INT(11) DEFAULT NULL,
date_column DATE DEFAULT NULL,
KEY(date_column));
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	date_column	date_column	4	NULL	1	Using index condition
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	date_column	date_column	9	NULL	1	Using index condition
DROP TABLE t1;
#
# Bug #31384 	DATE_ADD() and DATE_SUB() return binary data
#
SELECT @@collation_connection, @@character_set_results;
@@collation_connection	@@character_set_results
ucs2_general_ci	latin1
SELECT
CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2,
CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
field_str1	field_str2	field_date	field_datetime
ucs2	ucs2	binary	binary
CREATE TABLE t1 AS
SELECT
DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `field_str1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL,
  `field1_str2` varchar(19) CHARACTER SET ucs2 DEFAULT NULL,
  `field_date` date DEFAULT NULL,
  `field_datetime` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
SELECT
DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					field_str1	254	19	10	Y	0	0	8
def					field1_str2	254	19	19	Y	0	0	8
def					field_date	10	10	10	Y	128	0	63
def					field_datetime	12	19	19	Y	128	0	63
field_str1	field1_str2	field_date	field_datetime
2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
SELECT
HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2,
HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
field_str1	field1_str2	field_date	field_datetime
0032003000300037002D00300038002D00300032002000320033003A00350039003A00300030	0032003000300037002D00300038002D00300033002000310037003A00330032003A00300030	323030372D30382D3032	323030372D30382D30332031373A33323A3030
#
# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD()
#
SELECT @@collation_connection, @@character_set_results;
@@collation_connection	@@character_set_results
ucs2_general_ci	latin1
SELECT
CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1,
CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2,
CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1,
CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2;
addtime1	addtime2	date_add1	date_add2
ucs2	ucs2	ucs2	ucs2
CREATE TABLE t1 AS
SELECT
ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1,
ADDTIME('10:01:01','10:00:00') AS addtime2,
DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1,
DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `addtime1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL,
  `addtime2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL,
  `date_add1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL,
  `date_add2` varchar(19) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT * FROM t1;
addtime1	addtime2	date_add1	date_add2
20:01:01	20:01:01	2001-01-01 10:01:11	2001-01-01 10:01:11
DROP TABLE t1;
#
# Bug#11926811 / Bug#60625 Illegal mix of collations
#
SELECT @@collation_connection;
@@collation_connection
ucs2_general_ci
CREATE PROCEDURE p1()
BEGIN
DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
SELECT v_LastPaymentDate < NOW();
EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
SHOW WARNINGS;
EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
END//
CALL p1;
v_LastPaymentDate < NOW()
NULL
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Level	Code	Message
Note	1003	select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select concat(convert(v_LastPaymentDate@0 using ucs2),convert(now() using ucs2)) AS `CONCAT(v_LastPaymentDate, NOW())`
DROP PROCEDURE p1;
#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
CREATE TABLE t1 (b INT);
INSERT INTO t1 VALUES (0);
SELECT f1() FROM t1 LEFT JOIN (SELECT 1 AS a FROM t1 LIMIT 0) AS d ON 1 GROUP BY a;
f1()
00:00:01
DROP FUNCTION f1;
DROP TABLE t1;
#
# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool)
#
SELECT @@collation_connection;
@@collation_connection
ucs2_general_ci
SELECT CASE 1 WHEN 2 THEN ( - '3' ) END;
CASE 1 WHEN 2 THEN ( - '3' ) END
NULL
#
# MDEV-5702 Incorrect results are returned with NULLIF()
#
CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1999-11-11'),('2014-02-04');
SELECT DISTINCT d, CAST(d AS CHAR), NULLIF(d,"2000-01-01") AS bad, NULLIF(CAST(d AS CHAR),"2000-01-01") AS good FROM t1;
d	CAST(d AS CHAR)	bad	good
1999-11-11	1999-11-11	1999-11-11	1999-11-11
2014-02-04	2014-02-04	2014-02-04	2014-02-04
CREATE TABLE t2 AS SELECT DISTINCT d, NULLIF(d,'2000-01-01') AS bad FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `d` date DEFAULT NULL,
  `bad` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1, t2;
SET NAMES latin1;
SET sql_mode='';
CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a));
INSERT INTO t1 VALUES ();
SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1;
maketime(`a`,`a`,`a`)
00:00:00.000000
DROP TABLE t1;
SET sql_mode=default;
SET NAMES latin1;
#
# Bug #13832953 	MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED
#
CREATE TABLE t1 (c1 SET('','') CHARACTER SET ucs2);
Warnings:
Note	1291	Column 'c1' has duplicated value '' in SET
INSERT INTO t1 VALUES ('');
SELECT COALESCE(c1) FROM t1 ORDER BY 1;
COALESCE(c1)

DROP TABLE t1;
#
# MDEV-5745 analyze MySQL fix for bug#12368495
#
SELECT CHAR_LENGTH(TRIM(LEADING 0x000000 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(LEADING 0x000000 FROM _ucs2 0x0061))
2
SELECT CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _ucs2 0x0061))
2
SELECT CHAR_LENGTH(TRIM(LEADING 0x00 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(LEADING 0x00 FROM _ucs2 0x0061))
1
SELECT CHAR_LENGTH(TRIM(TRAILING 0x000000 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(TRAILING 0x000000 FROM _ucs2 0x0061))
2
SELECT CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _ucs2 0x0061))
2
SELECT CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _ucs2 0x0061))
1
SELECT CHAR_LENGTH(TRIM(BOTH 0x000000 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(BOTH 0x000000 FROM _ucs2 0x0061))
2
SELECT CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _ucs2 0x0061))
2
SELECT CHAR_LENGTH(TRIM(BOTH 0x61 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(BOTH 0x61 FROM _ucs2 0x0061))
1
SELECT CHAR_LENGTH(TRIM(BOTH 0x00 FROM _ucs2 0x0061));
CHAR_LENGTH(TRIM(BOTH 0x00 FROM _ucs2 0x0061))
1
#
# MDEV-11685: sql_mode can't be set with non-ascii connection charset
#
SET character_set_connection=ucs2;
SET sql_mode='NO_ENGINE_SUBSTITUTION';
SELECT @@sql_mode;
@@sql_mode
NO_ENGINE_SUBSTITUTION
SET sql_mode=DEFAULT;
SET NAMES utf8;
#
# End of 5.5 tests
#
#
# Start of 5.6 tests
#
#
# WL#3664 WEIGHT_STRING
#
set collation_connection=ucs2_general_ci;
select @@collation_connection;
@@collation_connection
ucs2_general_ci
CREATE TABLE t1 AS SELECT 'a' AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) 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(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
0041
SELECT HEX(ws) FROM t2;
HEX(ws)
0041
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 ucs2 NOT NULL DEFAULT ''
) 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(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
00410041004100410041
SELECT HEX(ws) FROM t2;
HEX(ws)
00410041004100410041
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(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(3)))
004100410041
SELECT HEX(ws) FROM t2;
HEX(ws)
004100410041
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(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
0041004100410041004100200020002000200020
SELECT HEX(ws) FROM t2;
HEX(ws)
0041004100410041004100200020002000200020
DROP TABLE t2;
DROP TABLE t1;
select hex(weight_string('a'));
hex(weight_string('a'))
0041
select hex(weight_string('A'));
hex(weight_string('A'))
0041
select hex(weight_string('abc'));
hex(weight_string('abc'))
004100420043
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
00410042
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
004100420043
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
00410042004300200020
select hex(weight_string('abc', 1, 2, 0xC0));
hex(weight_string('abc', 1, 2, 0xC0))
00
select hex(weight_string('abc', 2, 2, 0xC0));
hex(weight_string('abc', 2, 2, 0xC0))
0041
select hex(weight_string('abc', 3, 2, 0xC0));
hex(weight_string('abc', 3, 2, 0xC0))
004100
select hex(weight_string('abc', 4, 2, 0xC0));
hex(weight_string('abc', 4, 2, 0xC0))
00410042
select hex(weight_string('abc', 5, 2, 0xC0));
hex(weight_string('abc', 5, 2, 0xC0))
0041004200
select hex(weight_string('abc',25, 2, 0xC0));
hex(weight_string('abc',25, 2, 0xC0))
00410042002000200020002000200020002000200020002000
select hex(weight_string('abc', 1, 3, 0xC0));
hex(weight_string('abc', 1, 3, 0xC0))
00
select hex(weight_string('abc', 2, 3, 0xC0));
hex(weight_string('abc', 2, 3, 0xC0))
0041
select hex(weight_string('abc', 3, 3, 0xC0));
hex(weight_string('abc', 3, 3, 0xC0))
004100
select hex(weight_string('abc', 4, 3, 0xC0));
hex(weight_string('abc', 4, 3, 0xC0))
00410042
select hex(weight_string('abc', 5, 3, 0xC0));
hex(weight_string('abc', 5, 3, 0xC0))
0041004200
select hex(weight_string('abc',25, 3, 0xC0));
hex(weight_string('abc',25, 3, 0xC0))
00410042004300200020002000200020002000200020002000
select hex(weight_string('abc', 1, 4, 0xC0));
hex(weight_string('abc', 1, 4, 0xC0))
00
select hex(weight_string('abc', 2, 4, 0xC0));
hex(weight_string('abc', 2, 4, 0xC0))
0041
select hex(weight_string('abc', 3, 4, 0xC0));
hex(weight_string('abc', 3, 4, 0xC0))
004100
select hex(weight_string('abc', 4, 4, 0xC0));
hex(weight_string('abc', 4, 4, 0xC0))
00410042
select hex(weight_string('abc', 5, 4, 0xC0));
hex(weight_string('abc', 5, 4, 0xC0))
0041004200
select hex(weight_string('abc',25, 4, 0xC0));
hex(weight_string('abc',25, 4, 0xC0))
00410042004300200020002000200020002000200020002000
select @@collation_connection;
@@collation_connection
ucs2_general_ci
select hex(weight_string(cast(_latin1 0x80 as char)));
hex(weight_string(cast(_latin1 0x80 as char)))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char)));
hex(weight_string(cast(_latin1 0x808080 as char)))
20AC20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(2)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(2)))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(3)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(3)))
20AC20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(5)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(5)))
20AC20AC20AC00200020
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0))
20AC20AC00
select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0))
20AC20AC002000200020002000200020002000200020002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0))
20AC20AC20
select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0))
20AC20AC20AC00200020002000200020002000200020002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0))
20AC20AC20
select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0))
20AC20AC20AC00200020002000200020002000200020002000
select @@collation_connection;
@@collation_connection
ucs2_general_ci
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
0041
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
0041
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
004100420043
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
00410042
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
004100420043
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
00410042004300200020
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
20002000430042004100
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
FFBEFFBDFFBCFFDFFFDF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
DFFFDFFFBCFFBDFFBEFF
set collation_connection=ucs2_bin;
select @@collation_connection;
@@collation_connection
ucs2_bin
CREATE TABLE t1 AS SELECT 'a' AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL DEFAULT ''
) 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(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
0061
SELECT HEX(ws) FROM t2;
HEX(ws)
0061
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 ucs2 COLLATE ucs2_bin NOT NULL DEFAULT ''
) 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(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
00610061006100610061
SELECT HEX(ws) FROM t2;
HEX(ws)
00610061006100610061
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(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(3)))
006100610061
SELECT HEX(ws) FROM t2;
HEX(ws)
006100610061
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(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
0061006100610061006100200020002000200020
SELECT HEX(ws) FROM t2;
HEX(ws)
0061006100610061006100200020002000200020
DROP TABLE t2;
DROP TABLE t1;
select hex(weight_string('a'));
hex(weight_string('a'))
0061
select hex(weight_string('A'));
hex(weight_string('A'))
0041
select hex(weight_string('abc'));
hex(weight_string('abc'))
006100620063
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
00610062
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
006100620063
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
00610062006300200020
select hex(weight_string('abc', 1, 2, 0xC0));
hex(weight_string('abc', 1, 2, 0xC0))
00
select hex(weight_string('abc', 2, 2, 0xC0));
hex(weight_string('abc', 2, 2, 0xC0))
0061
select hex(weight_string('abc', 3, 2, 0xC0));
hex(weight_string('abc', 3, 2, 0xC0))
006100
select hex(weight_string('abc', 4, 2, 0xC0));
hex(weight_string('abc', 4, 2, 0xC0))
00610062
select hex(weight_string('abc', 5, 2, 0xC0));
hex(weight_string('abc', 5, 2, 0xC0))
0061006200
select hex(weight_string('abc',25, 2, 0xC0));
hex(weight_string('abc',25, 2, 0xC0))
00610062002000200020002000200020002000200020002000
select hex(weight_string('abc', 1, 3, 0xC0));
hex(weight_string('abc', 1, 3, 0xC0))
00
select hex(weight_string('abc', 2, 3, 0xC0));
hex(weight_string('abc', 2, 3, 0xC0))
0061
select hex(weight_string('abc', 3, 3, 0xC0));
hex(weight_string('abc', 3, 3, 0xC0))
006100
select hex(weight_string('abc', 4, 3, 0xC0));
hex(weight_string('abc', 4, 3, 0xC0))
00610062
select hex(weight_string('abc', 5, 3, 0xC0));
hex(weight_string('abc', 5, 3, 0xC0))
0061006200
select hex(weight_string('abc',25, 3, 0xC0));
hex(weight_string('abc',25, 3, 0xC0))
00610062006300200020002000200020002000200020002000
select hex(weight_string('abc', 1, 4, 0xC0));
hex(weight_string('abc', 1, 4, 0xC0))
00
select hex(weight_string('abc', 2, 4, 0xC0));
hex(weight_string('abc', 2, 4, 0xC0))
0061
select hex(weight_string('abc', 3, 4, 0xC0));
hex(weight_string('abc', 3, 4, 0xC0))
006100
select hex(weight_string('abc', 4, 4, 0xC0));
hex(weight_string('abc', 4, 4, 0xC0))
00610062
select hex(weight_string('abc', 5, 4, 0xC0));
hex(weight_string('abc', 5, 4, 0xC0))
0061006200
select hex(weight_string('abc',25, 4, 0xC0));
hex(weight_string('abc',25, 4, 0xC0))
00610062006300200020002000200020002000200020002000
select @@collation_connection;
@@collation_connection
ucs2_bin
select hex(weight_string(cast(_latin1 0x80 as char)));
hex(weight_string(cast(_latin1 0x80 as char)))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char)));
hex(weight_string(cast(_latin1 0x808080 as char)))
20AC20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(2)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(2)))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(3)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(3)))
20AC20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(5)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(5)))
20AC20AC20AC00200020
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0))
20AC20AC00
select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0))
20AC20AC002000200020002000200020002000200020002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0))
20AC20AC20
select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0))
20AC20AC20AC00200020002000200020002000200020002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0))
20AC20AC20
select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0))
20AC20AC20AC00200020002000200020002000200020002000
select @@collation_connection;
@@collation_connection
ucs2_bin
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
0061
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
0041
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
006100620063
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
00610062
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
006100620063
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
00610062006300200020
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
20002000630062006100
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
FF9EFF9DFF9CFFDFFFDF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
DFFFDFFF9CFF9DFF9EFF
#
# Bug #36418 Character sets: crash if char(256 using utf32)
#
select hex(char(0x01 using ucs2));
hex(char(0x01 using ucs2))
0001
select hex(char(0x0102 using ucs2));
hex(char(0x0102 using ucs2))
0102
select hex(char(0x010203 using ucs2));
hex(char(0x010203 using ucs2))
00010203
select hex(char(0x01020304 using ucs2));
hex(char(0x01020304 using ucs2))
01020304
#
# Bug#10094 Displays wrong error message for UNIQUE key index on CHAR(255) Unicode datatype
#
CREATE TABLE t1 (f1 CHAR(255) unicode);
INSERT INTO t1 values ('abc'),('bcd'),('abc');
ALTER TABLE t1 ADD UNIQUE Index_1 (f1);
ERROR 23000: Duplicate entry 'abc' for key 'Index_1'
DROP TABLE t1;
#
# Test how character set works with date/time
#
SET collation_connection=ucs2_general_ci;
#
# Bug#32390 Character sets: casting utf32 to/from date doesn't work
#
CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0;
SET time_zone=_latin1'+03:00';
SET timestamp=1216359724;
INSERT INTO t1 VALUES (current_date);
INSERT INTO t1 VALUES (current_time);
INSERT INTO t1 VALUES (current_timestamp);
SELECT s1, hex(s1) FROM t1;
s1	hex(s1)
2008-07-18	0032003000300038002D00300037002D00310038
08:42:04	00300038003A00340032003A00300034
2008-07-18 08:42:04	0032003000300038002D00300037002D00310038002000300038003A00340032003A00300034
DROP TABLE t1;
SET timestamp=0;
SET time_zone=default;
#
# MDEV-5298 Illegal mix of collations on timestamp
#
SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY);
CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY)
ucs2
SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY);
COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY)
4
SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
binary
SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
5
SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
ucs2
SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
4
SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
ucs2
SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
4
SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
ucs2
SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
4
SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
ucs2
SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
4
SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(64) CHARACTER SET ucs2 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES ('');
SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
ucs2
SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
2
SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
ucs2
SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
2
SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
0032003000300031002D00300031002D00300031002000300030003A00300030003A00300030
DROP TABLE t1;
CREATE TABLE t1 (t TIMESTAMP NOT NULL);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
DROP TABLE t1;
SET NAMES latin1;
#
# WL#4013 Unicode german2 collation
#
SET collation_connection=ucs2_german2_ci;
"BEGIN ctype_german.inc"
drop table if exists t1;
create table t1 as select repeat(' ', 64) as s1;
select collation(s1) from t1;
collation(s1)
ucs2_german2_ci
delete from t1;
INSERT INTO t1 VALUES ('ud'),('uf');
INSERT INTO t1 VALUES ('od'),('of');
INSERT INTO t1 VALUES ('e');
INSERT INTO t1 VALUES ('ad'),('af');
insert into t1 values ('a'),('ae'),(_latin1 0xE4);
insert into t1 values ('o'),('oe'),(_latin1 0xF6);
insert into t1 values ('s'),('ss'),(_latin1 0xDF);
insert into t1 values ('u'),('ue'),(_latin1 0xFC);
INSERT INTO t1 VALUES (_latin1 0xE6), (_latin1 0xC6);
INSERT INTO t1 VALUES (_latin1 0x9C), (_latin1 0x8C);
select s1, hex(s1) from t1 order by s1, binary s1;
s1	hex(s1)
a	0061
ad	00610064
ae	00610065
	00C6
	00E4
	00E6
af	00610066
e	0065
o	006F
od	006F0064
oe	006F0065
	00F6
	0152
	0153
of	006F0066
s	0073
ss	00730073
	00DF
u	0075
ud	00750064
ue	00750065
	00FC
uf	00750066
select group_concat(s1 order by binary s1) from t1 group by s1;
group_concat(s1 order by binary s1)
a
ad
ae,,,
af
e
o
od
oe,,,
of
s
ss,
u
ud
ue,
uf
SELECT s1, hex(s1), hex(weight_string(s1)) FROM t1 ORDER BY s1, BINARY(s1);
s1	hex(s1)	hex(weight_string(s1))
a	0061	0E33
ad	00610064	0E330E6D
ae	00610065	0E330E8B
	00C6	0E330E8B
	00E4	0E330E8B
	00E6	0E330E8B
af	00610066	0E330EB9
e	0065	0E8B
o	006F	0F82
od	006F0064	0F820E6D
oe	006F0065	0F820E8B
	00F6	0F820E8B
	0152	0F820E8B
	0153	0F820E8B
of	006F0066	0F820EB9
s	0073	0FEA
ss	00730073	0FEA0FEA
	00DF	0FEA0FEA
u	0075	101F
ud	00750064	101F0E6D
ue	00750065	101F0E8B
	00FC	101F0E8B
uf	00750066	101F0EB9
SELECT s1, hex(s1) FROM t1 WHERE s1='ae' ORDER BY s1, BINARY(s1);
s1	hex(s1)
ae	00610065
	00C6
	00E4
	00E6
drop table t1;
CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a, 1 AS b LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1) CHARACTER SET ucs2 COLLATE ucs2_german2_ci NOT NULL DEFAULT '',
  `b` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES ('s',0),(_latin1 0xDF,1);
SELECT * FROM t1 ORDER BY a, b;
a	b
s	0
	1
SELECT * FROM t1 ORDER BY a DESC, b;
a	b
	1
s	0
SELECT * FROM t1 ORDER BY CONCAT(a), b;
a	b
s	0
	1
SELECT * FROM t1 ORDER BY CONCAT(a) DESC, b;
a	b
	1
s	0
DROP TABLE t1;
"END ctype_german.inc"
#
# Bug#59145 valgrind warnings for uninitialized values in my_strtoll10_mb2
#
SET NAMES latin1;
SELECT CONVERT(CHAR(NULL USING ucs2), UNSIGNED);
CONVERT(CHAR(NULL USING ucs2), UNSIGNED)
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: ''
DO IFNULL(CHAR(NULL USING ucs2), '');
DO CAST(CONVERT('' USING ucs2) AS UNSIGNED);
Warnings:
Warning	1292	Truncated incorrect INTEGER value: ''
#
# Test error message for conversion using different charset
# 
CREATE TABLE t1 (a DECIMAL(2,0));
SET sql_mode='strict_all_tables';
INSERT INTO t1 VALUES (CONVERT('9e99999999' USING ucs2));
ERROR 22007: Incorrect decimal value: '9e99999999' for column 'a' at row 1
SET sql_mode=DEFAULT;
INSERT INTO t1 VALUES (CONVERT('aaa' USING ucs2));
Warnings:
Warning	1366	Incorrect decimal value: 'aaa' for column 'a' at row 1
DROP TABLE t1;
#
# End of 5.6 tests
#
#
# Start of 10.0 tests
#
SET NAMES latin1, collation_connection=ucs2_bin;
#
# 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 ucs2 COLLATE ucs2_bin NOT NULL DEFAULT ''
) 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 ucs2 COLLATE ucs2_bin NOT NULL DEFAULT ''
) 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 ucs2 COLLATE ucs2_bin NOT NULL DEFAULT ''
) 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 ucs2 COLLATE ucs2_bin NOT NULL DEFAULT ''
) 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;
SET NAMES latin1, collation_connection=ucs2_general_ci;
#
# 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 ucs2 NOT NULL DEFAULT ''
) 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 ucs2 NOT NULL DEFAULT ''
) 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 ucs2 NOT NULL DEFAULT ''
) 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 ucs2 NOT NULL DEFAULT ''
) 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;
SET NAMES latin1;
#
# MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context
#
SELECT CONCAT(CONVERT('pi=' USING ucs2),PI()) AS PI;
PI
pi=3.141593
#
# MDEV-6695 Bad column name for UCS2 string literals
#
SET NAMES utf8, character_set_connection=ucs2;
SELECT 'a','aa';
a	aa
a	aa
#
# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
#
SET NAMES utf8, character_set_connection=ucs2;
SET @save_optimizer_switch=@@optimizer_switch;
SET optimizer_switch=_utf8'derived_merge=on';
CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);
INSERT INTO t1 VALUES('abcdefghi');
SET NAMES utf8, character_set_connection=ucs2;
SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub;
c2
616263646566676869-616263646566676869
SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub;
c2
YWJjZGVmZ2hp-YWJjZGVmZ2hp
DROP TABLE t1;
SET optimizer_switch=@save_optimizer_switch;
#
# End of 10.0 tests
#