1SET TIME_ZONE = '+03:00'; 2DROP TABLE IF EXISTS t1; 3# 4# Start of 5.5 tests 5# 6SET NAMES latin1; 7SET character_set_connection=utf32; 8select hex('a'), hex('a '); 9hex('a') hex('a ') 1000000061 0000006100000020 11select 'a' = 'a', 'a' = 'a ', 'a ' = 'a'; 12'a' = 'a' 'a' = 'a ' 'a ' = 'a' 131 1 1 14select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a'; 15'a\0' = 'a' 'a\0' < 'a' 'a\0' > 'a' 160 1 0 17select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0'; 18'a' = 'a\0' 'a' < 'a\0' 'a' > 'a\0' 190 0 1 20select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a '; 21'a\0' = 'a ' 'a\0' < 'a ' 'a\0' > 'a ' 220 1 0 23select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0'; 24'a ' = 'a\0' 'a ' < 'a\0' 'a ' > 'a\0' 250 0 1 26select 'a a' > 'a', 'a \0' < 'a'; 27'a a' > 'a' 'a \0' < 'a' 281 1 29select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a'; 30binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a' 311 1 1 32select hex(_utf32 0x44); 33hex(_utf32 0x44) 3400000044 35select hex(_utf32 0x3344); 36hex(_utf32 0x3344) 3700003344 38select hex(_utf32 0x103344); 39hex(_utf32 0x103344) 4000103344 41select hex(_utf32 X'44'); 42hex(_utf32 X'44') 4300000044 44select hex(_utf32 X'3344'); 45hex(_utf32 X'3344') 4600003344 47select hex(_utf32 X'103344'); 48hex(_utf32 X'103344') 4900103344 50CREATE TABLE t1 (word VARCHAR(64), word2 CHAR(64)) CHARACTER SET utf32; 51INSERT INTO t1 VALUES (_koi8r 0xF2, _koi8r 0xF2), (X'2004',X'2004'); 52SELECT hex(word) FROM t1 ORDER BY word; 53hex(word) 5400000420 5500002004 56SELECT hex(word2) FROM t1 ORDER BY word2; 57hex(word2) 5800000420 5900002004 60DELETE FROM t1; 61INSERT INTO t1 VALUES 62(X'000004200000002000000020',X'000004200000002000000020'), 63(X'000020040000002000000020',X'000020040000002000000020'); 64SELECT hex(word) FROM t1 ORDER BY word; 65hex(word) 66000004200000002000000020 67000020040000002000000020 68SELECT hex(word2) FROM t1 ORDER BY word2; 69hex(word2) 7000000420 7100002004 72DROP TABLE t1; 73SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'0421')); 74hex(LPAD(_utf32 X'0420',10,_utf32 X'0421')) 7500000421000004210000042100000421000004210000042100000421000004210000042100000420 76SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')); 77hex(LPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')) 7800000421000004220000042100000422000004210000042200000421000004220000042100000420 79SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')); 80hex(LPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')) 8100000421000004220000042300000421000004220000042300000421000004220000042300000420 82SELECT hex(LPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')); 83hex(LPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')) 8400000420000004210000042200000423000004240000042500000426000004270000042800000429 85SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0421')); 86hex(RPAD(_utf32 X'0420',10,_utf32 X'0421')) 8700000420000004210000042100000421000004210000042100000421000004210000042100000421 88SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')); 89hex(RPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')) 9000000420000004210000042200000421000004220000042100000422000004210000042200000421 91SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')); 92hex(RPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')) 9300000420000004210000042200000423000004210000042200000423000004210000042200000423 94SELECT hex(RPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')); 95hex(RPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')) 9600000420000004210000042200000423000004240000042500000426000004270000042800000429 97CREATE TABLE t1 SELECT 98LPAD(_utf32 X'0420',10,_utf32 X'0421') l, 99RPAD(_utf32 X'0420',10,_utf32 X'0421') r; 100SHOW CREATE TABLE t1; 101Table Create Table 102t1 CREATE TABLE `t1` ( 103 `l` varchar(10) CHARACTER SET utf32 DEFAULT NULL, 104 `r` varchar(10) CHARACTER SET utf32 DEFAULT NULL 105) ENGINE=MyISAM DEFAULT CHARSET=latin1 106select hex(l), hex(r) from t1; 107hex(l) hex(r) 10800000421000004210000042100000421000004210000042100000421000004210000042100000420 00000420000004210000042100000421000004210000042100000421000004210000042100000421 109DROP TABLE t1; 110create table t1 (f1 char(30)); 111insert into t1 values ("103000"), ("22720000"), ("3401200"), ("78000"); 112select lpad(f1, 12, "-o-/") from t1; 113lpad(f1, 12, "-o-/") 114-o-/-o103000 115-o-/22720000 116-o-/-3401200 117-o-/-o-78000 118drop table t1; 119SET NAMES latin1; 120SET character_set_connection=utf32; 121select @@collation_connection; 122@@collation_connection 123utf32_general_ci 124create table t1 as select repeat(' ',10) as a union select null; 125alter table t1 add key(a); 126show create table t1; 127Table Create Table 128t1 CREATE TABLE `t1` ( 129 `a` varchar(10) CHARACTER SET utf32 DEFAULT NULL, 130 KEY `a` (`a`) 131) ENGINE=MyISAM DEFAULT CHARSET=latin1 132insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); 133explain select * from t1 where a like 'abc%'; 134id select_type table type possible_keys key key_len ref rows Extra 1351 SIMPLE t1 range a a 43 NULL 2 Using where; Using index 136explain select * from t1 where a like concat('abc','%'); 137id select_type table type possible_keys key key_len ref rows Extra 1381 SIMPLE t1 range a a 43 NULL 2 Using where; Using index 139select * from t1 where a like "abc%"; 140a 141abc 142abcd 143select * from t1 where a like concat("abc","%"); 144a 145abc 146abcd 147select * from t1 where a like "ABC%"; 148a 149abc 150abcd 151select * from t1 where a like "test%"; 152a 153test 154select * from t1 where a like "te_t"; 155a 156test 157select * from t1 where a like "%a%"; 158a 159a 160abc 161abcd 162select * from t1 where a like "%abcd%"; 163a 164abcd 165select * from t1 where a like "%abc\d%"; 166a 167abcd 168drop table t1; 169select 'AA' like 'AA'; 170'AA' like 'AA' 1711 172select 'AA' like 'A%A'; 173'AA' like 'A%A' 1741 175select 'AA' like 'A%%A'; 176'AA' like 'A%%A' 1771 178select 'AA' like 'AA%'; 179'AA' like 'AA%' 1801 181select 'AA' like '%AA%'; 182'AA' like '%AA%' 1831 184select 'AA' like '%A'; 185'AA' like '%A' 1861 187select 'AA' like '%AA'; 188'AA' like '%AA' 1891 190select 'AA' like 'A%A%'; 191'AA' like 'A%A%' 1921 193select 'AA' like '_%_%'; 194'AA' like '_%_%' 1951 196select 'AA' like '%A%A'; 197'AA' like '%A%A' 1981 199select 'AAA'like 'A%A%A'; 200'AAA'like 'A%A%A' 2011 202select 'AZ' like 'AZ'; 203'AZ' like 'AZ' 2041 205select 'AZ' like 'A%Z'; 206'AZ' like 'A%Z' 2071 208select 'AZ' like 'A%%Z'; 209'AZ' like 'A%%Z' 2101 211select 'AZ' like 'AZ%'; 212'AZ' like 'AZ%' 2131 214select 'AZ' like '%AZ%'; 215'AZ' like '%AZ%' 2161 217select 'AZ' like '%Z'; 218'AZ' like '%Z' 2191 220select 'AZ' like '%AZ'; 221'AZ' like '%AZ' 2221 223select 'AZ' like 'A%Z%'; 224'AZ' like 'A%Z%' 2251 226select 'AZ' like '_%_%'; 227'AZ' like '_%_%' 2281 229select 'AZ' like '%A%Z'; 230'AZ' like '%A%Z' 2311 232select 'AZ' like 'A_'; 233'AZ' like 'A_' 2341 235select 'AZ' like '_Z'; 236'AZ' like '_Z' 2371 238select 'AMZ'like 'A%M%Z'; 239'AMZ'like 'A%M%Z' 2401 241SET NAMES utf8; 242SET character_set_connection=utf32; 243CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32); 244INSERT INTO t1 VALUES ('фыва'),('Фыва'),('фЫва'),('фыВа'),('фывА'),('ФЫВА'); 245INSERT INTO t1 VALUES ('фывапролдж'),('Фывапролдж'),('фЫвапролдж'),('фыВапролдж'); 246INSERT INTO t1 VALUES ('фывАпролдж'),('фываПролдж'),('фывапРолдж'),('фывапрОлдж'); 247INSERT INTO t1 VALUES ('фывапроЛдж'),('фывапролДж'),('фывапролдЖ'),('ФЫВАПРОЛДЖ'); 248SELECT * FROM t1 WHERE a LIKE '%фЫва%' ORDER BY BINARY a; 249a 250ФЫВА 251ФЫВАПРОЛДЖ 252Фыва 253Фывапролдж 254фЫва 255фЫвапролдж 256фыВа 257фыВапролдж 258фывА 259фывАпролдж 260фыва 261фываПролдж 262фывапРолдж 263фывапрОлдж 264фывапроЛдж 265фывапролДж 266фывапролдЖ 267фывапролдж 268SELECT * FROM t1 WHERE a LIKE '%фЫв%' ORDER BY BINARY a; 269a 270ФЫВА 271ФЫВАПРОЛДЖ 272Фыва 273Фывапролдж 274фЫва 275фЫвапролдж 276фыВа 277фыВапролдж 278фывА 279фывАпролдж 280фыва 281фываПролдж 282фывапРолдж 283фывапрОлдж 284фывапроЛдж 285фывапролДж 286фывапролдЖ 287фывапролдж 288SELECT * FROM t1 WHERE a LIKE 'фЫва%' ORDER BY BINARY a; 289a 290ФЫВА 291ФЫВАПРОЛДЖ 292Фыва 293Фывапролдж 294фЫва 295фЫвапролдж 296фыВа 297фыВапролдж 298фывА 299фывАпролдж 300фыва 301фываПролдж 302фывапРолдж 303фывапрОлдж 304фывапроЛдж 305фывапролДж 306фывапролдЖ 307фывапролдж 308SELECT * FROM t1 WHERE a LIKE 'фЫва%' COLLATE utf32_bin ORDER BY BINARY a; 309a 310фЫва 311фЫвапролдж 312DROP TABLE t1; 313CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word)) 314ENGINE=MyISAM CHARACTER SET utf32; 315INSERT INTO t1 (word) VALUES ("cat"); 316SELECT * FROM t1 WHERE word LIKE "c%"; 317word 318cat 319SELECT * FROM t1 WHERE word LIKE "ca_"; 320word 321cat 322SELECT * FROM t1 WHERE word LIKE "cat"; 323word 324cat 325SELECT * FROM t1 WHERE word LIKE _utf32 x'0000006300000025'; 326word 327cat 328SELECT * FROM t1 WHERE word LIKE _utf32 x'00000063000000610000005F'; 329word 330cat 331DROP TABLE t1; 332select insert(_utf32 0x000000610000006200000063,10,2,_utf32 0x000000640000006500000066); 333insert(_utf32 0x000000610000006200000063,10,2,_utf32 0x000000640000006500000066) 334abc 335select insert(_utf32 0x000000610000006200000063,1,2,_utf32 0x000000640000006500000066); 336insert(_utf32 0x000000610000006200000063,1,2,_utf32 0x000000640000006500000066) 337defc 338SET NAMES latin1; 339CREATE TABLE t1 ( 340word VARCHAR(64), 341bar INT(11) default 0, 342PRIMARY KEY (word)) 343ENGINE=MyISAM 344CHARSET utf32 345COLLATE utf32_general_ci ; 346INSERT INTO t1 (word) VALUES ("aar"); 347INSERT INTO t1 (word) VALUES ("a"); 348INSERT INTO t1 (word) VALUES ("aardvar"); 349INSERT INTO t1 (word) VALUES ("aardvark"); 350INSERT INTO t1 (word) VALUES ("aardvara"); 351INSERT INTO t1 (word) VALUES ("aardvarz"); 352EXPLAIN SELECT * FROM t1 ORDER BY word; 353id select_type table type possible_keys key key_len ref rows Extra 3541 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 355SELECT * FROM t1 ORDER BY word; 356word bar 357a 0 358aar 0 359aardvar 0 360aardvara 0 361aardvark 0 362aardvarz 0 363EXPLAIN SELECT word FROM t1 ORDER BY word; 364id select_type table type possible_keys key key_len ref rows Extra 3651 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index 366SELECT word FROM t1 ORDER by word; 367word 368a 369aar 370aardvar 371aardvara 372aardvark 373aardvarz 374DROP TABLE t1; 375CREATE TABLE t1 ( 376word VARCHAR(64) , 377PRIMARY KEY (word)) 378ENGINE=MyISAM 379CHARSET utf32 380COLLATE utf32_general_ci; 381INSERT INTO t1 (word) VALUES ("aar"); 382INSERT INTO t1 (word) VALUES ("a"); 383INSERT INTO t1 (word) VALUES ("aardvar"); 384INSERT INTO t1 (word) VALUES ("aardvark"); 385INSERT INTO t1 (word) VALUES ("aardvara"); 386INSERT INTO t1 (word) VALUES ("aardvarz"); 387EXPLAIN SELECT * FROM t1 ORDER BY WORD; 388id select_type table type possible_keys key key_len ref rows Extra 3891 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index 390SELECT * FROM t1 ORDER BY word; 391word 392a 393aar 394aardvar 395aardvara 396aardvark 397aardvarz 398DROP TABLE t1; 399CREATE TABLE t1 ( 400word TEXT, 401bar INT(11) AUTO_INCREMENT, 402PRIMARY KEY (bar)) 403ENGINE=MyISAM 404CHARSET utf32 405COLLATE utf32_general_ci ; 406INSERT INTO t1 (word) VALUES ("aar"); 407INSERT INTO t1 (word) VALUES ("a" ); 408INSERT INTO t1 (word) VALUES ("aardvar"); 409INSERT INTO t1 (word) VALUES ("aardvark"); 410INSERT INTO t1 (word) VALUES ("aardvara"); 411INSERT INTO t1 (word) VALUES ("aardvarz"); 412EXPLAIN SELECT * FROM t1 ORDER BY word; 413id select_type table type possible_keys key key_len ref rows Extra 4141 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 415SELECT * FROM t1 ORDER BY word; 416word bar 417a 2 418aar 1 419aardvar 3 420aardvara 5 421aardvark 4 422aardvarz 6 423EXPLAIN SELECT word FROM t1 ORDER BY word; 424id select_type table type possible_keys key key_len ref rows Extra 4251 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort 426SELECT word FROM t1 ORDER BY word; 427word 428a 429aar 430aardvar 431aardvara 432aardvark 433aardvarz 434DROP TABLE t1; 435SELECT hex(cast(0xAA as char character set utf32)); 436hex(cast(0xAA as char character set utf32)) 437000000AA 438SELECT hex(convert(0xAA using utf32)); 439hex(convert(0xAA using utf32)) 440000000AA 441CREATE TABLE t1 (a char(10) character set utf32); 442INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 443SELECT HEX(a) FROM t1; 444HEX(a) 44500000001 44600000011 44700000111 44800001111 44900011111 450DROP TABLE t1; 451CREATE TABLE t1 (a varchar(10) character set utf32); 452INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 453SELECT HEX(a) FROM t1; 454HEX(a) 45500000001 45600000011 45700000111 45800001111 45900011111 460DROP TABLE t1; 461CREATE TABLE t1 (a text character set utf32); 462INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 463SELECT HEX(a) FROM t1; 464HEX(a) 46500000001 46600000011 46700000111 46800001111 46900011111 470DROP TABLE t1; 471CREATE TABLE t1 (a mediumtext character set utf32); 472INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 473SELECT HEX(a) FROM t1; 474HEX(a) 47500000001 47600000011 47700000111 47800001111 47900011111 480DROP TABLE t1; 481CREATE TABLE t1 (a longtext character set utf32); 482INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 483SELECT HEX(a) FROM t1; 484HEX(a) 48500000001 48600000011 48700000111 48800001111 48900011111 490DROP TABLE t1; 491create table t1(a char(1)) default charset utf32; 492insert into t1 values ('a'),('b'),('c'); 493alter table t1 modify a char(5); 494select a, hex(a) from t1; 495a hex(a) 496a 00000061 497b 00000062 498c 00000063 499drop table t1; 500set @ivar= 1234; 501set @str1 = 'select ?'; 502set @str2 = convert(@str1 using utf32); 503prepare stmt1 from @str2; 504execute stmt1 using @ivar; 505? 5061234 507set names utf8; 508create table t1 (a enum('x','y','z') character set utf32); 509show create table t1; 510Table Create Table 511t1 CREATE TABLE `t1` ( 512 `a` enum('x','y','z') CHARACTER SET utf32 DEFAULT NULL 513) ENGINE=MyISAM DEFAULT CHARSET=latin1 514insert into t1 values ('x'); 515insert into t1 values ('y'); 516insert into t1 values ('z'); 517select a, hex(a) from t1 order by a; 518a hex(a) 519x 00000078 520y 00000079 521z 0000007A 522alter table t1 change a a enum('x','y','z','d','e','ä','ö','ü') character set utf32; 523show create table t1; 524Table Create Table 525t1 CREATE TABLE `t1` ( 526 `a` enum('x','y','z','d','e','ä','ö','ü') CHARACTER SET utf32 DEFAULT NULL 527) ENGINE=MyISAM DEFAULT CHARSET=latin1 528insert into t1 values ('D'); 529insert into t1 values ('E '); 530insert into t1 values ('ä'); 531insert into t1 values ('ö'); 532insert into t1 values ('ü'); 533select a, hex(a) from t1 order by a; 534a hex(a) 535x 00000078 536y 00000079 537z 0000007A 538d 00000064 539e 00000065 540ä 000000E4 541ö 000000F6 542ü 000000FC 543drop table t1; 544create table t1 (a set ('x','y','z','ä','ö','ü') character set utf32); 545show create table t1; 546Table Create Table 547t1 CREATE TABLE `t1` ( 548 `a` set('x','y','z','ä','ö','ü') CHARACTER SET utf32 DEFAULT NULL 549) ENGINE=MyISAM DEFAULT CHARSET=latin1 550insert into t1 values ('x'); 551insert into t1 values ('y'); 552insert into t1 values ('z'); 553insert into t1 values ('x,y'); 554insert into t1 values ('x,y,z,ä,ö,ü'); 555select a, hex(a) from t1 order by a; 556a hex(a) 557x 00000078 558y 00000079 559x,y 000000780000002C00000079 560z 0000007A 561x,y,z,ä,ö,ü 000000780000002C000000790000002C0000007A0000002C000000E40000002C000000F60000002C000000FC 562drop table t1; 563create table t1(a enum('a','b','c')) default character set utf32; 564insert into t1 values('a'),('b'),('c'); 565alter table t1 add b char(1); 566show warnings; 567Level Code Message 568select * from t1 order by a; 569a b 570a NULL 571b NULL 572c NULL 573drop table t1; 574SET NAMES latin1; 575SET collation_connection='utf32_general_ci'; 576create table t1 select repeat('a',4000) a; 577delete from t1; 578insert into t1 values ('a'), ('a '), ('a\t'); 579select collation(a),hex(a) from t1 order by a; 580collation(a) hex(a) 581utf32_general_ci 0000006100000009 582utf32_general_ci 00000061 583utf32_general_ci 0000006100000020 584drop table t1; 585# 586# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP 587# 588SELECT @@collation_connection; 589@@collation_connection 590utf32_general_ci 591CREATE TABLE t1 (i INT) ENGINE=MyISAM; 592INSERT INTO t1 VALUES (1),(2); 593SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP; 594i 5951 5961 597SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP; 598i 5991 6001 601DROP TABLE t1; 602# 603# MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations 604# 605SELECT @@collation_connection; 606@@collation_connection 607utf32_general_ci 608CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0; 609INSERT INTO t1 (a) VALUES ("a"); 610INSERT INTO t1 (a) VALUES ("b"); 611INSERT INTO t1 (a) VALUES ("c"); 612INSERT INTO t1 (a) VALUES ("d"); 613INSERT INTO t1 (a) VALUES ("e"); 614INSERT INTO t1 (a) VALUES ("f"); 615INSERT INTO t1 (a) VALUES ("g"); 616INSERT INTO t1 (a) VALUES ("h"); 617INSERT INTO t1 (a) VALUES ("i"); 618INSERT INTO t1 (a) VALUES ("j"); 619INSERT INTO t1 (a) VALUES ("k"); 620INSERT INTO t1 (a) VALUES ("l"); 621INSERT INTO t1 (a) VALUES ("m"); 622SELECT * FROM t1 ORDER BY LOWER(a); 623a 624a 625b 626c 627d 628e 629f 630g 631h 632i 633j 634k 635l 636m 637SELECT * FROM t1 ORDER BY LOWER(a) DESC; 638a 639m 640l 641k 642j 643i 644h 645g 646f 647e 648d 649c 650b 651a 652DROP TABLE t1; 653select @@collation_connection; 654@@collation_connection 655utf32_general_ci 656create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; 657insert into t1 values('abcdef'); 658insert into t1 values('_bcdef'); 659insert into t1 values('a_cdef'); 660insert into t1 values('ab_def'); 661insert into t1 values('abc_ef'); 662insert into t1 values('abcd_f'); 663insert into t1 values('abcde_'); 664select c1 as c1u from t1 where c1 like 'ab\_def'; 665c1u 666ab_def 667select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; 668c2h 669ab_def 670drop table t1; 671SELECT @@collation_connection; 672@@collation_connection 673utf32_general_ci 674SELECT '\%b' LIKE '%\%'; 675'\%b' LIKE '%\%' 6760 677SET NAMES latin1; 678SET collation_connection='utf32_bin'; 679create table t1 select repeat('a',4000) a; 680delete from t1; 681insert into t1 values ('a'), ('a '), ('a\t'); 682select collation(a),hex(a) from t1 order by a; 683collation(a) hex(a) 684utf32_bin 0000006100000009 685utf32_bin 00000061 686utf32_bin 0000006100000020 687drop table t1; 688# 689# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP 690# 691SELECT @@collation_connection; 692@@collation_connection 693utf32_bin 694CREATE TABLE t1 (i INT) ENGINE=MyISAM; 695INSERT INTO t1 VALUES (1),(2); 696SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP; 697i 6981 6991 700SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP; 701i 7021 7031 704DROP TABLE t1; 705# 706# MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations 707# 708SELECT @@collation_connection; 709@@collation_connection 710utf32_bin 711CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0; 712INSERT INTO t1 (a) VALUES ("a"); 713INSERT INTO t1 (a) VALUES ("b"); 714INSERT INTO t1 (a) VALUES ("c"); 715INSERT INTO t1 (a) VALUES ("d"); 716INSERT INTO t1 (a) VALUES ("e"); 717INSERT INTO t1 (a) VALUES ("f"); 718INSERT INTO t1 (a) VALUES ("g"); 719INSERT INTO t1 (a) VALUES ("h"); 720INSERT INTO t1 (a) VALUES ("i"); 721INSERT INTO t1 (a) VALUES ("j"); 722INSERT INTO t1 (a) VALUES ("k"); 723INSERT INTO t1 (a) VALUES ("l"); 724INSERT INTO t1 (a) VALUES ("m"); 725SELECT * FROM t1 ORDER BY LOWER(a); 726a 727a 728b 729c 730d 731e 732f 733g 734h 735i 736j 737k 738l 739m 740SELECT * FROM t1 ORDER BY LOWER(a) DESC; 741a 742m 743l 744k 745j 746i 747h 748g 749f 750e 751d 752c 753b 754a 755DROP TABLE t1; 756# 757# Bug#55980 Character sets: supplementary character _bin ordering is wrong 758# 759CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a LIMIT 0; 760SHOW CREATE TABLE t1; 761Table Create Table 762t1 CREATE TABLE `t1` ( 763 `a` varchar(1) CHARACTER SET utf32 COLLATE utf32_bin DEFAULT NULL 764) ENGINE=MyISAM DEFAULT CHARSET=latin1 765INSERT INTO t1 VALUES (_utf8mb4 0xEFBE9D),(_utf8mb4 0xF0908E84); 766INSERT INTO t1 VALUES (_utf8mb4 0xCE85),(_utf8mb4 0xF4808080); 767SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a; 768HEX(a) HEX(CONVERT(a USING utf8mb4)) 76900000385 CE85 7700000FF9D EFBE9D 77100010384 F0908E84 77200100000 F4808080 773ALTER TABLE t1 ADD KEY(a); 774SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a; 775HEX(a) HEX(CONVERT(a USING utf8mb4)) 77600000385 CE85 7770000FF9D EFBE9D 77800010384 F0908E84 77900100000 F4808080 780DROP TABLE IF EXISTS t1; 781# 782# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES 783# OUT-OF-ORDER RESULTS 784# 785CREATE TABLE t1 SELECT ('a a') as n; 786INSERT INTO t1 VALUES('a b'); 787SELECT * FROM t1 ORDER BY LOWER(n) ASC; 788n 789a a 790a b 791SELECT * FROM t1 ORDER BY LOWER(n) DESC; 792n 793a b 794a a 795DROP TABLE t1; 796select @@collation_connection; 797@@collation_connection 798utf32_bin 799create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; 800insert into t1 values('abcdef'); 801insert into t1 values('_bcdef'); 802insert into t1 values('a_cdef'); 803insert into t1 values('ab_def'); 804insert into t1 values('abc_ef'); 805insert into t1 values('abcd_f'); 806insert into t1 values('abcde_'); 807select c1 as c1u from t1 where c1 like 'ab\_def'; 808c1u 809ab_def 810select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; 811c2h 812ab_def 813drop table t1; 814SELECT @@collation_connection; 815@@collation_connection 816utf32_bin 817SELECT '\%b' LIKE '%\%'; 818'\%b' LIKE '%\%' 8190 820select hex(substr(_utf32 0x000000e4000000e500000068,1)); 821hex(substr(_utf32 0x000000e4000000e500000068,1)) 822000000E4000000E500000068 823select hex(substr(_utf32 0x000000e4000000e500000068,2)); 824hex(substr(_utf32 0x000000e4000000e500000068,2)) 825000000E500000068 826select hex(substr(_utf32 0x000000e4000000e500000068,3)); 827hex(substr(_utf32 0x000000e4000000e500000068,3)) 82800000068 829select hex(substr(_utf32 0x000000e4000000e500000068,-1)); 830hex(substr(_utf32 0x000000e4000000e500000068,-1)) 83100000068 832select hex(substr(_utf32 0x000000e4000000e500000068,-2)); 833hex(substr(_utf32 0x000000e4000000e500000068,-2)) 834000000E500000068 835select hex(substr(_utf32 0x000000e4000000e500000068,-3)); 836hex(substr(_utf32 0x000000e4000000e500000068,-3)) 837000000E4000000E500000068 838CREATE TABLE t1 ( 839a varchar(250) NOT NULL default '', 840KEY a (a) 841) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE utf32_general_ci; 842insert into t1 values (0x803d); 843insert into t1 values (0x005b); 844select hex(a) from t1; 845hex(a) 8460000005B 8470000803D 848drop table t1; 849create table t1 (utext varchar(20) character set utf32); 850insert into t1 values ("lily"); 851insert into t1 values ("river"); 852prepare stmt from 'select utext from t1 where utext like ?'; 853set @param1='%%'; 854execute stmt using @param1; 855utext 856lily 857river 858execute stmt using @param1; 859utext 860lily 861river 862select utext from t1 where utext like '%%'; 863utext 864lily 865river 866drop table t1; 867deallocate prepare stmt; 868create table t1 ( 869a char(10) character set utf32 not null, 870index a (a) 871) engine=myisam; 872insert into t1 values (repeat(0x0000201f, 10)); 873insert into t1 values (repeat(0x00002020, 10)); 874insert into t1 values (repeat(0x00002021, 10)); 875explain select hex(a) from t1 order by a; 876id select_type table type possible_keys key key_len ref rows Extra 8771 SIMPLE t1 index NULL a 40 NULL 3 Using index 878select hex(a) from t1 order by a; 879hex(a) 8800000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F 88100002020000020200000202000002020000020200000202000002020000020200000202000002020 88200002021000020210000202100002021000020210000202100002021000020210000202100002021 883alter table t1 drop index a; 884select hex(a) from t1 order by a; 885hex(a) 8860000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F 88700002020000020200000202000002020000020200000202000002020000020200000202000002020 88800002021000020210000202100002021000020210000202100002021000020210000202100002021 889drop table t1; 890CREATE TABLE t1 ( 891status enum('active','passive') character set utf32 collate utf32_general_ci 892NOT NULL default 'passive' 893); 894SHOW CREATE TABLE t1; 895Table Create Table 896t1 CREATE TABLE `t1` ( 897 `status` enum('active','passive') CHARACTER SET utf32 NOT NULL DEFAULT 'passive' 898) ENGINE=MyISAM DEFAULT CHARSET=latin1 899ALTER TABLE t1 ADD a int NOT NULL AFTER status; 900SHOW CREATE TABLE t1; 901Table Create Table 902t1 CREATE TABLE `t1` ( 903 `status` enum('active','passive') CHARACTER SET utf32 NOT NULL DEFAULT 'passive', 904 `a` int(11) NOT NULL 905) ENGINE=MyISAM DEFAULT CHARSET=latin1 906DROP TABLE t1; 907End of 4.1 tests 908CREATE TABLE t1 (a varchar(64) character set utf32, b decimal(10,3)); 909INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0); 910update t1 set b=a; 911SELECT *, hex(a) FROM t1; 912a b hex(a) 9131.1 1.100 000000310000002E00000031 9142.1 2.100 000000320000002E00000031 915DROP TABLE t1; 916create table t1 (utext varchar(20) character set utf32); 917insert into t1 values ("lily"); 918insert into t1 values ("river"); 919prepare stmt from 'select utext from t1 where utext like ?'; 920set @param1='%%'; 921execute stmt using @param1; 922utext 923lily 924river 925execute stmt using @param1; 926utext 927lily 928river 929select utext from t1 where utext like '%%'; 930utext 931lily 932river 933drop table t1; 934deallocate prepare stmt; 935set names latin1; 936set character_set_connection=utf32; 937select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); 938soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb') 939 H000 H4142 I51231 940select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb')); 941hex(soundex('')) hex(soundex('he')) hex(soundex('hello all folks')) hex(soundex('#3556 in bugdb')) 942 00000048000000300000003000000030 0000004800000034000000310000003400000032 000000490000003500000031000000320000003300000031 943select 'mood' sounds like 'mud'; 944'mood' sounds like 'mud' 9451 946select hex(soundex(_utf32 0x000004100000041100000412)); 947hex(soundex(_utf32 0x000004100000041100000412)) 94800000410000000300000003000000030 949select hex(soundex(_utf32 0x000000BF000000C0)); 950hex(soundex(_utf32 0x000000BF000000C0)) 951000000C0000000300000003000000030 952set names latin1; 953create table t1(a blob, b text charset utf32); 954select data_type, character_octet_length, character_maximum_length 955from information_schema.columns where table_name='t1'; 956data_type character_octet_length character_maximum_length 957blob 65535 65535 958text 65535 16383 959drop table t1; 960set names latin1; 961set collation_connection=utf32_general_ci; 962select position('bb' in 'abba'); 963position('bb' in 'abba') 9642 965create table t1 (a varchar(10) character set utf32) engine=heap; 966insert into t1 values ('a'),('A'),('b'),('B'); 967select * from t1 where a='a' order by binary a; 968a 969A 970a 971select hex(min(binary a)),count(*) from t1 group by a; 972hex(min(binary a)) count(*) 97300000041 2 97400000042 2 975drop table t1; 976select char_length('abcd'), octet_length('abcd'); 977char_length('abcd') octet_length('abcd') 9784 16 979select left('abcd',2); 980left('abcd',2) 981ab 982create table t1 (a varchar(10) character set utf32); 983insert into t1 values (_utf32 0x0010FFFF); 984insert into t1 values (_utf32 0x00110000); 985ERROR HY000: Invalid utf32 character string: '001100' 986insert into t1 values (_utf32 0x00110101); 987ERROR HY000: Invalid utf32 character string: '001101' 988insert into t1 values (_utf32 0x01000101); 989ERROR HY000: Invalid utf32 character string: '010001' 990insert into t1 values (_utf32 0x11000101); 991ERROR HY000: Invalid utf32 character string: '110001' 992select hex(a) from t1; 993hex(a) 9940010FFFF 995drop table t1; 996create table t1 (utf32 varchar(2) character set utf32); 997Wrong character with pad 998insert ignore into t1 values (0x110000); 999Warnings: 1000Warning 1366 Incorrect string value: '\x11\x00\x00' for column `test`.`t1`.`utf32` at row 1 1001Wrong chsaracter without pad 1002insert ignore into t1 values (0x00110000); 1003Warnings: 1004Warning 1366 Incorrect string value: '\x00\x11\x00\x00' for column `test`.`t1`.`utf32` at row 1 1005Wrong character with pad followed by another wrong character 1006insert ignore into t1 values (0x11000000110000); 1007Warnings: 1008Warning 1366 Incorrect string value: '\x11\x00\x00\x00\x11\x00...' for column `test`.`t1`.`utf32` at row 1 1009Good character with pad followed by bad character 1010insert ignore into t1 values (0x10000000110000); 1011Warnings: 1012Warning 1366 Incorrect string value: '\x00\x11\x00\x00' for column `test`.`t1`.`utf32` at row 1 1013Good character without pad followed by bad character 1014insert ignore into t1 values (0x0010000000110000); 1015Warnings: 1016Warning 1366 Incorrect string value: '\x00\x11\x00\x00' for column `test`.`t1`.`utf32` at row 1 1017Wrong character with the second byte higher than 0x10 1018insert ignore into t1 values (0x00800037); 1019Warnings: 1020Warning 1366 Incorrect string value: '\x00\x80\x007' for column `test`.`t1`.`utf32` at row 1 1021Wrong character with pad with the second byte higher than 0x10 1022insert ignore into t1 values (0x00800037); 1023Warnings: 1024Warning 1366 Incorrect string value: '\x00\x80\x007' for column `test`.`t1`.`utf32` at row 1 1025drop table t1; 1026select _utf32'a' collate utf32_general_ci = 0xfffd; 1027_utf32'a' collate utf32_general_ci = 0xfffd 10280 1029select hex(concat(_utf32 0x0410 collate utf32_general_ci, 0x61)); 1030hex(concat(_utf32 0x0410 collate utf32_general_ci, 0x61)) 10310000041000000061 1032create table t1 (s1 varchar(5) character set utf32); 1033insert into t1 values (0xfffd); 1034select case when s1 = 0xfffd then 1 else 0 end from t1; 1035case when s1 = 0xfffd then 1 else 0 end 10361 1037select hex(s1) from t1 where s1 = 0xfffd; 1038hex(s1) 10390000FFFD 1040drop table t1; 1041create table t1 (a char(10)) character set utf32; 1042insert into t1 values ('a '); 1043select hex(a) from t1; 1044hex(a) 104500000061 1046drop table t1; 1047select upper('abcd'), lower('ABCD'); 1048upper('abcd') lower('ABCD') 1049ABCD abcd 1050create table t1 (a varchar(10) character set utf32); 1051insert into t1 values (123456); 1052select a, hex(a) from t1; 1053a hex(a) 1054123456 000000310000003200000033000000340000003500000036 1055drop table t1; 1056select hex(soundex('a')); 1057hex(soundex('a')) 105800000041000000300000003000000030 1059create table t1 (a enum ('a','b','c')) character set utf32; 1060insert into t1 values ('1'); 1061select * from t1; 1062a 1063a 1064drop table t1; 1065set names latin1; 1066select hex(conv(convert('123' using utf32), -10, 16)); 1067hex(conv(convert('123' using utf32), -10, 16)) 10683742 1069select hex(conv(convert('123' using utf32), 10, 16)); 1070hex(conv(convert('123' using utf32), 10, 16)) 10713742 1072set names latin1; 1073set character_set_connection=utf32; 1074select 1.1 + '1.2'; 10751.1 + '1.2' 10762.3 1077select 1.1 + '1.2xxx'; 10781.1 + '1.2xxx' 10792.3 1080Warnings: 1081Warning 1292 Truncated incorrect DOUBLE value: '1.2xxx' 1082select left('aaa','1'); 1083left('aaa','1') 1084a 1085# 1086# Testing strtoll10() 1087# 1088SELECT @@character_set_connection; 1089@@character_set_connection 1090utf32 1091SELECT CAST('1' AS UNSIGNED); 1092CAST('1' AS UNSIGNED) 10931 1094SELECT CAST('12' AS UNSIGNED); 1095CAST('12' AS UNSIGNED) 109612 1097SELECT CAST('123' AS UNSIGNED); 1098CAST('123' AS UNSIGNED) 1099123 1100SELECT CAST('1234' AS UNSIGNED); 1101CAST('1234' AS UNSIGNED) 11021234 1103SELECT CAST('12345' AS UNSIGNED); 1104CAST('12345' AS UNSIGNED) 110512345 1106SELECT CAST('123456' AS UNSIGNED); 1107CAST('123456' AS UNSIGNED) 1108123456 1109SELECT CAST('1234567' AS UNSIGNED); 1110CAST('1234567' AS UNSIGNED) 11111234567 1112SELECT CAST('12345678' AS UNSIGNED); 1113CAST('12345678' AS UNSIGNED) 111412345678 1115SELECT CAST('123456789' AS UNSIGNED); 1116CAST('123456789' AS UNSIGNED) 1117123456789 1118SELECT CAST('1234567891' AS UNSIGNED); 1119CAST('1234567891' AS UNSIGNED) 11201234567891 1121SELECT CAST('12345678912' AS UNSIGNED); 1122CAST('12345678912' AS UNSIGNED) 112312345678912 1124SELECT CAST('123456789123' AS UNSIGNED); 1125CAST('123456789123' AS UNSIGNED) 1126123456789123 1127SELECT CAST('1234567891234' AS UNSIGNED); 1128CAST('1234567891234' AS UNSIGNED) 11291234567891234 1130SELECT CAST('12345678912345' AS UNSIGNED); 1131CAST('12345678912345' AS UNSIGNED) 113212345678912345 1133SELECT CAST('123456789123456' AS UNSIGNED); 1134CAST('123456789123456' AS UNSIGNED) 1135123456789123456 1136SELECT CAST('1234567891234567' AS UNSIGNED); 1137CAST('1234567891234567' AS UNSIGNED) 11381234567891234567 1139SELECT CAST('12345678912345678' AS UNSIGNED); 1140CAST('12345678912345678' AS UNSIGNED) 114112345678912345678 1142SELECT CAST('123456789123456789' AS UNSIGNED); 1143CAST('123456789123456789' AS UNSIGNED) 1144123456789123456789 1145SELECT CAST('1234567891234567891' AS UNSIGNED); 1146CAST('1234567891234567891' AS UNSIGNED) 11471234567891234567891 1148SELECT CAST('12345678912345678912' AS UNSIGNED); 1149CAST('12345678912345678912' AS UNSIGNED) 115012345678912345678912 1151SELECT CAST('123456789123456789123' AS UNSIGNED); 1152CAST('123456789123456789123' AS UNSIGNED) 115318446744073709551615 1154Warnings: 1155Warning 1292 Truncated incorrect INTEGER value: '123456789123456789123' 1156SELECT CAST('1x' AS UNSIGNED); 1157CAST('1x' AS UNSIGNED) 11581 1159Warnings: 1160Warning 1292 Truncated incorrect INTEGER value: '1x' 1161SELECT CAST('12x' AS UNSIGNED); 1162CAST('12x' AS UNSIGNED) 116312 1164Warnings: 1165Warning 1292 Truncated incorrect INTEGER value: '12x' 1166SELECT CAST('123x' AS UNSIGNED); 1167CAST('123x' AS UNSIGNED) 1168123 1169Warnings: 1170Warning 1292 Truncated incorrect INTEGER value: '123x' 1171SELECT CAST('1234x' AS UNSIGNED); 1172CAST('1234x' AS UNSIGNED) 11731234 1174Warnings: 1175Warning 1292 Truncated incorrect INTEGER value: '1234x' 1176SELECT CAST('12345x' AS UNSIGNED); 1177CAST('12345x' AS UNSIGNED) 117812345 1179Warnings: 1180Warning 1292 Truncated incorrect INTEGER value: '12345x' 1181SELECT CAST('123456x' AS UNSIGNED); 1182CAST('123456x' AS UNSIGNED) 1183123456 1184Warnings: 1185Warning 1292 Truncated incorrect INTEGER value: '123456x' 1186SELECT CAST('1234567x' AS UNSIGNED); 1187CAST('1234567x' AS UNSIGNED) 11881234567 1189Warnings: 1190Warning 1292 Truncated incorrect INTEGER value: '1234567x' 1191SELECT CAST('12345678x' AS UNSIGNED); 1192CAST('12345678x' AS UNSIGNED) 119312345678 1194Warnings: 1195Warning 1292 Truncated incorrect INTEGER value: '12345678x' 1196SELECT CAST('123456789x' AS UNSIGNED); 1197CAST('123456789x' AS UNSIGNED) 1198123456789 1199Warnings: 1200Warning 1292 Truncated incorrect INTEGER value: '123456789x' 1201SELECT CAST('1234567891x' AS UNSIGNED); 1202CAST('1234567891x' AS UNSIGNED) 12031234567891 1204Warnings: 1205Warning 1292 Truncated incorrect INTEGER value: '1234567891x' 1206SELECT CAST('12345678912x' AS UNSIGNED); 1207CAST('12345678912x' AS UNSIGNED) 120812345678912 1209Warnings: 1210Warning 1292 Truncated incorrect INTEGER value: '12345678912x' 1211SELECT CAST('123456789123x' AS UNSIGNED); 1212CAST('123456789123x' AS UNSIGNED) 1213123456789123 1214Warnings: 1215Warning 1292 Truncated incorrect INTEGER value: '123456789123x' 1216SELECT CAST('1234567891234x' AS UNSIGNED); 1217CAST('1234567891234x' AS UNSIGNED) 12181234567891234 1219Warnings: 1220Warning 1292 Truncated incorrect INTEGER value: '1234567891234x' 1221SELECT CAST('12345678912345x' AS UNSIGNED); 1222CAST('12345678912345x' AS UNSIGNED) 122312345678912345 1224Warnings: 1225Warning 1292 Truncated incorrect INTEGER value: '12345678912345x' 1226SELECT CAST('123456789123456x' AS UNSIGNED); 1227CAST('123456789123456x' AS UNSIGNED) 1228123456789123456 1229Warnings: 1230Warning 1292 Truncated incorrect INTEGER value: '123456789123456x' 1231SELECT CAST('1234567891234567x' AS UNSIGNED); 1232CAST('1234567891234567x' AS UNSIGNED) 12331234567891234567 1234Warnings: 1235Warning 1292 Truncated incorrect INTEGER value: '1234567891234567x' 1236SELECT CAST('12345678912345678x' AS UNSIGNED); 1237CAST('12345678912345678x' AS UNSIGNED) 123812345678912345678 1239Warnings: 1240Warning 1292 Truncated incorrect INTEGER value: '12345678912345678x' 1241SELECT CAST('123456789123456789x' AS UNSIGNED); 1242CAST('123456789123456789x' AS UNSIGNED) 1243123456789123456789 1244Warnings: 1245Warning 1292 Truncated incorrect INTEGER value: '123456789123456789x' 1246SELECT CAST('1234567891234567891x' AS UNSIGNED); 1247CAST('1234567891234567891x' AS UNSIGNED) 12481234567891234567891 1249Warnings: 1250Warning 1292 Truncated incorrect INTEGER value: '1234567891234567891x' 1251SELECT CAST('12345678912345678912x' AS UNSIGNED); 1252CAST('12345678912345678912x' AS UNSIGNED) 125312345678912345678912 1254Warnings: 1255Warning 1292 Truncated incorrect INTEGER value: '12345678912345678912x' 1256SELECT CAST('123456789123456789123x' AS UNSIGNED); 1257CAST('123456789123456789123x' AS UNSIGNED) 125818446744073709551615 1259Warnings: 1260Warning 1292 Truncated incorrect INTEGER value: '123456789123456789123x' 1261create table t1 (a int); 1262insert into t1 values ('-1234.1e2'); 1263insert ignore into t1 values ('-1234.1e2xxxx'); 1264Warnings: 1265Warning 1265 Data truncated for column 'a' at row 1 1266insert into t1 values ('-1234.1e2 '); 1267Warnings: 1268Note 1265 Data truncated for column 'a' at row 1 1269select * from t1; 1270a 1271-123410 1272-123410 1273-123410 1274drop table t1; 1275create table t1 (a int); 1276insert into t1 values ('1 '); 1277Warnings: 1278Note 1265 Data truncated for column 'a' at row 1 1279insert ignore into t1 values ('1 x'); 1280Warnings: 1281Warning 1265 Data truncated for column 'a' at row 1 1282select * from t1; 1283a 12841 12851 1286drop table t1; 1287SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1288create table t1 (a varchar(17000) character set utf32); 1289Warnings: 1290Note 1246 Converting column 'a' from VARCHAR to TEXT 1291show create table t1; 1292Table Create Table 1293t1 CREATE TABLE `t1` ( 1294 `a` mediumtext CHARACTER SET utf32 DEFAULT NULL 1295) ENGINE=MyISAM DEFAULT CHARSET=latin1 1296drop table t1; 1297create table t1 (a varchar(250) character set utf32 primary key); 1298show create table t1; 1299Table Create Table 1300t1 CREATE TABLE `t1` ( 1301 `a` varchar(250) CHARACTER SET utf32 NOT NULL, 1302 PRIMARY KEY (`a`) 1303) ENGINE=MyISAM DEFAULT CHARSET=latin1 1304drop table t1; 1305create table t1 (a varchar(334) character set utf32 primary key); 1306ERROR 42000: Specified key was too long; max key length is 1000 bytes 1307create table t1 (a varchar(333) character set utf32, key(a)); 1308Warnings: 1309Note 1071 Specified key was too long; max key length is 1000 bytes 1310insert into t1 values (repeat('a',333)), (repeat('b',333)); 1311flush tables; 1312check table t1; 1313Table Op Msg_type Msg_text 1314test.t1 check status OK 1315drop table t1; 1316SET collation_connection=utf32_general_ci; 1317# 1318# Bug#32390 Character sets: casting utf32 to/from date doesn't work 1319# 1320CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0; 1321SET time_zone=_latin1'+03:00'; 1322SET timestamp=1216359724; 1323INSERT INTO t1 VALUES (current_date); 1324INSERT INTO t1 VALUES (current_time); 1325INSERT INTO t1 VALUES (current_timestamp); 1326SELECT s1, hex(s1) FROM t1; 1327s1 hex(s1) 13282008-07-18 000000320000003000000030000000380000002D00000030000000370000002D0000003100000038 132908:42:04 00000030000000380000003A00000034000000320000003A0000003000000034 13302008-07-18 08:42:04 000000320000003000000030000000380000002D00000030000000370000002D00000031000000380000002000000030000000380000003A00000034000000320000003A0000003000000034 1331DROP TABLE t1; 1332SET timestamp=0; 1333SET time_zone=default; 1334# 1335# MDEV-5298 Illegal mix of collations on timestamp 1336# 1337SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY); 1338CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY) 1339utf32 1340SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY); 1341COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY) 13424 1343SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY); 1344CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY) 1345binary 1346SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY); 1347COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY) 13485 1349SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)); 1350CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)) 1351utf32 1352SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)); 1353COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)) 13544 1355SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)); 1356CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)) 1357utf32 1358SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)); 1359COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)) 13604 1361SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); 1362CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) 1363utf32 1364SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); 1365COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) 13664 1367SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); 1368HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) 1369000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030 1370SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); 1371CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 1372utf32 1373SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); 1374COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 13754 1376SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); 1377HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 1378000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030 1379CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0; 1380SHOW CREATE TABLE t1; 1381Table Create Table 1382t1 CREATE TABLE `t1` ( 1383 `a` varchar(64) CHARACTER SET utf32 DEFAULT NULL 1384) ENGINE=MyISAM DEFAULT CHARSET=latin1 1385INSERT INTO t1 VALUES (''); 1386SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 1387CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 1388utf32 1389SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 1390COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 13912 1392SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 1393HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 1394000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030 1395SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 1396CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 1397utf32 1398SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 1399COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 14002 1401SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; 1402HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 1403000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030 1404DROP TABLE t1; 1405CREATE TABLE t1 (t TIMESTAMP NOT NULL); 1406INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); 1407SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY; 1408t 14092001-01-01 00:00:00 1410SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY; 1411t 14122001-01-01 00:00:00 1413SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY; 1414t 14152001-01-01 00:00:00 1416SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY; 1417t 14182001-01-01 00:00:00 1419SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY; 1420t 14212001-01-01 00:00:00 1422SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY; 1423t 14242001-01-01 00:00:00 1425DROP TABLE t1; 1426SET NAMES latin1; 1427set collation_connection=utf32_general_ci; 1428drop table if exists t1; 1429create table t1 as 1430select repeat(' ', 64) as s1, repeat(' ',64) as s2 1431union 1432select null, null; 1433show create table t1; 1434Table Create Table 1435t1 CREATE TABLE `t1` ( 1436 `s1` varchar(64) CHARACTER SET utf32 DEFAULT NULL, 1437 `s2` varchar(64) CHARACTER SET utf32 DEFAULT NULL 1438) ENGINE=MyISAM DEFAULT CHARSET=latin1 1439delete from t1; 1440insert into t1 values('aaa','aaa'); 1441insert into t1 values('aaa|qqq','qqq'); 1442insert into t1 values('gheis','^[^a-dXYZ]+$'); 1443insert into t1 values('aab','^aa?b'); 1444insert into t1 values('Baaan','^Ba*n'); 1445insert into t1 values('aaa','qqq|aaa'); 1446insert into t1 values('qqq','qqq|aaa'); 1447insert into t1 values('bbb','qqq|aaa'); 1448insert into t1 values('bbb','qqq'); 1449insert into t1 values('aaa','aba'); 1450insert into t1 values(null,'abc'); 1451insert into t1 values('def',null); 1452insert into t1 values(null,null); 1453insert into t1 values('ghi','ghi['); 1454select HIGH_PRIORITY s1 regexp s2 from t1; 1455s1 regexp s2 14560 14570 14580 14591 14601 14611 14621 14631 14641 14651 1466NULL 1467NULL 1468NULL 1469NULL 1470drop table t1; 1471set names latin1; 1472select hex(char(0x01 using utf32)); 1473hex(char(0x01 using utf32)) 147400000001 1475select hex(char(0x0102 using utf32)); 1476hex(char(0x0102 using utf32)) 147700000102 1478select hex(char(0x010203 using utf32)); 1479hex(char(0x010203 using utf32)) 148000010203 1481select hex(char(0x01020304 using utf32)); 1482hex(char(0x01020304 using utf32)) 1483NULL 1484Warnings: 1485Warning 1300 Invalid utf32 character string: '010203' 1486create table t1 (s1 varchar(1) character set utf32, s2 text character set utf32); 1487create index i on t1 (s1); 1488insert into t1 values (char(256 using utf32), char(256 using utf32)); 1489select hex(s1), hex(s2) from t1; 1490hex(s1) hex(s2) 149100000100 00000100 1492drop table t1; 1493SET collation_connection=utf32_general_ci; 1494CREATE TABLE t1 AS SELECT repeat('a',2) as s1 LIMIT 0; 1495SHOW CREATE TABLE t1; 1496Table Create Table 1497t1 CREATE TABLE `t1` ( 1498 `s1` varchar(2) CHARACTER SET utf32 DEFAULT NULL 1499) ENGINE=MyISAM DEFAULT CHARSET=latin1 1500INSERT INTO t1 VALUES ('ab'),('AE'),('ab'),('AE'); 1501SELECT * FROM t1 ORDER BY s1; 1502s1 1503ab 1504ab 1505AE 1506AE 1507SET max_sort_length=8; 1508Warnings: 1509Warning 1292 Truncated incorrect max_sort_length value: '8' 1510SELECT * FROM t1 ORDER BY s1; 1511s1 1512ab 1513ab 1514AE 1515AE 1516DROP TABLE t1; 1517SET max_sort_length=DEFAULT; 1518SET NAMES latin1; 1519# 1520# Bug#52520 Difference in tinytext utf column metadata 1521# 1522CREATE TABLE t1 ( 1523s1 TINYTEXT CHARACTER SET utf32, 1524s2 TEXT CHARACTER SET utf32, 1525s3 MEDIUMTEXT CHARACTER SET utf32, 1526s4 LONGTEXT CHARACTER SET utf32 1527); 1528SET NAMES utf8mb4, @@character_set_results=NULL; 1529SELECT *, HEX(s1) FROM t1; 1530Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1531def test t1 t1 s1 s1 252 255 0 Y 16 0 60 1532def test t1 t1 s2 s2 252 65535 0 Y 16 0 60 1533def test t1 t1 s3 s3 252 16777215 0 Y 16 0 60 1534def test t1 t1 s4 s4 252 4294967295 0 Y 16 0 60 1535def HEX(s1) 253 8160 0 Y 0 0 45 1536s1 s2 s3 s4 HEX(s1) 1537SET NAMES latin1; 1538SELECT *, HEX(s1) FROM t1; 1539Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1540def test t1 t1 s1 s1 252 63 0 Y 16 0 8 1541def test t1 t1 s2 s2 252 16383 0 Y 16 0 8 1542def test t1 t1 s3 s3 252 4194303 0 Y 16 0 8 1543def test t1 t1 s4 s4 252 1073741823 0 Y 16 0 8 1544def HEX(s1) 253 2040 0 Y 0 0 8 1545s1 s2 s3 s4 HEX(s1) 1546SET NAMES utf8mb4; 1547SELECT *, HEX(s1) FROM t1; 1548Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1549def test t1 t1 s1 s1 252 252 0 Y 16 0 45 1550def test t1 t1 s2 s2 252 65532 0 Y 16 0 45 1551def test t1 t1 s3 s3 252 16777212 0 Y 16 0 45 1552def test t1 t1 s4 s4 252 4294967292 0 Y 16 0 45 1553def HEX(s1) 253 8160 0 Y 0 0 45 1554s1 s2 s3 s4 HEX(s1) 1555CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1; 1556SHOW CREATE TABLE t2; 1557Table Create Table 1558t2 CREATE TABLE `t2` ( 1559 `CONCAT(s1)` varchar(255) CHARACTER SET utf32 DEFAULT NULL 1560) ENGINE=MyISAM DEFAULT CHARSET=latin1 1561DROP TABLE t1, t2; 1562# 1563# Bug#45263 utf32_general_ci, bad effects around CREATE TABLE AS SELECT 1564# 1565SET collation_connection=utf32_general_ci; 1566CREATE TABLE t1 AS SELECT HEX(0x00) AS my_col; 1567SELECT * FROM t1; 1568my_col 156900 1570DROP TABLE t1; 1571# 1572# Bug#32859 Character sets: no warning with non-fitting chariot wheel 1573# 1574CREATE TABLE t1 (utf32 CHAR(5) CHARACTER SET utf32, latin1 CHAR(5) CHARACTER SET latin1); 1575INSERT INTO t1 (utf32) VALUES (0xc581); 1576UPDATE IGNORE t1 SET latin1 = utf32; 1577Warnings: 1578Warning 1366 Incorrect string value: '\x00\x00\xC5\x81' for column `test`.`t1`.`latin1` at row 1 1579DELETE FROM t1; 1580INSERT INTO t1 (utf32) VALUES (0x100cc); 1581UPDATE IGNORE t1 SET latin1 = utf32; 1582Warnings: 1583Warning 1366 Incorrect string value: '\x00\x01\x00\xCC' for column `test`.`t1`.`latin1` at row 1 1584DROP TABLE t1; 1585# 1586# Bug#55912 FORMAT with locale set fails for numbers < 1000 1587# 1588SET collation_connection=utf32_general_ci; 1589CREATE TABLE t1 AS SELECT format(123,2,'no_NO'); 1590SHOW CREATE TABLE t1; 1591Table Create Table 1592t1 CREATE TABLE `t1` ( 1593 `format(123,2,'no_NO')` varchar(17) CHARACTER SET utf32 DEFAULT NULL 1594) ENGINE=MyISAM DEFAULT CHARSET=latin1 1595SELECT * FROM t1; 1596format(123,2,'no_NO') 1597123,00 1598DROP TABLE t1; 1599# 1600# Bug#42511 mysqld: ctype-ucs2.c:2044: my_strnncollsp_utf32: Assertion (tlen % 4) == 0' faied 1601# 1602CREATE TABLE t1 ( 1603b char(250) CHARACTER SET utf32, 1604key (b) 1605) ENGINE=MYISAM; 1606INSERT INTO t1 VALUES ('d'),('f'); 1607SELECT * FROM t1 WHERE b BETWEEN 'a' AND 'z'; 1608b 1609d 1610f 1611DROP TABLE t1; 1612# 1613# Bug#11753363 (Bug#44793) Character sets: case clause, ucs2 or utf32, failure 1614# 1615SELECT CASE _latin1'a' WHEN _utf32'a' THEN 'A' END; 1616CASE _latin1'a' WHEN _utf32'a' THEN 'A' END 1617A 1618SELECT CASE _utf32'a' WHEN _latin1'a' THEN 'A' END; 1619CASE _utf32'a' WHEN _latin1'a' THEN 'A' END 1620A 1621CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf32); 1622INSERT INTO t1 VALUES ('a'); 1623SELECT CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END FROM t1; 1624CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END 1625b 1626DROP TABLE t1; 1627# 1628# Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT 1629# 1630SET NAMES utf8, @@character_set_connection=utf32; 1631SET STATEMENT group_concat_max_len=1024 FOR 1632SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l 1633FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body 1634UNION ALL 1635SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1 1636GROUP BY id 1637ORDER BY l DESC; 1638id l 1639a 256 1640Warnings: 1641Warning 1260 Row 1 was cut by GROUP_CONCAT() 1642# 1643# incorrect charset for val_str_ascii 1644# 1645SELECT '2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second; 1646'2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second 16472010-10-10 10:10:10 1648# 1649# MDEV-5745 analyze MySQL fix for bug#12368495 1650# 1651SELECT CHAR_LENGTH(TRIM(LEADING 0x0000000000 FROM _utf32 0x00000061)); 1652CHAR_LENGTH(TRIM(LEADING 0x0000000000 FROM _utf32 0x00000061)) 16534 1654SELECT CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _utf32 0x00000061)); 1655CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _utf32 0x00000061)) 16564 1657SELECT CHAR_LENGTH(TRIM(LEADING 0x00 FROM _utf32 0x00000061)); 1658CHAR_LENGTH(TRIM(LEADING 0x00 FROM _utf32 0x00000061)) 16591 1660SELECT CHAR_LENGTH(TRIM(TRAILING 0x0000000000 FROM _utf32 0x00000061)); 1661CHAR_LENGTH(TRIM(TRAILING 0x0000000000 FROM _utf32 0x00000061)) 16624 1663SELECT CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _utf32 0x00000061)); 1664CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _utf32 0x00000061)) 16654 1666SELECT CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _utf32 0x00000061)); 1667CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _utf32 0x00000061)) 16683 1669SELECT CHAR_LENGTH(TRIM(BOTH 0x0000000000 FROM _utf32 0x00000061)); 1670CHAR_LENGTH(TRIM(BOTH 0x0000000000 FROM _utf32 0x00000061)) 16714 1672SELECT CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _utf32 0x00000061)); 1673CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _utf32 0x00000061)) 16744 1675SELECT CHAR_LENGTH(TRIM(BOTH 0x61 FROM _utf32 0x00000061)); 1676CHAR_LENGTH(TRIM(BOTH 0x61 FROM _utf32 0x00000061)) 16773 1678SELECT CHAR_LENGTH(TRIM(BOTH 0x00 FROM _utf32 0x00000061)); 1679CHAR_LENGTH(TRIM(BOTH 0x00 FROM _utf32 0x00000061)) 16801 1681select hex(lower(cast(0xffff0000 as char character set utf32))) as c; 1682c 16830000003F0000003F0000003F0000003F 1684Warnings: 1685Warning 1300 Invalid utf32 character string: '\xFF\xFF\x00\x00' 1686# 1687# MDEV-11685: sql_mode can't be set with non-ascii connection charset 1688# 1689SET character_set_connection=utf32; 1690SET sql_mode='NO_ENGINE_SUBSTITUTION'; 1691SELECT @@sql_mode; 1692@@sql_mode 1693NO_ENGINE_SUBSTITUTION 1694SET sql_mode=DEFAULT; 1695SET NAMES utf8; 1696# 1697# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 1698# 1699set sql_mode=''; 1700CREATE TABLE t1 (c1 VARCHAR(16383) CHARACTER SET utf32); 1701DESCRIBE t1; 1702Field Type Null Key Default Extra 1703c1 varchar(16383) YES NULL 1704DROP TABLE t1; 1705CREATE TABLE t1 (c1 VARCHAR(16384) CHARACTER SET utf32); 1706Warnings: 1707Note 1246 Converting column 'c1' from VARCHAR to TEXT 1708DESCRIBE t1; 1709Field Type Null Key Default Extra 1710c1 mediumtext YES NULL 1711DROP TABLE t1; 1712set sql_mode=default; 1713# 1714# End of 5.5 tests 1715# 1716# 1717# Start of 5.6 tests 1718# 1719# 1720# WL#3664 WEIGHT_STRING 1721# 1722set collation_connection=utf32_general_ci; 1723select @@collation_connection; 1724@@collation_connection 1725utf32_general_ci 1726CREATE TABLE t1 AS SELECT 'a' AS a; 1727SHOW CREATE TABLE t1; 1728Table Create Table 1729t1 CREATE TABLE `t1` ( 1730 `a` varchar(1) CHARACTER SET utf32 NOT NULL 1731) ENGINE=MyISAM DEFAULT CHARSET=latin1 1732CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; 1733SHOW CREATE TABLE t2; 1734Table Create Table 1735t2 CREATE TABLE `t2` ( 1736 `ws` varbinary(2) DEFAULT NULL 1737) ENGINE=MyISAM DEFAULT CHARSET=latin1 1738SELECT HEX(WEIGHT_STRING(a)) FROM t1; 1739HEX(WEIGHT_STRING(a)) 17400041 1741SELECT HEX(ws) FROM t2; 1742HEX(ws) 17430041 1744DROP TABLE t2; 1745DROP TABLE t1; 1746CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a; 1747SHOW CREATE TABLE t1; 1748Table Create Table 1749t1 CREATE TABLE `t1` ( 1750 `a` varchar(5) CHARACTER SET utf32 DEFAULT NULL 1751) ENGINE=MyISAM DEFAULT CHARSET=latin1 1752CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; 1753SHOW CREATE TABLE t2; 1754Table Create Table 1755t2 CREATE TABLE `t2` ( 1756 `ws` varbinary(10) DEFAULT NULL 1757) ENGINE=MyISAM DEFAULT CHARSET=latin1 1758SELECT HEX(WEIGHT_STRING(a)) FROM t1; 1759HEX(WEIGHT_STRING(a)) 176000410041004100410041 1761SELECT HEX(ws) FROM t2; 1762HEX(ws) 176300410041004100410041 1764DROP TABLE t2; 1765CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1; 1766SHOW CREATE TABLE t2; 1767Table Create Table 1768t2 CREATE TABLE `t2` ( 1769 `ws` varbinary(6) DEFAULT NULL 1770) ENGINE=MyISAM DEFAULT CHARSET=latin1 1771SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1; 1772HEX(WEIGHT_STRING(a AS CHAR(3))) 1773004100410041 1774SELECT HEX(ws) FROM t2; 1775HEX(ws) 1776004100410041 1777DROP TABLE t2; 1778CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1; 1779SHOW CREATE TABLE t2; 1780Table Create Table 1781t2 CREATE TABLE `t2` ( 1782 `ws` varbinary(20) DEFAULT NULL 1783) ENGINE=MyISAM DEFAULT CHARSET=latin1 1784SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 1785HEX(WEIGHT_STRING(a AS CHAR(10))) 17860041004100410041004100200020002000200020 1787SELECT HEX(ws) FROM t2; 1788HEX(ws) 17890041004100410041004100200020002000200020 1790DROP TABLE t2; 1791DROP TABLE t1; 1792select hex(weight_string('a')); 1793hex(weight_string('a')) 17940041 1795select hex(weight_string('A')); 1796hex(weight_string('A')) 17970041 1798select hex(weight_string('abc')); 1799hex(weight_string('abc')) 1800004100420043 1801select hex(weight_string('abc' as char(2))); 1802hex(weight_string('abc' as char(2))) 180300410042 1804select hex(weight_string('abc' as char(3))); 1805hex(weight_string('abc' as char(3))) 1806004100420043 1807select hex(weight_string('abc' as char(5))); 1808hex(weight_string('abc' as char(5))) 180900410042004300200020 1810select hex(weight_string('abc', 1, 2, 0xC0)); 1811hex(weight_string('abc', 1, 2, 0xC0)) 181200 1813select hex(weight_string('abc', 2, 2, 0xC0)); 1814hex(weight_string('abc', 2, 2, 0xC0)) 18150041 1816select hex(weight_string('abc', 3, 2, 0xC0)); 1817hex(weight_string('abc', 3, 2, 0xC0)) 1818004100 1819select hex(weight_string('abc', 4, 2, 0xC0)); 1820hex(weight_string('abc', 4, 2, 0xC0)) 182100410042 1822select hex(weight_string('abc', 5, 2, 0xC0)); 1823hex(weight_string('abc', 5, 2, 0xC0)) 18240041004200 1825select hex(weight_string('abc',25, 2, 0xC0)); 1826hex(weight_string('abc',25, 2, 0xC0)) 182700410042002000200020002000200020002000200020002000 1828select hex(weight_string('abc', 1, 3, 0xC0)); 1829hex(weight_string('abc', 1, 3, 0xC0)) 183000 1831select hex(weight_string('abc', 2, 3, 0xC0)); 1832hex(weight_string('abc', 2, 3, 0xC0)) 18330041 1834select hex(weight_string('abc', 3, 3, 0xC0)); 1835hex(weight_string('abc', 3, 3, 0xC0)) 1836004100 1837select hex(weight_string('abc', 4, 3, 0xC0)); 1838hex(weight_string('abc', 4, 3, 0xC0)) 183900410042 1840select hex(weight_string('abc', 5, 3, 0xC0)); 1841hex(weight_string('abc', 5, 3, 0xC0)) 18420041004200 1843select hex(weight_string('abc',25, 3, 0xC0)); 1844hex(weight_string('abc',25, 3, 0xC0)) 184500410042004300200020002000200020002000200020002000 1846select hex(weight_string('abc', 1, 4, 0xC0)); 1847hex(weight_string('abc', 1, 4, 0xC0)) 184800 1849select hex(weight_string('abc', 2, 4, 0xC0)); 1850hex(weight_string('abc', 2, 4, 0xC0)) 18510041 1852select hex(weight_string('abc', 3, 4, 0xC0)); 1853hex(weight_string('abc', 3, 4, 0xC0)) 1854004100 1855select hex(weight_string('abc', 4, 4, 0xC0)); 1856hex(weight_string('abc', 4, 4, 0xC0)) 185700410042 1858select hex(weight_string('abc', 5, 4, 0xC0)); 1859hex(weight_string('abc', 5, 4, 0xC0)) 18600041004200 1861select hex(weight_string('abc',25, 4, 0xC0)); 1862hex(weight_string('abc',25, 4, 0xC0)) 186300410042004300200020002000200020002000200020002000 1864select @@collation_connection; 1865@@collation_connection 1866utf32_general_ci 1867select hex(weight_string(cast(_latin1 0x80 as char))); 1868hex(weight_string(cast(_latin1 0x80 as char))) 186920AC 1870select hex(weight_string(cast(_latin1 0x808080 as char))); 1871hex(weight_string(cast(_latin1 0x808080 as char))) 187220AC20AC20AC 1873select hex(weight_string(cast(_latin1 0x808080 as char) as char(2))); 1874hex(weight_string(cast(_latin1 0x808080 as char) as char(2))) 187520AC20AC 1876select hex(weight_string(cast(_latin1 0x808080 as char) as char(3))); 1877hex(weight_string(cast(_latin1 0x808080 as char) as char(3))) 187820AC20AC20AC 1879select hex(weight_string(cast(_latin1 0x808080 as char) as char(5))); 1880hex(weight_string(cast(_latin1 0x808080 as char) as char(5))) 188120AC20AC20AC00200020 1882select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)); 1883hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)) 188420 1885select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)); 1886hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)) 188720AC 1888select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)); 1889hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)) 189020AC20 1891select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)); 1892hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)) 189320AC20AC 1894select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)); 1895hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)) 189620AC20AC00 1897select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)); 1898hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)) 189920AC20AC002000200020002000200020002000200020002000 1900select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)); 1901hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)) 190220 1903select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)); 1904hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)) 190520AC 1906select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)); 1907hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)) 190820AC20 1909select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)); 1910hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)) 191120AC20AC 1912select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)); 1913hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)) 191420AC20AC20 1915select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)); 1916hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)) 191720AC20AC20AC00200020002000200020002000200020002000 1918select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)); 1919hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)) 192020 1921select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)); 1922hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)) 192320AC 1924select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)); 1925hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)) 192620AC20 1927select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)); 1928hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)) 192920AC20AC 1930select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)); 1931hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)) 193220AC20AC20 1933select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)); 1934hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)) 193520AC20AC20AC00200020002000200020002000200020002000 1936select hex(weight_string(_utf32 0x10000)); 1937hex(weight_string(_utf32 0x10000)) 1938FFFD 1939select hex(weight_string(_utf32 0x10001)); 1940hex(weight_string(_utf32 0x10001)) 1941FFFD 1942select @@collation_connection; 1943@@collation_connection 1944utf32_general_ci 1945select hex(weight_string('a' LEVEL 1)); 1946hex(weight_string('a' LEVEL 1)) 19470041 1948select hex(weight_string('A' LEVEL 1)); 1949hex(weight_string('A' LEVEL 1)) 19500041 1951select hex(weight_string('abc' LEVEL 1)); 1952hex(weight_string('abc' LEVEL 1)) 1953004100420043 1954select hex(weight_string('abc' as char(2) LEVEL 1)); 1955hex(weight_string('abc' as char(2) LEVEL 1)) 195600410042 1957select hex(weight_string('abc' as char(3) LEVEL 1)); 1958hex(weight_string('abc' as char(3) LEVEL 1)) 1959004100420043 1960select hex(weight_string('abc' as char(5) LEVEL 1)); 1961hex(weight_string('abc' as char(5) LEVEL 1)) 196200410042004300200020 1963select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)); 1964hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)) 196520002000430042004100 1966select hex(weight_string('abc' as char(5) LEVEL 1 DESC)); 1967hex(weight_string('abc' as char(5) LEVEL 1 DESC)) 1968FFBEFFBDFFBCFFDFFFDF 1969select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)); 1970hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)) 1971DFFFDFFFBCFFBDFFBEFF 1972set collation_connection=utf32_bin; 1973select @@collation_connection; 1974@@collation_connection 1975utf32_bin 1976CREATE TABLE t1 AS SELECT 'a' AS a; 1977SHOW CREATE TABLE t1; 1978Table Create Table 1979t1 CREATE TABLE `t1` ( 1980 `a` varchar(1) CHARACTER SET utf32 COLLATE utf32_bin NOT NULL 1981) ENGINE=MyISAM DEFAULT CHARSET=latin1 1982CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; 1983SHOW CREATE TABLE t2; 1984Table Create Table 1985t2 CREATE TABLE `t2` ( 1986 `ws` varbinary(3) DEFAULT NULL 1987) ENGINE=MyISAM DEFAULT CHARSET=latin1 1988SELECT HEX(WEIGHT_STRING(a)) FROM t1; 1989HEX(WEIGHT_STRING(a)) 1990000061 1991SELECT HEX(ws) FROM t2; 1992HEX(ws) 1993000061 1994DROP TABLE t2; 1995DROP TABLE t1; 1996CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a; 1997SHOW CREATE TABLE t1; 1998Table Create Table 1999t1 CREATE TABLE `t1` ( 2000 `a` varchar(5) CHARACTER SET utf32 COLLATE utf32_bin DEFAULT NULL 2001) ENGINE=MyISAM DEFAULT CHARSET=latin1 2002CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; 2003SHOW CREATE TABLE t2; 2004Table Create Table 2005t2 CREATE TABLE `t2` ( 2006 `ws` varbinary(15) DEFAULT NULL 2007) ENGINE=MyISAM DEFAULT CHARSET=latin1 2008SELECT HEX(WEIGHT_STRING(a)) FROM t1; 2009HEX(WEIGHT_STRING(a)) 2010000061000061000061000061000061 2011SELECT HEX(ws) FROM t2; 2012HEX(ws) 2013000061000061000061000061000061 2014DROP TABLE t2; 2015CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1; 2016SHOW CREATE TABLE t2; 2017Table Create Table 2018t2 CREATE TABLE `t2` ( 2019 `ws` varbinary(9) DEFAULT NULL 2020) ENGINE=MyISAM DEFAULT CHARSET=latin1 2021SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1; 2022HEX(WEIGHT_STRING(a AS CHAR(3))) 2023000061000061000061 2024SELECT HEX(ws) FROM t2; 2025HEX(ws) 2026000061000061000061 2027DROP TABLE t2; 2028CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1; 2029SHOW CREATE TABLE t2; 2030Table Create Table 2031t2 CREATE TABLE `t2` ( 2032 `ws` varbinary(30) DEFAULT NULL 2033) ENGINE=MyISAM DEFAULT CHARSET=latin1 2034SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 2035HEX(WEIGHT_STRING(a AS CHAR(10))) 2036000061000061000061000061000061000020000020000020000020000020 2037SELECT HEX(ws) FROM t2; 2038HEX(ws) 2039000061000061000061000061000061000020000020000020000020000020 2040DROP TABLE t2; 2041DROP TABLE t1; 2042select hex(weight_string('a')); 2043hex(weight_string('a')) 2044000061 2045select hex(weight_string('A')); 2046hex(weight_string('A')) 2047000041 2048select hex(weight_string('abc')); 2049hex(weight_string('abc')) 2050000061000062000063 2051select hex(weight_string('abc' as char(2))); 2052hex(weight_string('abc' as char(2))) 2053000061000062 2054select hex(weight_string('abc' as char(3))); 2055hex(weight_string('abc' as char(3))) 2056000061000062000063 2057select hex(weight_string('abc' as char(5))); 2058hex(weight_string('abc' as char(5))) 2059000061000062000063000020000020 2060select hex(weight_string('abc', 1, 2, 0xC0)); 2061hex(weight_string('abc', 1, 2, 0xC0)) 206200 2063select hex(weight_string('abc', 2, 2, 0xC0)); 2064hex(weight_string('abc', 2, 2, 0xC0)) 20650000 2066select hex(weight_string('abc', 3, 2, 0xC0)); 2067hex(weight_string('abc', 3, 2, 0xC0)) 2068000061 2069select hex(weight_string('abc', 4, 2, 0xC0)); 2070hex(weight_string('abc', 4, 2, 0xC0)) 207100006100 2072select hex(weight_string('abc', 5, 2, 0xC0)); 2073hex(weight_string('abc', 5, 2, 0xC0)) 20740000610000 2075select hex(weight_string('abc',25, 2, 0xC0)); 2076hex(weight_string('abc',25, 2, 0xC0)) 207700006100006200002000002000002000002000002000002000 2078select hex(weight_string('abc', 1, 3, 0xC0)); 2079hex(weight_string('abc', 1, 3, 0xC0)) 208000 2081select hex(weight_string('abc', 2, 3, 0xC0)); 2082hex(weight_string('abc', 2, 3, 0xC0)) 20830000 2084select hex(weight_string('abc', 3, 3, 0xC0)); 2085hex(weight_string('abc', 3, 3, 0xC0)) 2086000061 2087select hex(weight_string('abc', 4, 3, 0xC0)); 2088hex(weight_string('abc', 4, 3, 0xC0)) 208900006100 2090select hex(weight_string('abc', 5, 3, 0xC0)); 2091hex(weight_string('abc', 5, 3, 0xC0)) 20920000610000 2093select hex(weight_string('abc',25, 3, 0xC0)); 2094hex(weight_string('abc',25, 3, 0xC0)) 209500006100006200006300002000002000002000002000002000 2096select hex(weight_string('abc', 1, 4, 0xC0)); 2097hex(weight_string('abc', 1, 4, 0xC0)) 209800 2099select hex(weight_string('abc', 2, 4, 0xC0)); 2100hex(weight_string('abc', 2, 4, 0xC0)) 21010000 2102select hex(weight_string('abc', 3, 4, 0xC0)); 2103hex(weight_string('abc', 3, 4, 0xC0)) 2104000061 2105select hex(weight_string('abc', 4, 4, 0xC0)); 2106hex(weight_string('abc', 4, 4, 0xC0)) 210700006100 2108select hex(weight_string('abc', 5, 4, 0xC0)); 2109hex(weight_string('abc', 5, 4, 0xC0)) 21100000610000 2111select hex(weight_string('abc',25, 4, 0xC0)); 2112hex(weight_string('abc',25, 4, 0xC0)) 211300006100006200006300002000002000002000002000002000 2114select @@collation_connection; 2115@@collation_connection 2116utf32_bin 2117select hex(weight_string(cast(_latin1 0x80 as char))); 2118hex(weight_string(cast(_latin1 0x80 as char))) 21190020AC 2120select hex(weight_string(cast(_latin1 0x808080 as char))); 2121hex(weight_string(cast(_latin1 0x808080 as char))) 21220020AC0020AC0020AC 2123select hex(weight_string(cast(_latin1 0x808080 as char) as char(2))); 2124hex(weight_string(cast(_latin1 0x808080 as char) as char(2))) 21250020AC0020AC 2126select hex(weight_string(cast(_latin1 0x808080 as char) as char(3))); 2127hex(weight_string(cast(_latin1 0x808080 as char) as char(3))) 21280020AC0020AC0020AC 2129select hex(weight_string(cast(_latin1 0x808080 as char) as char(5))); 2130hex(weight_string(cast(_latin1 0x808080 as char) as char(5))) 21310020AC0020AC0020AC000020000020 2132select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)); 2133hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)) 213400 2135select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)); 2136hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)) 21370020 2138select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)); 2139hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)) 21400020AC 2141select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)); 2142hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)) 21430020AC00 2144select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)); 2145hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)) 21460020AC0020 2147select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)); 2148hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)) 21490020AC0020AC00002000002000002000002000002000002000 2150select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)); 2151hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)) 215200 2153select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)); 2154hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)) 21550020 2156select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)); 2157hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)) 21580020AC 2159select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)); 2160hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)) 21610020AC00 2162select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)); 2163hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)) 21640020AC0020 2165select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)); 2166hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)) 21670020AC0020AC0020AC00002000002000002000002000002000 2168select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)); 2169hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)) 217000 2171select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)); 2172hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)) 21730020 2174select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)); 2175hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)) 21760020AC 2177select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)); 2178hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)) 21790020AC00 2180select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)); 2181hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)) 21820020AC0020 2183select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)); 2184hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)) 21850020AC0020AC0020AC00002000002000002000002000002000 2186select @@collation_connection; 2187@@collation_connection 2188utf32_bin 2189select hex(weight_string('a' LEVEL 1)); 2190hex(weight_string('a' LEVEL 1)) 2191000061 2192select hex(weight_string('A' LEVEL 1)); 2193hex(weight_string('A' LEVEL 1)) 2194000041 2195select hex(weight_string('abc' LEVEL 1)); 2196hex(weight_string('abc' LEVEL 1)) 2197000061000062000063 2198select hex(weight_string('abc' as char(2) LEVEL 1)); 2199hex(weight_string('abc' as char(2) LEVEL 1)) 2200000061000062 2201select hex(weight_string('abc' as char(3) LEVEL 1)); 2202hex(weight_string('abc' as char(3) LEVEL 1)) 2203000061000062000063 2204select hex(weight_string('abc' as char(5) LEVEL 1)); 2205hex(weight_string('abc' as char(5) LEVEL 1)) 2206000061000062000063000020000020 2207select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)); 2208hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)) 2209200000200000630000620000610000 2210select hex(weight_string('abc' as char(5) LEVEL 1 DESC)); 2211hex(weight_string('abc' as char(5) LEVEL 1 DESC)) 2212FFFF9EFFFF9DFFFF9CFFFFDFFFFFDF 2213select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)); 2214hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)) 2215DFFFFFDFFFFF9CFFFF9DFFFF9EFFFF 2216# 2217# End of 5.6 tests 2218# 2219# 2220# Start of 10.0 tests 2221# 2222# 2223# MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context 2224# 2225SELECT CONCAT(CONVERT('pi=' USING utf32),PI()) AS PI; 2226PI 2227pi=3.141593 2228# 2229# MDEV-6666 Malformed result for CONCAT(utf8_column, binary_string) 2230# 2231SET NAMES utf8mb4; 2232CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32); 2233INSERT INTO t1 VALUES ('a'); 2234SELECT CONCAT(a,0x20FFFF) FROM t1; 2235ERROR HY000: Invalid utf32 character string: '0020FF' 2236SELECT CONCAT(a,0x010000) FROM t1; 2237CONCAT(a,0x010000) 2238a�� 2239SELECT CONCAT(a,0x00FF) FROM t1; 2240CONCAT(a,0x00FF) 2241aÿ 2242DROP TABLE t1; 2243SELECT CONCAT(_utf32'a' COLLATE utf32_unicode_ci, _binary 0x20FFFF); 2244ERROR HY000: Invalid utf32 character string: '0020FF' 2245PREPARE stmt FROM "SELECT CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?)"; 2246SET @arg00=_binary 0x20FFFF; 2247EXECUTE stmt USING @arg00; 2248ERROR HY000: Invalid utf32 character string: '0020FF' 2249SET @arg00=_binary 0x010000; 2250EXECUTE stmt USING @arg00; 2251CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) 2252a�� 2253SET @arg00=_binary 0x00FF; 2254EXECUTE stmt USING @arg00; 2255CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) 2256aÿ 2257DEALLOCATE PREPARE stmt; 2258SET NAMEs utf8, character_set_connection=utf32; 2259# 2260# MDEV-13118 Wrong results with LOWER and UPPER and subquery 2261# 2262SET @save_optimizer_switch=@@optimizer_switch; 2263SET optimizer_switch=_latin1'derived_merge=on'; 2264CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; 2265SHOW CREATE TABLE t1; 2266Table Create Table 2267t1 CREATE TABLE `t1` ( 2268 `t` varchar(10) CHARACTER SET utf32 DEFAULT NULL 2269) ENGINE=MyISAM DEFAULT CHARSET=latin1 2270INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); 2271SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; 2272c2 2273abcdefghi-abcdefghi 2274abcdefghi-abcdefghi 2275SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; 2276c2 2277ABCDEFGHI-ABCDEFGHI 2278ABCDEFGHI-ABCDEFGHI 2279DROP TABLE t1; 2280SET optimizer_switch=@save_optimizer_switch; 2281# 2282# End of 10.0 tests 2283# 2284# 2285# Start of 10.1 tests 2286# 2287# 2288# MDEV-8419 utf32: compare broken bytes as "greater than any non-broken character" 2289# 2290CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32, KEY(a)); 2291INSERT INTO t1 VALUES (0x10000),(0x10001),(0x10002); 2292SELECT COUNT(DISTINCT a) FROM t1; 2293COUNT(DISTINCT a) 22941 2295DROP TABLE t1; 2296SELECT _utf32 0x10001=_utf32 0x10002; 2297_utf32 0x10001=_utf32 0x10002 22981 2299# 2300# MDEV-9178 Wrong result for CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED) 2301# 2302SET NAMES utf8; 2303SELECT CAST(CONVERT('1IJ3' USING utf32) AS SIGNED); 2304CAST(CONVERT('1IJ3' USING utf32) AS SIGNED) 23051 2306Warnings: 2307Warning 1292 Truncated incorrect INTEGER value: '1IJ3' 2308# 2309# End of 10.1 tests 2310# 2311# 2312# Start of 10.2 tests 2313# 2314# 2315# MDEV-9711 NO PAD Collatons 2316# 2317SET character_set_connection=utf32; 2318SET DEFAULT_STORAGE_ENGINE=MyISAM; 2319# 2320# Start of ctype_pad.inc 2321# 2322# 2323# Unique indexes 2324# 2325CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf32_general_nopad_ci'; 2326SHOW CREATE TABLE t1; 2327Table Create Table 2328t1 CREATE TABLE `t1` ( 2329 `a` varchar(10) COLLATE utf32_general_nopad_ci NOT NULL, 2330 PRIMARY KEY (`a`) 2331) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_general_nopad_ci 2332INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); 2333SELECT HEX(a), a FROM t1 ORDER BY a; 2334HEX(a) a 23350000002000000061 a 2336000000200000006100000020 a 233700000061000000200000002000000020 a 2338000000610000006200000063 abc 23390000006100000062000000630000002000000020 abc 2340SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; 2341HEX(a) a 23420000002000000061 a 2343000000200000006100000020 a 234400000061000000200000002000000020 a 2345000000610000006200000063 abc 23460000006100000062000000630000002000000020 abc 2347SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; 2348HEX(a) a 23490000006100000062000000630000002000000020 abc 2350000000610000006200000063 abc 235100000061000000200000002000000020 a 2352000000200000006100000020 a 23530000002000000061 a 2354# 2355# UNION 2356# 2357CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf32_general_nopad_ci'; 2358INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); 2359SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; 2360HEX(a) a 23610000002000000061 a 2362000000200000006100000020 a 23630000006100000020 a 236400000061000000200000002000000020 a 2365000000610000006200000063 abc 236600000061000000620000006300000020 abc 23670000006100000062000000630000002000000020 abc 2368DROP TABLE t1; 2369DROP TABLE t2; 2370# 2371# DISTINCT, COUNT, MAX 2372# 2373CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_general_nopad_ci'; 2374INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); 2375SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; 2376HEX(a) a 23770000002000000061 a 2378000000200000006100000020 a 237900000061 a 2380000000610000002000000020 a 238100000061000000200000002000000020 a 2382SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; 2383COUNT(DISTINCT a) 23845 2385SELECT HEX(MAX(a)), MAX(a) FROM t1; 2386HEX(MAX(a)) MAX(a) 238700000061000000200000002000000020 a 2388# 2389# GROUP BY 2390# 2391CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf32_general_nopad_ci'; 2392INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); 2393SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; 2394HEX(a) cnt 23950000006100000061 1 239600000061000000610000002000000020 1 23970000006100000062 2 2398DROP TABLE t2; 2399# 2400# Weights 2401# 2402SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 2403HEX(WEIGHT_STRING(a AS CHAR(10))) 24040041000000000000000000000000000000000000 24050041002000200000000000000000000000000000 24060020004100000000000000000000000000000000 24070020004100200000000000000000000000000000 24080041002000200020000000000000000000000000 2409DROP TABLE t1; 2410# 2411# IF, CASE, LEAST 2412# 2413SELECT IF('abc' COLLATE 'utf32_general_nopad_ci' = 'abc ', 'pad', 'nopad'); 2414IF('abc' COLLATE 'utf32_general_nopad_ci' = 'abc ', 'pad', 'nopad') 2415nopad 2416SELECT CASE 'abc' COLLATE 'utf32_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; 2417CASE 'abc' COLLATE 'utf32_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END 2418nopad 2419SELECT CASE WHEN 'abc' COLLATE 'utf32_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; 2420CASE WHEN 'abc' COLLATE 'utf32_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END 2421nopad 2422SELECT HEX(LEAST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')); 2423HEX(LEAST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')) 242400000061000000620000006300000020 2425SELECT HEX(GREATEST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')); 2426HEX(GREATEST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')) 24270000006100000062000000630000002000000020 2428# 2429# Collation mix 2430# 2431CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_general_ci'; 2432INSERT INTO t1 VALUES ('a'),('a '); 2433SELECT COUNT(*) FROM t1 WHERE a='a'; 2434COUNT(*) 24352 2436SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_ci'; 2437COUNT(*) 24382 2439SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_nopad_ci'; 2440COUNT(*) 24411 2442ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf32_general_nopad_ci'; 2443SELECT COUNT(*) FROM t1 WHERE a='a'; 2444COUNT(*) 24451 2446SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_ci'; 2447COUNT(*) 24482 2449SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_nopad_ci'; 2450COUNT(*) 24511 2452DROP TABLE t1; 2453# 2454# End of ctype_pad.inc 2455# 2456SET DEFAULT_STORAGE_ENGINE=HEAP; 2457# 2458# Start of ctype_pad.inc 2459# 2460# 2461# Unique indexes 2462# 2463CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf32_general_nopad_ci'; 2464SHOW CREATE TABLE t1; 2465Table Create Table 2466t1 CREATE TABLE `t1` ( 2467 `a` varchar(10) COLLATE utf32_general_nopad_ci NOT NULL, 2468 PRIMARY KEY (`a`) 2469) ENGINE=MEMORY DEFAULT CHARSET=utf32 COLLATE=utf32_general_nopad_ci 2470INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); 2471SELECT HEX(a), a FROM t1 ORDER BY a; 2472HEX(a) a 24730000002000000061 a 2474000000200000006100000020 a 247500000061000000200000002000000020 a 2476000000610000006200000063 abc 24770000006100000062000000630000002000000020 abc 2478SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; 2479HEX(a) a 24800000002000000061 a 2481000000200000006100000020 a 248200000061000000200000002000000020 a 2483000000610000006200000063 abc 24840000006100000062000000630000002000000020 abc 2485SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; 2486HEX(a) a 24870000006100000062000000630000002000000020 abc 2488000000610000006200000063 abc 248900000061000000200000002000000020 a 2490000000200000006100000020 a 24910000002000000061 a 2492# 2493# UNION 2494# 2495CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf32_general_nopad_ci'; 2496INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); 2497SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; 2498HEX(a) a 24990000002000000061 a 2500000000200000006100000020 a 25010000006100000020 a 250200000061000000200000002000000020 a 2503000000610000006200000063 abc 250400000061000000620000006300000020 abc 25050000006100000062000000630000002000000020 abc 2506DROP TABLE t1; 2507DROP TABLE t2; 2508# 2509# DISTINCT, COUNT, MAX 2510# 2511CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_general_nopad_ci'; 2512INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); 2513SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; 2514HEX(a) a 25150000002000000061 a 2516000000200000006100000020 a 251700000061 a 2518000000610000002000000020 a 251900000061000000200000002000000020 a 2520SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; 2521COUNT(DISTINCT a) 25225 2523SELECT HEX(MAX(a)), MAX(a) FROM t1; 2524HEX(MAX(a)) MAX(a) 252500000061000000200000002000000020 a 2526# 2527# GROUP BY 2528# 2529CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf32_general_nopad_ci'; 2530INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); 2531SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; 2532HEX(a) cnt 25330000006100000061 1 253400000061000000610000002000000020 1 25350000006100000062 2 2536DROP TABLE t2; 2537# 2538# Weights 2539# 2540SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 2541HEX(WEIGHT_STRING(a AS CHAR(10))) 25420041000000000000000000000000000000000000 25430041002000200000000000000000000000000000 25440020004100000000000000000000000000000000 25450020004100200000000000000000000000000000 25460041002000200020000000000000000000000000 2547DROP TABLE t1; 2548# 2549# IF, CASE, LEAST 2550# 2551SELECT IF('abc' COLLATE 'utf32_general_nopad_ci' = 'abc ', 'pad', 'nopad'); 2552IF('abc' COLLATE 'utf32_general_nopad_ci' = 'abc ', 'pad', 'nopad') 2553nopad 2554SELECT CASE 'abc' COLLATE 'utf32_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; 2555CASE 'abc' COLLATE 'utf32_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END 2556nopad 2557SELECT CASE WHEN 'abc' COLLATE 'utf32_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; 2558CASE WHEN 'abc' COLLATE 'utf32_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END 2559nopad 2560SELECT HEX(LEAST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')); 2561HEX(LEAST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')) 256200000061000000620000006300000020 2563SELECT HEX(GREATEST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')); 2564HEX(GREATEST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')) 25650000006100000062000000630000002000000020 2566# 2567# Collation mix 2568# 2569CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_general_ci'; 2570INSERT INTO t1 VALUES ('a'),('a '); 2571SELECT COUNT(*) FROM t1 WHERE a='a'; 2572COUNT(*) 25732 2574SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_ci'; 2575COUNT(*) 25762 2577SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_nopad_ci'; 2578COUNT(*) 25791 2580ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf32_general_nopad_ci'; 2581SELECT COUNT(*) FROM t1 WHERE a='a'; 2582COUNT(*) 25831 2584SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_ci'; 2585COUNT(*) 25862 2587SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_nopad_ci'; 2588COUNT(*) 25891 2590DROP TABLE t1; 2591# 2592# End of ctype_pad.inc 2593# 2594SET DEFAULT_STORAGE_ENGINE=Default; 2595SET DEFAULT_STORAGE_ENGINE=MyISAM; 2596# 2597# Start of ctype_pad.inc 2598# 2599# 2600# Unique indexes 2601# 2602CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf32_nopad_bin'; 2603SHOW CREATE TABLE t1; 2604Table Create Table 2605t1 CREATE TABLE `t1` ( 2606 `a` varchar(10) COLLATE utf32_nopad_bin NOT NULL, 2607 PRIMARY KEY (`a`) 2608) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_nopad_bin 2609INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); 2610SELECT HEX(a), a FROM t1 ORDER BY a; 2611HEX(a) a 26120000002000000061 a 2613000000200000006100000020 a 261400000061000000200000002000000020 a 2615000000610000006200000063 abc 26160000006100000062000000630000002000000020 abc 2617SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; 2618HEX(a) a 26190000002000000061 a 2620000000200000006100000020 a 262100000061000000200000002000000020 a 2622000000610000006200000063 abc 26230000006100000062000000630000002000000020 abc 2624SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; 2625HEX(a) a 26260000006100000062000000630000002000000020 abc 2627000000610000006200000063 abc 262800000061000000200000002000000020 a 2629000000200000006100000020 a 26300000002000000061 a 2631# 2632# UNION 2633# 2634CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf32_nopad_bin'; 2635INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); 2636SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; 2637HEX(a) a 26380000002000000061 a 2639000000200000006100000020 a 26400000006100000020 a 264100000061000000200000002000000020 a 2642000000610000006200000063 abc 264300000061000000620000006300000020 abc 26440000006100000062000000630000002000000020 abc 2645DROP TABLE t1; 2646DROP TABLE t2; 2647# 2648# DISTINCT, COUNT, MAX 2649# 2650CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_nopad_bin'; 2651INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); 2652SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; 2653HEX(a) a 26540000002000000061 a 2655000000200000006100000020 a 265600000061 a 2657000000610000002000000020 a 265800000061000000200000002000000020 a 2659SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; 2660COUNT(DISTINCT a) 26615 2662SELECT HEX(MAX(a)), MAX(a) FROM t1; 2663HEX(MAX(a)) MAX(a) 266400000061000000200000002000000020 a 2665# 2666# GROUP BY 2667# 2668CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf32_nopad_bin'; 2669INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); 2670SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; 2671HEX(a) cnt 26720000006100000061 1 267300000061000000610000002000000020 1 26740000006100000062 2 2675DROP TABLE t2; 2676# 2677# Weights 2678# 2679SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 2680HEX(WEIGHT_STRING(a AS CHAR(10))) 2681000061000000000000000000000000000000000000000000000000000000 2682000061000020000020000000000000000000000000000000000000000000 2683000020000061000000000000000000000000000000000000000000000000 2684000020000061000020000000000000000000000000000000000000000000 2685000061000020000020000020000000000000000000000000000000000000 2686DROP TABLE t1; 2687# 2688# IF, CASE, LEAST 2689# 2690SELECT IF('abc' COLLATE 'utf32_nopad_bin' = 'abc ', 'pad', 'nopad'); 2691IF('abc' COLLATE 'utf32_nopad_bin' = 'abc ', 'pad', 'nopad') 2692nopad 2693SELECT CASE 'abc' COLLATE 'utf32_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; 2694CASE 'abc' COLLATE 'utf32_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END 2695nopad 2696SELECT CASE WHEN 'abc' COLLATE 'utf32_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; 2697CASE WHEN 'abc' COLLATE 'utf32_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END 2698nopad 2699SELECT HEX(LEAST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')); 2700HEX(LEAST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')) 270100000061000000620000006300000020 2702SELECT HEX(GREATEST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')); 2703HEX(GREATEST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')) 27040000006100000062000000630000002000000020 2705# 2706# Collation mix 2707# 2708CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_bin'; 2709INSERT INTO t1 VALUES ('a'),('a '); 2710SELECT COUNT(*) FROM t1 WHERE a='a'; 2711COUNT(*) 27122 2713SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_bin'; 2714COUNT(*) 27152 2716SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_nopad_bin'; 2717COUNT(*) 27181 2719ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf32_nopad_bin'; 2720SELECT COUNT(*) FROM t1 WHERE a='a'; 2721COUNT(*) 27221 2723SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_bin'; 2724COUNT(*) 27252 2726SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_nopad_bin'; 2727COUNT(*) 27281 2729DROP TABLE t1; 2730# 2731# End of ctype_pad.inc 2732# 2733SET DEFAULT_STORAGE_ENGINE=HEAP; 2734# 2735# Start of ctype_pad.inc 2736# 2737# 2738# Unique indexes 2739# 2740CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf32_nopad_bin'; 2741SHOW CREATE TABLE t1; 2742Table Create Table 2743t1 CREATE TABLE `t1` ( 2744 `a` varchar(10) COLLATE utf32_nopad_bin NOT NULL, 2745 PRIMARY KEY (`a`) 2746) ENGINE=MEMORY DEFAULT CHARSET=utf32 COLLATE=utf32_nopad_bin 2747INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); 2748SELECT HEX(a), a FROM t1 ORDER BY a; 2749HEX(a) a 27500000002000000061 a 2751000000200000006100000020 a 275200000061000000200000002000000020 a 2753000000610000006200000063 abc 27540000006100000062000000630000002000000020 abc 2755SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; 2756HEX(a) a 27570000002000000061 a 2758000000200000006100000020 a 275900000061000000200000002000000020 a 2760000000610000006200000063 abc 27610000006100000062000000630000002000000020 abc 2762SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; 2763HEX(a) a 27640000006100000062000000630000002000000020 abc 2765000000610000006200000063 abc 276600000061000000200000002000000020 a 2767000000200000006100000020 a 27680000002000000061 a 2769# 2770# UNION 2771# 2772CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf32_nopad_bin'; 2773INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); 2774SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; 2775HEX(a) a 27760000002000000061 a 2777000000200000006100000020 a 27780000006100000020 a 277900000061000000200000002000000020 a 2780000000610000006200000063 abc 278100000061000000620000006300000020 abc 27820000006100000062000000630000002000000020 abc 2783DROP TABLE t1; 2784DROP TABLE t2; 2785# 2786# DISTINCT, COUNT, MAX 2787# 2788CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_nopad_bin'; 2789INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); 2790SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; 2791HEX(a) a 27920000002000000061 a 2793000000200000006100000020 a 279400000061 a 2795000000610000002000000020 a 279600000061000000200000002000000020 a 2797SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; 2798COUNT(DISTINCT a) 27995 2800SELECT HEX(MAX(a)), MAX(a) FROM t1; 2801HEX(MAX(a)) MAX(a) 280200000061000000200000002000000020 a 2803# 2804# GROUP BY 2805# 2806CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf32_nopad_bin'; 2807INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); 2808SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; 2809HEX(a) cnt 28100000006100000061 1 281100000061000000610000002000000020 1 28120000006100000062 2 2813DROP TABLE t2; 2814# 2815# Weights 2816# 2817SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; 2818HEX(WEIGHT_STRING(a AS CHAR(10))) 2819000061000000000000000000000000000000000000000000000000000000 2820000061000020000020000000000000000000000000000000000000000000 2821000020000061000000000000000000000000000000000000000000000000 2822000020000061000020000000000000000000000000000000000000000000 2823000061000020000020000020000000000000000000000000000000000000 2824DROP TABLE t1; 2825# 2826# IF, CASE, LEAST 2827# 2828SELECT IF('abc' COLLATE 'utf32_nopad_bin' = 'abc ', 'pad', 'nopad'); 2829IF('abc' COLLATE 'utf32_nopad_bin' = 'abc ', 'pad', 'nopad') 2830nopad 2831SELECT CASE 'abc' COLLATE 'utf32_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; 2832CASE 'abc' COLLATE 'utf32_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END 2833nopad 2834SELECT CASE WHEN 'abc' COLLATE 'utf32_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; 2835CASE WHEN 'abc' COLLATE 'utf32_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END 2836nopad 2837SELECT HEX(LEAST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')); 2838HEX(LEAST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')) 283900000061000000620000006300000020 2840SELECT HEX(GREATEST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')); 2841HEX(GREATEST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')) 28420000006100000062000000630000002000000020 2843# 2844# Collation mix 2845# 2846CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_bin'; 2847INSERT INTO t1 VALUES ('a'),('a '); 2848SELECT COUNT(*) FROM t1 WHERE a='a'; 2849COUNT(*) 28502 2851SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_bin'; 2852COUNT(*) 28532 2854SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_nopad_bin'; 2855COUNT(*) 28561 2857ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf32_nopad_bin'; 2858SELECT COUNT(*) FROM t1 WHERE a='a'; 2859COUNT(*) 28601 2861SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_bin'; 2862COUNT(*) 28632 2864SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_nopad_bin'; 2865COUNT(*) 28661 2867DROP TABLE t1; 2868# 2869# End of ctype_pad.inc 2870# 2871SET DEFAULT_STORAGE_ENGINE=Default; 2872# 2873# MDEV-22111 ERROR 1064 & 1033 and SIGSEGV on CREATE TABLE w/ various charsets on 10.4/5 optimized builds | Assertion `(uint) (table_check_constraints - share->check_constraints) == (uint) (share->table_check_constraints - share->field_check_constraints)' failed 2874# 10.2 tests 2875# 2876SET NAMES utf8, COLLATION_CONNECTION=utf32_bin; 2877CREATE TABLE t1(c1 ENUM('a','b','ac') CHARACTER SET 'Binary',c2 JSON,c3 INT); 2878SHOW CREATE TABLE t1; 2879Table Create Table 2880t1 CREATE TABLE `t1` ( 2881 `c1` enum('\0\0\0a','\0\0\0b','\0\0\0a\0\0\0c') CHARACTER SET binary DEFAULT NULL, 2882 `c2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`c2`)), 2883 `c3` int(11) DEFAULT NULL 2884) ENGINE=MyISAM DEFAULT CHARSET=latin1 2885INSERT INTO t1 (c1) VALUES (1),(2),(3); 2886SELECT HEX(c1) FROM t1 ORDER BY c1; 2887HEX(c1) 288800000061 288900000062 28900000006100000063 2891DROP TABLE t1; 2892# 2893# End of 10.2 tests 2894# 2895