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