B4BY.588
Home
Terminal
Upload
information
Create File
Create Folder
About
Tools
:
/
usr
/
share
/
mysql-test
/
r
/
Filename :
ctype_ucs.result
back
Copy
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; 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; 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; "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; 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; 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 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 1 1 1 1 1 1 1 0 0 0 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.5 tests # SET NAMES latin1; SET collation_connection=ucs2_general_ci; # # 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 Warnings: Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead create table t1 as select old_password('a') as c1; Warnings: Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead 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 NOT NULL DEFAULT '' ) 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(21) 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(11) CHARACTER SET ucs2 NOT NULL DEFAULT '' ) 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(10) 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 NOT NULL DEFAULT '' ) 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(8) 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(8) 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(29) 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(29) 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(29) 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: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. 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 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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column 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(4) YES NULL select hex(a) from v1; hex(a) 0032003000300031 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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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'); Warnings: Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead 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 # # # WL#5510 Functions to_base64 and from_base64 # CREATE TABLE t1 AS SELECT TO_BASE64('test') AS to_base64; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `to_base64` varchar(12) CHARACTER SET ucs2 NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT to_base64, LENGTH(to_base64), HEX(to_base64) FROM t1; to_base64 LENGTH(to_base64) HEX(to_base64) AHQAZQBzAHQ= 24 0041004800510041005A00510042007A004100480051003D CREATE TABLE t2 AS SELECT FROM_BASE64(to_base64) AS from_base64 FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `from_base64` varbinary(18) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT CAST(from_base64 AS CHAR), LENGTH(from_base64), HEX(from_base64) FROM t2; CAST(from_base64 AS CHAR) LENGTH(from_base64) HEX(from_base64) test 8 0074006500730074 DROP TABLE t2; DROP TABLE t1; # # 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 ALL NULL NULL NULL NULL 2 Using where 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 ALL NULL NULL NULL NULL 2 Using where 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 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(29) CHARACTER SET ucs2 DEFAULT NULL, `field1_str2` varchar(29) 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 29 10 Y 0 31 8 def field1_str2 254 29 19 Y 0 31 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 # # 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#1 */ 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#1 */ 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; 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; # # 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 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 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; CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0; 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 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; "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 HY000: 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 #