diff options
Diffstat (limited to 'mysql-test/main/ctype_cp1251.result')
| -rw-r--r-- | mysql-test/main/ctype_cp1251.result | 3424 |
1 files changed, 3424 insertions, 0 deletions
diff --git a/mysql-test/main/ctype_cp1251.result b/mysql-test/main/ctype_cp1251.result new file mode 100644 index 00000000000..2da53740619 --- /dev/null +++ b/mysql-test/main/ctype_cp1251.result @@ -0,0 +1,3424 @@ +drop table if exists t1; +SET NAMES cp1251; +create table t1 (a varchar(10) not null) character set cp1251; +insert into t1 values ("a"),("ab"),("abc"); +select * from t1; +a +a +ab +abc +select a, left(a,1) as b from t1; +a b +a a +ab a +abc a +select a, left(a,1) as b from t1 group by a; +a b +a a +ab a +abc a +SELECT DISTINCT RIGHT(a,1) from t1; +RIGHT(a,1) +a +b +c +drop table t1; +create table t1 (a char(3) binary, b binary(3)) character set cp1251; +insert into t1 values ('aaa','bbb'),('AAA','BBB'); +select upper(a),upper(b) from t1; +upper(a) upper(b) +AAA bbb +AAA BBB +select lower(a),lower(b) from t1; +lower(a) lower(b) +aaa bbb +aaa BBB +select * from t1 where upper(a)='AAA'; +a b +aaa bbb +AAA BBB +select * from t1 where lower(a)='aaa'; +a b +aaa bbb +AAA BBB +select * from t1 where upper(b)='BBB'; +a b +AAA BBB +select * from t1 where lower(b)='bbb'; +a b +aaa bbb +select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1; +charset(a) charset(b) charset(binary 'ccc') +cp1251 binary binary +select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1; +collation(a) collation(b) collation(binary 'ccc') +cp1251_bin binary binary +drop table t1; +create table t1 ( +a varchar(16) character set cp1251 collate cp1251_bin not null, +b int(10) default null, +primary key(a) +) charset=cp1251; +insert into t1 (a) values ('air'), +('we'),('g'),('we_toshko'), ('s0urce'),('we_ivo'),('we_iliyan'), +('we_martin'),('vw_grado'),('vw_vasko'),('tn_vili'),('tn_kalina'), +('tn_fakira'),('vw_silvia'),('vw_starshi'),('vw_geo'),('vw_b0x1'); +select * from t1 where a like 'we_%'; +a b +we_iliyan NULL +we_ivo NULL +we_martin NULL +we_toshko NULL +drop table t1; +CREATE TABLE t1 ( +e1 enum('ÿáúëêà'), +e2 enum('ìëÿêî') +) ENGINE=MYISAM character set cp1251; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `e1` enum('ÿáúëêà') DEFAULT NULL, + `e2` enum('ìëÿêî') DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=cp1251 +DROP TABLE t1; +# +# Start of 5.1 tests +# +SELECT @@collation_connection; +@@collation_connection +cp1251_general_ci +CREATE TABLE t1 AS SELECT ' ' AS a LIMIT 0; +INSERT INTO t1 VALUES (0x00),(0x01),(0x02),(0x03),(0x04),(0x05),(0x06),(0x07); +INSERT INTO t1 VALUES (0x08),(0x09),(0x0A),(0x0B),(0x0C),(0x0D),(0x0E),(0x0F); +INSERT INTO t1 VALUES (0x10),(0x11),(0x12),(0x13),(0x14),(0x15),(0x16),(0x17); +INSERT INTO t1 VALUES (0x18),(0x19),(0x1A),(0x1B),(0x1C),(0x1D),(0x1E),(0x1F); +INSERT INTO t1 VALUES (0x20),(0x21),(0x22),(0x23),(0x24),(0x25),(0x26),(0x27); +INSERT INTO t1 VALUES (0x28),(0x29),(0x2A),(0x2B),(0x2C),(0x2D),(0x2E),(0x2F); +INSERT INTO t1 VALUES (0x30),(0x31),(0x32),(0x33),(0x34),(0x35),(0x36),(0x37); +INSERT INTO t1 VALUES (0x38),(0x39),(0x3A),(0x3B),(0x3C),(0x3D),(0x3E),(0x3F); +INSERT INTO t1 VALUES (0x40),(0x41),(0x42),(0x43),(0x44),(0x45),(0x46),(0x47); +INSERT INTO t1 VALUES (0x48),(0x49),(0x4A),(0x4B),(0x4C),(0x4D),(0x4E),(0x4F); +INSERT INTO t1 VALUES (0x50),(0x51),(0x52),(0x53),(0x54),(0x55),(0x56),(0x57); +INSERT INTO t1 VALUES (0x58),(0x59),(0x5A),(0x5B),(0x5C),(0x5D),(0x5E),(0x5F); +INSERT INTO t1 VALUES (0x60),(0x61),(0x62),(0x63),(0x64),(0x65),(0x66),(0x67); +INSERT INTO t1 VALUES (0x68),(0x69),(0x6A),(0x6B),(0x6C),(0x6D),(0x6E),(0x6F); +INSERT INTO t1 VALUES (0x70),(0x71),(0x72),(0x73),(0x74),(0x75),(0x76),(0x77); +INSERT INTO t1 VALUES (0x78),(0x79),(0x7A),(0x7B),(0x7C),(0x7D),(0x7E),(0x7F); +INSERT INTO t1 VALUES (0x80),(0x81),(0x82),(0x83),(0x84),(0x85),(0x86),(0x87); +INSERT INTO t1 VALUES (0x88),(0x89),(0x8A),(0x8B),(0x8C),(0x8D),(0x8E),(0x8F); +INSERT INTO t1 VALUES (0x90),(0x91),(0x92),(0x93),(0x94),(0x95),(0x96),(0x97); +INSERT INTO t1 VALUES (0x98),(0x99),(0x9A),(0x9B),(0x9C),(0x9D),(0x9E),(0x9F); +INSERT INTO t1 VALUES (0xA0),(0xA1),(0xA2),(0xA3),(0xA4),(0xA5),(0xA6),(0xA7); +INSERT INTO t1 VALUES (0xA8),(0xA9),(0xAA),(0xAB),(0xAC),(0xAD),(0xAE),(0xAF); +INSERT INTO t1 VALUES (0xB0),(0xB1),(0xB2),(0xB3),(0xB4),(0xB5),(0xB6),(0xB7); +INSERT INTO t1 VALUES (0xB8),(0xB9),(0xBA),(0xBB),(0xBC),(0xBD),(0xBE),(0xBF); +INSERT INTO t1 VALUES (0xC0),(0xC1),(0xC2),(0xC3),(0xC4),(0xC5),(0xC6),(0xC7); +INSERT INTO t1 VALUES (0xC8),(0xC9),(0xCA),(0xCB),(0xCC),(0xCD),(0xCE),(0xCF); +INSERT INTO t1 VALUES (0xD0),(0xD1),(0xD2),(0xD3),(0xD4),(0xD5),(0xD6),(0xD7); +INSERT INTO t1 VALUES (0xD8),(0xD9),(0xDA),(0xDB),(0xDC),(0xDD),(0xDE),(0xDF); +INSERT INTO t1 VALUES (0xE0),(0xE1),(0xE2),(0xE3),(0xE4),(0xE5),(0xE6),(0xE7); +INSERT INTO t1 VALUES (0xE8),(0xE9),(0xEA),(0xEB),(0xEC),(0xED),(0xEE),(0xEF); +INSERT INTO t1 VALUES (0xF0),(0xF1),(0xF2),(0xF3),(0xF4),(0xF5),(0xF6),(0xF7); +INSERT INTO t1 VALUES (0xF8),(0xF9),(0xFA),(0xFB),(0xFC),(0xFD),(0xFE),(0xFF); +SELECT +HEX(a) AS chr, +HEX(LOWER(a)) AS upper, +HEX(LOWER(a)) AS lower, +HEX(@utf8:=CONVERT(a USING utf8)) AS utf8, +HEX(@roundtrip:=CAST(@utf8 AS CHAR)) AS roundtrip, +if(a=BINARY @roundtrip,'','Round trip unsafe') AS issafe +FROM t1 ORDER BY chr; +chr upper lower utf8 roundtrip issafe +00 00 00 00 00 +01 01 01 01 01 +02 02 02 02 02 +03 03 03 03 03 +04 04 04 04 04 +05 05 05 05 05 +06 06 06 06 06 +07 07 07 07 07 +08 08 08 08 08 +09 09 09 09 09 +0A 0A 0A 0A 0A +0B 0B 0B 0B 0B +0C 0C 0C 0C 0C +0D 0D 0D 0D 0D +0E 0E 0E 0E 0E +0F 0F 0F 0F 0F +10 10 10 10 10 +11 11 11 11 11 +12 12 12 12 12 +13 13 13 13 13 +14 14 14 14 14 +15 15 15 15 15 +16 16 16 16 16 +17 17 17 17 17 +18 18 18 18 18 +19 19 19 19 19 +1A 1A 1A 1A 1A +1B 1B 1B 1B 1B +1C 1C 1C 1C 1C +1D 1D 1D 1D 1D +1E 1E 1E 1E 1E +1F 1F 1F 1F 1F +20 20 20 20 20 +21 21 21 21 21 +22 22 22 22 22 +23 23 23 23 23 +24 24 24 24 24 +25 25 25 25 25 +26 26 26 26 26 +27 27 27 27 27 +28 28 28 28 28 +29 29 29 29 29 +2A 2A 2A 2A 2A +2B 2B 2B 2B 2B +2C 2C 2C 2C 2C +2D 2D 2D 2D 2D +2E 2E 2E 2E 2E +2F 2F 2F 2F 2F +30 30 30 30 30 +31 31 31 31 31 +32 32 32 32 32 +33 33 33 33 33 +34 34 34 34 34 +35 35 35 35 35 +36 36 36 36 36 +37 37 37 37 37 +38 38 38 38 38 +39 39 39 39 39 +3A 3A 3A 3A 3A +3B 3B 3B 3B 3B +3C 3C 3C 3C 3C +3D 3D 3D 3D 3D +3E 3E 3E 3E 3E +3F 3F 3F 3F 3F +40 40 40 40 40 +41 61 61 41 41 +42 62 62 42 42 +43 63 63 43 43 +44 64 64 44 44 +45 65 65 45 45 +46 66 66 46 46 +47 67 67 47 47 +48 68 68 48 48 +49 69 69 49 49 +4A 6A 6A 4A 4A +4B 6B 6B 4B 4B +4C 6C 6C 4C 4C +4D 6D 6D 4D 4D +4E 6E 6E 4E 4E +4F 6F 6F 4F 4F +50 70 70 50 50 +51 71 71 51 51 +52 72 72 52 52 +53 73 73 53 53 +54 74 74 54 54 +55 75 75 55 55 +56 76 76 56 56 +57 77 77 57 57 +58 78 78 58 58 +59 79 79 59 59 +5A 7A 7A 5A 5A +5B 5B 5B 5B 5B +5C 5C 5C 5C 5C +5D 5D 5D 5D 5D +5E 5E 5E 5E 5E +5F 5F 5F 5F 5F +60 60 60 60 60 +61 61 61 61 61 +62 62 62 62 62 +63 63 63 63 63 +64 64 64 64 64 +65 65 65 65 65 +66 66 66 66 66 +67 67 67 67 67 +68 68 68 68 68 +69 69 69 69 69 +6A 6A 6A 6A 6A +6B 6B 6B 6B 6B +6C 6C 6C 6C 6C +6D 6D 6D 6D 6D +6E 6E 6E 6E 6E +6F 6F 6F 6F 6F +70 70 70 70 70 +71 71 71 71 71 +72 72 72 72 72 +73 73 73 73 73 +74 74 74 74 74 +75 75 75 75 75 +76 76 76 76 76 +77 77 77 77 77 +78 78 78 78 78 +79 79 79 79 79 +7A 7A 7A 7A 7A +7B 7B 7B 7B 7B +7C 7C 7C 7C 7C +7D 7D 7D 7D 7D +7E 7E 7E 7E 7E +7F 7F 7F 7F 7F +80 90 90 D082 80 +81 83 83 D083 81 +82 82 82 E2809A 82 +83 83 83 D193 83 +84 84 84 E2809E 84 +85 85 85 E280A6 85 +86 86 86 E280A0 86 +87 87 87 E280A1 87 +88 88 88 E282AC 88 +89 89 89 E280B0 89 +8A 9A 9A D089 8A +8B 8B 8B E280B9 8B +8C 9C 9C D08A 8C +8D 9D 9D D08C 8D +8E 9E 9E D08B 8E +8F 9F 9F D08F 8F +90 90 90 D192 90 +91 91 91 E28098 91 +92 92 92 E28099 92 +93 93 93 E2809C 93 +94 94 94 E2809D 94 +95 95 95 E280A2 95 +96 96 96 E28093 96 +97 97 97 E28094 97 +98 98 98 3F 3F Round trip unsafe +99 99 99 E284A2 99 +9A 9A 9A D199 9A +9B 9B 9B E280BA 9B +9C 9C 9C D19A 9C +9D 9D 9D D19C 9D +9E 9E 9E D19B 9E +9F 9F 9F D19F 9F +A0 A0 A0 C2A0 A0 +A1 A2 A2 D08E A1 +A2 A2 A2 D19E A2 +A3 BC BC D088 A3 +A4 A4 A4 C2A4 A4 +A5 B4 B4 D290 A5 +A6 A6 A6 C2A6 A6 +A7 A7 A7 C2A7 A7 +A8 B8 B8 D081 A8 +A9 A9 A9 C2A9 A9 +AA BA BA D084 AA +AB AB AB C2AB AB +AC AC AC C2AC AC +AD AD AD C2AD AD +AE AE AE C2AE AE +AF BF BF D087 AF +B0 B0 B0 C2B0 B0 +B1 B1 B1 C2B1 B1 +B2 B3 B3 D086 B2 +B3 B3 B3 D196 B3 +B4 B4 B4 D291 B4 +B5 B5 B5 C2B5 B5 +B6 B6 B6 C2B6 B6 +B7 B7 B7 C2B7 B7 +B8 B8 B8 D191 B8 +B9 B9 B9 E28496 B9 +BA BA BA D194 BA +BB BB BB C2BB BB +BC BC BC D198 BC +BD BE BE D085 BD +BE BE BE D195 BE +BF BF BF D197 BF +C0 E0 E0 D090 C0 +C1 E1 E1 D091 C1 +C2 E2 E2 D092 C2 +C3 E3 E3 D093 C3 +C4 E4 E4 D094 C4 +C5 E5 E5 D095 C5 +C6 E6 E6 D096 C6 +C7 E7 E7 D097 C7 +C8 E8 E8 D098 C8 +C9 E9 E9 D099 C9 +CA EA EA D09A CA +CB EB EB D09B CB +CC EC EC D09C CC +CD ED ED D09D CD +CE EE EE D09E CE +CF EF EF D09F CF +D0 F0 F0 D0A0 D0 +D1 F1 F1 D0A1 D1 +D2 F2 F2 D0A2 D2 +D3 F3 F3 D0A3 D3 +D4 F4 F4 D0A4 D4 +D5 F5 F5 D0A5 D5 +D6 F6 F6 D0A6 D6 +D7 F7 F7 D0A7 D7 +D8 F8 F8 D0A8 D8 +D9 F9 F9 D0A9 D9 +DA FA FA D0AA DA +DB FB FB D0AB DB +DC FC FC D0AC DC +DD FD FD D0AD DD +DE FE FE D0AE DE +DF FF FF D0AF DF +E0 E0 E0 D0B0 E0 +E1 E1 E1 D0B1 E1 +E2 E2 E2 D0B2 E2 +E3 E3 E3 D0B3 E3 +E4 E4 E4 D0B4 E4 +E5 E5 E5 D0B5 E5 +E6 E6 E6 D0B6 E6 +E7 E7 E7 D0B7 E7 +E8 E8 E8 D0B8 E8 +E9 E9 E9 D0B9 E9 +EA EA EA D0BA EA +EB EB EB D0BB EB +EC EC EC D0BC EC +ED ED ED D0BD ED +EE EE EE D0BE EE +EF EF EF D0BF EF +F0 F0 F0 D180 F0 +F1 F1 F1 D181 F1 +F2 F2 F2 D182 F2 +F3 F3 F3 D183 F3 +F4 F4 F4 D184 F4 +F5 F5 F5 D185 F5 +F6 F6 F6 D186 F6 +F7 F7 F7 D187 F7 +F8 F8 F8 D188 F8 +F9 F9 F9 D189 F9 +FA FA FA D18A FA +FB FB FB D18B FB +FC FC FC D18C FC +FD FD FD D18D FD +FE FE FE D18E FE +FF FF FF D18F FF +Warnings: +Warning 1977 Cannot convert 'cp1251' character 0x98 to 'utf8' +DROP TABLE t1; +SELECT strcmp('a','a '), strcmp('a ','a'); +strcmp('a','a ') strcmp('a ','a') +0 0 +SELECT strcmp('a\0','a' ), strcmp('a','a\0'); +strcmp('a\0','a' ) strcmp('a','a\0') +-1 1 +SELECT strcmp('a\0','a '), strcmp('a ','a\0'); +strcmp('a\0','a ') strcmp('a ','a\0') +-1 1 +SELECT strcmp('a\t','a' ), strcmp('a', 'a\t'); +strcmp('a\t','a' ) strcmp('a', 'a\t') +-1 1 +SELECT strcmp('a\t','a '), strcmp('a ', 'a\t'); +strcmp('a\t','a ') strcmp('a ', 'a\t') +-1 1 +set global LC_TIME_NAMES=convert((-8388608) using cp1251); +ERROR HY000: Unknown locale: '-8388608' +# +# End of 5.1 tests +# +# +# Start of 5.5 tests +# +SET TIME_ZONE = _latin1 '+03:00'; +# +# Start of WL#2649 Number-to-string conversions +# +select hex(concat(1)); +hex(concat(1)) +31 +create table t1 as select concat(1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select hex(c1) from t1; +hex(c1) +31 +drop table t1; +select hex(concat(18446744073709551615)); +hex(concat(18446744073709551615)) +3138343436373434303733373039353531363135 +create table t1 as select concat(18446744073709551615) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(20) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select hex(c1) from t1; +hex(c1) +3138343436373434303733373039353531363135 +drop table t1; +select hex(concat(1.1)); +hex(concat(1.1)) +312E31 +create table t1 as select concat(1.1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select hex(c1) from t1; +hex(c1) +312E31 +drop table t1; +select hex(concat('a', 1+2)), charset(concat(1+2)); +hex(concat('a', 1+2)) charset(concat(1+2)) +6133 cp1251 +create table t1 as select concat(1+2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(1-2)); +hex(concat(1-2)) +2D31 +create table t1 as select concat(1-2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(1*2)); +hex(concat(1*2)) +32 +create table t1 as select concat(1*2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(1/2)); +hex(concat(1/2)) +302E35303030 +create table t1 as select concat(1/2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(7) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(1 div 2)); +hex(concat(1 div 2)) +30 +create table t1 as select concat(1 div 2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(1 % 2)); +hex(concat(1 % 2)) +31 +create table t1 as select concat(1 % 2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(-1)); +hex(concat(-1)) +2D31 +create table t1 as select concat(-1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(-(1+2))); +hex(concat(-(1+2))) +2D33 +create table t1 as select concat(-(1+2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(1|2)); +hex(concat(1|2)) +33 +create table t1 as select concat(1|2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(1&2)); +hex(concat(1&2)) +30 +create table t1 as select concat(1&2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(bit_count(12))); +hex(concat(bit_count(12))) +32 +create table t1 as select concat(bit_count(12)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(2<<1)); +hex(concat(2<<1)) +34 +create table t1 as select concat(2<<1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(2>>1)); +hex(concat(2>>1)) +31 +create table t1 as select concat(2>>1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(~0)); +hex(concat(~0)) +3138343436373434303733373039353531363135 +create table t1 as select concat(~0) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(3^2)); +hex(concat(3^2)) +31 +create table t1 as select concat(3^2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(abs(-2))); +hex(concat(abs(-2))) +32 +create table t1 as select concat(abs(-2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(exp(2)),1)); +hex(left(concat(exp(2)),1)) +37 +create table t1 as select concat(exp(2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(log(2)),1)); +hex(left(concat(log(2)),1)) +30 +create table t1 as select concat(log(2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(log2(2)),1)); +hex(left(concat(log2(2)),1)) +31 +create table t1 as select concat(log2(2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(log10(2)),1)); +hex(left(concat(log10(2)),1)) +30 +create table t1 as select concat(log10(2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(sqrt(2)),1)); +hex(left(concat(sqrt(2)),1)) +31 +create table t1 as select concat(sqrt(2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(pow(2,2)),1)); +hex(left(concat(pow(2,2)),1)) +34 +create table t1 as select concat(pow(2,2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(acos(0.5)),1)); +hex(left(concat(acos(0.5)),1)) +31 +create table t1 as select concat(acos(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(asin(0.5)),1)); +hex(left(concat(asin(0.5)),1)) +30 +create table t1 as select concat(asin(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(atan(0.5)),1)); +hex(left(concat(atan(0.5)),1)) +30 +create table t1 as select concat(atan(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(cos(0.5)),1)); +hex(left(concat(cos(0.5)),1)) +30 +create table t1 as select concat(cos(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(sin(0.5)),1)); +hex(left(concat(sin(0.5)),1)) +30 +create table t1 as select concat(sin(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(tan(0.5)),1)); +hex(left(concat(tan(0.5)),1)) +30 +create table t1 as select concat(tan(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(degrees(0))); +hex(concat(degrees(0))) +30 +create table t1 as select concat(degrees(0)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(radians(0))); +hex(concat(radians(0))) +30 +create table t1 as select concat(radians(0)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(ceiling(0.5))); +hex(concat(ceiling(0.5))) +31 +create table t1 as select concat(ceiling(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(floor(0.5))); +hex(concat(floor(0.5))) +30 +create table t1 as select concat(floor(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(round(0.5))); +hex(concat(round(0.5))) +31 +create table t1 as select concat(round(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(sign(0.5))); +hex(concat(sign(0.5))) +31 +create table t1 as select concat(sign(0.5)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(rand()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(length('a'))); +hex(concat(length('a'))) +31 +create table t1 as select concat(length('a')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(char_length('a'))); +hex(concat(char_length('a'))) +31 +create table t1 as select concat(char_length('a')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(bit_length('a'))); +hex(concat(bit_length('a'))) +38 +create table t1 as select concat(bit_length('a')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(11) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(coercibility('a'))); +hex(concat(coercibility('a'))) +34 +create table t1 as select concat(coercibility('a')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(locate('a','a'))); +hex(concat(locate('a','a'))) +31 +create table t1 as select concat(locate('a','a')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(11) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(field('c','a','b','c'))); +hex(concat(field('c','a','b','c'))) +33 +create table t1 as select concat(field('c','a','b','c')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(ascii(61))); +hex(concat(ascii(61))) +3534 +create table t1 as select concat(ascii(61)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(ord(61))); +hex(concat(ord(61))) +3534 +create table t1 as select concat(ord(61)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(7) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(find_in_set('b','a,b,c,d'))); +hex(concat(find_in_set('b','a,b,c,d'))) +32 +create table t1 as select concat(find_in_set('b','a,b,c,d')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select md5('a'), hex(md5('a')); +md5('a') hex(md5('a')) +0cc175b9c0f1b6a831c399e269772661 3063633137356239633066316236613833316333393965323639373732363631 +create table t1 as select md5('a') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(32) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select old_password('a'), hex(old_password('a')); +old_password('a') hex(old_password('a')) +60671c896665c3fa 36303637316338393636363563336661 +create table t1 as select old_password('a') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(16) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select password('a'), hex(password('a')); +password('a') hex(password('a')) +*667F407DE7C6AD07358FA38DAED7828A72014B4E 2A36363746343037444537433641443037333538464133384441454437383238413732303134423445 +create table t1 as select password('a') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(41) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select sha('a'), hex(sha('a')); +sha('a') hex(sha('a')) +86f7e437faa5a7fce15d1ddcb9eaeaea377667b8 38366637653433376661613561376663653135643164646362396561656165613337373636376238 +create table t1 as select sha('a') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(40) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select sha1('a'), hex(sha1('a')); +sha1('a') hex(sha1('a')) +86f7e437faa5a7fce15d1ddcb9eaeaea377667b8 38366637653433376661613561376663653135643164646362396561656165613337373636376238 +create table t1 as select sha1('a') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(40) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(cast('-1' as signed))); +hex(concat(cast('-1' as signed))) +2D31 +create table t1 as select concat(cast('-1' as signed)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(cast('1' as unsigned))); +hex(concat(cast('1' as unsigned))) +31 +create table t1 as select concat(cast('1' as unsigned)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(cast(1/2 as decimal(5,5)))); +hex(concat(cast(1/2 as decimal(5,5)))) +302E3530303030 +create table t1 as select concat(cast(1/2 as decimal(5,5))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(7) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(cast('2001-01-02 03:04:05' as date))); +hex(concat(cast('2001-01-02 03:04:05' as date))) +323030312D30312D3032 +create table t1 as select concat(cast('2001-01-02 03:04:05' as date)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t1; +c1 +2001-01-02 +drop table t1; +select hex(concat(cast('2001-01-02 03:04:05' as time))); +hex(concat(cast('2001-01-02 03:04:05' as time))) +30333A30343A3035 +create table t1 as select concat(cast('2001-01-02 03:04:05' as time)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t1; +c1 +03:04:05 +drop table t1; +select hex(concat(cast('2001-01-02' as datetime))); +hex(concat(cast('2001-01-02' as datetime))) +323030312D30312D30322030303A30303A3030 +create table t1 as select concat(cast('2001-01-02' as datetime)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t1; +c1 +2001-01-02 00:00:00 +drop table t1; +select hex(concat(least(1,2))); +hex(concat(least(1,2))) +31 +create table t1 as select concat(least(1,2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(greatest(1,2))); +hex(concat(greatest(1,2))) +32 +create table t1 as select concat(greatest(1,2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(case when 11 then 22 else 33 end)); +hex(concat(case when 11 then 22 else 33 end)) +3232 +create table t1 as select concat(case when 11 then 22 else 33 end) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(coalesce(1,2))); +hex(concat(coalesce(1,2))) +31 +create table t1 as select concat(coalesce(1,2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat_ws(1,2,3)); +hex(concat_ws(1,2,3)) +323133 +create table t1 as select concat_ws(1,2,3) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(group_concat(1,2,3)); +hex(group_concat(1,2,3)) +313233 +create table t1 as select group_concat(1,2,3) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` mediumtext CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select 1 as c1 union select 'a'; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select hex(c1) from t1 order by c1; +hex(c1) +31 +61 +drop table t1; +create table t1 as select concat(last_insert_id()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(benchmark(0,0))); +hex(concat(benchmark(0,0))) +30 +create table t1 as select concat(benchmark(0,0)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(sleep(0))); +hex(concat(sleep(0))) +30 +create table t1 as select concat(sleep(0)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(is_free_lock('xxxx'))); +hex(concat(is_free_lock('xxxx'))) +31 +create table t1 as select concat(is_free_lock('xxxx')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(is_used_lock('a')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(release_lock('a')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(crc32(''))); +hex(concat(crc32(''))) +30 +create table t1 as select concat(crc32('')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(uncompressed_length(''))); +hex(concat(uncompressed_length(''))) +30 +create table t1 as select concat(uncompressed_length('')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(connection_id()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(inet_aton('127.1.1.1'))); +hex(concat(inet_aton('127.1.1.1'))) +32313330373732323235 +create table t1 as select concat(inet_aton('127.1.1.1')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(inet_ntoa(2130772225))); +hex(concat(inet_ntoa(2130772225))) +3132372E312E312E31 +create table t1 as select concat(inet_ntoa(2130772225)) as c1; +select * from t1; +c1 +127.1.1.1 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(31) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select 1; +1 +1 +select hex(concat(row_count())); +hex(concat(row_count())) +2D31 +create table t1 as select concat(row_count()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(found_rows())); +hex(concat(found_rows())) +30 +create table t1 as select concat(found_rows()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(uuid_short()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(uuid()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(36) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select coercibility(uuid()), coercibility(cast('a' as char character set latin1)); +coercibility(uuid()) coercibility(cast('a' as char character set latin1)) +4 2 +select charset(concat(uuid(), cast('a' as char character set latin1))); +charset(concat(uuid(), cast('a' as char character set latin1))) +latin1 +create table t1 as select concat(uuid(), cast('a' as char character set latin1)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(37) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(master_pos_wait('non-existent',0,2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(@a1:=1)); +hex(concat(@a1:=1)) +31 +create table t1 as select concat(@a2:=2) as c1, @a3:=3 as c2; +select hex(c1) from t1; +hex(c1) +32 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL, + `c2` int(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +set @a2=1; +select hex(concat(@a2)); +hex(concat(@a2)) +31 +create table t1 as select concat(@a2) as c1, @a2 as c2; +select hex(c1) from t1; +hex(c1) +31 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(20) CHARACTER SET cp1251 DEFAULT NULL, + `c2` bigint(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(@a1:=sqrt(1))); +hex(concat(@a1:=sqrt(1))) +31 +create table t1 as select concat(@a2:=sqrt(1)) as c1, @a3:=sqrt(1) as c2; +select hex(c1) from t1; +hex(c1) +31 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL, + `c2` double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +set @a2=sqrt(1); +select hex(concat(@a2)); +hex(concat(@a2)) +31 +create table t1 as select concat(@a2) as c1, @a2 as c2; +select hex(c1) from t1; +hex(c1) +31 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL, + `c2` double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(@a1:=1.1)); +hex(concat(@a1:=1.1)) +312E31 +create table t1 as select concat(@a2:=1.1) as c1, @a3:=1.1 as c2; +select hex(c1) from t1; +hex(c1) +312E31 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL, + `c2` decimal(2,1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +set @a2=1.1; +select hex(concat(@a2)); +hex(concat(@a2)) +312E31 +create table t1 as select concat(@a2) as c1, @a2 as c2; +select hex(c1) from t1; +hex(c1) +312E31 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(83) CHARACTER SET cp1251 DEFAULT NULL, + `c2` decimal(65,38) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(@@ft_max_word_len)); +hex(concat(@@ft_max_word_len)) +3834 +create table t1 as select concat(@@ft_max_word_len) as c1; +select hex(c1) from t1; +hex(c1) +3834 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat('a'='a' IS TRUE)); +hex(concat('a'='a' IS TRUE)) +31 +create table t1 as select concat('a'='a' IS TRUE) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat('a'='a' IS NOT TRUE)); +hex(concat('a'='a' IS NOT TRUE)) +30 +create table t1 as select concat('a'='a' IS NOT TRUE) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(NOT 'a'='a')); +hex(concat(NOT 'a'='a')) +30 +create table t1 as select concat(NOT 'a'='a') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat('a' IS NULL)); +hex(concat('a' IS NULL)) +30 +create table t1 as select concat('a' IS NULL) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat('a' IS NOT NULL)); +hex(concat('a' IS NOT NULL)) +31 +create table t1 as select concat('a' IS NOT NULL) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat('a' rlike 'a')); +hex(concat('a' rlike 'a')) +31 +create table t1 as select concat('a' IS NOT NULL) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(strcmp('a','b'))); +hex(concat(strcmp('a','b'))) +2D31 +create table t1 as select concat(strcmp('a','b')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat('a' like 'a')); +hex(concat('a' like 'a')) +31 +create table t1 as select concat('a' like 'b') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat('a' between 'b' and 'c')); +hex(concat('a' between 'b' and 'c')) +30 +create table t1 as select concat('a' between 'b' and 'c') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat('a' in ('a','b'))); +hex(concat('a' in ('a','b'))) +31 +create table t1 as select concat('a' in ('a','b')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(interval(23, 1, 15, 17, 30, 44, 200))); +hex(concat(interval(23, 1, 15, 17, 30, 44, 200))) +33 +create table t1 as select concat(interval(23, 1, 15, 17, 30, 44, 200)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (a varchar(10), fulltext key(a)); +insert into t1 values ('a'); +select hex(concat(match (a) against ('a'))) from t1; +hex(concat(match (a) against ('a'))) +30 +create table t2 as select concat(match (a) against ('a')) as a from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +select hex(ifnull(1,'a')); +hex(ifnull(1,'a')) +31 +create table t1 as select ifnull(1,'a') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(ifnull(1,1))); +hex(concat(ifnull(1,1))) +31 +create table t1 as select concat(ifnull(1,1)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(ifnull(1.1,1.1))); +hex(concat(ifnull(1.1,1.1))) +312E31 +create table t1 as select concat(ifnull(1.1,1.1)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(if(1,'b',1)); +hex(if(1,'b',1)) +62 +create table t1 as select if(1,'b',1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(if(1,1,'b')); +hex(if(1,1,'b')) +31 +create table t1 as select if(1,1,'b') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(if(1,1,1))); +hex(concat(if(1,1,1))) +31 +create table t1 as select concat(if(1,1,1)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(nullif(1,2))); +hex(concat(nullif(1,2))) +31 +create table t1 as select concat(nullif(1,2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)')))); +hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)')))) +31 +create table t1 as select concat(Dimension(GeomFromText('LINSTRING(0 0,10 10)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)')))); +hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)')))) +32 +create table t1 as select concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)')))); +hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)')))) +32 +create table t1 as select concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)')))); +hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)')))) +30 +create table t1 as select concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +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))')))); +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))')))) +31 +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; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(IsEmpty(GeomFromText('POINT(1 1)')))); +hex(concat(IsEmpty(GeomFromText('POINT(1 1)')))) +30 +create table t1 as select concat(IsEmpty(GeomFromText('Point(1 1)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(IsSimple(GeomFromText('POINT(1 1)')))); +hex(concat(IsSimple(GeomFromText('POINT(1 1)')))) +31 +create table t1 as select concat(IsSimple(GeomFromText('Point(1 1)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)')))); +hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)')))) +30 +create table t1 as select concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)')))); +hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)')))) +31 +create table t1 as select concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))) as c1; +drop table t1; +select hex(concat(x(GeomFromText('Point(1 2)')))); +hex(concat(x(GeomFromText('Point(1 2)')))) +31 +create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(y(GeomFromText('Point(1 2)')))); +hex(concat(y(GeomFromText('Point(1 2)')))) +32 +create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(GLength(GeomFromText('LineString(1 2,2 2)')))); +hex(concat(GLength(GeomFromText('LineString(1 2,2 2)')))) +31 +create table t1 as select concat(GLength(GeomFromText('LineString(1 2, 2 2)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))')))); +hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))')))) +31 +create table t1 as select concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(23) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(GeometryType(GeomFromText('Point(1 2)')))); +hex(concat(GeometryType(GeomFromText('Point(1 2)')))) +504F494E54 +create table t1 as select concat(GeometryType(GeomFromText('Point(1 2)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(20) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(AsText(GeomFromText('Point(1 2)')))); +hex(concat(AsText(GeomFromText('Point(1 2)')))) +504F494E542831203229 +create table t1 as select concat(AsText(GeomFromText('Point(1 2)'))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` longtext CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(period_add(200902, 2))); +hex(concat(period_add(200902, 2))) +323030393034 +create table t1 as select concat(period_add(200902, 2)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(6) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(period_diff(200902, 200802))); +hex(concat(period_diff(200902, 200802))) +3132 +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +create table t1 as select concat(period_add(200902, 200802)) as c1; +Warnings: +Warning 1265 Data truncated for column 'c1' at row 1 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(6) CHARACTER SET cp1251 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(to_days(20090224))); +hex(concat(to_days(20090224))) +373333383237 +create table t1 as select concat(to_days(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(6) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(dayofmonth(20090224))); +hex(concat(dayofmonth(20090224))) +3234 +create table t1 as select concat(dayofmonth(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(dayofyear(20090224))); +hex(concat(dayofyear(20090224))) +3535 +create table t1 as select concat(dayofyear(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(hour('10:11:12'))); +hex(concat(hour('10:11:12'))) +3130 +create table t1 as select concat(hour('10:11:12')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(minute('10:11:12'))); +hex(concat(minute('10:11:12'))) +3131 +create table t1 as select concat(minute('10:11:12')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(second('10:11:12'))); +hex(concat(second('10:11:12'))) +3132 +create table t1 as select concat(second('10:11:12')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(quarter(20090224))); +hex(concat(quarter(20090224))) +31 +create table t1 as select concat(quarter(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(week(20090224))); +hex(concat(week(20090224))) +38 +create table t1 as select concat(week(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(yearweek(20090224))); +hex(concat(yearweek(20090224))) +323030393038 +create table t1 as select concat(yearweek(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(6) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(year(20090224))); +hex(concat(year(20090224))) +32303039 +create table t1 as select concat(year(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(weekday(20090224))); +hex(concat(weekday(20090224))) +31 +create table t1 as select concat(weekday(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(dayofweek(20090224))); +hex(concat(dayofweek(20090224))) +33 +create table t1 as select concat(dayofweek(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(unix_timestamp(20090224))); +hex(concat(unix_timestamp(20090224))) +31323335343232383030 +create table t1 as select concat(unix_timestamp(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(time_to_sec('10:11:12'))); +hex(concat(time_to_sec('10:11:12'))) +3336363732 +create table t1 as select concat(time_to_sec('10:11:12')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(extract(year from 20090702))); +hex(concat(extract(year from 20090702))) +32303039 +create table t1 as select concat(extract(year from 20090702)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(microsecond('12:00:00.123456'))); +hex(concat(microsecond('12:00:00.123456'))) +313233343536 +create table t1 as select concat(microsecond('12:00:00.123456')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(6) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(month(20090224))); +hex(concat(month(20090224))) +32 +create table t1 as select concat(month(20090224)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(last_day('2003-02-05')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select c1, hex(c1) from t1; +c1 hex(c1) +2003-02-28 323030332D30322D3238 +drop table t1; +create table t1 as select concat(from_days(730669)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select c1, hex(c1) from t1; +c1 hex(c1) +2000-07-03 323030302D30372D3033 +drop table t1; +create table t1 as select concat(curdate()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(utc_date()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(curtime()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select repeat('a',20) as c1 limit 0; +set timestamp=1216359724; +insert into t1 values (current_date); +insert into t1 values (current_time); +select c1, hex(c1) from t1; +c1 hex(c1) +2008-07-18 323030382D30372D3138 +08:42:04 30383A34323A3034 +drop table t1; +create table t1 as select concat(utc_time()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(sec_to_time(2378))); +hex(concat(sec_to_time(2378))) +30303A33393A3338 +create table t1 as select concat(sec_to_time(2378)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00'))); +hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00'))) +32343A30303A3030 +create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(maketime(10,11,12))); +hex(concat(maketime(10,11,12))) +31303A31313A3132 +create table t1 as select concat(maketime(10,11,12)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(get_format(DATE,'USA')); +hex(get_format(DATE,'USA')) +256D2E25642E2559 +create table t1 as select get_format(DATE,'USA') as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(concat(from_unixtime(1111885200)),4)); +hex(left(concat(from_unixtime(1111885200)),4)) +32303035 +create table t1 as select concat(from_unixtime(1111885200)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00'))); +hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00'))) +323030332D31322D33312032303A30303A3030 +create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); +hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))) +323030342D30312D30322031323A30303A3030 +create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t1; +c1 +2004-01-02 12:00:00 +drop table t1; +select hex(concat(makedate(2009,1))); +hex(concat(makedate(2009,1))) +323030392D30312D3031 +create table t1 as select concat(makedate(2009,1)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t1; +c1 +2009-01-01 +drop table t1; +create table t1 as select concat(now()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(utc_timestamp()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(sysdate()) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(addtime('00:00:00','11:22:33'))); +hex(concat(addtime('00:00:00','11:22:33'))) +31313A32323A3333 +create table t1 as select concat(addtime('00:00:00','11:22:33')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(concat(subtime('23:59:59','11:22:33'))); +hex(concat(subtime('23:59:59','11:22:33'))) +31323A33373A3236 +create table t1 as select concat(subtime('23:59:59','11:22:33')) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(elt(1,2,3)); +hex(elt(1,2,3)) +32 +create table t1 as select elt(1,2,3) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(export_set(1,2,3,4,2)); +hex(export_set(1,2,3,4,2)) +323433 +create table t1 as select export_set(1,2,3,4,2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(127) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(insert(1133,3,0,22)); +hex(insert(1133,3,0,22)) +313132323333 +create table t1 as select insert(1133,3,0,22) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(6) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(lcase(123)); +hex(lcase(123)) +313233 +create table t1 as select lcase(123) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(left(123,1)); +hex(left(123,1)) +31 +create table t1 as select left(123,1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(lower(123)); +hex(lower(123)) +313233 +create table t1 as select lower(123) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(lpad(1,2,0)); +hex(lpad(1,2,0)) +3031 +create table t1 as select lpad(1,2,0) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(ltrim(1)); +hex(ltrim(1)) +31 +create table t1 as select ltrim(1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(mid(1,1,1)); +hex(mid(1,1,1)) +31 +create table t1 as select mid(1,1,1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(repeat(1,2)); +hex(repeat(1,2)) +3131 +create table t1 as select repeat(1,2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(replace(1,1,2)); +hex(replace(1,1,2)) +32 +create table t1 as select replace(1,1,2) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(reverse(12)); +hex(reverse(12)) +3231 +create table t1 as select reverse(12) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(right(123,1)); +hex(right(123,1)) +33 +create table t1 as select right(123,1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(rpad(1,2,0)); +hex(rpad(1,2,0)) +3130 +create table t1 as select rpad(1,2,0) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(rtrim(1)); +hex(rtrim(1)) +31 +create table t1 as select rtrim(1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(soundex(1)); +hex(soundex(1)) + +create table t1 as select soundex(1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(substring(1,1,1)); +hex(substring(1,1,1)) +31 +create table t1 as select substring(1,1,1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(trim(1)); +hex(trim(1)) +31 +create table t1 as select trim(1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(ucase(1)); +hex(ucase(1)) +31 +create table t1 as select ucase(1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select hex(upper(1)); +hex(upper(1)) +31 +create table t1 as select upper(1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select repeat(' ', 64) as a limit 0; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(64) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values ("1.1"), ("2.1"); +select a, hex(a) from t1; +a hex(a) +1.1 312E31 +2.1 322E31 +update t1 set a= a + 0.1; +select a, hex(a) from t1; +a hex(a) +1.2000000000000002 312E32303030303030303030303030303032 +2.2 322E32 +drop table t1; +create table t1 (a tinyint); +insert into t1 values (1); +select hex(concat(a)) from t1; +hex(concat(a)) +31 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a tinyint zerofill); +insert into t1 values (1), (10), (100); +select hex(concat(a)), a from t1; +hex(concat(a)) a +303031 001 +303130 010 +313030 100 +drop table t1; +create table t1 (a tinyint(4) zerofill); +insert into t1 values (1), (10), (100); +select hex(concat(a)), a from t1; +hex(concat(a)) a +30303031 0001 +30303130 0010 +30313030 0100 +drop table t1; +create table t1 (a decimal(10,2)); +insert into t1 values (123.45); +select hex(concat(a)) from t1; +hex(concat(a)) +3132332E3435 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(12) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a smallint); +insert into t1 values (1); +select hex(concat(a)) from t1; +hex(concat(a)) +31 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(6) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a smallint zerofill); +insert into t1 values (1), (10), (100), (1000), (10000); +select hex(concat(a)), a from t1; +hex(concat(a)) a +3030303031 00001 +3030303130 00010 +3030313030 00100 +3031303030 01000 +3130303030 10000 +drop table t1; +create table t1 (a mediumint); +insert into t1 values (1); +select hex(concat(a)) from t1; +hex(concat(a)) +31 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(9) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a mediumint zerofill); +insert into t1 values (1), (10), (100), (1000), (10000); +select hex(concat(a)), a from t1; +hex(concat(a)) a +3030303030303031 00000001 +3030303030303130 00000010 +3030303030313030 00000100 +3030303031303030 00001000 +3030303130303030 00010000 +drop table t1; +create table t1 (a int); +insert into t1 values (1); +select hex(concat(a)) from t1; +hex(concat(a)) +31 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(11) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a int zerofill); +insert into t1 values (1), (10), (100), (1000), (10000); +select hex(concat(a)), a from t1; +hex(concat(a)) a +30303030303030303031 0000000001 +30303030303030303130 0000000010 +30303030303030313030 0000000100 +30303030303031303030 0000001000 +30303030303130303030 0000010000 +drop table t1; +create table t1 (a bigint); +insert into t1 values (1); +select hex(concat(a)) from t1; +hex(concat(a)) +31 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(20) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a bigint zerofill); +insert into t1 values (1), (10), (100), (1000), (10000); +select hex(concat(a)), a from t1; +hex(concat(a)) a +3030303030303030303030303030303030303031 00000000000000000001 +3030303030303030303030303030303030303130 00000000000000000010 +3030303030303030303030303030303030313030 00000000000000000100 +3030303030303030303030303030303031303030 00000000000000001000 +3030303030303030303030303030303130303030 00000000000000010000 +drop table t1; +create table t1 (a float); +insert into t1 values (123.456); +select hex(concat(a)) from t1; +hex(concat(a)) +3132332E343536 +select concat(a) from t1; +concat(a) +123.456 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(12) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a float zerofill); +insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); +select hex(concat(a)), a from t1; +hex(concat(a)) a +303030303030303030312E31 0000000001.1 +303030303030303031302E31 0000000010.1 +303030303030303130302E31 0000000100.1 +303030303030313030302E31 0000001000.1 +303030303031303030302E31 0000010000.1 +drop table t1; +create table t1 (a double); +insert into t1 values (123.456); +select hex(concat(a)) from t1; +hex(concat(a)) +3132332E343536 +select concat(a) from t1; +concat(a) +123.456 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(22) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a double zerofill); +insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); +select hex(concat(a)), a from t1; +hex(concat(a)) a +30303030303030303030303030303030303030312E31 00000000000000000001.1 +30303030303030303030303030303030303031302E31 00000000000000000010.1 +30303030303030303030303030303030303130302E31 00000000000000000100.1 +30303030303030303030303030303030313030302E31 00000000000000001000.1 +30303030303030303030303030303031303030302E31 00000000000000010000.1 +drop table t1; +create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +insert into t1 values (1); +select hex(concat(a)) from t1; +hex(concat(a)) +3031 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a year); +insert into t1 values (1); +select hex(concat(a)) from t1; +hex(concat(a)) +32303031 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a bit(64)); +insert into t1 values (1); +select hex(concat(a)) from t1; +hex(concat(a)) +0000000000000001 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varbinary(64) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); +insert into t1 values (0); +insert into t1 values (20010203040506); +insert into t1 values (19800203040506); +insert into t1 values ('2001-02-03 04:05:06'); +select hex(concat(a)) from t1; +hex(concat(a)) +303030302D30302D30302030303A30303A3030 +323030312D30322D30332030343A30353A3036 +313938302D30322D30332030343A30353A3036 +323030312D30322D30332030343A30353A3036 +select concat(a) from t1; +concat(a) +0000-00-00 00:00:00 +2001-02-03 04:05:06 +1980-02-03 04:05:06 +2001-02-03 04:05:06 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a date); +insert into t1 values ('2001-02-03'); +insert into t1 values (20010203); +select hex(concat(a)) from t1; +hex(concat(a)) +323030312D30322D3033 +323030312D30322D3033 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a time); +insert into t1 values (1); +insert into t1 values ('01:02:03'); +select hex(concat(a)) from t1; +hex(concat(a)) +30303A30303A3031 +30313A30323A3033 +select concat(a) from t1; +concat(a) +00:00:01 +01:02:03 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(10) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a datetime); +insert into t1 values ('2001-02-03 04:05:06'); +insert into t1 values (20010203040506); +select hex(concat(a)) from t1; +hex(concat(a)) +323030312D30322D30332030343A30353A3036 +323030312D30322D30332030343A30353A3036 +create table t2 as select concat(a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `concat(a)` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 (a tinyint); +insert into t1 values (1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(4) YES NULL +select hex(a) from v1; +hex(a) +31 +drop table t1; +drop view v1; +create table t1 (a tinyint zerofill); +insert into t1 values (1), (10), (100); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(3) YES NULL +select hex(a) from v1; +hex(a) +303031 +303130 +313030 +drop table t1; +drop view v1; +create table t1 (a tinyint(30) zerofill); +insert into t1 values (1), (10), (100); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(30) YES NULL +select hex(a) from v1; +hex(a) +303030303030303030303030303030303030303030303030303030303031 +303030303030303030303030303030303030303030303030303030303130 +303030303030303030303030303030303030303030303030303030313030 +drop table t1; +drop view v1; +create table t1 (a decimal(10,2)); +insert into t1 values (123.45); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(12) YES NULL +select hex(a) from v1; +hex(a) +3132332E3435 +drop table t1; +drop view v1; +create table t1 (a smallint); +insert into t1 values (1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(6) YES NULL +select hex(a) from v1; +hex(a) +31 +drop table t1; +drop view v1; +create table t1 (a smallint zerofill); +insert into t1 values (1), (10), (100), (1000), (10000); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(5) YES NULL +select hex(a) from v1; +hex(a) +3030303031 +3030303130 +3030313030 +3031303030 +3130303030 +drop table t1; +drop view v1; +create table t1 (a mediumint); +insert into t1 values (1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(9) YES NULL +select hex(a) from v1; +hex(a) +31 +drop table t1; +drop view v1; +create table t1 (a mediumint zerofill); +insert into t1 values (1), (10), (100), (1000), (10000); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(8) YES NULL +select hex(a) from v1; +hex(a) +3030303030303031 +3030303030303130 +3030303030313030 +3030303031303030 +3030303130303030 +drop table t1; +drop view v1; +create table t1 (a int); +insert into t1 values (1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(11) YES NULL +select hex(a) from v1; +hex(a) +31 +drop table t1; +drop view v1; +create table t1 (a int zerofill); +insert into t1 values (1), (10), (100), (1000), (10000); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(10) YES NULL +select hex(a) from v1; +hex(a) +30303030303030303031 +30303030303030303130 +30303030303030313030 +30303030303031303030 +30303030303130303030 +drop table t1; +drop view v1; +create table t1 (a bigint); +insert into t1 values (1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(20) YES NULL +select hex(a) from v1; +hex(a) +31 +drop table t1; +drop view v1; +create table t1 (a bigint zerofill); +insert into t1 values (1), (10), (100), (1000), (10000); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(20) YES NULL +select hex(a) from v1; +hex(a) +3030303030303030303030303030303030303031 +3030303030303030303030303030303030303130 +3030303030303030303030303030303030313030 +3030303030303030303030303030303031303030 +3030303030303030303030303030303130303030 +drop table t1; +drop view v1; +create table t1 (a float); +insert into t1 values (123.456); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(12) YES NULL +select hex(a) from v1; +hex(a) +3132332E343536 +drop table t1; +drop view v1; +create table t1 (a float zerofill); +insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(12) YES NULL +select hex(a) from v1; +hex(a) +303030303030303030312E31 +303030303030303031302E31 +303030303030303130302E31 +303030303030313030302E31 +303030303031303030302E31 +drop table t1; +drop view v1; +create table t1 (a double); +insert into t1 values (123.456); +select concat(a) from t1; +concat(a) +123.456 +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(22) YES NULL +select hex(a) from v1; +hex(a) +3132332E343536 +drop table t1; +drop view v1; +create table t1 (a double zerofill); +insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(22) YES NULL +select hex(a) from v1; +hex(a) +30303030303030303030303030303030303030312E31 +30303030303030303030303030303030303031302E31 +30303030303030303030303030303030303130302E31 +30303030303030303030303030303030313030302E31 +30303030303030303030303030303031303030302E31 +drop table t1; +drop view v1; +create table t1 (a year(2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +insert into t1 values (1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(2) YES NULL +select hex(a) from v1; +hex(a) +3031 +drop table t1; +drop view v1; +create table t1 (a year); +insert into t1 values (1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(4) YES NULL +select hex(a) from v1; +hex(a) +32303031 +drop table t1; +drop view v1; +create table t1 (a bit(64)); +insert into t1 values (1); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varbinary(64) YES NULL +select hex(a) from v1; +hex(a) +0000000000000001 +drop table t1; +drop view v1; +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); +insert into t1 values (0); +insert into t1 values (20010203040506); +insert into t1 values (19800203040506); +insert into t1 values ('2001-02-03 04:05:06'); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(19) YES NULL +select hex(a) from v1; +hex(a) +303030302D30302D30302030303A30303A3030 +323030312D30322D30332030343A30353A3036 +313938302D30322D30332030343A30353A3036 +323030312D30322D30332030343A30353A3036 +drop table t1; +drop view v1; +create table t1 (a date); +insert into t1 values ('2001-02-03'); +insert into t1 values (20010203); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(10) YES NULL +select hex(a) from v1; +hex(a) +323030312D30322D3033 +323030312D30322D3033 +drop table t1; +drop view v1; +create table t1 (a time); +insert into t1 values (1); +insert into t1 values ('01:02:03'); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(10) YES NULL +select hex(a) from v1; +hex(a) +30303A30303A3031 +30313A30323A3033 +drop table t1; +drop view v1; +create table t1 (a datetime); +insert into t1 values ('2001-02-03 04:05:06'); +insert into t1 values (20010203040506); +create view v1(a) as select concat(a) from t1; +show columns from v1; +Field Type Null Key Default Extra +a varchar(19) YES NULL +select hex(a) from v1; +hex(a) +323030312D30322D30332030343A30353A3036 +323030312D30322D30332030343A30353A3036 +drop table t1; +drop view v1; +create function f1 (par1 int) returns int +begin +return concat(par1); +end| +set @a= f1(1); +select hex(@a); +hex(@a) +1 +select hex(concat(f1(1))); +hex(concat(f1(1))) +31 +create table t1 as select f1(1) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(f1(1)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create view v1 as select concat(f1(1)) as c1; +show columns from v1; +Field Type Null Key Default Extra +c1 varchar(11) YES NULL +drop table t1; +drop view v1; +drop function f1; +create function f1 (par1 decimal(18,2)) returns decimal(18,2) +begin +return concat(par1); +end| +set @a= f1(123.45); +select hex(@a); +hex(@a) +7B +select hex(concat(f1(123.45))); +hex(concat(f1(123.45))) +3132332E3435 +create table t1 as select f1(123.45) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` decimal(18,2) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(f1(123.45)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create view v1 as select concat(f1(123.45)) as c1; +show columns from v1; +Field Type Null Key Default Extra +c1 varchar(20) YES NULL +drop table t1; +drop view v1; +drop function f1; +create function f1 (par1 float) returns float +begin +return concat(par1); +end| +set @a= f1(123.45); +select hex(@a); +hex(@a) +7B +select hex(concat(f1(123.45))); +hex(concat(f1(123.45))) +3132332E3435 +create table t1 as select f1(123.45) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` float DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(f1(123.45)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(12) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create view v1 as select concat(f1(123.45)) as c1; +show columns from v1; +Field Type Null Key Default Extra +c1 varchar(12) YES NULL +drop table t1; +drop view v1; +drop function f1; +create function f1 (par1 date) returns date +begin +return concat(par1); +end| +set @a= f1(cast('2001-01-02' as date)); +select hex(@a); +hex(@a) +323030312D30312D3032 +select hex(concat(f1(cast('2001-01-02' as date)))); +hex(concat(f1(cast('2001-01-02' as date)))) +323030312D30312D3032 +create table t1 as select f1(cast('2001-01-02' as date)) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as select concat(f1(cast('2001-01-02' as date))) as c1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create view v1 as select concat(f1(cast('2001-01-02' as date))) as c1; +show columns from v1; +Field Type Null Key Default Extra +c1 varchar(10) YES NULL +drop table t1; +drop view v1; +drop function f1; +# +# End of WL#2649 Number-to-string conversions +# +# +# Bug#54668 User variable assignments get wrong type +# +SET @x=md5('a'); +SELECT charset(@x), collation(@x); +charset(@x) collation(@x) +cp1251 cp1251_general_ci +SET @x=old_password('a'); +SELECT charset(@x), collation(@x); +charset(@x) collation(@x) +cp1251 cp1251_general_ci +SET @x=password('a'); +SELECT charset(@x), collation(@x); +charset(@x) collation(@x) +cp1251 cp1251_general_ci +SET @x=sha('a'); +SELECT charset(@x), collation(@x); +charset(@x) collation(@x) +cp1251 cp1251_general_ci +SET @x=sha1('a'); +SELECT charset(@x), collation(@x); +charset(@x) collation(@x) +cp1251 cp1251_general_ci +SET @x=astext(point(1,2)); +SELECT charset(@x), collation(@x); +charset(@x) collation(@x) +cp1251 cp1251_general_ci +SET @x=aswkt(point(1,2)); +SELECT charset(@x), collation(@x); +charset(@x) collation(@x) +cp1251 cp1251_general_ci +# +# Bug#54916 GROUP_CONCAT + IFNULL truncates output +# +SELECT @@collation_connection; +@@collation_connection +cp1251_general_ci +CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1234567); +SELECT GROUP_CONCAT(IFNULL(a,'')) FROM t1; +GROUP_CONCAT(IFNULL(a,'')) +1234567 +SELECT GROUP_CONCAT(IF(a,a,'')) FROM t1; +GROUP_CONCAT(IF(a,a,'')) +1234567 +SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1; +GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) +1234567 +SELECT COALESCE(a,'') FROM t1 GROUP BY 1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def COALESCE(a,'') 253 9 7 Y 0 39 51 +COALESCE(a,'') +1234567 +# All columns must be VARCHAR(9) with the same length: +CREATE TABLE t2 AS +SELECT +CONCAT(a), +IFNULL(a,''), +IF(a,a,''), +CASE WHEN a THEN a ELSE '' END, +COALESCE(a,'') +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `CONCAT(a)` varchar(9) CHARACTER SET cp1251 DEFAULT NULL, + `IFNULL(a,'')` varchar(9) CHARACTER SET cp1251 NOT NULL, + `IF(a,a,'')` varchar(9) CHARACTER SET cp1251 DEFAULT NULL, + `CASE WHEN a THEN a ELSE '' END` varchar(9) CHARACTER SET cp1251 DEFAULT NULL, + `COALESCE(a,'')` varchar(9) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `CONCAT_WS(1,2,3)` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `INSERT(1133,3,0,22)` varchar(6) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT LCASE(a) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `LCASE(a)` varchar(9) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT UCASE(a) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `UCASE(a)` varchar(9) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT REPEAT(1,2) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `REPEAT(1,2)` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT LEFT(123,2) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `LEFT(123,2)` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT RIGHT(123,2) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `RIGHT(123,2)` varchar(2) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT LTRIM(123) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `LTRIM(123)` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT RTRIM(123) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `RTRIM(123)` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT ELT(1,111,222,333) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ELT(1,111,222,333)` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT REPLACE(111,2,3) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `REPLACE(111,2,3)` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT SUBSTRING_INDEX(111,111,1) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `SUBSTRING_INDEX(111,111,1)` varchar(3) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT MAKE_SET(111,222,3) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `MAKE_SET(111,222,3)` varchar(5) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT SOUNDEX(1) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `SOUNDEX(1)` varchar(4) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT EXPORT_SET(1,'Y','N','',8); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `EXPORT_SET(1,'Y','N','',8)` varchar(64) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# +# End of Bug#54916 +# +# +# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields +# +SELECT @@collation_connection; +@@collation_connection +cp1251_general_ci +CREATE TABLE t1 ( +id INT(11) DEFAULT NULL, +date_column DATE DEFAULT NULL, +KEY(date_column)); +INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01'); +EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range date_column date_column 4 NULL 1 Using index condition +ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL; +EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range date_column date_column 6 NULL 1 Using index condition +DROP TABLE t1; +# +# Bug #31384 DATE_ADD() and DATE_SUB() return binary data +# +SELECT @@collation_connection, @@character_set_results; +@@collation_connection @@character_set_results +cp1251_general_ci cp1251 +SELECT +CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, +CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, +CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, +CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; +field_str1 field_str2 field_date field_datetime +cp1251 cp1251 binary binary +CREATE TABLE t1 AS +SELECT +DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, +DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, +DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, +DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `field_str1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, + `field1_str2` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, + `field_date` date DEFAULT NULL, + `field_datetime` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SELECT +DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1, +DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, +DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, +DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def field_str1 254 19 10 Y 0 0 51 +def field1_str2 254 19 19 Y 0 0 51 +def field_date 10 10 10 Y 128 0 63 +def field_datetime 12 19 19 Y 128 0 63 +field_str1 field1_str2 field_date field_datetime +2007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00 +SELECT +HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, +HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2, +HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, +HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; +field_str1 field1_str2 field_date field_datetime +323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 +# +# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() +# +SELECT @@collation_connection, @@character_set_results; +@@collation_connection @@character_set_results +cp1251_general_ci cp1251 +SELECT +CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, +CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, +CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, +CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; +addtime1 addtime2 date_add1 date_add2 +cp1251 cp1251 cp1251 cp1251 +CREATE TABLE t1 AS +SELECT +ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, +ADDTIME('10:01:01','10:00:00') AS addtime2, +DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, +DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `addtime1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, + `addtime2` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, + `date_add1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, + `date_add2` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +addtime1 addtime2 date_add1 date_add2 +20:01:01 20:01:01 2001-01-01 10:01:11 2001-01-01 10:01:11 +DROP TABLE t1; +# +# Bug#11926811 / Bug#60625 Illegal mix of collations +# +SELECT @@collation_connection; +@@collation_connection +cp1251_general_ci +CREATE PROCEDURE p1() +BEGIN +DECLARE v_LastPaymentDate DATETIME DEFAULT NULL; +SELECT v_LastPaymentDate < NOW(); +EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW(); +SHOW WARNINGS; +EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW()); +END// +CALL p1; +v_LastPaymentDate < NOW() +NULL +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Level Code Message +Note 1003 select v_LastPaymentDate@0 < current_timestamp() AS `v_LastPaymentDate < NOW()` +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select concat(convert(v_LastPaymentDate@0 using cp1251),current_timestamp()) AS `CONCAT(v_LastPaymentDate, NOW())` +DROP PROCEDURE p1; +# +# Bug#52159 returning time type from function and empty left join causes debug assertion +# +CREATE FUNCTION f1() RETURNS TIME RETURN 1; +CREATE TABLE t1 (b INT); +INSERT INTO t1 VALUES (0); +SELECT f1() FROM t1 LEFT JOIN (SELECT 1 AS a FROM t1 LIMIT 0) AS d ON 1 GROUP BY a; +f1() +00:00:01 +DROP FUNCTION f1; +DROP TABLE t1; +# +# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool) +# +SELECT @@collation_connection; +@@collation_connection +cp1251_general_ci +SELECT CASE 1 WHEN 2 THEN ( - '3' ) END; +CASE 1 WHEN 2 THEN ( - '3' ) END +NULL +# +# MDEV-5702 Incorrect results are returned with NULLIF() +# +CREATE TABLE t1 (d DATE); +INSERT INTO t1 VALUES ('1999-11-11'),('2014-02-04'); +SELECT DISTINCT d, CAST(d AS CHAR), NULLIF(d,"2000-01-01") AS bad, NULLIF(CAST(d AS CHAR),"2000-01-01") AS good FROM t1; +d CAST(d AS CHAR) bad good +1999-11-11 1999-11-11 1999-11-11 1999-11-11 +2014-02-04 2014-02-04 2014-02-04 2014-02-04 +CREATE TABLE t2 AS SELECT DISTINCT d, NULLIF(d,'2000-01-01') AS bad FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `d` date DEFAULT NULL, + `bad` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, t2; +SET NAMES latin1; +SET sql_mode=''; +CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); +INSERT INTO t1 VALUES (); +SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; +maketime(`a`,`a`,`a`) +00:00:00.000000 +DROP TABLE t1; +SET sql_mode=default; +# +# Bug#60101 COALESCE with cp1251 tables causes [Err] 1267 - Illegal mix of collations +# +CREATE TABLE t1 (test1 INT, test2 VARCHAR(255)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `test1` int(11) DEFAULT NULL, + `test2` varchar(255) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT COALESCE(IF(test1=1, 1, NULL), test2) FROM t1; +COALESCE(IF(test1=1, 1, NULL), test2) +SELECT COALESCE(IF(test1=1, NULL, 1), test2) FROM t1; +COALESCE(IF(test1=1, NULL, 1), test2) +DROP TABLE t1; +# +# MDEV-5459 Illegal mix of collations for datetime +# +SET NAMES cp1251; +CREATE TABLE t1 (dt DATETIME); +INSERT INTO t1 VALUES ('2014-01-02 10:20:30'); +SELECT date(dt) FROM t1 WHERE (CASE WHEN 1 THEN date(dt) ELSE null END >= '2013-12-01 00:00:00'); +date(dt) +2014-01-02 +DROP TABLE t1; +# +# End of 5.5 tests +# +# +# Start of 10.1 tests +# +# +# MDEV-8688 Wrong result for SELECT..WHERE varchar_column IN (1,2,3) AND varchar_column=' 1'; +# +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET cp1251 COLLATE cp1251_ukrainian_ci); +INSERT INTO t1 VALUES (' 1'),('`1'); +SELECT * FROM t1 WHERE a IN (1,2,3); +a + 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' +SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; +a + 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' +SELECT * FROM t1 WHERE a IN (1,2,3,'4') AND a=' 1'; +a + 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = ' 1' and `test`.`t1`.`a` in (1,2,3) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,3,'x') AND a=' 1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = ' 1' and `test`.`t1`.`a` in (1,2,3,'x') +DROP TABLE t1; +# +# MDEV-8671 Wrong result for SELECT..WHERE varchar_column=' 1' AND (varchar_column XOR '1') +# +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET cp1251 COLLATE cp1251_ukrainian_ci); +INSERT INTO t1 VALUES (' 1'),('`1'); +SELECT * FROM t1 WHERE a=' 1'; +a + 1 +`1 +SELECT * FROM t1 WHERE (a XOR '0'); +a + 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '`1' +SELECT * FROM t1 WHERE a=' 1' AND (a XOR '0'); +a + 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '`1' +# ' 1' should not be propagated into (a XIR '0') +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=' 1' AND (a XOR '0'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = ' 1' and (`test`.`t1`.`a` xor '0') +DROP TABLE t1; +# +# End of 10.1 tests +# |