1
drop table if exists t1;
3
create table t1 (a varchar(10) not null) character set cp1251;
4
insert into t1 values ("a"),("ab"),("abc");
10
select a, left(a,1) as b from t1;
15
select a, left(a,1) as b from t1 group by a;
20
SELECT DISTINCT RIGHT(a,1) from t1;
26
create table t1 (a char(3) binary, b binary(3)) character set cp1251;
27
insert into t1 values ('aaa','bbb'),('AAA','BBB');
28
select upper(a),upper(b) from t1;
32
select lower(a),lower(b) from t1;
36
select * from t1 where upper(a)='AAA';
40
select * from t1 where lower(a)='aaa';
44
select * from t1 where upper(b)='BBB';
47
select * from t1 where lower(b)='bbb';
50
select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1;
51
charset(a) charset(b) charset(binary 'ccc')
53
select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1;
54
collation(a) collation(b) collation(binary 'ccc')
55
cp1251_bin binary binary
58
a varchar(16) character set cp1251 collate cp1251_bin not null,
59
b int(10) default null,
62
insert into t1 (a) values ('air'),
63
('we'),('g'),('we_toshko'), ('s0urce'),('we_ivo'),('we_iliyan'),
64
('we_martin'),('vw_grado'),('vw_vasko'),('tn_vili'),('tn_kalina'),
65
('tn_fakira'),('vw_silvia'),('vw_starshi'),('vw_geo'),('vw_b0x1');
66
select * from t1 where a like 'we_%';
76
) ENGINE=MYISAM character set cp1251;
79
t1 CREATE TABLE `t1` (
80
`e1` enum('������') DEFAULT NULL,
81
`e2` enum('�����') DEFAULT NULL
82
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
87
SELECT @@collation_connection;
88
@@collation_connection
90
CREATE TABLE t1 AS SELECT ' ' AS a LIMIT 0;
91
INSERT INTO t1 VALUES (0x00),(0x01),(0x02),(0x03),(0x04),(0x05),(0x06),(0x07);
92
INSERT INTO t1 VALUES (0x08),(0x09),(0x0A),(0x0B),(0x0C),(0x0D),(0x0E),(0x0F);
93
INSERT INTO t1 VALUES (0x10),(0x11),(0x12),(0x13),(0x14),(0x15),(0x16),(0x17);
94
INSERT INTO t1 VALUES (0x18),(0x19),(0x1A),(0x1B),(0x1C),(0x1D),(0x1E),(0x1F);
95
INSERT INTO t1 VALUES (0x20),(0x21),(0x22),(0x23),(0x24),(0x25),(0x26),(0x27);
96
INSERT INTO t1 VALUES (0x28),(0x29),(0x2A),(0x2B),(0x2C),(0x2D),(0x2E),(0x2F);
97
INSERT INTO t1 VALUES (0x30),(0x31),(0x32),(0x33),(0x34),(0x35),(0x36),(0x37);
98
INSERT INTO t1 VALUES (0x38),(0x39),(0x3A),(0x3B),(0x3C),(0x3D),(0x3E),(0x3F);
99
INSERT INTO t1 VALUES (0x40),(0x41),(0x42),(0x43),(0x44),(0x45),(0x46),(0x47);
100
INSERT INTO t1 VALUES (0x48),(0x49),(0x4A),(0x4B),(0x4C),(0x4D),(0x4E),(0x4F);
101
INSERT INTO t1 VALUES (0x50),(0x51),(0x52),(0x53),(0x54),(0x55),(0x56),(0x57);
102
INSERT INTO t1 VALUES (0x58),(0x59),(0x5A),(0x5B),(0x5C),(0x5D),(0x5E),(0x5F);
103
INSERT INTO t1 VALUES (0x60),(0x61),(0x62),(0x63),(0x64),(0x65),(0x66),(0x67);
104
INSERT INTO t1 VALUES (0x68),(0x69),(0x6A),(0x6B),(0x6C),(0x6D),(0x6E),(0x6F);
105
INSERT INTO t1 VALUES (0x70),(0x71),(0x72),(0x73),(0x74),(0x75),(0x76),(0x77);
106
INSERT INTO t1 VALUES (0x78),(0x79),(0x7A),(0x7B),(0x7C),(0x7D),(0x7E),(0x7F);
107
INSERT INTO t1 VALUES (0x80),(0x81),(0x82),(0x83),(0x84),(0x85),(0x86),(0x87);
108
INSERT INTO t1 VALUES (0x88),(0x89),(0x8A),(0x8B),(0x8C),(0x8D),(0x8E),(0x8F);
109
INSERT INTO t1 VALUES (0x90),(0x91),(0x92),(0x93),(0x94),(0x95),(0x96),(0x97);
110
INSERT INTO t1 VALUES (0x98),(0x99),(0x9A),(0x9B),(0x9C),(0x9D),(0x9E),(0x9F);
111
INSERT INTO t1 VALUES (0xA0),(0xA1),(0xA2),(0xA3),(0xA4),(0xA5),(0xA6),(0xA7);
112
INSERT INTO t1 VALUES (0xA8),(0xA9),(0xAA),(0xAB),(0xAC),(0xAD),(0xAE),(0xAF);
113
INSERT INTO t1 VALUES (0xB0),(0xB1),(0xB2),(0xB3),(0xB4),(0xB5),(0xB6),(0xB7);
114
INSERT INTO t1 VALUES (0xB8),(0xB9),(0xBA),(0xBB),(0xBC),(0xBD),(0xBE),(0xBF);
115
INSERT INTO t1 VALUES (0xC0),(0xC1),(0xC2),(0xC3),(0xC4),(0xC5),(0xC6),(0xC7);
116
INSERT INTO t1 VALUES (0xC8),(0xC9),(0xCA),(0xCB),(0xCC),(0xCD),(0xCE),(0xCF);
117
INSERT INTO t1 VALUES (0xD0),(0xD1),(0xD2),(0xD3),(0xD4),(0xD5),(0xD6),(0xD7);
118
INSERT INTO t1 VALUES (0xD8),(0xD9),(0xDA),(0xDB),(0xDC),(0xDD),(0xDE),(0xDF);
119
INSERT INTO t1 VALUES (0xE0),(0xE1),(0xE2),(0xE3),(0xE4),(0xE5),(0xE6),(0xE7);
120
INSERT INTO t1 VALUES (0xE8),(0xE9),(0xEA),(0xEB),(0xEC),(0xED),(0xEE),(0xEF);
121
INSERT INTO t1 VALUES (0xF0),(0xF1),(0xF2),(0xF3),(0xF4),(0xF5),(0xF6),(0xF7);
122
INSERT INTO t1 VALUES (0xF8),(0xF9),(0xFA),(0xFB),(0xFC),(0xFD),(0xFE),(0xFF);
125
HEX(LOWER(a)) AS upper,
126
HEX(LOWER(a)) AS lower,
127
HEX(@utf8:=CONVERT(a USING utf8)) AS utf8,
128
HEX(@roundtrip:=CAST(@utf8 AS CHAR)) AS roundtrip,
129
if(a=BINARY @roundtrip,'','Round trip unsafe') AS issafe
130
FROM t1 ORDER BY chr;
131
chr upper lower utf8 roundtrip issafe
284
98 98 98 3F 3F Round trip unsafe
389
set global LC_TIME_NAMES=convert((-8388608) using cp1251);
390
ERROR HY000: Unknown locale: '-8388608'
397
SET TIME_ZONE = _latin1 '+03:00';
399
# Start of WL#2649 Number-to-string conversions
401
select hex(concat(1));
404
create table t1 as select concat(1) as c1;
405
show create table t1;
407
t1 CREATE TABLE `t1` (
408
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
409
) ENGINE=MyISAM DEFAULT CHARSET=latin1
410
select hex(c1) from t1;
414
select hex(concat(18446744073709551615));
415
hex(concat(18446744073709551615))
416
3138343436373434303733373039353531363135
417
create table t1 as select concat(18446744073709551615) as c1;
418
show create table t1;
420
t1 CREATE TABLE `t1` (
421
`c1` varchar(20) CHARACTER SET cp1251 NOT NULL DEFAULT ''
422
) ENGINE=MyISAM DEFAULT CHARSET=latin1
423
select hex(c1) from t1;
425
3138343436373434303733373039353531363135
427
select hex(concat(1.1));
430
create table t1 as select concat(1.1) as c1;
431
show create table t1;
433
t1 CREATE TABLE `t1` (
434
`c1` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT ''
435
) ENGINE=MyISAM DEFAULT CHARSET=latin1
436
select hex(c1) from t1;
440
select hex(concat('a', 1+2)), charset(concat(1+2));
441
hex(concat('a', 1+2)) charset(concat(1+2))
443
create table t1 as select concat(1+2) as c1;
444
show create table t1;
446
t1 CREATE TABLE `t1` (
447
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
448
) ENGINE=MyISAM DEFAULT CHARSET=latin1
450
select hex(concat(1-2));
453
create table t1 as select concat(1-2) as c1;
454
show create table t1;
456
t1 CREATE TABLE `t1` (
457
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
458
) ENGINE=MyISAM DEFAULT CHARSET=latin1
460
select hex(concat(1*2));
463
create table t1 as select concat(1*2) as c1;
464
show create table t1;
466
t1 CREATE TABLE `t1` (
467
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
468
) ENGINE=MyISAM DEFAULT CHARSET=latin1
470
select hex(concat(1/2));
473
create table t1 as select concat(1/2) as c1;
474
show create table t1;
476
t1 CREATE TABLE `t1` (
477
`c1` varchar(7) CHARACTER SET cp1251 DEFAULT NULL
478
) ENGINE=MyISAM DEFAULT CHARSET=latin1
480
select hex(concat(1 div 2));
483
create table t1 as select concat(1 div 2) as c1;
484
show create table t1;
486
t1 CREATE TABLE `t1` (
487
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
488
) ENGINE=MyISAM DEFAULT CHARSET=latin1
490
select hex(concat(1 % 2));
493
create table t1 as select concat(1 % 2) as c1;
494
show create table t1;
496
t1 CREATE TABLE `t1` (
497
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
498
) ENGINE=MyISAM DEFAULT CHARSET=latin1
500
select hex(concat(-1));
503
create table t1 as select concat(-1) as c1;
504
show create table t1;
506
t1 CREATE TABLE `t1` (
507
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
508
) ENGINE=MyISAM DEFAULT CHARSET=latin1
510
select hex(concat(-(1+2)));
513
create table t1 as select concat(-(1+2)) as c1;
514
show create table t1;
516
t1 CREATE TABLE `t1` (
517
`c1` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT ''
518
) ENGINE=MyISAM DEFAULT CHARSET=latin1
520
select hex(concat(1|2));
523
create table t1 as select concat(1|2) as c1;
524
show create table t1;
526
t1 CREATE TABLE `t1` (
527
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
528
) ENGINE=MyISAM DEFAULT CHARSET=latin1
530
select hex(concat(1&2));
533
create table t1 as select concat(1&2) as c1;
534
show create table t1;
536
t1 CREATE TABLE `t1` (
537
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
538
) ENGINE=MyISAM DEFAULT CHARSET=latin1
540
select hex(concat(bit_count(12)));
541
hex(concat(bit_count(12)))
543
create table t1 as select concat(bit_count(12)) as c1;
544
show create table t1;
546
t1 CREATE TABLE `t1` (
547
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
548
) ENGINE=MyISAM DEFAULT CHARSET=latin1
550
select hex(concat(2<<1));
553
create table t1 as select concat(2<<1) as c1;
554
show create table t1;
556
t1 CREATE TABLE `t1` (
557
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
558
) ENGINE=MyISAM DEFAULT CHARSET=latin1
560
select hex(concat(2>>1));
563
create table t1 as select concat(2>>1) as c1;
564
show create table t1;
566
t1 CREATE TABLE `t1` (
567
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
568
) ENGINE=MyISAM DEFAULT CHARSET=latin1
570
select hex(concat(~0));
572
3138343436373434303733373039353531363135
573
create table t1 as select concat(~0) as c1;
574
show create table t1;
576
t1 CREATE TABLE `t1` (
577
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
578
) ENGINE=MyISAM DEFAULT CHARSET=latin1
580
select hex(concat(3^2));
583
create table t1 as select concat(3^2) as c1;
584
show create table t1;
586
t1 CREATE TABLE `t1` (
587
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
588
) ENGINE=MyISAM DEFAULT CHARSET=latin1
590
select hex(concat(abs(-2)));
593
create table t1 as select concat(abs(-2)) as c1;
594
show create table t1;
596
t1 CREATE TABLE `t1` (
597
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
598
) ENGINE=MyISAM DEFAULT CHARSET=latin1
600
select hex(left(concat(exp(2)),1));
601
hex(left(concat(exp(2)),1))
603
create table t1 as select concat(exp(2)) as c1;
604
show create table t1;
606
t1 CREATE TABLE `t1` (
607
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
608
) ENGINE=MyISAM DEFAULT CHARSET=latin1
610
select hex(left(concat(log(2)),1));
611
hex(left(concat(log(2)),1))
613
create table t1 as select concat(log(2)) as c1;
614
show create table t1;
616
t1 CREATE TABLE `t1` (
617
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
618
) ENGINE=MyISAM DEFAULT CHARSET=latin1
620
select hex(left(concat(log2(2)),1));
621
hex(left(concat(log2(2)),1))
623
create table t1 as select concat(log2(2)) as c1;
624
show create table t1;
626
t1 CREATE TABLE `t1` (
627
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
628
) ENGINE=MyISAM DEFAULT CHARSET=latin1
630
select hex(left(concat(log10(2)),1));
631
hex(left(concat(log10(2)),1))
633
create table t1 as select concat(log10(2)) as c1;
634
show create table t1;
636
t1 CREATE TABLE `t1` (
637
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
638
) ENGINE=MyISAM DEFAULT CHARSET=latin1
640
select hex(left(concat(sqrt(2)),1));
641
hex(left(concat(sqrt(2)),1))
643
create table t1 as select concat(sqrt(2)) as c1;
644
show create table t1;
646
t1 CREATE TABLE `t1` (
647
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
648
) ENGINE=MyISAM DEFAULT CHARSET=latin1
650
select hex(left(concat(pow(2,2)),1));
651
hex(left(concat(pow(2,2)),1))
653
create table t1 as select concat(pow(2,2)) as c1;
654
show create table t1;
656
t1 CREATE TABLE `t1` (
657
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
658
) ENGINE=MyISAM DEFAULT CHARSET=latin1
660
select hex(left(concat(acos(0.5)),1));
661
hex(left(concat(acos(0.5)),1))
663
create table t1 as select concat(acos(0.5)) as c1;
664
show create table t1;
666
t1 CREATE TABLE `t1` (
667
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
668
) ENGINE=MyISAM DEFAULT CHARSET=latin1
670
select hex(left(concat(asin(0.5)),1));
671
hex(left(concat(asin(0.5)),1))
673
create table t1 as select concat(asin(0.5)) as c1;
674
show create table t1;
676
t1 CREATE TABLE `t1` (
677
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
678
) ENGINE=MyISAM DEFAULT CHARSET=latin1
680
select hex(left(concat(atan(0.5)),1));
681
hex(left(concat(atan(0.5)),1))
683
create table t1 as select concat(atan(0.5)) as c1;
684
show create table t1;
686
t1 CREATE TABLE `t1` (
687
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
688
) ENGINE=MyISAM DEFAULT CHARSET=latin1
690
select hex(left(concat(cos(0.5)),1));
691
hex(left(concat(cos(0.5)),1))
693
create table t1 as select concat(cos(0.5)) as c1;
694
show create table t1;
696
t1 CREATE TABLE `t1` (
697
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
698
) ENGINE=MyISAM DEFAULT CHARSET=latin1
700
select hex(left(concat(sin(0.5)),1));
701
hex(left(concat(sin(0.5)),1))
703
create table t1 as select concat(sin(0.5)) as c1;
704
show create table t1;
706
t1 CREATE TABLE `t1` (
707
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
708
) ENGINE=MyISAM DEFAULT CHARSET=latin1
710
select hex(left(concat(tan(0.5)),1));
711
hex(left(concat(tan(0.5)),1))
713
create table t1 as select concat(tan(0.5)) as c1;
714
show create table t1;
716
t1 CREATE TABLE `t1` (
717
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
718
) ENGINE=MyISAM DEFAULT CHARSET=latin1
720
select hex(concat(degrees(0)));
721
hex(concat(degrees(0)))
723
create table t1 as select concat(degrees(0)) as c1;
724
show create table t1;
726
t1 CREATE TABLE `t1` (
727
`c1` varchar(23) CHARACTER SET cp1251 NOT NULL DEFAULT ''
728
) ENGINE=MyISAM DEFAULT CHARSET=latin1
730
select hex(concat(radians(0)));
731
hex(concat(radians(0)))
733
create table t1 as select concat(radians(0)) as c1;
734
show create table t1;
736
t1 CREATE TABLE `t1` (
737
`c1` varchar(23) CHARACTER SET cp1251 NOT NULL DEFAULT ''
738
) ENGINE=MyISAM DEFAULT CHARSET=latin1
740
select hex(concat(ceiling(0.5)));
741
hex(concat(ceiling(0.5)))
743
create table t1 as select concat(ceiling(0.5)) as c1;
744
show create table t1;
746
t1 CREATE TABLE `t1` (
747
`c1` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT ''
748
) ENGINE=MyISAM DEFAULT CHARSET=latin1
750
select hex(concat(floor(0.5)));
751
hex(concat(floor(0.5)))
753
create table t1 as select concat(floor(0.5)) as c1;
754
show create table t1;
756
t1 CREATE TABLE `t1` (
757
`c1` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT ''
758
) ENGINE=MyISAM DEFAULT CHARSET=latin1
760
select hex(concat(round(0.5)));
761
hex(concat(round(0.5)))
763
create table t1 as select concat(round(0.5)) as c1;
764
show create table t1;
766
t1 CREATE TABLE `t1` (
767
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
768
) ENGINE=MyISAM DEFAULT CHARSET=latin1
770
select hex(concat(sign(0.5)));
771
hex(concat(sign(0.5)))
773
create table t1 as select concat(sign(0.5)) as c1;
774
show create table t1;
776
t1 CREATE TABLE `t1` (
777
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
778
) ENGINE=MyISAM DEFAULT CHARSET=latin1
780
create table t1 as select concat(rand()) as c1;
781
show create table t1;
783
t1 CREATE TABLE `t1` (
784
`c1` varchar(23) CHARACTER SET cp1251 NOT NULL DEFAULT ''
785
) ENGINE=MyISAM DEFAULT CHARSET=latin1
787
select hex(concat(length('a')));
788
hex(concat(length('a')))
790
create table t1 as select concat(length('a')) as c1;
791
show create table t1;
793
t1 CREATE TABLE `t1` (
794
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
795
) ENGINE=MyISAM DEFAULT CHARSET=latin1
797
select hex(concat(char_length('a')));
798
hex(concat(char_length('a')))
800
create table t1 as select concat(char_length('a')) as c1;
801
show create table t1;
803
t1 CREATE TABLE `t1` (
804
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
805
) ENGINE=MyISAM DEFAULT CHARSET=latin1
807
select hex(concat(bit_length('a')));
808
hex(concat(bit_length('a')))
810
create table t1 as select concat(bit_length('a')) as c1;
811
show create table t1;
813
t1 CREATE TABLE `t1` (
814
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
815
) ENGINE=MyISAM DEFAULT CHARSET=latin1
817
select hex(concat(coercibility('a')));
818
hex(concat(coercibility('a')))
820
create table t1 as select concat(coercibility('a')) as c1;
821
show create table t1;
823
t1 CREATE TABLE `t1` (
824
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
825
) ENGINE=MyISAM DEFAULT CHARSET=latin1
827
select hex(concat(locate('a','a')));
828
hex(concat(locate('a','a')))
830
create table t1 as select concat(locate('a','a')) as c1;
831
show create table t1;
833
t1 CREATE TABLE `t1` (
834
`c1` varchar(11) CHARACTER SET cp1251 NOT NULL DEFAULT ''
835
) ENGINE=MyISAM DEFAULT CHARSET=latin1
837
select hex(concat(field('c','a','b','c')));
838
hex(concat(field('c','a','b','c')))
840
create table t1 as select concat(field('c','a','b','c')) as c1;
841
show create table t1;
843
t1 CREATE TABLE `t1` (
844
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
845
) ENGINE=MyISAM DEFAULT CHARSET=latin1
847
select hex(concat(ascii(61)));
848
hex(concat(ascii(61)))
850
create table t1 as select concat(ascii(61)) as c1;
851
show create table t1;
853
t1 CREATE TABLE `t1` (
854
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
855
) ENGINE=MyISAM DEFAULT CHARSET=latin1
857
select hex(concat(ord(61)));
860
create table t1 as select concat(ord(61)) as c1;
861
show create table t1;
863
t1 CREATE TABLE `t1` (
864
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
865
) ENGINE=MyISAM DEFAULT CHARSET=latin1
867
select hex(concat(find_in_set('b','a,b,c,d')));
868
hex(concat(find_in_set('b','a,b,c,d')))
870
create table t1 as select concat(find_in_set('b','a,b,c,d')) as c1;
871
show create table t1;
873
t1 CREATE TABLE `t1` (
874
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
875
) ENGINE=MyISAM DEFAULT CHARSET=latin1
877
select md5('a'), hex(md5('a'));
878
md5('a') hex(md5('a'))
879
0cc175b9c0f1b6a831c399e269772661 3063633137356239633066316236613833316333393965323639373732363631
880
create table t1 as select md5('a') as c1;
881
show create table t1;
883
t1 CREATE TABLE `t1` (
884
`c1` varchar(32) CHARACTER SET cp1251 NOT NULL DEFAULT ''
885
) ENGINE=MyISAM DEFAULT CHARSET=latin1
887
select old_password('a'), hex(old_password('a'));
888
old_password('a') hex(old_password('a'))
889
60671c896665c3fa 36303637316338393636363563336661
890
create table t1 as select old_password('a') as c1;
891
show create table t1;
893
t1 CREATE TABLE `t1` (
894
`c1` varchar(16) CHARACTER SET cp1251 NOT NULL DEFAULT ''
895
) ENGINE=MyISAM DEFAULT CHARSET=latin1
897
select password('a'), hex(password('a'));
898
password('a') hex(password('a'))
899
*667F407DE7C6AD07358FA38DAED7828A72014B4E 2A36363746343037444537433641443037333538464133384441454437383238413732303134423445
900
create table t1 as select password('a') as c1;
901
show create table t1;
903
t1 CREATE TABLE `t1` (
904
`c1` varchar(41) CHARACTER SET cp1251 NOT NULL DEFAULT ''
905
) ENGINE=MyISAM DEFAULT CHARSET=latin1
907
select sha('a'), hex(sha('a'));
908
sha('a') hex(sha('a'))
909
86f7e437faa5a7fce15d1ddcb9eaeaea377667b8 38366637653433376661613561376663653135643164646362396561656165613337373636376238
910
create table t1 as select sha('a') as c1;
911
show create table t1;
913
t1 CREATE TABLE `t1` (
914
`c1` varchar(40) CHARACTER SET cp1251 NOT NULL DEFAULT ''
915
) ENGINE=MyISAM DEFAULT CHARSET=latin1
917
select sha1('a'), hex(sha1('a'));
918
sha1('a') hex(sha1('a'))
919
86f7e437faa5a7fce15d1ddcb9eaeaea377667b8 38366637653433376661613561376663653135643164646362396561656165613337373636376238
920
create table t1 as select sha1('a') as c1;
921
show create table t1;
923
t1 CREATE TABLE `t1` (
924
`c1` varchar(40) CHARACTER SET cp1251 NOT NULL DEFAULT ''
925
) ENGINE=MyISAM DEFAULT CHARSET=latin1
927
select hex(concat(cast('-1' as signed)));
928
hex(concat(cast('-1' as signed)))
930
create table t1 as select concat(cast('-1' as signed)) as c1;
931
show create table t1;
933
t1 CREATE TABLE `t1` (
934
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
935
) ENGINE=MyISAM DEFAULT CHARSET=latin1
937
select hex(concat(cast('1' as unsigned)));
938
hex(concat(cast('1' as unsigned)))
940
create table t1 as select concat(cast('1' as unsigned)) as c1;
941
show create table t1;
943
t1 CREATE TABLE `t1` (
944
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
945
) ENGINE=MyISAM DEFAULT CHARSET=latin1
947
select hex(concat(cast(1/2 as decimal(5,5))));
948
hex(concat(cast(1/2 as decimal(5,5))))
950
create table t1 as select concat(cast(1/2 as decimal(5,5))) as c1;
951
show create table t1;
953
t1 CREATE TABLE `t1` (
954
`c1` varchar(7) CHARACTER SET cp1251 DEFAULT NULL
955
) ENGINE=MyISAM DEFAULT CHARSET=latin1
957
select hex(concat(cast('2001-01-02 03:04:05' as date)));
958
hex(concat(cast('2001-01-02 03:04:05' as date)))
960
create table t1 as select concat(cast('2001-01-02 03:04:05' as date)) as c1;
961
show create table t1;
963
t1 CREATE TABLE `t1` (
964
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
965
) ENGINE=MyISAM DEFAULT CHARSET=latin1
970
select hex(concat(cast('2001-01-02 03:04:05' as time)));
971
hex(concat(cast('2001-01-02 03:04:05' as time)))
973
create table t1 as select concat(cast('2001-01-02 03:04:05' as time)) as c1;
974
show create table t1;
976
t1 CREATE TABLE `t1` (
977
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
978
) ENGINE=MyISAM DEFAULT CHARSET=latin1
983
select hex(concat(cast('2001-01-02' as datetime)));
984
hex(concat(cast('2001-01-02' as datetime)))
985
323030312D30312D30322030303A30303A3030
986
create table t1 as select concat(cast('2001-01-02' as datetime)) as c1;
987
show create table t1;
989
t1 CREATE TABLE `t1` (
990
`c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL
991
) ENGINE=MyISAM DEFAULT CHARSET=latin1
996
select hex(concat(least(1,2)));
997
hex(concat(least(1,2)))
999
create table t1 as select concat(least(1,2)) as c1;
1000
show create table t1;
1002
t1 CREATE TABLE `t1` (
1003
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1004
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1006
select hex(concat(greatest(1,2)));
1007
hex(concat(greatest(1,2)))
1009
create table t1 as select concat(greatest(1,2)) as c1;
1010
show create table t1;
1012
t1 CREATE TABLE `t1` (
1013
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1014
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1016
select hex(concat(case when 11 then 22 else 33 end));
1017
hex(concat(case when 11 then 22 else 33 end))
1019
create table t1 as select concat(case when 11 then 22 else 33 end) as c1;
1020
show create table t1;
1022
t1 CREATE TABLE `t1` (
1023
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1024
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1026
select hex(concat(coalesce(1,2)));
1027
hex(concat(coalesce(1,2)))
1029
create table t1 as select concat(coalesce(1,2)) as c1;
1030
show create table t1;
1032
t1 CREATE TABLE `t1` (
1033
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1034
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1036
select hex(concat_ws(1,2,3));
1037
hex(concat_ws(1,2,3))
1039
create table t1 as select concat_ws(1,2,3) as c1;
1040
show create table t1;
1042
t1 CREATE TABLE `t1` (
1043
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1044
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1046
select hex(group_concat(1,2,3));
1047
hex(group_concat(1,2,3))
1049
create table t1 as select group_concat(1,2,3) as c1;
1050
show create table t1;
1052
t1 CREATE TABLE `t1` (
1053
`c1` text CHARACTER SET cp1251
1054
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1056
create table t1 as select 1 as c1 union select 'a';
1057
show create table t1;
1059
t1 CREATE TABLE `t1` (
1060
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1061
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1062
select hex(c1) from t1 order by c1;
1067
create table t1 as select concat(last_insert_id()) as c1;
1068
show create table t1;
1070
t1 CREATE TABLE `t1` (
1071
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1072
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1074
select hex(concat(benchmark(0,0)));
1075
hex(concat(benchmark(0,0)))
1077
create table t1 as select concat(benchmark(0,0)) as c1;
1078
show create table t1;
1080
t1 CREATE TABLE `t1` (
1081
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1082
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1084
select hex(concat(sleep(0)));
1085
hex(concat(sleep(0)))
1087
create table t1 as select concat(sleep(0)) as c1;
1088
show create table t1;
1090
t1 CREATE TABLE `t1` (
1091
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1092
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1094
select hex(concat(is_free_lock('xxxx')));
1095
hex(concat(is_free_lock('xxxx')))
1097
create table t1 as select concat(is_free_lock('xxxx')) as c1;
1098
show create table t1;
1100
t1 CREATE TABLE `t1` (
1101
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
1102
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1104
create table t1 as select concat(is_used_lock('a')) as c1;
1105
show create table t1;
1107
t1 CREATE TABLE `t1` (
1108
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1109
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1111
create table t1 as select concat(release_lock('a')) as c1;
1112
show create table t1;
1114
t1 CREATE TABLE `t1` (
1115
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
1116
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1118
select hex(concat(crc32('')));
1119
hex(concat(crc32('')))
1121
create table t1 as select concat(crc32('')) as c1;
1122
show create table t1;
1124
t1 CREATE TABLE `t1` (
1125
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1126
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1128
select hex(concat(uncompressed_length('')));
1129
hex(concat(uncompressed_length('')))
1131
create table t1 as select concat(uncompressed_length('')) as c1;
1132
show create table t1;
1134
t1 CREATE TABLE `t1` (
1135
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1136
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1138
create table t1 as select concat(connection_id()) as c1;
1139
show create table t1;
1141
t1 CREATE TABLE `t1` (
1142
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1143
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1145
select hex(concat(inet_aton('127.1.1.1')));
1146
hex(concat(inet_aton('127.1.1.1')))
1147
32313330373732323235
1148
create table t1 as select concat(inet_aton('127.1.1.1')) as c1;
1149
show create table t1;
1151
t1 CREATE TABLE `t1` (
1152
`c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL
1153
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1155
select hex(concat(inet_ntoa(2130772225)));
1156
hex(concat(inet_ntoa(2130772225)))
1158
create table t1 as select concat(inet_ntoa(2130772225)) as c1;
1162
show create table t1;
1164
t1 CREATE TABLE `t1` (
1165
`c1` varchar(31) CHARACTER SET cp1251 DEFAULT NULL
1166
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1171
select hex(concat(row_count()));
1172
hex(concat(row_count()))
1174
create table t1 as select concat(row_count()) as c1;
1175
show create table t1;
1177
t1 CREATE TABLE `t1` (
1178
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1179
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1181
select hex(concat(found_rows()));
1182
hex(concat(found_rows()))
1184
create table t1 as select concat(found_rows()) as c1;
1185
show create table t1;
1187
t1 CREATE TABLE `t1` (
1188
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1189
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1191
create table t1 as select concat(uuid_short()) as c1;
1192
show create table t1;
1194
t1 CREATE TABLE `t1` (
1195
`c1` varchar(21) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1196
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1198
create table t1 as select concat(uuid()) as c1;
1199
show create table t1;
1201
t1 CREATE TABLE `t1` (
1202
`c1` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT ''
1203
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1205
select coercibility(uuid()), coercibility(cast('a' as char character set latin1));
1206
coercibility(uuid()) coercibility(cast('a' as char character set latin1))
1208
select charset(concat(uuid(), cast('a' as char character set latin1)));
1209
charset(concat(uuid(), cast('a' as char character set latin1)))
1211
create table t1 as select concat(uuid(), cast('a' as char character set latin1)) as c1;
1212
show create table t1;
1214
t1 CREATE TABLE `t1` (
1215
`c1` varchar(37) NOT NULL DEFAULT ''
1216
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1218
create table t1 as select concat(master_pos_wait('non-existent',0,2)) as c1;
1219
show create table t1;
1221
t1 CREATE TABLE `t1` (
1222
`c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL
1223
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1225
select hex(concat(@a1:=1));
1228
create table t1 as select concat(@a2:=2) as c1, @a3:=3 as c2;
1229
select hex(c1) from t1;
1232
show create table t1;
1234
t1 CREATE TABLE `t1` (
1235
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT '',
1236
`c2` int(1) NOT NULL DEFAULT '0'
1237
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1240
select hex(concat(@a2));
1243
create table t1 as select concat(@a2) as c1, @a2 as c2;
1244
select hex(c1) from t1;
1247
show create table t1;
1249
t1 CREATE TABLE `t1` (
1250
`c1` varchar(20) CHARACTER SET cp1251 DEFAULT NULL,
1251
`c2` bigint(20) DEFAULT NULL
1252
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1254
select hex(concat(@a1:=sqrt(1)));
1255
hex(concat(@a1:=sqrt(1)))
1257
create table t1 as select concat(@a2:=sqrt(1)) as c1, @a3:=sqrt(1) as c2;
1258
select hex(c1) from t1;
1261
show create table t1;
1263
t1 CREATE TABLE `t1` (
1264
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL,
1265
`c2` double DEFAULT NULL
1266
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1269
select hex(concat(@a2));
1272
create table t1 as select concat(@a2) as c1, @a2 as c2;
1273
select hex(c1) from t1;
1276
show create table t1;
1278
t1 CREATE TABLE `t1` (
1279
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL,
1280
`c2` double DEFAULT NULL
1281
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1283
select hex(concat(@a1:=1.1));
1284
hex(concat(@a1:=1.1))
1286
create table t1 as select concat(@a2:=1.1) as c1, @a3:=1.1 as c2;
1287
select hex(c1) from t1;
1290
show create table t1;
1292
t1 CREATE TABLE `t1` (
1293
`c1` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT '',
1294
`c2` decimal(2,1) NOT NULL DEFAULT '0.0'
1295
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1298
select hex(concat(@a2));
1301
create table t1 as select concat(@a2) as c1, @a2 as c2;
1302
select hex(c1) from t1;
1305
show create table t1;
1307
t1 CREATE TABLE `t1` (
1308
`c1` varchar(83) CHARACTER SET cp1251 DEFAULT NULL,
1309
`c2` decimal(65,30) DEFAULT NULL
1310
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1312
select hex(concat(@@ft_max_word_len));
1313
hex(concat(@@ft_max_word_len))
1315
create table t1 as select concat(@@ft_max_word_len) as c1;
1316
select hex(c1) from t1;
1319
show create table t1;
1321
t1 CREATE TABLE `t1` (
1322
`c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL
1323
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1325
select hex(concat('a'='a' IS TRUE));
1326
hex(concat('a'='a' IS TRUE))
1328
create table t1 as select concat('a'='a' IS TRUE) as c1;
1329
show create table t1;
1331
t1 CREATE TABLE `t1` (
1332
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1333
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1335
select hex(concat('a'='a' IS NOT TRUE));
1336
hex(concat('a'='a' IS NOT TRUE))
1338
create table t1 as select concat('a'='a' IS NOT TRUE) as c1;
1339
show create table t1;
1341
t1 CREATE TABLE `t1` (
1342
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1343
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1345
select hex(concat(NOT 'a'='a'));
1346
hex(concat(NOT 'a'='a'))
1348
create table t1 as select concat(NOT 'a'='a') as c1;
1349
show create table t1;
1351
t1 CREATE TABLE `t1` (
1352
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1353
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1355
select hex(concat('a' IS NULL));
1356
hex(concat('a' IS NULL))
1358
create table t1 as select concat('a' IS NULL) as c1;
1359
show create table t1;
1361
t1 CREATE TABLE `t1` (
1362
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1363
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1365
select hex(concat('a' IS NOT NULL));
1366
hex(concat('a' IS NOT NULL))
1368
create table t1 as select concat('a' IS NOT NULL) as c1;
1369
show create table t1;
1371
t1 CREATE TABLE `t1` (
1372
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1373
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1375
select hex(concat('a' rlike 'a'));
1376
hex(concat('a' rlike 'a'))
1378
create table t1 as select concat('a' IS NOT NULL) as c1;
1379
show create table t1;
1381
t1 CREATE TABLE `t1` (
1382
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1383
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1385
select hex(concat(strcmp('a','b')));
1386
hex(concat(strcmp('a','b')))
1388
create table t1 as select concat(strcmp('a','b')) as c1;
1389
show create table t1;
1391
t1 CREATE TABLE `t1` (
1392
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1393
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1395
select hex(concat('a' like 'a'));
1396
hex(concat('a' like 'a'))
1398
create table t1 as select concat('a' like 'b') as c1;
1399
show create table t1;
1401
t1 CREATE TABLE `t1` (
1402
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1403
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1405
select hex(concat('a' between 'b' and 'c'));
1406
hex(concat('a' between 'b' and 'c'))
1408
create table t1 as select concat('a' between 'b' and 'c') as c1;
1409
show create table t1;
1411
t1 CREATE TABLE `t1` (
1412
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1413
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1415
select hex(concat('a' in ('a','b')));
1416
hex(concat('a' in ('a','b')))
1418
create table t1 as select concat('a' in ('a','b')) as c1;
1419
show create table t1;
1421
t1 CREATE TABLE `t1` (
1422
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1423
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1425
select hex(concat(interval(23, 1, 15, 17, 30, 44, 200)));
1426
hex(concat(interval(23, 1, 15, 17, 30, 44, 200)))
1428
create table t1 as select concat(interval(23, 1, 15, 17, 30, 44, 200)) as c1;
1429
show create table t1;
1431
t1 CREATE TABLE `t1` (
1432
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1433
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1435
create table t1 (a varchar(10), fulltext key(a));
1436
insert into t1 values ('a');
1437
select hex(concat(match (a) against ('a'))) from t1;
1438
hex(concat(match (a) against ('a')))
1440
create table t2 as select concat(match (a) against ('a')) as a from t1;
1441
show create table t2;
1443
t2 CREATE TABLE `t2` (
1444
`a` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
1445
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1447
select hex(ifnull(1,'a'));
1450
create table t1 as select ifnull(1,'a') as c1;
1451
show create table t1;
1453
t1 CREATE TABLE `t1` (
1454
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1455
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1457
select hex(concat(ifnull(1,1)));
1458
hex(concat(ifnull(1,1)))
1460
create table t1 as select concat(ifnull(1,1)) as c1;
1461
show create table t1;
1463
t1 CREATE TABLE `t1` (
1464
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1465
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1467
select hex(concat(ifnull(1.1,1.1)));
1468
hex(concat(ifnull(1.1,1.1)))
1470
create table t1 as select concat(ifnull(1.1,1.1)) as c1;
1471
show create table t1;
1473
t1 CREATE TABLE `t1` (
1474
`c1` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1475
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1477
select hex(if(1,'b',1));
1480
create table t1 as select if(1,'b',1) as c1;
1481
show create table t1;
1483
t1 CREATE TABLE `t1` (
1484
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1485
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1487
select hex(if(1,1,'b'));
1490
create table t1 as select if(1,1,'b') as c1;
1491
show create table t1;
1493
t1 CREATE TABLE `t1` (
1494
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1495
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1497
select hex(concat(if(1,1,1)));
1498
hex(concat(if(1,1,1)))
1500
create table t1 as select concat(if(1,1,1)) as c1;
1501
show create table t1;
1503
t1 CREATE TABLE `t1` (
1504
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1505
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1507
select hex(concat(nullif(1,2)));
1508
hex(concat(nullif(1,2)))
1510
create table t1 as select concat(nullif(1,2)) as c1;
1511
show create table t1;
1513
t1 CREATE TABLE `t1` (
1514
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
1515
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1517
select hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)'))));
1518
hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)'))))
1520
create table t1 as select concat(Dimension(GeomFromText('LINSTRING(0 0,10 10)'))) as c1;
1521
show create table t1;
1523
t1 CREATE TABLE `t1` (
1524
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1525
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1527
select hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))));
1528
hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))))
1530
create table t1 as select concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1;
1531
show create table t1;
1533
t1 CREATE TABLE `t1` (
1534
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1535
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1537
select hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))));
1538
hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))))
1540
create table t1 as select concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))) as c1;
1541
show create table t1;
1543
t1 CREATE TABLE `t1` (
1544
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1545
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1547
select hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))));
1548
hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))))
1550
create table t1 as select concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1;
1551
show create table t1;
1553
t1 CREATE TABLE `t1` (
1554
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1555
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1557
select 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))'))));
1558
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))'))))
1560
create 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;
1561
show create table t1;
1563
t1 CREATE TABLE `t1` (
1564
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1565
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1567
select hex(concat(IsEmpty(GeomFromText('POINT(1 1)'))));
1568
hex(concat(IsEmpty(GeomFromText('POINT(1 1)'))))
1570
create table t1 as select concat(IsEmpty(GeomFromText('Point(1 1)'))) as c1;
1571
show create table t1;
1573
t1 CREATE TABLE `t1` (
1574
`c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL
1575
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1577
select hex(concat(IsSimple(GeomFromText('POINT(1 1)'))));
1578
hex(concat(IsSimple(GeomFromText('POINT(1 1)'))))
1580
create table t1 as select concat(IsSimple(GeomFromText('Point(1 1)'))) as c1;
1581
show create table t1;
1583
t1 CREATE TABLE `t1` (
1584
`c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL
1585
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1587
select hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))));
1588
hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))))
1590
create table t1 as select concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))) as c1;
1591
show create table t1;
1593
t1 CREATE TABLE `t1` (
1594
`c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL
1595
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1597
select hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))));
1598
hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))))
1600
create table t1 as select concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))) as c1;
1602
select hex(concat(x(GeomFromText('Point(1 2)'))));
1603
hex(concat(x(GeomFromText('Point(1 2)'))))
1605
create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1;
1606
show create table t1;
1608
t1 CREATE TABLE `t1` (
1609
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
1610
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1612
select hex(concat(y(GeomFromText('Point(1 2)'))));
1613
hex(concat(y(GeomFromText('Point(1 2)'))))
1615
create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1;
1616
show create table t1;
1618
t1 CREATE TABLE `t1` (
1619
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
1620
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1622
select hex(concat(GLength(GeomFromText('LineString(1 2,2 2)'))));
1623
hex(concat(GLength(GeomFromText('LineString(1 2,2 2)'))))
1625
create table t1 as select concat(GLength(GeomFromText('LineString(1 2, 2 2)'))) as c1;
1626
show create table t1;
1628
t1 CREATE TABLE `t1` (
1629
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
1630
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1632
select hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))));
1633
hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))))
1635
create table t1 as select concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))) as c1;
1636
show create table t1;
1638
t1 CREATE TABLE `t1` (
1639
`c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL
1640
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1642
select hex(concat(GeometryType(GeomFromText('Point(1 2)'))));
1643
hex(concat(GeometryType(GeomFromText('Point(1 2)'))))
1645
create table t1 as select concat(GeometryType(GeomFromText('Point(1 2)'))) as c1;
1646
show create table t1;
1648
t1 CREATE TABLE `t1` (
1649
`c1` varchar(20) CHARACTER SET cp1251 DEFAULT NULL
1650
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1652
select hex(concat(AsText(GeomFromText('Point(1 2)'))));
1653
hex(concat(AsText(GeomFromText('Point(1 2)'))))
1654
504F494E542831203229
1655
create table t1 as select concat(AsText(GeomFromText('Point(1 2)'))) as c1;
1656
show create table t1;
1658
t1 CREATE TABLE `t1` (
1659
`c1` longtext CHARACTER SET cp1251
1660
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1662
select hex(concat(period_add(200902, 2)));
1663
hex(concat(period_add(200902, 2)))
1665
create table t1 as select concat(period_add(200902, 2)) as c1;
1666
show create table t1;
1668
t1 CREATE TABLE `t1` (
1669
`c1` varchar(6) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1670
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1672
select hex(concat(period_diff(200902, 200802)));
1673
hex(concat(period_diff(200902, 200802)))
1675
create table t1 as select concat(period_add(200902, 200802)) as c1;
1677
Warning 1265 Data truncated for column 'c1' at row 1
1678
show create table t1;
1680
t1 CREATE TABLE `t1` (
1681
`c1` varchar(6) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1682
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1684
select hex(concat(to_days(20090224)));
1685
hex(concat(to_days(20090224)))
1687
create table t1 as select concat(to_days(20090224)) as c1;
1688
show create table t1;
1690
t1 CREATE TABLE `t1` (
1691
`c1` varchar(6) CHARACTER SET cp1251 DEFAULT NULL
1692
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1694
select hex(concat(dayofmonth(20090224)));
1695
hex(concat(dayofmonth(20090224)))
1697
create table t1 as select concat(dayofmonth(20090224)) as c1;
1698
show create table t1;
1700
t1 CREATE TABLE `t1` (
1701
`c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL
1702
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1704
select hex(concat(dayofyear(20090224)));
1705
hex(concat(dayofyear(20090224)))
1707
create table t1 as select concat(dayofyear(20090224)) as c1;
1708
show create table t1;
1710
t1 CREATE TABLE `t1` (
1711
`c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL
1712
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1714
select hex(concat(hour('10:11:12')));
1715
hex(concat(hour('10:11:12')))
1717
create table t1 as select concat(hour('10:11:12')) as c1;
1718
show create table t1;
1720
t1 CREATE TABLE `t1` (
1721
`c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL
1722
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1724
select hex(concat(minute('10:11:12')));
1725
hex(concat(minute('10:11:12')))
1727
create table t1 as select concat(minute('10:11:12')) as c1;
1728
show create table t1;
1730
t1 CREATE TABLE `t1` (
1731
`c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL
1732
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1734
select hex(concat(second('10:11:12')));
1735
hex(concat(second('10:11:12')))
1737
create table t1 as select concat(second('10:11:12')) as c1;
1738
show create table t1;
1740
t1 CREATE TABLE `t1` (
1741
`c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL
1742
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1744
select hex(concat(quarter(20090224)));
1745
hex(concat(quarter(20090224)))
1747
create table t1 as select concat(quarter(20090224)) as c1;
1748
show create table t1;
1750
t1 CREATE TABLE `t1` (
1751
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
1752
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1754
select hex(concat(week(20090224)));
1755
hex(concat(week(20090224)))
1757
create table t1 as select concat(week(20090224)) as c1;
1758
show create table t1;
1760
t1 CREATE TABLE `t1` (
1761
`c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL
1762
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1764
select hex(concat(yearweek(20090224)));
1765
hex(concat(yearweek(20090224)))
1767
create table t1 as select concat(yearweek(20090224)) as c1;
1768
show create table t1;
1770
t1 CREATE TABLE `t1` (
1771
`c1` varchar(6) CHARACTER SET cp1251 DEFAULT NULL
1772
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1774
select hex(concat(year(20090224)));
1775
hex(concat(year(20090224)))
1777
create table t1 as select concat(year(20090224)) as c1;
1778
show create table t1;
1780
t1 CREATE TABLE `t1` (
1781
`c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL
1782
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1784
select hex(concat(weekday(20090224)));
1785
hex(concat(weekday(20090224)))
1787
create table t1 as select concat(weekday(20090224)) as c1;
1788
show create table t1;
1790
t1 CREATE TABLE `t1` (
1791
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
1792
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1794
select hex(concat(dayofweek(20090224)));
1795
hex(concat(dayofweek(20090224)))
1797
create table t1 as select concat(dayofweek(20090224)) as c1;
1798
show create table t1;
1800
t1 CREATE TABLE `t1` (
1801
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
1802
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1804
select hex(concat(unix_timestamp(20090224)));
1805
hex(concat(unix_timestamp(20090224)))
1806
31323335343232383030
1807
create table t1 as select concat(unix_timestamp(20090224)) as c1;
1808
show create table t1;
1810
t1 CREATE TABLE `t1` (
1811
`c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL
1812
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1814
select hex(concat(time_to_sec('10:11:12')));
1815
hex(concat(time_to_sec('10:11:12')))
1816
33363637322E303030303030
1817
create table t1 as select concat(time_to_sec('10:11:12')) as c1;
1818
show create table t1;
1820
t1 CREATE TABLE `t1` (
1821
`c1` varchar(24) CHARACTER SET cp1251 DEFAULT NULL
1822
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1824
select hex(concat(extract(year from 20090702)));
1825
hex(concat(extract(year from 20090702)))
1827
create table t1 as select concat(extract(year from 20090702)) as c1;
1828
show create table t1;
1830
t1 CREATE TABLE `t1` (
1831
`c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL
1832
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1834
select hex(concat(microsecond('12:00:00.123456')));
1835
hex(concat(microsecond('12:00:00.123456')))
1837
create table t1 as select concat(microsecond('12:00:00.123456')) as c1;
1838
show create table t1;
1840
t1 CREATE TABLE `t1` (
1841
`c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL
1842
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1844
select hex(concat(month(20090224)));
1845
hex(concat(month(20090224)))
1847
create table t1 as select concat(month(20090224)) as c1;
1848
show create table t1;
1850
t1 CREATE TABLE `t1` (
1851
`c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL
1852
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1854
create table t1 as select concat(last_day('2003-02-05')) as c1;
1855
show create table t1;
1857
t1 CREATE TABLE `t1` (
1858
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1859
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1860
select c1, hex(c1) from t1;
1862
2003-02-28 323030332D30322D3238
1864
create table t1 as select concat(from_days(730669)) as c1;
1865
show create table t1;
1867
t1 CREATE TABLE `t1` (
1868
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1869
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1870
select c1, hex(c1) from t1;
1872
2000-07-03 323030302D30372D3033
1874
create table t1 as select concat(curdate()) as c1;
1875
show create table t1;
1877
t1 CREATE TABLE `t1` (
1878
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1879
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1881
create table t1 as select concat(utc_date()) as c1;
1882
show create table t1;
1884
t1 CREATE TABLE `t1` (
1885
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1886
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1888
create table t1 as select concat(curtime()) as c1;
1889
show create table t1;
1891
t1 CREATE TABLE `t1` (
1892
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1893
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1895
create table t1 as select repeat('a',20) as c1 limit 0;
1896
set timestamp=1216359724;
1897
insert into t1 values (current_date);
1898
insert into t1 values (current_time);
1899
select c1, hex(c1) from t1;
1901
2008-07-18 323030382D30372D3138
1902
08:42:04 30383A34323A3034
1904
create table t1 as select concat(utc_time()) as c1;
1905
show create table t1;
1907
t1 CREATE TABLE `t1` (
1908
`c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
1909
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1911
select hex(concat(sec_to_time(2378)));
1912
hex(concat(sec_to_time(2378)))
1914
create table t1 as select concat(sec_to_time(2378)) as c1;
1915
show create table t1;
1917
t1 CREATE TABLE `t1` (
1918
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1919
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1921
select hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')));
1922
hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')))
1924
create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')) as c1;
1925
show create table t1;
1927
t1 CREATE TABLE `t1` (
1928
`c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL
1929
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1931
select hex(concat(maketime(10,11,12)));
1932
hex(concat(maketime(10,11,12)))
1934
create table t1 as select concat(maketime(10,11,12)) as c1;
1935
show create table t1;
1937
t1 CREATE TABLE `t1` (
1938
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1939
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1941
select hex(get_format(DATE,'USA'));
1942
hex(get_format(DATE,'USA'))
1944
create table t1 as select get_format(DATE,'USA') as c1;
1945
show create table t1;
1947
t1 CREATE TABLE `t1` (
1948
`c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL
1949
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1951
select hex(left(concat(from_unixtime(1111885200)),4));
1952
hex(left(concat(from_unixtime(1111885200)),4))
1954
create table t1 as select concat(from_unixtime(1111885200)) as c1;
1955
show create table t1;
1957
t1 CREATE TABLE `t1` (
1958
`c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL
1959
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1961
select hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')));
1962
hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')))
1963
323030332D31322D33312032303A30303A3030
1964
create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')) as c1;
1965
show create table t1;
1967
t1 CREATE TABLE `t1` (
1968
`c1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL
1969
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1971
select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day)));
1972
hex(concat(date_add('2004-01-01 12:00:00', interval 1 day)))
1973
323030342D30312D30322031323A30303A3030
1974
create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day)) as c1;
1975
show create table t1;
1977
t1 CREATE TABLE `t1` (
1978
`c1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL
1979
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1984
select hex(concat(makedate(2009,1)));
1985
hex(concat(makedate(2009,1)))
1986
323030392D30312D3031
1987
create table t1 as select concat(makedate(2009,1)) as c1;
1988
show create table t1;
1990
t1 CREATE TABLE `t1` (
1991
`c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
1992
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1997
create table t1 as select concat(now()) as c1;
1998
show create table t1;
2000
t1 CREATE TABLE `t1` (
2001
`c1` varchar(19) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2002
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2004
create table t1 as select concat(utc_timestamp()) as c1;
2005
show create table t1;
2007
t1 CREATE TABLE `t1` (
2008
`c1` varchar(19) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2009
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2011
create table t1 as select concat(sysdate()) as c1;
2012
show create table t1;
2014
t1 CREATE TABLE `t1` (
2015
`c1` varchar(19) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2016
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2018
select hex(concat(addtime('00:00:00','11:22:33')));
2019
hex(concat(addtime('00:00:00','11:22:33')))
2021
create table t1 as select concat(addtime('00:00:00','11:22:33')) as c1;
2022
show create table t1;
2024
t1 CREATE TABLE `t1` (
2025
`c1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL
2026
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2028
select hex(concat(subtime('23:59:59','11:22:33')));
2029
hex(concat(subtime('23:59:59','11:22:33')))
2031
create table t1 as select concat(subtime('23:59:59','11:22:33')) as c1;
2032
show create table t1;
2034
t1 CREATE TABLE `t1` (
2035
`c1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL
2036
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2038
select hex(elt(1,2,3));
2041
create table t1 as select elt(1,2,3) as c1;
2042
show create table t1;
2044
t1 CREATE TABLE `t1` (
2045
`c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL
2046
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2048
select hex(export_set(1,2,3,4,2));
2049
hex(export_set(1,2,3,4,2))
2051
create table t1 as select export_set(1,2,3,4,2) as c1;
2052
show create table t1;
2054
t1 CREATE TABLE `t1` (
2055
`c1` varchar(127) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2056
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2058
select hex(insert(1133,3,0,22));
2059
hex(insert(1133,3,0,22))
2061
create table t1 as select insert(1133,3,0,22) as c1;
2062
show create table t1;
2064
t1 CREATE TABLE `t1` (
2065
`c1` varchar(6) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2066
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2068
select hex(lcase(123));
2071
create table t1 as select lcase(123) as c1;
2072
show create table t1;
2074
t1 CREATE TABLE `t1` (
2075
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2076
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2078
select hex(left(123,1));
2081
create table t1 as select left(123,1) as c1;
2082
show create table t1;
2084
t1 CREATE TABLE `t1` (
2085
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2086
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2088
select hex(lower(123));
2091
create table t1 as select lower(123) as c1;
2092
show create table t1;
2094
t1 CREATE TABLE `t1` (
2095
`c1` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2096
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2098
select hex(lpad(1,2,0));
2101
create table t1 as select lpad(1,2,0) as c1;
2102
show create table t1;
2104
t1 CREATE TABLE `t1` (
2105
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2106
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2108
select hex(ltrim(1));
2111
create table t1 as select ltrim(1) as c1;
2112
show create table t1;
2114
t1 CREATE TABLE `t1` (
2115
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2116
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2118
select hex(mid(1,1,1));
2121
create table t1 as select mid(1,1,1) as c1;
2122
show create table t1;
2124
t1 CREATE TABLE `t1` (
2125
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2126
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2128
select hex(repeat(1,2));
2131
create table t1 as select repeat(1,2) as c1;
2132
show create table t1;
2134
t1 CREATE TABLE `t1` (
2135
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2136
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2138
select hex(replace(1,1,2));
2141
create table t1 as select replace(1,1,2) as c1;
2142
show create table t1;
2144
t1 CREATE TABLE `t1` (
2145
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2146
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2148
select hex(reverse(12));
2151
create table t1 as select reverse(12) as c1;
2152
show create table t1;
2154
t1 CREATE TABLE `t1` (
2155
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2156
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2158
select hex(right(123,1));
2161
create table t1 as select right(123,1) as c1;
2162
show create table t1;
2164
t1 CREATE TABLE `t1` (
2165
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2166
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2168
select hex(rpad(1,2,0));
2171
create table t1 as select rpad(1,2,0) as c1;
2172
show create table t1;
2174
t1 CREATE TABLE `t1` (
2175
`c1` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2176
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2178
select hex(rtrim(1));
2181
create table t1 as select rtrim(1) as c1;
2182
show create table t1;
2184
t1 CREATE TABLE `t1` (
2185
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2186
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2188
select hex(soundex(1));
2191
create table t1 as select soundex(1) as c1;
2192
show create table t1;
2194
t1 CREATE TABLE `t1` (
2195
`c1` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2196
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2198
select hex(substring(1,1,1));
2199
hex(substring(1,1,1))
2201
create table t1 as select substring(1,1,1) as c1;
2202
show create table t1;
2204
t1 CREATE TABLE `t1` (
2205
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2206
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2208
select hex(trim(1));
2211
create table t1 as select trim(1) as c1;
2212
show create table t1;
2214
t1 CREATE TABLE `t1` (
2215
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2216
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2218
select hex(ucase(1));
2221
create table t1 as select ucase(1) as c1;
2222
show create table t1;
2224
t1 CREATE TABLE `t1` (
2225
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2226
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2228
select hex(upper(1));
2231
create table t1 as select upper(1) as c1;
2232
show create table t1;
2234
t1 CREATE TABLE `t1` (
2235
`c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2236
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2238
create table t1 as select repeat(' ', 64) as a limit 0;
2239
show create table t1;
2241
t1 CREATE TABLE `t1` (
2242
`a` varchar(64) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2243
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2244
insert into t1 values ("1.1"), ("2.1");
2245
select a, hex(a) from t1;
2249
update t1 set a= a + 0.1;
2250
select a, hex(a) from t1;
2252
1.2000000000000002 312E32303030303030303030303030303032
2255
create table t1 (a tinyint);
2256
insert into t1 values (1);
2257
select hex(concat(a)) from t1;
2260
create table t2 as select concat(a) from t1;
2261
show create table t2;
2263
t2 CREATE TABLE `t2` (
2264
`concat(a)` varchar(4) CHARACTER SET cp1251 DEFAULT NULL
2265
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2267
create table t1 (a tinyint zerofill);
2268
insert into t1 values (1), (10), (100);
2269
select hex(concat(a)), a from t1;
2275
create table t1 (a tinyint(4) zerofill);
2276
insert into t1 values (1), (10), (100);
2277
select hex(concat(a)), a from t1;
2283
create table t1 (a decimal(10,2));
2284
insert into t1 values (123.45);
2285
select hex(concat(a)) from t1;
2288
create table t2 as select concat(a) from t1;
2289
show create table t2;
2291
t2 CREATE TABLE `t2` (
2292
`concat(a)` varchar(12) CHARACTER SET cp1251 DEFAULT NULL
2293
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2295
create table t1 (a smallint);
2296
insert into t1 values (1);
2297
select hex(concat(a)) from t1;
2300
create table t2 as select concat(a) from t1;
2301
show create table t2;
2303
t2 CREATE TABLE `t2` (
2304
`concat(a)` varchar(6) CHARACTER SET cp1251 DEFAULT NULL
2305
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2307
create table t1 (a smallint zerofill);
2308
insert into t1 values (1), (10), (100), (1000), (10000);
2309
select hex(concat(a)), a from t1;
2317
create table t1 (a mediumint);
2318
insert into t1 values (1);
2319
select hex(concat(a)) from t1;
2322
create table t2 as select concat(a) from t1;
2323
show create table t2;
2325
t2 CREATE TABLE `t2` (
2326
`concat(a)` varchar(9) CHARACTER SET cp1251 DEFAULT NULL
2327
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2329
create table t1 (a mediumint zerofill);
2330
insert into t1 values (1), (10), (100), (1000), (10000);
2331
select hex(concat(a)), a from t1;
2333
3030303030303031 00000001
2334
3030303030303130 00000010
2335
3030303030313030 00000100
2336
3030303031303030 00001000
2337
3030303130303030 00010000
2339
create table t1 (a int);
2340
insert into t1 values (1);
2341
select hex(concat(a)) from t1;
2344
create table t2 as select concat(a) from t1;
2345
show create table t2;
2347
t2 CREATE TABLE `t2` (
2348
`concat(a)` varchar(11) CHARACTER SET cp1251 DEFAULT NULL
2349
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2351
create table t1 (a int zerofill);
2352
insert into t1 values (1), (10), (100), (1000), (10000);
2353
select hex(concat(a)), a from t1;
2355
30303030303030303031 0000000001
2356
30303030303030303130 0000000010
2357
30303030303030313030 0000000100
2358
30303030303031303030 0000001000
2359
30303030303130303030 0000010000
2361
create table t1 (a bigint);
2362
insert into t1 values (1);
2363
select hex(concat(a)) from t1;
2366
create table t2 as select concat(a) from t1;
2367
show create table t2;
2369
t2 CREATE TABLE `t2` (
2370
`concat(a)` varchar(20) CHARACTER SET cp1251 DEFAULT NULL
2371
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2373
create table t1 (a bigint zerofill);
2374
insert into t1 values (1), (10), (100), (1000), (10000);
2375
select hex(concat(a)), a from t1;
2377
3030303030303030303030303030303030303031 00000000000000000001
2378
3030303030303030303030303030303030303130 00000000000000000010
2379
3030303030303030303030303030303030313030 00000000000000000100
2380
3030303030303030303030303030303031303030 00000000000000001000
2381
3030303030303030303030303030303130303030 00000000000000010000
2383
create table t1 (a float);
2384
insert into t1 values (123.456);
2385
select hex(concat(a)) from t1;
2388
select concat(a) from t1;
2391
create table t2 as select concat(a) from t1;
2392
show create table t2;
2394
t2 CREATE TABLE `t2` (
2395
`concat(a)` varchar(12) CHARACTER SET cp1251 DEFAULT NULL
2396
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2398
create table t1 (a float zerofill);
2399
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
2400
select hex(concat(a)), a from t1;
2402
303030303030303030312E31 0000000001.1
2403
303030303030303031302E31 0000000010.1
2404
303030303030303130302E31 0000000100.1
2405
303030303030313030302E31 0000001000.1
2406
303030303031303030302E31 0000010000.1
2408
create table t1 (a double);
2409
insert into t1 values (123.456);
2410
select hex(concat(a)) from t1;
2413
select concat(a) from t1;
2416
create table t2 as select concat(a) from t1;
2417
show create table t2;
2419
t2 CREATE TABLE `t2` (
2420
`concat(a)` varchar(22) CHARACTER SET cp1251 DEFAULT NULL
2421
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2423
create table t1 (a double zerofill);
2424
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
2425
select hex(concat(a)), a from t1;
2427
30303030303030303030303030303030303030312E31 00000000000000000001.1
2428
30303030303030303030303030303030303031302E31 00000000000000000010.1
2429
30303030303030303030303030303030303130302E31 00000000000000000100.1
2430
30303030303030303030303030303030313030302E31 00000000000000001000.1
2431
30303030303030303030303030303031303030302E31 00000000000000010000.1
2433
create table t1 (a year(2));
2435
Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
2436
insert into t1 values (1);
2437
select hex(concat(a)) from t1;
2440
create table t2 as select concat(a) from t1;
2441
show create table t2;
2443
t2 CREATE TABLE `t2` (
2444
`concat(a)` varchar(2) CHARACTER SET cp1251 DEFAULT NULL
2445
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2447
create table t1 (a year);
2448
insert into t1 values (1);
2449
select hex(concat(a)) from t1;
2452
create table t2 as select concat(a) from t1;
2453
show create table t2;
2455
t2 CREATE TABLE `t2` (
2456
`concat(a)` varchar(4) CHARACTER SET cp1251 DEFAULT NULL
2457
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2459
create table t1 (a bit(64));
2460
insert into t1 values (1);
2461
select hex(concat(a)) from t1;
2464
create table t2 as select concat(a) from t1;
2465
show create table t2;
2467
t2 CREATE TABLE `t2` (
2468
`concat(a)` varbinary(64) DEFAULT NULL
2469
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2471
create table t1 (a timestamp);
2472
insert into t1 values (0);
2473
insert into t1 values (20010203040506);
2474
insert into t1 values (19800203040506);
2475
insert into t1 values ('2001-02-03 04:05:06');
2476
select hex(concat(a)) from t1;
2478
303030302D30302D30302030303A30303A3030
2479
323030312D30322D30332030343A30353A3036
2480
313938302D30322D30332030343A30353A3036
2481
323030312D30322D30332030343A30353A3036
2482
select concat(a) from t1;
2488
create table t2 as select concat(a) from t1;
2489
show create table t2;
2491
t2 CREATE TABLE `t2` (
2492
`concat(a)` varchar(19) CHARACTER SET cp1251 NOT NULL DEFAULT ''
2493
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2495
create table t1 (a date);
2496
insert into t1 values ('2001-02-03');
2497
insert into t1 values (20010203);
2498
select hex(concat(a)) from t1;
2500
323030312D30322D3033
2501
323030312D30322D3033
2502
create table t2 as select concat(a) from t1;
2503
show create table t2;
2505
t2 CREATE TABLE `t2` (
2506
`concat(a)` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
2507
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2509
create table t1 (a time);
2510
insert into t1 values (1);
2511
insert into t1 values ('01:02:03');
2512
select hex(concat(a)) from t1;
2516
select concat(a) from t1;
2520
create table t2 as select concat(a) from t1;
2521
show create table t2;
2523
t2 CREATE TABLE `t2` (
2524
`concat(a)` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
2525
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2527
create table t1 (a datetime);
2528
insert into t1 values ('2001-02-03 04:05:06');
2529
insert into t1 values (20010203040506);
2530
select hex(concat(a)) from t1;
2532
323030312D30322D30332030343A30353A3036
2533
323030312D30322D30332030343A30353A3036
2534
create table t2 as select concat(a) from t1;
2535
show create table t2;
2537
t2 CREATE TABLE `t2` (
2538
`concat(a)` varchar(19) CHARACTER SET cp1251 DEFAULT NULL
2539
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2541
create table t1 (a tinyint);
2542
insert into t1 values (1);
2543
create view v1(a) as select concat(a) from t1;
2544
show columns from v1;
2545
Field Type Null Key Default Extra
2546
a varchar(4) YES NULL
2547
select hex(a) from v1;
2552
create table t1 (a tinyint zerofill);
2553
insert into t1 values (1), (10), (100);
2554
create view v1(a) as select concat(a) from t1;
2555
show columns from v1;
2556
Field Type Null Key Default Extra
2557
a varchar(3) YES NULL
2558
select hex(a) from v1;
2565
create table t1 (a tinyint(30) zerofill);
2566
insert into t1 values (1), (10), (100);
2567
create view v1(a) as select concat(a) from t1;
2568
show columns from v1;
2569
Field Type Null Key Default Extra
2570
a varchar(30) YES NULL
2571
select hex(a) from v1;
2573
303030303030303030303030303030303030303030303030303030303031
2574
303030303030303030303030303030303030303030303030303030303130
2575
303030303030303030303030303030303030303030303030303030313030
2578
create table t1 (a decimal(10,2));
2579
insert into t1 values (123.45);
2580
create view v1(a) as select concat(a) from t1;
2581
show columns from v1;
2582
Field Type Null Key Default Extra
2583
a varchar(12) YES NULL
2584
select hex(a) from v1;
2589
create table t1 (a smallint);
2590
insert into t1 values (1);
2591
create view v1(a) as select concat(a) from t1;
2592
show columns from v1;
2593
Field Type Null Key Default Extra
2594
a varchar(6) YES NULL
2595
select hex(a) from v1;
2600
create table t1 (a smallint zerofill);
2601
insert into t1 values (1), (10), (100), (1000), (10000);
2602
create view v1(a) as select concat(a) from t1;
2603
show columns from v1;
2604
Field Type Null Key Default Extra
2605
a varchar(5) YES NULL
2606
select hex(a) from v1;
2615
create table t1 (a mediumint);
2616
insert into t1 values (1);
2617
create view v1(a) as select concat(a) from t1;
2618
show columns from v1;
2619
Field Type Null Key Default Extra
2620
a varchar(9) YES NULL
2621
select hex(a) from v1;
2626
create table t1 (a mediumint zerofill);
2627
insert into t1 values (1), (10), (100), (1000), (10000);
2628
create view v1(a) as select concat(a) from t1;
2629
show columns from v1;
2630
Field Type Null Key Default Extra
2631
a varchar(8) YES NULL
2632
select hex(a) from v1;
2641
create table t1 (a int);
2642
insert into t1 values (1);
2643
create view v1(a) as select concat(a) from t1;
2644
show columns from v1;
2645
Field Type Null Key Default Extra
2646
a varchar(11) YES NULL
2647
select hex(a) from v1;
2652
create table t1 (a int zerofill);
2653
insert into t1 values (1), (10), (100), (1000), (10000);
2654
create view v1(a) as select concat(a) from t1;
2655
show columns from v1;
2656
Field Type Null Key Default Extra
2657
a varchar(10) YES NULL
2658
select hex(a) from v1;
2660
30303030303030303031
2661
30303030303030303130
2662
30303030303030313030
2663
30303030303031303030
2664
30303030303130303030
2667
create table t1 (a bigint);
2668
insert into t1 values (1);
2669
create view v1(a) as select concat(a) from t1;
2670
show columns from v1;
2671
Field Type Null Key Default Extra
2672
a varchar(20) YES NULL
2673
select hex(a) from v1;
2678
create table t1 (a bigint zerofill);
2679
insert into t1 values (1), (10), (100), (1000), (10000);
2680
create view v1(a) as select concat(a) from t1;
2681
show columns from v1;
2682
Field Type Null Key Default Extra
2683
a varchar(20) YES NULL
2684
select hex(a) from v1;
2686
3030303030303030303030303030303030303031
2687
3030303030303030303030303030303030303130
2688
3030303030303030303030303030303030313030
2689
3030303030303030303030303030303031303030
2690
3030303030303030303030303030303130303030
2693
create table t1 (a float);
2694
insert into t1 values (123.456);
2695
create view v1(a) as select concat(a) from t1;
2696
show columns from v1;
2697
Field Type Null Key Default Extra
2698
a varchar(12) YES NULL
2699
select hex(a) from v1;
2704
create table t1 (a float zerofill);
2705
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
2706
create view v1(a) as select concat(a) from t1;
2707
show columns from v1;
2708
Field Type Null Key Default Extra
2709
a varchar(12) YES NULL
2710
select hex(a) from v1;
2712
303030303030303030312E31
2713
303030303030303031302E31
2714
303030303030303130302E31
2715
303030303030313030302E31
2716
303030303031303030302E31
2719
create table t1 (a double);
2720
insert into t1 values (123.456);
2721
select concat(a) from t1;
2724
create view v1(a) as select concat(a) from t1;
2725
show columns from v1;
2726
Field Type Null Key Default Extra
2727
a varchar(22) YES NULL
2728
select hex(a) from v1;
2733
create table t1 (a double zerofill);
2734
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
2735
create view v1(a) as select concat(a) from t1;
2736
show columns from v1;
2737
Field Type Null Key Default Extra
2738
a varchar(22) YES NULL
2739
select hex(a) from v1;
2741
30303030303030303030303030303030303030312E31
2742
30303030303030303030303030303030303031302E31
2743
30303030303030303030303030303030303130302E31
2744
30303030303030303030303030303030313030302E31
2745
30303030303030303030303030303031303030302E31
2748
create table t1 (a year(2));
2750
Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
2751
insert into t1 values (1);
2752
create view v1(a) as select concat(a) from t1;
2753
show columns from v1;
2754
Field Type Null Key Default Extra
2755
a varchar(2) YES NULL
2756
select hex(a) from v1;
2761
create table t1 (a year);
2762
insert into t1 values (1);
2763
create view v1(a) as select concat(a) from t1;
2764
show columns from v1;
2765
Field Type Null Key Default Extra
2766
a varchar(4) YES NULL
2767
select hex(a) from v1;
2772
create table t1 (a bit(64));
2773
insert into t1 values (1);
2774
create view v1(a) as select concat(a) from t1;
2775
show columns from v1;
2776
Field Type Null Key Default Extra
2777
a varbinary(64) YES NULL
2778
select hex(a) from v1;
2783
create table t1 (a timestamp);
2784
insert into t1 values (0);
2785
insert into t1 values (20010203040506);
2786
insert into t1 values (19800203040506);
2787
insert into t1 values ('2001-02-03 04:05:06');
2788
create view v1(a) as select concat(a) from t1;
2789
show columns from v1;
2790
Field Type Null Key Default Extra
2792
select hex(a) from v1;
2794
303030302D30302D30302030303A30303A3030
2795
323030312D30322D30332030343A30353A3036
2796
313938302D30322D30332030343A30353A3036
2797
323030312D30322D30332030343A30353A3036
2800
create table t1 (a date);
2801
insert into t1 values ('2001-02-03');
2802
insert into t1 values (20010203);
2803
create view v1(a) as select concat(a) from t1;
2804
show columns from v1;
2805
Field Type Null Key Default Extra
2806
a varchar(10) YES NULL
2807
select hex(a) from v1;
2809
323030312D30322D3033
2810
323030312D30322D3033
2813
create table t1 (a time);
2814
insert into t1 values (1);
2815
insert into t1 values ('01:02:03');
2816
create view v1(a) as select concat(a) from t1;
2817
show columns from v1;
2818
Field Type Null Key Default Extra
2819
a varchar(10) YES NULL
2820
select hex(a) from v1;
2826
create table t1 (a datetime);
2827
insert into t1 values ('2001-02-03 04:05:06');
2828
insert into t1 values (20010203040506);
2829
create view v1(a) as select concat(a) from t1;
2830
show columns from v1;
2831
Field Type Null Key Default Extra
2832
a varchar(19) YES NULL
2833
select hex(a) from v1;
2835
323030312D30322D30332030343A30353A3036
2836
323030312D30322D30332030343A30353A3036
2839
create function f1 (par1 int) returns int
2841
return concat(par1);
2847
select hex(concat(f1(1)));
2850
create table t1 as select f1(1) as c1;
2851
show create table t1;
2853
t1 CREATE TABLE `t1` (
2854
`c1` int(11) DEFAULT NULL
2855
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2857
create table t1 as select concat(f1(1)) as c1;
2858
show create table t1;
2860
t1 CREATE TABLE `t1` (
2861
`c1` varchar(11) DEFAULT NULL
2862
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2863
create view v1 as select concat(f1(1)) as c1;
2864
show columns from v1;
2865
Field Type Null Key Default Extra
2866
c1 varchar(11) YES NULL
2870
create function f1 (par1 decimal(18,2)) returns decimal(18,2)
2872
return concat(par1);
2878
select hex(concat(f1(123.45)));
2879
hex(concat(f1(123.45)))
2881
create table t1 as select f1(123.45) as c1;
2882
show create table t1;
2884
t1 CREATE TABLE `t1` (
2885
`c1` decimal(18,2) DEFAULT NULL
2886
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2888
create table t1 as select concat(f1(123.45)) as c1;
2889
show create table t1;
2891
t1 CREATE TABLE `t1` (
2892
`c1` varchar(20) DEFAULT NULL
2893
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2894
create view v1 as select concat(f1(123.45)) as c1;
2895
show columns from v1;
2896
Field Type Null Key Default Extra
2897
c1 varchar(20) YES NULL
2901
create function f1 (par1 float) returns float
2903
return concat(par1);
2909
select hex(concat(f1(123.45)));
2910
hex(concat(f1(123.45)))
2912
create table t1 as select f1(123.45) as c1;
2913
show create table t1;
2915
t1 CREATE TABLE `t1` (
2916
`c1` float DEFAULT NULL
2917
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2919
create table t1 as select concat(f1(123.45)) as c1;
2920
show create table t1;
2922
t1 CREATE TABLE `t1` (
2923
`c1` varchar(12) DEFAULT NULL
2924
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2925
create view v1 as select concat(f1(123.45)) as c1;
2926
show columns from v1;
2927
Field Type Null Key Default Extra
2928
c1 varchar(12) YES NULL
2932
create function f1 (par1 date) returns date
2934
return concat(par1);
2936
set @a= f1(cast('2001-01-02' as date));
2939
323030312D30312D3032
2940
select hex(concat(f1(cast('2001-01-02' as date))));
2941
hex(concat(f1(cast('2001-01-02' as date))))
2942
323030312D30312D3032
2943
create table t1 as select f1(cast('2001-01-02' as date)) as c1;
2944
show create table t1;
2946
t1 CREATE TABLE `t1` (
2947
`c1` date DEFAULT NULL
2948
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2950
create table t1 as select concat(f1(cast('2001-01-02' as date))) as c1;
2951
show create table t1;
2953
t1 CREATE TABLE `t1` (
2954
`c1` varchar(10) DEFAULT NULL
2955
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2956
create view v1 as select concat(f1(cast('2001-01-02' as date))) as c1;
2957
show columns from v1;
2958
Field Type Null Key Default Extra
2959
c1 varchar(10) YES NULL
2964
# End of WL#2649 Number-to-string conversions
2967
# Bug#54668 User variable assignments get wrong type
2970
SELECT charset(@x), collation(@x);
2971
charset(@x) collation(@x)
2972
cp1251 cp1251_general_ci
2973
SET @x=old_password('a');
2974
SELECT charset(@x), collation(@x);
2975
charset(@x) collation(@x)
2976
cp1251 cp1251_general_ci
2977
SET @x=password('a');
2978
SELECT charset(@x), collation(@x);
2979
charset(@x) collation(@x)
2980
cp1251 cp1251_general_ci
2982
SELECT charset(@x), collation(@x);
2983
charset(@x) collation(@x)
2984
cp1251 cp1251_general_ci
2986
SELECT charset(@x), collation(@x);
2987
charset(@x) collation(@x)
2988
cp1251 cp1251_general_ci
2989
SET @x=astext(point(1,2));
2990
SELECT charset(@x), collation(@x);
2991
charset(@x) collation(@x)
2992
cp1251 cp1251_general_ci
2993
SET @x=aswkt(point(1,2));
2994
SELECT charset(@x), collation(@x);
2995
charset(@x) collation(@x)
2996
cp1251 cp1251_general_ci
2998
# Bug#54916 GROUP_CONCAT + IFNULL truncates output
3000
SELECT @@collation_connection;
3001
@@collation_connection
3003
CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM;
3004
INSERT INTO t1 VALUES (1234567);
3005
SELECT GROUP_CONCAT(IFNULL(a,'')) FROM t1;
3006
GROUP_CONCAT(IFNULL(a,''))
3008
SELECT GROUP_CONCAT(IF(a,a,'')) FROM t1;
3009
GROUP_CONCAT(IF(a,a,''))
3011
SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1;
3012
GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END)
3014
SELECT COALESCE(a,'') FROM t1 GROUP BY 1;
3015
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
3016
def COALESCE(a,'') 253 9 7 Y 0 31 51
3019
# All columns must be VARCHAR(9) with the same length:
3025
CASE WHEN a THEN a ELSE '' END,
3028
SHOW CREATE TABLE t2;
3030
t2 CREATE TABLE `t2` (
3031
`CONCAT(a)` varchar(9) CHARACTER SET cp1251 DEFAULT NULL,
3032
`IFNULL(a,'')` varchar(9) CHARACTER SET cp1251 NOT NULL DEFAULT '',
3033
`IF(a,a,'')` varchar(9) CHARACTER SET cp1251 DEFAULT NULL,
3034
`CASE WHEN a THEN a ELSE '' END` varchar(9) CHARACTER SET cp1251 DEFAULT NULL,
3035
`COALESCE(a,'')` varchar(9) CHARACTER SET cp1251 DEFAULT NULL
3036
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3038
CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1;
3039
SHOW CREATE TABLE t2;
3041
t2 CREATE TABLE `t2` (
3042
`CONCAT_WS(1,2,3)` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3043
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3045
CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1;
3046
SHOW CREATE TABLE t2;
3048
t2 CREATE TABLE `t2` (
3049
`INSERT(1133,3,0,22)` varchar(6) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3050
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3052
CREATE TABLE t2 AS SELECT LCASE(a) FROM t1;
3053
SHOW CREATE TABLE t2;
3055
t2 CREATE TABLE `t2` (
3056
`LCASE(a)` varchar(9) CHARACTER SET cp1251 DEFAULT NULL
3057
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3059
CREATE TABLE t2 AS SELECT UCASE(a) FROM t1;
3060
SHOW CREATE TABLE t2;
3062
t2 CREATE TABLE `t2` (
3063
`UCASE(a)` varchar(9) CHARACTER SET cp1251 DEFAULT NULL
3064
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3066
CREATE TABLE t2 AS SELECT REPEAT(1,2) FROM t1;
3067
SHOW CREATE TABLE t2;
3069
t2 CREATE TABLE `t2` (
3070
`REPEAT(1,2)` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3071
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3073
CREATE TABLE t2 AS SELECT LEFT(123,2) FROM t1;
3074
SHOW CREATE TABLE t2;
3076
t2 CREATE TABLE `t2` (
3077
`LEFT(123,2)` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3078
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3080
CREATE TABLE t2 AS SELECT RIGHT(123,2) FROM t1;
3081
SHOW CREATE TABLE t2;
3083
t2 CREATE TABLE `t2` (
3084
`RIGHT(123,2)` varchar(2) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3085
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3087
CREATE TABLE t2 AS SELECT LTRIM(123) FROM t1;
3088
SHOW CREATE TABLE t2;
3090
t2 CREATE TABLE `t2` (
3091
`LTRIM(123)` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3092
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3094
CREATE TABLE t2 AS SELECT RTRIM(123) FROM t1;
3095
SHOW CREATE TABLE t2;
3097
t2 CREATE TABLE `t2` (
3098
`RTRIM(123)` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3099
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3101
CREATE TABLE t2 AS SELECT ELT(1,111,222,333) FROM t1;
3102
SHOW CREATE TABLE t2;
3104
t2 CREATE TABLE `t2` (
3105
`ELT(1,111,222,333)` varchar(3) CHARACTER SET cp1251 DEFAULT NULL
3106
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3108
CREATE TABLE t2 AS SELECT REPLACE(111,2,3) FROM t1;
3109
SHOW CREATE TABLE t2;
3111
t2 CREATE TABLE `t2` (
3112
`REPLACE(111,2,3)` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3113
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3115
CREATE TABLE t2 AS SELECT SUBSTRING_INDEX(111,111,1) FROM t1;
3116
SHOW CREATE TABLE t2;
3118
t2 CREATE TABLE `t2` (
3119
`SUBSTRING_INDEX(111,111,1)` varchar(3) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3120
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3122
CREATE TABLE t2 AS SELECT MAKE_SET(111,222,3) FROM t1;
3123
SHOW CREATE TABLE t2;
3125
t2 CREATE TABLE `t2` (
3126
`MAKE_SET(111,222,3)` varchar(5) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3127
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3129
CREATE TABLE t2 AS SELECT SOUNDEX(1) FROM t1;
3130
SHOW CREATE TABLE t2;
3132
t2 CREATE TABLE `t2` (
3133
`SOUNDEX(1)` varchar(4) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3134
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3136
CREATE TABLE t2 AS SELECT EXPORT_SET(1,'Y','N','',8);
3137
SHOW CREATE TABLE t2;
3139
t2 CREATE TABLE `t2` (
3140
`EXPORT_SET(1,'Y','N','',8)` varchar(64) CHARACTER SET cp1251 NOT NULL DEFAULT ''
3141
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3148
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
3150
SELECT @@collation_connection;
3151
@@collation_connection
3154
id INT(11) DEFAULT NULL,
3155
date_column DATE DEFAULT NULL,
3157
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
3158
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
3159
id select_type table type possible_keys key key_len ref rows Extra
3160
1 SIMPLE t1 range date_column date_column 4 NULL 1 Using index condition
3161
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
3162
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
3163
id select_type table type possible_keys key key_len ref rows Extra
3164
1 SIMPLE t1 range date_column date_column 9 NULL 1 Using index condition
3167
# Bug #31384 DATE_ADD() and DATE_SUB() return binary data
3169
SELECT @@collation_connection, @@character_set_results;
3170
@@collation_connection @@character_set_results
3171
cp1251_general_ci cp1251
3174
DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
3175
DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
3176
DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
3177
DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
3178
SHOW CREATE TABLE t1;
3180
t1 CREATE TABLE `t1` (
3181
`field_str1` varchar(26) DEFAULT NULL,
3182
`field1_str2` varchar(26) DEFAULT NULL,
3183
`field_date` date DEFAULT NULL,
3184
`field_datetime` datetime DEFAULT NULL
3185
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3188
DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
3189
DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
3190
DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
3191
DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
3192
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
3193
def field_str1 254 26 10 Y 128 31 63
3194
def field1_str2 254 26 19 Y 128 31 63
3195
def field_date 10 10 10 Y 128 0 63
3196
def field_datetime 12 19 19 Y 128 0 63
3197
field_str1 field1_str2 field_date field_datetime
3198
2007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00
3200
HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
3201
HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2,
3202
HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
3203
HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
3204
field_str1 field1_str2 field_date field_datetime
3205
323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030
3207
# Bug#11926811 / Bug#60625 Illegal mix of collations
3209
SELECT @@collation_connection;
3210
@@collation_connection
3212
CREATE PROCEDURE p1()
3214
DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
3215
SELECT v_LastPaymentDate < NOW();
3216
EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
3218
EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
3221
v_LastPaymentDate < NOW()
3223
id select_type table type possible_keys key key_len ref rows filtered Extra
3224
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3226
Note 1003 select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
3227
id select_type table type possible_keys key key_len ref rows filtered Extra
3228
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3230
Note 1003 select concat(convert(v_LastPaymentDate@0 using cp1251),now()) AS `CONCAT(v_LastPaymentDate, NOW())`
3233
# Bug#52159 returning time type from function and empty left join causes debug assertion
3235
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
3236
CREATE TABLE t1 (b INT);
3237
INSERT INTO t1 VALUES (0);
3238
SELECT f1() FROM t1 LEFT JOIN (SELECT 1 AS a FROM t1 LIMIT 0) AS d ON 1 GROUP BY a;
3245
CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a));
3246
INSERT INTO t1 VALUES ();
3247
SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1;
3248
maketime(`a`,`a`,`a`)
3251
SET sql_mode=default;
3253
# Bug#60101 COALESCE with cp1251 tables causes [Err] 1267 - Illegal mix of collations
3255
CREATE TABLE t1 (test1 INT, test2 VARCHAR(255));
3256
SHOW CREATE TABLE t1;
3258
t1 CREATE TABLE `t1` (
3259
`test1` int(11) DEFAULT NULL,
3260
`test2` varchar(255) DEFAULT NULL
3261
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3262
SELECT COALESCE(IF(test1=1, 1, NULL), test2) FROM t1;
3263
COALESCE(IF(test1=1, 1, NULL), test2)
3264
SELECT COALESCE(IF(test1=1, NULL, 1), test2) FROM t1;
3265
COALESCE(IF(test1=1, NULL, 1), test2)