1DROP TABLE IF EXISTS t1; 2SET @test_character_set= 'ucs2'; 3SET @test_collation= 'ucs2_general_ci'; 4SET @safe_character_set_server= @@character_set_server; 5SET @safe_collation_server= @@collation_server; 6SET @safe_character_set_client= @@character_set_client; 7SET @safe_character_set_results= @@character_set_results; 8SET character_set_server= @test_character_set; 9SET collation_server= @test_collation; 10CREATE DATABASE d1; 11USE d1; 12CREATE TABLE t1 (c CHAR(10), KEY(c)); 13SHOW FULL COLUMNS FROM t1; 14Field Type Collation Null Key Default Extra Privileges Comment 15c char(10) ucs2_general_ci YES MUL NULL 16INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); 17SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; 18want3results 19aaa 20aaaa 21aaaaa 22DROP TABLE t1; 23CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); 24SHOW FULL COLUMNS FROM t1; 25Field Type Collation Null Key Default Extra Privileges Comment 26c1 varchar(15) ucs2_general_ci YES MUL NULL 27INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); 28SELECT c1 as want3results from t1 where c1 like 'l%'; 29want3results 30location 31loberge 32lotre 33SELECT c1 as want3results from t1 where c1 like 'lo%'; 34want3results 35location 36loberge 37lotre 38SELECT c1 as want1result from t1 where c1 like 'loc%'; 39want1result 40location 41SELECT c1 as want1result from t1 where c1 like 'loca%'; 42want1result 43location 44SELECT c1 as want1result from t1 where c1 like 'locat%'; 45want1result 46location 47SELECT c1 as want1result from t1 where c1 like 'locati%'; 48want1result 49location 50SELECT c1 as want1result from t1 where c1 like 'locatio%'; 51want1result 52location 53SELECT c1 as want1result from t1 where c1 like 'location%'; 54want1result 55location 56DROP TABLE t1; 57create table t1 (a set('a') not null); 58insert ignore into t1 values (),(); 59Warnings: 60Warning 1364 Field 'a' doesn't have a default value 61select cast(a as char(1)) from t1; 62cast(a as char(1)) 63 64 65select a sounds like a from t1; 66a sounds like a 671 681 69select 1 from t1 order by cast(a as char(1)); 701 711 721 73drop table t1; 74# 75# MDEV-6134 SUBSTRING_INDEX returns wrong result for 8bit character sets when delimiter is not found 76# 77SET character_set_client=latin1; 78SET character_set_connection= @test_character_set; 79SET collation_connection= @test_collation; 80SELECT COLLATION('.'), SUBSTRING_INDEX('.wwwmysqlcom', '.', -2) AS c1; 81COLLATION('.') c1 82ucs2_general_ci .wwwmysqlcom 83set names utf8; 84create table t1 ( 85name varchar(10), 86level smallint unsigned); 87show create table t1; 88Table Create Table 89t1 CREATE TABLE `t1` ( 90 `name` varchar(10) DEFAULT NULL, 91 `level` smallint(5) unsigned DEFAULT NULL 92) ENGINE=MyISAM DEFAULT CHARSET=ucs2 93insert into t1 values ('string',1); 94select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; 95concat(name,space(level)) concat(name, repeat(' ',level)) 96string string 97drop table t1; 98DROP DATABASE d1; 99USE test; 100SET character_set_server= @safe_character_set_server; 101SET collation_server= @safe_collation_server; 102SET character_set_client= @safe_character_set_client; 103SET character_set_results= @safe_character_set_results; 104SET NAMES latin1; 105SET character_set_connection=ucs2; 106select 'a' = 'a', 'a' = 'a ', 'a ' = 'a'; 107'a' = 'a' 'a' = 'a ' 'a ' = 'a' 1081 1 1 109select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a'; 110'a\0' = 'a' 'a\0' < 'a' 'a\0' > 'a' 1110 1 0 112select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0'; 113'a' = 'a\0' 'a' < 'a\0' 'a' > 'a\0' 1140 0 1 115select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a '; 116'a\0' = 'a ' 'a\0' < 'a ' 'a\0' > 'a ' 1170 1 0 118select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0'; 119'a ' = 'a\0' 'a ' < 'a\0' 'a ' > 'a\0' 1200 0 1 121select 'a a' > 'a', 'a \0' < 'a'; 122'a a' > 'a' 'a \0' < 'a' 1231 1 124select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a'; 125binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a' 1261 1 1 127SET CHARACTER SET koi8r; 128create table t1 (a varchar(2) character set ucs2 collate ucs2_bin, key(a)); 129insert into t1 values ('A'),('A'),('B'),('C'),('D'),('A\t'); 130insert into t1 values ('A\0'),('A\0'),('A\0'),('A\0'),('AZ'); 131select hex(a) from t1 where a like 'A_' order by a; 132hex(a) 13300410000 13400410000 13500410000 13600410000 13700410009 1380041005A 139select hex(a) from t1 ignore key(a) where a like 'A_' order by a; 140hex(a) 14100410000 14200410000 14300410000 14400410000 14500410009 1460041005A 147drop table t1; 148CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2, word2 CHAR(64) CHARACTER SET ucs2); 149INSERT INTO t1 VALUES (_koi8r'�',_koi8r'�'), (X'2004',X'2004'); 150SELECT hex(word) FROM t1 ORDER BY word; 151hex(word) 1520420 1532004 154SELECT hex(word2) FROM t1 ORDER BY word2; 155hex(word2) 1560420 1572004 158DELETE FROM t1; 159INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020'); 160SELECT hex(word) FROM t1 ORDER BY word; 161hex(word) 162042000200020 163200400200020 164SELECT hex(word2) FROM t1 ORDER BY word2; 165hex(word2) 1660420 1672004 168DROP TABLE t1; 169SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'0421'); 170LPAD(_ucs2 X'0420',10,_ucs2 X'0421') 171���������� 172SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'04210422'); 173LPAD(_ucs2 X'0420',10,_ucs2 X'04210422') 174���������� 175SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'042104220423'); 176LPAD(_ucs2 X'0420',10,_ucs2 X'042104220423') 177���������� 178SELECT LPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423'); 179LPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423') 180���������� 181SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'0421'); 182RPAD(_ucs2 X'0420',10,_ucs2 X'0421') 183���������� 184SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'04210422'); 185RPAD(_ucs2 X'0420',10,_ucs2 X'04210422') 186���������� 187SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'042104220423'); 188RPAD(_ucs2 X'0420',10,_ucs2 X'042104220423') 189���������� 190SELECT RPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423'); 191RPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423') 192���������� 193CREATE TABLE t1 SELECT 194LPAD(_ucs2 X'0420',10,_ucs2 X'0421') l, 195RPAD(_ucs2 X'0420',10,_ucs2 X'0421') r; 196SHOW CREATE TABLE t1; 197Table Create Table 198t1 CREATE TABLE `t1` ( 199 `l` varchar(10) CHARACTER SET ucs2 DEFAULT NULL, 200 `r` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 201) ENGINE=MyISAM DEFAULT CHARSET=latin1 202DROP TABLE t1; 203# 204# Bug #51876 : crash/memory underrun when loading data with ucs2 205# and reverse() function 206# 207# Problem # 1 (original report): wrong parsing of ucs2 data 208SET character_set_connection=ucs2; 209SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp.txt'; 210Warnings: 211Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 212CREATE TABLE t1(a INT); 213LOAD DATA INFILE 'tmpp.txt' INTO TABLE t1 CHARACTER SET ucs2 214(@b) SET a=REVERSE(@b); 215# should return 2 zeroes (as the value is truncated) 216SELECT * FROM t1; 217a 2180 2191 220DROP TABLE t1; 221# Problem # 2 : if you write and read ucs2 data to a file they're lost 222SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp2.txt' CHARACTER SET ucs2; 223Warnings: 224Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 225CREATE TABLE t1(a INT); 226LOAD DATA INFILE 'tmpp2.txt' INTO TABLE t1 CHARACTER SET ucs2 227(@b) SET a=REVERSE(@b); 228# should return 0 and 1 (10 reversed) 229SELECT * FROM t1; 230a 2310 2321 233DROP TABLE t1; 234create table t2(f1 Char(30)); 235insert into t2 values ("103000"), ("22720000"), ("3401200"), ("78000"); 236select lpad(f1, 12, "-o-/") from t2; 237lpad(f1, 12, "-o-/") 238-o-/-o103000 239-o-/22720000 240-o-/-3401200 241-o-/-o-78000 242drop table t2; 243SET NAMES koi8r; 244SET character_set_connection=ucs2; 245select @@collation_connection; 246@@collation_connection 247ucs2_general_ci 248create table t1 as select repeat(' ',10) as a union select null; 249alter table t1 add key(a); 250show create table t1; 251Table Create Table 252t1 CREATE TABLE `t1` ( 253 `a` varchar(10) CHARACTER SET ucs2 DEFAULT NULL, 254 KEY `a` (`a`) 255) ENGINE=MyISAM DEFAULT CHARSET=latin1 256insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); 257explain select * from t1 where a like 'abc%'; 258id select_type table type possible_keys key key_len ref rows Extra 2591 SIMPLE t1 range a a 23 NULL 2 Using where; Using index 260explain select * from t1 where a like concat('abc','%'); 261id select_type table type possible_keys key key_len ref rows Extra 2621 SIMPLE t1 range a a 23 NULL 2 Using where; Using index 263select * from t1 where a like "abc%"; 264a 265abc 266abcd 267select * from t1 where a like concat("abc","%"); 268a 269abc 270abcd 271select * from t1 where a like "ABC%"; 272a 273abc 274abcd 275select * from t1 where a like "test%"; 276a 277test 278select * from t1 where a like "te_t"; 279a 280test 281select * from t1 where a like "%a%"; 282a 283a 284abc 285abcd 286select * from t1 where a like "%abcd%"; 287a 288abcd 289select * from t1 where a like "%abc\d%"; 290a 291abcd 292drop table t1; 293select 'AA' like 'AA'; 294'AA' like 'AA' 2951 296select 'AA' like 'A%A'; 297'AA' like 'A%A' 2981 299select 'AA' like 'A%%A'; 300'AA' like 'A%%A' 3011 302select 'AA' like 'AA%'; 303'AA' like 'AA%' 3041 305select 'AA' like '%AA%'; 306'AA' like '%AA%' 3071 308select 'AA' like '%A'; 309'AA' like '%A' 3101 311select 'AA' like '%AA'; 312'AA' like '%AA' 3131 314select 'AA' like 'A%A%'; 315'AA' like 'A%A%' 3161 317select 'AA' like '_%_%'; 318'AA' like '_%_%' 3191 320select 'AA' like '%A%A'; 321'AA' like '%A%A' 3221 323select 'AAA'like 'A%A%A'; 324'AAA'like 'A%A%A' 3251 326select 'AZ' like 'AZ'; 327'AZ' like 'AZ' 3281 329select 'AZ' like 'A%Z'; 330'AZ' like 'A%Z' 3311 332select 'AZ' like 'A%%Z'; 333'AZ' like 'A%%Z' 3341 335select 'AZ' like 'AZ%'; 336'AZ' like 'AZ%' 3371 338select 'AZ' like '%AZ%'; 339'AZ' like '%AZ%' 3401 341select 'AZ' like '%Z'; 342'AZ' like '%Z' 3431 344select 'AZ' like '%AZ'; 345'AZ' like '%AZ' 3461 347select 'AZ' like 'A%Z%'; 348'AZ' like 'A%Z%' 3491 350select 'AZ' like '_%_%'; 351'AZ' like '_%_%' 3521 353select 'AZ' like '%A%Z'; 354'AZ' like '%A%Z' 3551 356select 'AZ' like 'A_'; 357'AZ' like 'A_' 3581 359select 'AZ' like '_Z'; 360'AZ' like '_Z' 3611 362select 'AMZ'like 'A%M%Z'; 363'AMZ'like 'A%M%Z' 3641 365CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ucs2); 366INSERT INTO t1 VALUES ('����'),('����'),('����'),('����'),('����'),('����'); 367INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 368INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 369INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 370SELECT * FROM t1 WHERE a LIKE '%����%'; 371a 372���� 373���� 374���� 375���� 376���� 377���� 378���������� 379���������� 380���������� 381���������� 382���������� 383���������� 384���������� 385���������� 386���������� 387���������� 388���������� 389���������� 390SELECT * FROM t1 WHERE a LIKE '%���%'; 391a 392���� 393���� 394���� 395���� 396���� 397���� 398���������� 399���������� 400���������� 401���������� 402���������� 403���������� 404���������� 405���������� 406���������� 407���������� 408���������� 409���������� 410SELECT * FROM t1 WHERE a LIKE '����%'; 411a 412���� 413���� 414���� 415���� 416���� 417���� 418���������� 419���������� 420���������� 421���������� 422���������� 423���������� 424���������� 425���������� 426���������� 427���������� 428���������� 429���������� 430SELECT * FROM t1 WHERE a LIKE '����%' COLLATE ucs2_bin; 431a 432���� 433���������� 434DROP TABLE t1; 435CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word)) 436ENGINE=MyISAM CHARACTER SET ucs2 COLLATE ucs2_general_ci; 437INSERT INTO t1 (word) VALUES ("cat"); 438SELECT * FROM t1 WHERE word LIKE "c%"; 439word 440cat 441SELECT * FROM t1 WHERE word LIKE "ca_"; 442word 443cat 444SELECT * FROM t1 WHERE word LIKE "cat"; 445word 446cat 447SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630025'; 448word 449cat 450SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630061005F'; 451word 452cat 453DROP TABLE t1; 454select insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066); 455insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066) 456abc 457select insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066); 458insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066) 459defc 460SET NAMES latin1; 461CREATE TABLE t1 ( 462word VARCHAR(64), 463bar INT(11) default 0, 464PRIMARY KEY (word)) 465ENGINE=MyISAM 466CHARSET ucs2 467COLLATE ucs2_general_ci ; 468INSERT INTO t1 (word) VALUES ("aar"); 469INSERT INTO t1 (word) VALUES ("a"); 470INSERT INTO t1 (word) VALUES ("aardvar"); 471INSERT INTO t1 (word) VALUES ("aardvark"); 472INSERT INTO t1 (word) VALUES ("aardvara"); 473INSERT INTO t1 (word) VALUES ("aardvarz"); 474EXPLAIN SELECT * FROM t1 ORDER BY word; 475id select_type table type possible_keys key key_len ref rows Extra 4761 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 477SELECT * FROM t1 ORDER BY word; 478word bar 479a 0 480aar 0 481aardvar 0 482aardvara 0 483aardvark 0 484aardvarz 0 485EXPLAIN SELECT word FROM t1 ORDER BY word; 486id select_type table type possible_keys key key_len ref rows Extra 4871 SIMPLE t1 index NULL PRIMARY 130 NULL 6 Using index 488SELECT word FROM t1 ORDER by word; 489word 490a 491aar 492aardvar 493aardvara 494aardvark 495aardvarz 496DROP TABLE t1; 497CREATE TABLE t1 ( 498word VARCHAR(64) , 499PRIMARY KEY (word)) 500ENGINE=MyISAM 501CHARSET ucs2 502COLLATE ucs2_general_ci; 503INSERT INTO t1 (word) VALUES ("aar"); 504INSERT INTO t1 (word) VALUES ("a"); 505INSERT INTO t1 (word) VALUES ("aardvar"); 506INSERT INTO t1 (word) VALUES ("aardvark"); 507INSERT INTO t1 (word) VALUES ("aardvara"); 508INSERT INTO t1 (word) VALUES ("aardvarz"); 509EXPLAIN SELECT * FROM t1 ORDER BY WORD; 510id select_type table type possible_keys key key_len ref rows Extra 5111 SIMPLE t1 index NULL PRIMARY 130 NULL 6 Using index 512SELECT * FROM t1 ORDER BY word; 513word 514a 515aar 516aardvar 517aardvara 518aardvark 519aardvarz 520DROP TABLE t1; 521CREATE TABLE t1 ( 522word TEXT, 523bar INT(11) AUTO_INCREMENT, 524PRIMARY KEY (bar)) 525ENGINE=MyISAM 526CHARSET ucs2 527COLLATE ucs2_general_ci ; 528INSERT INTO t1 (word) VALUES ("aar"); 529INSERT INTO t1 (word) VALUES ("a" ); 530INSERT INTO t1 (word) VALUES ("aardvar"); 531INSERT INTO t1 (word) VALUES ("aardvark"); 532INSERT INTO t1 (word) VALUES ("aardvara"); 533INSERT INTO t1 (word) VALUES ("aardvarz"); 534EXPLAIN SELECT * FROM t1 ORDER BY word; 535id select_type table type possible_keys key key_len ref rows Extra 5361 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 537SELECT * FROM t1 ORDER BY word; 538word bar 539a 2 540aar 1 541aardvar 3 542aardvara 5 543aardvark 4 544aardvarz 6 545EXPLAIN SELECT word FROM t1 ORDER BY word; 546id select_type table type possible_keys key key_len ref rows Extra 5471 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 548SELECT word FROM t1 ORDER BY word; 549word 550a 551aar 552aardvar 553aardvara 554aardvark 555aardvarz 556DROP TABLE t1; 557SELECT HEX(_ucs2 0x0); 558HEX(_ucs2 0x0) 5590000 560SELECT HEX(_ucs2 0x01); 561HEX(_ucs2 0x01) 5620001 563SELECT HEX(_ucs2 0x012); 564HEX(_ucs2 0x012) 5650012 566SELECT HEX(_ucs2 0x0123); 567HEX(_ucs2 0x0123) 5680123 569SELECT HEX(_ucs2 0x01234); 570HEX(_ucs2 0x01234) 57100001234 572SELECT HEX(_ucs2 0x012345); 573HEX(_ucs2 0x012345) 57400012345 575SELECT HEX(_ucs2 0x0123456); 576HEX(_ucs2 0x0123456) 57700123456 578SELECT HEX(_ucs2 0x01234567); 579HEX(_ucs2 0x01234567) 58001234567 581SELECT HEX(_ucs2 0x012345678); 582HEX(_ucs2 0x012345678) 583000012345678 584SELECT HEX(_ucs2 0x0123456789); 585HEX(_ucs2 0x0123456789) 586000123456789 587SELECT HEX(_ucs2 0x0123456789A); 588HEX(_ucs2 0x0123456789A) 58900123456789A 590SELECT HEX(_ucs2 0x0123456789AB); 591HEX(_ucs2 0x0123456789AB) 5920123456789AB 593SELECT HEX(_ucs2 0x0123456789ABC); 594HEX(_ucs2 0x0123456789ABC) 5950000123456789ABC 596SELECT HEX(_ucs2 0x0123456789ABCD); 597HEX(_ucs2 0x0123456789ABCD) 598000123456789ABCD 599SELECT HEX(_ucs2 0x0123456789ABCDE); 600HEX(_ucs2 0x0123456789ABCDE) 60100123456789ABCDE 602SELECT HEX(_ucs2 0x0123456789ABCDEF); 603HEX(_ucs2 0x0123456789ABCDEF) 6040123456789ABCDEF 605SELECT hex(cast(0xAA as char character set ucs2)); 606hex(cast(0xAA as char character set ucs2)) 60700AA 608SELECT hex(convert(0xAA using ucs2)); 609hex(convert(0xAA using ucs2)) 61000AA 611CREATE TABLE t1 (a char(10) character set ucs2); 612INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 613SELECT HEX(a) FROM t1; 614HEX(a) 615000A 61600AA 6170AAA 618AAAA 619000AAAAA 620DROP TABLE t1; 621CREATE TABLE t1 (a varchar(10) character set ucs2); 622INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 623SELECT HEX(a) FROM t1; 624HEX(a) 625000A 62600AA 6270AAA 628AAAA 629000AAAAA 630DROP TABLE t1; 631CREATE TABLE t1 (a text character set ucs2); 632INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 633SELECT HEX(a) FROM t1; 634HEX(a) 635000A 63600AA 6370AAA 638AAAA 639000AAAAA 640DROP TABLE t1; 641CREATE TABLE t1 (a mediumtext character set ucs2); 642INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 643SELECT HEX(a) FROM t1; 644HEX(a) 645000A 64600AA 6470AAA 648AAAA 649000AAAAA 650DROP TABLE t1; 651CREATE TABLE t1 (a longtext character set ucs2); 652INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 653SELECT HEX(a) FROM t1; 654HEX(a) 655000A 65600AA 6570AAA 658AAAA 659000AAAAA 660DROP TABLE t1; 661create table t1 (s1 char character set `ucs2` collate `ucs2_czech_ci`); 662insert into t1 values ('0'),('1'),('2'),('a'),('b'),('c'); 663select s1 from t1 where s1 > 'a' order by s1; 664s1 665b 666c 667drop table t1; 668create table t1(a char(1)) default charset = ucs2; 669insert into t1 values ('a'),('b'),('c'); 670alter table t1 modify a char(5); 671select a, hex(a) from t1; 672a hex(a) 673a 0061 674b 0062 675c 0063 676drop table t1; 677set @ivar= 1234; 678set @str1 = 'select ?'; 679set @str2 = convert(@str1 using ucs2); 680prepare stmt1 from @str2; 681execute stmt1 using @ivar; 682? 6831234 684set names latin1; 685create table t1 (a enum('x','y','z') character set ucs2); 686show create table t1; 687Table Create Table 688t1 CREATE TABLE `t1` ( 689 `a` enum('x','y','z') CHARACTER SET ucs2 DEFAULT NULL 690) ENGINE=MyISAM DEFAULT CHARSET=latin1 691insert into t1 values ('x'); 692insert into t1 values ('y'); 693insert into t1 values ('z'); 694select a, hex(a) from t1 order by a; 695a hex(a) 696x 0078 697y 0079 698z 007A 699alter table t1 change a a enum('x','y','z','d','e','�','�','�') character set ucs2; 700show create table t1; 701Table Create Table 702t1 CREATE TABLE `t1` ( 703 `a` enum('x','y','z','d','e','�','�','�') CHARACTER SET ucs2 DEFAULT NULL 704) ENGINE=MyISAM DEFAULT CHARSET=latin1 705insert into t1 values ('D'); 706insert into t1 values ('E '); 707insert into t1 values ('�'); 708insert into t1 values ('�'); 709insert into t1 values ('�'); 710select a, hex(a) from t1 order by a; 711a hex(a) 712x 0078 713y 0079 714z 007A 715d 0064 716e 0065 717� 00E4 718� 00F6 719� 00FC 720drop table t1; 721create table t1 (a set ('x','y','z','�','�','�') character set ucs2); 722show create table t1; 723Table Create Table 724t1 CREATE TABLE `t1` ( 725 `a` set('x','y','z','�','�','�') CHARACTER SET ucs2 DEFAULT NULL 726) ENGINE=MyISAM DEFAULT CHARSET=latin1 727insert into t1 values ('x'); 728insert into t1 values ('y'); 729insert into t1 values ('z'); 730insert into t1 values ('x,y'); 731insert into t1 values ('x,y,z,�,�,�'); 732select a, hex(a) from t1 order by a; 733a hex(a) 734x 0078 735y 0079 736x,y 0078002C0079 737z 007A 738x,y,z,�,�,� 0078002C0079002C007A002C00E4002C00F6002C00FC 739drop table t1; 740create table t1(a enum('a','b','c')) default character set ucs2; 741insert into t1 values('a'),('b'),('c'); 742alter table t1 add b char(1); 743show warnings; 744Level Code Message 745select * from t1 order by a; 746a b 747a NULL 748b NULL 749c NULL 750drop table t1; 751SET collation_connection='ucs2_general_ci'; 752create table t1 select repeat('a',4000) a; 753delete from t1; 754insert into t1 values ('a'), ('a '), ('a\t'); 755select collation(a),hex(a) from t1 order by a; 756collation(a) hex(a) 757ucs2_general_ci 00610009 758ucs2_general_ci 0061 759ucs2_general_ci 00610020 760drop table t1; 761# 762# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP 763# 764SELECT @@collation_connection; 765@@collation_connection 766ucs2_general_ci 767CREATE TABLE t1 (i INT) ENGINE=MyISAM; 768INSERT INTO t1 VALUES (1),(2); 769SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP; 770i 7711 7721 773SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP; 774i 7751 7761 777DROP TABLE t1; 778# 779# MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations 780# 781SELECT @@collation_connection; 782@@collation_connection 783ucs2_general_ci 784CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0; 785INSERT INTO t1 (a) VALUES ("a"); 786INSERT INTO t1 (a) VALUES ("b"); 787INSERT INTO t1 (a) VALUES ("c"); 788INSERT INTO t1 (a) VALUES ("d"); 789INSERT INTO t1 (a) VALUES ("e"); 790INSERT INTO t1 (a) VALUES ("f"); 791INSERT INTO t1 (a) VALUES ("g"); 792INSERT INTO t1 (a) VALUES ("h"); 793INSERT INTO t1 (a) VALUES ("i"); 794INSERT INTO t1 (a) VALUES ("j"); 795INSERT INTO t1 (a) VALUES ("k"); 796INSERT INTO t1 (a) VALUES ("l"); 797INSERT INTO t1 (a) VALUES ("m"); 798SELECT * FROM t1 ORDER BY LOWER(a); 799a 800a 801b 802c 803d 804e 805f 806g 807h 808i 809j 810k 811l 812m 813SELECT * FROM t1 ORDER BY LOWER(a) DESC; 814a 815m 816l 817k 818j 819i 820h 821g 822f 823e 824d 825c 826b 827a 828DROP TABLE t1; 829select @@collation_connection; 830@@collation_connection 831ucs2_general_ci 832create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; 833insert into t1 values('abcdef'); 834insert into t1 values('_bcdef'); 835insert into t1 values('a_cdef'); 836insert into t1 values('ab_def'); 837insert into t1 values('abc_ef'); 838insert into t1 values('abcd_f'); 839insert into t1 values('abcde_'); 840select c1 as c1u from t1 where c1 like 'ab\_def'; 841c1u 842ab_def 843select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; 844c2h 845ab_def 846drop table t1; 847SELECT @@collation_connection; 848@@collation_connection 849ucs2_general_ci 850SELECT '\%b' LIKE '%\%'; 851'\%b' LIKE '%\%' 8520 853"BEGIN ctype_german.inc" 854drop table if exists t1; 855create table t1 as select repeat(' ', 64) as s1; 856select collation(s1) from t1; 857collation(s1) 858ucs2_general_ci 859delete from t1; 860INSERT INTO t1 VALUES ('ud'),('uf'); 861INSERT INTO t1 VALUES ('od'),('of'); 862INSERT INTO t1 VALUES ('e'); 863INSERT INTO t1 VALUES ('ad'),('af'); 864insert into t1 values ('a'),('ae'),(_latin1 0xE4); 865insert into t1 values ('o'),('oe'),(_latin1 0xF6); 866insert into t1 values ('s'),('ss'),(_latin1 0xDF); 867insert into t1 values ('u'),('ue'),(_latin1 0xFC); 868INSERT INTO t1 VALUES (_latin1 0xE6), (_latin1 0xC6); 869INSERT INTO t1 VALUES (_latin1 0x9C), (_latin1 0x8C); 870select s1, hex(s1) from t1 order by s1, binary s1; 871s1 hex(s1) 872a 0061 873� 00E4 874ad 00610064 875ae 00610065 876af 00610066 877e 0065 878o 006F 879� 00F6 880od 006F0064 881oe 006F0065 882of 006F0066 883s 0073 884� 00DF 885ss 00730073 886u 0075 887� 00FC 888ud 00750064 889ue 00750065 890uf 00750066 891� 00C6 892� 00E6 893� 0152 894� 0153 895select group_concat(s1 order by binary s1) from t1 group by s1; 896group_concat(s1 order by binary s1) 897a,� 898ad 899ae 900af 901e 902o,� 903od 904oe 905of 906s,� 907ss 908u,� 909ud 910ue 911uf 912�,� 913�,� 914SELECT s1, hex(s1), hex(weight_string(s1)) FROM t1 ORDER BY s1, BINARY(s1); 915s1 hex(s1) hex(weight_string(s1)) 916a 0061 0041 917� 00E4 0041 918ad 00610064 00410044 919ae 00610065 00410045 920af 00610066 00410046 921e 0065 0045 922o 006F 004F 923� 00F6 004F 924od 006F0064 004F0044 925oe 006F0065 004F0045 926of 006F0066 004F0046 927s 0073 0053 928� 00DF 0053 929ss 00730073 00530053 930u 0075 0055 931� 00FC 0055 932ud 00750064 00550044 933ue 00750065 00550045 934uf 00750066 00550046 935� 00C6 00C6 936� 00E6 00C6 937� 0152 0152 938� 0153 0152 939SELECT s1, hex(s1) FROM t1 WHERE s1='ae' ORDER BY s1, BINARY(s1); 940s1 hex(s1) 941ae 00610065 942drop table t1; 943CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a, 1 AS b LIMIT 0; 944SHOW CREATE TABLE t1; 945Table Create Table 946t1 CREATE TABLE `t1` ( 947 `a` varchar(1) CHARACTER SET ucs2 DEFAULT NULL, 948 `b` int(1) NOT NULL 949) ENGINE=MyISAM DEFAULT CHARSET=latin1 950INSERT INTO t1 VALUES ('s',0),(_latin1 0xDF,1); 951SELECT * FROM t1 ORDER BY a, b; 952a b 953s 0 954� 1 955SELECT * FROM t1 ORDER BY a DESC, b; 956a b 957s 0 958� 1 959SELECT * FROM t1 ORDER BY CONCAT(a), b; 960a b 961s 0 962� 1 963SELECT * FROM t1 ORDER BY CONCAT(a) DESC, b; 964a b 965s 0 966� 1 967DROP TABLE t1; 968"END ctype_german.inc" 969CREATE TABLE t1 AS 970SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d; 971ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b); 972INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5)); 973INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10)); 974INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11)); 975INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12)); 976SELECT hex(concat(repeat(0xF1F2, 10), '%')); 977hex(concat(repeat(0xF1F2, 10), '%')) 978F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F20025 9793 rows expected 980SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%'); 981a hex(b) c 9822 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 NULL 9833 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 NULL 9844 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 NULL 985DROP TABLE t1; 986# 987# MDEV-4842 STR_TO_DATE does not work with UCS2/UTF16/UTF32 988# 989SELECT @@character_set_connection, HEX(CAST(_utf8'÷' AS CHAR)); 990@@character_set_connection HEX(CAST(_utf8'÷' AS CHAR)) 991ucs2 00F7 992SELECT STR_TO_DATE(CAST(_utf8'2001÷01÷01' AS CHAR),CAST(_utf8'%Y÷%m÷%d' AS CHAR)); 993STR_TO_DATE(CAST(_utf8'2001÷01÷01' AS CHAR),CAST(_utf8'%Y÷%m÷%d' AS CHAR)) 9942001-01-01 995CREATE TABLE t1 AS SELECT REPEAT(' ', 64) AS subject, REPEAT(' ',64) AS pattern LIMIT 0; 996SHOW COLUMNS FROM t1; 997Field Type Null Key Default Extra 998subject varchar(64) YES NULL 999pattern varchar(64) YES NULL 1000INSERT INTO t1 VALUES (_utf8'2001÷01÷01',_utf8'%Y÷%m÷%d'); 1001SELECT HEX(subject),HEX(pattern),STR_TO_DATE(subject, pattern) FROM t1; 1002HEX(subject) HEX(pattern) STR_TO_DATE(subject, pattern) 1003003200300030003100F70030003100F700300031 0025005900F70025006D00F700250064 2001-01-01 00:00:00.000000 1004DROP TABLE t1; 1005SET NAMES latin1; 1006SET collation_connection='ucs2_bin'; 1007create table t1 select repeat('a',4000) a; 1008delete from t1; 1009insert into t1 values ('a'), ('a '), ('a\t'); 1010select collation(a),hex(a) from t1 order by a; 1011collation(a) hex(a) 1012ucs2_bin 00610009 1013ucs2_bin 0061 1014ucs2_bin 00610020 1015drop table t1; 1016# 1017# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP 1018# 1019SELECT @@collation_connection; 1020@@collation_connection 1021ucs2_bin 1022CREATE TABLE t1 (i INT) ENGINE=MyISAM; 1023INSERT INTO t1 VALUES (1),(2); 1024SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP; 1025i 10261 10271 1028SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP; 1029i 10301 10311 1032DROP TABLE t1; 1033# 1034# MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations 1035# 1036SELECT @@collation_connection; 1037@@collation_connection 1038ucs2_bin 1039CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0; 1040INSERT INTO t1 (a) VALUES ("a"); 1041INSERT INTO t1 (a) VALUES ("b"); 1042INSERT INTO t1 (a) VALUES ("c"); 1043INSERT INTO t1 (a) VALUES ("d"); 1044INSERT INTO t1 (a) VALUES ("e"); 1045INSERT INTO t1 (a) VALUES ("f"); 1046INSERT INTO t1 (a) VALUES ("g"); 1047INSERT INTO t1 (a) VALUES ("h"); 1048INSERT INTO t1 (a) VALUES ("i"); 1049INSERT INTO t1 (a) VALUES ("j"); 1050INSERT INTO t1 (a) VALUES ("k"); 1051INSERT INTO t1 (a) VALUES ("l"); 1052INSERT INTO t1 (a) VALUES ("m"); 1053SELECT * FROM t1 ORDER BY LOWER(a); 1054a 1055a 1056b 1057c 1058d 1059e 1060f 1061g 1062h 1063i 1064j 1065k 1066l 1067m 1068SELECT * FROM t1 ORDER BY LOWER(a) DESC; 1069a 1070m 1071l 1072k 1073j 1074i 1075h 1076g 1077f 1078e 1079d 1080c 1081b 1082a 1083DROP TABLE t1; 1084select @@collation_connection; 1085@@collation_connection 1086ucs2_bin 1087create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; 1088insert into t1 values('abcdef'); 1089insert into t1 values('_bcdef'); 1090insert into t1 values('a_cdef'); 1091insert into t1 values('ab_def'); 1092insert into t1 values('abc_ef'); 1093insert into t1 values('abcd_f'); 1094insert into t1 values('abcde_'); 1095select c1 as c1u from t1 where c1 like 'ab\_def'; 1096c1u 1097ab_def 1098select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; 1099c2h 1100ab_def 1101drop table t1; 1102SELECT @@collation_connection; 1103@@collation_connection 1104ucs2_bin 1105SELECT '\%b' LIKE '%\%'; 1106'\%b' LIKE '%\%' 11070 1108CREATE TABLE t1 AS 1109SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d; 1110ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b); 1111INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5)); 1112INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10)); 1113INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11)); 1114INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12)); 1115SELECT hex(concat(repeat(0xF1F2, 10), '%')); 1116hex(concat(repeat(0xF1F2, 10), '%')) 1117F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F20025 11183 rows expected 1119SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%'); 1120a hex(b) c 11212 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 NULL 11223 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 NULL 11234 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 NULL 1124DROP TABLE t1; 1125select hex(substr(_ucs2 0x00e400e50068,1)); 1126hex(substr(_ucs2 0x00e400e50068,1)) 112700E400E50068 1128select hex(substr(_ucs2 0x00e400e50068,2)); 1129hex(substr(_ucs2 0x00e400e50068,2)) 113000E50068 1131select hex(substr(_ucs2 0x00e400e50068,3)); 1132hex(substr(_ucs2 0x00e400e50068,3)) 11330068 1134select hex(substr(_ucs2 0x00e400e50068,-1)); 1135hex(substr(_ucs2 0x00e400e50068,-1)) 11360068 1137select hex(substr(_ucs2 0x00e400e50068,-2)); 1138hex(substr(_ucs2 0x00e400e50068,-2)) 113900E50068 1140select hex(substr(_ucs2 0x00e400e50068,-3)); 1141hex(substr(_ucs2 0x00e400e50068,-3)) 114200E400E50068 1143SET NAMES latin1; 1144SET collation_connection='ucs2_swedish_ci'; 1145CREATE TABLE t1 (Field1 int(10) default '0'); 1146INSERT INTO t1 VALUES ('-1'); 1147SELECT * FROM t1; 1148Field1 1149-1 1150DROP TABLE t1; 1151CREATE TABLE t1 (Field1 int(10) unsigned default '0'); 1152INSERT IGNORE INTO t1 VALUES ('-1'); 1153Warnings: 1154Warning 1264 Out of range value for column 'Field1' at row 1 1155DROP TABLE t1; 1156SET NAMES latin1; 1157SELECT CONVERT(103, CHAR(50) UNICODE); 1158CONVERT(103, CHAR(50) UNICODE) 1159103 1160SELECT CONVERT(103.0, CHAR(50) UNICODE); 1161CONVERT(103.0, CHAR(50) UNICODE) 1162103.0 1163SELECT CONVERT(-103, CHAR(50) UNICODE); 1164CONVERT(-103, CHAR(50) UNICODE) 1165-103 1166SELECT CONVERT(-103.0, CHAR(50) UNICODE); 1167CONVERT(-103.0, CHAR(50) UNICODE) 1168-103.0 1169CREATE TABLE t1 ( 1170a varchar(255) NOT NULL default '', 1171KEY a (a) 1172) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE ucs2_general_ci; 1173insert into t1 values (0x803d); 1174insert into t1 values (0x005b); 1175select hex(a) from t1; 1176hex(a) 1177005B 1178803D 1179drop table t1; 1180set sql_mode=""; 1181create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB; 1182set sql_mode=default; 1183insert into t1 values('a'); 1184create index t1f1 on t1(f1); 1185select f1 from t1 where f1 like 'a%'; 1186f1 1187a 1188drop table t1; 1189create table t1 (utext varchar(20) character set ucs2); 1190insert into t1 values ("lily"); 1191insert into t1 values ("river"); 1192prepare stmt from 'select utext from t1 where utext like ?'; 1193set @param1='%%'; 1194execute stmt using @param1; 1195utext 1196lily 1197river 1198execute stmt using @param1; 1199utext 1200lily 1201river 1202select utext from t1 where utext like '%%'; 1203utext 1204lily 1205river 1206drop table t1; 1207deallocate prepare stmt; 1208create table t1 ( 1209a char(10) unicode not null, 1210index a (a) 1211) engine=myisam; 1212insert into t1 values (repeat(0x201f, 10)); 1213insert into t1 values (repeat(0x2020, 10)); 1214insert into t1 values (repeat(0x2021, 10)); 1215explain select hex(a) from t1 order by a; 1216id select_type table type possible_keys key key_len ref rows Extra 12171 SIMPLE t1 index NULL a 20 NULL 3 Using index 1218select hex(a) from t1 order by a; 1219hex(a) 1220201F201F201F201F201F201F201F201F201F201F 12212020202020202020202020202020202020202020 12222021202120212021202120212021202120212021 1223alter table t1 drop index a; 1224select hex(a) from t1 order by a; 1225hex(a) 1226201F201F201F201F201F201F201F201F201F201F 12272020202020202020202020202020202020202020 12282021202120212021202120212021202120212021 1229drop table t1; 1230CREATE TABLE t1 (id int, s char(5) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci); 1231INSERT INTO t1 VALUES (1, 'ZZZZZ'), (1, 'ZZZ'), (2, 'ZZZ'), (2, 'ZZZZZ'); 1232SELECT id, MIN(s) FROM t1 GROUP BY id; 1233id MIN(s) 12341 ZZZ 12352 ZZZ 1236DROP TABLE t1; 1237drop table if exists bug20536; 1238set names latin1; 1239create table bug20536 (id bigint not null auto_increment primary key, name 1240varchar(255) character set ucs2 not null); 1241insert into `bug20536` (`id`,`name`) values (1, _latin1 x'7465737431'), (2, "'test\\_2'"); 1242select md5(name) from bug20536; 1243md5(name) 1244f4b7ce8b45a20e3c4e84bef515d1525c 124548d95db0d8305c2fe11548a3635c9385 1246select sha1(name) from bug20536; 1247sha1(name) 1248e0b52f38deddb9f9e8d5336b153592794cb49baf 1249677d4d505355eb5b0549b865fcae4b7f0c28aef5 1250select make_set(3, name, upper(name)) from bug20536; 1251make_set(3, name, upper(name)) 1252test1,TEST1 1253'test\_2','TEST\_2' 1254select export_set(5, name, upper(name)) from bug20536; 1255export_set(5, name, upper(name)) 1256test1,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 1257'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' 1258select export_set(5, name, upper(name), ",", 5) from bug20536; 1259export_set(5, name, upper(name), ",", 5) 1260test1,TEST1,test1,TEST1,TEST1 1261'test\_2','TEST\_2','test\_2','TEST\_2','TEST\_2' 1262CREATE TABLE t1 ( 1263status enum('active','passive') collate latin1_general_ci 1264NOT NULL default 'passive' 1265); 1266SHOW CREATE TABLE t1; 1267Table Create Table 1268t1 CREATE TABLE `t1` ( 1269 `status` enum('active','passive') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'passive' 1270) ENGINE=MyISAM DEFAULT CHARSET=latin1 1271ALTER TABLE t1 ADD a int NOT NULL AFTER status; 1272CREATE TABLE t2 ( 1273status enum('active','passive') collate ucs2_turkish_ci 1274NOT NULL default 'passive' 1275); 1276SHOW CREATE TABLE t2; 1277Table Create Table 1278t2 CREATE TABLE `t2` ( 1279 `status` enum('active','passive') CHARACTER SET ucs2 COLLATE ucs2_turkish_ci NOT NULL DEFAULT 'passive' 1280) ENGINE=MyISAM DEFAULT CHARSET=latin1 1281ALTER TABLE t2 ADD a int NOT NULL AFTER status; 1282DROP TABLE t1,t2; 1283select password(name) from bug20536; 1284password(name) 1285*286C12C0F32248BD65B30EE65F3ECFB2AA3F7849 1286*947A5674312754578F132655C74A11533B105FF6 1287select old_password(name) from bug20536; 1288old_password(name) 128910e155cb44e2adb5 129014e500b131773991 1291select quote(name) from bug20536; 1292quote(name) 1293'test1' 1294'\'test\\_2\'' 1295drop table bug20536; 1296set names ucs2; 1297ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2' 1298set names ucs2 collate ucs2_bin; 1299ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2' 1300set character_set_client= ucs2; 1301ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2' 1302set character_set_client= concat('ucs', substr('2', 1)); 1303ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2' 1304CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci); 1305INSERT INTO t1 VALUES('abcd'); 1306SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE); 1307a 1308abcd 1309DROP TABLE t1; 1310End of 4.1 tests 1311CREATE TABLE t1 (a varchar(64) character set ucs2, b decimal(10,3)); 1312INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0); 1313update t1 set b=a; 1314SELECT * FROM t1; 1315a b 13161.1 1.100 13172.1 2.100 1318DROP TABLE t1; 1319create table t1 (utext varchar(20) character set ucs2); 1320insert into t1 values ("lily"); 1321insert into t1 values ("river"); 1322prepare stmt from 'select utext from t1 where utext like ?'; 1323set @param1='%%'; 1324execute stmt using @param1; 1325utext 1326lily 1327river 1328execute stmt using @param1; 1329utext 1330lily 1331river 1332select utext from t1 where utext like '%%'; 1333utext 1334lily 1335river 1336drop table t1; 1337deallocate prepare stmt; 1338set names latin1; 1339set character_set_connection=ucs2; 1340select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); 1341soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb') 1342 H000 H4142 I51231 1343select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb')); 1344hex(soundex('')) hex(soundex('he')) hex(soundex('hello all folks')) hex(soundex('#3556 in bugdb')) 1345 0048003000300030 00480034003100340032 004900350031003200330031 1346select 'mood' sounds like 'mud'; 1347'mood' sounds like 'mud' 13481 1349select hex(soundex(_ucs2 0x041004110412)); 1350hex(soundex(_ucs2 0x041004110412)) 13510410003000300030 1352select hex(soundex(_ucs2 0x00BF00C0)); 1353hex(soundex(_ucs2 0x00BF00C0)) 135400C0003000300030 1355set names latin1; 1356create table t1(a blob, b text charset utf8, c text charset ucs2); 1357select data_type, character_octet_length, character_maximum_length 1358from information_schema.columns where table_name='t1'; 1359data_type character_octet_length character_maximum_length 1360blob 65535 65535 1361text 65535 65535 1362text 65535 32767 1363drop table t1; 1364create table t1 (a char(1) character set ucs2); 1365insert into t1 values ('a'),('b'),('c'); 1366select hex(group_concat(a)) from t1; 1367hex(group_concat(a)) 13680061002C0062002C0063 1369select collation(group_concat(a)) from t1; 1370collation(group_concat(a)) 1371ucs2_general_ci 1372drop table t1; 1373set names latin1; 1374create table t1 (a char(1) character set latin1); 1375insert into t1 values ('a'),('b'),('c'); 1376set character_set_connection=ucs2; 1377select hex(group_concat(a separator ',')) from t1; 1378hex(group_concat(a separator ',')) 1379612C622C63 1380select collation(group_concat(a separator ',')) from t1; 1381collation(group_concat(a separator ',')) 1382latin1_swedish_ci 1383drop table t1; 1384set names latin1; 1385create table t1 (s1 char(1) character set ascii, s2 char(1) character set ucs2); 1386insert into t1 (s1) values (0x7f); 1387update t1 set s2 = s1; 1388select hex(s2) from t1; 1389hex(s2) 1390007F 1391select hex(convert(s1 using latin1)) from t1; 1392hex(convert(s1 using latin1)) 13937F 1394drop table t1; 1395create table t1 (a varchar(15) character set ascii not null, b int); 1396insert into t1 values ('a',1); 1397select concat(a,if(b<10,_ucs2 0x0061,_ucs2 0x0062)) from t1; 1398concat(a,if(b<10,_ucs2 0x0061,_ucs2 0x0062)) 1399aa 1400select concat(a,if(b>10,_ucs2 0x0061,_ucs2 0x0062)) from t1; 1401concat(a,if(b>10,_ucs2 0x0061,_ucs2 0x0062)) 1402ab 1403select * from t1 where a=if(b<10,_ucs2 0x0061,_ucs2 0x0062); 1404a b 1405a 1 1406select * from t1 where a=if(b>10,_ucs2 0x0061,_ucs2 0x0062); 1407a b 1408select concat(a,if(b<10,_ucs2 0x00C0,_ucs2 0x0062)) from t1; 1409ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat' 1410select concat(a,if(b>10,_ucs2 0x00C0,_ucs2 0x0062)) from t1; 1411ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat' 1412select concat(a,if(b<10,_ucs2 0x0062,_ucs2 0x00C0)) from t1; 1413ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat' 1414select concat(a,if(b>10,_ucs2 0x0062,_ucs2 0x00C0)) from t1; 1415ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation 'concat' 1416select * from t1 where a=if(b<10,_ucs2 0x00C0,_ucs2 0x0062); 1417ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '=' 1418select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0); 1419ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '=' 1420drop table t1; 1421CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET UCS2); 1422INSERT INTO t1 VALUES ('a'); 1423SET @@sql_mode=pad_char_to_full_length; 1424SELECT HEX(s1) FROM t1; 1425HEX(s1) 142600610020002000200020 1427SET @@sql_mode=default; 1428SELECT HEX(s1) FROM t1; 1429HEX(s1) 14300061 1431DROP TABLE t1; 1432set collation_connection=ucs2_general_ci; 1433drop table if exists t1; 1434create table t1 as 1435select repeat(' ', 64) as s1, repeat(' ',64) as s2 1436union 1437select null, null; 1438show create table t1; 1439Table Create Table 1440t1 CREATE TABLE `t1` ( 1441 `s1` varchar(64) CHARACTER SET ucs2 DEFAULT NULL, 1442 `s2` varchar(64) CHARACTER SET ucs2 DEFAULT NULL 1443) ENGINE=MyISAM DEFAULT CHARSET=latin1 1444delete from t1; 1445insert into t1 values('aaa','aaa'); 1446insert into t1 values('aaa|qqq','qqq'); 1447insert into t1 values('gheis','^[^a-dXYZ]+$'); 1448insert into t1 values('aab','^aa?b'); 1449insert into t1 values('Baaan','^Ba*n'); 1450insert into t1 values('aaa','qqq|aaa'); 1451insert into t1 values('qqq','qqq|aaa'); 1452insert into t1 values('bbb','qqq|aaa'); 1453insert into t1 values('bbb','qqq'); 1454insert into t1 values('aaa','aba'); 1455insert into t1 values(null,'abc'); 1456insert into t1 values('def',null); 1457insert into t1 values(null,null); 1458insert into t1 values('ghi','ghi['); 1459select HIGH_PRIORITY s1 regexp s2 from t1; 1460s1 regexp s2 14610 14620 14630 14641 14651 14661 14671 14681 14691 14701 1471NULL 1472NULL 1473NULL 1474NULL 1475drop table t1; 1476set names latin1; 1477select hex(char(0x41 using ucs2)); 1478hex(char(0x41 using ucs2)) 14790041 1480SET character_set_connection=ucs2; 1481SELECT CHARSET(DAYNAME(19700101)); 1482CHARSET(DAYNAME(19700101)) 1483ucs2 1484SELECT CHARSET(MONTHNAME(19700101)); 1485CHARSET(MONTHNAME(19700101)) 1486ucs2 1487SELECT LOWER(DAYNAME(19700101)); 1488LOWER(DAYNAME(19700101)) 1489thursday 1490SELECT LOWER(MONTHNAME(19700101)); 1491LOWER(MONTHNAME(19700101)) 1492january 1493SELECT UPPER(DAYNAME(19700101)); 1494UPPER(DAYNAME(19700101)) 1495THURSDAY 1496SELECT UPPER(MONTHNAME(19700101)); 1497UPPER(MONTHNAME(19700101)) 1498JANUARY 1499SELECT HEX(MONTHNAME(19700101)); 1500HEX(MONTHNAME(19700101)) 1501004A0061006E0075006100720079 1502SELECT HEX(DAYNAME(19700101)); 1503HEX(DAYNAME(19700101)) 150400540068007500720073006400610079 1505SET LC_TIME_NAMES=ru_RU; 1506SET NAMES utf8; 1507SET character_set_connection=ucs2; 1508SELECT CHARSET(DAYNAME(19700101)); 1509CHARSET(DAYNAME(19700101)) 1510ucs2 1511SELECT CHARSET(MONTHNAME(19700101)); 1512CHARSET(MONTHNAME(19700101)) 1513ucs2 1514SELECT LOWER(DAYNAME(19700101)); 1515LOWER(DAYNAME(19700101)) 1516четверг 1517SELECT LOWER(MONTHNAME(19700101)); 1518LOWER(MONTHNAME(19700101)) 1519января 1520SELECT UPPER(DAYNAME(19700101)); 1521UPPER(DAYNAME(19700101)) 1522ЧЕТВЕРГ 1523SELECT UPPER(MONTHNAME(19700101)); 1524UPPER(MONTHNAME(19700101)) 1525ЯНВАРЯ 1526SELECT HEX(MONTHNAME(19700101)); 1527HEX(MONTHNAME(19700101)) 1528042F043D043204300440044F 1529SELECT HEX(DAYNAME(19700101)); 1530HEX(DAYNAME(19700101)) 15310427043504420432043504400433 1532SET character_set_connection=latin1; 1533# 1534# Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817 1535# 1536CREATE TABLE t1 (a CHAR(1) CHARSET ascii, b CHAR(1) CHARSET latin1); 1537CREATE VIEW v1 AS SELECT 1 from t1 1538WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1')); 1539DROP VIEW v1; 1540DROP TABLE t1; 1541# 1542# Bug#59648 my_strtoll10_mb2: Assertion `(*endptr - s) % 2 == 0' failed. 1543# 1544SELECT HEX(CHAR(COALESCE(NULL, CHAR(COUNT('%s') USING ucs2), 1, @@global.license, NULL) USING cp850)); 1545HEX(CHAR(COALESCE(NULL, CHAR(COUNT('%s') USING ucs2), 1, @@global.license, NULL) USING cp850)) 154600 1547SELECT CONVERT(QUOTE(CHAR(0xf5 using ucs2)), SIGNED); 1548CONVERT(QUOTE(CHAR(0xf5 using ucs2)), SIGNED) 15490 1550Warnings: 1551Warning 1292 Truncated incorrect INTEGER value: ''õ'' 1552End of 5.0 tests 1553# 1554# Start of 5.1 tests 1555# 1556SET NAMES utf8; 1557CREATE TABLE t1 ( 1558a varchar(10) CHARACTER SET ucs2 COLLATE ucs2_czech_ci, 1559key(a) 1560); 1561INSERT INTO t1 VALUES 1562('aa'),('bb'),('cc'),('dd'),('ee'),('ff'),('gg'),('hh'),('ii'), 1563('jj'),('kk'),('ll'),('mm'),('nn'),('oo'),('pp'),('rr'),('ss'), 1564('tt'),('uu'),('vv'),('ww'),('xx'),('yy'),('zz'); 1565INSERT INTO t1 VALUES ('ca'),('cz'),('ch'); 1566INSERT INTO t1 VALUES ('da'),('dz'), (X'0064017E'); 1567EXPLAIN SELECT * FROM t1 WHERE a LIKE 'b%'; 1568id select_type table type possible_keys key key_len ref rows Extra 15691 SIMPLE t1 range a a 23 NULL 1 Using where; Using index 1570EXPLAIN SELECT * FROM t1 WHERE a LIKE 'c%'; 1571id select_type table type possible_keys key key_len ref rows Extra 15721 SIMPLE t1 index a a 23 NULL 31 Using where; Using index 1573SELECT * FROM t1 WHERE a LIKE 'c%'; 1574a 1575ca 1576cc 1577cz 1578ch 1579EXPLAIN SELECT * FROM t1 WHERE a LIKE 'ch%'; 1580id select_type table type possible_keys key key_len ref rows Extra 15811 SIMPLE t1 range a a 23 NULL 1 Using where; Using index 1582SELECT * FROM t1 WHERE a LIKE 'ch%'; 1583a 1584ch 1585ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci; 1586EXPLAIN SELECT * FROM t1 WHERE a LIKE 'd%'; 1587id select_type table type possible_keys key key_len ref rows Extra 15881 SIMPLE t1 index a a 23 NULL 31 Using where; Using index 1589SELECT hex(concat('d',_ucs2 0x017E,'%')); 1590hex(concat('d',_ucs2 0x017E,'%')) 15910064017E0025 1592EXPLAIN SELECT * FROM t1 WHERE a LIKE concat('d',_ucs2 0x017E,'%'); 1593id select_type table type possible_keys key key_len ref rows Extra 15941 SIMPLE t1 range a a 23 NULL 1 Using where; Using index 1595SELECT hex(a) FROM t1 WHERE a LIKE concat('D',_ucs2 0x017E,'%'); 1596hex(a) 15970064017E 1598DROP TABLE t1; 1599# 1600# End of 5.1 tests 1601# 1602# 1603# Start of 5.5 tests 1604# 1605SET NAMES latin1; 1606SET collation_connection=ucs2_general_ci; 1607SET TIME_ZONE = _latin1 '+03:00'; 1608# 1609# Start of WL#2649 Number-to-string conversions 1610# 1611select hex(concat(1)); 1612hex(concat(1)) 16130031 1614create table t1 as select concat(1) as c1; 1615show create table t1; 1616Table Create Table 1617t1 CREATE TABLE `t1` ( 1618 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 1619) ENGINE=MyISAM DEFAULT CHARSET=latin1 1620select hex(c1) from t1; 1621hex(c1) 16220031 1623drop table t1; 1624select hex(concat(18446744073709551615)); 1625hex(concat(18446744073709551615)) 162600310038003400340036003700340034003000370033003700300039003500350031003600310035 1627create table t1 as select concat(18446744073709551615) as c1; 1628show create table t1; 1629Table Create Table 1630t1 CREATE TABLE `t1` ( 1631 `c1` varchar(20) CHARACTER SET ucs2 DEFAULT NULL 1632) ENGINE=MyISAM DEFAULT CHARSET=latin1 1633select hex(c1) from t1; 1634hex(c1) 163500310038003400340036003700340034003000370033003700300039003500350031003600310035 1636drop table t1; 1637select hex(concat(1.1)); 1638hex(concat(1.1)) 16390031002E0031 1640create table t1 as select concat(1.1) as c1; 1641show create table t1; 1642Table Create Table 1643t1 CREATE TABLE `t1` ( 1644 `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 1645) ENGINE=MyISAM DEFAULT CHARSET=latin1 1646select hex(c1) from t1; 1647hex(c1) 16480031002E0031 1649drop table t1; 1650select hex(concat('a', 1+2)), charset(concat(1+2)); 1651hex(concat('a', 1+2)) charset(concat(1+2)) 165200610033 ucs2 1653create table t1 as select concat(1+2) as c1; 1654show create table t1; 1655Table Create Table 1656t1 CREATE TABLE `t1` ( 1657 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 1658) ENGINE=MyISAM DEFAULT CHARSET=latin1 1659drop table t1; 1660select hex(concat(1-2)); 1661hex(concat(1-2)) 1662002D0031 1663create table t1 as select concat(1-2) as c1; 1664show create table t1; 1665Table Create Table 1666t1 CREATE TABLE `t1` ( 1667 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 1668) ENGINE=MyISAM DEFAULT CHARSET=latin1 1669drop table t1; 1670select hex(concat(1*2)); 1671hex(concat(1*2)) 16720032 1673create table t1 as select concat(1*2) as c1; 1674show create table t1; 1675Table Create Table 1676t1 CREATE TABLE `t1` ( 1677 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 1678) ENGINE=MyISAM DEFAULT CHARSET=latin1 1679drop table t1; 1680select hex(concat(1/2)); 1681hex(concat(1/2)) 16820030002E0035003000300030 1683create table t1 as select concat(1/2) as c1; 1684show create table t1; 1685Table Create Table 1686t1 CREATE TABLE `t1` ( 1687 `c1` varchar(7) CHARACTER SET ucs2 DEFAULT NULL 1688) ENGINE=MyISAM DEFAULT CHARSET=latin1 1689drop table t1; 1690select hex(concat(1 div 2)); 1691hex(concat(1 div 2)) 16920030 1693create table t1 as select concat(1 div 2) as c1; 1694show create table t1; 1695Table Create Table 1696t1 CREATE TABLE `t1` ( 1697 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 1698) ENGINE=MyISAM DEFAULT CHARSET=latin1 1699drop table t1; 1700select hex(concat(1 % 2)); 1701hex(concat(1 % 2)) 17020031 1703create table t1 as select concat(1 % 2) as c1; 1704show create table t1; 1705Table Create Table 1706t1 CREATE TABLE `t1` ( 1707 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 1708) ENGINE=MyISAM DEFAULT CHARSET=latin1 1709drop table t1; 1710select hex(concat(-1)); 1711hex(concat(-1)) 1712002D0031 1713create table t1 as select concat(-1) as c1; 1714show create table t1; 1715Table Create Table 1716t1 CREATE TABLE `t1` ( 1717 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 1718) ENGINE=MyISAM DEFAULT CHARSET=latin1 1719drop table t1; 1720select hex(concat(-(1+2))); 1721hex(concat(-(1+2))) 1722002D0033 1723create table t1 as select concat(-(1+2)) as c1; 1724show create table t1; 1725Table Create Table 1726t1 CREATE TABLE `t1` ( 1727 `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 1728) ENGINE=MyISAM DEFAULT CHARSET=latin1 1729drop table t1; 1730select hex(concat(1|2)); 1731hex(concat(1|2)) 17320033 1733create table t1 as select concat(1|2) as c1; 1734show create table t1; 1735Table Create Table 1736t1 CREATE TABLE `t1` ( 1737 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 1738) ENGINE=MyISAM DEFAULT CHARSET=latin1 1739drop table t1; 1740select hex(concat(1&2)); 1741hex(concat(1&2)) 17420030 1743create table t1 as select concat(1&2) as c1; 1744show create table t1; 1745Table Create Table 1746t1 CREATE TABLE `t1` ( 1747 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 1748) ENGINE=MyISAM DEFAULT CHARSET=latin1 1749drop table t1; 1750select hex(concat(bit_count(12))); 1751hex(concat(bit_count(12))) 17520032 1753create table t1 as select concat(bit_count(12)) as c1; 1754show create table t1; 1755Table Create Table 1756t1 CREATE TABLE `t1` ( 1757 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 1758) ENGINE=MyISAM DEFAULT CHARSET=latin1 1759drop table t1; 1760select hex(concat(2<<1)); 1761hex(concat(2<<1)) 17620034 1763create table t1 as select concat(2<<1) as c1; 1764show create table t1; 1765Table Create Table 1766t1 CREATE TABLE `t1` ( 1767 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 1768) ENGINE=MyISAM DEFAULT CHARSET=latin1 1769drop table t1; 1770select hex(concat(2>>1)); 1771hex(concat(2>>1)) 17720031 1773create table t1 as select concat(2>>1) as c1; 1774show create table t1; 1775Table Create Table 1776t1 CREATE TABLE `t1` ( 1777 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 1778) ENGINE=MyISAM DEFAULT CHARSET=latin1 1779drop table t1; 1780select hex(concat(~0)); 1781hex(concat(~0)) 178200310038003400340036003700340034003000370033003700300039003500350031003600310035 1783create table t1 as select concat(~0) as c1; 1784show create table t1; 1785Table Create Table 1786t1 CREATE TABLE `t1` ( 1787 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 1788) ENGINE=MyISAM DEFAULT CHARSET=latin1 1789drop table t1; 1790select hex(concat(3^2)); 1791hex(concat(3^2)) 17920031 1793create table t1 as select concat(3^2) as c1; 1794show create table t1; 1795Table Create Table 1796t1 CREATE TABLE `t1` ( 1797 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 1798) ENGINE=MyISAM DEFAULT CHARSET=latin1 1799drop table t1; 1800select hex(concat(abs(-2))); 1801hex(concat(abs(-2))) 18020032 1803create table t1 as select concat(abs(-2)) as c1; 1804show create table t1; 1805Table Create Table 1806t1 CREATE TABLE `t1` ( 1807 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 1808) ENGINE=MyISAM DEFAULT CHARSET=latin1 1809drop table t1; 1810select hex(left(concat(exp(2)),1)); 1811hex(left(concat(exp(2)),1)) 18120037 1813create table t1 as select concat(exp(2)) as c1; 1814show create table t1; 1815Table Create Table 1816t1 CREATE TABLE `t1` ( 1817 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1818) ENGINE=MyISAM DEFAULT CHARSET=latin1 1819drop table t1; 1820select hex(left(concat(log(2)),1)); 1821hex(left(concat(log(2)),1)) 18220030 1823create table t1 as select concat(log(2)) as c1; 1824show create table t1; 1825Table Create Table 1826t1 CREATE TABLE `t1` ( 1827 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1828) ENGINE=MyISAM DEFAULT CHARSET=latin1 1829drop table t1; 1830select hex(left(concat(log2(2)),1)); 1831hex(left(concat(log2(2)),1)) 18320031 1833create table t1 as select concat(log2(2)) as c1; 1834show create table t1; 1835Table Create Table 1836t1 CREATE TABLE `t1` ( 1837 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1838) ENGINE=MyISAM DEFAULT CHARSET=latin1 1839drop table t1; 1840select hex(left(concat(log10(2)),1)); 1841hex(left(concat(log10(2)),1)) 18420030 1843create table t1 as select concat(log10(2)) as c1; 1844show create table t1; 1845Table Create Table 1846t1 CREATE TABLE `t1` ( 1847 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1848) ENGINE=MyISAM DEFAULT CHARSET=latin1 1849drop table t1; 1850select hex(left(concat(sqrt(2)),1)); 1851hex(left(concat(sqrt(2)),1)) 18520031 1853create table t1 as select concat(sqrt(2)) as c1; 1854show create table t1; 1855Table Create Table 1856t1 CREATE TABLE `t1` ( 1857 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1858) ENGINE=MyISAM DEFAULT CHARSET=latin1 1859drop table t1; 1860select hex(left(concat(pow(2,2)),1)); 1861hex(left(concat(pow(2,2)),1)) 18620034 1863create table t1 as select concat(pow(2,2)) as c1; 1864show create table t1; 1865Table Create Table 1866t1 CREATE TABLE `t1` ( 1867 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1868) ENGINE=MyISAM DEFAULT CHARSET=latin1 1869drop table t1; 1870select hex(left(concat(acos(0.5)),1)); 1871hex(left(concat(acos(0.5)),1)) 18720031 1873create table t1 as select concat(acos(0.5)) as c1; 1874show create table t1; 1875Table Create Table 1876t1 CREATE TABLE `t1` ( 1877 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1878) ENGINE=MyISAM DEFAULT CHARSET=latin1 1879drop table t1; 1880select hex(left(concat(asin(0.5)),1)); 1881hex(left(concat(asin(0.5)),1)) 18820030 1883create table t1 as select concat(asin(0.5)) as c1; 1884show create table t1; 1885Table Create Table 1886t1 CREATE TABLE `t1` ( 1887 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1888) ENGINE=MyISAM DEFAULT CHARSET=latin1 1889drop table t1; 1890select hex(left(concat(atan(0.5)),1)); 1891hex(left(concat(atan(0.5)),1)) 18920030 1893create table t1 as select concat(atan(0.5)) as c1; 1894show create table t1; 1895Table Create Table 1896t1 CREATE TABLE `t1` ( 1897 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1898) ENGINE=MyISAM DEFAULT CHARSET=latin1 1899drop table t1; 1900select hex(left(concat(cos(0.5)),1)); 1901hex(left(concat(cos(0.5)),1)) 19020030 1903create table t1 as select concat(cos(0.5)) as c1; 1904show create table t1; 1905Table Create Table 1906t1 CREATE TABLE `t1` ( 1907 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1908) ENGINE=MyISAM DEFAULT CHARSET=latin1 1909drop table t1; 1910select hex(left(concat(sin(0.5)),1)); 1911hex(left(concat(sin(0.5)),1)) 19120030 1913create table t1 as select concat(sin(0.5)) as c1; 1914show create table t1; 1915Table Create Table 1916t1 CREATE TABLE `t1` ( 1917 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1918) ENGINE=MyISAM DEFAULT CHARSET=latin1 1919drop table t1; 1920select hex(left(concat(tan(0.5)),1)); 1921hex(left(concat(tan(0.5)),1)) 19220030 1923create table t1 as select concat(tan(0.5)) as c1; 1924show create table t1; 1925Table Create Table 1926t1 CREATE TABLE `t1` ( 1927 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1928) ENGINE=MyISAM DEFAULT CHARSET=latin1 1929drop table t1; 1930select hex(concat(degrees(0))); 1931hex(concat(degrees(0))) 19320030 1933create table t1 as select concat(degrees(0)) as c1; 1934show create table t1; 1935Table Create Table 1936t1 CREATE TABLE `t1` ( 1937 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1938) ENGINE=MyISAM DEFAULT CHARSET=latin1 1939drop table t1; 1940select hex(concat(radians(0))); 1941hex(concat(radians(0))) 19420030 1943create table t1 as select concat(radians(0)) as c1; 1944show create table t1; 1945Table Create Table 1946t1 CREATE TABLE `t1` ( 1947 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1948) ENGINE=MyISAM DEFAULT CHARSET=latin1 1949drop table t1; 1950select hex(concat(ceiling(0.5))); 1951hex(concat(ceiling(0.5))) 19520031 1953create table t1 as select ceiling(0.5) as c0, concat(ceiling(0.5)) as c1; 1954show create table t1; 1955Table Create Table 1956t1 CREATE TABLE `t1` ( 1957 `c0` int(3) NOT NULL, 1958 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 1959) ENGINE=MyISAM DEFAULT CHARSET=latin1 1960drop table t1; 1961select hex(concat(floor(0.5))); 1962hex(concat(floor(0.5))) 19630030 1964create table t1 as select floor(0.5) as c0, concat(floor(0.5)) as c1; 1965show create table t1; 1966Table Create Table 1967t1 CREATE TABLE `t1` ( 1968 `c0` int(3) NOT NULL, 1969 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 1970) ENGINE=MyISAM DEFAULT CHARSET=latin1 1971drop table t1; 1972select hex(concat(round(0.5))); 1973hex(concat(round(0.5))) 19740031 1975create table t1 as select concat(round(0.5)) as c1; 1976show create table t1; 1977Table Create Table 1978t1 CREATE TABLE `t1` ( 1979 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 1980) ENGINE=MyISAM DEFAULT CHARSET=latin1 1981drop table t1; 1982select hex(concat(sign(0.5))); 1983hex(concat(sign(0.5))) 19840031 1985create table t1 as select concat(sign(0.5)) as c1; 1986show create table t1; 1987Table Create Table 1988t1 CREATE TABLE `t1` ( 1989 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 1990) ENGINE=MyISAM DEFAULT CHARSET=latin1 1991drop table t1; 1992create table t1 as select concat(rand()) as c1; 1993show create table t1; 1994Table Create Table 1995t1 CREATE TABLE `t1` ( 1996 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 1997) ENGINE=MyISAM DEFAULT CHARSET=latin1 1998drop table t1; 1999select hex(concat(length('a'))); 2000hex(concat(length('a'))) 20010032 2002create table t1 as select concat(length('a')) as c1; 2003show create table t1; 2004Table Create Table 2005t1 CREATE TABLE `t1` ( 2006 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2007) ENGINE=MyISAM DEFAULT CHARSET=latin1 2008drop table t1; 2009select hex(concat(char_length('a'))); 2010hex(concat(char_length('a'))) 20110031 2012create table t1 as select concat(char_length('a')) as c1; 2013show create table t1; 2014Table Create Table 2015t1 CREATE TABLE `t1` ( 2016 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2017) ENGINE=MyISAM DEFAULT CHARSET=latin1 2018drop table t1; 2019select hex(concat(bit_length('a'))); 2020hex(concat(bit_length('a'))) 202100310036 2022create table t1 as select concat(bit_length('a')) as c1; 2023show create table t1; 2024Table Create Table 2025t1 CREATE TABLE `t1` ( 2026 `c1` varchar(11) CHARACTER SET ucs2 DEFAULT NULL 2027) ENGINE=MyISAM DEFAULT CHARSET=latin1 2028drop table t1; 2029select hex(concat(coercibility('a'))); 2030hex(concat(coercibility('a'))) 20310034 2032create table t1 as select concat(coercibility('a')) as c1; 2033show create table t1; 2034Table Create Table 2035t1 CREATE TABLE `t1` ( 2036 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2037) ENGINE=MyISAM DEFAULT CHARSET=latin1 2038drop table t1; 2039select hex(concat(locate('a','a'))); 2040hex(concat(locate('a','a'))) 20410031 2042create table t1 as select concat(locate('a','a')) as c1; 2043show create table t1; 2044Table Create Table 2045t1 CREATE TABLE `t1` ( 2046 `c1` varchar(11) CHARACTER SET ucs2 DEFAULT NULL 2047) ENGINE=MyISAM DEFAULT CHARSET=latin1 2048drop table t1; 2049select hex(concat(field('c','a','b','c'))); 2050hex(concat(field('c','a','b','c'))) 20510033 2052create table t1 as select concat(field('c','a','b','c')) as c1; 2053show create table t1; 2054Table Create Table 2055t1 CREATE TABLE `t1` ( 2056 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 2057) ENGINE=MyISAM DEFAULT CHARSET=latin1 2058drop table t1; 2059select hex(concat(ascii(61))); 2060hex(concat(ascii(61))) 206100350034 2062create table t1 as select concat(ascii(61)) as c1; 2063show create table t1; 2064Table Create Table 2065t1 CREATE TABLE `t1` ( 2066 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 2067) ENGINE=MyISAM DEFAULT CHARSET=latin1 2068drop table t1; 2069select hex(concat(ord(61))); 2070hex(concat(ord(61))) 207100350034 2072create table t1 as select concat(ord(61)) as c1; 2073show create table t1; 2074Table Create Table 2075t1 CREATE TABLE `t1` ( 2076 `c1` varchar(7) CHARACTER SET ucs2 DEFAULT NULL 2077) ENGINE=MyISAM DEFAULT CHARSET=latin1 2078drop table t1; 2079select hex(concat(find_in_set('b','a,b,c,d'))); 2080hex(concat(find_in_set('b','a,b,c,d'))) 20810032 2082create table t1 as select concat(find_in_set('b','a,b,c,d')) as c1; 2083show create table t1; 2084Table Create Table 2085t1 CREATE TABLE `t1` ( 2086 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 2087) ENGINE=MyISAM DEFAULT CHARSET=latin1 2088drop table t1; 2089select md5('a'), hex(md5('a')); 2090md5('a') hex(md5('a')) 2091760f753576f2955b0074758acb4d5fa6 00370036003000660037003500330035003700360066003200390035003500620030003000370034003700350038006100630062003400640035006600610036 2092create table t1 as select md5('a') as c1; 2093show create table t1; 2094Table Create Table 2095t1 CREATE TABLE `t1` ( 2096 `c1` varchar(32) CHARACTER SET ucs2 DEFAULT NULL 2097) ENGINE=MyISAM DEFAULT CHARSET=latin1 2098drop table t1; 2099select old_password('a'), hex(old_password('a')); 2100old_password('a') hex(old_password('a')) 21010705298948d1f92f 0030003700300035003200390038003900340038006400310066003900320066 2102create table t1 as select old_password('a') as c1; 2103show create table t1; 2104Table Create Table 2105t1 CREATE TABLE `t1` ( 2106 `c1` varchar(16) CHARACTER SET ucs2 DEFAULT NULL 2107) ENGINE=MyISAM DEFAULT CHARSET=latin1 2108drop table t1; 2109select password('a'), hex(password('a')); 2110password('a') hex(password('a')) 2111*9A48A662559C49521B25C43077059DD109FBD84A 002A0039004100340038004100360036003200350035003900430034003900350032003100420032003500430034003300300037003700300035003900440044003100300039004600420044003800340041 2112create table t1 as select password('a') as c1; 2113show create table t1; 2114Table Create Table 2115t1 CREATE TABLE `t1` ( 2116 `c1` varchar(41) CHARACTER SET ucs2 DEFAULT NULL 2117) ENGINE=MyISAM DEFAULT CHARSET=latin1 2118drop table t1; 2119select sha('a'), hex(sha('a')); 2120sha('a') hex(sha('a')) 21213106600e0327ca77371f2526df794ed84322585c 0033003100300036003600300030006500300033003200370063006100370037003300370031006600320035003200360064006600370039003400650064003800340033003200320035003800350063 2122create table t1 as select sha('a') as c1; 2123show create table t1; 2124Table Create Table 2125t1 CREATE TABLE `t1` ( 2126 `c1` varchar(40) CHARACTER SET ucs2 DEFAULT NULL 2127) ENGINE=MyISAM DEFAULT CHARSET=latin1 2128drop table t1; 2129select sha1('a'), hex(sha1('a')); 2130sha1('a') hex(sha1('a')) 21313106600e0327ca77371f2526df794ed84322585c 0033003100300036003600300030006500300033003200370063006100370037003300370031006600320035003200360064006600370039003400650064003800340033003200320035003800350063 2132create table t1 as select sha1('a') as c1; 2133show create table t1; 2134Table Create Table 2135t1 CREATE TABLE `t1` ( 2136 `c1` varchar(40) CHARACTER SET ucs2 DEFAULT NULL 2137) ENGINE=MyISAM DEFAULT CHARSET=latin1 2138drop table t1; 2139select hex(concat(cast('-1' as signed))); 2140hex(concat(cast('-1' as signed))) 2141002D0031 2142create table t1 as select concat(cast('-1' as signed)) as c1; 2143show create table t1; 2144Table Create Table 2145t1 CREATE TABLE `t1` ( 2146 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2147) ENGINE=MyISAM DEFAULT CHARSET=latin1 2148drop table t1; 2149select hex(concat(cast('1' as unsigned))); 2150hex(concat(cast('1' as unsigned))) 21510031 2152create table t1 as select concat(cast('1' as unsigned)) as c1; 2153show create table t1; 2154Table Create Table 2155t1 CREATE TABLE `t1` ( 2156 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2157) ENGINE=MyISAM DEFAULT CHARSET=latin1 2158drop table t1; 2159select hex(concat(cast(1/2 as decimal(5,5)))); 2160hex(concat(cast(1/2 as decimal(5,5)))) 21610030002E00350030003000300030 2162create table t1 as select concat(cast(1/2 as decimal(5,5))) as c1; 2163show create table t1; 2164Table Create Table 2165t1 CREATE TABLE `t1` ( 2166 `c1` varchar(7) CHARACTER SET ucs2 DEFAULT NULL 2167) ENGINE=MyISAM DEFAULT CHARSET=latin1 2168drop table t1; 2169select hex(concat(cast('2001-01-02 03:04:05' as date))); 2170hex(concat(cast('2001-01-02 03:04:05' as date))) 21710032003000300031002D00300031002D00300032 2172create table t1 as select concat(cast('2001-01-02 03:04:05' as date)) as c1; 2173show create table t1; 2174Table Create Table 2175t1 CREATE TABLE `t1` ( 2176 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2177) ENGINE=MyISAM DEFAULT CHARSET=latin1 2178select * from t1; 2179c1 21802001-01-02 2181drop table t1; 2182select hex(concat(cast('2001-01-02 03:04:05' as time))); 2183hex(concat(cast('2001-01-02 03:04:05' as time))) 218400300033003A00300034003A00300035 2185create table t1 as select concat(cast('2001-01-02 03:04:05' as time)) as c1; 2186show create table t1; 2187Table Create Table 2188t1 CREATE TABLE `t1` ( 2189 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2190) ENGINE=MyISAM DEFAULT CHARSET=latin1 2191select * from t1; 2192c1 219303:04:05 2194drop table t1; 2195select hex(concat(cast('2001-01-02' as datetime))); 2196hex(concat(cast('2001-01-02' as datetime))) 21970032003000300031002D00300031002D00300032002000300030003A00300030003A00300030 2198create table t1 as select concat(cast('2001-01-02' as datetime)) as c1; 2199show create table t1; 2200Table Create Table 2201t1 CREATE TABLE `t1` ( 2202 `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 2203) ENGINE=MyISAM DEFAULT CHARSET=latin1 2204select * from t1; 2205c1 22062001-01-02 00:00:00 2207drop table t1; 2208select hex(concat(least(1,2))); 2209hex(concat(least(1,2))) 22100031 2211create table t1 as select concat(least(1,2)) as c1; 2212show create table t1; 2213Table Create Table 2214t1 CREATE TABLE `t1` ( 2215 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2216) ENGINE=MyISAM DEFAULT CHARSET=latin1 2217drop table t1; 2218select hex(concat(greatest(1,2))); 2219hex(concat(greatest(1,2))) 22200032 2221create table t1 as select concat(greatest(1,2)) as c1; 2222show create table t1; 2223Table Create Table 2224t1 CREATE TABLE `t1` ( 2225 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2226) ENGINE=MyISAM DEFAULT CHARSET=latin1 2227drop table t1; 2228select hex(concat(case when 11 then 22 else 33 end)); 2229hex(concat(case when 11 then 22 else 33 end)) 223000320032 2231create table t1 as select concat(case when 11 then 22 else 33 end) as c1; 2232show create table t1; 2233Table Create Table 2234t1 CREATE TABLE `t1` ( 2235 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2236) ENGINE=MyISAM DEFAULT CHARSET=latin1 2237drop table t1; 2238select hex(concat(coalesce(1,2))); 2239hex(concat(coalesce(1,2))) 22400031 2241create table t1 as select concat(coalesce(1,2)) as c1; 2242show create table t1; 2243Table Create Table 2244t1 CREATE TABLE `t1` ( 2245 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2246) ENGINE=MyISAM DEFAULT CHARSET=latin1 2247drop table t1; 2248select hex(concat_ws(1,2,3)); 2249hex(concat_ws(1,2,3)) 2250003200310033 2251create table t1 as select concat_ws(1,2,3) as c1; 2252show create table t1; 2253Table Create Table 2254t1 CREATE TABLE `t1` ( 2255 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 2256) ENGINE=MyISAM DEFAULT CHARSET=latin1 2257drop table t1; 2258select hex(group_concat(1,2,3)); 2259hex(group_concat(1,2,3)) 2260003100320033 2261create table t1 as select group_concat(1,2,3) as c1; 2262show create table t1; 2263Table Create Table 2264t1 CREATE TABLE `t1` ( 2265 `c1` mediumtext CHARACTER SET ucs2 DEFAULT NULL 2266) ENGINE=MyISAM DEFAULT CHARSET=latin1 2267drop table t1; 2268create table t1 as select 1 as c1 union select 'a'; 2269show create table t1; 2270Table Create Table 2271t1 CREATE TABLE `t1` ( 2272 `c1` varchar(1) CHARACTER SET ucs2 NOT NULL DEFAULT '' 2273) ENGINE=MyISAM DEFAULT CHARSET=latin1 2274select hex(c1) from t1 order by c1; 2275hex(c1) 22760031 22770061 2278drop table t1; 2279create table t1 as select concat(last_insert_id()) as c1; 2280show create table t1; 2281Table Create Table 2282t1 CREATE TABLE `t1` ( 2283 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 2284) ENGINE=MyISAM DEFAULT CHARSET=latin1 2285drop table t1; 2286select hex(concat(benchmark(0,0))); 2287hex(concat(benchmark(0,0))) 22880030 2289create table t1 as select concat(benchmark(0,0)) as c1; 2290show create table t1; 2291Table Create Table 2292t1 CREATE TABLE `t1` ( 2293 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2294) ENGINE=MyISAM DEFAULT CHARSET=latin1 2295drop table t1; 2296select hex(concat(sleep(0))); 2297hex(concat(sleep(0))) 22980030 2299create table t1 as select concat(sleep(0)) as c1; 2300show create table t1; 2301Table Create Table 2302t1 CREATE TABLE `t1` ( 2303 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2304) ENGINE=MyISAM DEFAULT CHARSET=latin1 2305drop table t1; 2306select hex(concat(is_free_lock('xxxx'))); 2307hex(concat(is_free_lock('xxxx'))) 23080031 2309create table t1 as select concat(is_free_lock('xxxx')) as c1; 2310show create table t1; 2311Table Create Table 2312t1 CREATE TABLE `t1` ( 2313 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2314) ENGINE=MyISAM DEFAULT CHARSET=latin1 2315drop table t1; 2316create table t1 as select concat(is_used_lock('a')) as c1; 2317show create table t1; 2318Table Create Table 2319t1 CREATE TABLE `t1` ( 2320 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2321) ENGINE=MyISAM DEFAULT CHARSET=latin1 2322drop table t1; 2323create table t1 as select concat(release_lock('a')) as c1; 2324show create table t1; 2325Table Create Table 2326t1 CREATE TABLE `t1` ( 2327 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2328) ENGINE=MyISAM DEFAULT CHARSET=latin1 2329drop table t1; 2330select hex(concat(crc32(''))); 2331hex(concat(crc32(''))) 23320030 2333create table t1 as select concat(crc32('')) as c1; 2334show create table t1; 2335Table Create Table 2336t1 CREATE TABLE `t1` ( 2337 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2338) ENGINE=MyISAM DEFAULT CHARSET=latin1 2339drop table t1; 2340select hex(concat(uncompressed_length(''))); 2341hex(concat(uncompressed_length(''))) 23420030 2343create table t1 as select concat(uncompressed_length('')) as c1; 2344show create table t1; 2345Table Create Table 2346t1 CREATE TABLE `t1` ( 2347 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2348) ENGINE=MyISAM DEFAULT CHARSET=latin1 2349drop table t1; 2350create table t1 as select concat(connection_id()) as c1; 2351show create table t1; 2352Table Create Table 2353t1 CREATE TABLE `t1` ( 2354 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2355) ENGINE=MyISAM DEFAULT CHARSET=latin1 2356drop table t1; 2357select hex(concat(inet_aton('127.1.1.1'))); 2358hex(concat(inet_aton('127.1.1.1'))) 23590032003100330030003700370032003200320035 2360create table t1 as select concat(inet_aton('127.1.1.1')) as c1; 2361show create table t1; 2362Table Create Table 2363t1 CREATE TABLE `t1` ( 2364 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 2365) ENGINE=MyISAM DEFAULT CHARSET=latin1 2366drop table t1; 2367select hex(concat(inet_ntoa(2130772225))); 2368hex(concat(inet_ntoa(2130772225))) 2369003100320037002E0031002E0031002E0031 2370create table t1 as select concat(inet_ntoa(2130772225)) as c1; 2371select * from t1; 2372c1 2373127.1.1.1 2374show create table t1; 2375Table Create Table 2376t1 CREATE TABLE `t1` ( 2377 `c1` varchar(31) CHARACTER SET ucs2 DEFAULT NULL 2378) ENGINE=MyISAM DEFAULT CHARSET=latin1 2379drop table t1; 2380select 1; 23811 23821 2383select hex(concat(row_count())); 2384hex(concat(row_count())) 2385002D0031 2386create table t1 as select concat(row_count()) as c1; 2387show create table t1; 2388Table Create Table 2389t1 CREATE TABLE `t1` ( 2390 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 2391) ENGINE=MyISAM DEFAULT CHARSET=latin1 2392drop table t1; 2393select hex(concat(found_rows())); 2394hex(concat(found_rows())) 23950030 2396create table t1 as select concat(found_rows()) as c1; 2397show create table t1; 2398Table Create Table 2399t1 CREATE TABLE `t1` ( 2400 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 2401) ENGINE=MyISAM DEFAULT CHARSET=latin1 2402drop table t1; 2403create table t1 as select concat(uuid_short()) as c1; 2404show create table t1; 2405Table Create Table 2406t1 CREATE TABLE `t1` ( 2407 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 2408) ENGINE=MyISAM DEFAULT CHARSET=latin1 2409drop table t1; 2410create table t1 as select concat(uuid()) as c1; 2411show create table t1; 2412Table Create Table 2413t1 CREATE TABLE `t1` ( 2414 `c1` varchar(36) CHARACTER SET utf8 DEFAULT NULL 2415) ENGINE=MyISAM DEFAULT CHARSET=latin1 2416drop table t1; 2417select coercibility(uuid()), coercibility(cast('a' as char character set latin1)); 2418coercibility(uuid()) coercibility(cast('a' as char character set latin1)) 24194 2 2420select charset(concat(uuid(), cast('a' as char character set latin1))); 2421charset(concat(uuid(), cast('a' as char character set latin1))) 2422latin1 2423create table t1 as select concat(uuid(), cast('a' as char character set latin1)) as c1; 2424show create table t1; 2425Table Create Table 2426t1 CREATE TABLE `t1` ( 2427 `c1` varchar(37) DEFAULT NULL 2428) ENGINE=MyISAM DEFAULT CHARSET=latin1 2429drop table t1; 2430create table t1 as select concat(master_pos_wait('non-existent',0,2)) as c1; 2431show create table t1; 2432Table Create Table 2433t1 CREATE TABLE `t1` ( 2434 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 2435) ENGINE=MyISAM DEFAULT CHARSET=latin1 2436drop table t1; 2437select hex(concat(@a1:=1)); 2438hex(concat(@a1:=1)) 24390031 2440create table t1 as select concat(@a2:=2) as c1, @a3:=3 as c2; 2441select hex(c1) from t1; 2442hex(c1) 24430032 2444show create table t1; 2445Table Create Table 2446t1 CREATE TABLE `t1` ( 2447 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL, 2448 `c2` int(1) NOT NULL 2449) ENGINE=MyISAM DEFAULT CHARSET=latin1 2450drop table t1; 2451set @a2=1; 2452select hex(concat(@a2)); 2453hex(concat(@a2)) 24540031 2455create table t1 as select concat(@a2) as c1, @a2 as c2; 2456select hex(c1) from t1; 2457hex(c1) 24580031 2459show create table t1; 2460Table Create Table 2461t1 CREATE TABLE `t1` ( 2462 `c1` varchar(20) CHARACTER SET ucs2 DEFAULT NULL, 2463 `c2` bigint(20) DEFAULT NULL 2464) ENGINE=MyISAM DEFAULT CHARSET=latin1 2465drop table t1; 2466select hex(concat(@a1:=sqrt(1))); 2467hex(concat(@a1:=sqrt(1))) 24680031 2469create table t1 as select concat(@a2:=sqrt(1)) as c1, @a3:=sqrt(1) as c2; 2470select hex(c1) from t1; 2471hex(c1) 24720031 2473show create table t1; 2474Table Create Table 2475t1 CREATE TABLE `t1` ( 2476 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL, 2477 `c2` double DEFAULT NULL 2478) ENGINE=MyISAM DEFAULT CHARSET=latin1 2479drop table t1; 2480set @a2=sqrt(1); 2481select hex(concat(@a2)); 2482hex(concat(@a2)) 24830031 2484create table t1 as select concat(@a2) as c1, @a2 as c2; 2485select hex(c1) from t1; 2486hex(c1) 24870031 2488show create table t1; 2489Table Create Table 2490t1 CREATE TABLE `t1` ( 2491 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL, 2492 `c2` double DEFAULT NULL 2493) ENGINE=MyISAM DEFAULT CHARSET=latin1 2494drop table t1; 2495select hex(concat(@a1:=1.1)); 2496hex(concat(@a1:=1.1)) 24970031002E0031 2498create table t1 as select concat(@a2:=1.1) as c1, @a3:=1.1 as c2; 2499select hex(c1) from t1; 2500hex(c1) 25010031002E0031 2502show create table t1; 2503Table Create Table 2504t1 CREATE TABLE `t1` ( 2505 `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL, 2506 `c2` decimal(2,1) NOT NULL 2507) ENGINE=MyISAM DEFAULT CHARSET=latin1 2508drop table t1; 2509set @a2=1.1; 2510select hex(concat(@a2)); 2511hex(concat(@a2)) 25120031002E0031 2513create table t1 as select concat(@a2) as c1, @a2 as c2; 2514select hex(c1) from t1; 2515hex(c1) 25160031002E0031 2517show create table t1; 2518Table Create Table 2519t1 CREATE TABLE `t1` ( 2520 `c1` varchar(83) CHARACTER SET ucs2 DEFAULT NULL, 2521 `c2` decimal(65,38) DEFAULT NULL 2522) ENGINE=MyISAM DEFAULT CHARSET=latin1 2523drop table t1; 2524select hex(concat(@@ft_max_word_len)); 2525hex(concat(@@ft_max_word_len)) 252600380034 2527create table t1 as select concat(@@ft_max_word_len) as c1; 2528select hex(c1) from t1; 2529hex(c1) 253000380034 2531show create table t1; 2532Table Create Table 2533t1 CREATE TABLE `t1` ( 2534 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 2535) ENGINE=MyISAM DEFAULT CHARSET=latin1 2536drop table t1; 2537select hex(concat('a'='a' IS TRUE)); 2538hex(concat('a'='a' IS TRUE)) 25390031 2540create table t1 as select concat('a'='a' IS TRUE) as c1; 2541show create table t1; 2542Table Create Table 2543t1 CREATE TABLE `t1` ( 2544 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2545) ENGINE=MyISAM DEFAULT CHARSET=latin1 2546drop table t1; 2547select hex(concat('a'='a' IS NOT TRUE)); 2548hex(concat('a'='a' IS NOT TRUE)) 25490030 2550create table t1 as select concat('a'='a' IS NOT TRUE) as c1; 2551show create table t1; 2552Table Create Table 2553t1 CREATE TABLE `t1` ( 2554 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2555) ENGINE=MyISAM DEFAULT CHARSET=latin1 2556drop table t1; 2557select hex(concat(NOT 'a'='a')); 2558hex(concat(NOT 'a'='a')) 25590030 2560create table t1 as select concat(NOT 'a'='a') as c1; 2561show create table t1; 2562Table Create Table 2563t1 CREATE TABLE `t1` ( 2564 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2565) ENGINE=MyISAM DEFAULT CHARSET=latin1 2566drop table t1; 2567select hex(concat('a' IS NULL)); 2568hex(concat('a' IS NULL)) 25690030 2570create table t1 as select concat('a' IS NULL) as c1; 2571show create table t1; 2572Table Create Table 2573t1 CREATE TABLE `t1` ( 2574 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2575) ENGINE=MyISAM DEFAULT CHARSET=latin1 2576drop table t1; 2577select hex(concat('a' IS NOT NULL)); 2578hex(concat('a' IS NOT NULL)) 25790031 2580create table t1 as select concat('a' IS NOT NULL) as c1; 2581show create table t1; 2582Table Create Table 2583t1 CREATE TABLE `t1` ( 2584 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2585) ENGINE=MyISAM DEFAULT CHARSET=latin1 2586drop table t1; 2587select hex(concat('a' rlike 'a')); 2588hex(concat('a' rlike 'a')) 25890031 2590create table t1 as select concat('a' IS NOT NULL) as c1; 2591show create table t1; 2592Table Create Table 2593t1 CREATE TABLE `t1` ( 2594 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2595) ENGINE=MyISAM DEFAULT CHARSET=latin1 2596drop table t1; 2597select hex(concat(strcmp('a','b'))); 2598hex(concat(strcmp('a','b'))) 2599002D0031 2600create table t1 as select concat(strcmp('a','b')) as c1; 2601show create table t1; 2602Table Create Table 2603t1 CREATE TABLE `t1` ( 2604 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2605) ENGINE=MyISAM DEFAULT CHARSET=latin1 2606drop table t1; 2607select hex(concat('a' like 'a')); 2608hex(concat('a' like 'a')) 26090031 2610create table t1 as select concat('a' like 'b') as c1; 2611show create table t1; 2612Table Create Table 2613t1 CREATE TABLE `t1` ( 2614 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2615) ENGINE=MyISAM DEFAULT CHARSET=latin1 2616drop table t1; 2617select hex(concat('a' between 'b' and 'c')); 2618hex(concat('a' between 'b' and 'c')) 26190030 2620create table t1 as select concat('a' between 'b' and 'c') as c1; 2621show create table t1; 2622Table Create Table 2623t1 CREATE TABLE `t1` ( 2624 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2625) ENGINE=MyISAM DEFAULT CHARSET=latin1 2626drop table t1; 2627select hex(concat('a' in ('a','b'))); 2628hex(concat('a' in ('a','b'))) 26290031 2630create table t1 as select concat('a' in ('a','b')) as c1; 2631show create table t1; 2632Table Create Table 2633t1 CREATE TABLE `t1` ( 2634 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2635) ENGINE=MyISAM DEFAULT CHARSET=latin1 2636drop table t1; 2637select hex(concat(interval(23, 1, 15, 17, 30, 44, 200))); 2638hex(concat(interval(23, 1, 15, 17, 30, 44, 200))) 26390033 2640create table t1 as select concat(interval(23, 1, 15, 17, 30, 44, 200)) as c1; 2641show create table t1; 2642Table Create Table 2643t1 CREATE TABLE `t1` ( 2644 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2645) ENGINE=MyISAM DEFAULT CHARSET=latin1 2646drop table t1; 2647create table t1 (a varchar(10), fulltext key(a)); 2648insert into t1 values ('a'); 2649select hex(concat(match (a) against ('a'))) from t1; 2650hex(concat(match (a) against ('a'))) 26510030 2652create table t2 as select concat(match (a) against ('a')) as a from t1; 2653show create table t2; 2654Table Create Table 2655t2 CREATE TABLE `t2` ( 2656 `a` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 2657) ENGINE=MyISAM DEFAULT CHARSET=latin1 2658drop table t1, t2; 2659select hex(ifnull(1,'a')); 2660hex(ifnull(1,'a')) 26610031 2662create table t1 as select ifnull(1,'a') as c1; 2663show create table t1; 2664Table Create Table 2665t1 CREATE TABLE `t1` ( 2666 `c1` varchar(1) CHARACTER SET ucs2 NOT NULL 2667) ENGINE=MyISAM DEFAULT CHARSET=latin1 2668drop table t1; 2669select hex(concat(ifnull(1,1))); 2670hex(concat(ifnull(1,1))) 26710031 2672create table t1 as select concat(ifnull(1,1)) as c1; 2673show create table t1; 2674Table Create Table 2675t1 CREATE TABLE `t1` ( 2676 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2677) ENGINE=MyISAM DEFAULT CHARSET=latin1 2678drop table t1; 2679select hex(concat(ifnull(1.1,1.1))); 2680hex(concat(ifnull(1.1,1.1))) 26810031002E0031 2682create table t1 as select concat(ifnull(1.1,1.1)) as c1; 2683show create table t1; 2684Table Create Table 2685t1 CREATE TABLE `t1` ( 2686 `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 2687) ENGINE=MyISAM DEFAULT CHARSET=latin1 2688drop table t1; 2689select hex(if(1,'b',1)); 2690hex(if(1,'b',1)) 26910062 2692create table t1 as select if(1,'b',1) as c1; 2693show create table t1; 2694Table Create Table 2695t1 CREATE TABLE `t1` ( 2696 `c1` varchar(1) CHARACTER SET ucs2 NOT NULL 2697) ENGINE=MyISAM DEFAULT CHARSET=latin1 2698drop table t1; 2699select hex(if(1,1,'b')); 2700hex(if(1,1,'b')) 27010031 2702create table t1 as select if(1,1,'b') as c1; 2703show create table t1; 2704Table Create Table 2705t1 CREATE TABLE `t1` ( 2706 `c1` varchar(1) CHARACTER SET ucs2 NOT NULL 2707) ENGINE=MyISAM DEFAULT CHARSET=latin1 2708drop table t1; 2709select hex(concat(if(1,1,1))); 2710hex(concat(if(1,1,1))) 27110031 2712create table t1 as select concat(if(1,1,1)) as c1; 2713show create table t1; 2714Table Create Table 2715t1 CREATE TABLE `t1` ( 2716 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2717) ENGINE=MyISAM DEFAULT CHARSET=latin1 2718drop table t1; 2719select hex(concat(nullif(1,2))); 2720hex(concat(nullif(1,2))) 27210031 2722create table t1 as select concat(nullif(1,2)) as c1; 2723show create table t1; 2724Table Create Table 2725t1 CREATE TABLE `t1` ( 2726 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2727) ENGINE=MyISAM DEFAULT CHARSET=latin1 2728drop table t1; 2729select hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)')))); 2730hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)')))) 27310031 2732create table t1 as select concat(Dimension(GeomFromText('LINSTRING(0 0,10 10)'))) as c1; 2733show create table t1; 2734Table Create Table 2735t1 CREATE TABLE `t1` ( 2736 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2737) ENGINE=MyISAM DEFAULT CHARSET=latin1 2738drop table t1; 2739select hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)')))); 2740hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)')))) 27410032 2742create table t1 as select concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1; 2743show create table t1; 2744Table Create Table 2745t1 CREATE TABLE `t1` ( 2746 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2747) ENGINE=MyISAM DEFAULT CHARSET=latin1 2748drop table t1; 2749select hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)')))); 2750hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)')))) 27510032 2752create table t1 as select concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))) as c1; 2753show create table t1; 2754Table Create Table 2755t1 CREATE TABLE `t1` ( 2756 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2757) ENGINE=MyISAM DEFAULT CHARSET=latin1 2758drop table t1; 2759select hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)')))); 2760hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)')))) 27610030 2762create table t1 as select concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1; 2763show create table t1; 2764Table Create Table 2765t1 CREATE TABLE `t1` ( 2766 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2767) ENGINE=MyISAM DEFAULT CHARSET=latin1 2768drop table t1; 2769select 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))')))); 2770hex(concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')))) 27710031 2772create 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; 2773show create table t1; 2774Table Create Table 2775t1 CREATE TABLE `t1` ( 2776 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 2777) ENGINE=MyISAM DEFAULT CHARSET=latin1 2778drop table t1; 2779select hex(concat(IsEmpty(GeomFromText('POINT(1 1)')))); 2780hex(concat(IsEmpty(GeomFromText('POINT(1 1)')))) 27810030 2782create table t1 as select concat(IsEmpty(GeomFromText('Point(1 1)'))) as c1; 2783show create table t1; 2784Table Create Table 2785t1 CREATE TABLE `t1` ( 2786 `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL 2787) ENGINE=MyISAM DEFAULT CHARSET=latin1 2788drop table t1; 2789select hex(concat(IsSimple(GeomFromText('POINT(1 1)')))); 2790hex(concat(IsSimple(GeomFromText('POINT(1 1)')))) 27910031 2792create table t1 as select concat(IsSimple(GeomFromText('Point(1 1)'))) as c1; 2793show create table t1; 2794Table Create Table 2795t1 CREATE TABLE `t1` ( 2796 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2797) ENGINE=MyISAM DEFAULT CHARSET=latin1 2798drop table t1; 2799select hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)')))); 2800hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)')))) 28010030 2802create table t1 as select concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))) as c1; 2803show create table t1; 2804Table Create Table 2805t1 CREATE TABLE `t1` ( 2806 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2807) ENGINE=MyISAM DEFAULT CHARSET=latin1 2808drop table t1; 2809select hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)')))); 2810hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)')))) 28110031 2812create table t1 as select concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))) as c1; 2813drop table t1; 2814select hex(concat(x(GeomFromText('Point(1 2)')))); 2815hex(concat(x(GeomFromText('Point(1 2)')))) 28160031 2817create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1; 2818show create table t1; 2819Table Create Table 2820t1 CREATE TABLE `t1` ( 2821 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 2822) ENGINE=MyISAM DEFAULT CHARSET=latin1 2823drop table t1; 2824select hex(concat(y(GeomFromText('Point(1 2)')))); 2825hex(concat(y(GeomFromText('Point(1 2)')))) 28260032 2827create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1; 2828show create table t1; 2829Table Create Table 2830t1 CREATE TABLE `t1` ( 2831 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 2832) ENGINE=MyISAM DEFAULT CHARSET=latin1 2833drop table t1; 2834select hex(concat(GLength(GeomFromText('LineString(1 2,2 2)')))); 2835hex(concat(GLength(GeomFromText('LineString(1 2,2 2)')))) 28360031 2837create table t1 as select concat(GLength(GeomFromText('LineString(1 2, 2 2)'))) as c1; 2838show create table t1; 2839Table Create Table 2840t1 CREATE TABLE `t1` ( 2841 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 2842) ENGINE=MyISAM DEFAULT CHARSET=latin1 2843drop table t1; 2844select hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))')))); 2845hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))')))) 28460031 2847create table t1 as select concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))) as c1; 2848show create table t1; 2849Table Create Table 2850t1 CREATE TABLE `t1` ( 2851 `c1` varchar(23) CHARACTER SET ucs2 DEFAULT NULL 2852) ENGINE=MyISAM DEFAULT CHARSET=latin1 2853drop table t1; 2854select hex(concat(GeometryType(GeomFromText('Point(1 2)')))); 2855hex(concat(GeometryType(GeomFromText('Point(1 2)')))) 28560050004F0049004E0054 2857create table t1 as select concat(GeometryType(GeomFromText('Point(1 2)'))) as c1; 2858show create table t1; 2859Table Create Table 2860t1 CREATE TABLE `t1` ( 2861 `c1` varchar(20) CHARACTER SET ucs2 DEFAULT NULL 2862) ENGINE=MyISAM DEFAULT CHARSET=latin1 2863drop table t1; 2864select hex(concat(AsText(GeomFromText('Point(1 2)')))); 2865hex(concat(AsText(GeomFromText('Point(1 2)')))) 28660050004F0049004E005400280031002000320029 2867create table t1 as select concat(AsText(GeomFromText('Point(1 2)'))) as c1; 2868show create table t1; 2869Table Create Table 2870t1 CREATE TABLE `t1` ( 2871 `c1` longtext CHARACTER SET ucs2 DEFAULT NULL 2872) ENGINE=MyISAM DEFAULT CHARSET=latin1 2873drop table t1; 2874select hex(concat(period_add(200902, 2))); 2875hex(concat(period_add(200902, 2))) 2876003200300030003900300034 2877create table t1 as select concat(period_add(200902, 2)) as c1; 2878show create table t1; 2879Table Create Table 2880t1 CREATE TABLE `t1` ( 2881 `c1` varchar(6) CHARACTER SET ucs2 DEFAULT NULL 2882) ENGINE=MyISAM DEFAULT CHARSET=latin1 2883drop table t1; 2884select hex(concat(period_diff(200902, 200802))); 2885hex(concat(period_diff(200902, 200802))) 288600310032 2887SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 2888create table t1 as select concat(period_add(200902, 200802)) as c1; 2889Warnings: 2890Warning 1265 Data truncated for column 'c1' at row 1 2891show create table t1; 2892Table Create Table 2893t1 CREATE TABLE `t1` ( 2894 `c1` varchar(6) CHARACTER SET ucs2 NOT NULL 2895) ENGINE=MyISAM DEFAULT CHARSET=latin1 2896drop table t1; 2897select hex(concat(to_days(20090224))); 2898hex(concat(to_days(20090224))) 2899003700330033003800320037 2900create table t1 as select concat(to_days(20090224)) as c1; 2901show create table t1; 2902Table Create Table 2903t1 CREATE TABLE `t1` ( 2904 `c1` varchar(6) CHARACTER SET ucs2 DEFAULT NULL 2905) ENGINE=MyISAM DEFAULT CHARSET=latin1 2906drop table t1; 2907select hex(concat(dayofmonth(20090224))); 2908hex(concat(dayofmonth(20090224))) 290900320034 2910create table t1 as select concat(dayofmonth(20090224)) as c1; 2911show create table t1; 2912Table Create Table 2913t1 CREATE TABLE `t1` ( 2914 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2915) ENGINE=MyISAM DEFAULT CHARSET=latin1 2916drop table t1; 2917select hex(concat(dayofyear(20090224))); 2918hex(concat(dayofyear(20090224))) 291900350035 2920create table t1 as select concat(dayofyear(20090224)) as c1; 2921show create table t1; 2922Table Create Table 2923t1 CREATE TABLE `t1` ( 2924 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 2925) ENGINE=MyISAM DEFAULT CHARSET=latin1 2926drop table t1; 2927select hex(concat(hour('10:11:12'))); 2928hex(concat(hour('10:11:12'))) 292900310030 2930create table t1 as select concat(hour('10:11:12')) as c1; 2931show create table t1; 2932Table Create Table 2933t1 CREATE TABLE `t1` ( 2934 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2935) ENGINE=MyISAM DEFAULT CHARSET=latin1 2936drop table t1; 2937select hex(concat(minute('10:11:12'))); 2938hex(concat(minute('10:11:12'))) 293900310031 2940create table t1 as select concat(minute('10:11:12')) as c1; 2941show create table t1; 2942Table Create Table 2943t1 CREATE TABLE `t1` ( 2944 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2945) ENGINE=MyISAM DEFAULT CHARSET=latin1 2946drop table t1; 2947select hex(concat(second('10:11:12'))); 2948hex(concat(second('10:11:12'))) 294900310032 2950create table t1 as select concat(second('10:11:12')) as c1; 2951show create table t1; 2952Table Create Table 2953t1 CREATE TABLE `t1` ( 2954 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2955) ENGINE=MyISAM DEFAULT CHARSET=latin1 2956drop table t1; 2957select hex(concat(quarter(20090224))); 2958hex(concat(quarter(20090224))) 29590031 2960create table t1 as select concat(quarter(20090224)) as c1; 2961show create table t1; 2962Table Create Table 2963t1 CREATE TABLE `t1` ( 2964 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 2965) ENGINE=MyISAM DEFAULT CHARSET=latin1 2966drop table t1; 2967select hex(concat(week(20090224))); 2968hex(concat(week(20090224))) 29690038 2970create table t1 as select concat(week(20090224)) as c1; 2971show create table t1; 2972Table Create Table 2973t1 CREATE TABLE `t1` ( 2974 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 2975) ENGINE=MyISAM DEFAULT CHARSET=latin1 2976drop table t1; 2977select hex(concat(yearweek(20090224))); 2978hex(concat(yearweek(20090224))) 2979003200300030003900300038 2980create table t1 as select concat(yearweek(20090224)) as c1; 2981show create table t1; 2982Table Create Table 2983t1 CREATE TABLE `t1` ( 2984 `c1` varchar(6) CHARACTER SET ucs2 DEFAULT NULL 2985) ENGINE=MyISAM DEFAULT CHARSET=latin1 2986drop table t1; 2987select hex(concat(year(20090224))); 2988hex(concat(year(20090224))) 29890032003000300039 2990create table t1 as select concat(year(20090224)) as c1; 2991show create table t1; 2992Table Create Table 2993t1 CREATE TABLE `t1` ( 2994 `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 2995) ENGINE=MyISAM DEFAULT CHARSET=latin1 2996drop table t1; 2997select hex(concat(weekday(20090224))); 2998hex(concat(weekday(20090224))) 29990031 3000create table t1 as select concat(weekday(20090224)) as c1; 3001show create table t1; 3002Table Create Table 3003t1 CREATE TABLE `t1` ( 3004 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3005) ENGINE=MyISAM DEFAULT CHARSET=latin1 3006drop table t1; 3007select hex(concat(dayofweek(20090224))); 3008hex(concat(dayofweek(20090224))) 30090033 3010create table t1 as select concat(dayofweek(20090224)) as c1; 3011show create table t1; 3012Table Create Table 3013t1 CREATE TABLE `t1` ( 3014 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3015) ENGINE=MyISAM DEFAULT CHARSET=latin1 3016drop table t1; 3017select hex(concat(unix_timestamp(20090224))); 3018hex(concat(unix_timestamp(20090224))) 30190031003200330035003400320032003800300030 3020create table t1 as select concat(unix_timestamp(20090224)) as c1; 3021show create table t1; 3022Table Create Table 3023t1 CREATE TABLE `t1` ( 3024 `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL 3025) ENGINE=MyISAM DEFAULT CHARSET=latin1 3026drop table t1; 3027select hex(concat(time_to_sec('10:11:12'))); 3028hex(concat(time_to_sec('10:11:12'))) 302900330036003600370032 3030create table t1 as select concat(time_to_sec('10:11:12')) as c1; 3031show create table t1; 3032Table Create Table 3033t1 CREATE TABLE `t1` ( 3034 `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL 3035) ENGINE=MyISAM DEFAULT CHARSET=latin1 3036drop table t1; 3037select hex(concat(extract(year from 20090702))); 3038hex(concat(extract(year from 20090702))) 30390032003000300039 3040create table t1 as select concat(extract(year from 20090702)) as c1; 3041show create table t1; 3042Table Create Table 3043t1 CREATE TABLE `t1` ( 3044 `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 3045) ENGINE=MyISAM DEFAULT CHARSET=latin1 3046drop table t1; 3047select hex(concat(microsecond('12:00:00.123456'))); 3048hex(concat(microsecond('12:00:00.123456'))) 3049003100320033003400350036 3050create table t1 as select concat(microsecond('12:00:00.123456')) as c1; 3051show create table t1; 3052Table Create Table 3053t1 CREATE TABLE `t1` ( 3054 `c1` varchar(6) CHARACTER SET ucs2 DEFAULT NULL 3055) ENGINE=MyISAM DEFAULT CHARSET=latin1 3056drop table t1; 3057select hex(concat(month(20090224))); 3058hex(concat(month(20090224))) 30590032 3060create table t1 as select concat(month(20090224)) as c1; 3061show create table t1; 3062Table Create Table 3063t1 CREATE TABLE `t1` ( 3064 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 3065) ENGINE=MyISAM DEFAULT CHARSET=latin1 3066drop table t1; 3067create table t1 as select concat(last_day('2003-02-05')) as c1; 3068show create table t1; 3069Table Create Table 3070t1 CREATE TABLE `t1` ( 3071 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3072) ENGINE=MyISAM DEFAULT CHARSET=latin1 3073select c1, hex(c1) from t1; 3074c1 hex(c1) 30752003-02-28 0032003000300033002D00300032002D00320038 3076drop table t1; 3077create table t1 as select concat(from_days(730669)) as c1; 3078show create table t1; 3079Table Create Table 3080t1 CREATE TABLE `t1` ( 3081 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3082) ENGINE=MyISAM DEFAULT CHARSET=latin1 3083select c1, hex(c1) from t1; 3084c1 hex(c1) 30852000-07-03 0032003000300030002D00300037002D00300033 3086drop table t1; 3087create table t1 as select concat(curdate()) as c1; 3088show create table t1; 3089Table Create Table 3090t1 CREATE TABLE `t1` ( 3091 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3092) ENGINE=MyISAM DEFAULT CHARSET=latin1 3093drop table t1; 3094create table t1 as select concat(utc_date()) as c1; 3095show create table t1; 3096Table Create Table 3097t1 CREATE TABLE `t1` ( 3098 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3099) ENGINE=MyISAM DEFAULT CHARSET=latin1 3100drop table t1; 3101create table t1 as select concat(curtime()) as c1; 3102show create table t1; 3103Table Create Table 3104t1 CREATE TABLE `t1` ( 3105 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3106) ENGINE=MyISAM DEFAULT CHARSET=latin1 3107drop table t1; 3108create table t1 as select repeat('a',20) as c1 limit 0; 3109set timestamp=1216359724; 3110insert into t1 values (current_date); 3111insert into t1 values (current_time); 3112select c1, hex(c1) from t1; 3113c1 hex(c1) 31142008-07-18 0032003000300038002D00300037002D00310038 311508:42:04 00300038003A00340032003A00300034 3116drop table t1; 3117create table t1 as select concat(utc_time()) as c1; 3118show create table t1; 3119Table Create Table 3120t1 CREATE TABLE `t1` ( 3121 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3122) ENGINE=MyISAM DEFAULT CHARSET=latin1 3123drop table t1; 3124select hex(concat(sec_to_time(2378))); 3125hex(concat(sec_to_time(2378))) 312600300030003A00330039003A00330038 3127create table t1 as select concat(sec_to_time(2378)) as c1; 3128show create table t1; 3129Table Create Table 3130t1 CREATE TABLE `t1` ( 3131 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3132) ENGINE=MyISAM DEFAULT CHARSET=latin1 3133drop table t1; 3134select hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00'))); 3135hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00'))) 313600320034003A00300030003A00300030 3137create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')) as c1; 3138show create table t1; 3139Table Create Table 3140t1 CREATE TABLE `t1` ( 3141 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3142) ENGINE=MyISAM DEFAULT CHARSET=latin1 3143drop table t1; 3144select hex(concat(maketime(10,11,12))); 3145hex(concat(maketime(10,11,12))) 314600310030003A00310031003A00310032 3147create table t1 as select concat(maketime(10,11,12)) as c1; 3148show create table t1; 3149Table Create Table 3150t1 CREATE TABLE `t1` ( 3151 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3152) ENGINE=MyISAM DEFAULT CHARSET=latin1 3153drop table t1; 3154select hex(get_format(DATE,'USA')); 3155hex(get_format(DATE,'USA')) 31560025006D002E00250064002E00250059 3157create table t1 as select get_format(DATE,'USA') as c1; 3158show create table t1; 3159Table Create Table 3160t1 CREATE TABLE `t1` ( 3161 `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL 3162) ENGINE=MyISAM DEFAULT CHARSET=latin1 3163drop table t1; 3164select hex(left(concat(from_unixtime(1111885200)),4)); 3165hex(left(concat(from_unixtime(1111885200)),4)) 31660032003000300035 3167create table t1 as select concat(from_unixtime(1111885200)) as c1; 3168show create table t1; 3169Table Create Table 3170t1 CREATE TABLE `t1` ( 3171 `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 3172) ENGINE=MyISAM DEFAULT CHARSET=latin1 3173drop table t1; 3174select hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00'))); 3175hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00'))) 31760032003000300033002D00310032002D00330031002000320030003A00300030003A00300030 3177create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')) as c1; 3178show create table t1; 3179Table Create Table 3180t1 CREATE TABLE `t1` ( 3181 `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 3182) ENGINE=MyISAM DEFAULT CHARSET=latin1 3183drop table t1; 3184select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); 3185hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))) 31860032003000300034002D00300031002D00300032002000310032003A00300030003A00300030 3187create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day)) as c1; 3188show create table t1; 3189Table Create Table 3190t1 CREATE TABLE `t1` ( 3191 `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 3192) ENGINE=MyISAM DEFAULT CHARSET=latin1 3193select * from t1; 3194c1 31952004-01-02 12:00:00 3196drop table t1; 3197select hex(concat(makedate(2009,1))); 3198hex(concat(makedate(2009,1))) 31990032003000300039002D00300031002D00300031 3200create table t1 as select concat(makedate(2009,1)) as c1; 3201show create table t1; 3202Table Create Table 3203t1 CREATE TABLE `t1` ( 3204 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3205) ENGINE=MyISAM DEFAULT CHARSET=latin1 3206select * from t1; 3207c1 32082009-01-01 3209drop table t1; 3210create table t1 as select concat(now()) as c1; 3211show create table t1; 3212Table Create Table 3213t1 CREATE TABLE `t1` ( 3214 `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 3215) ENGINE=MyISAM DEFAULT CHARSET=latin1 3216drop table t1; 3217create table t1 as select concat(utc_timestamp()) as c1; 3218show create table t1; 3219Table Create Table 3220t1 CREATE TABLE `t1` ( 3221 `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 3222) ENGINE=MyISAM DEFAULT CHARSET=latin1 3223drop table t1; 3224create table t1 as select concat(sysdate()) as c1; 3225show create table t1; 3226Table Create Table 3227t1 CREATE TABLE `t1` ( 3228 `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 3229) ENGINE=MyISAM DEFAULT CHARSET=latin1 3230drop table t1; 3231select hex(concat(addtime('00:00:00','11:22:33'))); 3232hex(concat(addtime('00:00:00','11:22:33'))) 323300310031003A00320032003A00330033 3234create table t1 as select concat(addtime('00:00:00','11:22:33')) as c1; 3235show create table t1; 3236Table Create Table 3237t1 CREATE TABLE `t1` ( 3238 `c1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL 3239) ENGINE=MyISAM DEFAULT CHARSET=latin1 3240drop table t1; 3241select hex(concat(subtime('23:59:59','11:22:33'))); 3242hex(concat(subtime('23:59:59','11:22:33'))) 324300310032003A00330037003A00320036 3244create table t1 as select concat(subtime('23:59:59','11:22:33')) as c1; 3245show create table t1; 3246Table Create Table 3247t1 CREATE TABLE `t1` ( 3248 `c1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL 3249) ENGINE=MyISAM DEFAULT CHARSET=latin1 3250drop table t1; 3251select hex(elt(1,2,3)); 3252hex(elt(1,2,3)) 32530032 3254create table t1 as select elt(1,2,3) as c1; 3255show create table t1; 3256Table Create Table 3257t1 CREATE TABLE `t1` ( 3258 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3259) ENGINE=MyISAM DEFAULT CHARSET=latin1 3260drop table t1; 3261select hex(export_set(1,2,3,4,2)); 3262hex(export_set(1,2,3,4,2)) 3263003200340033 3264create table t1 as select export_set(1,2,3,4,2) as c1; 3265show create table t1; 3266Table Create Table 3267t1 CREATE TABLE `t1` ( 3268 `c1` varchar(127) CHARACTER SET ucs2 DEFAULT NULL 3269) ENGINE=MyISAM DEFAULT CHARSET=latin1 3270drop table t1; 3271select hex(insert(1133,3,0,22)); 3272hex(insert(1133,3,0,22)) 3273003100310032003200330033 3274create table t1 as select insert(1133,3,0,22) as c1; 3275show create table t1; 3276Table Create Table 3277t1 CREATE TABLE `t1` ( 3278 `c1` varchar(6) CHARACTER SET ucs2 DEFAULT NULL 3279) ENGINE=MyISAM DEFAULT CHARSET=latin1 3280drop table t1; 3281select hex(lcase(123)); 3282hex(lcase(123)) 3283003100320033 3284create table t1 as select lcase(123) as c1; 3285show create table t1; 3286Table Create Table 3287t1 CREATE TABLE `t1` ( 3288 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 3289) ENGINE=MyISAM DEFAULT CHARSET=latin1 3290drop table t1; 3291select hex(left(123,1)); 3292hex(left(123,1)) 32930031 3294create table t1 as select left(123,1) as c1; 3295show create table t1; 3296Table Create Table 3297t1 CREATE TABLE `t1` ( 3298 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3299) ENGINE=MyISAM DEFAULT CHARSET=latin1 3300drop table t1; 3301select hex(lower(123)); 3302hex(lower(123)) 3303003100320033 3304create table t1 as select lower(123) as c1; 3305show create table t1; 3306Table Create Table 3307t1 CREATE TABLE `t1` ( 3308 `c1` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 3309) ENGINE=MyISAM DEFAULT CHARSET=latin1 3310drop table t1; 3311select hex(lpad(1,2,0)); 3312hex(lpad(1,2,0)) 331300300031 3314create table t1 as select lpad(1,2,0) as c1; 3315show create table t1; 3316Table Create Table 3317t1 CREATE TABLE `t1` ( 3318 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 3319) ENGINE=MyISAM DEFAULT CHARSET=latin1 3320drop table t1; 3321select hex(ltrim(1)); 3322hex(ltrim(1)) 33230031 3324create table t1 as select ltrim(1) as c1; 3325show create table t1; 3326Table Create Table 3327t1 CREATE TABLE `t1` ( 3328 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3329) ENGINE=MyISAM DEFAULT CHARSET=latin1 3330drop table t1; 3331select hex(mid(1,1,1)); 3332hex(mid(1,1,1)) 33330031 3334create table t1 as select mid(1,1,1) as c1; 3335show create table t1; 3336Table Create Table 3337t1 CREATE TABLE `t1` ( 3338 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3339) ENGINE=MyISAM DEFAULT CHARSET=latin1 3340drop table t1; 3341select hex(repeat(1,2)); 3342hex(repeat(1,2)) 334300310031 3344create table t1 as select repeat(1,2) as c1; 3345show create table t1; 3346Table Create Table 3347t1 CREATE TABLE `t1` ( 3348 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 3349) ENGINE=MyISAM DEFAULT CHARSET=latin1 3350drop table t1; 3351select hex(replace(1,1,2)); 3352hex(replace(1,1,2)) 33530032 3354create table t1 as select replace(1,1,2) as c1; 3355show create table t1; 3356Table Create Table 3357t1 CREATE TABLE `t1` ( 3358 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3359) ENGINE=MyISAM DEFAULT CHARSET=latin1 3360drop table t1; 3361select hex(reverse(12)); 3362hex(reverse(12)) 336300320031 3364create table t1 as select reverse(12) as c1; 3365show create table t1; 3366Table Create Table 3367t1 CREATE TABLE `t1` ( 3368 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 3369) ENGINE=MyISAM DEFAULT CHARSET=latin1 3370drop table t1; 3371select hex(right(123,1)); 3372hex(right(123,1)) 33730033 3374create table t1 as select right(123,1) as c1; 3375show create table t1; 3376Table Create Table 3377t1 CREATE TABLE `t1` ( 3378 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3379) ENGINE=MyISAM DEFAULT CHARSET=latin1 3380drop table t1; 3381select hex(rpad(1,2,0)); 3382hex(rpad(1,2,0)) 338300310030 3384create table t1 as select rpad(1,2,0) as c1; 3385show create table t1; 3386Table Create Table 3387t1 CREATE TABLE `t1` ( 3388 `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 3389) ENGINE=MyISAM DEFAULT CHARSET=latin1 3390drop table t1; 3391select hex(rtrim(1)); 3392hex(rtrim(1)) 33930031 3394create table t1 as select rtrim(1) as c1; 3395show create table t1; 3396Table Create Table 3397t1 CREATE TABLE `t1` ( 3398 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3399) ENGINE=MyISAM DEFAULT CHARSET=latin1 3400drop table t1; 3401select hex(soundex(1)); 3402hex(soundex(1)) 3403 3404create table t1 as select soundex(1) as c1; 3405show create table t1; 3406Table Create Table 3407t1 CREATE TABLE `t1` ( 3408 `c1` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 3409) ENGINE=MyISAM DEFAULT CHARSET=latin1 3410drop table t1; 3411select hex(substring(1,1,1)); 3412hex(substring(1,1,1)) 34130031 3414create table t1 as select substring(1,1,1) as c1; 3415show create table t1; 3416Table Create Table 3417t1 CREATE TABLE `t1` ( 3418 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3419) ENGINE=MyISAM DEFAULT CHARSET=latin1 3420drop table t1; 3421select hex(trim(1)); 3422hex(trim(1)) 34230031 3424create table t1 as select trim(1) as c1; 3425show create table t1; 3426Table Create Table 3427t1 CREATE TABLE `t1` ( 3428 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3429) ENGINE=MyISAM DEFAULT CHARSET=latin1 3430drop table t1; 3431select hex(ucase(1)); 3432hex(ucase(1)) 34330031 3434create table t1 as select ucase(1) as c1; 3435show create table t1; 3436Table Create Table 3437t1 CREATE TABLE `t1` ( 3438 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3439) ENGINE=MyISAM DEFAULT CHARSET=latin1 3440drop table t1; 3441select hex(upper(1)); 3442hex(upper(1)) 34430031 3444create table t1 as select upper(1) as c1; 3445show create table t1; 3446Table Create Table 3447t1 CREATE TABLE `t1` ( 3448 `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL 3449) ENGINE=MyISAM DEFAULT CHARSET=latin1 3450drop table t1; 3451create table t1 as select repeat(' ', 64) as a limit 0; 3452show create table t1; 3453Table Create Table 3454t1 CREATE TABLE `t1` ( 3455 `a` varchar(64) CHARACTER SET ucs2 DEFAULT NULL 3456) ENGINE=MyISAM DEFAULT CHARSET=latin1 3457insert into t1 values ("1.1"), ("2.1"); 3458select a, hex(a) from t1; 3459a hex(a) 34601.1 0031002E0031 34612.1 0032002E0031 3462update t1 set a= a + 0.1; 3463select a, hex(a) from t1; 3464a hex(a) 34651.2000000000000002 0031002E0032003000300030003000300030003000300030003000300030003000300032 34662.2 0032002E0032 3467drop table t1; 3468create table t1 (a tinyint); 3469insert into t1 values (1); 3470select hex(concat(a)) from t1; 3471hex(concat(a)) 34720031 3473create table t2 as select concat(a) from t1; 3474show create table t2; 3475Table Create Table 3476t2 CREATE TABLE `t2` ( 3477 `concat(a)` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 3478) ENGINE=MyISAM DEFAULT CHARSET=latin1 3479drop table t1, t2; 3480create table t1 (a tinyint zerofill); 3481insert into t1 values (1), (10), (100); 3482select hex(concat(a)), a from t1; 3483hex(concat(a)) a 3484003000300031 001 3485003000310030 010 3486003100300030 100 3487drop table t1; 3488create table t1 (a tinyint(4) zerofill); 3489insert into t1 values (1), (10), (100); 3490select hex(concat(a)), a from t1; 3491hex(concat(a)) a 34920030003000300031 0001 34930030003000310030 0010 34940030003100300030 0100 3495drop table t1; 3496create table t1 (a decimal(10,2)); 3497insert into t1 values (123.45); 3498select hex(concat(a)) from t1; 3499hex(concat(a)) 3500003100320033002E00340035 3501create table t2 as select concat(a) from t1; 3502show create table t2; 3503Table Create Table 3504t2 CREATE TABLE `t2` ( 3505 `concat(a)` varchar(12) CHARACTER SET ucs2 DEFAULT NULL 3506) ENGINE=MyISAM DEFAULT CHARSET=latin1 3507drop table t1, t2; 3508create table t1 (a smallint); 3509insert into t1 values (1); 3510select hex(concat(a)) from t1; 3511hex(concat(a)) 35120031 3513create table t2 as select concat(a) from t1; 3514show create table t2; 3515Table Create Table 3516t2 CREATE TABLE `t2` ( 3517 `concat(a)` varchar(6) CHARACTER SET ucs2 DEFAULT NULL 3518) ENGINE=MyISAM DEFAULT CHARSET=latin1 3519drop table t1, t2; 3520create table t1 (a smallint zerofill); 3521insert into t1 values (1), (10), (100), (1000), (10000); 3522select hex(concat(a)), a from t1; 3523hex(concat(a)) a 352400300030003000300031 00001 352500300030003000310030 00010 352600300030003100300030 00100 352700300031003000300030 01000 352800310030003000300030 10000 3529drop table t1; 3530create table t1 (a mediumint); 3531insert into t1 values (1); 3532select hex(concat(a)) from t1; 3533hex(concat(a)) 35340031 3535create table t2 as select concat(a) from t1; 3536show create table t2; 3537Table Create Table 3538t2 CREATE TABLE `t2` ( 3539 `concat(a)` varchar(9) CHARACTER SET ucs2 DEFAULT NULL 3540) ENGINE=MyISAM DEFAULT CHARSET=latin1 3541drop table t1, t2; 3542create table t1 (a mediumint zerofill); 3543insert into t1 values (1), (10), (100), (1000), (10000); 3544select hex(concat(a)), a from t1; 3545hex(concat(a)) a 354600300030003000300030003000300031 00000001 354700300030003000300030003000310030 00000010 354800300030003000300030003100300030 00000100 354900300030003000300031003000300030 00001000 355000300030003000310030003000300030 00010000 3551drop table t1; 3552create table t1 (a int); 3553insert into t1 values (1); 3554select hex(concat(a)) from t1; 3555hex(concat(a)) 35560031 3557create table t2 as select concat(a) from t1; 3558show create table t2; 3559Table Create Table 3560t2 CREATE TABLE `t2` ( 3561 `concat(a)` varchar(11) CHARACTER SET ucs2 DEFAULT NULL 3562) ENGINE=MyISAM DEFAULT CHARSET=latin1 3563drop table t1, t2; 3564create table t1 (a int zerofill); 3565insert into t1 values (1), (10), (100), (1000), (10000); 3566select hex(concat(a)), a from t1; 3567hex(concat(a)) a 35680030003000300030003000300030003000300031 0000000001 35690030003000300030003000300030003000310030 0000000010 35700030003000300030003000300030003100300030 0000000100 35710030003000300030003000300031003000300030 0000001000 35720030003000300030003000310030003000300030 0000010000 3573drop table t1; 3574create table t1 (a bigint); 3575insert into t1 values (1); 3576select hex(concat(a)) from t1; 3577hex(concat(a)) 35780031 3579create table t2 as select concat(a) from t1; 3580show create table t2; 3581Table Create Table 3582t2 CREATE TABLE `t2` ( 3583 `concat(a)` varchar(20) CHARACTER SET ucs2 DEFAULT NULL 3584) ENGINE=MyISAM DEFAULT CHARSET=latin1 3585drop table t1, t2; 3586create table t1 (a bigint zerofill); 3587insert into t1 values (1), (10), (100), (1000), (10000); 3588select hex(concat(a)), a from t1; 3589hex(concat(a)) a 359000300030003000300030003000300030003000300030003000300030003000300030003000300031 00000000000000000001 359100300030003000300030003000300030003000300030003000300030003000300030003000310030 00000000000000000010 359200300030003000300030003000300030003000300030003000300030003000300030003100300030 00000000000000000100 359300300030003000300030003000300030003000300030003000300030003000300031003000300030 00000000000000001000 359400300030003000300030003000300030003000300030003000300030003000310030003000300030 00000000000000010000 3595drop table t1; 3596create table t1 (a float); 3597insert into t1 values (123.456); 3598select hex(concat(a)) from t1; 3599hex(concat(a)) 3600003100320033002E003400350036 3601select concat(a) from t1; 3602concat(a) 3603123.456 3604create table t2 as select concat(a) from t1; 3605show create table t2; 3606Table Create Table 3607t2 CREATE TABLE `t2` ( 3608 `concat(a)` varchar(12) CHARACTER SET ucs2 DEFAULT NULL 3609) ENGINE=MyISAM DEFAULT CHARSET=latin1 3610drop table t1, t2; 3611create table t1 (a float zerofill); 3612insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 3613select hex(concat(a)), a from t1; 3614hex(concat(a)) a 36150030003000300030003000300030003000300031002E0031 0000000001.1 36160030003000300030003000300030003000310030002E0031 0000000010.1 36170030003000300030003000300030003100300030002E0031 0000000100.1 36180030003000300030003000300031003000300030002E0031 0000001000.1 36190030003000300030003000310030003000300030002E0031 0000010000.1 3620drop table t1; 3621create table t1 (a double); 3622insert into t1 values (123.456); 3623select hex(concat(a)) from t1; 3624hex(concat(a)) 3625003100320033002E003400350036 3626select concat(a) from t1; 3627concat(a) 3628123.456 3629create table t2 as select concat(a) from t1; 3630show create table t2; 3631Table Create Table 3632t2 CREATE TABLE `t2` ( 3633 `concat(a)` varchar(22) CHARACTER SET ucs2 DEFAULT NULL 3634) ENGINE=MyISAM DEFAULT CHARSET=latin1 3635drop table t1, t2; 3636create table t1 (a double zerofill); 3637insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 3638select hex(concat(a)), a from t1; 3639hex(concat(a)) a 364000300030003000300030003000300030003000300030003000300030003000300030003000300031002E0031 00000000000000000001.1 364100300030003000300030003000300030003000300030003000300030003000300030003000310030002E0031 00000000000000000010.1 364200300030003000300030003000300030003000300030003000300030003000300030003100300030002E0031 00000000000000000100.1 364300300030003000300030003000300030003000300030003000300030003000300031003000300030002E0031 00000000000000001000.1 364400300030003000300030003000300030003000300030003000300030003000310030003000300030002E0031 00000000000000010000.1 3645drop table t1; 3646create table t1 (a year(2)); 3647Warnings: 3648Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 3649insert into t1 values (1); 3650select hex(concat(a)) from t1; 3651hex(concat(a)) 365200300031 3653create table t2 as select concat(a) from t1; 3654show create table t2; 3655Table Create Table 3656t2 CREATE TABLE `t2` ( 3657 `concat(a)` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 3658) ENGINE=MyISAM DEFAULT CHARSET=latin1 3659drop table t1, t2; 3660create table t1 (a year); 3661insert into t1 values (1); 3662select hex(concat(a)) from t1; 3663hex(concat(a)) 36640032003000300031 3665create table t2 as select concat(a) from t1; 3666show create table t2; 3667Table Create Table 3668t2 CREATE TABLE `t2` ( 3669 `concat(a)` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 3670) ENGINE=MyISAM DEFAULT CHARSET=latin1 3671drop table t1, t2; 3672create table t1 (a bit(64)); 3673insert into t1 values (1); 3674select hex(concat(a)) from t1; 3675hex(concat(a)) 36760000000000000001 3677create table t2 as select concat(a) from t1; 3678show create table t2; 3679Table Create Table 3680t2 CREATE TABLE `t2` ( 3681 `concat(a)` varbinary(64) DEFAULT NULL 3682) ENGINE=MyISAM DEFAULT CHARSET=latin1 3683drop table t1, t2; 3684create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 3685insert into t1 values (0); 3686insert into t1 values (20010203040506); 3687insert into t1 values (19800203040506); 3688insert into t1 values ('2001-02-03 04:05:06'); 3689select hex(concat(a)) from t1; 3690hex(concat(a)) 36910030003000300030002D00300030002D00300030002000300030003A00300030003A00300030 36920032003000300031002D00300032002D00300033002000300034003A00300035003A00300036 36930031003900380030002D00300032002D00300033002000300034003A00300035003A00300036 36940032003000300031002D00300032002D00300033002000300034003A00300035003A00300036 3695select concat(a) from t1; 3696concat(a) 36970000-00-00 00:00:00 36982001-02-03 04:05:06 36991980-02-03 04:05:06 37002001-02-03 04:05:06 3701create table t2 as select concat(a) from t1; 3702show create table t2; 3703Table Create Table 3704t2 CREATE TABLE `t2` ( 3705 `concat(a)` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 3706) ENGINE=MyISAM DEFAULT CHARSET=latin1 3707drop table t1, t2; 3708create table t1 (a date); 3709insert into t1 values ('2001-02-03'); 3710insert into t1 values (20010203); 3711select hex(concat(a)) from t1; 3712hex(concat(a)) 37130032003000300031002D00300032002D00300033 37140032003000300031002D00300032002D00300033 3715create table t2 as select concat(a) from t1; 3716show create table t2; 3717Table Create Table 3718t2 CREATE TABLE `t2` ( 3719 `concat(a)` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3720) ENGINE=MyISAM DEFAULT CHARSET=latin1 3721drop table t1, t2; 3722create table t1 (a time); 3723insert into t1 values (1); 3724insert into t1 values ('01:02:03'); 3725select hex(concat(a)) from t1; 3726hex(concat(a)) 372700300030003A00300030003A00300031 372800300031003A00300032003A00300033 3729select concat(a) from t1; 3730concat(a) 373100:00:01 373201:02:03 3733create table t2 as select concat(a) from t1; 3734show create table t2; 3735Table Create Table 3736t2 CREATE TABLE `t2` ( 3737 `concat(a)` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 3738) ENGINE=MyISAM DEFAULT CHARSET=latin1 3739drop table t1, t2; 3740create table t1 (a datetime); 3741insert into t1 values ('2001-02-03 04:05:06'); 3742insert into t1 values (20010203040506); 3743select hex(concat(a)) from t1; 3744hex(concat(a)) 37450032003000300031002D00300032002D00300033002000300034003A00300035003A00300036 37460032003000300031002D00300032002D00300033002000300034003A00300035003A00300036 3747create table t2 as select concat(a) from t1; 3748show create table t2; 3749Table Create Table 3750t2 CREATE TABLE `t2` ( 3751 `concat(a)` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 3752) ENGINE=MyISAM DEFAULT CHARSET=latin1 3753drop table t1, t2; 3754create table t1 (a tinyint); 3755insert into t1 values (1); 3756create view v1(a) as select concat(a) from t1; 3757show columns from v1; 3758Field Type Null Key Default Extra 3759a varchar(4) YES NULL 3760select hex(a) from v1; 3761hex(a) 37620031 3763drop table t1; 3764drop view v1; 3765create table t1 (a tinyint zerofill); 3766insert into t1 values (1), (10), (100); 3767create view v1(a) as select concat(a) from t1; 3768show columns from v1; 3769Field Type Null Key Default Extra 3770a varchar(3) YES NULL 3771select hex(a) from v1; 3772hex(a) 3773003000300031 3774003000310030 3775003100300030 3776drop table t1; 3777drop view v1; 3778create table t1 (a tinyint(30) zerofill); 3779insert into t1 values (1), (10), (100); 3780create view v1(a) as select concat(a) from t1; 3781show columns from v1; 3782Field Type Null Key Default Extra 3783a varchar(30) YES NULL 3784select hex(a) from v1; 3785hex(a) 3786003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300031 3787003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000310030 3788003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003000300030003100300030 3789drop table t1; 3790drop view v1; 3791create table t1 (a decimal(10,2)); 3792insert into t1 values (123.45); 3793create view v1(a) as select concat(a) from t1; 3794show columns from v1; 3795Field Type Null Key Default Extra 3796a varchar(12) YES NULL 3797select hex(a) from v1; 3798hex(a) 3799003100320033002E00340035 3800drop table t1; 3801drop view v1; 3802create table t1 (a smallint); 3803insert into t1 values (1); 3804create view v1(a) as select concat(a) from t1; 3805show columns from v1; 3806Field Type Null Key Default Extra 3807a varchar(6) YES NULL 3808select hex(a) from v1; 3809hex(a) 38100031 3811drop table t1; 3812drop view v1; 3813create table t1 (a smallint zerofill); 3814insert into t1 values (1), (10), (100), (1000), (10000); 3815create view v1(a) as select concat(a) from t1; 3816show columns from v1; 3817Field Type Null Key Default Extra 3818a varchar(5) YES NULL 3819select hex(a) from v1; 3820hex(a) 382100300030003000300031 382200300030003000310030 382300300030003100300030 382400300031003000300030 382500310030003000300030 3826drop table t1; 3827drop view v1; 3828create table t1 (a mediumint); 3829insert into t1 values (1); 3830create view v1(a) as select concat(a) from t1; 3831show columns from v1; 3832Field Type Null Key Default Extra 3833a varchar(9) YES NULL 3834select hex(a) from v1; 3835hex(a) 38360031 3837drop table t1; 3838drop view v1; 3839create table t1 (a mediumint zerofill); 3840insert into t1 values (1), (10), (100), (1000), (10000); 3841create view v1(a) as select concat(a) from t1; 3842show columns from v1; 3843Field Type Null Key Default Extra 3844a varchar(8) YES NULL 3845select hex(a) from v1; 3846hex(a) 384700300030003000300030003000300031 384800300030003000300030003000310030 384900300030003000300030003100300030 385000300030003000300031003000300030 385100300030003000310030003000300030 3852drop table t1; 3853drop view v1; 3854create table t1 (a int); 3855insert into t1 values (1); 3856create view v1(a) as select concat(a) from t1; 3857show columns from v1; 3858Field Type Null Key Default Extra 3859a varchar(11) YES NULL 3860select hex(a) from v1; 3861hex(a) 38620031 3863drop table t1; 3864drop view v1; 3865create table t1 (a int zerofill); 3866insert into t1 values (1), (10), (100), (1000), (10000); 3867create view v1(a) as select concat(a) from t1; 3868show columns from v1; 3869Field Type Null Key Default Extra 3870a varchar(10) YES NULL 3871select hex(a) from v1; 3872hex(a) 38730030003000300030003000300030003000300031 38740030003000300030003000300030003000310030 38750030003000300030003000300030003100300030 38760030003000300030003000300031003000300030 38770030003000300030003000310030003000300030 3878drop table t1; 3879drop view v1; 3880create table t1 (a bigint); 3881insert into t1 values (1); 3882create view v1(a) as select concat(a) from t1; 3883show columns from v1; 3884Field Type Null Key Default Extra 3885a varchar(20) YES NULL 3886select hex(a) from v1; 3887hex(a) 38880031 3889drop table t1; 3890drop view v1; 3891create table t1 (a bigint zerofill); 3892insert into t1 values (1), (10), (100), (1000), (10000); 3893create view v1(a) as select concat(a) from t1; 3894show columns from v1; 3895Field Type Null Key Default Extra 3896a varchar(20) YES NULL 3897select hex(a) from v1; 3898hex(a) 389900300030003000300030003000300030003000300030003000300030003000300030003000300031 390000300030003000300030003000300030003000300030003000300030003000300030003000310030 390100300030003000300030003000300030003000300030003000300030003000300030003100300030 390200300030003000300030003000300030003000300030003000300030003000300031003000300030 390300300030003000300030003000300030003000300030003000300030003000310030003000300030 3904drop table t1; 3905drop view v1; 3906create table t1 (a float); 3907insert into t1 values (123.456); 3908create view v1(a) as select concat(a) from t1; 3909show columns from v1; 3910Field Type Null Key Default Extra 3911a varchar(12) YES NULL 3912select hex(a) from v1; 3913hex(a) 3914003100320033002E003400350036 3915drop table t1; 3916drop view v1; 3917create table t1 (a float zerofill); 3918insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 3919create view v1(a) as select concat(a) from t1; 3920show columns from v1; 3921Field Type Null Key Default Extra 3922a varchar(12) YES NULL 3923select hex(a) from v1; 3924hex(a) 39250030003000300030003000300030003000300031002E0031 39260030003000300030003000300030003000310030002E0031 39270030003000300030003000300030003100300030002E0031 39280030003000300030003000300031003000300030002E0031 39290030003000300030003000310030003000300030002E0031 3930drop table t1; 3931drop view v1; 3932create table t1 (a double); 3933insert into t1 values (123.456); 3934select concat(a) from t1; 3935concat(a) 3936123.456 3937create view v1(a) as select concat(a) from t1; 3938show columns from v1; 3939Field Type Null Key Default Extra 3940a varchar(22) YES NULL 3941select hex(a) from v1; 3942hex(a) 3943003100320033002E003400350036 3944drop table t1; 3945drop view v1; 3946create table t1 (a double zerofill); 3947insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 3948create view v1(a) as select concat(a) from t1; 3949show columns from v1; 3950Field Type Null Key Default Extra 3951a varchar(22) YES NULL 3952select hex(a) from v1; 3953hex(a) 395400300030003000300030003000300030003000300030003000300030003000300030003000300031002E0031 395500300030003000300030003000300030003000300030003000300030003000300030003000310030002E0031 395600300030003000300030003000300030003000300030003000300030003000300030003100300030002E0031 395700300030003000300030003000300030003000300030003000300030003000300031003000300030002E0031 395800300030003000300030003000300030003000300030003000300030003000310030003000300030002E0031 3959drop table t1; 3960drop view v1; 3961create table t1 (a year(2)); 3962Warnings: 3963Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 3964insert into t1 values (1); 3965create view v1(a) as select concat(a) from t1; 3966show columns from v1; 3967Field Type Null Key Default Extra 3968a varchar(2) YES NULL 3969select hex(a) from v1; 3970hex(a) 397100300031 3972drop table t1; 3973drop view v1; 3974create table t1 (a year); 3975insert into t1 values (1); 3976create view v1(a) as select concat(a) from t1; 3977show columns from v1; 3978Field Type Null Key Default Extra 3979a varchar(4) YES NULL 3980select hex(a) from v1; 3981hex(a) 39820032003000300031 3983drop table t1; 3984drop view v1; 3985create table t1 (a bit(64)); 3986insert into t1 values (1); 3987create view v1(a) as select concat(a) from t1; 3988show columns from v1; 3989Field Type Null Key Default Extra 3990a varbinary(64) YES NULL 3991select hex(a) from v1; 3992hex(a) 39930000000000000001 3994drop table t1; 3995drop view v1; 3996create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 3997insert into t1 values (0); 3998insert into t1 values (20010203040506); 3999insert into t1 values (19800203040506); 4000insert into t1 values ('2001-02-03 04:05:06'); 4001create view v1(a) as select concat(a) from t1; 4002show columns from v1; 4003Field Type Null Key Default Extra 4004a varchar(19) YES NULL 4005select hex(a) from v1; 4006hex(a) 40070030003000300030002D00300030002D00300030002000300030003A00300030003A00300030 40080032003000300031002D00300032002D00300033002000300034003A00300035003A00300036 40090031003900380030002D00300032002D00300033002000300034003A00300035003A00300036 40100032003000300031002D00300032002D00300033002000300034003A00300035003A00300036 4011drop table t1; 4012drop view v1; 4013create table t1 (a date); 4014insert into t1 values ('2001-02-03'); 4015insert into t1 values (20010203); 4016create view v1(a) as select concat(a) from t1; 4017show columns from v1; 4018Field Type Null Key Default Extra 4019a varchar(10) YES NULL 4020select hex(a) from v1; 4021hex(a) 40220032003000300031002D00300032002D00300033 40230032003000300031002D00300032002D00300033 4024drop table t1; 4025drop view v1; 4026create table t1 (a time); 4027insert into t1 values (1); 4028insert into t1 values ('01:02:03'); 4029create view v1(a) as select concat(a) from t1; 4030show columns from v1; 4031Field Type Null Key Default Extra 4032a varchar(10) YES NULL 4033select hex(a) from v1; 4034hex(a) 403500300030003A00300030003A00300031 403600300031003A00300032003A00300033 4037drop table t1; 4038drop view v1; 4039create table t1 (a datetime); 4040insert into t1 values ('2001-02-03 04:05:06'); 4041insert into t1 values (20010203040506); 4042create view v1(a) as select concat(a) from t1; 4043show columns from v1; 4044Field Type Null Key Default Extra 4045a varchar(19) YES NULL 4046select hex(a) from v1; 4047hex(a) 40480032003000300031002D00300032002D00300033002000300034003A00300035003A00300036 40490032003000300031002D00300032002D00300033002000300034003A00300035003A00300036 4050drop table t1; 4051drop view v1; 4052create function f1 (par1 int) returns int 4053begin 4054return concat(par1); 4055end| 4056set @a= f1(1); 4057select hex(@a); 4058hex(@a) 40591 4060select hex(concat(f1(1))); 4061hex(concat(f1(1))) 406231 4063create table t1 as select f1(1) as c1; 4064show create table t1; 4065Table Create Table 4066t1 CREATE TABLE `t1` ( 4067 `c1` int(11) DEFAULT NULL 4068) ENGINE=MyISAM DEFAULT CHARSET=latin1 4069drop table t1; 4070create table t1 as select concat(f1(1)) as c1; 4071show create table t1; 4072Table Create Table 4073t1 CREATE TABLE `t1` ( 4074 `c1` varchar(11) DEFAULT NULL 4075) ENGINE=MyISAM DEFAULT CHARSET=latin1 4076create view v1 as select concat(f1(1)) as c1; 4077show columns from v1; 4078Field Type Null Key Default Extra 4079c1 varchar(11) YES NULL 4080drop table t1; 4081drop view v1; 4082drop function f1; 4083create function f1 (par1 decimal(18,2)) returns decimal(18,2) 4084begin 4085return concat(par1); 4086end| 4087set @a= f1(123.45); 4088select hex(@a); 4089hex(@a) 40907B 4091select hex(concat(f1(123.45))); 4092hex(concat(f1(123.45))) 40933132332E3435 4094create table t1 as select f1(123.45) as c1; 4095show create table t1; 4096Table Create Table 4097t1 CREATE TABLE `t1` ( 4098 `c1` decimal(18,2) DEFAULT NULL 4099) ENGINE=MyISAM DEFAULT CHARSET=latin1 4100drop table t1; 4101create table t1 as select concat(f1(123.45)) as c1; 4102show create table t1; 4103Table Create Table 4104t1 CREATE TABLE `t1` ( 4105 `c1` varchar(20) DEFAULT NULL 4106) ENGINE=MyISAM DEFAULT CHARSET=latin1 4107create view v1 as select concat(f1(123.45)) as c1; 4108show columns from v1; 4109Field Type Null Key Default Extra 4110c1 varchar(20) YES NULL 4111drop table t1; 4112drop view v1; 4113drop function f1; 4114create function f1 (par1 float) returns float 4115begin 4116return concat(par1); 4117end| 4118set @a= f1(123.45); 4119select hex(@a); 4120hex(@a) 41217B 4122select hex(concat(f1(123.45))); 4123hex(concat(f1(123.45))) 41243132332E3435 4125create table t1 as select f1(123.45) as c1; 4126show create table t1; 4127Table Create Table 4128t1 CREATE TABLE `t1` ( 4129 `c1` float DEFAULT NULL 4130) ENGINE=MyISAM DEFAULT CHARSET=latin1 4131drop table t1; 4132create table t1 as select concat(f1(123.45)) as c1; 4133show create table t1; 4134Table Create Table 4135t1 CREATE TABLE `t1` ( 4136 `c1` varchar(12) DEFAULT NULL 4137) ENGINE=MyISAM DEFAULT CHARSET=latin1 4138create view v1 as select concat(f1(123.45)) as c1; 4139show columns from v1; 4140Field Type Null Key Default Extra 4141c1 varchar(12) YES NULL 4142drop table t1; 4143drop view v1; 4144drop function f1; 4145create function f1 (par1 date) returns date 4146begin 4147return concat(par1); 4148end| 4149set @a= f1(cast('2001-01-02' as date)); 4150select hex(@a); 4151hex(@a) 4152323030312D30312D3032 4153select hex(concat(f1(cast('2001-01-02' as date)))); 4154hex(concat(f1(cast('2001-01-02' as date)))) 4155323030312D30312D3032 4156create table t1 as select f1(cast('2001-01-02' as date)) as c1; 4157show create table t1; 4158Table Create Table 4159t1 CREATE TABLE `t1` ( 4160 `c1` date DEFAULT NULL 4161) ENGINE=MyISAM DEFAULT CHARSET=latin1 4162drop table t1; 4163create table t1 as select concat(f1(cast('2001-01-02' as date))) as c1; 4164show create table t1; 4165Table Create Table 4166t1 CREATE TABLE `t1` ( 4167 `c1` varchar(10) DEFAULT NULL 4168) ENGINE=MyISAM DEFAULT CHARSET=latin1 4169create view v1 as select concat(f1(cast('2001-01-02' as date))) as c1; 4170show columns from v1; 4171Field Type Null Key Default Extra 4172c1 varchar(10) YES NULL 4173drop table t1; 4174drop view v1; 4175drop function f1; 4176# 4177# End of WL#2649 Number-to-string conversions 4178# 4179# 4180# Bug#54668 User variable assignments get wrong type 4181# 4182SET @x=md5('a'); 4183SELECT charset(@x), collation(@x); 4184charset(@x) collation(@x) 4185ucs2 ucs2_general_ci 4186SET @x=old_password('a'); 4187SELECT charset(@x), collation(@x); 4188charset(@x) collation(@x) 4189ucs2 ucs2_general_ci 4190SET @x=password('a'); 4191SELECT charset(@x), collation(@x); 4192charset(@x) collation(@x) 4193ucs2 ucs2_general_ci 4194SET @x=sha('a'); 4195SELECT charset(@x), collation(@x); 4196charset(@x) collation(@x) 4197ucs2 ucs2_general_ci 4198SET @x=sha1('a'); 4199SELECT charset(@x), collation(@x); 4200charset(@x) collation(@x) 4201ucs2 ucs2_general_ci 4202SET @x=astext(point(1,2)); 4203SELECT charset(@x), collation(@x); 4204charset(@x) collation(@x) 4205ucs2 ucs2_general_ci 4206SET @x=aswkt(point(1,2)); 4207SELECT charset(@x), collation(@x); 4208charset(@x) collation(@x) 4209ucs2 ucs2_general_ci 4210# 4211# Bug#54916 GROUP_CONCAT + IFNULL truncates output 4212# 4213SELECT @@collation_connection; 4214@@collation_connection 4215ucs2_general_ci 4216CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM; 4217INSERT INTO t1 VALUES (1234567); 4218SELECT GROUP_CONCAT(IFNULL(a,'')) FROM t1; 4219GROUP_CONCAT(IFNULL(a,'')) 42201234567 4221SELECT GROUP_CONCAT(IF(a,a,'')) FROM t1; 4222GROUP_CONCAT(IF(a,a,'')) 42231234567 4224SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1; 4225GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) 42261234567 4227SELECT COALESCE(a,'') FROM t1 GROUP BY 1; 4228Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 4229def COALESCE(a,'') 253 9 7 Y 0 39 8 4230COALESCE(a,'') 42311234567 4232# All columns must be VARCHAR(9) with the same length: 4233CREATE TABLE t2 AS 4234SELECT 4235CONCAT(a), 4236IFNULL(a,''), 4237IF(a,a,''), 4238CASE WHEN a THEN a ELSE '' END, 4239COALESCE(a,'') 4240FROM t1; 4241SHOW CREATE TABLE t2; 4242Table Create Table 4243t2 CREATE TABLE `t2` ( 4244 `CONCAT(a)` varchar(9) CHARACTER SET ucs2 DEFAULT NULL, 4245 `IFNULL(a,'')` varchar(9) CHARACTER SET ucs2 NOT NULL, 4246 `IF(a,a,'')` varchar(9) CHARACTER SET ucs2 DEFAULT NULL, 4247 `CASE WHEN a THEN a ELSE '' END` varchar(9) CHARACTER SET ucs2 DEFAULT NULL, 4248 `COALESCE(a,'')` varchar(9) CHARACTER SET ucs2 DEFAULT NULL 4249) ENGINE=MyISAM DEFAULT CHARSET=latin1 4250DROP TABLE t2; 4251CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1; 4252SHOW CREATE TABLE t2; 4253Table Create Table 4254t2 CREATE TABLE `t2` ( 4255 `CONCAT_WS(1,2,3)` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 4256) ENGINE=MyISAM DEFAULT CHARSET=latin1 4257DROP TABLE t2; 4258CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1; 4259SHOW CREATE TABLE t2; 4260Table Create Table 4261t2 CREATE TABLE `t2` ( 4262 `INSERT(1133,3,0,22)` varchar(6) CHARACTER SET ucs2 DEFAULT NULL 4263) ENGINE=MyISAM DEFAULT CHARSET=latin1 4264DROP TABLE t2; 4265CREATE TABLE t2 AS SELECT LCASE(a) FROM t1; 4266SHOW CREATE TABLE t2; 4267Table Create Table 4268t2 CREATE TABLE `t2` ( 4269 `LCASE(a)` varchar(9) CHARACTER SET ucs2 DEFAULT NULL 4270) ENGINE=MyISAM DEFAULT CHARSET=latin1 4271DROP TABLE t2; 4272CREATE TABLE t2 AS SELECT UCASE(a) FROM t1; 4273SHOW CREATE TABLE t2; 4274Table Create Table 4275t2 CREATE TABLE `t2` ( 4276 `UCASE(a)` varchar(9) CHARACTER SET ucs2 DEFAULT NULL 4277) ENGINE=MyISAM DEFAULT CHARSET=latin1 4278DROP TABLE t2; 4279CREATE TABLE t2 AS SELECT REPEAT(1,2) FROM t1; 4280SHOW CREATE TABLE t2; 4281Table Create Table 4282t2 CREATE TABLE `t2` ( 4283 `REPEAT(1,2)` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 4284) ENGINE=MyISAM DEFAULT CHARSET=latin1 4285DROP TABLE t2; 4286CREATE TABLE t2 AS SELECT LEFT(123,2) FROM t1; 4287SHOW CREATE TABLE t2; 4288Table Create Table 4289t2 CREATE TABLE `t2` ( 4290 `LEFT(123,2)` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 4291) ENGINE=MyISAM DEFAULT CHARSET=latin1 4292DROP TABLE t2; 4293CREATE TABLE t2 AS SELECT RIGHT(123,2) FROM t1; 4294SHOW CREATE TABLE t2; 4295Table Create Table 4296t2 CREATE TABLE `t2` ( 4297 `RIGHT(123,2)` varchar(2) CHARACTER SET ucs2 DEFAULT NULL 4298) ENGINE=MyISAM DEFAULT CHARSET=latin1 4299DROP TABLE t2; 4300CREATE TABLE t2 AS SELECT LTRIM(123) FROM t1; 4301SHOW CREATE TABLE t2; 4302Table Create Table 4303t2 CREATE TABLE `t2` ( 4304 `LTRIM(123)` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 4305) ENGINE=MyISAM DEFAULT CHARSET=latin1 4306DROP TABLE t2; 4307CREATE TABLE t2 AS SELECT RTRIM(123) FROM t1; 4308SHOW CREATE TABLE t2; 4309Table Create Table 4310t2 CREATE TABLE `t2` ( 4311 `RTRIM(123)` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 4312) ENGINE=MyISAM DEFAULT CHARSET=latin1 4313DROP TABLE t2; 4314CREATE TABLE t2 AS SELECT ELT(1,111,222,333) FROM t1; 4315SHOW CREATE TABLE t2; 4316Table Create Table 4317t2 CREATE TABLE `t2` ( 4318 `ELT(1,111,222,333)` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 4319) ENGINE=MyISAM DEFAULT CHARSET=latin1 4320DROP TABLE t2; 4321CREATE TABLE t2 AS SELECT REPLACE(111,2,3) FROM t1; 4322SHOW CREATE TABLE t2; 4323Table Create Table 4324t2 CREATE TABLE `t2` ( 4325 `REPLACE(111,2,3)` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 4326) ENGINE=MyISAM DEFAULT CHARSET=latin1 4327DROP TABLE t2; 4328CREATE TABLE t2 AS SELECT SUBSTRING_INDEX(111,111,1) FROM t1; 4329SHOW CREATE TABLE t2; 4330Table Create Table 4331t2 CREATE TABLE `t2` ( 4332 `SUBSTRING_INDEX(111,111,1)` varchar(3) CHARACTER SET ucs2 DEFAULT NULL 4333) ENGINE=MyISAM DEFAULT CHARSET=latin1 4334DROP TABLE t2; 4335CREATE TABLE t2 AS SELECT MAKE_SET(111,222,3) FROM t1; 4336SHOW CREATE TABLE t2; 4337Table Create Table 4338t2 CREATE TABLE `t2` ( 4339 `MAKE_SET(111,222,3)` varchar(5) CHARACTER SET ucs2 DEFAULT NULL 4340) ENGINE=MyISAM DEFAULT CHARSET=latin1 4341DROP TABLE t2; 4342CREATE TABLE t2 AS SELECT SOUNDEX(1) FROM t1; 4343SHOW CREATE TABLE t2; 4344Table Create Table 4345t2 CREATE TABLE `t2` ( 4346 `SOUNDEX(1)` varchar(4) CHARACTER SET ucs2 DEFAULT NULL 4347) ENGINE=MyISAM DEFAULT CHARSET=latin1 4348DROP TABLE t2; 4349CREATE TABLE t2 AS SELECT EXPORT_SET(1,'Y','N','',8); 4350SHOW CREATE TABLE t2; 4351Table Create Table 4352t2 CREATE TABLE `t2` ( 4353 `EXPORT_SET(1,'Y','N','',8)` varchar(64) CHARACTER SET ucs2 DEFAULT NULL 4354) ENGINE=MyISAM DEFAULT CHARSET=latin1 4355DROP TABLE t2; 4356DROP TABLE t1; 4357# 4358# End of Bug#54916 4359# 4360# 4361# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields 4362# 4363SELECT @@collation_connection; 4364@@collation_connection 4365ucs2_general_ci 4366CREATE TABLE t1 ( 4367id INT(11) DEFAULT NULL, 4368date_column DATE DEFAULT NULL, 4369KEY(date_column)); 4370INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01'); 4371EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; 4372id select_type table type possible_keys key key_len ref rows Extra 43731 SIMPLE t1 range date_column date_column 4 NULL 2 Using index condition 4374ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL; 4375EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; 4376id select_type table type possible_keys key key_len ref rows Extra 43771 SIMPLE t1 range date_column date_column 6 NULL 2 Using index condition 4378DROP TABLE t1; 4379# 4380# Bug #31384 DATE_ADD() and DATE_SUB() return binary data 4381# 4382SELECT @@collation_connection, @@character_set_results; 4383@@collation_connection @@character_set_results 4384ucs2_general_ci latin1 4385SELECT 4386CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, 4387CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, 4388CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, 4389CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; 4390field_str1 field_str2 field_date field_datetime 4391ucs2 ucs2 binary binary 4392CREATE TABLE t1 AS 4393SELECT 4394DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, 4395DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, 4396DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, 4397DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; 4398SHOW CREATE TABLE t1; 4399Table Create Table 4400t1 CREATE TABLE `t1` ( 4401 `field_str1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, 4402 `field1_str2` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, 4403 `field_date` date DEFAULT NULL, 4404 `field_datetime` datetime DEFAULT NULL 4405) ENGINE=MyISAM DEFAULT CHARSET=latin1 4406DROP TABLE t1; 4407SELECT 4408DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1, 4409DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, 4410DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, 4411DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; 4412Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 4413def field_str1 254 19 10 Y 0 0 8 4414def field1_str2 254 19 19 Y 0 0 8 4415def field_date 10 10 10 Y 128 0 63 4416def field_datetime 12 19 19 Y 128 0 63 4417field_str1 field1_str2 field_date field_datetime 44182007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00 4419SELECT 4420HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, 4421HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2, 4422HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, 4423HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; 4424field_str1 field1_str2 field_date field_datetime 44250032003000300037002D00300038002D00300032002000320033003A00350039003A00300030 0032003000300037002D00300038002D00300033002000310037003A00330032003A00300030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 4426# 4427# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() 4428# 4429SELECT @@collation_connection, @@character_set_results; 4430@@collation_connection @@character_set_results 4431ucs2_general_ci latin1 4432SELECT 4433CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, 4434CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, 4435CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, 4436CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; 4437addtime1 addtime2 date_add1 date_add2 4438ucs2 ucs2 ucs2 ucs2 4439CREATE TABLE t1 AS 4440SELECT 4441ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, 4442ADDTIME('10:01:01','10:00:00') AS addtime2, 4443DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, 4444DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; 4445SHOW CREATE TABLE t1; 4446Table Create Table 4447t1 CREATE TABLE `t1` ( 4448 `addtime1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, 4449 `addtime2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, 4450 `date_add1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, 4451 `date_add2` varchar(19) CHARACTER SET ucs2 DEFAULT NULL 4452) ENGINE=MyISAM DEFAULT CHARSET=latin1 4453SELECT * FROM t1; 4454addtime1 addtime2 date_add1 date_add2 445520:01:01 20:01:01 2001-01-01 10:01:11 2001-01-01 10:01:11 4456DROP TABLE t1; 4457# 4458# Bug#11926811 / Bug#60625 Illegal mix of collations 4459# 4460SELECT @@collation_connection; 4461@@collation_connection 4462ucs2_general_ci 4463CREATE PROCEDURE p1() 4464BEGIN 4465DECLARE v_LastPaymentDate DATETIME DEFAULT NULL; 4466SELECT v_LastPaymentDate < NOW(); 4467EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW(); 4468SHOW WARNINGS; 4469EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW()); 4470END// 4471CALL p1; 4472v_LastPaymentDate < NOW() 4473NULL 4474id select_type table type possible_keys key key_len ref rows filtered Extra 44751 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4476Level Code Message 4477Note 1003 select v_LastPaymentDate@0 < current_timestamp() AS `v_LastPaymentDate < NOW()` 4478id select_type table type possible_keys key key_len ref rows filtered Extra 44791 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4480Warnings: 4481Note 1003 select concat(convert(v_LastPaymentDate@0 using ucs2),convert(current_timestamp() using ucs2)) AS `CONCAT(v_LastPaymentDate, NOW())` 4482DROP PROCEDURE p1; 4483# 4484# Bug#52159 returning time type from function and empty left join causes debug assertion 4485# 4486CREATE FUNCTION f1() RETURNS TIME RETURN 1; 4487CREATE TABLE t1 (b INT); 4488INSERT INTO t1 VALUES (0); 4489SELECT f1() FROM t1 LEFT JOIN (SELECT 1 AS a FROM t1 LIMIT 0) AS d ON 1 GROUP BY a; 4490f1() 449100:00:01 4492DROP FUNCTION f1; 4493DROP TABLE t1; 4494# 4495# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool) 4496# 4497SELECT @@collation_connection; 4498@@collation_connection 4499ucs2_general_ci 4500SELECT CASE 1 WHEN 2 THEN ( - '3' ) END; 4501CASE 1 WHEN 2 THEN ( - '3' ) END 4502NULL 4503# 4504# MDEV-5702 Incorrect results are returned with NULLIF() 4505# 4506CREATE TABLE t1 (d DATE); 4507INSERT INTO t1 VALUES ('1999-11-11'),('2014-02-04'); 4508SELECT DISTINCT d, CAST(d AS CHAR), NULLIF(d,"2000-01-01") AS bad, NULLIF(CAST(d AS CHAR),"2000-01-01") AS good FROM t1; 4509d CAST(d AS CHAR) bad good 45101999-11-11 1999-11-11 1999-11-11 1999-11-11 45112014-02-04 2014-02-04 2014-02-04 2014-02-04 4512CREATE TABLE t2 AS SELECT DISTINCT d, NULLIF(d,'2000-01-01') AS bad FROM t1; 4513SHOW CREATE TABLE t2; 4514Table Create Table 4515t2 CREATE TABLE `t2` ( 4516 `d` date DEFAULT NULL, 4517 `bad` date DEFAULT NULL 4518) ENGINE=MyISAM DEFAULT CHARSET=latin1 4519DROP TABLE t1, t2; 4520SET NAMES latin1; 4521SET sql_mode=''; 4522CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); 4523INSERT INTO t1 VALUES (); 4524SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; 4525maketime(`a`,`a`,`a`) 452600:00:00.000000 4527DROP TABLE t1; 4528SET sql_mode=default; 4529SET NAMES latin1; 4530# 4531# Bug #13832953 MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED 4532# 4533SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 4534CREATE TABLE t1 (c1 SET('','') CHARACTER SET ucs2); 4535Warnings: 4536Note 1291 Column 'c1' has duplicated value '' in SET 4537INSERT INTO t1 VALUES (''); 4538SELECT COALESCE(c1) FROM t1 ORDER BY 1; 4539COALESCE(c1) 4540 4541DROP TABLE t1; 4542# 4543# MDEV-5745 analyze MySQL fix for bug#12368495 4544# 4545SELECT CHAR_LENGTH(TRIM(LEADING 0x000000 FROM _ucs2 0x0061)); 4546CHAR_LENGTH(TRIM(LEADING 0x000000 FROM _ucs2 0x0061)) 45472 4548SELECT CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _ucs2 0x0061)); 4549CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _ucs2 0x0061)) 45502 4551SELECT CHAR_LENGTH(TRIM(LEADING 0x00 FROM _ucs2 0x0061)); 4552CHAR_LENGTH(TRIM(LEADING 0x00 FROM _ucs2 0x0061)) 45531 4554SELECT CHAR_LENGTH(TRIM(TRAILING 0x000000 FROM _ucs2 0x0061)); 4555CHAR_LENGTH(TRIM(TRAILING 0x000000 FROM _ucs2 0x0061)) 45562 4557SELECT CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _ucs2 0x0061)); 4558CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _ucs2 0x0061)) 45592 4560SELECT CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _ucs2 0x0061)); 4561CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _ucs2 0x0061)) 45621 4563SELECT CHAR_LENGTH(TRIM(BOTH 0x000000 FROM _ucs2 0x0061)); 4564CHAR_LENGTH(TRIM(BOTH 0x000000 FROM _ucs2 0x0061)) 45652 4566SELECT CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _ucs2 0x0061)); 4567CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _ucs2 0x0061)) 45682 4569SELECT CHAR_LENGTH(TRIM(BOTH 0x61 FROM _ucs2 0x0061)); 4570CHAR_LENGTH(TRIM(BOTH 0x61 FROM _ucs2 0x0061)) 45711 4572SELECT CHAR_LENGTH(TRIM(BOTH 0x00 FROM _ucs2 0x0061)); 4573CHAR_LENGTH(TRIM(BOTH 0x00 FROM _ucs2 0x0061)) 45741 4575# 4576# MDEV-11685: sql_mode can't be set with non-ascii connection charset 4577# 4578SET character_set_connection=ucs2; 4579SET sql_mode='NO_ENGINE_SUBSTITUTION'; 4580SELECT @@sql_mode; 4581@@sql_mode 4582NO_ENGINE_SUBSTITUTION 4583SET sql_mode=DEFAULT; 4584SET NAMES utf8; 4585# 4586# MDEV-13972 crash in Item_func_sec_to_time::get_date 4587# 4588SELECT SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)); 4589SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)) 4590838:59:59.999999 4591Warnings: 4592Warning 1292 Truncated incorrect seconds value: '77760000' 4593# 4594# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 4595# 4596set sql_mode=""; 4597CREATE TABLE t1 (c1 VARCHAR(32766) CHARACTER SET ucs2); 4598DESCRIBE t1; 4599Field Type Null Key Default Extra 4600c1 varchar(32766) YES NULL 4601DROP TABLE t1; 4602CREATE TABLE t1 (c1 VARCHAR(32767) CHARACTER SET ucs2); 4603Warnings: 4604Note 1246 Converting column 'c1' from VARCHAR to TEXT 4605DESCRIBE t1; 4606Field Type Null Key Default Extra 4607c1 text YES NULL 4608DROP TABLE t1; 4609CREATE TABLE t1 (c1 VARCHAR(32768) CHARACTER SET ucs2); 4610Warnings: 4611Note 1246 Converting column 'c1' from VARCHAR to TEXT 4612DESCRIBE t1; 4613Field Type Null Key Default Extra 4614c1 mediumtext YES NULL 4615DROP TABLE t1; 4616set sql_mode=default; 4617# 4618# MDEV-15624 Changing the default character set to utf8mb4 changes query evaluation in a very surprising way 4619# 4620SET NAMES utf8; 4621CREATE TABLE t1 (id INT); 4622INSERT INTO t1 VALUES (1),(2),(3); 4623SELECT COUNT(DISTINCT c) FROM (SELECT id, REPLACE(uuid_short(), '0', CAST('o' AS CHAR CHARACTER SET ucs2)) AS c FROM t1) AS d1; 4624COUNT(DISTINCT c) 46253 4626SELECT DISTINCT REPLACE(uuid_short(), '0', CAST('o' AS CHAR CHARACTER SET ucs2)) AS c FROM t1; 4627c 4628xxxxxxxxxxxxxxxxx 4629xxxxxxxxxxxxxxxxx 4630xxxxxxxxxxxxxxxxx 4631SELECT COUNT(DISTINCT c) FROM (SELECT id, INSERT(uuid_short(), 1, 1, CAST('0' AS CHAR CHARACTER SET ucs2)) AS c FROM t1) AS d1; 4632COUNT(DISTINCT c) 46333 4634SELECT DISTINCT INSERT(uuid_short(), 1, 1, CAST('0' AS CHAR CHARACTER SET ucs2)) AS c FROM t1; 4635c 4636xxxxxxxxxxxxxxxxx 4637xxxxxxxxxxxxxxxxx 4638xxxxxxxxxxxxxxxxx 4639SELECT COUNT(DISTINCT c) FROM (SELECT id, CONCAT(uuid_short(), CAST('0' AS CHAR CHARACTER SET ucs2)) AS c FROM t1) AS d1; 4640COUNT(DISTINCT c) 46413 4642SELECT DISTINCT CONCAT(uuid_short(), CAST('0' AS CHAR CHARACTER SET ucs2)) AS c FROM t1; 4643c 4644xxxxxxxxxxxxxxxxx 4645xxxxxxxxxxxxxxxxx 4646xxxxxxxxxxxxxxxxx 4647DROP TABLE t1; 4648# 4649# End of 5.5 tests 4650# 4651# 4652# Start of 5.6 tests 4653# 4654# 4655# WL#3664 WEIGHT_STRING 4656# 4657set collation_connection=ucs2_general_ci; 4658select @@collation_connection; 4659@@collation_connection 4660ucs2_general_ci 4661CREATE TABLE t1 AS SELECT 'a' AS a; 4662SHOW CREATE TABLE t1; 4663Table Create Table 4664t1 CREATE TABLE `t1` ( 4665 `a` varchar(1) CHARACTER SET ucs2 NOT NULL 4666) ENGINE=MyISAM DEFAULT CHARSET=latin1 4667CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; 4668SHOW CREATE TABLE t2; 4669Table Create Table 4670t2 CREATE TABLE `t2` ( 4671 `ws` varbinary(2) DEFAULT NULL 4672) ENGINE=MyISAM DEFAULT CHARSET=latin1 4673SELECT HEX(WEIGHT_STRING(a)) FROM t1; 4674HEX(WEIGHT_STRING(a)) 46750041 4676SELECT HEX(ws) FROM t2; 4677HEX(ws) 46780041 4679DROP TABLE t2; 4680DROP TABLE t1; 4681CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a; 4682SHOW CREATE TABLE t1; 4683Table Create Table 4684t1 CREATE TABLE `t1` ( 4685 `a` varchar(5) CHARACTER SET ucs2 DEFAULT NULL 4686) ENGINE=MyISAM DEFAULT CHARSET=latin1 4687CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; 4688SHOW CREATE TABLE t2; 4689Table Create Table 4690t2 CREATE TABLE `t2` ( 4691 `ws` varbinary(10) DEFAULT NULL 4692) ENGINE=MyISAM DEFAULT CHARSET=latin1 4693SELECT HEX(WEIGHT_STRING(a)) FROM t1; 4694HEX(WEIGHT_STRING(a)) 469500410041004100410041 4696SELECT HEX(ws) FROM t2; 4697HEX(ws) 469800410041004100410041 4699DROP TABLE t2; 4700CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1; 4701SHOW CREATE TABLE t2; 4702Table Create Table 4703t2 CREATE TABLE `t2` ( 4704 `ws` varbinary(6) DEFAULT NULL 4705) ENGINE=MyISAM DEFAULT CHARSET=latin1 4706SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1; 4707HEX(WEIGHT_STRING(a AS CHAR(3))) 4708004100410041 4709SELECT HEX(ws) FROM t2; 4710HEX(ws) 4711004100410041 4712DROP TABLE t2; 4713CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1; 4714SHOW CREATE TABLE t2; 4715Table Create Table 4716t2 CREATE TABLE `t2` ( 4717 `ws` varbinary(20) DEFAULT NULL 4718) ENGINE=MyISAM DEFAULT CHARSET=latin1 4719SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 4720HEX(WEIGHT_STRING(a AS CHAR(10))) 47210041004100410041004100200020002000200020 4722SELECT HEX(ws) FROM t2; 4723HEX(ws) 47240041004100410041004100200020002000200020 4725DROP TABLE t2; 4726DROP TABLE t1; 4727select hex(weight_string('a')); 4728hex(weight_string('a')) 47290041 4730select hex(weight_string('A')); 4731hex(weight_string('A')) 47320041 4733select hex(weight_string('abc')); 4734hex(weight_string('abc')) 4735004100420043 4736select hex(weight_string('abc' as char(2))); 4737hex(weight_string('abc' as char(2))) 473800410042 4739select hex(weight_string('abc' as char(3))); 4740hex(weight_string('abc' as char(3))) 4741004100420043 4742select hex(weight_string('abc' as char(5))); 4743hex(weight_string('abc' as char(5))) 474400410042004300200020 4745select hex(weight_string('abc', 1, 2, 0xC0)); 4746hex(weight_string('abc', 1, 2, 0xC0)) 474700 4748select hex(weight_string('abc', 2, 2, 0xC0)); 4749hex(weight_string('abc', 2, 2, 0xC0)) 47500041 4751select hex(weight_string('abc', 3, 2, 0xC0)); 4752hex(weight_string('abc', 3, 2, 0xC0)) 4753004100 4754select hex(weight_string('abc', 4, 2, 0xC0)); 4755hex(weight_string('abc', 4, 2, 0xC0)) 475600410042 4757select hex(weight_string('abc', 5, 2, 0xC0)); 4758hex(weight_string('abc', 5, 2, 0xC0)) 47590041004200 4760select hex(weight_string('abc',25, 2, 0xC0)); 4761hex(weight_string('abc',25, 2, 0xC0)) 476200410042002000200020002000200020002000200020002000 4763select hex(weight_string('abc', 1, 3, 0xC0)); 4764hex(weight_string('abc', 1, 3, 0xC0)) 476500 4766select hex(weight_string('abc', 2, 3, 0xC0)); 4767hex(weight_string('abc', 2, 3, 0xC0)) 47680041 4769select hex(weight_string('abc', 3, 3, 0xC0)); 4770hex(weight_string('abc', 3, 3, 0xC0)) 4771004100 4772select hex(weight_string('abc', 4, 3, 0xC0)); 4773hex(weight_string('abc', 4, 3, 0xC0)) 477400410042 4775select hex(weight_string('abc', 5, 3, 0xC0)); 4776hex(weight_string('abc', 5, 3, 0xC0)) 47770041004200 4778select hex(weight_string('abc',25, 3, 0xC0)); 4779hex(weight_string('abc',25, 3, 0xC0)) 478000410042004300200020002000200020002000200020002000 4781select hex(weight_string('abc', 1, 4, 0xC0)); 4782hex(weight_string('abc', 1, 4, 0xC0)) 478300 4784select hex(weight_string('abc', 2, 4, 0xC0)); 4785hex(weight_string('abc', 2, 4, 0xC0)) 47860041 4787select hex(weight_string('abc', 3, 4, 0xC0)); 4788hex(weight_string('abc', 3, 4, 0xC0)) 4789004100 4790select hex(weight_string('abc', 4, 4, 0xC0)); 4791hex(weight_string('abc', 4, 4, 0xC0)) 479200410042 4793select hex(weight_string('abc', 5, 4, 0xC0)); 4794hex(weight_string('abc', 5, 4, 0xC0)) 47950041004200 4796select hex(weight_string('abc',25, 4, 0xC0)); 4797hex(weight_string('abc',25, 4, 0xC0)) 479800410042004300200020002000200020002000200020002000 4799select @@collation_connection; 4800@@collation_connection 4801ucs2_general_ci 4802select hex(weight_string(cast(_latin1 0x80 as char))); 4803hex(weight_string(cast(_latin1 0x80 as char))) 480420AC 4805select hex(weight_string(cast(_latin1 0x808080 as char))); 4806hex(weight_string(cast(_latin1 0x808080 as char))) 480720AC20AC20AC 4808select hex(weight_string(cast(_latin1 0x808080 as char) as char(2))); 4809hex(weight_string(cast(_latin1 0x808080 as char) as char(2))) 481020AC20AC 4811select hex(weight_string(cast(_latin1 0x808080 as char) as char(3))); 4812hex(weight_string(cast(_latin1 0x808080 as char) as char(3))) 481320AC20AC20AC 4814select hex(weight_string(cast(_latin1 0x808080 as char) as char(5))); 4815hex(weight_string(cast(_latin1 0x808080 as char) as char(5))) 481620AC20AC20AC00200020 4817select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)); 4818hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)) 481920 4820select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)); 4821hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)) 482220AC 4823select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)); 4824hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)) 482520AC20 4826select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)); 4827hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)) 482820AC20AC 4829select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)); 4830hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)) 483120AC20AC00 4832select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)); 4833hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)) 483420AC20AC002000200020002000200020002000200020002000 4835select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)); 4836hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)) 483720 4838select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)); 4839hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)) 484020AC 4841select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)); 4842hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)) 484320AC20 4844select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)); 4845hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)) 484620AC20AC 4847select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)); 4848hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)) 484920AC20AC20 4850select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)); 4851hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)) 485220AC20AC20AC00200020002000200020002000200020002000 4853select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)); 4854hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)) 485520 4856select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)); 4857hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)) 485820AC 4859select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)); 4860hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)) 486120AC20 4862select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)); 4863hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)) 486420AC20AC 4865select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)); 4866hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)) 486720AC20AC20 4868select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)); 4869hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)) 487020AC20AC20AC00200020002000200020002000200020002000 4871select @@collation_connection; 4872@@collation_connection 4873ucs2_general_ci 4874select hex(weight_string('a' LEVEL 1)); 4875hex(weight_string('a' LEVEL 1)) 48760041 4877select hex(weight_string('A' LEVEL 1)); 4878hex(weight_string('A' LEVEL 1)) 48790041 4880select hex(weight_string('abc' LEVEL 1)); 4881hex(weight_string('abc' LEVEL 1)) 4882004100420043 4883select hex(weight_string('abc' as char(2) LEVEL 1)); 4884hex(weight_string('abc' as char(2) LEVEL 1)) 488500410042 4886select hex(weight_string('abc' as char(3) LEVEL 1)); 4887hex(weight_string('abc' as char(3) LEVEL 1)) 4888004100420043 4889select hex(weight_string('abc' as char(5) LEVEL 1)); 4890hex(weight_string('abc' as char(5) LEVEL 1)) 489100410042004300200020 4892select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)); 4893hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)) 489420002000430042004100 4895select hex(weight_string('abc' as char(5) LEVEL 1 DESC)); 4896hex(weight_string('abc' as char(5) LEVEL 1 DESC)) 4897FFBEFFBDFFBCFFDFFFDF 4898select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)); 4899hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)) 4900DFFFDFFFBCFFBDFFBEFF 4901set collation_connection=ucs2_bin; 4902select @@collation_connection; 4903@@collation_connection 4904ucs2_bin 4905CREATE TABLE t1 AS SELECT 'a' AS a; 4906SHOW CREATE TABLE t1; 4907Table Create Table 4908t1 CREATE TABLE `t1` ( 4909 `a` varchar(1) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL 4910) ENGINE=MyISAM DEFAULT CHARSET=latin1 4911CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; 4912SHOW CREATE TABLE t2; 4913Table Create Table 4914t2 CREATE TABLE `t2` ( 4915 `ws` varbinary(2) DEFAULT NULL 4916) ENGINE=MyISAM DEFAULT CHARSET=latin1 4917SELECT HEX(WEIGHT_STRING(a)) FROM t1; 4918HEX(WEIGHT_STRING(a)) 49190061 4920SELECT HEX(ws) FROM t2; 4921HEX(ws) 49220061 4923DROP TABLE t2; 4924DROP TABLE t1; 4925CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a; 4926SHOW CREATE TABLE t1; 4927Table Create Table 4928t1 CREATE TABLE `t1` ( 4929 `a` varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin DEFAULT NULL 4930) ENGINE=MyISAM DEFAULT CHARSET=latin1 4931CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; 4932SHOW CREATE TABLE t2; 4933Table Create Table 4934t2 CREATE TABLE `t2` ( 4935 `ws` varbinary(10) DEFAULT NULL 4936) ENGINE=MyISAM DEFAULT CHARSET=latin1 4937SELECT HEX(WEIGHT_STRING(a)) FROM t1; 4938HEX(WEIGHT_STRING(a)) 493900610061006100610061 4940SELECT HEX(ws) FROM t2; 4941HEX(ws) 494200610061006100610061 4943DROP TABLE t2; 4944CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1; 4945SHOW CREATE TABLE t2; 4946Table Create Table 4947t2 CREATE TABLE `t2` ( 4948 `ws` varbinary(6) DEFAULT NULL 4949) ENGINE=MyISAM DEFAULT CHARSET=latin1 4950SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1; 4951HEX(WEIGHT_STRING(a AS CHAR(3))) 4952006100610061 4953SELECT HEX(ws) FROM t2; 4954HEX(ws) 4955006100610061 4956DROP TABLE t2; 4957CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1; 4958SHOW CREATE TABLE t2; 4959Table Create Table 4960t2 CREATE TABLE `t2` ( 4961 `ws` varbinary(20) DEFAULT NULL 4962) ENGINE=MyISAM DEFAULT CHARSET=latin1 4963SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 4964HEX(WEIGHT_STRING(a AS CHAR(10))) 49650061006100610061006100200020002000200020 4966SELECT HEX(ws) FROM t2; 4967HEX(ws) 49680061006100610061006100200020002000200020 4969DROP TABLE t2; 4970DROP TABLE t1; 4971select hex(weight_string('a')); 4972hex(weight_string('a')) 49730061 4974select hex(weight_string('A')); 4975hex(weight_string('A')) 49760041 4977select hex(weight_string('abc')); 4978hex(weight_string('abc')) 4979006100620063 4980select hex(weight_string('abc' as char(2))); 4981hex(weight_string('abc' as char(2))) 498200610062 4983select hex(weight_string('abc' as char(3))); 4984hex(weight_string('abc' as char(3))) 4985006100620063 4986select hex(weight_string('abc' as char(5))); 4987hex(weight_string('abc' as char(5))) 498800610062006300200020 4989select hex(weight_string('abc', 1, 2, 0xC0)); 4990hex(weight_string('abc', 1, 2, 0xC0)) 499100 4992select hex(weight_string('abc', 2, 2, 0xC0)); 4993hex(weight_string('abc', 2, 2, 0xC0)) 49940061 4995select hex(weight_string('abc', 3, 2, 0xC0)); 4996hex(weight_string('abc', 3, 2, 0xC0)) 4997006100 4998select hex(weight_string('abc', 4, 2, 0xC0)); 4999hex(weight_string('abc', 4, 2, 0xC0)) 500000610062 5001select hex(weight_string('abc', 5, 2, 0xC0)); 5002hex(weight_string('abc', 5, 2, 0xC0)) 50030061006200 5004select hex(weight_string('abc',25, 2, 0xC0)); 5005hex(weight_string('abc',25, 2, 0xC0)) 500600610062002000200020002000200020002000200020002000 5007select hex(weight_string('abc', 1, 3, 0xC0)); 5008hex(weight_string('abc', 1, 3, 0xC0)) 500900 5010select hex(weight_string('abc', 2, 3, 0xC0)); 5011hex(weight_string('abc', 2, 3, 0xC0)) 50120061 5013select hex(weight_string('abc', 3, 3, 0xC0)); 5014hex(weight_string('abc', 3, 3, 0xC0)) 5015006100 5016select hex(weight_string('abc', 4, 3, 0xC0)); 5017hex(weight_string('abc', 4, 3, 0xC0)) 501800610062 5019select hex(weight_string('abc', 5, 3, 0xC0)); 5020hex(weight_string('abc', 5, 3, 0xC0)) 50210061006200 5022select hex(weight_string('abc',25, 3, 0xC0)); 5023hex(weight_string('abc',25, 3, 0xC0)) 502400610062006300200020002000200020002000200020002000 5025select hex(weight_string('abc', 1, 4, 0xC0)); 5026hex(weight_string('abc', 1, 4, 0xC0)) 502700 5028select hex(weight_string('abc', 2, 4, 0xC0)); 5029hex(weight_string('abc', 2, 4, 0xC0)) 50300061 5031select hex(weight_string('abc', 3, 4, 0xC0)); 5032hex(weight_string('abc', 3, 4, 0xC0)) 5033006100 5034select hex(weight_string('abc', 4, 4, 0xC0)); 5035hex(weight_string('abc', 4, 4, 0xC0)) 503600610062 5037select hex(weight_string('abc', 5, 4, 0xC0)); 5038hex(weight_string('abc', 5, 4, 0xC0)) 50390061006200 5040select hex(weight_string('abc',25, 4, 0xC0)); 5041hex(weight_string('abc',25, 4, 0xC0)) 504200610062006300200020002000200020002000200020002000 5043select @@collation_connection; 5044@@collation_connection 5045ucs2_bin 5046select hex(weight_string(cast(_latin1 0x80 as char))); 5047hex(weight_string(cast(_latin1 0x80 as char))) 504820AC 5049select hex(weight_string(cast(_latin1 0x808080 as char))); 5050hex(weight_string(cast(_latin1 0x808080 as char))) 505120AC20AC20AC 5052select hex(weight_string(cast(_latin1 0x808080 as char) as char(2))); 5053hex(weight_string(cast(_latin1 0x808080 as char) as char(2))) 505420AC20AC 5055select hex(weight_string(cast(_latin1 0x808080 as char) as char(3))); 5056hex(weight_string(cast(_latin1 0x808080 as char) as char(3))) 505720AC20AC20AC 5058select hex(weight_string(cast(_latin1 0x808080 as char) as char(5))); 5059hex(weight_string(cast(_latin1 0x808080 as char) as char(5))) 506020AC20AC20AC00200020 5061select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)); 5062hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)) 506320 5064select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)); 5065hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)) 506620AC 5067select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)); 5068hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)) 506920AC20 5070select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)); 5071hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)) 507220AC20AC 5073select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)); 5074hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)) 507520AC20AC00 5076select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)); 5077hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)) 507820AC20AC002000200020002000200020002000200020002000 5079select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)); 5080hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)) 508120 5082select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)); 5083hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)) 508420AC 5085select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)); 5086hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)) 508720AC20 5088select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)); 5089hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)) 509020AC20AC 5091select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)); 5092hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)) 509320AC20AC20 5094select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)); 5095hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)) 509620AC20AC20AC00200020002000200020002000200020002000 5097select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)); 5098hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)) 509920 5100select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)); 5101hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)) 510220AC 5103select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)); 5104hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)) 510520AC20 5106select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)); 5107hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)) 510820AC20AC 5109select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)); 5110hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)) 511120AC20AC20 5112select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)); 5113hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)) 511420AC20AC20AC00200020002000200020002000200020002000 5115select @@collation_connection; 5116@@collation_connection 5117ucs2_bin 5118select hex(weight_string('a' LEVEL 1)); 5119hex(weight_string('a' LEVEL 1)) 51200061 5121select hex(weight_string('A' LEVEL 1)); 5122hex(weight_string('A' LEVEL 1)) 51230041 5124select hex(weight_string('abc' LEVEL 1)); 5125hex(weight_string('abc' LEVEL 1)) 5126006100620063 5127select hex(weight_string('abc' as char(2) LEVEL 1)); 5128hex(weight_string('abc' as char(2) LEVEL 1)) 512900610062 5130select hex(weight_string('abc' as char(3) LEVEL 1)); 5131hex(weight_string('abc' as char(3) LEVEL 1)) 5132006100620063 5133select hex(weight_string('abc' as char(5) LEVEL 1)); 5134hex(weight_string('abc' as char(5) LEVEL 1)) 513500610062006300200020 5136select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)); 5137hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)) 513820002000630062006100 5139select hex(weight_string('abc' as char(5) LEVEL 1 DESC)); 5140hex(weight_string('abc' as char(5) LEVEL 1 DESC)) 5141FF9EFF9DFF9CFFDFFFDF 5142select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)); 5143hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)) 5144DFFFDFFF9CFF9DFF9EFF 5145# 5146# Bug #36418 Character sets: crash if char(256 using utf32) 5147# 5148select hex(char(0x01 using ucs2)); 5149hex(char(0x01 using ucs2)) 51500001 5151select hex(char(0x0102 using ucs2)); 5152hex(char(0x0102 using ucs2)) 51530102 5154select hex(char(0x010203 using ucs2)); 5155hex(char(0x010203 using ucs2)) 515600010203 5157select hex(char(0x01020304 using ucs2)); 5158hex(char(0x01020304 using ucs2)) 515901020304 5160# 5161# Bug#10094 Displays wrong error message for UNIQUE key index on CHAR(255) Unicode datatype 5162# 5163CREATE TABLE t1 (f1 CHAR(255) unicode); 5164INSERT INTO t1 values ('abc'),('bcd'),('abc'); 5165ALTER TABLE t1 ADD UNIQUE Index_1 (f1); 5166ERROR 23000: Duplicate entry 'abc' for key 'Index_1' 5167DROP TABLE t1; 5168# 5169# Test how character set works with date/time 5170# 5171SET collation_connection=ucs2_general_ci; 5172# 5173# Bug#32390 Character sets: casting utf32 to/from date doesn't work 5174# 5175CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0; 5176SET time_zone=_latin1'+03:00'; 5177SET timestamp=1216359724; 5178INSERT INTO t1 VALUES (current_date); 5179INSERT INTO t1 VALUES (current_time); 5180INSERT INTO t1 VALUES (current_timestamp); 5181SELECT s1, hex(s1) FROM t1; 5182s1 hex(s1) 51832008-07-18 0032003000300038002D00300037002D00310038 518408:42:04 00300038003A00340032003A00300034 51852008-07-18 08:42:04 0032003000300038002D00300037002D00310038002000300038003A00340032003A00300034 5186DROP TABLE t1; 5187SET timestamp=0; 5188SET time_zone=default; 5189# 5190# MDEV-5298 Illegal mix of collations on timestamp 5191# 5192SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY); 5193CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY) 5194ucs2 5195SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY); 5196COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY) 51974 5198SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY); 5199CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY) 5200binary 5201SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY); 5202COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY) 52035 5204SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)); 5205CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)) 5206ucs2 5207SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)); 5208COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)) 52094 5210SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)); 5211CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)) 5212ucs2 5213SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)); 5214COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)) 52154 5216SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); 5217CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) 5218ucs2 5219SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); 5220COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) 52214 5222SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); 5223HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) 52240032003000300031002D00300031002D00300031002000300030003A00300030003A00300030 5225SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); 5226CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 5227ucs2 5228SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); 5229COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 52304 5231SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); 5232HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 52330032003000300031002D00300031002D00300031002000300030003A00300030003A00300030 5234CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0; 5235SHOW CREATE TABLE t1; 5236Table Create Table 5237t1 CREATE TABLE `t1` ( 5238 `a` varchar(64) CHARACTER SET ucs2 DEFAULT NULL 5239) ENGINE=MyISAM DEFAULT CHARSET=latin1 5240INSERT INTO t1 VALUES (''); 5241SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 5242CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 5243ucs2 5244SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 5245COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 52462 5247SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 5248HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 52490032003000300031002D00300031002D00300031002000300030003A00300030003A00300030 5250SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 5251CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 5252ucs2 5253SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 5254COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 52552 5256SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 5257HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 52580032003000300031002D00300031002D00300031002000300030003A00300030003A00300030 5259DROP TABLE t1; 5260CREATE TABLE t1 (t TIMESTAMP NOT NULL); 5261INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); 5262SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY; 5263t 52642001-01-01 00:00:00 5265SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY; 5266t 52672001-01-01 00:00:00 5268SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY; 5269t 52702001-01-01 00:00:00 5271SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY; 5272t 52732001-01-01 00:00:00 5274SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY; 5275t 52762001-01-01 00:00:00 5277SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY; 5278t 52792001-01-01 00:00:00 5280DROP TABLE t1; 5281SET NAMES latin1; 5282# 5283# WL#4013 Unicode german2 collation 5284# 5285SET collation_connection=ucs2_german2_ci; 5286"BEGIN ctype_german.inc" 5287drop table if exists t1; 5288create table t1 as select repeat(' ', 64) as s1; 5289select collation(s1) from t1; 5290collation(s1) 5291ucs2_german2_ci 5292delete from t1; 5293INSERT INTO t1 VALUES ('ud'),('uf'); 5294INSERT INTO t1 VALUES ('od'),('of'); 5295INSERT INTO t1 VALUES ('e'); 5296INSERT INTO t1 VALUES ('ad'),('af'); 5297insert into t1 values ('a'),('ae'),(_latin1 0xE4); 5298insert into t1 values ('o'),('oe'),(_latin1 0xF6); 5299insert into t1 values ('s'),('ss'),(_latin1 0xDF); 5300insert into t1 values ('u'),('ue'),(_latin1 0xFC); 5301INSERT INTO t1 VALUES (_latin1 0xE6), (_latin1 0xC6); 5302INSERT INTO t1 VALUES (_latin1 0x9C), (_latin1 0x8C); 5303select s1, hex(s1) from t1 order by s1, binary s1; 5304s1 hex(s1) 5305a 0061 5306ad 00610064 5307ae 00610065 5308� 00C6 5309� 00E4 5310� 00E6 5311af 00610066 5312e 0065 5313o 006F 5314od 006F0064 5315oe 006F0065 5316� 00F6 5317� 0152 5318� 0153 5319of 006F0066 5320s 0073 5321ss 00730073 5322� 00DF 5323u 0075 5324ud 00750064 5325ue 00750065 5326� 00FC 5327uf 00750066 5328select group_concat(s1 order by binary s1) from t1 group by s1; 5329group_concat(s1 order by binary s1) 5330a 5331ad 5332ae,�,�,� 5333af 5334e 5335o 5336od 5337oe,�,�,� 5338of 5339s 5340ss,� 5341u 5342ud 5343ue,� 5344uf 5345SELECT s1, hex(s1), hex(weight_string(s1)) FROM t1 ORDER BY s1, BINARY(s1); 5346s1 hex(s1) hex(weight_string(s1)) 5347a 0061 0E33 5348ad 00610064 0E330E6D 5349ae 00610065 0E330E8B 5350� 00C6 0E330E8B 5351� 00E4 0E330E8B 5352� 00E6 0E330E8B 5353af 00610066 0E330EB9 5354e 0065 0E8B 5355o 006F 0F82 5356od 006F0064 0F820E6D 5357oe 006F0065 0F820E8B 5358� 00F6 0F820E8B 5359� 0152 0F820E8B 5360� 0153 0F820E8B 5361of 006F0066 0F820EB9 5362s 0073 0FEA 5363ss 00730073 0FEA0FEA 5364� 00DF 0FEA0FEA 5365u 0075 101F 5366ud 00750064 101F0E6D 5367ue 00750065 101F0E8B 5368� 00FC 101F0E8B 5369uf 00750066 101F0EB9 5370SELECT s1, hex(s1) FROM t1 WHERE s1='ae' ORDER BY s1, BINARY(s1); 5371s1 hex(s1) 5372ae 00610065 5373� 00C6 5374� 00E4 5375� 00E6 5376drop table t1; 5377CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a, 1 AS b LIMIT 0; 5378SHOW CREATE TABLE t1; 5379Table Create Table 5380t1 CREATE TABLE `t1` ( 5381 `a` varchar(1) CHARACTER SET ucs2 COLLATE ucs2_german2_ci DEFAULT NULL, 5382 `b` int(1) NOT NULL 5383) ENGINE=MyISAM DEFAULT CHARSET=latin1 5384INSERT INTO t1 VALUES ('s',0),(_latin1 0xDF,1); 5385SELECT * FROM t1 ORDER BY a, b; 5386a b 5387s 0 5388� 1 5389SELECT * FROM t1 ORDER BY a DESC, b; 5390a b 5391� 1 5392s 0 5393SELECT * FROM t1 ORDER BY CONCAT(a), b; 5394a b 5395s 0 5396� 1 5397SELECT * FROM t1 ORDER BY CONCAT(a) DESC, b; 5398a b 5399� 1 5400s 0 5401DROP TABLE t1; 5402"END ctype_german.inc" 5403# 5404# Bug#59145 valgrind warnings for uninitialized values in my_strtoll10_mb2 5405# 5406SET NAMES latin1; 5407SELECT CONVERT(CHAR(NULL USING ucs2), UNSIGNED); 5408CONVERT(CHAR(NULL USING ucs2), UNSIGNED) 54090 5410Warnings: 5411Warning 1292 Truncated incorrect INTEGER value: '' 5412DO IFNULL(CHAR(NULL USING ucs2), ''); 5413DO CAST(CONVERT('' USING ucs2) AS UNSIGNED); 5414Warnings: 5415Warning 1292 Truncated incorrect INTEGER value: '' 5416# 5417# Test error message for conversion using different charset 5418# 5419CREATE TABLE t1 (a DECIMAL(2,0)); 5420SET sql_mode='strict_all_tables'; 5421INSERT INTO t1 VALUES (CONVERT('9e99999999' USING ucs2)); 5422ERROR 22003: Out of range value for column 'a' at row 1 5423SET sql_mode=DEFAULT; 5424INSERT IGNORE INTO t1 VALUES (CONVERT('aaa' USING ucs2)); 5425Warnings: 5426Warning 1366 Incorrect decimal value: 'aaa' for column `test`.`t1`.`a` at row 1 5427DROP TABLE t1; 5428# 5429# End of 5.6 tests 5430# 5431# 5432# Start of 10.0 tests 5433# 5434SET NAMES latin1, collation_connection=ucs2_bin; 5435# 5436# MDEV-7149 Constant condition propagation erroneously applied for LIKE 5437# 5438CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 5439SHOW CREATE TABLE t1; 5440Table Create Table 5441t1 CREATE TABLE `t1` ( 5442 `c1` varchar(10) CHARACTER SET ucs2 COLLATE ucs2_bin DEFAULT NULL 5443) ENGINE=MyISAM DEFAULT CHARSET=latin1 5444INSERT INTO t1 VALUES ('a'),('a '); 5445SELECT * FROM t1 WHERE CONCAT(c1)='a'; 5446c1 5447a 5448a 5449SELECT * FROM t1 WHERE CONCAT(c1) LIKE 'a '; 5450c1 5451a 5452SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a '; 5453c1 5454a 5455EXPLAIN EXTENDED SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a '; 5456id select_type table type possible_keys key key_len ref rows filtered Extra 54571 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5458Warnings: 5459Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where concat(`test`.`t1`.`c1`) = 'a' and concat(`test`.`t1`.`c1`) like 'a ' 5460DROP TABLE t1; 5461CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 5462SHOW CREATE TABLE t1; 5463Table Create Table 5464t1 CREATE TABLE `t1` ( 5465 `c1` varchar(10) CHARACTER SET ucs2 COLLATE ucs2_bin DEFAULT NULL 5466) ENGINE=MyISAM DEFAULT CHARSET=latin1 5467INSERT INTO t1 VALUES ('a'),('a '); 5468SELECT * FROM t1 WHERE 'a'=CONCAT(c1); 5469c1 5470a 5471a 5472SELECT * FROM t1 WHERE 'a ' LIKE CONCAT(c1); 5473c1 5474a 5475SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1); 5476c1 5477a 5478EXPLAIN EXTENDED SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1); 5479id select_type table type possible_keys key key_len ref rows filtered Extra 54801 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5481Warnings: 5482Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 'a' = concat(`test`.`t1`.`c1`) and 'a ' like concat(`test`.`t1`.`c1`) 5483DROP TABLE t1; 5484CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 5485SHOW CREATE TABLE t1; 5486Table Create Table 5487t1 CREATE TABLE `t1` ( 5488 `c1` varchar(10) CHARACTER SET ucs2 COLLATE ucs2_bin DEFAULT NULL 5489) ENGINE=MyISAM DEFAULT CHARSET=latin1 5490INSERT INTO t1 VALUES ('%'),('% '); 5491SELECT * FROM t1 WHERE '% '=CONCAT(c1); 5492c1 5493% 5494% 5495SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1); 5496c1 5497% 5498SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 5499c1 5500% 5501EXPLAIN EXTENDED SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 5502id select_type table type possible_keys key key_len ref rows filtered Extra 55031 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5504Warnings: 5505Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where '% ' = concat(`test`.`t1`.`c1`) and 'a' like concat(`test`.`t1`.`c1`) 5506DROP TABLE t1; 5507CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 5508SHOW CREATE TABLE t1; 5509Table Create Table 5510t1 CREATE TABLE `t1` ( 5511 `c1` varchar(10) CHARACTER SET ucs2 COLLATE ucs2_bin DEFAULT NULL 5512) ENGINE=MyISAM DEFAULT CHARSET=latin1 5513INSERT INTO t1 VALUES ('%'),('% '); 5514SELECT * FROM t1 WHERE '%'=CONCAT(c1); 5515c1 5516% 5517% 5518SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1); 5519c1 5520% 5521SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 5522c1 5523% 5524EXPLAIN EXTENDED SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 5525id select_type table type possible_keys key key_len ref rows filtered Extra 55261 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5527Warnings: 5528Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where '%' = concat(`test`.`t1`.`c1`) and 'a' like concat(`test`.`t1`.`c1`) 5529DROP TABLE t1; 5530# 5531# MDEV-8694 Wrong result for SELECT..WHERE a NOT LIKE 'a ' AND a='a' 5532# 5533CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0; 5534SHOW CREATE TABLE t1; 5535Table Create Table 5536t1 CREATE TABLE `t1` ( 5537 `a` varchar(10) CHARACTER SET ucs2 COLLATE ucs2_bin DEFAULT NULL 5538) ENGINE=MyISAM DEFAULT CHARSET=latin1 5539INSERT INTO t1 VALUES ('a'),('a '); 5540SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a '; 5541a LENGTH(a) 5542a 2 5543SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; 5544a LENGTH(a) 5545a 2 5546EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; 5547id select_type table type possible_keys key key_len ref rows filtered Extra 55481 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5549Warnings: 5550Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' 5551DROP TABLE t1; 5552# 5553# End of MDEV-8694 5554# 5555SET NAMES latin1, collation_connection=ucs2_general_ci; 5556# 5557# MDEV-7149 Constant condition propagation erroneously applied for LIKE 5558# 5559CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 5560SHOW CREATE TABLE t1; 5561Table Create Table 5562t1 CREATE TABLE `t1` ( 5563 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 5564) ENGINE=MyISAM DEFAULT CHARSET=latin1 5565INSERT INTO t1 VALUES ('a'),('a '); 5566SELECT * FROM t1 WHERE CONCAT(c1)='a'; 5567c1 5568a 5569a 5570SELECT * FROM t1 WHERE CONCAT(c1) LIKE 'a '; 5571c1 5572a 5573SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a '; 5574c1 5575a 5576EXPLAIN EXTENDED SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a '; 5577id select_type table type possible_keys key key_len ref rows filtered Extra 55781 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5579Warnings: 5580Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where concat(`test`.`t1`.`c1`) = 'a' and concat(`test`.`t1`.`c1`) like 'a ' 5581DROP TABLE t1; 5582CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 5583SHOW CREATE TABLE t1; 5584Table Create Table 5585t1 CREATE TABLE `t1` ( 5586 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 5587) ENGINE=MyISAM DEFAULT CHARSET=latin1 5588INSERT INTO t1 VALUES ('a'),('a '); 5589SELECT * FROM t1 WHERE 'a'=CONCAT(c1); 5590c1 5591a 5592a 5593SELECT * FROM t1 WHERE 'a ' LIKE CONCAT(c1); 5594c1 5595a 5596SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1); 5597c1 5598a 5599EXPLAIN EXTENDED SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1); 5600id select_type table type possible_keys key key_len ref rows filtered Extra 56011 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5602Warnings: 5603Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 'a' = concat(`test`.`t1`.`c1`) and 'a ' like concat(`test`.`t1`.`c1`) 5604DROP TABLE t1; 5605CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 5606SHOW CREATE TABLE t1; 5607Table Create Table 5608t1 CREATE TABLE `t1` ( 5609 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 5610) ENGINE=MyISAM DEFAULT CHARSET=latin1 5611INSERT INTO t1 VALUES ('%'),('% '); 5612SELECT * FROM t1 WHERE '% '=CONCAT(c1); 5613c1 5614% 5615% 5616SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1); 5617c1 5618% 5619SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 5620c1 5621% 5622EXPLAIN EXTENDED SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 5623id select_type table type possible_keys key key_len ref rows filtered Extra 56241 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5625Warnings: 5626Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where '% ' = concat(`test`.`t1`.`c1`) and 'a' like concat(`test`.`t1`.`c1`) 5627DROP TABLE t1; 5628CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 5629SHOW CREATE TABLE t1; 5630Table Create Table 5631t1 CREATE TABLE `t1` ( 5632 `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 5633) ENGINE=MyISAM DEFAULT CHARSET=latin1 5634INSERT INTO t1 VALUES ('%'),('% '); 5635SELECT * FROM t1 WHERE '%'=CONCAT(c1); 5636c1 5637% 5638% 5639SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1); 5640c1 5641% 5642SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 5643c1 5644% 5645EXPLAIN EXTENDED SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 5646id select_type table type possible_keys key key_len ref rows filtered Extra 56471 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5648Warnings: 5649Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where '%' = concat(`test`.`t1`.`c1`) and 'a' like concat(`test`.`t1`.`c1`) 5650DROP TABLE t1; 5651# 5652# MDEV-8694 Wrong result for SELECT..WHERE a NOT LIKE 'a ' AND a='a' 5653# 5654CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0; 5655SHOW CREATE TABLE t1; 5656Table Create Table 5657t1 CREATE TABLE `t1` ( 5658 `a` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 5659) ENGINE=MyISAM DEFAULT CHARSET=latin1 5660INSERT INTO t1 VALUES ('a'),('a '); 5661SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a '; 5662a LENGTH(a) 5663a 2 5664SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; 5665a LENGTH(a) 5666a 2 5667EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; 5668id select_type table type possible_keys key key_len ref rows filtered Extra 56691 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5670Warnings: 5671Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' 5672DROP TABLE t1; 5673# 5674# End of MDEV-8694 5675# 5676SET NAMES latin1; 5677# 5678# MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context 5679# 5680SELECT CONCAT(CONVERT('pi=' USING ucs2),PI()) AS PI; 5681PI 5682pi=3.141593 5683# 5684# MDEV-6695 Bad column name for UCS2 string literals 5685# 5686SET NAMES utf8, character_set_connection=ucs2; 5687SELECT 'a','aa'; 5688a aa 5689a aa 5690# 5691# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery 5692# 5693SET NAMES utf8, character_set_connection=ucs2; 5694SET @save_optimizer_switch=@@optimizer_switch; 5695SET optimizer_switch=_utf8'derived_merge=on'; 5696CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); 5697INSERT INTO t1 VALUES('abcdefghi'); 5698SET NAMES utf8, character_set_connection=ucs2; 5699SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; 5700c2 5701616263646566676869-616263646566676869 5702SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; 5703c2 5704YWJjZGVmZ2hp-YWJjZGVmZ2hp 5705DROP TABLE t1; 5706SET optimizer_switch=@save_optimizer_switch; 5707SET NAMES utf8, character_set_connection=ucs2; 5708# 5709# MDEV-13118 Wrong results with LOWER and UPPER and subquery 5710# 5711SET @save_optimizer_switch=@@optimizer_switch; 5712SET optimizer_switch=_latin1'derived_merge=on'; 5713CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; 5714SHOW CREATE TABLE t1; 5715Table Create Table 5716t1 CREATE TABLE `t1` ( 5717 `t` varchar(10) CHARACTER SET ucs2 DEFAULT NULL 5718) ENGINE=MyISAM DEFAULT CHARSET=latin1 5719INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); 5720SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; 5721c2 5722abcdefghi-abcdefghi 5723abcdefghi-abcdefghi 5724SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; 5725c2 5726ABCDEFGHI-ABCDEFGHI 5727ABCDEFGHI-ABCDEFGHI 5728DROP TABLE t1; 5729SET optimizer_switch=@save_optimizer_switch; 5730# 5731# End of 10.0 tests 5732# 5733select collation(cast("a" as char(10) unicode binary)); 5734collation(cast("a" as char(10) unicode binary)) 5735ucs2_bin 5736select collation(cast("a" as char(10) binary unicode)); 5737collation(cast("a" as char(10) binary unicode)) 5738ucs2_bin 5739# 5740# MDEV-8222 "string_field LIKE int_const" returns a wrong result in case of UCS2 5741# 5742CREATE TABLE t1 (a VARCHAR(10) CHARSET ucs2); 5743INSERT INTO t1 VALUES ('1'); 5744SELECT * FROM t1 WHERE a LIKE 1; 5745a 57461 5747DROP TABLE t1; 5748# 5749# MDEV-8253 EXPLAIN SELECT prints unexpected characters 5750# 5751SET NAMES latin1, character_set_connection=ucs2; 5752CREATE TABLE t1 (a DECIMAL(10,1),b DECIMAL(10,1),c VARCHAR(10),d VARCHAR(10)); 5753INSERT INTO t1 VALUES (1.5,1.5,'1','1'),(3.5,3.5,'3','3'); 5754EXPLAIN EXTENDED 5755SELECT * FROM t1 WHERE COALESCE(c,0)='3 ' AND COALESCE(d,0)=COALESCE(c,0); 5756id select_type table type possible_keys key key_len ref rows filtered Extra 57571 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5758Warnings: 5759Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where coalesce(`test`.`t1`.`c`,0) = '3 ' and coalesce(`test`.`t1`.`d`,0) = '3 ' 5760DROP TABLE t1; 5761# 5762# MDEV-9178 Wrong result for CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED) 5763# 5764SET NAMES utf8; 5765SELECT CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED); 5766CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED) 57671 5768Warnings: 5769Warning 1292 Truncated incorrect INTEGER value: '1IJ3' 5770# 5771# End of 10.1 tests 5772# 5773# 5774# Start of 10.2 tests 5775# 5776# 5777# MDEV-9711 NO PAD Collatons 5778# 5779SET character_set_connection=ucs2; 5780SET STORAGE_ENGINE=MyISAM; 5781# 5782# Start of ctype_pad.inc 5783# 5784# 5785# Unique indexes 5786# 5787CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'ucs2_general_nopad_ci'; 5788SHOW CREATE TABLE t1; 5789Table Create Table 5790t1 CREATE TABLE `t1` ( 5791 `a` varchar(10) COLLATE ucs2_general_nopad_ci NOT NULL, 5792 PRIMARY KEY (`a`) 5793) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_general_nopad_ci 5794INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); 5795SELECT HEX(a), a FROM t1 ORDER BY a; 5796HEX(a) a 579700200061 a 5798002000610020 a 57990061002000200020 a 5800006100620063 abc 580100610062006300200020 abc 5802SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; 5803HEX(a) a 580400200061 a 5805002000610020 a 58060061002000200020 a 5807006100620063 abc 580800610062006300200020 abc 5809SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; 5810HEX(a) a 581100610062006300200020 abc 5812006100620063 abc 58130061002000200020 a 5814002000610020 a 581500200061 a 5816# 5817# UNION 5818# 5819CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'ucs2_general_nopad_ci'; 5820INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); 5821SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; 5822HEX(a) a 582300200061 a 5824002000610020 a 582500610020 a 58260061002000200020 a 5827006100620063 abc 58280061006200630020 abc 582900610062006300200020 abc 5830DROP TABLE t1; 5831DROP TABLE t2; 5832# 5833# DISTINCT, COUNT, MAX 5834# 5835CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'ucs2_general_nopad_ci'; 5836INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); 5837SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; 5838HEX(a) a 583900200061 a 5840002000610020 a 58410061 a 5842006100200020 a 58430061002000200020 a 5844SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; 5845COUNT(DISTINCT a) 58465 5847SELECT HEX(MAX(a)), MAX(a) FROM t1; 5848HEX(MAX(a)) MAX(a) 58490061002000200020 a 5850# 5851# GROUP BY 5852# 5853CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'ucs2_general_nopad_ci'; 5854INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); 5855SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; 5856HEX(a) cnt 585700610061 1 58580061006100200020 1 585900610062 2 5860DROP TABLE t2; 5861# 5862# Weights 5863# 5864SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 5865HEX(WEIGHT_STRING(a AS CHAR(10))) 58660041000000000000000000000000000000000000 58670041002000200000000000000000000000000000 58680020004100000000000000000000000000000000 58690020004100200000000000000000000000000000 58700041002000200020000000000000000000000000 5871DROP TABLE t1; 5872# 5873# IF, CASE, LEAST 5874# 5875SELECT IF('abc' COLLATE 'ucs2_general_nopad_ci' = 'abc ', 'pad', 'nopad'); 5876IF('abc' COLLATE 'ucs2_general_nopad_ci' = 'abc ', 'pad', 'nopad') 5877nopad 5878SELECT CASE 'abc' COLLATE 'ucs2_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; 5879CASE 'abc' COLLATE 'ucs2_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END 5880nopad 5881SELECT CASE WHEN 'abc' COLLATE 'ucs2_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; 5882CASE WHEN 'abc' COLLATE 'ucs2_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END 5883nopad 5884SELECT HEX(LEAST('abc ' COLLATE 'ucs2_general_nopad_ci', 'abc ')); 5885HEX(LEAST('abc ' COLLATE 'ucs2_general_nopad_ci', 'abc ')) 58860061006200630020 5887SELECT HEX(GREATEST('abc ' COLLATE 'ucs2_general_nopad_ci', 'abc ')); 5888HEX(GREATEST('abc ' COLLATE 'ucs2_general_nopad_ci', 'abc ')) 588900610062006300200020 5890# 5891# Collation mix 5892# 5893CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'ucs2_general_ci'; 5894INSERT INTO t1 VALUES ('a'),('a '); 5895SELECT COUNT(*) FROM t1 WHERE a='a'; 5896COUNT(*) 58972 5898SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_general_ci'; 5899COUNT(*) 59002 5901SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_general_nopad_ci'; 5902COUNT(*) 59031 5904ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'ucs2_general_nopad_ci'; 5905SELECT COUNT(*) FROM t1 WHERE a='a'; 5906COUNT(*) 59071 5908SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_general_ci'; 5909COUNT(*) 59102 5911SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_general_nopad_ci'; 5912COUNT(*) 59131 5914DROP TABLE t1; 5915# 5916# End of ctype_pad.inc 5917# 5918SET STORAGE_ENGINE=HEAP; 5919# 5920# Start of ctype_pad.inc 5921# 5922# 5923# Unique indexes 5924# 5925CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'ucs2_general_nopad_ci'; 5926SHOW CREATE TABLE t1; 5927Table Create Table 5928t1 CREATE TABLE `t1` ( 5929 `a` varchar(10) COLLATE ucs2_general_nopad_ci NOT NULL, 5930 PRIMARY KEY (`a`) 5931) ENGINE=MEMORY DEFAULT CHARSET=ucs2 COLLATE=ucs2_general_nopad_ci 5932INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); 5933SELECT HEX(a), a FROM t1 ORDER BY a; 5934HEX(a) a 593500200061 a 5936002000610020 a 59370061002000200020 a 5938006100620063 abc 593900610062006300200020 abc 5940SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; 5941HEX(a) a 594200200061 a 5943002000610020 a 59440061002000200020 a 5945006100620063 abc 594600610062006300200020 abc 5947SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; 5948HEX(a) a 594900610062006300200020 abc 5950006100620063 abc 59510061002000200020 a 5952002000610020 a 595300200061 a 5954# 5955# UNION 5956# 5957CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'ucs2_general_nopad_ci'; 5958INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); 5959SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; 5960HEX(a) a 596100200061 a 5962002000610020 a 596300610020 a 59640061002000200020 a 5965006100620063 abc 59660061006200630020 abc 596700610062006300200020 abc 5968DROP TABLE t1; 5969DROP TABLE t2; 5970# 5971# DISTINCT, COUNT, MAX 5972# 5973CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'ucs2_general_nopad_ci'; 5974INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); 5975SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; 5976HEX(a) a 597700200061 a 5978002000610020 a 59790061 a 5980006100200020 a 59810061002000200020 a 5982SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; 5983COUNT(DISTINCT a) 59845 5985SELECT HEX(MAX(a)), MAX(a) FROM t1; 5986HEX(MAX(a)) MAX(a) 59870061002000200020 a 5988# 5989# GROUP BY 5990# 5991CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'ucs2_general_nopad_ci'; 5992INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); 5993SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; 5994HEX(a) cnt 599500610061 1 59960061006100200020 1 599700610062 2 5998DROP TABLE t2; 5999# 6000# Weights 6001# 6002SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 6003HEX(WEIGHT_STRING(a AS CHAR(10))) 60040041000000000000000000000000000000000000 60050041002000200000000000000000000000000000 60060020004100000000000000000000000000000000 60070020004100200000000000000000000000000000 60080041002000200020000000000000000000000000 6009DROP TABLE t1; 6010# 6011# IF, CASE, LEAST 6012# 6013SELECT IF('abc' COLLATE 'ucs2_general_nopad_ci' = 'abc ', 'pad', 'nopad'); 6014IF('abc' COLLATE 'ucs2_general_nopad_ci' = 'abc ', 'pad', 'nopad') 6015nopad 6016SELECT CASE 'abc' COLLATE 'ucs2_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; 6017CASE 'abc' COLLATE 'ucs2_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END 6018nopad 6019SELECT CASE WHEN 'abc' COLLATE 'ucs2_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; 6020CASE WHEN 'abc' COLLATE 'ucs2_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END 6021nopad 6022SELECT HEX(LEAST('abc ' COLLATE 'ucs2_general_nopad_ci', 'abc ')); 6023HEX(LEAST('abc ' COLLATE 'ucs2_general_nopad_ci', 'abc ')) 60240061006200630020 6025SELECT HEX(GREATEST('abc ' COLLATE 'ucs2_general_nopad_ci', 'abc ')); 6026HEX(GREATEST('abc ' COLLATE 'ucs2_general_nopad_ci', 'abc ')) 602700610062006300200020 6028# 6029# Collation mix 6030# 6031CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'ucs2_general_ci'; 6032INSERT INTO t1 VALUES ('a'),('a '); 6033SELECT COUNT(*) FROM t1 WHERE a='a'; 6034COUNT(*) 60352 6036SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_general_ci'; 6037COUNT(*) 60382 6039SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_general_nopad_ci'; 6040COUNT(*) 60411 6042ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'ucs2_general_nopad_ci'; 6043SELECT COUNT(*) FROM t1 WHERE a='a'; 6044COUNT(*) 60451 6046SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_general_ci'; 6047COUNT(*) 60482 6049SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_general_nopad_ci'; 6050COUNT(*) 60511 6052DROP TABLE t1; 6053# 6054# End of ctype_pad.inc 6055# 6056SET STORAGE_ENGINE=Default; 6057SET STORAGE_ENGINE=MyISAM; 6058# 6059# Start of ctype_pad.inc 6060# 6061# 6062# Unique indexes 6063# 6064CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'ucs2_nopad_bin'; 6065SHOW CREATE TABLE t1; 6066Table Create Table 6067t1 CREATE TABLE `t1` ( 6068 `a` varchar(10) COLLATE ucs2_nopad_bin NOT NULL, 6069 PRIMARY KEY (`a`) 6070) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_nopad_bin 6071INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); 6072SELECT HEX(a), a FROM t1 ORDER BY a; 6073HEX(a) a 607400200061 a 6075002000610020 a 60760061002000200020 a 6077006100620063 abc 607800610062006300200020 abc 6079SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; 6080HEX(a) a 608100200061 a 6082002000610020 a 60830061002000200020 a 6084006100620063 abc 608500610062006300200020 abc 6086SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; 6087HEX(a) a 608800610062006300200020 abc 6089006100620063 abc 60900061002000200020 a 6091002000610020 a 609200200061 a 6093# 6094# UNION 6095# 6096CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'ucs2_nopad_bin'; 6097INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); 6098SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; 6099HEX(a) a 610000200061 a 6101002000610020 a 610200610020 a 61030061002000200020 a 6104006100620063 abc 61050061006200630020 abc 610600610062006300200020 abc 6107DROP TABLE t1; 6108DROP TABLE t2; 6109# 6110# DISTINCT, COUNT, MAX 6111# 6112CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'ucs2_nopad_bin'; 6113INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); 6114SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; 6115HEX(a) a 611600200061 a 6117002000610020 a 61180061 a 6119006100200020 a 61200061002000200020 a 6121SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; 6122COUNT(DISTINCT a) 61235 6124SELECT HEX(MAX(a)), MAX(a) FROM t1; 6125HEX(MAX(a)) MAX(a) 61260061002000200020 a 6127# 6128# GROUP BY 6129# 6130CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'ucs2_nopad_bin'; 6131INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); 6132SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; 6133HEX(a) cnt 613400610061 1 61350061006100200020 1 613600610062 2 6137DROP TABLE t2; 6138# 6139# Weights 6140# 6141SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 6142HEX(WEIGHT_STRING(a AS CHAR(10))) 61430061000000000000000000000000000000000000 61440061002000200000000000000000000000000000 61450020006100000000000000000000000000000000 61460020006100200000000000000000000000000000 61470061002000200020000000000000000000000000 6148DROP TABLE t1; 6149# 6150# IF, CASE, LEAST 6151# 6152SELECT IF('abc' COLLATE 'ucs2_nopad_bin' = 'abc ', 'pad', 'nopad'); 6153IF('abc' COLLATE 'ucs2_nopad_bin' = 'abc ', 'pad', 'nopad') 6154nopad 6155SELECT CASE 'abc' COLLATE 'ucs2_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; 6156CASE 'abc' COLLATE 'ucs2_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END 6157nopad 6158SELECT CASE WHEN 'abc' COLLATE 'ucs2_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; 6159CASE WHEN 'abc' COLLATE 'ucs2_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END 6160nopad 6161SELECT HEX(LEAST('abc ' COLLATE 'ucs2_nopad_bin', 'abc ')); 6162HEX(LEAST('abc ' COLLATE 'ucs2_nopad_bin', 'abc ')) 61630061006200630020 6164SELECT HEX(GREATEST('abc ' COLLATE 'ucs2_nopad_bin', 'abc ')); 6165HEX(GREATEST('abc ' COLLATE 'ucs2_nopad_bin', 'abc ')) 616600610062006300200020 6167# 6168# Collation mix 6169# 6170CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'ucs2_bin'; 6171INSERT INTO t1 VALUES ('a'),('a '); 6172SELECT COUNT(*) FROM t1 WHERE a='a'; 6173COUNT(*) 61742 6175SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_bin'; 6176COUNT(*) 61772 6178SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_nopad_bin'; 6179COUNT(*) 61801 6181ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'ucs2_nopad_bin'; 6182SELECT COUNT(*) FROM t1 WHERE a='a'; 6183COUNT(*) 61841 6185SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_bin'; 6186COUNT(*) 61872 6188SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_nopad_bin'; 6189COUNT(*) 61901 6191DROP TABLE t1; 6192# 6193# End of ctype_pad.inc 6194# 6195SET STORAGE_ENGINE=HEAP; 6196# 6197# Start of ctype_pad.inc 6198# 6199# 6200# Unique indexes 6201# 6202CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'ucs2_nopad_bin'; 6203SHOW CREATE TABLE t1; 6204Table Create Table 6205t1 CREATE TABLE `t1` ( 6206 `a` varchar(10) COLLATE ucs2_nopad_bin NOT NULL, 6207 PRIMARY KEY (`a`) 6208) ENGINE=MEMORY DEFAULT CHARSET=ucs2 COLLATE=ucs2_nopad_bin 6209INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); 6210SELECT HEX(a), a FROM t1 ORDER BY a; 6211HEX(a) a 621200200061 a 6213002000610020 a 62140061002000200020 a 6215006100620063 abc 621600610062006300200020 abc 6217SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; 6218HEX(a) a 621900200061 a 6220002000610020 a 62210061002000200020 a 6222006100620063 abc 622300610062006300200020 abc 6224SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; 6225HEX(a) a 622600610062006300200020 abc 6227006100620063 abc 62280061002000200020 a 6229002000610020 a 623000200061 a 6231# 6232# UNION 6233# 6234CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'ucs2_nopad_bin'; 6235INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); 6236SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; 6237HEX(a) a 623800200061 a 6239002000610020 a 624000610020 a 62410061002000200020 a 6242006100620063 abc 62430061006200630020 abc 624400610062006300200020 abc 6245DROP TABLE t1; 6246DROP TABLE t2; 6247# 6248# DISTINCT, COUNT, MAX 6249# 6250CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'ucs2_nopad_bin'; 6251INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); 6252SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; 6253HEX(a) a 625400200061 a 6255002000610020 a 62560061 a 6257006100200020 a 62580061002000200020 a 6259SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; 6260COUNT(DISTINCT a) 62615 6262SELECT HEX(MAX(a)), MAX(a) FROM t1; 6263HEX(MAX(a)) MAX(a) 62640061002000200020 a 6265# 6266# GROUP BY 6267# 6268CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'ucs2_nopad_bin'; 6269INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); 6270SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; 6271HEX(a) cnt 627200610061 1 62730061006100200020 1 627400610062 2 6275DROP TABLE t2; 6276# 6277# Weights 6278# 6279SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 6280HEX(WEIGHT_STRING(a AS CHAR(10))) 62810061000000000000000000000000000000000000 62820061002000200000000000000000000000000000 62830020006100000000000000000000000000000000 62840020006100200000000000000000000000000000 62850061002000200020000000000000000000000000 6286DROP TABLE t1; 6287# 6288# IF, CASE, LEAST 6289# 6290SELECT IF('abc' COLLATE 'ucs2_nopad_bin' = 'abc ', 'pad', 'nopad'); 6291IF('abc' COLLATE 'ucs2_nopad_bin' = 'abc ', 'pad', 'nopad') 6292nopad 6293SELECT CASE 'abc' COLLATE 'ucs2_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; 6294CASE 'abc' COLLATE 'ucs2_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END 6295nopad 6296SELECT CASE WHEN 'abc' COLLATE 'ucs2_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; 6297CASE WHEN 'abc' COLLATE 'ucs2_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END 6298nopad 6299SELECT HEX(LEAST('abc ' COLLATE 'ucs2_nopad_bin', 'abc ')); 6300HEX(LEAST('abc ' COLLATE 'ucs2_nopad_bin', 'abc ')) 63010061006200630020 6302SELECT HEX(GREATEST('abc ' COLLATE 'ucs2_nopad_bin', 'abc ')); 6303HEX(GREATEST('abc ' COLLATE 'ucs2_nopad_bin', 'abc ')) 630400610062006300200020 6305# 6306# Collation mix 6307# 6308CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'ucs2_bin'; 6309INSERT INTO t1 VALUES ('a'),('a '); 6310SELECT COUNT(*) FROM t1 WHERE a='a'; 6311COUNT(*) 63122 6313SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_bin'; 6314COUNT(*) 63152 6316SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_nopad_bin'; 6317COUNT(*) 63181 6319ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'ucs2_nopad_bin'; 6320SELECT COUNT(*) FROM t1 WHERE a='a'; 6321COUNT(*) 63221 6323SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_bin'; 6324COUNT(*) 63252 6326SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'ucs2_nopad_bin'; 6327COUNT(*) 63281 6329DROP TABLE t1; 6330# 6331# End of ctype_pad.inc 6332# 6333SET STORAGE_ENGINE=Default; 6334# 6335# MDEV-10585 EXECUTE IMMEDIATE statement 6336# 6337SET character_set_connection=ucs2; 6338EXECUTE IMMEDIATE 'SELECT COLLATION("a")'; 6339COLLATION("a") 6340ucs2_general_ci 6341SET @stmt='SELECT COLLATION("a")'; 6342EXECUTE IMMEDIATE @stmt; 6343COLLATION("a") 6344ucs2_general_ci 6345# 6346# MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions 6347# 6348SET NAMES utf8, collation_connection=ucs2_bin; 6349SET @stmt='SELECT COLLATION(''a'')'; 6350EXECUTE IMMEDIATE @stmt; 6351COLLATION('a') 6352ucs2_bin 6353SET NAMES utf8, character_set_connection=ucs2; 6354SET @stmt='SELECT COLLATION(''a'')'; 6355EXECUTE IMMEDIATE @stmt; 6356COLLATION('a') 6357ucs2_general_ci 6358EXECUTE IMMEDIATE CONCAT('SELECT ''a'' FROM DUAL'); 6359a 6360a 6361SELECT HEX('aä') FROM DUAL; 6362HEX('aä') 6363006100E4 6364EXECUTE IMMEDIATE 'SELECT HEX(''aä'') FROM DUAL'; 6365HEX('aä') 6366006100E4 6367EXECUTE IMMEDIATE CONCAT('SELECT HEX(''aä'') FROM DUAL'); 6368HEX('aä') 6369006100E4 6370EXECUTE IMMEDIATE CONCAT('SELECT HEX(''aä'') FROM ', 'DUAL'); 6371HEX('aä') 6372006100E4 6373PREPARE stmt FROM 'SELECT HEX(''aä'') FROM DUAL'; 6374EXECUTE stmt; 6375HEX('aä') 6376006100E4 6377DEALLOCATE PREPARE stmt; 6378SET @table='DUAL'; 6379SELECT HEX(@table); 6380HEX(@table) 6381004400550041004C 6382EXECUTE IMMEDIATE CONCAT('SELECT HEX(''aä'') FROM ', @table); 6383HEX('aä') 6384006100E4 6385EXECUTE IMMEDIATE CONCAT('SELECT HEX(''aä'') FROM ', CONVERT(@table USING utf8)); 6386HEX('aä') 6387006100E4 6388SET @stmt='SELECT HEX(''aä'') FROM DUAL'; 6389EXECUTE IMMEDIATE @stmt; 6390HEX('aä') 6391006100E4 6392PREPARE stmt FROM @stmt; 6393EXECUTE stmt; 6394HEX('aä') 6395006100E4 6396DEALLOCATE PREPARE stmt; 6397# 6398# End of 10.2 tests 6399# 6400# 6401# Start of 10.4 tests 6402# 6403# 6404# MDEV-17995 INET6_NTOA(ucs2_input) erroneously returns NULL 6405# 6406SELECT HEX(INET6_ATON('1::1')), HEX(INET6_ATON(CONVERT('1::1' USING ucs2))); 6407HEX(INET6_ATON('1::1')) HEX(INET6_ATON(CONVERT('1::1' USING ucs2))) 640800010000000000000000000000000001 00010000000000000000000000000001 6409# 6410# MDEV-19184 Crash in IS_IPV6(_ucs2 0x0031) 6411# 6412SET NAMES utf8; 6413SELECT IS_IPV6(_ucs2 0x0031); 6414IS_IPV6(_ucs2 0x0031) 64150 6416SELECT IS_IPV4(_ucs2 0x0031); 6417IS_IPV4(_ucs2 0x0031) 64180 6419SELECT IS_IPV6(_ucs2 0x003A003A); 6420IS_IPV6(_ucs2 0x003A003A) 64211 6422SELECT IS_IPV4(_ucs2 0x00310030002E0030002E0030002E0031); 6423IS_IPV4(_ucs2 0x00310030002E0030002E0030002E0031) 64241 6425SET NAMES utf8, collation_connection=ucs2_bin; 6426SELECT IS_IPV6('::'); 6427IS_IPV6('::') 64281 6429SELECT IS_IPV4('10.0.0.1'); 6430IS_IPV4('10.0.0.1') 64311 6432SET NAMES utf8; 6433# 6434# End of 10.4 tests 6435# 6436