1set names binary; 2SET TIME_ZONE = _latin1 '+03:00'; 3# 4# Start of WL#2649 Number-to-string conversions 5# 6select hex(concat(1)); 7hex(concat(1)) 831 9create table t1 as select concat(1) as c1; 10show create table t1; 11Table Create Table 12t1 CREATE TABLE `t1` ( 13 `c1` varbinary(1) DEFAULT NULL 14) ENGINE=MyISAM DEFAULT CHARSET=latin1 15select hex(c1) from t1; 16hex(c1) 1731 18drop table t1; 19select hex(concat(18446744073709551615)); 20hex(concat(18446744073709551615)) 213138343436373434303733373039353531363135 22create table t1 as select concat(18446744073709551615) as c1; 23show create table t1; 24Table Create Table 25t1 CREATE TABLE `t1` ( 26 `c1` varbinary(20) DEFAULT NULL 27) ENGINE=MyISAM DEFAULT CHARSET=latin1 28select hex(c1) from t1; 29hex(c1) 303138343436373434303733373039353531363135 31drop table t1; 32select hex(concat(1.1)); 33hex(concat(1.1)) 34312E31 35create table t1 as select concat(1.1) as c1; 36show create table t1; 37Table Create Table 38t1 CREATE TABLE `t1` ( 39 `c1` varbinary(4) DEFAULT NULL 40) ENGINE=MyISAM DEFAULT CHARSET=latin1 41select hex(c1) from t1; 42hex(c1) 43312E31 44drop table t1; 45select hex(concat('a', 1+2)), charset(concat(1+2)); 46hex(concat('a', 1+2)) charset(concat(1+2)) 476133 binary 48create table t1 as select concat(1+2) as c1; 49show create table t1; 50Table Create Table 51t1 CREATE TABLE `t1` ( 52 `c1` varbinary(3) DEFAULT NULL 53) ENGINE=MyISAM DEFAULT CHARSET=latin1 54drop table t1; 55select hex(concat(1-2)); 56hex(concat(1-2)) 572D31 58create table t1 as select concat(1-2) as c1; 59show create table t1; 60Table Create Table 61t1 CREATE TABLE `t1` ( 62 `c1` varbinary(3) DEFAULT NULL 63) ENGINE=MyISAM DEFAULT CHARSET=latin1 64drop table t1; 65select hex(concat(1*2)); 66hex(concat(1*2)) 6732 68create table t1 as select concat(1*2) as c1; 69show create table t1; 70Table Create Table 71t1 CREATE TABLE `t1` ( 72 `c1` varbinary(3) DEFAULT NULL 73) ENGINE=MyISAM DEFAULT CHARSET=latin1 74drop table t1; 75select hex(concat(1/2)); 76hex(concat(1/2)) 77302E35303030 78create table t1 as select concat(1/2) as c1; 79show create table t1; 80Table Create Table 81t1 CREATE TABLE `t1` ( 82 `c1` varbinary(7) DEFAULT NULL 83) ENGINE=MyISAM DEFAULT CHARSET=latin1 84drop table t1; 85select hex(concat(1 div 2)); 86hex(concat(1 div 2)) 8730 88create table t1 as select concat(1 div 2) as c1; 89show create table t1; 90Table Create Table 91t1 CREATE TABLE `t1` ( 92 `c1` varbinary(1) DEFAULT NULL 93) ENGINE=MyISAM DEFAULT CHARSET=latin1 94drop table t1; 95select hex(concat(1 % 2)); 96hex(concat(1 % 2)) 9731 98create table t1 as select concat(1 % 2) as c1; 99show create table t1; 100Table Create Table 101t1 CREATE TABLE `t1` ( 102 `c1` varbinary(2) DEFAULT NULL 103) ENGINE=MyISAM DEFAULT CHARSET=latin1 104drop table t1; 105select hex(concat(-1)); 106hex(concat(-1)) 1072D31 108create table t1 as select concat(-1) as c1; 109show create table t1; 110Table Create Table 111t1 CREATE TABLE `t1` ( 112 `c1` varbinary(2) DEFAULT NULL 113) ENGINE=MyISAM DEFAULT CHARSET=latin1 114drop table t1; 115select hex(concat(-(1+2))); 116hex(concat(-(1+2))) 1172D33 118create table t1 as select concat(-(1+2)) as c1; 119show create table t1; 120Table Create Table 121t1 CREATE TABLE `t1` ( 122 `c1` varbinary(4) DEFAULT NULL 123) ENGINE=MyISAM DEFAULT CHARSET=latin1 124drop table t1; 125select hex(concat(1|2)); 126hex(concat(1|2)) 12733 128create table t1 as select concat(1|2) as c1; 129show create table t1; 130Table Create Table 131t1 CREATE TABLE `t1` ( 132 `c1` varbinary(21) DEFAULT NULL 133) ENGINE=MyISAM DEFAULT CHARSET=latin1 134drop table t1; 135select hex(concat(1&2)); 136hex(concat(1&2)) 13730 138create table t1 as select concat(1&2) as c1; 139show create table t1; 140Table Create Table 141t1 CREATE TABLE `t1` ( 142 `c1` varbinary(21) DEFAULT NULL 143) ENGINE=MyISAM DEFAULT CHARSET=latin1 144drop table t1; 145select hex(concat(bit_count(12))); 146hex(concat(bit_count(12))) 14732 148create table t1 as select concat(bit_count(12)) as c1; 149show create table t1; 150Table Create Table 151t1 CREATE TABLE `t1` ( 152 `c1` varbinary(2) DEFAULT NULL 153) ENGINE=MyISAM DEFAULT CHARSET=latin1 154drop table t1; 155select hex(concat(2<<1)); 156hex(concat(2<<1)) 15734 158create table t1 as select concat(2<<1) as c1; 159show create table t1; 160Table Create Table 161t1 CREATE TABLE `t1` ( 162 `c1` varbinary(21) DEFAULT NULL 163) ENGINE=MyISAM DEFAULT CHARSET=latin1 164drop table t1; 165select hex(concat(2>>1)); 166hex(concat(2>>1)) 16731 168create table t1 as select concat(2>>1) as c1; 169show create table t1; 170Table Create Table 171t1 CREATE TABLE `t1` ( 172 `c1` varbinary(21) DEFAULT NULL 173) ENGINE=MyISAM DEFAULT CHARSET=latin1 174drop table t1; 175select hex(concat(~0)); 176hex(concat(~0)) 1773138343436373434303733373039353531363135 178create table t1 as select concat(~0) as c1; 179show create table t1; 180Table Create Table 181t1 CREATE TABLE `t1` ( 182 `c1` varbinary(21) DEFAULT NULL 183) ENGINE=MyISAM DEFAULT CHARSET=latin1 184drop table t1; 185select hex(concat(3^2)); 186hex(concat(3^2)) 18731 188create table t1 as select concat(3^2) as c1; 189show create table t1; 190Table Create Table 191t1 CREATE TABLE `t1` ( 192 `c1` varbinary(21) DEFAULT NULL 193) ENGINE=MyISAM DEFAULT CHARSET=latin1 194drop table t1; 195select hex(concat(abs(-2))); 196hex(concat(abs(-2))) 19732 198create table t1 as select concat(abs(-2)) as c1; 199show create table t1; 200Table Create Table 201t1 CREATE TABLE `t1` ( 202 `c1` varbinary(2) DEFAULT NULL 203) ENGINE=MyISAM DEFAULT CHARSET=latin1 204drop table t1; 205select hex(left(concat(exp(2)),1)); 206hex(left(concat(exp(2)),1)) 20737 208create table t1 as select concat(exp(2)) as c1; 209show create table t1; 210Table Create Table 211t1 CREATE TABLE `t1` ( 212 `c1` varbinary(23) DEFAULT NULL 213) ENGINE=MyISAM DEFAULT CHARSET=latin1 214drop table t1; 215select hex(left(concat(log(2)),1)); 216hex(left(concat(log(2)),1)) 21730 218create table t1 as select concat(log(2)) as c1; 219show create table t1; 220Table Create Table 221t1 CREATE TABLE `t1` ( 222 `c1` varbinary(23) DEFAULT NULL 223) ENGINE=MyISAM DEFAULT CHARSET=latin1 224drop table t1; 225select hex(left(concat(log2(2)),1)); 226hex(left(concat(log2(2)),1)) 22731 228create table t1 as select concat(log2(2)) as c1; 229show create table t1; 230Table Create Table 231t1 CREATE TABLE `t1` ( 232 `c1` varbinary(23) DEFAULT NULL 233) ENGINE=MyISAM DEFAULT CHARSET=latin1 234drop table t1; 235select hex(left(concat(log10(2)),1)); 236hex(left(concat(log10(2)),1)) 23730 238create table t1 as select concat(log10(2)) as c1; 239show create table t1; 240Table Create Table 241t1 CREATE TABLE `t1` ( 242 `c1` varbinary(23) DEFAULT NULL 243) ENGINE=MyISAM DEFAULT CHARSET=latin1 244drop table t1; 245select hex(left(concat(sqrt(2)),1)); 246hex(left(concat(sqrt(2)),1)) 24731 248create table t1 as select concat(sqrt(2)) as c1; 249show create table t1; 250Table Create Table 251t1 CREATE TABLE `t1` ( 252 `c1` varbinary(23) DEFAULT NULL 253) ENGINE=MyISAM DEFAULT CHARSET=latin1 254drop table t1; 255select hex(left(concat(pow(2,2)),1)); 256hex(left(concat(pow(2,2)),1)) 25734 258create table t1 as select concat(pow(2,2)) as c1; 259show create table t1; 260Table Create Table 261t1 CREATE TABLE `t1` ( 262 `c1` varbinary(23) DEFAULT NULL 263) ENGINE=MyISAM DEFAULT CHARSET=latin1 264drop table t1; 265select hex(left(concat(acos(0.5)),1)); 266hex(left(concat(acos(0.5)),1)) 26731 268create table t1 as select concat(acos(0.5)) as c1; 269show create table t1; 270Table Create Table 271t1 CREATE TABLE `t1` ( 272 `c1` varbinary(23) DEFAULT NULL 273) ENGINE=MyISAM DEFAULT CHARSET=latin1 274drop table t1; 275select hex(left(concat(asin(0.5)),1)); 276hex(left(concat(asin(0.5)),1)) 27730 278create table t1 as select concat(asin(0.5)) as c1; 279show create table t1; 280Table Create Table 281t1 CREATE TABLE `t1` ( 282 `c1` varbinary(23) DEFAULT NULL 283) ENGINE=MyISAM DEFAULT CHARSET=latin1 284drop table t1; 285select hex(left(concat(atan(0.5)),1)); 286hex(left(concat(atan(0.5)),1)) 28730 288create table t1 as select concat(atan(0.5)) as c1; 289show create table t1; 290Table Create Table 291t1 CREATE TABLE `t1` ( 292 `c1` varbinary(23) DEFAULT NULL 293) ENGINE=MyISAM DEFAULT CHARSET=latin1 294drop table t1; 295select hex(left(concat(cos(0.5)),1)); 296hex(left(concat(cos(0.5)),1)) 29730 298create table t1 as select concat(cos(0.5)) as c1; 299show create table t1; 300Table Create Table 301t1 CREATE TABLE `t1` ( 302 `c1` varbinary(23) DEFAULT NULL 303) ENGINE=MyISAM DEFAULT CHARSET=latin1 304drop table t1; 305select hex(left(concat(sin(0.5)),1)); 306hex(left(concat(sin(0.5)),1)) 30730 308create table t1 as select concat(sin(0.5)) as c1; 309show create table t1; 310Table Create Table 311t1 CREATE TABLE `t1` ( 312 `c1` varbinary(23) DEFAULT NULL 313) ENGINE=MyISAM DEFAULT CHARSET=latin1 314drop table t1; 315select hex(left(concat(tan(0.5)),1)); 316hex(left(concat(tan(0.5)),1)) 31730 318create table t1 as select concat(tan(0.5)) as c1; 319show create table t1; 320Table Create Table 321t1 CREATE TABLE `t1` ( 322 `c1` varbinary(23) DEFAULT NULL 323) ENGINE=MyISAM DEFAULT CHARSET=latin1 324drop table t1; 325select hex(concat(degrees(0))); 326hex(concat(degrees(0))) 32730 328create table t1 as select concat(degrees(0)) as c1; 329show create table t1; 330Table Create Table 331t1 CREATE TABLE `t1` ( 332 `c1` varbinary(23) DEFAULT NULL 333) ENGINE=MyISAM DEFAULT CHARSET=latin1 334drop table t1; 335select hex(concat(radians(0))); 336hex(concat(radians(0))) 33730 338create table t1 as select concat(radians(0)) as c1; 339show create table t1; 340Table Create Table 341t1 CREATE TABLE `t1` ( 342 `c1` varbinary(23) DEFAULT NULL 343) ENGINE=MyISAM DEFAULT CHARSET=latin1 344drop table t1; 345select hex(concat(ceiling(0.5))); 346hex(concat(ceiling(0.5))) 34731 348create table t1 as select ceiling(0.5) as c0, concat(ceiling(0.5)) as c1; 349show create table t1; 350Table Create Table 351t1 CREATE TABLE `t1` ( 352 `c0` int(3) NOT NULL, 353 `c1` varbinary(3) DEFAULT NULL 354) ENGINE=MyISAM DEFAULT CHARSET=latin1 355drop table t1; 356select hex(concat(floor(0.5))); 357hex(concat(floor(0.5))) 35830 359create table t1 as select floor(0.5) as c0, concat(floor(0.5)) as c1; 360show create table t1; 361Table Create Table 362t1 CREATE TABLE `t1` ( 363 `c0` int(3) NOT NULL, 364 `c1` varbinary(3) DEFAULT NULL 365) ENGINE=MyISAM DEFAULT CHARSET=latin1 366drop table t1; 367select hex(concat(round(0.5))); 368hex(concat(round(0.5))) 36931 370create table t1 as select concat(round(0.5)) as c1; 371show create table t1; 372Table Create Table 373t1 CREATE TABLE `t1` ( 374 `c1` varbinary(3) DEFAULT NULL 375) ENGINE=MyISAM DEFAULT CHARSET=latin1 376drop table t1; 377select hex(concat(sign(0.5))); 378hex(concat(sign(0.5))) 37931 380create table t1 as select concat(sign(0.5)) as c1; 381show create table t1; 382Table Create Table 383t1 CREATE TABLE `t1` ( 384 `c1` varbinary(2) DEFAULT NULL 385) ENGINE=MyISAM DEFAULT CHARSET=latin1 386drop table t1; 387create table t1 as select concat(rand()) as c1; 388show create table t1; 389Table Create Table 390t1 CREATE TABLE `t1` ( 391 `c1` varbinary(23) DEFAULT NULL 392) ENGINE=MyISAM DEFAULT CHARSET=latin1 393drop table t1; 394select hex(concat(length('a'))); 395hex(concat(length('a'))) 39631 397create table t1 as select concat(length('a')) as c1; 398show create table t1; 399Table Create Table 400t1 CREATE TABLE `t1` ( 401 `c1` varbinary(10) DEFAULT NULL 402) ENGINE=MyISAM DEFAULT CHARSET=latin1 403drop table t1; 404select hex(concat(char_length('a'))); 405hex(concat(char_length('a'))) 40631 407create table t1 as select concat(char_length('a')) as c1; 408show create table t1; 409Table Create Table 410t1 CREATE TABLE `t1` ( 411 `c1` varbinary(10) DEFAULT NULL 412) ENGINE=MyISAM DEFAULT CHARSET=latin1 413drop table t1; 414select hex(concat(bit_length('a'))); 415hex(concat(bit_length('a'))) 41638 417create table t1 as select concat(bit_length('a')) as c1; 418show create table t1; 419Table Create Table 420t1 CREATE TABLE `t1` ( 421 `c1` varbinary(11) DEFAULT NULL 422) ENGINE=MyISAM DEFAULT CHARSET=latin1 423drop table t1; 424select hex(concat(coercibility('a'))); 425hex(concat(coercibility('a'))) 42634 427create table t1 as select concat(coercibility('a')) as c1; 428show create table t1; 429Table Create Table 430t1 CREATE TABLE `t1` ( 431 `c1` varbinary(10) DEFAULT NULL 432) ENGINE=MyISAM DEFAULT CHARSET=latin1 433drop table t1; 434select hex(concat(locate('a','a'))); 435hex(concat(locate('a','a'))) 43631 437create table t1 as select concat(locate('a','a')) as c1; 438show create table t1; 439Table Create Table 440t1 CREATE TABLE `t1` ( 441 `c1` varbinary(11) DEFAULT NULL 442) ENGINE=MyISAM DEFAULT CHARSET=latin1 443drop table t1; 444select hex(concat(field('c','a','b','c'))); 445hex(concat(field('c','a','b','c'))) 44633 447create table t1 as select concat(field('c','a','b','c')) as c1; 448show create table t1; 449Table Create Table 450t1 CREATE TABLE `t1` ( 451 `c1` varbinary(3) DEFAULT NULL 452) ENGINE=MyISAM DEFAULT CHARSET=latin1 453drop table t1; 454select hex(concat(ascii(61))); 455hex(concat(ascii(61))) 4563534 457create table t1 as select concat(ascii(61)) as c1; 458show create table t1; 459Table Create Table 460t1 CREATE TABLE `t1` ( 461 `c1` varbinary(3) DEFAULT NULL 462) ENGINE=MyISAM DEFAULT CHARSET=latin1 463drop table t1; 464select hex(concat(ord(61))); 465hex(concat(ord(61))) 4663534 467create table t1 as select concat(ord(61)) as c1; 468show create table t1; 469Table Create Table 470t1 CREATE TABLE `t1` ( 471 `c1` varbinary(7) DEFAULT NULL 472) ENGINE=MyISAM DEFAULT CHARSET=latin1 473drop table t1; 474select hex(concat(find_in_set('b','a,b,c,d'))); 475hex(concat(find_in_set('b','a,b,c,d'))) 47632 477create table t1 as select concat(find_in_set('b','a,b,c,d')) as c1; 478show create table t1; 479Table Create Table 480t1 CREATE TABLE `t1` ( 481 `c1` varbinary(3) DEFAULT NULL 482) ENGINE=MyISAM DEFAULT CHARSET=latin1 483drop table t1; 484select md5('a'), hex(md5('a')); 485md5('a') hex(md5('a')) 4860cc175b9c0f1b6a831c399e269772661 3063633137356239633066316236613833316333393965323639373732363631 487create table t1 as select md5('a') as c1; 488show create table t1; 489Table Create Table 490t1 CREATE TABLE `t1` ( 491 `c1` varbinary(32) DEFAULT NULL 492) ENGINE=MyISAM DEFAULT CHARSET=latin1 493drop table t1; 494select old_password('a'), hex(old_password('a')); 495old_password('a') hex(old_password('a')) 49660671c896665c3fa 36303637316338393636363563336661 497create table t1 as select old_password('a') as c1; 498show create table t1; 499Table Create Table 500t1 CREATE TABLE `t1` ( 501 `c1` varbinary(16) DEFAULT NULL 502) ENGINE=MyISAM DEFAULT CHARSET=latin1 503drop table t1; 504select password('a'), hex(password('a')); 505password('a') hex(password('a')) 506*667F407DE7C6AD07358FA38DAED7828A72014B4E 2A36363746343037444537433641443037333538464133384441454437383238413732303134423445 507create table t1 as select password('a') as c1; 508show create table t1; 509Table Create Table 510t1 CREATE TABLE `t1` ( 511 `c1` varbinary(41) DEFAULT NULL 512) ENGINE=MyISAM DEFAULT CHARSET=latin1 513drop table t1; 514select sha('a'), hex(sha('a')); 515sha('a') hex(sha('a')) 51686f7e437faa5a7fce15d1ddcb9eaeaea377667b8 38366637653433376661613561376663653135643164646362396561656165613337373636376238 517create table t1 as select sha('a') as c1; 518show create table t1; 519Table Create Table 520t1 CREATE TABLE `t1` ( 521 `c1` varbinary(40) DEFAULT NULL 522) ENGINE=MyISAM DEFAULT CHARSET=latin1 523drop table t1; 524select sha1('a'), hex(sha1('a')); 525sha1('a') hex(sha1('a')) 52686f7e437faa5a7fce15d1ddcb9eaeaea377667b8 38366637653433376661613561376663653135643164646362396561656165613337373636376238 527create table t1 as select sha1('a') as c1; 528show create table t1; 529Table Create Table 530t1 CREATE TABLE `t1` ( 531 `c1` varbinary(40) DEFAULT NULL 532) ENGINE=MyISAM DEFAULT CHARSET=latin1 533drop table t1; 534select hex(concat(cast('-1' as signed))); 535hex(concat(cast('-1' as signed))) 5362D31 537create table t1 as select concat(cast('-1' as signed)) as c1; 538show create table t1; 539Table Create Table 540t1 CREATE TABLE `t1` ( 541 `c1` varbinary(2) DEFAULT NULL 542) ENGINE=MyISAM DEFAULT CHARSET=latin1 543drop table t1; 544select hex(concat(cast('1' as unsigned))); 545hex(concat(cast('1' as unsigned))) 54631 547create table t1 as select concat(cast('1' as unsigned)) as c1; 548show create table t1; 549Table Create Table 550t1 CREATE TABLE `t1` ( 551 `c1` varbinary(1) DEFAULT NULL 552) ENGINE=MyISAM DEFAULT CHARSET=latin1 553drop table t1; 554select hex(concat(cast(1/2 as decimal(5,5)))); 555hex(concat(cast(1/2 as decimal(5,5)))) 556302E3530303030 557create table t1 as select concat(cast(1/2 as decimal(5,5))) as c1; 558show create table t1; 559Table Create Table 560t1 CREATE TABLE `t1` ( 561 `c1` varbinary(7) DEFAULT NULL 562) ENGINE=MyISAM DEFAULT CHARSET=latin1 563drop table t1; 564select hex(concat(cast('2001-01-02 03:04:05' as date))); 565hex(concat(cast('2001-01-02 03:04:05' as date))) 566323030312D30312D3032 567create table t1 as select concat(cast('2001-01-02 03:04:05' as date)) as c1; 568show create table t1; 569Table Create Table 570t1 CREATE TABLE `t1` ( 571 `c1` varbinary(10) DEFAULT NULL 572) ENGINE=MyISAM DEFAULT CHARSET=latin1 573select * from t1; 574c1 5752001-01-02 576drop table t1; 577select hex(concat(cast('2001-01-02 03:04:05' as time))); 578hex(concat(cast('2001-01-02 03:04:05' as time))) 57930333A30343A3035 580create table t1 as select concat(cast('2001-01-02 03:04:05' as time)) as c1; 581show create table t1; 582Table Create Table 583t1 CREATE TABLE `t1` ( 584 `c1` varbinary(10) DEFAULT NULL 585) ENGINE=MyISAM DEFAULT CHARSET=latin1 586select * from t1; 587c1 58803:04:05 589drop table t1; 590select hex(concat(cast('2001-01-02' as datetime))); 591hex(concat(cast('2001-01-02' as datetime))) 592323030312D30312D30322030303A30303A3030 593create table t1 as select concat(cast('2001-01-02' as datetime)) as c1; 594show create table t1; 595Table Create Table 596t1 CREATE TABLE `t1` ( 597 `c1` varbinary(19) DEFAULT NULL 598) ENGINE=MyISAM DEFAULT CHARSET=latin1 599select * from t1; 600c1 6012001-01-02 00:00:00 602drop table t1; 603select hex(concat(least(1,2))); 604hex(concat(least(1,2))) 60531 606create table t1 as select concat(least(1,2)) as c1; 607show create table t1; 608Table Create Table 609t1 CREATE TABLE `t1` ( 610 `c1` varbinary(1) DEFAULT NULL 611) ENGINE=MyISAM DEFAULT CHARSET=latin1 612drop table t1; 613select hex(concat(greatest(1,2))); 614hex(concat(greatest(1,2))) 61532 616create table t1 as select concat(greatest(1,2)) as c1; 617show create table t1; 618Table Create Table 619t1 CREATE TABLE `t1` ( 620 `c1` varbinary(1) DEFAULT NULL 621) ENGINE=MyISAM DEFAULT CHARSET=latin1 622drop table t1; 623select hex(concat(case when 11 then 22 else 33 end)); 624hex(concat(case when 11 then 22 else 33 end)) 6253232 626create table t1 as select concat(case when 11 then 22 else 33 end) as c1; 627show create table t1; 628Table Create Table 629t1 CREATE TABLE `t1` ( 630 `c1` varbinary(2) DEFAULT NULL 631) ENGINE=MyISAM DEFAULT CHARSET=latin1 632drop table t1; 633select hex(concat(coalesce(1,2))); 634hex(concat(coalesce(1,2))) 63531 636create table t1 as select concat(coalesce(1,2)) as c1; 637show create table t1; 638Table Create Table 639t1 CREATE TABLE `t1` ( 640 `c1` varbinary(1) DEFAULT NULL 641) ENGINE=MyISAM DEFAULT CHARSET=latin1 642drop table t1; 643select hex(concat_ws(1,2,3)); 644hex(concat_ws(1,2,3)) 645323133 646create table t1 as select concat_ws(1,2,3) as c1; 647show create table t1; 648Table Create Table 649t1 CREATE TABLE `t1` ( 650 `c1` varbinary(3) DEFAULT NULL 651) ENGINE=MyISAM DEFAULT CHARSET=latin1 652drop table t1; 653select hex(group_concat(1,2,3)); 654hex(group_concat(1,2,3)) 655313233 656create table t1 as select group_concat(1,2,3) as c1; 657show create table t1; 658Table Create Table 659t1 CREATE TABLE `t1` ( 660 `c1` mediumblob DEFAULT NULL 661) ENGINE=MyISAM DEFAULT CHARSET=latin1 662drop table t1; 663create table t1 as select 1 as c1 union select 'a'; 664show create table t1; 665Table Create Table 666t1 CREATE TABLE `t1` ( 667 `c1` varbinary(1) NOT NULL DEFAULT '' 668) ENGINE=MyISAM DEFAULT CHARSET=latin1 669select hex(c1) from t1 order by c1; 670hex(c1) 67131 67261 673drop table t1; 674create table t1 as select concat(last_insert_id()) as c1; 675show create table t1; 676Table Create Table 677t1 CREATE TABLE `t1` ( 678 `c1` varbinary(21) DEFAULT NULL 679) ENGINE=MyISAM DEFAULT CHARSET=latin1 680drop table t1; 681select hex(concat(benchmark(0,0))); 682hex(concat(benchmark(0,0))) 68330 684create table t1 as select concat(benchmark(0,0)) as c1; 685show create table t1; 686Table Create Table 687t1 CREATE TABLE `t1` ( 688 `c1` varbinary(1) DEFAULT NULL 689) ENGINE=MyISAM DEFAULT CHARSET=latin1 690drop table t1; 691select hex(concat(sleep(0))); 692hex(concat(sleep(0))) 69330 694create table t1 as select concat(sleep(0)) as c1; 695show create table t1; 696Table Create Table 697t1 CREATE TABLE `t1` ( 698 `c1` varbinary(1) DEFAULT NULL 699) ENGINE=MyISAM DEFAULT CHARSET=latin1 700drop table t1; 701select hex(concat(is_free_lock('xxxx'))); 702hex(concat(is_free_lock('xxxx'))) 70331 704create table t1 as select concat(is_free_lock('xxxx')) as c1; 705show create table t1; 706Table Create Table 707t1 CREATE TABLE `t1` ( 708 `c1` varbinary(1) DEFAULT NULL 709) ENGINE=MyISAM DEFAULT CHARSET=latin1 710drop table t1; 711create table t1 as select concat(is_used_lock('a')) as c1; 712show create table t1; 713Table Create Table 714t1 CREATE TABLE `t1` ( 715 `c1` varbinary(10) DEFAULT NULL 716) ENGINE=MyISAM DEFAULT CHARSET=latin1 717drop table t1; 718create table t1 as select concat(release_lock('a')) as c1; 719show create table t1; 720Table Create Table 721t1 CREATE TABLE `t1` ( 722 `c1` varbinary(1) DEFAULT NULL 723) ENGINE=MyISAM DEFAULT CHARSET=latin1 724drop table t1; 725select hex(concat(crc32(''))); 726hex(concat(crc32(''))) 72730 728create table t1 as select concat(crc32('')) as c1; 729show create table t1; 730Table Create Table 731t1 CREATE TABLE `t1` ( 732 `c1` varbinary(10) DEFAULT NULL 733) ENGINE=MyISAM DEFAULT CHARSET=latin1 734drop table t1; 735select hex(concat(uncompressed_length(''))); 736hex(concat(uncompressed_length(''))) 73730 738create table t1 as select concat(uncompressed_length('')) as c1; 739show create table t1; 740Table Create Table 741t1 CREATE TABLE `t1` ( 742 `c1` varbinary(10) DEFAULT NULL 743) ENGINE=MyISAM DEFAULT CHARSET=latin1 744drop table t1; 745create table t1 as select concat(connection_id()) as c1; 746show create table t1; 747Table Create Table 748t1 CREATE TABLE `t1` ( 749 `c1` varbinary(10) DEFAULT NULL 750) ENGINE=MyISAM DEFAULT CHARSET=latin1 751drop table t1; 752select hex(concat(inet_aton('127.1.1.1'))); 753hex(concat(inet_aton('127.1.1.1'))) 75432313330373732323235 755create table t1 as select concat(inet_aton('127.1.1.1')) as c1; 756show create table t1; 757Table Create Table 758t1 CREATE TABLE `t1` ( 759 `c1` varbinary(21) DEFAULT NULL 760) ENGINE=MyISAM DEFAULT CHARSET=latin1 761drop table t1; 762select hex(concat(inet_ntoa(2130772225))); 763hex(concat(inet_ntoa(2130772225))) 7643132372E312E312E31 765create table t1 as select concat(inet_ntoa(2130772225)) as c1; 766select * from t1; 767c1 768127.1.1.1 769show create table t1; 770Table Create Table 771t1 CREATE TABLE `t1` ( 772 `c1` varbinary(31) DEFAULT NULL 773) ENGINE=MyISAM DEFAULT CHARSET=latin1 774drop table t1; 775select 1; 7761 7771 778select hex(concat(row_count())); 779hex(concat(row_count())) 7802D31 781create table t1 as select concat(row_count()) as c1; 782show create table t1; 783Table Create Table 784t1 CREATE TABLE `t1` ( 785 `c1` varbinary(21) DEFAULT NULL 786) ENGINE=MyISAM DEFAULT CHARSET=latin1 787drop table t1; 788select hex(concat(found_rows())); 789hex(concat(found_rows())) 79030 791create table t1 as select concat(found_rows()) as c1; 792show create table t1; 793Table Create Table 794t1 CREATE TABLE `t1` ( 795 `c1` varbinary(21) DEFAULT NULL 796) ENGINE=MyISAM DEFAULT CHARSET=latin1 797drop table t1; 798create table t1 as select concat(uuid_short()) as c1; 799show create table t1; 800Table Create Table 801t1 CREATE TABLE `t1` ( 802 `c1` varbinary(21) DEFAULT NULL 803) ENGINE=MyISAM DEFAULT CHARSET=latin1 804drop table t1; 805create table t1 as select concat(uuid()) as c1; 806show create table t1; 807Table Create Table 808t1 CREATE TABLE `t1` ( 809 `c1` varbinary(36) DEFAULT NULL 810) ENGINE=MyISAM DEFAULT CHARSET=latin1 811drop table t1; 812select coercibility(uuid()), coercibility(cast('a' as char character set latin1)); 813coercibility(uuid()) coercibility(cast('a' as char character set latin1)) 8145 2 815select charset(concat(uuid(), cast('a' as char character set latin1))); 816charset(concat(uuid(), cast('a' as char character set latin1))) 817latin1 818create table t1 as select concat(uuid(), cast('a' as char character set latin1)) as c1; 819show create table t1; 820Table Create Table 821t1 CREATE TABLE `t1` ( 822 `c1` varchar(37) DEFAULT NULL 823) ENGINE=MyISAM DEFAULT CHARSET=latin1 824drop table t1; 825create table t1 as select concat(master_pos_wait('non-existent',0,2)) as c1; 826show create table t1; 827Table Create Table 828t1 CREATE TABLE `t1` ( 829 `c1` varbinary(21) DEFAULT NULL 830) ENGINE=MyISAM DEFAULT CHARSET=latin1 831drop table t1; 832select hex(concat(@a1:=1)); 833hex(concat(@a1:=1)) 83431 835create table t1 as select concat(@a2:=2) as c1, @a3:=3 as c2; 836select hex(c1) from t1; 837hex(c1) 83832 839show create table t1; 840Table Create Table 841t1 CREATE TABLE `t1` ( 842 `c1` varbinary(1) DEFAULT NULL, 843 `c2` int(1) NOT NULL 844) ENGINE=MyISAM DEFAULT CHARSET=latin1 845drop table t1; 846set @a2=1; 847select hex(concat(@a2)); 848hex(concat(@a2)) 84931 850create table t1 as select concat(@a2) as c1, @a2 as c2; 851select hex(c1) from t1; 852hex(c1) 85331 854show create table t1; 855Table Create Table 856t1 CREATE TABLE `t1` ( 857 `c1` varbinary(20) DEFAULT NULL, 858 `c2` bigint(20) DEFAULT NULL 859) ENGINE=MyISAM DEFAULT CHARSET=latin1 860drop table t1; 861select hex(concat(@a1:=sqrt(1))); 862hex(concat(@a1:=sqrt(1))) 86331 864create table t1 as select concat(@a2:=sqrt(1)) as c1, @a3:=sqrt(1) as c2; 865select hex(c1) from t1; 866hex(c1) 86731 868show create table t1; 869Table Create Table 870t1 CREATE TABLE `t1` ( 871 `c1` varbinary(23) DEFAULT NULL, 872 `c2` double DEFAULT NULL 873) ENGINE=MyISAM DEFAULT CHARSET=latin1 874drop table t1; 875set @a2=sqrt(1); 876select hex(concat(@a2)); 877hex(concat(@a2)) 87831 879create table t1 as select concat(@a2) as c1, @a2 as c2; 880select hex(c1) from t1; 881hex(c1) 88231 883show create table t1; 884Table Create Table 885t1 CREATE TABLE `t1` ( 886 `c1` varbinary(23) DEFAULT NULL, 887 `c2` double DEFAULT NULL 888) ENGINE=MyISAM DEFAULT CHARSET=latin1 889drop table t1; 890select hex(concat(@a1:=1.1)); 891hex(concat(@a1:=1.1)) 892312E31 893create table t1 as select concat(@a2:=1.1) as c1, @a3:=1.1 as c2; 894select hex(c1) from t1; 895hex(c1) 896312E31 897show create table t1; 898Table Create Table 899t1 CREATE TABLE `t1` ( 900 `c1` varbinary(4) DEFAULT NULL, 901 `c2` decimal(2,1) NOT NULL 902) ENGINE=MyISAM DEFAULT CHARSET=latin1 903drop table t1; 904set @a2=1.1; 905select hex(concat(@a2)); 906hex(concat(@a2)) 907312E31 908create table t1 as select concat(@a2) as c1, @a2 as c2; 909select hex(c1) from t1; 910hex(c1) 911312E31 912show create table t1; 913Table Create Table 914t1 CREATE TABLE `t1` ( 915 `c1` varbinary(83) DEFAULT NULL, 916 `c2` decimal(65,38) DEFAULT NULL 917) ENGINE=MyISAM DEFAULT CHARSET=latin1 918drop table t1; 919select hex(concat(@@ft_max_word_len)); 920hex(concat(@@ft_max_word_len)) 9213834 922create table t1 as select concat(@@ft_max_word_len) as c1; 923select hex(c1) from t1; 924hex(c1) 9253834 926show create table t1; 927Table Create Table 928t1 CREATE TABLE `t1` ( 929 `c1` varbinary(21) DEFAULT NULL 930) ENGINE=MyISAM DEFAULT CHARSET=latin1 931drop table t1; 932select hex(concat('a'='a' IS TRUE)); 933hex(concat('a'='a' IS TRUE)) 93431 935create table t1 as select concat('a'='a' IS TRUE) as c1; 936show create table t1; 937Table Create Table 938t1 CREATE TABLE `t1` ( 939 `c1` varbinary(1) DEFAULT NULL 940) ENGINE=MyISAM DEFAULT CHARSET=latin1 941drop table t1; 942select hex(concat('a'='a' IS NOT TRUE)); 943hex(concat('a'='a' IS NOT TRUE)) 94430 945create table t1 as select concat('a'='a' IS NOT TRUE) as c1; 946show create table t1; 947Table Create Table 948t1 CREATE TABLE `t1` ( 949 `c1` varbinary(1) DEFAULT NULL 950) ENGINE=MyISAM DEFAULT CHARSET=latin1 951drop table t1; 952select hex(concat(NOT 'a'='a')); 953hex(concat(NOT 'a'='a')) 95430 955create table t1 as select concat(NOT 'a'='a') as c1; 956show create table t1; 957Table Create Table 958t1 CREATE TABLE `t1` ( 959 `c1` varbinary(1) DEFAULT NULL 960) ENGINE=MyISAM DEFAULT CHARSET=latin1 961drop table t1; 962select hex(concat('a' IS NULL)); 963hex(concat('a' IS NULL)) 96430 965create table t1 as select concat('a' IS NULL) as c1; 966show create table t1; 967Table Create Table 968t1 CREATE TABLE `t1` ( 969 `c1` varbinary(1) DEFAULT NULL 970) ENGINE=MyISAM DEFAULT CHARSET=latin1 971drop table t1; 972select hex(concat('a' IS NOT NULL)); 973hex(concat('a' IS NOT NULL)) 97431 975create table t1 as select concat('a' IS NOT NULL) as c1; 976show create table t1; 977Table Create Table 978t1 CREATE TABLE `t1` ( 979 `c1` varbinary(1) DEFAULT NULL 980) ENGINE=MyISAM DEFAULT CHARSET=latin1 981drop table t1; 982select hex(concat('a' rlike 'a')); 983hex(concat('a' rlike 'a')) 98431 985create table t1 as select concat('a' IS NOT NULL) as c1; 986show create table t1; 987Table Create Table 988t1 CREATE TABLE `t1` ( 989 `c1` varbinary(1) DEFAULT NULL 990) ENGINE=MyISAM DEFAULT CHARSET=latin1 991drop table t1; 992select hex(concat(strcmp('a','b'))); 993hex(concat(strcmp('a','b'))) 9942D31 995create table t1 as select concat(strcmp('a','b')) as c1; 996show create table t1; 997Table Create Table 998t1 CREATE TABLE `t1` ( 999 `c1` varbinary(2) DEFAULT NULL 1000) ENGINE=MyISAM DEFAULT CHARSET=latin1 1001drop table t1; 1002select hex(concat('a' like 'a')); 1003hex(concat('a' like 'a')) 100431 1005create table t1 as select concat('a' like 'b') as c1; 1006show create table t1; 1007Table Create Table 1008t1 CREATE TABLE `t1` ( 1009 `c1` varbinary(1) DEFAULT NULL 1010) ENGINE=MyISAM DEFAULT CHARSET=latin1 1011drop table t1; 1012select hex(concat('a' between 'b' and 'c')); 1013hex(concat('a' between 'b' and 'c')) 101430 1015create table t1 as select concat('a' between 'b' and 'c') as c1; 1016show create table t1; 1017Table Create Table 1018t1 CREATE TABLE `t1` ( 1019 `c1` varbinary(1) DEFAULT NULL 1020) ENGINE=MyISAM DEFAULT CHARSET=latin1 1021drop table t1; 1022select hex(concat('a' in ('a','b'))); 1023hex(concat('a' in ('a','b'))) 102431 1025create table t1 as select concat('a' in ('a','b')) as c1; 1026show create table t1; 1027Table Create Table 1028t1 CREATE TABLE `t1` ( 1029 `c1` varbinary(1) DEFAULT NULL 1030) ENGINE=MyISAM DEFAULT CHARSET=latin1 1031drop table t1; 1032select hex(concat(interval(23, 1, 15, 17, 30, 44, 200))); 1033hex(concat(interval(23, 1, 15, 17, 30, 44, 200))) 103433 1035create table t1 as select concat(interval(23, 1, 15, 17, 30, 44, 200)) as c1; 1036show create table t1; 1037Table Create Table 1038t1 CREATE TABLE `t1` ( 1039 `c1` varbinary(2) DEFAULT NULL 1040) ENGINE=MyISAM DEFAULT CHARSET=latin1 1041drop table t1; 1042create table t1 (a varchar(10), fulltext key(a)); 1043insert into t1 values ('a'); 1044select hex(concat(match (a) against ('a'))) from t1; 1045hex(concat(match (a) against ('a'))) 104630 1047create table t2 as select concat(match (a) against ('a')) as a from t1; 1048show create table t2; 1049Table Create Table 1050t2 CREATE TABLE `t2` ( 1051 `a` varbinary(23) DEFAULT NULL 1052) ENGINE=MyISAM DEFAULT CHARSET=latin1 1053drop table t1, t2; 1054select hex(ifnull(1,'a')); 1055hex(ifnull(1,'a')) 105631 1057create table t1 as select ifnull(1,'a') as c1; 1058show create table t1; 1059Table Create Table 1060t1 CREATE TABLE `t1` ( 1061 `c1` varbinary(1) NOT NULL 1062) ENGINE=MyISAM DEFAULT CHARSET=latin1 1063drop table t1; 1064select hex(concat(ifnull(1,1))); 1065hex(concat(ifnull(1,1))) 106631 1067create table t1 as select concat(ifnull(1,1)) as c1; 1068show create table t1; 1069Table Create Table 1070t1 CREATE TABLE `t1` ( 1071 `c1` varbinary(1) DEFAULT NULL 1072) ENGINE=MyISAM DEFAULT CHARSET=latin1 1073drop table t1; 1074select hex(concat(ifnull(1.1,1.1))); 1075hex(concat(ifnull(1.1,1.1))) 1076312E31 1077create table t1 as select concat(ifnull(1.1,1.1)) as c1; 1078show create table t1; 1079Table Create Table 1080t1 CREATE TABLE `t1` ( 1081 `c1` varbinary(4) DEFAULT NULL 1082) ENGINE=MyISAM DEFAULT CHARSET=latin1 1083drop table t1; 1084select hex(if(1,'b',1)); 1085hex(if(1,'b',1)) 108662 1087create table t1 as select if(1,'b',1) as c1; 1088show create table t1; 1089Table Create Table 1090t1 CREATE TABLE `t1` ( 1091 `c1` varbinary(1) NOT NULL 1092) ENGINE=MyISAM DEFAULT CHARSET=latin1 1093drop table t1; 1094select hex(if(1,1,'b')); 1095hex(if(1,1,'b')) 109631 1097create table t1 as select if(1,1,'b') as c1; 1098show create table t1; 1099Table Create Table 1100t1 CREATE TABLE `t1` ( 1101 `c1` varbinary(1) NOT NULL 1102) ENGINE=MyISAM DEFAULT CHARSET=latin1 1103drop table t1; 1104select hex(concat(if(1,1,1))); 1105hex(concat(if(1,1,1))) 110631 1107create table t1 as select concat(if(1,1,1)) as c1; 1108show create table t1; 1109Table Create Table 1110t1 CREATE TABLE `t1` ( 1111 `c1` varbinary(1) DEFAULT NULL 1112) ENGINE=MyISAM DEFAULT CHARSET=latin1 1113drop table t1; 1114select hex(concat(nullif(1,2))); 1115hex(concat(nullif(1,2))) 111631 1117create table t1 as select concat(nullif(1,2)) as c1; 1118show create table t1; 1119Table Create Table 1120t1 CREATE TABLE `t1` ( 1121 `c1` varbinary(1) DEFAULT NULL 1122) ENGINE=MyISAM DEFAULT CHARSET=latin1 1123drop table t1; 1124select hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)')))); 1125hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)')))) 112631 1127create table t1 as select concat(Dimension(GeomFromText('LINSTRING(0 0,10 10)'))) as c1; 1128show create table t1; 1129Table Create Table 1130t1 CREATE TABLE `t1` ( 1131 `c1` varbinary(10) DEFAULT NULL 1132) ENGINE=MyISAM DEFAULT CHARSET=latin1 1133drop table t1; 1134select hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)')))); 1135hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)')))) 113632 1137create table t1 as select concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1; 1138show create table t1; 1139Table Create Table 1140t1 CREATE TABLE `t1` ( 1141 `c1` varbinary(10) DEFAULT NULL 1142) ENGINE=MyISAM DEFAULT CHARSET=latin1 1143drop table t1; 1144select hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)')))); 1145hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)')))) 114632 1147create table t1 as select concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))) as c1; 1148show create table t1; 1149Table Create Table 1150t1 CREATE TABLE `t1` ( 1151 `c1` varbinary(10) DEFAULT NULL 1152) ENGINE=MyISAM DEFAULT CHARSET=latin1 1153drop table t1; 1154select hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)')))); 1155hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)')))) 115630 1157create table t1 as select concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1; 1158show create table t1; 1159Table Create Table 1160t1 CREATE TABLE `t1` ( 1161 `c1` varbinary(10) DEFAULT NULL 1162) ENGINE=MyISAM DEFAULT CHARSET=latin1 1163drop table t1; 1164select hex(concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')))); 1165hex(concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')))) 116631 1167create table t1 as select concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))) as c1; 1168show create table t1; 1169Table Create Table 1170t1 CREATE TABLE `t1` ( 1171 `c1` varbinary(10) DEFAULT NULL 1172) ENGINE=MyISAM DEFAULT CHARSET=latin1 1173drop table t1; 1174select hex(concat(IsEmpty(GeomFromText('POINT(1 1)')))); 1175hex(concat(IsEmpty(GeomFromText('POINT(1 1)')))) 117630 1177create table t1 as select concat(IsEmpty(GeomFromText('Point(1 1)'))) as c1; 1178show create table t1; 1179Table Create Table 1180t1 CREATE TABLE `t1` ( 1181 `c1` varbinary(21) DEFAULT NULL 1182) ENGINE=MyISAM DEFAULT CHARSET=latin1 1183drop table t1; 1184select hex(concat(IsSimple(GeomFromText('POINT(1 1)')))); 1185hex(concat(IsSimple(GeomFromText('POINT(1 1)')))) 118631 1187create table t1 as select concat(IsSimple(GeomFromText('Point(1 1)'))) as c1; 1188show create table t1; 1189Table Create Table 1190t1 CREATE TABLE `t1` ( 1191 `c1` varbinary(2) DEFAULT NULL 1192) ENGINE=MyISAM DEFAULT CHARSET=latin1 1193drop table t1; 1194select hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)')))); 1195hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)')))) 119630 1197create table t1 as select concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))) as c1; 1198show create table t1; 1199Table Create Table 1200t1 CREATE TABLE `t1` ( 1201 `c1` varbinary(2) DEFAULT NULL 1202) ENGINE=MyISAM DEFAULT CHARSET=latin1 1203drop table t1; 1204select hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)')))); 1205hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)')))) 120631 1207create table t1 as select concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))) as c1; 1208drop table t1; 1209select hex(concat(x(GeomFromText('Point(1 2)')))); 1210hex(concat(x(GeomFromText('Point(1 2)')))) 121131 1212create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1; 1213show create table t1; 1214Table Create Table 1215t1 CREATE TABLE `t1` ( 1216 `c1` varbinary(23) DEFAULT NULL 1217) ENGINE=MyISAM DEFAULT CHARSET=latin1 1218drop table t1; 1219select hex(concat(y(GeomFromText('Point(1 2)')))); 1220hex(concat(y(GeomFromText('Point(1 2)')))) 122132 1222create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1; 1223show create table t1; 1224Table Create Table 1225t1 CREATE TABLE `t1` ( 1226 `c1` varbinary(23) DEFAULT NULL 1227) ENGINE=MyISAM DEFAULT CHARSET=latin1 1228drop table t1; 1229select hex(concat(GLength(GeomFromText('LineString(1 2,2 2)')))); 1230hex(concat(GLength(GeomFromText('LineString(1 2,2 2)')))) 123131 1232create table t1 as select concat(GLength(GeomFromText('LineString(1 2, 2 2)'))) as c1; 1233show create table t1; 1234Table Create Table 1235t1 CREATE TABLE `t1` ( 1236 `c1` varbinary(23) DEFAULT NULL 1237) ENGINE=MyISAM DEFAULT CHARSET=latin1 1238drop table t1; 1239select hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))')))); 1240hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))')))) 124131 1242create table t1 as select concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))) as c1; 1243show create table t1; 1244Table Create Table 1245t1 CREATE TABLE `t1` ( 1246 `c1` varbinary(23) DEFAULT NULL 1247) ENGINE=MyISAM DEFAULT CHARSET=latin1 1248drop table t1; 1249select hex(concat(GeometryType(GeomFromText('Point(1 2)')))); 1250hex(concat(GeometryType(GeomFromText('Point(1 2)')))) 1251504F494E54 1252create table t1 as select concat(GeometryType(GeomFromText('Point(1 2)'))) as c1; 1253show create table t1; 1254Table Create Table 1255t1 CREATE TABLE `t1` ( 1256 `c1` varbinary(20) DEFAULT NULL 1257) ENGINE=MyISAM DEFAULT CHARSET=latin1 1258drop table t1; 1259select hex(concat(AsText(GeomFromText('Point(1 2)')))); 1260hex(concat(AsText(GeomFromText('Point(1 2)')))) 1261504F494E542831203229 1262create table t1 as select concat(AsText(GeomFromText('Point(1 2)'))) as c1; 1263show create table t1; 1264Table Create Table 1265t1 CREATE TABLE `t1` ( 1266 `c1` longblob DEFAULT NULL 1267) ENGINE=MyISAM DEFAULT CHARSET=latin1 1268drop table t1; 1269select hex(concat(period_add(200902, 2))); 1270hex(concat(period_add(200902, 2))) 1271323030393034 1272create table t1 as select concat(period_add(200902, 2)) as c1; 1273show create table t1; 1274Table Create Table 1275t1 CREATE TABLE `t1` ( 1276 `c1` varbinary(6) DEFAULT NULL 1277) ENGINE=MyISAM DEFAULT CHARSET=latin1 1278drop table t1; 1279select hex(concat(period_diff(200902, 200802))); 1280hex(concat(period_diff(200902, 200802))) 12813132 1282SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1283create table t1 as select concat(period_add(200902, 200802)) as c1; 1284Warnings: 1285Warning 1265 Data truncated for column 'c1' at row 1 1286show create table t1; 1287Table Create Table 1288t1 CREATE TABLE `t1` ( 1289 `c1` varbinary(6) NOT NULL 1290) ENGINE=MyISAM DEFAULT CHARSET=latin1 1291drop table t1; 1292select hex(concat(to_days(20090224))); 1293hex(concat(to_days(20090224))) 1294373333383237 1295create table t1 as select concat(to_days(20090224)) as c1; 1296show create table t1; 1297Table Create Table 1298t1 CREATE TABLE `t1` ( 1299 `c1` varbinary(6) DEFAULT NULL 1300) ENGINE=MyISAM DEFAULT CHARSET=latin1 1301drop table t1; 1302select hex(concat(dayofmonth(20090224))); 1303hex(concat(dayofmonth(20090224))) 13043234 1305create table t1 as select concat(dayofmonth(20090224)) as c1; 1306show create table t1; 1307Table Create Table 1308t1 CREATE TABLE `t1` ( 1309 `c1` varbinary(2) DEFAULT NULL 1310) ENGINE=MyISAM DEFAULT CHARSET=latin1 1311drop table t1; 1312select hex(concat(dayofyear(20090224))); 1313hex(concat(dayofyear(20090224))) 13143535 1315create table t1 as select concat(dayofyear(20090224)) as c1; 1316show create table t1; 1317Table Create Table 1318t1 CREATE TABLE `t1` ( 1319 `c1` varbinary(3) DEFAULT NULL 1320) ENGINE=MyISAM DEFAULT CHARSET=latin1 1321drop table t1; 1322select hex(concat(hour('10:11:12'))); 1323hex(concat(hour('10:11:12'))) 13243130 1325create table t1 as select concat(hour('10:11:12')) as c1; 1326show create table t1; 1327Table Create Table 1328t1 CREATE TABLE `t1` ( 1329 `c1` varbinary(2) DEFAULT NULL 1330) ENGINE=MyISAM DEFAULT CHARSET=latin1 1331drop table t1; 1332select hex(concat(minute('10:11:12'))); 1333hex(concat(minute('10:11:12'))) 13343131 1335create table t1 as select concat(minute('10:11:12')) as c1; 1336show create table t1; 1337Table Create Table 1338t1 CREATE TABLE `t1` ( 1339 `c1` varbinary(2) DEFAULT NULL 1340) ENGINE=MyISAM DEFAULT CHARSET=latin1 1341drop table t1; 1342select hex(concat(second('10:11:12'))); 1343hex(concat(second('10:11:12'))) 13443132 1345create table t1 as select concat(second('10:11:12')) as c1; 1346show create table t1; 1347Table Create Table 1348t1 CREATE TABLE `t1` ( 1349 `c1` varbinary(2) DEFAULT NULL 1350) ENGINE=MyISAM DEFAULT CHARSET=latin1 1351drop table t1; 1352select hex(concat(quarter(20090224))); 1353hex(concat(quarter(20090224))) 135431 1355create table t1 as select concat(quarter(20090224)) as c1; 1356show create table t1; 1357Table Create Table 1358t1 CREATE TABLE `t1` ( 1359 `c1` varbinary(1) DEFAULT NULL 1360) ENGINE=MyISAM DEFAULT CHARSET=latin1 1361drop table t1; 1362select hex(concat(week(20090224))); 1363hex(concat(week(20090224))) 136438 1365create table t1 as select concat(week(20090224)) as c1; 1366show create table t1; 1367Table Create Table 1368t1 CREATE TABLE `t1` ( 1369 `c1` varbinary(2) DEFAULT NULL 1370) ENGINE=MyISAM DEFAULT CHARSET=latin1 1371drop table t1; 1372select hex(concat(yearweek(20090224))); 1373hex(concat(yearweek(20090224))) 1374323030393038 1375create table t1 as select concat(yearweek(20090224)) as c1; 1376show create table t1; 1377Table Create Table 1378t1 CREATE TABLE `t1` ( 1379 `c1` varbinary(6) DEFAULT NULL 1380) ENGINE=MyISAM DEFAULT CHARSET=latin1 1381drop table t1; 1382select hex(concat(year(20090224))); 1383hex(concat(year(20090224))) 138432303039 1385create table t1 as select concat(year(20090224)) as c1; 1386show create table t1; 1387Table Create Table 1388t1 CREATE TABLE `t1` ( 1389 `c1` varbinary(4) DEFAULT NULL 1390) ENGINE=MyISAM DEFAULT CHARSET=latin1 1391drop table t1; 1392select hex(concat(weekday(20090224))); 1393hex(concat(weekday(20090224))) 139431 1395create table t1 as select concat(weekday(20090224)) as c1; 1396show create table t1; 1397Table Create Table 1398t1 CREATE TABLE `t1` ( 1399 `c1` varbinary(1) DEFAULT NULL 1400) ENGINE=MyISAM DEFAULT CHARSET=latin1 1401drop table t1; 1402select hex(concat(dayofweek(20090224))); 1403hex(concat(dayofweek(20090224))) 140433 1405create table t1 as select concat(dayofweek(20090224)) as c1; 1406show create table t1; 1407Table Create Table 1408t1 CREATE TABLE `t1` ( 1409 `c1` varbinary(1) DEFAULT NULL 1410) ENGINE=MyISAM DEFAULT CHARSET=latin1 1411drop table t1; 1412select hex(concat(unix_timestamp(20090224))); 1413hex(concat(unix_timestamp(20090224))) 141431323335343232383030 1415create table t1 as select concat(unix_timestamp(20090224)) as c1; 1416show create table t1; 1417Table Create Table 1418t1 CREATE TABLE `t1` ( 1419 `c1` varbinary(17) DEFAULT NULL 1420) ENGINE=MyISAM DEFAULT CHARSET=latin1 1421drop table t1; 1422select hex(concat(time_to_sec('10:11:12'))); 1423hex(concat(time_to_sec('10:11:12'))) 14243336363732 1425create table t1 as select concat(time_to_sec('10:11:12')) as c1; 1426show create table t1; 1427Table Create Table 1428t1 CREATE TABLE `t1` ( 1429 `c1` varbinary(17) DEFAULT NULL 1430) ENGINE=MyISAM DEFAULT CHARSET=latin1 1431drop table t1; 1432select hex(concat(extract(year from 20090702))); 1433hex(concat(extract(year from 20090702))) 143432303039 1435create table t1 as select concat(extract(year from 20090702)) as c1; 1436show create table t1; 1437Table Create Table 1438t1 CREATE TABLE `t1` ( 1439 `c1` varbinary(4) DEFAULT NULL 1440) ENGINE=MyISAM DEFAULT CHARSET=latin1 1441drop table t1; 1442select hex(concat(microsecond('12:00:00.123456'))); 1443hex(concat(microsecond('12:00:00.123456'))) 1444313233343536 1445create table t1 as select concat(microsecond('12:00:00.123456')) as c1; 1446show create table t1; 1447Table Create Table 1448t1 CREATE TABLE `t1` ( 1449 `c1` varbinary(6) DEFAULT NULL 1450) ENGINE=MyISAM DEFAULT CHARSET=latin1 1451drop table t1; 1452select hex(concat(month(20090224))); 1453hex(concat(month(20090224))) 145432 1455create table t1 as select concat(month(20090224)) as c1; 1456show create table t1; 1457Table Create Table 1458t1 CREATE TABLE `t1` ( 1459 `c1` varbinary(2) DEFAULT NULL 1460) ENGINE=MyISAM DEFAULT CHARSET=latin1 1461drop table t1; 1462create table t1 as select concat(last_day('2003-02-05')) as c1; 1463show create table t1; 1464Table Create Table 1465t1 CREATE TABLE `t1` ( 1466 `c1` varbinary(10) DEFAULT NULL 1467) ENGINE=MyISAM DEFAULT CHARSET=latin1 1468select c1, hex(c1) from t1; 1469c1 hex(c1) 14702003-02-28 323030332D30322D3238 1471drop table t1; 1472create table t1 as select concat(from_days(730669)) as c1; 1473show create table t1; 1474Table Create Table 1475t1 CREATE TABLE `t1` ( 1476 `c1` varbinary(10) DEFAULT NULL 1477) ENGINE=MyISAM DEFAULT CHARSET=latin1 1478select c1, hex(c1) from t1; 1479c1 hex(c1) 14802000-07-03 323030302D30372D3033 1481drop table t1; 1482create table t1 as select concat(curdate()) as c1; 1483show create table t1; 1484Table Create Table 1485t1 CREATE TABLE `t1` ( 1486 `c1` varbinary(10) DEFAULT NULL 1487) ENGINE=MyISAM DEFAULT CHARSET=latin1 1488drop table t1; 1489create table t1 as select concat(utc_date()) as c1; 1490show create table t1; 1491Table Create Table 1492t1 CREATE TABLE `t1` ( 1493 `c1` varbinary(10) DEFAULT NULL 1494) ENGINE=MyISAM DEFAULT CHARSET=latin1 1495drop table t1; 1496create table t1 as select concat(curtime()) as c1; 1497show create table t1; 1498Table Create Table 1499t1 CREATE TABLE `t1` ( 1500 `c1` varbinary(10) DEFAULT NULL 1501) ENGINE=MyISAM DEFAULT CHARSET=latin1 1502drop table t1; 1503create table t1 as select repeat('a',20) as c1 limit 0; 1504set timestamp=1216359724; 1505insert into t1 values (current_date); 1506insert into t1 values (current_time); 1507select c1, hex(c1) from t1; 1508c1 hex(c1) 15092008-07-18 323030382D30372D3138 151008:42:04 30383A34323A3034 1511drop table t1; 1512create table t1 as select concat(utc_time()) as c1; 1513show create table t1; 1514Table Create Table 1515t1 CREATE TABLE `t1` ( 1516 `c1` varbinary(10) DEFAULT NULL 1517) ENGINE=MyISAM DEFAULT CHARSET=latin1 1518drop table t1; 1519select hex(concat(sec_to_time(2378))); 1520hex(concat(sec_to_time(2378))) 152130303A33393A3338 1522create table t1 as select concat(sec_to_time(2378)) as c1; 1523show create table t1; 1524Table Create Table 1525t1 CREATE TABLE `t1` ( 1526 `c1` varbinary(10) DEFAULT NULL 1527) ENGINE=MyISAM DEFAULT CHARSET=latin1 1528drop table t1; 1529select hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00'))); 1530hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00'))) 153132343A30303A3030 1532create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')) as c1; 1533show create table t1; 1534Table Create Table 1535t1 CREATE TABLE `t1` ( 1536 `c1` varbinary(10) DEFAULT NULL 1537) ENGINE=MyISAM DEFAULT CHARSET=latin1 1538drop table t1; 1539select hex(concat(maketime(10,11,12))); 1540hex(concat(maketime(10,11,12))) 154131303A31313A3132 1542create table t1 as select concat(maketime(10,11,12)) as c1; 1543show create table t1; 1544Table Create Table 1545t1 CREATE TABLE `t1` ( 1546 `c1` varbinary(10) DEFAULT NULL 1547) ENGINE=MyISAM DEFAULT CHARSET=latin1 1548drop table t1; 1549select hex(get_format(DATE,'USA')); 1550hex(get_format(DATE,'USA')) 1551256D2E25642E2559 1552create table t1 as select get_format(DATE,'USA') as c1; 1553show create table t1; 1554Table Create Table 1555t1 CREATE TABLE `t1` ( 1556 `c1` varbinary(17) DEFAULT NULL 1557) ENGINE=MyISAM DEFAULT CHARSET=latin1 1558drop table t1; 1559select hex(left(concat(from_unixtime(1111885200)),4)); 1560hex(left(concat(from_unixtime(1111885200)),4)) 156132303035 1562create table t1 as select concat(from_unixtime(1111885200)) as c1; 1563show create table t1; 1564Table Create Table 1565t1 CREATE TABLE `t1` ( 1566 `c1` varbinary(19) DEFAULT NULL 1567) ENGINE=MyISAM DEFAULT CHARSET=latin1 1568drop table t1; 1569select hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00'))); 1570hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00'))) 1571323030332D31322D33312032303A30303A3030 1572create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')) as c1; 1573show create table t1; 1574Table Create Table 1575t1 CREATE TABLE `t1` ( 1576 `c1` varbinary(19) DEFAULT NULL 1577) ENGINE=MyISAM DEFAULT CHARSET=latin1 1578drop table t1; 1579select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); 1580hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))) 1581323030342D30312D30322031323A30303A3030 1582create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day)) as c1; 1583show create table t1; 1584Table Create Table 1585t1 CREATE TABLE `t1` ( 1586 `c1` varbinary(19) DEFAULT NULL 1587) ENGINE=MyISAM DEFAULT CHARSET=latin1 1588select * from t1; 1589c1 15902004-01-02 12:00:00 1591drop table t1; 1592select hex(concat(makedate(2009,1))); 1593hex(concat(makedate(2009,1))) 1594323030392D30312D3031 1595create table t1 as select concat(makedate(2009,1)) as c1; 1596show create table t1; 1597Table Create Table 1598t1 CREATE TABLE `t1` ( 1599 `c1` varbinary(10) DEFAULT NULL 1600) ENGINE=MyISAM DEFAULT CHARSET=latin1 1601select * from t1; 1602c1 16032009-01-01 1604drop table t1; 1605create table t1 as select concat(now()) as c1; 1606show create table t1; 1607Table Create Table 1608t1 CREATE TABLE `t1` ( 1609 `c1` varbinary(19) DEFAULT NULL 1610) ENGINE=MyISAM DEFAULT CHARSET=latin1 1611drop table t1; 1612create table t1 as select concat(utc_timestamp()) as c1; 1613show create table t1; 1614Table Create Table 1615t1 CREATE TABLE `t1` ( 1616 `c1` varbinary(19) DEFAULT NULL 1617) ENGINE=MyISAM DEFAULT CHARSET=latin1 1618drop table t1; 1619create table t1 as select concat(sysdate()) as c1; 1620show create table t1; 1621Table Create Table 1622t1 CREATE TABLE `t1` ( 1623 `c1` varbinary(19) DEFAULT NULL 1624) ENGINE=MyISAM DEFAULT CHARSET=latin1 1625drop table t1; 1626select hex(concat(addtime('00:00:00','11:22:33'))); 1627hex(concat(addtime('00:00:00','11:22:33'))) 162831313A32323A3333 1629create table t1 as select concat(addtime('00:00:00','11:22:33')) as c1; 1630show create table t1; 1631Table Create Table 1632t1 CREATE TABLE `t1` ( 1633 `c1` varbinary(26) DEFAULT NULL 1634) ENGINE=MyISAM DEFAULT CHARSET=latin1 1635drop table t1; 1636select hex(concat(subtime('23:59:59','11:22:33'))); 1637hex(concat(subtime('23:59:59','11:22:33'))) 163831323A33373A3236 1639create table t1 as select concat(subtime('23:59:59','11:22:33')) as c1; 1640show create table t1; 1641Table Create Table 1642t1 CREATE TABLE `t1` ( 1643 `c1` varbinary(26) DEFAULT NULL 1644) ENGINE=MyISAM DEFAULT CHARSET=latin1 1645drop table t1; 1646select hex(elt(1,2,3)); 1647hex(elt(1,2,3)) 164832 1649create table t1 as select elt(1,2,3) as c1; 1650show create table t1; 1651Table Create Table 1652t1 CREATE TABLE `t1` ( 1653 `c1` varbinary(1) DEFAULT NULL 1654) ENGINE=MyISAM DEFAULT CHARSET=latin1 1655drop table t1; 1656select hex(export_set(1,2,3,4,2)); 1657hex(export_set(1,2,3,4,2)) 1658323433 1659create table t1 as select export_set(1,2,3,4,2) as c1; 1660show create table t1; 1661Table Create Table 1662t1 CREATE TABLE `t1` ( 1663 `c1` varbinary(127) DEFAULT NULL 1664) ENGINE=MyISAM DEFAULT CHARSET=latin1 1665drop table t1; 1666select hex(insert(1133,3,0,22)); 1667hex(insert(1133,3,0,22)) 1668313132323333 1669create table t1 as select insert(1133,3,0,22) as c1; 1670show create table t1; 1671Table Create Table 1672t1 CREATE TABLE `t1` ( 1673 `c1` varbinary(6) DEFAULT NULL 1674) ENGINE=MyISAM DEFAULT CHARSET=latin1 1675drop table t1; 1676select hex(lcase(123)); 1677hex(lcase(123)) 1678313233 1679create table t1 as select lcase(123) as c1; 1680show create table t1; 1681Table Create Table 1682t1 CREATE TABLE `t1` ( 1683 `c1` varbinary(3) DEFAULT NULL 1684) ENGINE=MyISAM DEFAULT CHARSET=latin1 1685drop table t1; 1686select hex(left(123,1)); 1687hex(left(123,1)) 168831 1689create table t1 as select left(123,1) as c1; 1690show create table t1; 1691Table Create Table 1692t1 CREATE TABLE `t1` ( 1693 `c1` varbinary(1) DEFAULT NULL 1694) ENGINE=MyISAM DEFAULT CHARSET=latin1 1695drop table t1; 1696select hex(lower(123)); 1697hex(lower(123)) 1698313233 1699create table t1 as select lower(123) as c1; 1700show create table t1; 1701Table Create Table 1702t1 CREATE TABLE `t1` ( 1703 `c1` varbinary(3) DEFAULT NULL 1704) ENGINE=MyISAM DEFAULT CHARSET=latin1 1705drop table t1; 1706select hex(lpad(1,2,0)); 1707hex(lpad(1,2,0)) 17083031 1709create table t1 as select lpad(1,2,0) as c1; 1710show create table t1; 1711Table Create Table 1712t1 CREATE TABLE `t1` ( 1713 `c1` varbinary(2) DEFAULT NULL 1714) ENGINE=MyISAM DEFAULT CHARSET=latin1 1715drop table t1; 1716select hex(ltrim(1)); 1717hex(ltrim(1)) 171831 1719create table t1 as select ltrim(1) as c1; 1720show create table t1; 1721Table Create Table 1722t1 CREATE TABLE `t1` ( 1723 `c1` varbinary(1) DEFAULT NULL 1724) ENGINE=MyISAM DEFAULT CHARSET=latin1 1725drop table t1; 1726select hex(mid(1,1,1)); 1727hex(mid(1,1,1)) 172831 1729create table t1 as select mid(1,1,1) as c1; 1730show create table t1; 1731Table Create Table 1732t1 CREATE TABLE `t1` ( 1733 `c1` varbinary(1) DEFAULT NULL 1734) ENGINE=MyISAM DEFAULT CHARSET=latin1 1735drop table t1; 1736select hex(repeat(1,2)); 1737hex(repeat(1,2)) 17383131 1739create table t1 as select repeat(1,2) as c1; 1740show create table t1; 1741Table Create Table 1742t1 CREATE TABLE `t1` ( 1743 `c1` varbinary(2) DEFAULT NULL 1744) ENGINE=MyISAM DEFAULT CHARSET=latin1 1745drop table t1; 1746select hex(replace(1,1,2)); 1747hex(replace(1,1,2)) 174832 1749create table t1 as select replace(1,1,2) as c1; 1750show create table t1; 1751Table Create Table 1752t1 CREATE TABLE `t1` ( 1753 `c1` varbinary(1) DEFAULT NULL 1754) ENGINE=MyISAM DEFAULT CHARSET=latin1 1755drop table t1; 1756select hex(reverse(12)); 1757hex(reverse(12)) 17583231 1759create table t1 as select reverse(12) as c1; 1760show create table t1; 1761Table Create Table 1762t1 CREATE TABLE `t1` ( 1763 `c1` varbinary(2) DEFAULT NULL 1764) ENGINE=MyISAM DEFAULT CHARSET=latin1 1765drop table t1; 1766select hex(right(123,1)); 1767hex(right(123,1)) 176833 1769create table t1 as select right(123,1) as c1; 1770show create table t1; 1771Table Create Table 1772t1 CREATE TABLE `t1` ( 1773 `c1` varbinary(1) DEFAULT NULL 1774) ENGINE=MyISAM DEFAULT CHARSET=latin1 1775drop table t1; 1776select hex(rpad(1,2,0)); 1777hex(rpad(1,2,0)) 17783130 1779create table t1 as select rpad(1,2,0) as c1; 1780show create table t1; 1781Table Create Table 1782t1 CREATE TABLE `t1` ( 1783 `c1` varbinary(2) DEFAULT NULL 1784) ENGINE=MyISAM DEFAULT CHARSET=latin1 1785drop table t1; 1786select hex(rtrim(1)); 1787hex(rtrim(1)) 178831 1789create table t1 as select rtrim(1) as c1; 1790show create table t1; 1791Table Create Table 1792t1 CREATE TABLE `t1` ( 1793 `c1` varbinary(1) DEFAULT NULL 1794) ENGINE=MyISAM DEFAULT CHARSET=latin1 1795drop table t1; 1796select hex(soundex(1)); 1797hex(soundex(1)) 1798 1799create table t1 as select soundex(1) as c1; 1800show create table t1; 1801Table Create Table 1802t1 CREATE TABLE `t1` ( 1803 `c1` varbinary(4) DEFAULT NULL 1804) ENGINE=MyISAM DEFAULT CHARSET=latin1 1805drop table t1; 1806select hex(substring(1,1,1)); 1807hex(substring(1,1,1)) 180831 1809create table t1 as select substring(1,1,1) as c1; 1810show create table t1; 1811Table Create Table 1812t1 CREATE TABLE `t1` ( 1813 `c1` varbinary(1) DEFAULT NULL 1814) ENGINE=MyISAM DEFAULT CHARSET=latin1 1815drop table t1; 1816select hex(trim(1)); 1817hex(trim(1)) 181831 1819create table t1 as select trim(1) as c1; 1820show create table t1; 1821Table Create Table 1822t1 CREATE TABLE `t1` ( 1823 `c1` varbinary(1) DEFAULT NULL 1824) ENGINE=MyISAM DEFAULT CHARSET=latin1 1825drop table t1; 1826select hex(ucase(1)); 1827hex(ucase(1)) 182831 1829create table t1 as select ucase(1) as c1; 1830show create table t1; 1831Table Create Table 1832t1 CREATE TABLE `t1` ( 1833 `c1` varbinary(1) DEFAULT NULL 1834) ENGINE=MyISAM DEFAULT CHARSET=latin1 1835drop table t1; 1836select hex(upper(1)); 1837hex(upper(1)) 183831 1839create table t1 as select upper(1) as c1; 1840show create table t1; 1841Table Create Table 1842t1 CREATE TABLE `t1` ( 1843 `c1` varbinary(1) DEFAULT NULL 1844) ENGINE=MyISAM DEFAULT CHARSET=latin1 1845drop table t1; 1846create table t1 as select repeat(' ', 64) as a limit 0; 1847show create table t1; 1848Table Create Table 1849t1 CREATE TABLE `t1` ( 1850 `a` varbinary(64) DEFAULT NULL 1851) ENGINE=MyISAM DEFAULT CHARSET=latin1 1852insert into t1 values ("1.1"), ("2.1"); 1853select a, hex(a) from t1; 1854a hex(a) 18551.1 312E31 18562.1 322E31 1857update t1 set a= a + 0.1; 1858select a, hex(a) from t1; 1859a hex(a) 18601.2000000000000002 312E32303030303030303030303030303032 18612.2 322E32 1862drop table t1; 1863create table t1 (a tinyint); 1864insert into t1 values (1); 1865select hex(concat(a)) from t1; 1866hex(concat(a)) 186731 1868create table t2 as select concat(a) from t1; 1869show create table t2; 1870Table Create Table 1871t2 CREATE TABLE `t2` ( 1872 `concat(a)` varbinary(4) DEFAULT NULL 1873) ENGINE=MyISAM DEFAULT CHARSET=latin1 1874drop table t1, t2; 1875create table t1 (a tinyint zerofill); 1876insert into t1 values (1), (10), (100); 1877select hex(concat(a)), a from t1; 1878hex(concat(a)) a 1879303031 001 1880303130 010 1881313030 100 1882drop table t1; 1883create table t1 (a tinyint(4) zerofill); 1884insert into t1 values (1), (10), (100); 1885select hex(concat(a)), a from t1; 1886hex(concat(a)) a 188730303031 0001 188830303130 0010 188930313030 0100 1890drop table t1; 1891create table t1 (a decimal(10,2)); 1892insert into t1 values (123.45); 1893select hex(concat(a)) from t1; 1894hex(concat(a)) 18953132332E3435 1896create table t2 as select concat(a) from t1; 1897show create table t2; 1898Table Create Table 1899t2 CREATE TABLE `t2` ( 1900 `concat(a)` varbinary(12) DEFAULT NULL 1901) ENGINE=MyISAM DEFAULT CHARSET=latin1 1902drop table t1, t2; 1903create table t1 (a smallint); 1904insert into t1 values (1); 1905select hex(concat(a)) from t1; 1906hex(concat(a)) 190731 1908create table t2 as select concat(a) from t1; 1909show create table t2; 1910Table Create Table 1911t2 CREATE TABLE `t2` ( 1912 `concat(a)` varbinary(6) DEFAULT NULL 1913) ENGINE=MyISAM DEFAULT CHARSET=latin1 1914drop table t1, t2; 1915create table t1 (a smallint zerofill); 1916insert into t1 values (1), (10), (100), (1000), (10000); 1917select hex(concat(a)), a from t1; 1918hex(concat(a)) a 19193030303031 00001 19203030303130 00010 19213030313030 00100 19223031303030 01000 19233130303030 10000 1924drop table t1; 1925create table t1 (a mediumint); 1926insert into t1 values (1); 1927select hex(concat(a)) from t1; 1928hex(concat(a)) 192931 1930create table t2 as select concat(a) from t1; 1931show create table t2; 1932Table Create Table 1933t2 CREATE TABLE `t2` ( 1934 `concat(a)` varbinary(9) DEFAULT NULL 1935) ENGINE=MyISAM DEFAULT CHARSET=latin1 1936drop table t1, t2; 1937create table t1 (a mediumint zerofill); 1938insert into t1 values (1), (10), (100), (1000), (10000); 1939select hex(concat(a)), a from t1; 1940hex(concat(a)) a 19413030303030303031 00000001 19423030303030303130 00000010 19433030303030313030 00000100 19443030303031303030 00001000 19453030303130303030 00010000 1946drop table t1; 1947create table t1 (a int); 1948insert into t1 values (1); 1949select hex(concat(a)) from t1; 1950hex(concat(a)) 195131 1952create table t2 as select concat(a) from t1; 1953show create table t2; 1954Table Create Table 1955t2 CREATE TABLE `t2` ( 1956 `concat(a)` varbinary(11) DEFAULT NULL 1957) ENGINE=MyISAM DEFAULT CHARSET=latin1 1958drop table t1, t2; 1959create table t1 (a int zerofill); 1960insert into t1 values (1), (10), (100), (1000), (10000); 1961select hex(concat(a)), a from t1; 1962hex(concat(a)) a 196330303030303030303031 0000000001 196430303030303030303130 0000000010 196530303030303030313030 0000000100 196630303030303031303030 0000001000 196730303030303130303030 0000010000 1968drop table t1; 1969create table t1 (a bigint); 1970insert into t1 values (1); 1971select hex(concat(a)) from t1; 1972hex(concat(a)) 197331 1974create table t2 as select concat(a) from t1; 1975show create table t2; 1976Table Create Table 1977t2 CREATE TABLE `t2` ( 1978 `concat(a)` varbinary(20) DEFAULT NULL 1979) ENGINE=MyISAM DEFAULT CHARSET=latin1 1980drop table t1, t2; 1981create table t1 (a bigint zerofill); 1982insert into t1 values (1), (10), (100), (1000), (10000); 1983select hex(concat(a)), a from t1; 1984hex(concat(a)) a 19853030303030303030303030303030303030303031 00000000000000000001 19863030303030303030303030303030303030303130 00000000000000000010 19873030303030303030303030303030303030313030 00000000000000000100 19883030303030303030303030303030303031303030 00000000000000001000 19893030303030303030303030303030303130303030 00000000000000010000 1990drop table t1; 1991create table t1 (a float); 1992insert into t1 values (123.456); 1993select hex(concat(a)) from t1; 1994hex(concat(a)) 19953132332E343536 1996select concat(a) from t1; 1997concat(a) 1998123.456 1999create table t2 as select concat(a) from t1; 2000show create table t2; 2001Table Create Table 2002t2 CREATE TABLE `t2` ( 2003 `concat(a)` varbinary(12) DEFAULT NULL 2004) ENGINE=MyISAM DEFAULT CHARSET=latin1 2005drop table t1, t2; 2006create table t1 (a float zerofill); 2007insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 2008select hex(concat(a)), a from t1; 2009hex(concat(a)) a 2010303030303030303030312E31 0000000001.1 2011303030303030303031302E31 0000000010.1 2012303030303030303130302E31 0000000100.1 2013303030303030313030302E31 0000001000.1 2014303030303031303030302E31 0000010000.1 2015drop table t1; 2016create table t1 (a double); 2017insert into t1 values (123.456); 2018select hex(concat(a)) from t1; 2019hex(concat(a)) 20203132332E343536 2021select concat(a) from t1; 2022concat(a) 2023123.456 2024create table t2 as select concat(a) from t1; 2025show create table t2; 2026Table Create Table 2027t2 CREATE TABLE `t2` ( 2028 `concat(a)` varbinary(22) DEFAULT NULL 2029) ENGINE=MyISAM DEFAULT CHARSET=latin1 2030drop table t1, t2; 2031create table t1 (a double zerofill); 2032insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 2033select hex(concat(a)), a from t1; 2034hex(concat(a)) a 203530303030303030303030303030303030303030312E31 00000000000000000001.1 203630303030303030303030303030303030303031302E31 00000000000000000010.1 203730303030303030303030303030303030303130302E31 00000000000000000100.1 203830303030303030303030303030303030313030302E31 00000000000000001000.1 203930303030303030303030303030303031303030302E31 00000000000000010000.1 2040drop table t1; 2041create table t1 (a year(2)); 2042Warnings: 2043Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 2044insert into t1 values (1); 2045select hex(concat(a)) from t1; 2046hex(concat(a)) 20473031 2048create table t2 as select concat(a) from t1; 2049show create table t2; 2050Table Create Table 2051t2 CREATE TABLE `t2` ( 2052 `concat(a)` varbinary(2) DEFAULT NULL 2053) ENGINE=MyISAM DEFAULT CHARSET=latin1 2054drop table t1, t2; 2055create table t1 (a year); 2056insert into t1 values (1); 2057select hex(concat(a)) from t1; 2058hex(concat(a)) 205932303031 2060create table t2 as select concat(a) from t1; 2061show create table t2; 2062Table Create Table 2063t2 CREATE TABLE `t2` ( 2064 `concat(a)` varbinary(4) DEFAULT NULL 2065) ENGINE=MyISAM DEFAULT CHARSET=latin1 2066drop table t1, t2; 2067create table t1 (a bit(64)); 2068insert into t1 values (1); 2069select hex(concat(a)) from t1; 2070hex(concat(a)) 20710000000000000001 2072create table t2 as select concat(a) from t1; 2073show create table t2; 2074Table Create Table 2075t2 CREATE TABLE `t2` ( 2076 `concat(a)` varbinary(64) DEFAULT NULL 2077) ENGINE=MyISAM DEFAULT CHARSET=latin1 2078drop table t1, t2; 2079create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 2080insert into t1 values (0); 2081insert into t1 values (20010203040506); 2082insert into t1 values (19800203040506); 2083insert into t1 values ('2001-02-03 04:05:06'); 2084select hex(concat(a)) from t1; 2085hex(concat(a)) 2086303030302D30302D30302030303A30303A3030 2087323030312D30322D30332030343A30353A3036 2088313938302D30322D30332030343A30353A3036 2089323030312D30322D30332030343A30353A3036 2090select concat(a) from t1; 2091concat(a) 20920000-00-00 00:00:00 20932001-02-03 04:05:06 20941980-02-03 04:05:06 20952001-02-03 04:05:06 2096create table t2 as select concat(a) from t1; 2097show create table t2; 2098Table Create Table 2099t2 CREATE TABLE `t2` ( 2100 `concat(a)` varbinary(19) DEFAULT NULL 2101) ENGINE=MyISAM DEFAULT CHARSET=latin1 2102drop table t1, t2; 2103create table t1 (a date); 2104insert into t1 values ('2001-02-03'); 2105insert into t1 values (20010203); 2106select hex(concat(a)) from t1; 2107hex(concat(a)) 2108323030312D30322D3033 2109323030312D30322D3033 2110create table t2 as select concat(a) from t1; 2111show create table t2; 2112Table Create Table 2113t2 CREATE TABLE `t2` ( 2114 `concat(a)` varbinary(10) DEFAULT NULL 2115) ENGINE=MyISAM DEFAULT CHARSET=latin1 2116drop table t1, t2; 2117create table t1 (a time); 2118insert into t1 values (1); 2119insert into t1 values ('01:02:03'); 2120select hex(concat(a)) from t1; 2121hex(concat(a)) 212230303A30303A3031 212330313A30323A3033 2124select concat(a) from t1; 2125concat(a) 212600:00:01 212701:02:03 2128create table t2 as select concat(a) from t1; 2129show create table t2; 2130Table Create Table 2131t2 CREATE TABLE `t2` ( 2132 `concat(a)` varbinary(10) DEFAULT NULL 2133) ENGINE=MyISAM DEFAULT CHARSET=latin1 2134drop table t1, t2; 2135create table t1 (a datetime); 2136insert into t1 values ('2001-02-03 04:05:06'); 2137insert into t1 values (20010203040506); 2138select hex(concat(a)) from t1; 2139hex(concat(a)) 2140323030312D30322D30332030343A30353A3036 2141323030312D30322D30332030343A30353A3036 2142create table t2 as select concat(a) from t1; 2143show create table t2; 2144Table Create Table 2145t2 CREATE TABLE `t2` ( 2146 `concat(a)` varbinary(19) DEFAULT NULL 2147) ENGINE=MyISAM DEFAULT CHARSET=latin1 2148drop table t1, t2; 2149create table t1 (a tinyint); 2150insert into t1 values (1); 2151create view v1(a) as select concat(a) from t1; 2152show columns from v1; 2153Field Type Null Key Default Extra 2154a varbinary(4) YES NULL 2155select hex(a) from v1; 2156hex(a) 215731 2158drop table t1; 2159drop view v1; 2160create table t1 (a tinyint zerofill); 2161insert into t1 values (1), (10), (100); 2162create view v1(a) as select concat(a) from t1; 2163show columns from v1; 2164Field Type Null Key Default Extra 2165a varbinary(3) YES NULL 2166select hex(a) from v1; 2167hex(a) 2168303031 2169303130 2170313030 2171drop table t1; 2172drop view v1; 2173create table t1 (a tinyint(30) zerofill); 2174insert into t1 values (1), (10), (100); 2175create view v1(a) as select concat(a) from t1; 2176show columns from v1; 2177Field Type Null Key Default Extra 2178a varbinary(30) YES NULL 2179select hex(a) from v1; 2180hex(a) 2181303030303030303030303030303030303030303030303030303030303031 2182303030303030303030303030303030303030303030303030303030303130 2183303030303030303030303030303030303030303030303030303030313030 2184drop table t1; 2185drop view v1; 2186create table t1 (a decimal(10,2)); 2187insert into t1 values (123.45); 2188create view v1(a) as select concat(a) from t1; 2189show columns from v1; 2190Field Type Null Key Default Extra 2191a varbinary(12) YES NULL 2192select hex(a) from v1; 2193hex(a) 21943132332E3435 2195drop table t1; 2196drop view v1; 2197create table t1 (a smallint); 2198insert into t1 values (1); 2199create view v1(a) as select concat(a) from t1; 2200show columns from v1; 2201Field Type Null Key Default Extra 2202a varbinary(6) YES NULL 2203select hex(a) from v1; 2204hex(a) 220531 2206drop table t1; 2207drop view v1; 2208create table t1 (a smallint zerofill); 2209insert into t1 values (1), (10), (100), (1000), (10000); 2210create view v1(a) as select concat(a) from t1; 2211show columns from v1; 2212Field Type Null Key Default Extra 2213a varbinary(5) YES NULL 2214select hex(a) from v1; 2215hex(a) 22163030303031 22173030303130 22183030313030 22193031303030 22203130303030 2221drop table t1; 2222drop view v1; 2223create table t1 (a mediumint); 2224insert into t1 values (1); 2225create view v1(a) as select concat(a) from t1; 2226show columns from v1; 2227Field Type Null Key Default Extra 2228a varbinary(9) YES NULL 2229select hex(a) from v1; 2230hex(a) 223131 2232drop table t1; 2233drop view v1; 2234create table t1 (a mediumint zerofill); 2235insert into t1 values (1), (10), (100), (1000), (10000); 2236create view v1(a) as select concat(a) from t1; 2237show columns from v1; 2238Field Type Null Key Default Extra 2239a varbinary(8) YES NULL 2240select hex(a) from v1; 2241hex(a) 22423030303030303031 22433030303030303130 22443030303030313030 22453030303031303030 22463030303130303030 2247drop table t1; 2248drop view v1; 2249create table t1 (a int); 2250insert into t1 values (1); 2251create view v1(a) as select concat(a) from t1; 2252show columns from v1; 2253Field Type Null Key Default Extra 2254a varbinary(11) YES NULL 2255select hex(a) from v1; 2256hex(a) 225731 2258drop table t1; 2259drop view v1; 2260create table t1 (a int zerofill); 2261insert into t1 values (1), (10), (100), (1000), (10000); 2262create view v1(a) as select concat(a) from t1; 2263show columns from v1; 2264Field Type Null Key Default Extra 2265a varbinary(10) YES NULL 2266select hex(a) from v1; 2267hex(a) 226830303030303030303031 226930303030303030303130 227030303030303030313030 227130303030303031303030 227230303030303130303030 2273drop table t1; 2274drop view v1; 2275create table t1 (a bigint); 2276insert into t1 values (1); 2277create view v1(a) as select concat(a) from t1; 2278show columns from v1; 2279Field Type Null Key Default Extra 2280a varbinary(20) YES NULL 2281select hex(a) from v1; 2282hex(a) 228331 2284drop table t1; 2285drop view v1; 2286create table t1 (a bigint zerofill); 2287insert into t1 values (1), (10), (100), (1000), (10000); 2288create view v1(a) as select concat(a) from t1; 2289show columns from v1; 2290Field Type Null Key Default Extra 2291a varbinary(20) YES NULL 2292select hex(a) from v1; 2293hex(a) 22943030303030303030303030303030303030303031 22953030303030303030303030303030303030303130 22963030303030303030303030303030303030313030 22973030303030303030303030303030303031303030 22983030303030303030303030303030303130303030 2299drop table t1; 2300drop view v1; 2301create table t1 (a float); 2302insert into t1 values (123.456); 2303create view v1(a) as select concat(a) from t1; 2304show columns from v1; 2305Field Type Null Key Default Extra 2306a varbinary(12) YES NULL 2307select hex(a) from v1; 2308hex(a) 23093132332E343536 2310drop table t1; 2311drop view v1; 2312create table t1 (a float zerofill); 2313insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 2314create view v1(a) as select concat(a) from t1; 2315show columns from v1; 2316Field Type Null Key Default Extra 2317a varbinary(12) YES NULL 2318select hex(a) from v1; 2319hex(a) 2320303030303030303030312E31 2321303030303030303031302E31 2322303030303030303130302E31 2323303030303030313030302E31 2324303030303031303030302E31 2325drop table t1; 2326drop view v1; 2327create table t1 (a double); 2328insert into t1 values (123.456); 2329select concat(a) from t1; 2330concat(a) 2331123.456 2332create view v1(a) as select concat(a) from t1; 2333show columns from v1; 2334Field Type Null Key Default Extra 2335a varbinary(22) YES NULL 2336select hex(a) from v1; 2337hex(a) 23383132332E343536 2339drop table t1; 2340drop view v1; 2341create table t1 (a double zerofill); 2342insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 2343create view v1(a) as select concat(a) from t1; 2344show columns from v1; 2345Field Type Null Key Default Extra 2346a varbinary(22) YES NULL 2347select hex(a) from v1; 2348hex(a) 234930303030303030303030303030303030303030312E31 235030303030303030303030303030303030303031302E31 235130303030303030303030303030303030303130302E31 235230303030303030303030303030303030313030302E31 235330303030303030303030303030303031303030302E31 2354drop table t1; 2355drop view v1; 2356create table t1 (a year(2)); 2357Warnings: 2358Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 2359insert into t1 values (1); 2360create view v1(a) as select concat(a) from t1; 2361show columns from v1; 2362Field Type Null Key Default Extra 2363a varbinary(2) YES NULL 2364select hex(a) from v1; 2365hex(a) 23663031 2367drop table t1; 2368drop view v1; 2369create table t1 (a year); 2370insert into t1 values (1); 2371create view v1(a) as select concat(a) from t1; 2372show columns from v1; 2373Field Type Null Key Default Extra 2374a varbinary(4) YES NULL 2375select hex(a) from v1; 2376hex(a) 237732303031 2378drop table t1; 2379drop view v1; 2380create table t1 (a bit(64)); 2381insert into t1 values (1); 2382create view v1(a) as select concat(a) from t1; 2383show columns from v1; 2384Field Type Null Key Default Extra 2385a varbinary(64) YES NULL 2386select hex(a) from v1; 2387hex(a) 23880000000000000001 2389drop table t1; 2390drop view v1; 2391create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 2392insert into t1 values (0); 2393insert into t1 values (20010203040506); 2394insert into t1 values (19800203040506); 2395insert into t1 values ('2001-02-03 04:05:06'); 2396create view v1(a) as select concat(a) from t1; 2397show columns from v1; 2398Field Type Null Key Default Extra 2399a varbinary(19) YES NULL 2400select hex(a) from v1; 2401hex(a) 2402303030302D30302D30302030303A30303A3030 2403323030312D30322D30332030343A30353A3036 2404313938302D30322D30332030343A30353A3036 2405323030312D30322D30332030343A30353A3036 2406drop table t1; 2407drop view v1; 2408create table t1 (a date); 2409insert into t1 values ('2001-02-03'); 2410insert into t1 values (20010203); 2411create view v1(a) as select concat(a) from t1; 2412show columns from v1; 2413Field Type Null Key Default Extra 2414a varbinary(10) YES NULL 2415select hex(a) from v1; 2416hex(a) 2417323030312D30322D3033 2418323030312D30322D3033 2419drop table t1; 2420drop view v1; 2421create table t1 (a time); 2422insert into t1 values (1); 2423insert into t1 values ('01:02:03'); 2424create view v1(a) as select concat(a) from t1; 2425show columns from v1; 2426Field Type Null Key Default Extra 2427a varbinary(10) YES NULL 2428select hex(a) from v1; 2429hex(a) 243030303A30303A3031 243130313A30323A3033 2432drop table t1; 2433drop view v1; 2434create table t1 (a datetime); 2435insert into t1 values ('2001-02-03 04:05:06'); 2436insert into t1 values (20010203040506); 2437create view v1(a) as select concat(a) from t1; 2438show columns from v1; 2439Field Type Null Key Default Extra 2440a varbinary(19) YES NULL 2441select hex(a) from v1; 2442hex(a) 2443323030312D30322D30332030343A30353A3036 2444323030312D30322D30332030343A30353A3036 2445drop table t1; 2446drop view v1; 2447create function f1 (par1 int) returns int 2448begin 2449return concat(par1); 2450end| 2451set @a= f1(1); 2452select hex(@a); 2453hex(@a) 24541 2455select hex(concat(f1(1))); 2456hex(concat(f1(1))) 245731 2458create table t1 as select f1(1) as c1; 2459show create table t1; 2460Table Create Table 2461t1 CREATE TABLE `t1` ( 2462 `c1` int(11) DEFAULT NULL 2463) ENGINE=MyISAM DEFAULT CHARSET=latin1 2464drop table t1; 2465create table t1 as select concat(f1(1)) as c1; 2466show create table t1; 2467Table Create Table 2468t1 CREATE TABLE `t1` ( 2469 `c1` varchar(11) DEFAULT NULL 2470) ENGINE=MyISAM DEFAULT CHARSET=latin1 2471create view v1 as select concat(f1(1)) as c1; 2472show columns from v1; 2473Field Type Null Key Default Extra 2474c1 varchar(11) YES NULL 2475drop table t1; 2476drop view v1; 2477drop function f1; 2478create function f1 (par1 decimal(18,2)) returns decimal(18,2) 2479begin 2480return concat(par1); 2481end| 2482set @a= f1(123.45); 2483select hex(@a); 2484hex(@a) 24857B 2486select hex(concat(f1(123.45))); 2487hex(concat(f1(123.45))) 24883132332E3435 2489create table t1 as select f1(123.45) as c1; 2490show create table t1; 2491Table Create Table 2492t1 CREATE TABLE `t1` ( 2493 `c1` decimal(18,2) DEFAULT NULL 2494) ENGINE=MyISAM DEFAULT CHARSET=latin1 2495drop table t1; 2496create table t1 as select concat(f1(123.45)) as c1; 2497show create table t1; 2498Table Create Table 2499t1 CREATE TABLE `t1` ( 2500 `c1` varchar(20) DEFAULT NULL 2501) ENGINE=MyISAM DEFAULT CHARSET=latin1 2502create view v1 as select concat(f1(123.45)) as c1; 2503show columns from v1; 2504Field Type Null Key Default Extra 2505c1 varchar(20) YES NULL 2506drop table t1; 2507drop view v1; 2508drop function f1; 2509create function f1 (par1 float) returns float 2510begin 2511return concat(par1); 2512end| 2513set @a= f1(123.45); 2514select hex(@a); 2515hex(@a) 25167B 2517select hex(concat(f1(123.45))); 2518hex(concat(f1(123.45))) 25193132332E3435 2520create table t1 as select f1(123.45) as c1; 2521show create table t1; 2522Table Create Table 2523t1 CREATE TABLE `t1` ( 2524 `c1` float DEFAULT NULL 2525) ENGINE=MyISAM DEFAULT CHARSET=latin1 2526drop table t1; 2527create table t1 as select concat(f1(123.45)) as c1; 2528show create table t1; 2529Table Create Table 2530t1 CREATE TABLE `t1` ( 2531 `c1` varchar(12) DEFAULT NULL 2532) ENGINE=MyISAM DEFAULT CHARSET=latin1 2533create view v1 as select concat(f1(123.45)) as c1; 2534show columns from v1; 2535Field Type Null Key Default Extra 2536c1 varchar(12) YES NULL 2537drop table t1; 2538drop view v1; 2539drop function f1; 2540create function f1 (par1 date) returns date 2541begin 2542return concat(par1); 2543end| 2544set @a= f1(cast('2001-01-02' as date)); 2545select hex(@a); 2546hex(@a) 2547323030312D30312D3032 2548select hex(concat(f1(cast('2001-01-02' as date)))); 2549hex(concat(f1(cast('2001-01-02' as date)))) 2550323030312D30312D3032 2551create table t1 as select f1(cast('2001-01-02' as date)) as c1; 2552show create table t1; 2553Table Create Table 2554t1 CREATE TABLE `t1` ( 2555 `c1` date DEFAULT NULL 2556) ENGINE=MyISAM DEFAULT CHARSET=latin1 2557drop table t1; 2558create table t1 as select concat(f1(cast('2001-01-02' as date))) as c1; 2559show create table t1; 2560Table Create Table 2561t1 CREATE TABLE `t1` ( 2562 `c1` varchar(10) DEFAULT NULL 2563) ENGINE=MyISAM DEFAULT CHARSET=latin1 2564create view v1 as select concat(f1(cast('2001-01-02' as date))) as c1; 2565show columns from v1; 2566Field Type Null Key Default Extra 2567c1 varchar(10) YES NULL 2568drop table t1; 2569drop view v1; 2570drop function f1; 2571# 2572# End of WL#2649 Number-to-string conversions 2573# 2574# 2575# Bug#54668 User variable assignments get wrong type 2576# 2577SET @x=md5('a'); 2578SELECT charset(@x), collation(@x); 2579charset(@x) collation(@x) 2580binary binary 2581SET @x=old_password('a'); 2582SELECT charset(@x), collation(@x); 2583charset(@x) collation(@x) 2584binary binary 2585SET @x=password('a'); 2586SELECT charset(@x), collation(@x); 2587charset(@x) collation(@x) 2588binary binary 2589SET @x=sha('a'); 2590SELECT charset(@x), collation(@x); 2591charset(@x) collation(@x) 2592binary binary 2593SET @x=sha1('a'); 2594SELECT charset(@x), collation(@x); 2595charset(@x) collation(@x) 2596binary binary 2597SET @x=astext(point(1,2)); 2598SELECT charset(@x), collation(@x); 2599charset(@x) collation(@x) 2600binary binary 2601SET @x=aswkt(point(1,2)); 2602SELECT charset(@x), collation(@x); 2603charset(@x) collation(@x) 2604binary binary 2605# 2606# Bug#54916 GROUP_CONCAT + IFNULL truncates output 2607# 2608SELECT @@collation_connection; 2609@@collation_connection 2610binary 2611CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM; 2612INSERT INTO t1 VALUES (1234567); 2613SELECT GROUP_CONCAT(IFNULL(a,'')) FROM t1; 2614GROUP_CONCAT(IFNULL(a,'')) 26151234567 2616SELECT GROUP_CONCAT(IF(a,a,'')) FROM t1; 2617GROUP_CONCAT(IF(a,a,'')) 26181234567 2619SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1; 2620GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) 26211234567 2622SELECT COALESCE(a,'') FROM t1 GROUP BY 1; 2623Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2624def COALESCE(a,'') 253 9 7 Y 128 39 63 2625COALESCE(a,'') 26261234567 2627# All columns must be VARCHAR(9) with the same length: 2628CREATE TABLE t2 AS 2629SELECT 2630CONCAT(a), 2631IFNULL(a,''), 2632IF(a,a,''), 2633CASE WHEN a THEN a ELSE '' END, 2634COALESCE(a,'') 2635FROM t1; 2636SHOW CREATE TABLE t2; 2637Table Create Table 2638t2 CREATE TABLE `t2` ( 2639 `CONCAT(a)` varbinary(9) DEFAULT NULL, 2640 `IFNULL(a,'')` varbinary(9) NOT NULL, 2641 `IF(a,a,'')` varbinary(9) DEFAULT NULL, 2642 `CASE WHEN a THEN a ELSE '' END` varbinary(9) DEFAULT NULL, 2643 `COALESCE(a,'')` varbinary(9) DEFAULT NULL 2644) ENGINE=MyISAM DEFAULT CHARSET=latin1 2645DROP TABLE t2; 2646CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1; 2647SHOW CREATE TABLE t2; 2648Table Create Table 2649t2 CREATE TABLE `t2` ( 2650 `CONCAT_WS(1,2,3)` varbinary(3) DEFAULT NULL 2651) ENGINE=MyISAM DEFAULT CHARSET=latin1 2652DROP TABLE t2; 2653CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1; 2654SHOW CREATE TABLE t2; 2655Table Create Table 2656t2 CREATE TABLE `t2` ( 2657 `INSERT(1133,3,0,22)` varbinary(6) DEFAULT NULL 2658) ENGINE=MyISAM DEFAULT CHARSET=latin1 2659DROP TABLE t2; 2660CREATE TABLE t2 AS SELECT LCASE(a) FROM t1; 2661SHOW CREATE TABLE t2; 2662Table Create Table 2663t2 CREATE TABLE `t2` ( 2664 `LCASE(a)` varbinary(9) DEFAULT NULL 2665) ENGINE=MyISAM DEFAULT CHARSET=latin1 2666DROP TABLE t2; 2667CREATE TABLE t2 AS SELECT UCASE(a) FROM t1; 2668SHOW CREATE TABLE t2; 2669Table Create Table 2670t2 CREATE TABLE `t2` ( 2671 `UCASE(a)` varbinary(9) DEFAULT NULL 2672) ENGINE=MyISAM DEFAULT CHARSET=latin1 2673DROP TABLE t2; 2674CREATE TABLE t2 AS SELECT REPEAT(1,2) FROM t1; 2675SHOW CREATE TABLE t2; 2676Table Create Table 2677t2 CREATE TABLE `t2` ( 2678 `REPEAT(1,2)` varbinary(2) DEFAULT NULL 2679) ENGINE=MyISAM DEFAULT CHARSET=latin1 2680DROP TABLE t2; 2681CREATE TABLE t2 AS SELECT LEFT(123,2) FROM t1; 2682SHOW CREATE TABLE t2; 2683Table Create Table 2684t2 CREATE TABLE `t2` ( 2685 `LEFT(123,2)` varbinary(2) DEFAULT NULL 2686) ENGINE=MyISAM DEFAULT CHARSET=latin1 2687DROP TABLE t2; 2688CREATE TABLE t2 AS SELECT RIGHT(123,2) FROM t1; 2689SHOW CREATE TABLE t2; 2690Table Create Table 2691t2 CREATE TABLE `t2` ( 2692 `RIGHT(123,2)` varbinary(2) DEFAULT NULL 2693) ENGINE=MyISAM DEFAULT CHARSET=latin1 2694DROP TABLE t2; 2695CREATE TABLE t2 AS SELECT LTRIM(123) FROM t1; 2696SHOW CREATE TABLE t2; 2697Table Create Table 2698t2 CREATE TABLE `t2` ( 2699 `LTRIM(123)` varbinary(3) DEFAULT NULL 2700) ENGINE=MyISAM DEFAULT CHARSET=latin1 2701DROP TABLE t2; 2702CREATE TABLE t2 AS SELECT RTRIM(123) FROM t1; 2703SHOW CREATE TABLE t2; 2704Table Create Table 2705t2 CREATE TABLE `t2` ( 2706 `RTRIM(123)` varbinary(3) DEFAULT NULL 2707) ENGINE=MyISAM DEFAULT CHARSET=latin1 2708DROP TABLE t2; 2709CREATE TABLE t2 AS SELECT ELT(1,111,222,333) FROM t1; 2710SHOW CREATE TABLE t2; 2711Table Create Table 2712t2 CREATE TABLE `t2` ( 2713 `ELT(1,111,222,333)` varbinary(3) DEFAULT NULL 2714) ENGINE=MyISAM DEFAULT CHARSET=latin1 2715DROP TABLE t2; 2716CREATE TABLE t2 AS SELECT REPLACE(111,2,3) FROM t1; 2717SHOW CREATE TABLE t2; 2718Table Create Table 2719t2 CREATE TABLE `t2` ( 2720 `REPLACE(111,2,3)` varbinary(3) DEFAULT NULL 2721) ENGINE=MyISAM DEFAULT CHARSET=latin1 2722DROP TABLE t2; 2723CREATE TABLE t2 AS SELECT SUBSTRING_INDEX(111,111,1) FROM t1; 2724SHOW CREATE TABLE t2; 2725Table Create Table 2726t2 CREATE TABLE `t2` ( 2727 `SUBSTRING_INDEX(111,111,1)` varbinary(3) DEFAULT NULL 2728) ENGINE=MyISAM DEFAULT CHARSET=latin1 2729DROP TABLE t2; 2730CREATE TABLE t2 AS SELECT MAKE_SET(111,222,3) FROM t1; 2731SHOW CREATE TABLE t2; 2732Table Create Table 2733t2 CREATE TABLE `t2` ( 2734 `MAKE_SET(111,222,3)` varbinary(5) DEFAULT NULL 2735) ENGINE=MyISAM DEFAULT CHARSET=latin1 2736DROP TABLE t2; 2737CREATE TABLE t2 AS SELECT SOUNDEX(1) FROM t1; 2738SHOW CREATE TABLE t2; 2739Table Create Table 2740t2 CREATE TABLE `t2` ( 2741 `SOUNDEX(1)` varbinary(4) DEFAULT NULL 2742) ENGINE=MyISAM DEFAULT CHARSET=latin1 2743DROP TABLE t2; 2744CREATE TABLE t2 AS SELECT EXPORT_SET(1,'Y','N','',8); 2745SHOW CREATE TABLE t2; 2746Table Create Table 2747t2 CREATE TABLE `t2` ( 2748 `EXPORT_SET(1,'Y','N','',8)` varbinary(64) DEFAULT NULL 2749) ENGINE=MyISAM DEFAULT CHARSET=latin1 2750DROP TABLE t2; 2751DROP TABLE t1; 2752# 2753# End of Bug#54916 2754# 2755# 2756# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields 2757# 2758SELECT @@collation_connection; 2759@@collation_connection 2760binary 2761CREATE TABLE t1 ( 2762id INT(11) DEFAULT NULL, 2763date_column DATE DEFAULT NULL, 2764KEY(date_column)); 2765INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01'); 2766EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; 2767id select_type table type possible_keys key key_len ref rows Extra 27681 SIMPLE t1 range date_column date_column 4 NULL 2 Using index condition 2769ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL; 2770EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; 2771id select_type table type possible_keys key key_len ref rows Extra 27721 SIMPLE t1 range date_column date_column 6 NULL 2 Using index condition 2773DROP TABLE t1; 2774# 2775# Bug #31384 DATE_ADD() and DATE_SUB() return binary data 2776# 2777SELECT @@collation_connection, @@character_set_results; 2778@@collation_connection @@character_set_results 2779binary binary 2780SELECT 2781CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, 2782CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, 2783CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, 2784CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; 2785field_str1 field_str2 field_date field_datetime 2786binary binary binary binary 2787CREATE TABLE t1 AS 2788SELECT 2789DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, 2790DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, 2791DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, 2792DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; 2793SHOW CREATE TABLE t1; 2794Table Create Table 2795t1 CREATE TABLE `t1` ( 2796 `field_str1` varbinary(19) DEFAULT NULL, 2797 `field1_str2` varbinary(19) DEFAULT NULL, 2798 `field_date` date DEFAULT NULL, 2799 `field_datetime` datetime DEFAULT NULL 2800) ENGINE=MyISAM DEFAULT CHARSET=latin1 2801DROP TABLE t1; 2802SELECT 2803DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1, 2804DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, 2805DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, 2806DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; 2807Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2808def field_str1 254 19 10 Y 128 0 63 2809def field1_str2 254 19 19 Y 128 0 63 2810def field_date 10 10 10 Y 128 0 63 2811def field_datetime 12 19 19 Y 128 0 63 2812field_str1 field1_str2 field_date field_datetime 28132007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00 2814SELECT 2815HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, 2816HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2, 2817HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, 2818HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; 2819field_str1 field1_str2 field_date field_datetime 2820323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 2821# 2822# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() 2823# 2824SELECT @@collation_connection, @@character_set_results; 2825@@collation_connection @@character_set_results 2826binary binary 2827SELECT 2828CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, 2829CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, 2830CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, 2831CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; 2832addtime1 addtime2 date_add1 date_add2 2833binary binary binary binary 2834CREATE TABLE t1 AS 2835SELECT 2836ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, 2837ADDTIME('10:01:01','10:00:00') AS addtime2, 2838DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, 2839DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; 2840SHOW CREATE TABLE t1; 2841Table Create Table 2842t1 CREATE TABLE `t1` ( 2843 `addtime1` varbinary(26) DEFAULT NULL, 2844 `addtime2` varbinary(26) DEFAULT NULL, 2845 `date_add1` varbinary(19) DEFAULT NULL, 2846 `date_add2` varbinary(19) DEFAULT NULL 2847) ENGINE=MyISAM DEFAULT CHARSET=latin1 2848SELECT * FROM t1; 2849addtime1 addtime2 date_add1 date_add2 285020:01:01 20:01:01 2001-01-01 10:01:11 2001-01-01 10:01:11 2851DROP TABLE t1; 2852# 2853# Bug#11926811 / Bug#60625 Illegal mix of collations 2854# 2855SELECT @@collation_connection; 2856@@collation_connection 2857binary 2858CREATE PROCEDURE p1() 2859BEGIN 2860DECLARE v_LastPaymentDate DATETIME DEFAULT NULL; 2861SELECT v_LastPaymentDate < NOW(); 2862EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW(); 2863SHOW WARNINGS; 2864EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW()); 2865END// 2866CALL p1; 2867v_LastPaymentDate < NOW() 2868NULL 2869id select_type table type possible_keys key key_len ref rows filtered Extra 28701 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2871Level Code Message 2872Note 1003 select v_LastPaymentDate@0 < current_timestamp() AS `v_LastPaymentDate < NOW()` 2873id select_type table type possible_keys key key_len ref rows filtered Extra 28741 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2875Warnings: 2876Note 1003 select concat(v_LastPaymentDate@0,current_timestamp()) AS `CONCAT(v_LastPaymentDate, NOW())` 2877DROP PROCEDURE p1; 2878# 2879# Bug#52159 returning time type from function and empty left join causes debug assertion 2880# 2881CREATE FUNCTION f1() RETURNS TIME RETURN 1; 2882CREATE TABLE t1 (b INT); 2883INSERT INTO t1 VALUES (0); 2884SELECT f1() FROM t1 LEFT JOIN (SELECT 1 AS a FROM t1 LIMIT 0) AS d ON 1 GROUP BY a; 2885f1() 288600:00:01 2887DROP FUNCTION f1; 2888DROP TABLE t1; 2889# 2890# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool) 2891# 2892SELECT @@collation_connection; 2893@@collation_connection 2894binary 2895SELECT CASE 1 WHEN 2 THEN ( - '3' ) END; 2896CASE 1 WHEN 2 THEN ( - '3' ) END 2897NULL 2898# 2899# MDEV-5702 Incorrect results are returned with NULLIF() 2900# 2901CREATE TABLE t1 (d DATE); 2902INSERT INTO t1 VALUES ('1999-11-11'),('2014-02-04'); 2903SELECT DISTINCT d, CAST(d AS CHAR), NULLIF(d,"2000-01-01") AS bad, NULLIF(CAST(d AS CHAR),"2000-01-01") AS good FROM t1; 2904d CAST(d AS CHAR) bad good 29051999-11-11 1999-11-11 1999-11-11 1999-11-11 29062014-02-04 2014-02-04 2014-02-04 2014-02-04 2907CREATE TABLE t2 AS SELECT DISTINCT d, NULLIF(d,'2000-01-01') AS bad FROM t1; 2908SHOW CREATE TABLE t2; 2909Table Create Table 2910t2 CREATE TABLE `t2` ( 2911 `d` date DEFAULT NULL, 2912 `bad` date DEFAULT NULL 2913) ENGINE=MyISAM DEFAULT CHARSET=latin1 2914DROP TABLE t1, t2; 2915SET NAMES latin1; 2916SET sql_mode=''; 2917CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); 2918INSERT INTO t1 VALUES (); 2919SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; 2920maketime(`a`,`a`,`a`) 292100:00:00.000000 2922DROP TABLE t1; 2923SET sql_mode=default; 2924# 2925# End of 5.5 tests 2926# 2927SET NAMES binary; 2928# 2929# MDEV-7149 Constant condition propagation erroneously applied for LIKE 2930# 2931CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 2932SHOW CREATE TABLE t1; 2933Table Create Table 2934t1 CREATE TABLE `t1` ( 2935 `c1` varbinary(10) DEFAULT NULL 2936) ENGINE=MyISAM DEFAULT CHARSET=latin1 2937INSERT INTO t1 VALUES ('a'),('a '); 2938SELECT * FROM t1 WHERE CONCAT(c1)='a'; 2939c1 2940a 2941SELECT * FROM t1 WHERE CONCAT(c1) LIKE 'a '; 2942c1 2943a 2944SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a '; 2945c1 2946EXPLAIN EXTENDED SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a '; 2947id select_type table type possible_keys key key_len ref rows filtered Extra 29481 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2949Warnings: 2950Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 0 2951DROP TABLE t1; 2952CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 2953SHOW CREATE TABLE t1; 2954Table Create Table 2955t1 CREATE TABLE `t1` ( 2956 `c1` varbinary(10) DEFAULT NULL 2957) ENGINE=MyISAM DEFAULT CHARSET=latin1 2958INSERT INTO t1 VALUES ('a'),('a '); 2959SELECT * FROM t1 WHERE 'a'=CONCAT(c1); 2960c1 2961a 2962SELECT * FROM t1 WHERE 'a ' LIKE CONCAT(c1); 2963c1 2964a 2965SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1); 2966c1 2967EXPLAIN EXTENDED SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1); 2968id select_type table type possible_keys key key_len ref rows filtered Extra 29691 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2970Warnings: 2971Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 0 2972DROP TABLE t1; 2973CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 2974SHOW CREATE TABLE t1; 2975Table Create Table 2976t1 CREATE TABLE `t1` ( 2977 `c1` varbinary(10) DEFAULT NULL 2978) ENGINE=MyISAM DEFAULT CHARSET=latin1 2979INSERT INTO t1 VALUES ('%'),('% '); 2980SELECT * FROM t1 WHERE '% '=CONCAT(c1); 2981c1 2982% 2983SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1); 2984c1 2985% 2986SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 2987c1 2988EXPLAIN EXTENDED SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 2989id select_type table type possible_keys key key_len ref rows filtered Extra 29901 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2991Warnings: 2992Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 0 2993DROP TABLE t1; 2994CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0; 2995SHOW CREATE TABLE t1; 2996Table Create Table 2997t1 CREATE TABLE `t1` ( 2998 `c1` varbinary(10) DEFAULT NULL 2999) ENGINE=MyISAM DEFAULT CHARSET=latin1 3000INSERT INTO t1 VALUES ('%'),('% '); 3001SELECT * FROM t1 WHERE '%'=CONCAT(c1); 3002c1 3003% 3004SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1); 3005c1 3006% 3007SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 3008c1 3009% 3010EXPLAIN EXTENDED SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1); 3011id select_type table type possible_keys key key_len ref rows filtered Extra 30121 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3013Warnings: 3014Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where '%' = concat(`test`.`t1`.`c1`) 3015DROP TABLE t1; 3016# 3017# MDEV-8694 Wrong result for SELECT..WHERE a NOT LIKE 'a ' AND a='a' 3018# 3019CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0; 3020SHOW CREATE TABLE t1; 3021Table Create Table 3022t1 CREATE TABLE `t1` ( 3023 `a` varbinary(10) DEFAULT NULL 3024) ENGINE=MyISAM DEFAULT CHARSET=latin1 3025INSERT INTO t1 VALUES ('a'),('a '); 3026SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a '; 3027a LENGTH(a) 3028a 1 3029SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; 3030a LENGTH(a) 3031a 1 3032EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; 3033id select_type table type possible_keys key key_len ref rows filtered Extra 30341 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3035Warnings: 3036Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' 3037DROP TABLE t1; 3038# 3039# End of MDEV-8694 3040# 3041# 3042# MDEV-7629 Regression: Bit and hex string literals changed column names in 10.0.14 3043# 3044SELECT _binary 0x7E, _binary X'7E', _binary B'01111110'; 3045_binary 0x7E _binary X'7E' _binary B'01111110' 3046~ ~ ~ 3047SET NAMES utf8, character_set_connection=binary; 3048# 3049# MDEV-13118 Wrong results with LOWER and UPPER and subquery 3050# 3051SET @save_optimizer_switch=@@optimizer_switch; 3052SET optimizer_switch=_latin1'derived_merge=on'; 3053CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; 3054SHOW CREATE TABLE t1; 3055Table Create Table 3056t1 CREATE TABLE `t1` ( 3057 `t` varbinary(10) DEFAULT NULL 3058) ENGINE=MyISAM DEFAULT CHARSET=latin1 3059INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); 3060SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; 3061c2 3062ABCDEFGHI-ABCDEFGHI 3063abcdefghi-abcdefghi 3064SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; 3065c2 3066ABCDEFGHI-ABCDEFGHI 3067abcdefghi-abcdefghi 3068DROP TABLE t1; 3069SET optimizer_switch=@save_optimizer_switch; 3070# 3071# End of 10.0 tests 3072# 3073# 3074# MDEV-8695 Wrong result for SELECT..WHERE varchar_column='a' AND CRC32(varchar_column)=3904355907 3075# 3076CREATE TABLE t1 (a VARBINARY(10)); 3077INSERT INTO t1 VALUES ('a'),('a '); 3078SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE CRC32(a)=3904355907; 3079a LENGTH(a) CRC32(a) 3080a 1 3904355907 3081SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)=3904355907; 3082a LENGTH(a) CRC32(a) 3083a 1 3904355907 3084# Okey to propagate 'a' into CRC32(a) 3085EXPLAIN EXTENDED SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)=3904355907; 3086id select_type table type possible_keys key key_len ref rows filtered Extra 30871 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3088Warnings: 3089Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)`,crc32(`test`.`t1`.`a`) AS `CRC32(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' 3090SELECT a, HEX(a) FROM t1 WHERE HEX(a)='61'; 3091a HEX(a) 3092a 61 3093SELECT *,HEX(a) FROM t1 WHERE a='a' AND HEX(a)='61'; 3094a HEX(a) 3095a 61 3096# Okey to propagate 'a' into HEX(a) 3097EXPLAIN EXTENDED SELECT *,HEX(a) FROM t1 WHERE a='a' AND HEX(a)='61'; 3098id select_type table type possible_keys key key_len ref rows filtered Extra 30991 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3100Warnings: 3101Note 1003 select `test`.`t1`.`a` AS `a`,hex(`test`.`t1`.`a`) AS `HEX(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' 3102SELECT * FROM t1 WHERE a='a'; 3103a 3104a 3105SELECT * FROM t1 WHERE LENGTH(a)=2; 3106a 3107a 3108SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2; 3109a 3110# Okey to propagate 'a' into LENGTH(a) 3111EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2; 3112id select_type table type possible_keys key key_len ref rows filtered Extra 31131 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3114Warnings: 3115Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 3116SELECT * FROM t1 WHERE a='a '; 3117a 3118a 3119SELECT * FROM t1 WHERE a='a ' AND LENGTH(a)=2; 3120a 3121a 3122# Okey to propagate 'a ' into LENGTH(a) 3123EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a ' AND LENGTH(a)=2; 3124id select_type table type possible_keys key key_len ref rows filtered Extra 31251 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3126Warnings: 3127Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a ' 3128DROP TABLE t1; 3129# 3130# MDEV-8723 Wrong result for SELECT..WHERE COLLATION(a)='binary' AND a='a' 3131# 3132CREATE TABLE t1 (a VARBINARY(10)); 3133INSERT INTO t1 VALUES ('a'),('A'); 3134SELECT * FROM t1 WHERE COLLATION(a)='binary' AND a='a'; 3135a 3136a 3137SELECT * FROM t1 WHERE CHARSET(a)='binary' AND a='a'; 3138a 3139a 3140SELECT * FROM t1 WHERE COERCIBILITY(a)=2 AND a='a'; 3141a 3142a 3143SELECT * FROM t1 WHERE WEIGHT_STRING(a)='a' AND a='a'; 3144a 3145a 3146EXPLAIN EXTENDED SELECT * FROM t1 WHERE COLLATION(a)='binary' AND a='a'; 3147id select_type table type possible_keys key key_len ref rows filtered Extra 31481 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3149Warnings: 3150Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 3151EXPLAIN EXTENDED SELECT * FROM t1 WHERE CHARSET(a)='binary' AND a='a'; 3152id select_type table type possible_keys key key_len ref rows filtered Extra 31531 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3154Warnings: 3155Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 3156EXPLAIN EXTENDED SELECT * FROM t1 WHERE COERCIBILITY(a)=2 AND a='a'; 3157id select_type table type possible_keys key key_len ref rows filtered Extra 31581 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3159Warnings: 3160Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' 3161EXPLAIN EXTENDED SELECT * FROM t1 WHERE WEIGHT_STRING(a)='a' AND a='a'; 3162id select_type table type possible_keys key key_len ref rows filtered Extra 31631 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3164Warnings: 3165Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' and weight_string(`test`.`t1`.`a`,0,0,1) = 'a' 3166DROP TABLE t1; 3167# 3168# 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 3169# 3170CREATE TABLE t1(a ENUM(0x6100,0x6200,0x6300) CHARACTER SET 'Binary'); 3171SHOW CREATE TABLE t1; 3172Table Create Table 3173t1 CREATE TABLE `t1` ( 3174 `a` enum('a\0','b\0','c\0') CHARACTER SET binary DEFAULT NULL 3175) ENGINE=MyISAM DEFAULT CHARSET=latin1 3176INSERT INTO t1 VALUES (1),(2),(3); 3177SELECT HEX(a) FROM t1 ORDER BY a; 3178HEX(a) 31796100 31806200 31816300 3182DROP TABLE t1; 31830x00 in the middle or in the end of a value 3184CREATE TABLE t1 (a ENUM(0x6100)); 3185SHOW CREATE TABLE t1; 3186Table Create Table 3187t1 CREATE TABLE `t1` ( 3188 `a` enum('a\0') DEFAULT NULL 3189) ENGINE=MyISAM DEFAULT CHARSET=latin1 3190INSERT INTO t1 VALUES (1); 3191SELECT HEX(a) FROM t1; 3192HEX(a) 31936100 3194DROP TABLE t1; 3195CREATE TABLE t1 (a ENUM(0x610062)); 3196SHOW CREATE TABLE t1; 3197Table Create Table 3198t1 CREATE TABLE `t1` ( 3199 `a` enum('a\0b') DEFAULT NULL 3200) ENGINE=MyISAM DEFAULT CHARSET=latin1 3201INSERT INTO t1 VALUES (1); 3202SELECT HEX(a) FROM t1; 3203HEX(a) 3204610062 3205DROP TABLE t1; 32060x00 in the beginning of the first value: 3207CREATE TABLE t1 (a ENUM(0x0061)); 3208SHOW CREATE TABLE t1; 3209Table Create Table 3210t1 CREATE TABLE `t1` ( 3211 `a` enum('\0a') DEFAULT NULL 3212) ENGINE=MyISAM DEFAULT CHARSET=latin1 3213INSERT INTO t1 VALUES(1); 3214SELECT HEX(a) FROM t1; 3215HEX(a) 32160061 3217DROP TABLE t1; 3218CREATE TABLE t1 (a ENUM(0x0061), b ENUM('b')); 3219SHOW CREATE TABLE t1; 3220Table Create Table 3221t1 CREATE TABLE `t1` ( 3222 `a` enum('\0a') DEFAULT NULL, 3223 `b` enum('b') DEFAULT NULL 3224) ENGINE=MyISAM DEFAULT CHARSET=latin1 3225INSERT INTO t1 VALUES (1,1); 3226SELECT HEX(a), HEX(b) FROM t1; 3227HEX(a) HEX(b) 32280061 62 3229DROP TABLE t1; 3230# 0x00 in the beginning of the second (and following) value of the *last* ENUM/SET in the table: 3231CREATE TABLE t1 (a ENUM('a',0x0061)); 3232SHOW CREATE TABLE t1; 3233Table Create Table 3234t1 CREATE TABLE `t1` ( 3235 `a` enum('a','\0a') DEFAULT NULL 3236) ENGINE=MyISAM DEFAULT CHARSET=latin1 3237INSERT INTO t1 VALUES (1),(2); 3238SELECT HEX(a) FROM t1 ORDER BY a; 3239HEX(a) 324061 32410061 3242DROP TABLE t1; 3243CREATE TABLE t1 (a ENUM('a'), b ENUM('b',0x0061)); 3244SHOW CREATE TABLE t1; 3245Table Create Table 3246t1 CREATE TABLE `t1` ( 3247 `a` enum('a') DEFAULT NULL, 3248 `b` enum('b','\0a') DEFAULT NULL 3249) ENGINE=MyISAM DEFAULT CHARSET=latin1 3250INSERT INTO t1 VALUES (1,1); 3251INSERT INTO t1 VALUES (1,2); 3252SELECT HEX(a), HEX(b) FROM t1 ORDER BY a, b; 3253HEX(a) HEX(b) 325461 62 325561 0061 3256DROP TABLE t1; 32570x00 in the beginning of a value of a non-last ENUM/SET causes an error: 3258CREATE TABLE t1 (a ENUM('a',0x0061), b ENUM('b')); 3259ERROR HY000: Incorrect information in file: 'DIR/t1.frm' 3260# 3261# End of 10.1 tests 3262# 3263# 3264# 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 3265# 10.2 tests 3266# 3267SET NAMES latin1; 3268CREATE TABLE t1(c ENUM(0x0061) CHARACTER SET 'Binary', d JSON); 3269SHOW CREATE TABLE t1; 3270Table Create Table 3271t1 CREATE TABLE `t1` ( 3272 `c` enum('\0a') CHARACTER SET binary DEFAULT NULL, 3273 `d` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`d`)) 3274) ENGINE=MyISAM DEFAULT CHARSET=latin1 3275INSERT INTO t1 (c) VALUES (1); 3276SELECT HEX(c) FROM t1; 3277HEX(c) 32780061 3279DROP TABLE t1; 3280CREATE TABLE t1( 3281c ENUM(0x0061) CHARACTER SET 'Binary', 3282d INT DEFAULT NULL CHECK (d>0) 3283); 3284SHOW CREATE TABLE t1; 3285Table Create Table 3286t1 CREATE TABLE `t1` ( 3287 `c` enum('\0a') CHARACTER SET binary DEFAULT NULL, 3288 `d` int(11) DEFAULT NULL CHECK (`d` > 0) 3289) ENGINE=MyISAM DEFAULT CHARSET=latin1 3290INSERT INTO t1 VALUES (1,1); 3291SELECT HEX(c), d FROM t1; 3292HEX(c) d 32930061 1 3294DROP TABLE t1; 3295CREATE TABLE t1(c ENUM(0x0061) CHARACTER SET 'Binary' CHECK (c>0)); 3296SHOW CREATE TABLE t1; 3297Table Create Table 3298t1 CREATE TABLE `t1` ( 3299 `c` enum('\0a') CHARACTER SET binary DEFAULT NULL CHECK (`c` > 0) 3300) ENGINE=MyISAM DEFAULT CHARSET=latin1 3301INSERT INTO t1 VALUES (1); 3302SELECT HEX(c) FROM t1; 3303HEX(c) 33040061 3305DROP TABLE t1; 3306# 3307# End of 10.2 tests 3308# 3309# 3310# Start of 10.5 tests 3311# 3312# 3313# MDEV-8844 Unreadable control characters printed as is in warnings 3314# 3315SET NAMES binary; 3316CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1, UNIQUE(a)); 3317INSERT INTO t1 VALUES (0x61000162FF); 3318INSERT INTO t1 VALUES (0x61000162FF); 3319ERROR 23000: Duplicate entry 'a\0000\0001bÿ' for key 'a' 3320INSERT IGNORE INTO t1 VALUES (0x61000162FF); 3321Warnings: 3322Warning 1062 Duplicate entry 'a\0000\0001bÿ' for key 'a' 3323DROP TABLE t1; 3324CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8, UNIQUE(a)); 3325INSERT INTO t1 VALUES (_latin1 0x61000162FF); 3326INSERT INTO t1 VALUES (_latin1 0x61000162FF); 3327ERROR 23000: Duplicate entry 'a\0000\0001bÿ' for key 'a' 3328INSERT IGNORE INTO t1 VALUES (_latin1 0x61000162FF); 3329Warnings: 3330Warning 1062 Duplicate entry 'a\0000\0001bÿ' for key 'a' 3331DROP TABLE t1; 3332# 3333# End of 10.5 tests 3334# 3335