1--echo # 2--echo # Start of WL#2649 Number-to-string conversions 3--echo # 4# 5# Basic constants 6# 7select hex(concat(1)); 8create table t1 as select concat(1) as c1; 9show create table t1; 10select hex(c1) from t1; 11drop table t1; 12 13select hex(concat(18446744073709551615)); 14create table t1 as select concat(18446744073709551615) as c1; 15show create table t1; 16select hex(c1) from t1; 17drop table t1; 18 19select hex(concat(1.1)); 20create table t1 as select concat(1.1) as c1; 21show create table t1; 22select hex(c1) from t1; 23drop table t1; 24 25 26# 27# Arithmetic operators 28# 29 30select hex(concat('a', 1+2)), charset(concat(1+2)); 31create table t1 as select concat(1+2) as c1; 32show create table t1; 33drop table t1; 34 35select hex(concat(1-2)); 36create table t1 as select concat(1-2) as c1; 37show create table t1; 38drop table t1; 39 40select hex(concat(1*2)); 41create table t1 as select concat(1*2) as c1; 42show create table t1; 43drop table t1; 44 45select hex(concat(1/2)); 46create table t1 as select concat(1/2) as c1; 47show create table t1; 48drop table t1; 49 50select hex(concat(1 div 2)); 51create table t1 as select concat(1 div 2) as c1; 52show create table t1; 53drop table t1; 54 55select hex(concat(1 % 2)); 56create table t1 as select concat(1 % 2) as c1; 57show create table t1; 58drop table t1; 59 60select hex(concat(-1)); 61create table t1 as select concat(-1) as c1; 62show create table t1; 63drop table t1; 64 65select hex(concat(-(1+2))); 66create table t1 as select concat(-(1+2)) as c1; 67show create table t1; 68drop table t1; 69 70 71# 72# Bit functions 73# 74 75select hex(concat(1|2)); 76create table t1 as select concat(1|2) as c1; 77show create table t1; 78drop table t1; 79 80select hex(concat(1&2)); 81create table t1 as select concat(1&2) as c1; 82show create table t1; 83drop table t1; 84 85select hex(concat(bit_count(12))); 86create table t1 as select concat(bit_count(12)) as c1; 87show create table t1; 88drop table t1; 89 90select hex(concat(2<<1)); 91create table t1 as select concat(2<<1) as c1; 92show create table t1; 93drop table t1; 94 95select hex(concat(2>>1)); 96create table t1 as select concat(2>>1) as c1; 97show create table t1; 98drop table t1; 99 100select hex(concat(~0)); 101create table t1 as select concat(~0) as c1; 102show create table t1; 103drop table t1; 104 105select hex(concat(3^2)); 106create table t1 as select concat(3^2) as c1; 107show create table t1; 108drop table t1; 109 110 111 112# 113# Math functions 114# 115# Note, some tests use LEFT(func(),1) to avoid 116# non-deterministic results on various platforms. 117# 118 119select hex(concat(abs(-2))); 120create table t1 as select concat(abs(-2)) as c1; 121show create table t1; 122drop table t1; 123 124select hex(left(concat(exp(2)),1)); 125create table t1 as select concat(exp(2)) as c1; 126show create table t1; 127drop table t1; 128 129select hex(left(concat(log(2)),1)); 130create table t1 as select concat(log(2)) as c1; 131show create table t1; 132drop table t1; 133 134select hex(left(concat(log2(2)),1)); 135create table t1 as select concat(log2(2)) as c1; 136show create table t1; 137drop table t1; 138 139select hex(left(concat(log10(2)),1)); 140create table t1 as select concat(log10(2)) as c1; 141show create table t1; 142drop table t1; 143 144select hex(left(concat(sqrt(2)),1)); 145create table t1 as select concat(sqrt(2)) as c1; 146show create table t1; 147drop table t1; 148 149select hex(left(concat(pow(2,2)),1)); 150create table t1 as select concat(pow(2,2)) as c1; 151show create table t1; 152drop table t1; 153 154select hex(left(concat(acos(0.5)),1)); 155create table t1 as select concat(acos(0.5)) as c1; 156show create table t1; 157drop table t1; 158 159select hex(left(concat(asin(0.5)),1)); 160create table t1 as select concat(asin(0.5)) as c1; 161show create table t1; 162drop table t1; 163 164select hex(left(concat(atan(0.5)),1)); 165create table t1 as select concat(atan(0.5)) as c1; 166show create table t1; 167drop table t1; 168 169select hex(left(concat(cos(0.5)),1)); 170create table t1 as select concat(cos(0.5)) as c1; 171show create table t1; 172drop table t1; 173 174select hex(left(concat(sin(0.5)),1)); 175create table t1 as select concat(sin(0.5)) as c1; 176show create table t1; 177drop table t1; 178 179select hex(left(concat(tan(0.5)),1)); 180create table t1 as select concat(tan(0.5)) as c1; 181show create table t1; 182drop table t1; 183 184select hex(concat(degrees(0))); 185create table t1 as select concat(degrees(0)) as c1; 186show create table t1; 187drop table t1; 188 189select hex(concat(radians(0))); 190create table t1 as select concat(radians(0)) as c1; 191show create table t1; 192drop table t1; 193 194select hex(concat(ceiling(0.5))); 195create table t1 as select concat(ceiling(0.5)) as c1; 196show create table t1; 197drop table t1; 198 199select hex(concat(floor(0.5))); 200create table t1 as select concat(floor(0.5)) as c1; 201show create table t1; 202drop table t1; 203 204select hex(concat(round(0.5))); 205create table t1 as select concat(round(0.5)) as c1; 206show create table t1; 207drop table t1; 208 209select hex(concat(sign(0.5))); 210create table t1 as select concat(sign(0.5)) as c1; 211show create table t1; 212drop table t1; 213 214create table t1 as select concat(rand()) as c1; 215show create table t1; 216drop table t1; 217 218 219# 220# String functions 221# 222 223select hex(concat(length('a'))); 224create table t1 as select concat(length('a')) as c1; 225show create table t1; 226drop table t1; 227 228select hex(concat(char_length('a'))); 229create table t1 as select concat(char_length('a')) as c1; 230show create table t1; 231drop table t1; 232 233select hex(concat(bit_length('a'))); 234create table t1 as select concat(bit_length('a')) as c1; 235show create table t1; 236drop table t1; 237 238select hex(concat(coercibility('a'))); 239create table t1 as select concat(coercibility('a')) as c1; 240show create table t1; 241drop table t1; 242 243select hex(concat(locate('a','a'))); 244create table t1 as select concat(locate('a','a')) as c1; 245show create table t1; 246drop table t1; 247 248select hex(concat(field('c','a','b','c'))); 249create table t1 as select concat(field('c','a','b','c')) as c1; 250show create table t1; 251drop table t1; 252 253select hex(concat(ascii(61))); 254create table t1 as select concat(ascii(61)) as c1; 255show create table t1; 256drop table t1; 257 258select hex(concat(ord(61))); 259create table t1 as select concat(ord(61)) as c1; 260show create table t1; 261drop table t1; 262 263select hex(concat(find_in_set('b','a,b,c,d'))); 264create table t1 as select concat(find_in_set('b','a,b,c,d')) as c1; 265show create table t1; 266drop table t1; 267 268 269# 270# String hash functions 271# 272 273select md5('a'), hex(md5('a')); 274create table t1 as select md5('a') as c1; 275show create table t1; 276drop table t1; 277 278select password('a'), hex(password('a')); 279--disable_warnings 280create table t1 as select password('a') as c1; 281--enable_warnings 282#The above query returns 2 warnings in ps test. 283show create table t1; 284drop table t1; 285 286select sha('a'), hex(sha('a')); 287create table t1 as select sha('a') as c1; 288show create table t1; 289drop table t1; 290 291select sha1('a'), hex(sha1('a')); 292create table t1 as select sha1('a') as c1; 293show create table t1; 294drop table t1; 295 296#select sha2('a',224), hex(sha2('a',224)); 297#create table t1 as select sha2('a',224) as c1; 298#show create table t1; 299#drop table t1; 300 301 302 303# 304# CAST 305# 306 307select hex(concat(cast('-1' as signed))); 308create table t1 as select concat(cast('-1' as signed)) as c1; 309show create table t1; 310drop table t1; 311 312select hex(concat(cast('1' as unsigned))); 313create table t1 as select concat(cast('1' as unsigned)) as c1; 314show create table t1; 315drop table t1; 316 317select hex(concat(cast(1/2 as decimal(5,5)))); 318create table t1 as select concat(cast(1/2 as decimal(5,5))) as c1; 319show create table t1; 320drop table t1; 321 322select hex(concat(cast('2001-01-02 03:04:05' as date))); 323create table t1 as select concat(cast('2001-01-02 03:04:05' as date)) as c1; 324show create table t1; 325select * from t1; 326drop table t1; 327 328select hex(concat(cast('2001-01-02 03:04:05' as time))); 329create table t1 as select concat(cast('2001-01-02 03:04:05' as time)) as c1; 330show create table t1; 331select * from t1; 332drop table t1; 333 334select hex(concat(cast('2001-01-02' as datetime))); 335create table t1 as select concat(cast('2001-01-02' as datetime)) as c1; 336show create table t1; 337select * from t1; 338drop table t1; 339 340 341# 342# Aggregation: LEAST, GREATEST 343# 344select hex(concat(least(1,2))); 345create table t1 as select concat(least(1,2)) as c1; 346show create table t1; 347drop table t1; 348 349select hex(concat(greatest(1,2))); 350create table t1 as select concat(greatest(1,2)) as c1; 351show create table t1; 352drop table t1; 353 354 355# 356# Aggregation: CASE 357# 358select hex(concat(case when 11 then 22 else 33 end)); 359create table t1 as select concat(case when 11 then 22 else 33 end) as c1; 360show create table t1; 361drop table t1; 362 363 364# 365# Aggregation: COALESCE 366# 367select hex(concat(coalesce(1,2))); 368create table t1 as select concat(coalesce(1,2)) as c1; 369show create table t1; 370drop table t1; 371 372 373# 374# Aggregation: CONCAT_WS, GROUP_CONCAT 375# 376select hex(concat_ws(1,2,3)); 377create table t1 as select concat_ws(1,2,3) as c1; 378show create table t1; 379drop table t1; 380 381select hex(group_concat(1,2,3)); 382create table t1 as select group_concat(1,2,3) as c1; 383show create table t1; 384drop table t1; 385 386# 387# Aggregation: UNION 388# 389create table t1 as select 1 as c1 union select 'a'; 390show create table t1; 391select hex(c1) from t1 order by c1; 392drop table t1; 393 394 395# 396# Miscelaneous functions 397# 398 399create table t1 as select concat(last_insert_id()) as c1; 400show create table t1; 401drop table t1; 402 403select hex(concat(benchmark(0,0))); 404create table t1 as select concat(benchmark(0,0)) as c1; 405show create table t1; 406drop table t1; 407 408select hex(concat(sleep(0))); 409create table t1 as select concat(sleep(0)) as c1; 410show create table t1; 411drop table t1; 412 413# Fails with "mtr --ps-protocol" for some reasons. 414#select hex(concat(get_lock('a',0))); 415#select hex(concat(release_lock('a'))); 416#create table t1 as select concat(get_lock('a',0)) as c1; 417#show create table t1; 418#drop table t1; 419 420select hex(concat(is_free_lock('xxxx'))); 421create table t1 as select concat(is_free_lock('xxxx')) as c1; 422show create table t1; 423drop table t1; 424 425create table t1 as select concat(is_used_lock('a')) as c1; 426show create table t1; 427drop table t1; 428 429create table t1 as select concat(release_lock('a')) as c1; 430show create table t1; 431drop table t1; 432 433select hex(concat(crc32(''))); 434create table t1 as select concat(crc32('')) as c1; 435show create table t1; 436drop table t1; 437 438select hex(concat(uncompressed_length(''))); 439create table t1 as select concat(uncompressed_length('')) as c1; 440show create table t1; 441drop table t1; 442 443create table t1 as select concat(connection_id()) as c1; 444show create table t1; 445drop table t1; 446 447select hex(concat(inet_aton('127.1.1.1'))); 448create table t1 as select concat(inet_aton('127.1.1.1')) as c1; 449show create table t1; 450drop table t1; 451 452select hex(concat(inet_ntoa(2130772225))); 453create table t1 as select concat(inet_ntoa(2130772225)) as c1; 454select * from t1; 455show create table t1; 456drop table t1; 457 458# Ensure that row_count() value is reset after drop table. 459select 1; 460select hex(concat(row_count())); 461create table t1 as select concat(row_count()) as c1; 462show create table t1; 463drop table t1; 464 465select hex(concat(found_rows())); 466create table t1 as select concat(found_rows()) as c1; 467show create table t1; 468drop table t1; 469 470create table t1 as select concat(uuid_short()) as c1; 471show create table t1; 472drop table t1; 473 474create table t1 as select concat(uuid()) as c1; 475show create table t1; 476drop table t1; 477 478# 479# Make sure we can mix uuid() to a latin1 object 480# with DERIVATION_IMPLICIT (and higher): 481# (DERIVATION_COERCIBLE + MY_REPERTOIRE_ASCII allow to do so) 482# 483select coercibility(uuid()), coercibility(cast('a' as char character set latin1)); 484select charset(concat(uuid(), cast('a' as char character set latin1))); 485create table t1 as select concat(uuid(), cast('a' as char character set latin1)) as c1; 486show create table t1; 487drop table t1; 488 489create table t1 as select concat(master_pos_wait('non-existent',0,2)) as c1; 490show create table t1; 491drop table t1; 492 493 494# 495# User and system variable functions 496# 497 498# User variables: INT 499select hex(concat(@a1:=1)); 500create table t1 as select concat(@a2:=2) as c1, @a3:=3 as c2; 501select hex(c1) from t1; 502show create table t1; 503drop table t1; 504 505set @a2=1; 506select hex(concat(@a2)); 507create table t1 as select concat(@a2) as c1, @a2 as c2; 508select hex(c1) from t1; 509show create table t1; 510drop table t1; 511 512# User variables: REAL 513select hex(concat(@a1:=sqrt(1))); 514create table t1 as select concat(@a2:=sqrt(1)) as c1, @a3:=sqrt(1) as c2; 515select hex(c1) from t1; 516show create table t1; 517drop table t1; 518 519set @a2=sqrt(1); 520select hex(concat(@a2)); 521create table t1 as select concat(@a2) as c1, @a2 as c2; 522select hex(c1) from t1; 523show create table t1; 524drop table t1; 525 526# User variables: DECIMAL 527select hex(concat(@a1:=1.1)); 528create table t1 as select concat(@a2:=1.1) as c1, @a3:=1.1 as c2; 529select hex(c1) from t1; 530show create table t1; 531drop table t1; 532 533set @a2=1.1; 534select hex(concat(@a2)); 535create table t1 as select concat(@a2) as c1, @a2 as c2; 536select hex(c1) from t1; 537show create table t1; 538drop table t1; 539 540 541select hex(concat(@@ft_max_word_len)); 542create table t1 as select concat(@@ft_max_word_len) as c1; 543select hex(c1) from t1; 544show create table t1; 545drop table t1; 546 547# 548# Comparison functions 549# 550 551select hex(concat('a'='a' IS TRUE)); 552create table t1 as select concat('a'='a' IS TRUE) as c1; 553show create table t1; 554drop table t1; 555 556select hex(concat('a'='a' IS NOT TRUE)); 557create table t1 as select concat('a'='a' IS NOT TRUE) as c1; 558show create table t1; 559drop table t1; 560 561select hex(concat(NOT 'a'='a')); 562create table t1 as select concat(NOT 'a'='a') as c1; 563show create table t1; 564drop table t1; 565 566select hex(concat('a' IS NULL)); 567create table t1 as select concat('a' IS NULL) as c1; 568show create table t1; 569drop table t1; 570 571select hex(concat('a' IS NOT NULL)); 572create table t1 as select concat('a' IS NOT NULL) as c1; 573show create table t1; 574drop table t1; 575 576select hex(concat('a' rlike 'a')); 577create table t1 as select concat('a' IS NOT NULL) as c1; 578show create table t1; 579drop table t1; 580 581select hex(concat(strcmp('a','b'))); 582create table t1 as select concat(strcmp('a','b')) as c1; 583show create table t1; 584drop table t1; 585 586select hex(concat('a' like 'a')); 587create table t1 as select concat('a' like 'b') as c1; 588show create table t1; 589drop table t1; 590 591select hex(concat('a' between 'b' and 'c')); 592create table t1 as select concat('a' between 'b' and 'c') as c1; 593show create table t1; 594drop table t1; 595 596select hex(concat('a' in ('a','b'))); 597create table t1 as select concat('a' in ('a','b')) as c1; 598show create table t1; 599drop table t1; 600 601select hex(concat(interval(23, 1, 15, 17, 30, 44, 200))); 602create table t1 as select concat(interval(23, 1, 15, 17, 30, 44, 200)) as c1; 603show create table t1; 604drop table t1; 605 606create table t1 (a varchar(10), fulltext key(a)); 607insert into t1 values ('a'); 608select hex(concat(match (a) against ('a'))) from t1; 609create table t2 as select concat(match (a) against ('a')) as a from t1; 610show create table t2; 611drop table t1, t2; 612 613select hex(ifnull(1,'a')); 614create table t1 as select ifnull(1,'a') as c1; 615show create table t1; 616drop table t1; 617 618select hex(concat(ifnull(1,1))); 619create table t1 as select concat(ifnull(1,1)) as c1; 620show create table t1; 621drop table t1; 622 623select hex(concat(ifnull(1.1,1.1))); 624create table t1 as select concat(ifnull(1.1,1.1)) as c1; 625show create table t1; 626drop table t1; 627 628select hex(if(1,'b',1)); 629create table t1 as select if(1,'b',1) as c1; 630show create table t1; 631drop table t1; 632 633select hex(if(1,1,'b')); 634create table t1 as select if(1,1,'b') as c1; 635show create table t1; 636drop table t1; 637 638select hex(concat(if(1,1,1))); 639create table t1 as select concat(if(1,1,1)) as c1; 640show create table t1; 641drop table t1; 642 643select hex(concat(nullif(1,2))); 644create table t1 as select concat(nullif(1,2)) as c1; 645show create table t1; 646drop table t1; 647 648# 649# GIS functions 650# 651 652select hex(concat(ST_Dimension(ST_GeomFromText('LINESTRING(0 0,10 10)')))); 653--error ER_GIS_INVALID_DATA 654create table t1 as select concat(ST_Dimension(ST_GeomFromText('LINSTRING(0 0,10 10)'))) as c1; 655 656select hex(concat(ST_NumGeometries(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)')))); 657create table t1 as select concat(ST_NumGeometries(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1; 658show create table t1; 659drop table t1; 660 661select hex(concat(ST_NumPoints(ST_MultiPointFromText('LINESTRING(0 0,10 10)')))); 662create table t1 as select concat(ST_NumPoints(ST_MultiPointFromText('LINESTRING(0 0,10 10)'))) as c1; 663show create table t1; 664drop table t1; 665 666select hex(concat(ST_SRID(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)')))); 667create table t1 as select concat(ST_SRID(ST_MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1; 668show create table t1; 669drop table t1; 670 671select hex(concat(ST_NumInteriorRings(ST_PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')))); 672create table t1 as select concat(ST_NumInteriorRings(ST_PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))) as c1; 673show create table t1; 674drop table t1; 675 676select hex(concat(ST_IsEmpty(ST_GeomFromText('POINT(1 1)')))); 677create table t1 as select concat(ST_IsEmpty(ST_GeomFromText('Point(1 1)'))) as c1; 678show create table t1; 679drop table t1; 680 681select hex(concat(ST_IsSimple(ST_GeomFromText('POINT(1 1)')))); 682create table t1 as select concat(ST_IsSimple(ST_GeomFromText('Point(1 1)'))) as c1; 683show create table t1; 684drop table t1; 685 686select hex(concat(ST_IsClosed(ST_GeomFromText('LineString(1 1,2 2)')))); 687create table t1 as select concat(ST_IsClosed(ST_GeomFromText('LineString(1 1,2 2)'))) as c1; 688show create table t1; 689drop table t1; 690 691select hex(concat(MBREquals(ST_GeomFromText('Point(1 1)'),ST_GeomFromText('Point(1 1)')))); 692create table t1 as select concat(MBREquals(ST_GeomFromText('Point(1 1)'),ST_GeomFromText('Point(1 1)'))) as c1; 693drop table t1; 694 695select hex(concat(ST_X(ST_GeomFromText('Point(1 2)')))); 696create table t1 as select concat(ST_X(ST_GeomFromText('Point(1 2)'))) as c1; 697show create table t1; 698drop table t1; 699 700select hex(concat(ST_Y(ST_GeomFromText('Point(1 2)')))); 701create table t1 as select concat(ST_X(ST_GeomFromText('Point(1 2)'))) as c1; 702show create table t1; 703drop table t1; 704 705select hex(concat(ST_Length(ST_GeomFromText('LineString(1 2,2 2)')))); 706create table t1 as select concat(ST_Length(ST_GeomFromText('LineString(1 2, 2 2)'))) as c1; 707show create table t1; 708drop table t1; 709 710select hex(concat(ST_Area(ST_GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))')))); 711create table t1 as select concat(ST_Area(ST_GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))) as c1; 712show create table t1; 713drop table t1; 714 715select hex(concat(ST_GeometryType(ST_GeomFromText('Point(1 2)')))); 716create table t1 as select concat(ST_GeometryType(ST_GeomFromText('Point(1 2)'))) as c1; 717show create table t1; 718drop table t1; 719 720select hex(concat(ST_AsText(ST_GeomFromText('Point(1 2)')))); 721create table t1 as select concat(ST_AsText(ST_GeomFromText('Point(1 2)'))) as c1; 722show create table t1; 723drop table t1; 724 725 726 727# 728# Date/Time functions 729# 730 731select hex(concat(period_add(200902, 2))); 732create table t1 as select concat(period_add(200902, 2)) as c1; 733show create table t1; 734drop table t1; 735SET sql_mode = ''; 736select hex(concat(period_diff(200902, 200802))); 737create table t1 as select concat(period_add(200902, 200802)) as c1; 738show create table t1; 739drop table t1; 740SET sql_mode = default; 741select hex(concat(to_days(20090224))); 742create table t1 as select concat(to_days(20090224)) as c1; 743show create table t1; 744drop table t1; 745 746select hex(concat(dayofmonth(20090224))); 747create table t1 as select concat(dayofmonth(20090224)) as c1; 748show create table t1; 749drop table t1; 750 751select hex(concat(dayofyear(20090224))); 752create table t1 as select concat(dayofyear(20090224)) as c1; 753show create table t1; 754drop table t1; 755 756select hex(concat(hour('10:11:12'))); 757create table t1 as select concat(hour('10:11:12')) as c1; 758show create table t1; 759drop table t1; 760 761select hex(concat(minute('10:11:12'))); 762create table t1 as select concat(minute('10:11:12')) as c1; 763show create table t1; 764drop table t1; 765 766select hex(concat(second('10:11:12'))); 767create table t1 as select concat(second('10:11:12')) as c1; 768show create table t1; 769drop table t1; 770 771select hex(concat(quarter(20090224))); 772create table t1 as select concat(quarter(20090224)) as c1; 773show create table t1; 774drop table t1; 775 776select hex(concat(week(20090224))); 777create table t1 as select concat(week(20090224)) as c1; 778show create table t1; 779drop table t1; 780 781select hex(concat(yearweek(20090224))); 782create table t1 as select concat(yearweek(20090224)) as c1; 783show create table t1; 784drop table t1; 785 786select hex(concat(year(20090224))); 787create table t1 as select concat(year(20090224)) as c1; 788show create table t1; 789drop table t1; 790 791select hex(concat(weekday(20090224))); 792create table t1 as select concat(weekday(20090224)) as c1; 793show create table t1; 794drop table t1; 795 796select hex(concat(dayofweek(20090224))); 797create table t1 as select concat(dayofweek(20090224)) as c1; 798show create table t1; 799drop table t1; 800 801select hex(concat(unix_timestamp(20090224))); 802create table t1 as select concat(unix_timestamp(20090224)) as c1; 803show create table t1; 804drop table t1; 805 806select hex(concat(time_to_sec('10:11:12'))); 807create table t1 as select concat(time_to_sec('10:11:12')) as c1; 808show create table t1; 809drop table t1; 810 811select hex(concat(extract(year from 20090702))); 812create table t1 as select concat(extract(year from 20090702)) as c1; 813show create table t1; 814drop table t1; 815 816select hex(concat(microsecond('12:00:00.123456'))); 817create table t1 as select concat(microsecond('12:00:00.123456')) as c1; 818show create table t1; 819drop table t1; 820 821select hex(concat(month(20090224))); 822create table t1 as select concat(month(20090224)) as c1; 823show create table t1; 824drop table t1; 825 826 827create table t1 as select concat(last_day('2003-02-05')) as c1; 828show create table t1; 829select c1, hex(c1) from t1; 830drop table t1; 831 832create table t1 as select concat(from_days(730669)) as c1; 833show create table t1; 834select c1, hex(c1) from t1; 835drop table t1; 836 837create table t1 as select concat(curdate()) as c1; 838show create table t1; 839drop table t1; 840 841create table t1 as select concat(utc_date()) as c1; 842show create table t1; 843drop table t1; 844 845create table t1 as select concat(curtime()) as c1; 846show create table t1; 847drop table t1; 848 849create table t1 as select repeat('a',20) as c1 limit 0; 850set timestamp=1216359724; 851insert into t1 values (current_date); 852insert into t1 values (current_time); 853select c1, hex(c1) from t1; 854drop table t1; 855 856create table t1 as select concat(utc_time()) as c1; 857show create table t1; 858drop table t1; 859 860select hex(concat(sec_to_time(2378))); 861create table t1 as select concat(sec_to_time(2378)) as c1; 862show create table t1; 863drop table t1; 864 865select hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00'))); 866create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')) as c1; 867show create table t1; 868drop table t1; 869 870select hex(concat(maketime(10,11,12))); 871create table t1 as select concat(maketime(10,11,12)) as c1; 872show create table t1; 873drop table t1; 874 875select hex(get_format(DATE,'USA')); 876create table t1 as select get_format(DATE,'USA') as c1; 877show create table t1; 878drop table t1; 879 880select hex(left(concat(from_unixtime(1111885200)),4)); 881create table t1 as select concat(from_unixtime(1111885200)) as c1; 882show create table t1; 883drop table t1; 884 885select hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00'))); 886create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')) as c1; 887show create table t1; 888drop table t1; 889 890select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); 891create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day)) as c1; 892show create table t1; 893select * from t1; 894drop table t1; 895 896select hex(concat(makedate(2009,1))); 897create table t1 as select concat(makedate(2009,1)) as c1; 898show create table t1; 899select * from t1; 900drop table t1; 901 902create table t1 as select concat(now()) as c1; 903show create table t1; 904drop table t1; 905 906create table t1 as select concat(utc_timestamp()) as c1; 907show create table t1; 908drop table t1; 909 910create table t1 as select concat(sysdate()) as c1; 911show create table t1; 912drop table t1; 913 914select hex(concat(addtime('00:00:00','11:22:33'))); 915create table t1 as select concat(addtime('00:00:00','11:22:33')) as c1; 916show create table t1; 917drop table t1; 918 919select hex(concat(subtime('23:59:59','11:22:33'))); 920create table t1 as select concat(subtime('23:59:59','11:22:33')) as c1; 921show create table t1; 922drop table t1; 923 924 925# 926# Other string functions with numeric input 927# 928select hex(elt(1,2,3)); 929create table t1 as select elt(1,2,3) as c1; 930show create table t1; 931drop table t1; 932 933select hex(export_set(1,2,3,4,2)); 934create table t1 as select export_set(1,2,3,4,2) as c1; 935show create table t1; 936drop table t1; 937 938select hex(insert(1133,3,0,22)); 939create table t1 as select insert(1133,3,0,22) as c1; 940show create table t1; 941drop table t1; 942 943select hex(lcase(123)); 944create table t1 as select lcase(123) as c1; 945show create table t1; 946drop table t1; 947 948select hex(left(123,1)); 949create table t1 as select left(123,1) as c1; 950show create table t1; 951drop table t1; 952 953select hex(lower(123)); 954create table t1 as select lower(123) as c1; 955show create table t1; 956drop table t1; 957 958select hex(lpad(1,2,0)); 959create table t1 as select lpad(1,2,0) as c1; 960show create table t1; 961drop table t1; 962 963select hex(ltrim(1)); 964create table t1 as select ltrim(1) as c1; 965show create table t1; 966drop table t1; 967 968select hex(mid(1,1,1)); 969create table t1 as select mid(1,1,1) as c1; 970show create table t1; 971drop table t1; 972 973select hex(repeat(1,2)); 974create table t1 as select repeat(1,2) as c1; 975show create table t1; 976drop table t1; 977 978select hex(replace(1,1,2)); 979create table t1 as select replace(1,1,2) as c1; 980show create table t1; 981drop table t1; 982 983select hex(reverse(12)); 984create table t1 as select reverse(12) as c1; 985show create table t1; 986drop table t1; 987 988select hex(right(123,1)); 989create table t1 as select right(123,1) as c1; 990show create table t1; 991drop table t1; 992 993select hex(rpad(1,2,0)); 994create table t1 as select rpad(1,2,0) as c1; 995show create table t1; 996drop table t1; 997 998select hex(rtrim(1)); 999create table t1 as select rtrim(1) as c1; 1000show create table t1; 1001drop table t1; 1002 1003select hex(soundex(1)); 1004create table t1 as select soundex(1) as c1; 1005show create table t1; 1006drop table t1; 1007 1008select hex(substring(1,1,1)); 1009create table t1 as select substring(1,1,1) as c1; 1010show create table t1; 1011drop table t1; 1012 1013select hex(trim(1)); 1014create table t1 as select trim(1) as c1; 1015show create table t1; 1016drop table t1; 1017 1018select hex(ucase(1)); 1019create table t1 as select ucase(1) as c1; 1020show create table t1; 1021drop table t1; 1022 1023select hex(upper(1)); 1024create table t1 as select upper(1) as c1; 1025show create table t1; 1026drop table t1; 1027 1028 1029# 1030# Bug#8204 1031# 1032create table t1 as select repeat(' ', 64) as a limit 0; 1033show create table t1; 1034insert into t1 values ("1.1"), ("2.1"); 1035select a, hex(a) from t1; 1036update t1 set a= a + 0.1; 1037select a, hex(a) from t1; 1038drop table t1; 1039 1040 1041# 1042# Columns 1043# 1044create table t1 (a tinyint); 1045insert into t1 values (1); 1046select hex(concat(a)) from t1; 1047create table t2 as select concat(a) from t1; 1048show create table t2; 1049drop table t1, t2; 1050 1051create table t1 (a tinyint zerofill); 1052insert into t1 values (1), (10), (100); 1053select hex(concat(a)), a from t1; 1054drop table t1; 1055 1056create table t1 (a tinyint(4) zerofill); 1057insert into t1 values (1), (10), (100); 1058select hex(concat(a)), a from t1; 1059drop table t1; 1060 1061create table t1 (a decimal(10,2)); 1062insert into t1 values (123.45); 1063select hex(concat(a)) from t1; 1064create table t2 as select concat(a) from t1; 1065show create table t2; 1066drop table t1, t2; 1067 1068create table t1 (a smallint); 1069insert into t1 values (1); 1070select hex(concat(a)) from t1; 1071create table t2 as select concat(a) from t1; 1072show create table t2; 1073drop table t1, t2; 1074 1075create table t1 (a smallint zerofill); 1076insert into t1 values (1), (10), (100), (1000), (10000); 1077select hex(concat(a)), a from t1; 1078drop table t1; 1079 1080create table t1 (a mediumint); 1081insert into t1 values (1); 1082select hex(concat(a)) from t1; 1083create table t2 as select concat(a) from t1; 1084show create table t2; 1085drop table t1, t2; 1086 1087create table t1 (a mediumint zerofill); 1088insert into t1 values (1), (10), (100), (1000), (10000); 1089select hex(concat(a)), a from t1; 1090drop table t1; 1091 1092create table t1 (a int); 1093insert into t1 values (1); 1094select hex(concat(a)) from t1; 1095create table t2 as select concat(a) from t1; 1096show create table t2; 1097drop table t1, t2; 1098 1099create table t1 (a int zerofill); 1100insert into t1 values (1), (10), (100), (1000), (10000); 1101select hex(concat(a)), a from t1; 1102drop table t1; 1103 1104create table t1 (a bigint); 1105insert into t1 values (1); 1106select hex(concat(a)) from t1; 1107create table t2 as select concat(a) from t1; 1108show create table t2; 1109drop table t1, t2; 1110 1111create table t1 (a bigint zerofill); 1112insert into t1 values (1), (10), (100), (1000), (10000); 1113select hex(concat(a)), a from t1; 1114drop table t1; 1115 1116create table t1 (a float); 1117insert into t1 values (123.456); 1118select hex(concat(a)) from t1; 1119select concat(a) from t1; 1120create table t2 as select concat(a) from t1; 1121show create table t2; 1122drop table t1, t2; 1123 1124create table t1 (a float zerofill); 1125insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 1126select hex(concat(a)), a from t1; 1127drop table t1; 1128 1129create table t1 (a double); 1130insert into t1 values (123.456); 1131select hex(concat(a)) from t1; 1132select concat(a) from t1; 1133create table t2 as select concat(a) from t1; 1134show create table t2; 1135drop table t1, t2; 1136 1137create table t1 (a double zerofill); 1138insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 1139select hex(concat(a)), a from t1; 1140drop table t1; 1141 1142create table t1 (a year(4)); 1143insert into t1 values (1); 1144select hex(concat(a)) from t1; 1145create table t2 as select concat(a) from t1; 1146show create table t2; 1147drop table t1, t2; 1148 1149create table t1 (a year); 1150insert into t1 values (1); 1151select hex(concat(a)) from t1; 1152create table t2 as select concat(a) from t1; 1153show create table t2; 1154drop table t1, t2; 1155 1156create table t1 (a bit(64)); 1157# BIT is always BINARY 1158insert into t1 values (1); 1159select hex(concat(a)) from t1; 1160create table t2 as select concat(a) from t1; 1161show create table t2; 1162drop table t1, t2; 1163 1164create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 1165insert ignore into t1 values (0); 1166insert into t1 values (20010203040506); 1167insert into t1 values (19800203040506); 1168insert into t1 values ('2001-02-03 04:05:06'); 1169select hex(concat(a)) from t1; 1170select concat(a) from t1; 1171create table t2 as select concat(a) from t1; 1172show create table t2; 1173drop table t1, t2; 1174 1175create table t1 (a date); 1176insert into t1 values ('2001-02-03'); 1177insert into t1 values (20010203); 1178select hex(concat(a)) from t1; 1179create table t2 as select concat(a) from t1; 1180show create table t2; 1181drop table t1, t2; 1182 1183create table t1 (a time); 1184insert into t1 values (1); 1185insert into t1 values ('01:02:03'); 1186select hex(concat(a)) from t1; 1187select concat(a) from t1; 1188create table t2 as select concat(a) from t1; 1189show create table t2; 1190drop table t1, t2; 1191 1192create table t1 (a datetime); 1193insert into t1 values ('2001-02-03 04:05:06'); 1194insert into t1 values (20010203040506); 1195select hex(concat(a)) from t1; 1196create table t2 as select concat(a) from t1; 1197show create table t2; 1198drop table t1, t2; 1199 1200 1201# 1202# create view with string functions with numeric input 1203# 1204# Switched off in ucs tests due to bug#50716 1205if ($not_ucs) 1206{ 1207create view v1 as select concat(1,2,3) as c1; 1208show columns from v1; 1209select hex(c1) from v1; 1210drop view v1; 1211 1212create view v1 as select concat_ws(',',1,2,3) as c1; 1213show columns from v1; 1214select hex(c1) from v1; 1215drop view v1; 1216 1217create view v1 as select elt(1,2,3) as c1; 1218show columns from v1; 1219select hex(c1) from v1; 1220drop view v1; 1221 1222create view v1 as select export_set(1,2,3,4,2) as c1; 1223show columns from v1; 1224select hex(c1) from v1; 1225drop view v1; 1226 1227create view v1 as select insert(1133,3,0,22) as c1; 1228show columns from v1; 1229select hex(c1) from v1; 1230drop view v1; 1231 1232create view v1 as select lcase(123) as c1; 1233show columns from v1; 1234select hex(c1) from v1; 1235drop view v1; 1236 1237create view v1 as select left(123,1) as c1; 1238show columns from v1; 1239select hex(c1) from v1; 1240drop view v1; 1241 1242create view v1 as select lower(123) as c1; 1243show columns from v1; 1244select hex(c1) from v1; 1245drop view v1; 1246 1247create view v1 as select lpad(1,2,0) as c1; 1248show columns from v1; 1249select hex(c1) from v1; 1250drop view v1; 1251 1252create view v1 as select ltrim(1) as c1; 1253show columns from v1; 1254select hex(c1) from v1; 1255drop view v1; 1256 1257create view v1 as select mid(1,1,1) as c1; 1258show columns from v1; 1259select hex(c1) from v1; 1260drop view v1; 1261 1262create view v1 as select repeat(1,2) as c1; 1263show columns from v1; 1264select hex(c1) from v1; 1265drop view v1; 1266 1267create view v1 as select replace(1,1,2) as c1; 1268show columns from v1; 1269select hex(c1) from v1; 1270drop view v1; 1271 1272create view v1 as select reverse(12) as c1; 1273show columns from v1; 1274select hex(c1) from v1; 1275drop view v1; 1276 1277create view v1 as select right(123,1) as c1; 1278show columns from v1; 1279select hex(c1) from v1; 1280drop view v1; 1281 1282create view v1 as select rpad(1,2,0) as c1; 1283show columns from v1; 1284select hex(c1) from v1; 1285drop view v1; 1286 1287create view v1 as select rtrim(1) as c1; 1288show columns from v1; 1289select hex(c1) from v1; 1290drop view v1; 1291 1292create view v1 as select soundex(1) as c1; 1293show columns from v1; 1294select hex(c1) from v1; 1295drop view v1; 1296 1297create view v1 as select substring(1,1,1) as c1; 1298show columns from v1; 1299select hex(c1) from v1; 1300drop view v1; 1301 1302create view v1 as select trim(1) as c1; 1303show columns from v1; 1304select hex(c1) from v1; 1305drop view v1; 1306 1307create view v1 as select ucase(1) as c1; 1308show columns from v1; 1309select hex(c1) from v1; 1310drop view v1; 1311 1312create view v1 as select upper(1) as c1; 1313show columns from v1; 1314select hex(c1) from v1; 1315drop view v1; 1316} 1317 1318 1319# 1320# Views from tables with numeric columns 1321# 1322create table t1 (a tinyint); 1323insert into t1 values (1); 1324create view v1(a) as select concat(a) from t1; 1325show columns from v1; 1326select hex(a) from v1; 1327drop table t1; 1328drop view v1; 1329 1330create table t1 (a tinyint zerofill); 1331insert into t1 values (1), (10), (100); 1332create view v1(a) as select concat(a) from t1; 1333show columns from v1; 1334select hex(a) from v1; 1335drop table t1; 1336drop view v1; 1337 1338create table t1 (a tinyint(30) zerofill); 1339insert into t1 values (1), (10), (100); 1340create view v1(a) as select concat(a) from t1; 1341show columns from v1; 1342select hex(a) from v1; 1343drop table t1; 1344drop view v1; 1345 1346create table t1 (a decimal(10,2)); 1347insert into t1 values (123.45); 1348create view v1(a) as select concat(a) from t1; 1349show columns from v1; 1350select hex(a) from v1; 1351drop table t1; 1352drop view v1; 1353 1354create table t1 (a smallint); 1355insert into t1 values (1); 1356create view v1(a) as select concat(a) from t1; 1357show columns from v1; 1358select hex(a) from v1; 1359drop table t1; 1360drop view v1; 1361 1362create table t1 (a smallint zerofill); 1363insert into t1 values (1), (10), (100), (1000), (10000); 1364create view v1(a) as select concat(a) from t1; 1365show columns from v1; 1366select hex(a) from v1; 1367drop table t1; 1368drop view v1; 1369 1370create table t1 (a mediumint); 1371insert into t1 values (1); 1372create view v1(a) as select concat(a) from t1; 1373show columns from v1; 1374select hex(a) from v1; 1375drop table t1; 1376drop view v1; 1377 1378create table t1 (a mediumint zerofill); 1379insert into t1 values (1), (10), (100), (1000), (10000); 1380create view v1(a) as select concat(a) from t1; 1381show columns from v1; 1382select hex(a) from v1; 1383drop table t1; 1384drop view v1; 1385 1386create table t1 (a int); 1387insert into t1 values (1); 1388create view v1(a) as select concat(a) from t1; 1389show columns from v1; 1390select hex(a) from v1; 1391drop table t1; 1392drop view v1; 1393 1394create table t1 (a int zerofill); 1395insert into t1 values (1), (10), (100), (1000), (10000); 1396create view v1(a) as select concat(a) from t1; 1397show columns from v1; 1398select hex(a) from v1; 1399drop table t1; 1400drop view v1; 1401 1402create table t1 (a bigint); 1403insert into t1 values (1); 1404create view v1(a) as select concat(a) from t1; 1405show columns from v1; 1406select hex(a) from v1; 1407drop table t1; 1408drop view v1; 1409 1410create table t1 (a bigint zerofill); 1411insert into t1 values (1), (10), (100), (1000), (10000); 1412create view v1(a) as select concat(a) from t1; 1413show columns from v1; 1414select hex(a) from v1; 1415drop table t1; 1416drop view v1; 1417 1418create table t1 (a float); 1419insert into t1 values (123.456); 1420create view v1(a) as select concat(a) from t1; 1421show columns from v1; 1422select hex(a) from v1; 1423drop table t1; 1424drop view v1; 1425 1426create table t1 (a float zerofill); 1427insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 1428create view v1(a) as select concat(a) from t1; 1429show columns from v1; 1430select hex(a) from v1; 1431drop table t1; 1432drop view v1; 1433 1434create table t1 (a double); 1435insert into t1 values (123.456); 1436select concat(a) from t1; 1437create view v1(a) as select concat(a) from t1; 1438show columns from v1; 1439select hex(a) from v1; 1440drop table t1; 1441drop view v1; 1442 1443create table t1 (a double zerofill); 1444insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); 1445create view v1(a) as select concat(a) from t1; 1446show columns from v1; 1447select hex(a) from v1; 1448drop table t1; 1449drop view v1; 1450 1451create table t1 (a year(4)); 1452insert into t1 values (1); 1453create view v1(a) as select concat(a) from t1; 1454show columns from v1; 1455select hex(a) from v1; 1456drop table t1; 1457drop view v1; 1458 1459create table t1 (a year); 1460insert into t1 values (1); 1461create view v1(a) as select concat(a) from t1; 1462show columns from v1; 1463select hex(a) from v1; 1464drop table t1; 1465drop view v1; 1466 1467create table t1 (a bit(64)); 1468# BIT is always BINARY 1469insert into t1 values (1); 1470create view v1(a) as select concat(a) from t1; 1471show columns from v1; 1472select hex(a) from v1; 1473drop table t1; 1474drop view v1; 1475 1476create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 1477insert ignore into t1 values (0); 1478insert into t1 values (20010203040506); 1479insert into t1 values (19800203040506); 1480insert into t1 values ('2001-02-03 04:05:06'); 1481create view v1(a) as select concat(a) from t1; 1482show columns from v1; 1483select hex(a) from v1; 1484drop table t1; 1485drop view v1; 1486 1487create table t1 (a date); 1488insert into t1 values ('2001-02-03'); 1489insert into t1 values (20010203); 1490create view v1(a) as select concat(a) from t1; 1491show columns from v1; 1492select hex(a) from v1; 1493drop table t1; 1494drop view v1; 1495 1496create table t1 (a time); 1497insert into t1 values (1); 1498insert into t1 values ('01:02:03'); 1499create view v1(a) as select concat(a) from t1; 1500show columns from v1; 1501select hex(a) from v1; 1502drop table t1; 1503drop view v1; 1504 1505create table t1 (a datetime); 1506insert into t1 values ('2001-02-03 04:05:06'); 1507insert into t1 values (20010203040506); 1508create view v1(a) as select concat(a) from t1; 1509show columns from v1; 1510select hex(a) from v1; 1511drop table t1; 1512drop view v1; 1513 1514# 1515# User defined function returning numeric result 1516# 1517delimiter |; 1518create function f1 (par1 int) returns int 1519begin 1520return concat(par1); 1521end| 1522delimiter ;| 1523 1524set @a= f1(1); 1525select hex(@a); 1526select hex(concat(f1(1))); 1527create table t1 as select f1(1) as c1; 1528show create table t1; 1529drop table t1; 1530create table t1 as select concat(f1(1)) as c1; 1531show create table t1; 1532create view v1 as select concat(f1(1)) as c1; 1533show columns from v1; 1534drop table t1; 1535drop view v1; 1536drop function f1; 1537 1538delimiter |; 1539create function f1 (par1 decimal(18,2)) returns decimal(18,2) 1540begin 1541return concat(par1); 1542end| 1543delimiter ;| 1544 1545set @a= f1(123.45); 1546select hex(@a); 1547select hex(concat(f1(123.45))); 1548create table t1 as select f1(123.45) as c1; 1549show create table t1; 1550drop table t1; 1551create table t1 as select concat(f1(123.45)) as c1; 1552show create table t1; 1553create view v1 as select concat(f1(123.45)) as c1; 1554show columns from v1; 1555drop table t1; 1556drop view v1; 1557drop function f1; 1558 1559delimiter |; 1560create function f1 (par1 float) returns float 1561begin 1562return concat(par1); 1563end| 1564delimiter ;| 1565 1566set @a= f1(123.45); 1567select hex(@a); 1568select hex(concat(f1(123.45))); 1569create table t1 as select f1(123.45) as c1; 1570show create table t1; 1571drop table t1; 1572create table t1 as select concat(f1(123.45)) as c1; 1573show create table t1; 1574create view v1 as select concat(f1(123.45)) as c1; 1575show columns from v1; 1576drop table t1; 1577drop view v1; 1578drop function f1; 1579 1580delimiter |; 1581create function f1 (par1 date) returns date 1582begin 1583return concat(par1); 1584end| 1585delimiter ;| 1586 1587set @a= f1(cast('2001-01-02' as date)); 1588select hex(@a); 1589select hex(concat(f1(cast('2001-01-02' as date)))); 1590create table t1 as select f1(cast('2001-01-02' as date)) as c1; 1591show create table t1; 1592drop table t1; 1593create table t1 as select concat(f1(cast('2001-01-02' as date))) as c1; 1594show create table t1; 1595create view v1 as select concat(f1(cast('2001-01-02' as date))) as c1; 1596show columns from v1; 1597drop table t1; 1598drop view v1; 1599drop function f1; 1600 1601 1602--echo # 1603--echo # End of WL#2649 Number-to-string conversions 1604--echo # 1605 1606--echo # 1607--echo # Bug#54668 User variable assignments get wrong type 1608--echo # 1609SET @ST_X=md5('a'); 1610SELECT charset(@ST_X), collation(@ST_X); 1611SET @ST_X=password('a'); 1612SELECT charset(@ST_X), collation(@ST_X); 1613SET @ST_X=sha('a'); 1614SELECT charset(@ST_X), collation(@ST_X); 1615SET @ST_X=sha1('a'); 1616SELECT charset(@ST_X), collation(@ST_X); 1617SET @ST_X=ST_astext(point(1,2)); 1618SELECT charset(@ST_X), collation(@ST_X); 1619SET @ST_X=ST_AsWKT(point(1,2)); 1620SELECT charset(@ST_X), collation(@ST_X); 1621 1622 1623--echo # 1624--echo # Bug#54916 GROUP_CONCAT + IFNULL truncates output 1625--echo # 1626SELECT @@collation_connection; 1627# ENGINE=MYISAM is very important to make sure "SYSTEM" join type 1628# is in use, which will create instances of Item_copy. 1629CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM; 1630INSERT INTO t1 VALUES (1234567); 1631SELECT GROUP_CONCAT(IFNULL(a,'')) FROM t1; 1632SELECT GROUP_CONCAT(IF(a,a,'')) FROM t1; 1633SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1; 1634--enable_metadata 1635SELECT COALESCE(a,'') FROM t1 GROUP BY 1; 1636--disable_metadata 1637--echo # All columns must be VARCHAR(9) with the same length: 1638--disable_warnings 1639CREATE TABLE t2 AS 1640SELECT 1641 CONCAT(a), 1642 IFNULL(a,''), 1643 IF(a,a,''), 1644 CASE WHEN a THEN a ELSE '' END, 1645 COALESCE(a,'') 1646FROM t1; 1647--enable_warnings 1648# The above query is expected to send a warning 1649# in case of ucs2 character set, until Bug#55744 is fixed. 1650SHOW CREATE TABLE t2; 1651DROP TABLE t2; 1652 1653CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1; 1654SHOW CREATE TABLE t2; 1655DROP TABLE t2; 1656 1657CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1; 1658SHOW CREATE TABLE t2; 1659DROP TABLE t2; 1660 1661CREATE TABLE t2 AS SELECT LCASE(a) FROM t1; 1662SHOW CREATE TABLE t2; 1663DROP TABLE t2; 1664 1665CREATE TABLE t2 AS SELECT UCASE(a) FROM t1; 1666SHOW CREATE TABLE t2; 1667DROP TABLE t2; 1668 1669CREATE TABLE t2 AS SELECT REPEAT(1,2) FROM t1; 1670SHOW CREATE TABLE t2; 1671DROP TABLE t2; 1672 1673CREATE TABLE t2 AS SELECT LEFT(123,2) FROM t1; 1674SHOW CREATE TABLE t2; 1675DROP TABLE t2; 1676 1677CREATE TABLE t2 AS SELECT RIGHT(123,2) FROM t1; 1678SHOW CREATE TABLE t2; 1679DROP TABLE t2; 1680 1681CREATE TABLE t2 AS SELECT LTRIM(123) FROM t1; 1682SHOW CREATE TABLE t2; 1683DROP TABLE t2; 1684 1685CREATE TABLE t2 AS SELECT RTRIM(123) FROM t1; 1686SHOW CREATE TABLE t2; 1687DROP TABLE t2; 1688 1689CREATE TABLE t2 AS SELECT ELT(1,111,222,333) FROM t1; 1690SHOW CREATE TABLE t2; 1691DROP TABLE t2; 1692 1693CREATE TABLE t2 AS SELECT REPLACE(111,2,3) FROM t1; 1694SHOW CREATE TABLE t2; 1695DROP TABLE t2; 1696 1697CREATE TABLE t2 AS SELECT SUBSTRING_INDEX(111,111,1) FROM t1; 1698SHOW CREATE TABLE t2; 1699DROP TABLE t2; 1700 1701CREATE TABLE t2 AS SELECT MAKE_SET(111,222,3) FROM t1; 1702SHOW CREATE TABLE t2; 1703DROP TABLE t2; 1704 1705CREATE TABLE t2 AS SELECT SOUNDEX(1) FROM t1; 1706SHOW CREATE TABLE t2; 1707DROP TABLE t2; 1708 1709CREATE TABLE t2 AS SELECT EXPORT_SET(1,'ST_Y','N','',8); 1710SHOW CREATE TABLE t2; 1711DROP TABLE t2; 1712 1713DROP TABLE t1; 1714 1715--echo # 1716--echo # End of Bug#54916 1717--echo # 1718 1719--echo # 1720--echo # WL#5510 Functions to_base64 and from_base64 1721--echo # 1722CREATE TABLE t1 AS SELECT TO_BASE64('test') AS to_base64; 1723SHOW CREATE TABLE t1; 1724SELECT to_base64, LENGTH(to_base64), HEX(to_base64) FROM t1; 1725CREATE TABLE t2 AS SELECT FROM_BASE64(to_base64) AS from_base64 FROM t1; 1726SHOW CREATE TABLE t2; 1727SELECT CAST(from_base64 AS CHAR), LENGTH(from_base64), HEX(from_base64) FROM t2; 1728DROP TABLE t2; 1729DROP TABLE t1; 1730 1731 1732--echo # 1733--echo # Bug#58190 BETWEEN no longer uses indexes for date or datetime fields 1734--echo # 1735SELECT @@collation_connection; 1736CREATE TABLE t1 ( 1737 id INT(11) DEFAULT NULL, 1738 date_column DATE DEFAULT NULL, 1739 KEY(date_column)); 1740INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01'); 1741ANALYZE TABLE t1; 1742EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; 1743ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL; 1744ANALYZE TABLE t1; 1745EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; 1746DROP TABLE t1; 1747 1748 1749--echo # 1750--echo # Bug #31384 DATE_ADD() and DATE_SUB() return binary data 1751--echo # 1752SELECT @@collation_connection, @@character_set_results; 1753CREATE TABLE t1 AS 1754SELECT 1755 DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, 1756 DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, 1757 DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, 1758 DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; 1759SHOW CREATE TABLE t1; 1760DROP TABLE t1; 1761--enable_metadata 1762# PS protocol gives different "Max length" value for DATETIME. 1763--disable_ps_protocol 1764SELECT 1765 DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1, 1766 DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, 1767 DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, 1768 DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; 1769--disable_metadata 1770--enable_ps_protocol 1771SELECT 1772 HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, 1773 HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2, 1774 HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, 1775 HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; 1776 1777--echo # 1778--echo # Bug#11926811 / Bug#60625 Illegal mix of collations 1779--echo # 1780SELECT @@collation_connection; 1781DELIMITER //; 1782CREATE PROCEDURE p1() 1783BEGIN 1784 DECLARE v_LastPaymentDate DATETIME DEFAULT NULL; 1785 SELECT v_LastPaymentDate < NOW(); 1786 EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW(); 1787 SHOW WARNINGS; 1788 EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW()); 1789END// 1790DELIMITER ;// 1791CALL p1; 1792DROP PROCEDURE p1; 1793 1794--echo # 1795--echo # Bug#52159 returning time type from function and empty left join causes debug assertion 1796--echo # 1797CREATE FUNCTION f1() RETURNS TIME RETURN 1; 1798CREATE TABLE t1 (b INT); 1799INSERT INTO t1 VALUES (0); 1800SELECT f1() FROM t1 LEFT JOIN (SELECT 1 AS a FROM t1 LIMIT 0) AS d ON 1 GROUP BY a; 1801DROP FUNCTION f1; 1802DROP TABLE t1; 1803 1804SET NAMES latin1; 1805SET sql_mode=''; 1806CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); 1807INSERT INTO t1 VALUES (); 1808--disable_warnings 1809SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; 1810--enable_warnings 1811DROP TABLE t1; 1812SET sql_mode=default; 1813