summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/ctype_many.result1
-rw-r--r--mysql-test/main/ctype_many.test2
-rw-r--r--mysql-test/main/ctype_utf32.result2
-rw-r--r--mysql-test/main/func_math.result1257
-rw-r--r--mysql-test/main/func_math.test710
-rw-r--r--mysql-test/main/func_str.result6
-rw-r--r--sql/item_strfunc.cc13
-rw-r--r--sql/sql_type.cc45
-rw-r--r--sql/sql_type.h13
9 files changed, 2041 insertions, 8 deletions
diff --git a/mysql-test/main/ctype_many.result b/mysql-test/main/ctype_many.result
index 25802af4b5d..9def4fea80b 100644
--- a/mysql-test/main/ctype_many.result
+++ b/mysql-test/main/ctype_many.result
@@ -1695,6 +1695,7 @@ DO CONVERT(CAST(SUBSTRING_INDEX(FORMAT(1,'1111'), FORMAT('','Zpq'),1)
AS BINARY(0)) USING utf8);
Warnings:
Warning 1292 Truncated incorrect INTEGER value: 'Zpq'
+Warning 1292 Truncated incorrect INTEGER value: 'Zpq'
Warning 1292 Truncated incorrect DOUBLE value: ''
Warning 1292 Truncated incorrect BINARY(0) value: '1.'
#
diff --git a/mysql-test/main/ctype_many.test b/mysql-test/main/ctype_many.test
index a619b16416c..ba99c2e925a 100644
--- a/mysql-test/main/ctype_many.test
+++ b/mysql-test/main/ctype_many.test
@@ -225,9 +225,11 @@ DROP TABLE t1;
--echo # Bug#58371 Assertion failed: !s.uses_buffer_owned_by(this) with format string function
--echo #
+--disable_ps_protocol
SET NAMES latin1;
DO CONVERT(CAST(SUBSTRING_INDEX(FORMAT(1,'1111'), FORMAT('','Zpq'),1)
AS BINARY(0)) USING utf8);
+--enable_ps_protocol
--echo #
--echo # End of 5.1 tests
--echo #
diff --git a/mysql-test/main/ctype_utf32.result b/mysql-test/main/ctype_utf32.result
index cae8ba4ebaa..501732848b1 100644
--- a/mysql-test/main/ctype_utf32.result
+++ b/mysql-test/main/ctype_utf32.result
@@ -1588,7 +1588,7 @@ CREATE TABLE t1 AS SELECT format(123,2,'no_NO');
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `format(123,2,'no_NO')` varchar(45) CHARACTER SET utf32 DEFAULT NULL
+ `format(123,2,'no_NO')` varchar(17) CHARACTER SET utf32 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT * FROM t1;
format(123,2,'no_NO')
diff --git a/mysql-test/main/func_math.result b/mysql-test/main/func_math.result
index 968d394117a..aef00044bc4 100644
--- a/mysql-test/main/func_math.result
+++ b/mysql-test/main/func_math.result
@@ -1426,3 +1426,1260 @@ DROP TABLE t1;
#
# End of 10.3 tests
#
+#
+# Start of 10.4 tests
+#
+#
+# MDEV-20732 max_char_length() doesn't account for FORMAT() with doubles in scientific notation
+#
+CREATE OR REPLACE TABLE t1 AS SELECT FORMAT(-1e308,2);
+SELECT * FROM t1;
+FORMAT(-1e308,2)
+-100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.00
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `FORMAT(-1e308,2)` varchar(416) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 AS SELECT FORMAT('-1e308',2);
+SELECT * FROM t1;
+FORMAT('-1e308',2)
+-100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.00
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `FORMAT('-1e308',2)` varchar(416) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 AS SELECT FORMAT(DATE'20191231',0),FORMAT(TIME'99:05:00',0),FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0);
+SELECT * FROM t1;
+FORMAT(DATE'20191231',0) FORMAT(TIME'99:05:00',0) FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0)
+20,191,231 990,500 20,191,231,235,959
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `FORMAT(DATE'20191231',0)` varchar(11) DEFAULT NULL,
+ `FORMAT(TIME'99:05:00',0)` varchar(10) DEFAULT NULL,
+ `FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0)` varchar(19) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (y YEAR);
+INSERT INTO t1 VALUES ('2099'),('99');
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(y,0) FROM t1;
+SELECT * FROM t2;
+FORMAT(y,0)
+2,099
+1,999
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `FORMAT(y,0)` varchar(6) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1, t2;
+CREATE OR REPLACE TABLE t1 (bi BIGINT UNSIGNED, bis BIGINT);
+INSERT INTO t1 VALUES (18446744073709551615,-9223372036854775808),(0,0);
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(bi,0),FORMAT(bis,0) FROM t1;
+SELECT * FROM t2;
+FORMAT(bi,0) FORMAT(bis,0)
+18,446,744,073,709,551,615 -9,223,372,036,854,775,808
+0 0
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `FORMAT(bi,0)` varchar(27) DEFAULT NULL,
+ `FORMAT(bis,0)` varchar(26) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (d DECIMAL(65,0));
+INSERT INTO t1 VALUES (99999999999999999999999999999999999999999999999999999999999999999),(-99999999999999999999999999999999999999999999999999999999999999999),(0);
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(d,0) FROM t1;
+SELECT * FROM t2;
+FORMAT(d,0)
+99,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999
+-99,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999
+0
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `FORMAT(d,0)` varchar(87) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 AS SELECT -99999999999999999999999999999999999999999999999999999999999999999 as c1;
+SELECT * FROM t1;
+c1
+-99999999999999999999999999999999999999999999999999999999999999999
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` decimal(65,0) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (b1 BIT(1),
+b2 BIT(2),
+b3 BIT(3),
+b4 BIT(4),
+b5 BIT(5),
+b6 BIT(6),
+b7 BIT(7),
+b8 BIT(8),
+b9 BIT(9),
+b10 BIT(10),
+b11 BIT(11),
+b12 BIT(12),
+b13 BIT(13),
+b14 BIT(14),
+b15 BIT(15),
+b16 BIT(16),
+b17 BIT(17),
+b18 BIT(18),
+b19 BIT(19),
+b20 BIT(20),
+b21 BIT(21),
+b22 BIT(22),
+b23 BIT(23),
+b24 BIT(24),
+b25 BIT(25),
+b26 BIT(26),
+b27 BIT(27),
+b28 BIT(28),
+b29 BIT(29),
+b30 BIT(30),
+b31 BIT(31),
+b32 BIT(32),
+b33 BIT(33),
+b34 BIT(34),
+b35 BIT(35),
+b36 BIT(36),
+b37 BIT(37),
+b38 BIT(38),
+b39 BIT(39),
+b40 BIT(40),
+b41 BIT(41),
+b42 BIT(42),
+b43 BIT(43),
+b44 BIT(44),
+b45 BIT(45),
+b46 BIT(46),
+b47 BIT(47),
+b48 BIT(48),
+b49 BIT(49),
+b50 BIT(50),
+b51 BIT(51),
+b52 BIT(52),
+b53 BIT(53),
+b54 BIT(54),
+b55 BIT(55),
+b56 BIT(56),
+b57 BIT(57),
+b58 BIT(58),
+b59 BIT(59),
+b60 BIT(60),
+b61 BIT(61),
+b62 BIT(62),
+b63 BIT(63),
+b64 BIT(64));
+INSERT INTO t1 VALUES(1,3,7,15,31,63,127,255,511,1023,2047,4095,8191,16383,
+32767,65535,131071,262143,524287,1048575,2097151,4194303,8388607,16777215,
+33554431,67108863,134217727,268435455,536870911,1073741823,2147483647,
+4294967295,8589934591,17179869183,34359738367,68719476735,137438953471,
+274877906943,549755813887,1099511627775,2199023255551,4398046511103,
+8796093022207,17592186044415,35184372088831,70368744177663,140737488355327,
+281474976710655,562949953421311,1125899906842623,2251799813685247,
+4503599627370495,9007199254740991,18014398509481983,36028797018963967,
+72057594037927935,144115188075855871,288230376151711743,576460752303423487,
+1152921504606846975,2305843009213693951,4611686018427387903,
+9223372036854775807,18446744073709551615);
+SELECT FORMAT(b1,0),
+FORMAT(b2,0),
+FORMAT(b3,0),
+FORMAT(b4,0),
+FORMAT(b5,0),
+FORMAT(b6,0),
+FORMAT(b7,0),
+FORMAT(b8,0),
+FORMAT(b9,0),
+FORMAT(b10,0),
+FORMAT(b11,0),
+FORMAT(b12,0),
+FORMAT(b13,0),
+FORMAT(b14,0),
+FORMAT(b15,0),
+FORMAT(b16,0),
+FORMAT(b17,0),
+FORMAT(b18,0),
+FORMAT(b19,0),
+FORMAT(b20,0),
+FORMAT(b21,0),
+FORMAT(b22,0),
+FORMAT(b23,0),
+FORMAT(b24,0),
+FORMAT(b25,0),
+FORMAT(b26,0),
+FORMAT(b27,0),
+FORMAT(b28,0),
+FORMAT(b29,0),
+FORMAT(b30,0),
+FORMAT(b31,0),
+FORMAT(b32,0),
+FORMAT(b33,0),
+FORMAT(b34,0),
+FORMAT(b35,0),
+FORMAT(b36,0),
+FORMAT(b37,0),
+FORMAT(b38,0),
+FORMAT(b39,0),
+FORMAT(b40,0),
+FORMAT(b41,0),
+FORMAT(b42,0),
+FORMAT(b43,0),
+FORMAT(b44,0),
+FORMAT(b45,0),
+FORMAT(b46,0),
+FORMAT(b47,0),
+FORMAT(b48,0),
+FORMAT(b49,0),
+FORMAT(b50,0),
+FORMAT(b51,0),
+FORMAT(b52,0),
+FORMAT(b53,0),
+FORMAT(b54,0),
+FORMAT(b55,0),
+FORMAT(b56,0),
+FORMAT(b57,0),
+FORMAT(b58,0),
+FORMAT(b59,0),
+FORMAT(b60,0),
+FORMAT(b61,0),
+FORMAT(b62,0),
+FORMAT(b63,0),
+FORMAT(b64,0)
+FROM t1;
+FORMAT(b1,0) 1
+FORMAT(b2,0) 3
+FORMAT(b3,0) 7
+FORMAT(b4,0) 15
+FORMAT(b5,0) 31
+FORMAT(b6,0) 63
+FORMAT(b7,0) 127
+FORMAT(b8,0) 255
+FORMAT(b9,0) 511
+FORMAT(b10,0) 1,023
+FORMAT(b11,0) 2,047
+FORMAT(b12,0) 4,095
+FORMAT(b13,0) 8,191
+FORMAT(b14,0) 16,383
+FORMAT(b15,0) 32,767
+FORMAT(b16,0) 65,535
+FORMAT(b17,0) 131,071
+FORMAT(b18,0) 262,143
+FORMAT(b19,0) 524,287
+FORMAT(b20,0) 1,048,575
+FORMAT(b21,0) 2,097,151
+FORMAT(b22,0) 4,194,303
+FORMAT(b23,0) 8,388,607
+FORMAT(b24,0) 16,777,215
+FORMAT(b25,0) 33,554,431
+FORMAT(b26,0) 67,108,863
+FORMAT(b27,0) 134,217,727
+FORMAT(b28,0) 268,435,455
+FORMAT(b29,0) 536,870,911
+FORMAT(b30,0) 1,073,741,823
+FORMAT(b31,0) 2,147,483,647
+FORMAT(b32,0) 4,294,967,295
+FORMAT(b33,0) 8,589,934,591
+FORMAT(b34,0) 17,179,869,183
+FORMAT(b35,0) 34,359,738,367
+FORMAT(b36,0) 68,719,476,735
+FORMAT(b37,0) 137,438,953,471
+FORMAT(b38,0) 274,877,906,943
+FORMAT(b39,0) 549,755,813,887
+FORMAT(b40,0) 1,099,511,627,775
+FORMAT(b41,0) 2,199,023,255,551
+FORMAT(b42,0) 4,398,046,511,103
+FORMAT(b43,0) 8,796,093,022,207
+FORMAT(b44,0) 17,592,186,044,415
+FORMAT(b45,0) 35,184,372,088,831
+FORMAT(b46,0) 70,368,744,177,663
+FORMAT(b47,0) 140,737,488,355,327
+FORMAT(b48,0) 281,474,976,710,655
+FORMAT(b49,0) 562,949,953,421,311
+FORMAT(b50,0) 1,125,899,906,842,623
+FORMAT(b51,0) 2,251,799,813,685,247
+FORMAT(b52,0) 4,503,599,627,370,495
+FORMAT(b53,0) 9,007,199,254,740,991
+FORMAT(b54,0) 18,014,398,509,481,983
+FORMAT(b55,0) 36,028,797,018,963,967
+FORMAT(b56,0) 72,057,594,037,927,935
+FORMAT(b57,0) 144,115,188,075,855,871
+FORMAT(b58,0) 288,230,376,151,711,743
+FORMAT(b59,0) 576,460,752,303,423,487
+FORMAT(b60,0) 1,152,921,504,606,846,975
+FORMAT(b61,0) 2,305,843,009,213,693,951
+FORMAT(b62,0) 4,611,686,018,427,387,903
+FORMAT(b63,0) 9,223,372,036,854,775,807
+FORMAT(b64,0) 18,446,744,073,709,551,615
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (c1 BIT(1));
+INSERT INTO t1 VALUES (b'1');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+31
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(2) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(2));
+INSERT INTO t1 VALUES (b'11');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+33
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(2) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(3));
+INSERT INTO t1 VALUES (b'111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+37
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(3) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(4));
+INSERT INTO t1 VALUES (b'1111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+F
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3135
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(3) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(5));
+INSERT INTO t1 VALUES (b'11111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1F
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3331
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(3) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(6));
+INSERT INTO t1 VALUES (b'111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3F
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3633
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(5) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(7));
+INSERT INTO t1 VALUES (b'1111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7F
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+313237
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(5) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(8));
+INSERT INTO t1 VALUES (b'11111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+323535
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(5) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(9));
+INSERT INTO t1 VALUES (b'111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+353131
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(6) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(10));
+INSERT INTO t1 VALUES (b'1111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+312C303233
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(6) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(11));
+INSERT INTO t1 VALUES (b'11111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+322C303437
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(6) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(12));
+INSERT INTO t1 VALUES (b'111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+342C303935
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(7) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(13));
+INSERT INTO t1 VALUES (b'1111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+382C313931
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(7) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(14));
+INSERT INTO t1 VALUES (b'11111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+31362C333833
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(7) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(15));
+INSERT INTO t1 VALUES (b'111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+33322C373637
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(9) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(16));
+INSERT INTO t1 VALUES (b'1111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+36352C353335
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(9) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(17));
+INSERT INTO t1 VALUES (b'11111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3133312C303731
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(9) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(18));
+INSERT INTO t1 VALUES (b'111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3236322C313433
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(10) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(19));
+INSERT INTO t1 VALUES (b'1111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3532342C323837
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(10) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(20));
+INSERT INTO t1 VALUES (b'11111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+312C3034382C353735
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(10) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(21));
+INSERT INTO t1 VALUES (b'111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+322C3039372C313531
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(22));
+INSERT INTO t1 VALUES (b'1111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+342C3139342C333033
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(23));
+INSERT INTO t1 VALUES (b'11111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+382C3338382C363037
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(24));
+INSERT INTO t1 VALUES (b'111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+31362C3737372C323135
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(13) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(25));
+INSERT INTO t1 VALUES (b'1111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+33332C3535342C343331
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(13) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(26));
+INSERT INTO t1 VALUES (b'11111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+36372C3130382C383633
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(13) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(27));
+INSERT INTO t1 VALUES (b'111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3133342C3231372C373237
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(14) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(28));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3236382C3433352C343535
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(14) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(29));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3533362C3837302C393131
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(14) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(30));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+312C3037332C3734312C383233
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(15) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(31));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+322C3134372C3438332C363437
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(15) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(32));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+342C3239342C3936372C323935
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(15) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(33));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+382C3538392C3933342C353931
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(17) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(34));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+31372C3137392C3836392C313833
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(17) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(35));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+33342C3335392C3733382C333637
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(17) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(36));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+36382C3731392C3437362C373335
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(18) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(37));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3133372C3433382C3935332C343731
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(18) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(38));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3237342C3837372C3930362C393433
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(18) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(39));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3534392C3735352C3831332C383837
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(19) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(40));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+312C3039392C3531312C3632372C373735
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(19) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(41));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+322C3139392C3032332C3235352C353531
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(19) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(42));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+342C3339382C3034362C3531312C313033
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(21) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(43));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+382C3739362C3039332C3032322C323037
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(21) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(44));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+31372C3539322C3138362C3034342C343135
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(21) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(45));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+33352C3138342C3337322C3038382C383331
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(22) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(46));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+37302C3336382C3734342C3137372C363633
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(22) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(47));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3134302C3733372C3438382C3335352C333237
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(22) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(48));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3238312C3437342C3937362C3731302C363535
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(23) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(49));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3536322C3934392C3935332C3432312C333131
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(23) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(50));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+312C3132352C3839392C3930362C3834322C363233
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(23) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(51));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+322C3235312C3739392C3831332C3638352C323437
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(25) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(52));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+342C3530332C3539392C3632372C3337302C343935
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(25) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(53));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+392C3030372C3139392C3235342C3734302C393931
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(25) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(54));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+31382C3031342C3339382C3530392C3438312C393833
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(26) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(55));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+33362C3032382C3739372C3031382C3936332C393637
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(26) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(56));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+37322C3035372C3539342C3033372C3932372C393335
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(26) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(57));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3134342C3131352C3138382C3037352C3835352C383731
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(27) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(58));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3238382C3233302C3337362C3135312C3731312C373433
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(27) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(59));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+3537362C3436302C3735322C3330332C3432332C343837
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(27) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(60));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+312C3135322C3932312C3530342C3630362C3834362C393735
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(29) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(61));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+1FFFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+322C3330352C3834332C3030392C3231332C3639332C393531
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(29) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(62));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+3FFFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+342C3631312C3638362C3031382C3432372C3338372C393033
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(29) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(63));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+7FFFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+392C3232332C3337322C3033362C3835342C3737352C383037
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(30) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+CREATE OR REPLACE TABLE t1 (c1 BIT(64));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+HEX(c1)
+FFFFFFFFFFFFFFFF
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+HEX(c1)
+31382C3434362C3734342C3037332C3730392C3535312C363135
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c1` varchar(30) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/func_math.test b/mysql-test/main/func_math.test
index 4d65f5e00b0..b1216e87576 100644
--- a/mysql-test/main/func_math.test
+++ b/mysql-test/main/func_math.test
@@ -1001,3 +1001,713 @@ DROP TABLE t1;
--echo #
--echo # End of 10.3 tests
--echo #
+
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-20732 max_char_length() doesn't account for FORMAT() with doubles in scientific notation
+--echo #
+
+CREATE OR REPLACE TABLE t1 AS SELECT FORMAT(-1e308,2);
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 AS SELECT FORMAT('-1e308',2);
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 AS SELECT FORMAT(DATE'20191231',0),FORMAT(TIME'99:05:00',0),FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0);
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (y YEAR);
+INSERT INTO t1 VALUES ('2099'),('99');
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(y,0) FROM t1;
+SELECT * FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1, t2;
+
+CREATE OR REPLACE TABLE t1 (bi BIGINT UNSIGNED, bis BIGINT);
+INSERT INTO t1 VALUES (18446744073709551615,-9223372036854775808),(0,0);
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(bi,0),FORMAT(bis,0) FROM t1;
+SELECT * FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (d DECIMAL(65,0));
+INSERT INTO t1 VALUES (99999999999999999999999999999999999999999999999999999999999999999),(-99999999999999999999999999999999999999999999999999999999999999999),(0);
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(d,0) FROM t1;
+SELECT * FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 AS SELECT -99999999999999999999999999999999999999999999999999999999999999999 as c1;
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (b1 BIT(1),
+b2 BIT(2),
+b3 BIT(3),
+b4 BIT(4),
+b5 BIT(5),
+b6 BIT(6),
+b7 BIT(7),
+b8 BIT(8),
+b9 BIT(9),
+b10 BIT(10),
+b11 BIT(11),
+b12 BIT(12),
+b13 BIT(13),
+b14 BIT(14),
+b15 BIT(15),
+b16 BIT(16),
+b17 BIT(17),
+b18 BIT(18),
+b19 BIT(19),
+b20 BIT(20),
+b21 BIT(21),
+b22 BIT(22),
+b23 BIT(23),
+b24 BIT(24),
+b25 BIT(25),
+b26 BIT(26),
+b27 BIT(27),
+b28 BIT(28),
+b29 BIT(29),
+b30 BIT(30),
+b31 BIT(31),
+b32 BIT(32),
+b33 BIT(33),
+b34 BIT(34),
+b35 BIT(35),
+b36 BIT(36),
+b37 BIT(37),
+b38 BIT(38),
+b39 BIT(39),
+b40 BIT(40),
+b41 BIT(41),
+b42 BIT(42),
+b43 BIT(43),
+b44 BIT(44),
+b45 BIT(45),
+b46 BIT(46),
+b47 BIT(47),
+b48 BIT(48),
+b49 BIT(49),
+b50 BIT(50),
+b51 BIT(51),
+b52 BIT(52),
+b53 BIT(53),
+b54 BIT(54),
+b55 BIT(55),
+b56 BIT(56),
+b57 BIT(57),
+b58 BIT(58),
+b59 BIT(59),
+b60 BIT(60),
+b61 BIT(61),
+b62 BIT(62),
+b63 BIT(63),
+b64 BIT(64));
+INSERT INTO t1 VALUES(1,3,7,15,31,63,127,255,511,1023,2047,4095,8191,16383,
+ 32767,65535,131071,262143,524287,1048575,2097151,4194303,8388607,16777215,
+ 33554431,67108863,134217727,268435455,536870911,1073741823,2147483647,
+ 4294967295,8589934591,17179869183,34359738367,68719476735,137438953471,
+ 274877906943,549755813887,1099511627775,2199023255551,4398046511103,
+ 8796093022207,17592186044415,35184372088831,70368744177663,140737488355327,
+ 281474976710655,562949953421311,1125899906842623,2251799813685247,
+ 4503599627370495,9007199254740991,18014398509481983,36028797018963967,
+ 72057594037927935,144115188075855871,288230376151711743,576460752303423487,
+ 1152921504606846975,2305843009213693951,4611686018427387903,
+ 9223372036854775807,18446744073709551615);
+--vertical_results
+SELECT FORMAT(b1,0),
+FORMAT(b2,0),
+FORMAT(b3,0),
+FORMAT(b4,0),
+FORMAT(b5,0),
+FORMAT(b6,0),
+FORMAT(b7,0),
+FORMAT(b8,0),
+FORMAT(b9,0),
+FORMAT(b10,0),
+FORMAT(b11,0),
+FORMAT(b12,0),
+FORMAT(b13,0),
+FORMAT(b14,0),
+FORMAT(b15,0),
+FORMAT(b16,0),
+FORMAT(b17,0),
+FORMAT(b18,0),
+FORMAT(b19,0),
+FORMAT(b20,0),
+FORMAT(b21,0),
+FORMAT(b22,0),
+FORMAT(b23,0),
+FORMAT(b24,0),
+FORMAT(b25,0),
+FORMAT(b26,0),
+FORMAT(b27,0),
+FORMAT(b28,0),
+FORMAT(b29,0),
+FORMAT(b30,0),
+FORMAT(b31,0),
+FORMAT(b32,0),
+FORMAT(b33,0),
+FORMAT(b34,0),
+FORMAT(b35,0),
+FORMAT(b36,0),
+FORMAT(b37,0),
+FORMAT(b38,0),
+FORMAT(b39,0),
+FORMAT(b40,0),
+FORMAT(b41,0),
+FORMAT(b42,0),
+FORMAT(b43,0),
+FORMAT(b44,0),
+FORMAT(b45,0),
+FORMAT(b46,0),
+FORMAT(b47,0),
+FORMAT(b48,0),
+FORMAT(b49,0),
+FORMAT(b50,0),
+FORMAT(b51,0),
+FORMAT(b52,0),
+FORMAT(b53,0),
+FORMAT(b54,0),
+FORMAT(b55,0),
+FORMAT(b56,0),
+FORMAT(b57,0),
+FORMAT(b58,0),
+FORMAT(b59,0),
+FORMAT(b60,0),
+FORMAT(b61,0),
+FORMAT(b62,0),
+FORMAT(b63,0),
+FORMAT(b64,0)
+FROM t1;
+--horizontal_results
+
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(1));
+INSERT INTO t1 VALUES (b'1');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(2));
+INSERT INTO t1 VALUES (b'11');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(3));
+INSERT INTO t1 VALUES (b'111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(4));
+INSERT INTO t1 VALUES (b'1111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(5));
+INSERT INTO t1 VALUES (b'11111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(6));
+INSERT INTO t1 VALUES (b'111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(7));
+INSERT INTO t1 VALUES (b'1111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(8));
+INSERT INTO t1 VALUES (b'11111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(9));
+INSERT INTO t1 VALUES (b'111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(10));
+INSERT INTO t1 VALUES (b'1111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(11));
+INSERT INTO t1 VALUES (b'11111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(12));
+INSERT INTO t1 VALUES (b'111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(13));
+INSERT INTO t1 VALUES (b'1111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(14));
+INSERT INTO t1 VALUES (b'11111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(15));
+INSERT INTO t1 VALUES (b'111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(16));
+INSERT INTO t1 VALUES (b'1111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(17));
+INSERT INTO t1 VALUES (b'11111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(18));
+INSERT INTO t1 VALUES (b'111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(19));
+INSERT INTO t1 VALUES (b'1111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(20));
+INSERT INTO t1 VALUES (b'11111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(21));
+INSERT INTO t1 VALUES (b'111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(22));
+INSERT INTO t1 VALUES (b'1111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(23));
+INSERT INTO t1 VALUES (b'11111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(24));
+INSERT INTO t1 VALUES (b'111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(25));
+INSERT INTO t1 VALUES (b'1111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(26));
+INSERT INTO t1 VALUES (b'11111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(27));
+INSERT INTO t1 VALUES (b'111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(28));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(29));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(30));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(31));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(32));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(33));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(34));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(35));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(36));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(37));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(38));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(39));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(40));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(41));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(42));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(43));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(44));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(45));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(46));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(47));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(48));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(49));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(50));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(51));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(52));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(53));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(54));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(55));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(56));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(57));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(58));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(59));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(60));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(61));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(62));
+INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(63));
+INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+CREATE OR REPLACE TABLE t1 (c1 BIT(64));
+INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111111');
+SELECT HEX(c1) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
+SELECT HEX(c1) FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/func_str.result b/mysql-test/main/func_str.result
index 5fca53def04..5de77f18a24 100644
--- a/mysql-test/main/func_str.result
+++ b/mysql-test/main/func_str.result
@@ -745,7 +745,7 @@ t1 CREATE TABLE `t1` (
`conv(130,16,10)` varchar(64) DEFAULT NULL,
`hex(130)` varchar(6) DEFAULT NULL,
`char(130)` varbinary(4) DEFAULT NULL,
- `format(130,10)` varchar(45) DEFAULT NULL,
+ `format(130,10)` varchar(25) DEFAULT NULL,
`left(_latin2'a',1)` varchar(1) CHARACTER SET latin2 DEFAULT NULL,
`right(_latin2'a',1)` varchar(1) CHARACTER SET latin2 DEFAULT NULL,
`lcase(_latin2'a')` varchar(1) CHARACTER SET latin2 DEFAULT NULL,
@@ -2708,7 +2708,7 @@ create table t1(a float);
insert into t1 values (1.33);
select format(a, 2) from t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
-def format(a, 2) 253 57 4 Y 0 39 8
+def format(a, 2) 253 4 4 Y 0 39 8
format(a, 2)
1.33
drop table t1;
@@ -3042,7 +3042,7 @@ CREATE TABLE t1 AS SELECT format(123,2,'no_NO');
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `format(123,2,'no_NO')` varchar(45) DEFAULT NULL
+ `format(123,2,'no_NO')` varchar(17) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT * FROM t1;
format(123,2,'no_NO')
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index f571521b982..32f05a815db 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -2666,10 +2666,17 @@ const int FORMAT_MAX_DECIMALS= 30;
bool Item_func_format::fix_length_and_dec()
{
- uint32 char_length= args[0]->max_char_length();
- uint32 max_sep_count= (char_length / 3) + (decimals ? 1 : 0) + /*sign*/1;
+ uint32 char_length= args[0]->type_handler()->Item_decimal_notation_int_digits(args[0]);
+ uint dec= FORMAT_MAX_DECIMALS;
+ if (args[1]->const_item() && !args[1]->is_expensive())
+ {
+ Longlong_hybrid tmp= args[1]->to_longlong_hybrid();
+ if (!args[1]->null_value)
+ dec= tmp.to_uint(FORMAT_MAX_DECIMALS);
+ }
+ uint32 max_sep_count= (char_length / 3) + (dec ? 1 : 0) + /*sign*/1;
collation.set(default_charset());
- fix_char_length(char_length + max_sep_count + decimals);
+ fix_char_length(char_length + max_sep_count + dec);
if (arg_count == 3)
locale= args[2]->basic_const_item() ? args[2]->locale_from_val_str() : NULL;
else
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 8b382773f7a..31b85c2e858 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -3490,7 +3490,6 @@ uint32 Type_handler_temporal_result::max_display_length(const Item *item) const
return item->max_length;
}
-
uint32 Type_handler_string_result::max_display_length(const Item *item) const
{
return item->max_length;
@@ -3515,6 +3514,50 @@ uint32 Type_handler_general_purpose_int::max_display_length(const Item *item)
return type_limits_int_by_unsigned_flag(item->unsigned_flag)->char_length();
}
+/*************************************************************************/
+
+uint32
+Type_handler_decimal_result::Item_decimal_notation_int_digits(const Item *item)
+ const
+{
+ return item->decimal_int_part();
+}
+
+
+uint32
+Type_handler_temporal_result::Item_decimal_notation_int_digits(const Item *item)
+ const
+{
+ return item->decimal_int_part();
+}
+
+
+uint32
+Type_handler_bit::Item_decimal_notation_int_digits(const Item *item)
+ const
+{
+ return Bit_decimal_notation_int_digits(item);
+}
+
+
+uint32
+Type_handler_general_purpose_int::Item_decimal_notation_int_digits(
+ const Item *item) const
+{
+ return type_limits_int_by_unsigned_flag(item->unsigned_flag)->precision();
+}
+
+/*************************************************************************/
+
+/*
+ Decimal to binary digits ratio converges to log2(10) thus using 3 as
+ a divisor.
+*/
+uint32
+Type_handler_bit::Bit_decimal_notation_int_digits(const Item *item)
+{
+ return item->max_length/3+1;
+}
/*************************************************************************/
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 0d6f7901480..0dd82f3dfaf 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -3488,6 +3488,7 @@ public:
SORT_FIELD_ATTR *attr) const= 0;
virtual uint32 max_display_length(const Item *item) const= 0;
+ virtual uint32 Item_decimal_notation_int_digits(const Item *item) const { return 0; }
virtual uint32 calc_pack_length(uint32 length) const= 0;
virtual void Item_update_null_value(Item *item) const= 0;
virtual bool Item_save_in_value(THD *thd, Item *item, st_value *value) const= 0;
@@ -4236,6 +4237,7 @@ public:
const Type_std_attributes *item,
SORT_FIELD_ATTR *attr) const;
uint32 max_display_length(const Item *item) const;
+ uint32 Item_decimal_notation_int_digits(const Item *item) const;
Item *create_typecast_item(THD *thd, Item *item,
const Type_cast_attributes &attr) const;
bool Item_const_eq(const Item_const *a, const Item_const *b,
@@ -4529,6 +4531,7 @@ public:
virtual const Type_limits_int *
type_limits_int_by_unsigned_flag(bool unsigned_flag) const= 0;
uint32 max_display_length(const Item *item) const;
+ uint32 Item_decimal_notation_int_digits(const Item *item) const;
bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p) const;
};
@@ -4554,6 +4557,7 @@ public:
const Type_all_attributes *attr,
const st_value *value) const;
uint32 max_display_length(const Item *item) const;
+ uint32 Item_decimal_notation_int_digits(const Item *item) const;
bool can_change_cond_ref_to_const(Item_bool_func2 *target,
Item *target_expr, Item *target_value,
Item_bool_func2 *source,
@@ -4638,6 +4642,11 @@ public:
const Schema_specification_st *schema)
const;
uint32 max_display_length(const Item *item) const;
+/*
+ The next method returns 309 for long stringified doubles in scientific
+ notation, e.g. FORMAT('1e308', 2).
+*/
+ uint32 Item_decimal_notation_int_digits(const Item *item) const { return 309; }
bool Item_const_eq(const Item_const *a, const Item_const *b,
bool binary_cmp) const;
bool Item_eq_value(THD *thd, const Type_cmp_attributes *attr,
@@ -5024,6 +5033,7 @@ public:
return PROTOCOL_SEND_SHORT;
}
uint32 max_display_length(const Item *item) const;
+ uint32 Item_decimal_notation_int_digits(const Item *item) const { return 4; };
uint32 calc_pack_length(uint32 length) const { return 1; }
bool Item_send(Item *item, Protocol *protocol, st_value *buf) const
{
@@ -5073,6 +5083,8 @@ public:
return PROTOCOL_SEND_STRING;
}
uint32 max_display_length(const Item *item) const;
+ uint32 Item_decimal_notation_int_digits(const Item *item) const;
+ static uint32 Bit_decimal_notation_int_digits(const Item *item);
uint32 calc_pack_length(uint32 length) const { return length / 8; }
bool Item_send(Item *item, Protocol *protocol, st_value *buf) const
{
@@ -5175,6 +5187,7 @@ public:
}
bool type_can_have_auto_increment_attribute() const { return true; }
uint32 max_display_length(const Item *item) const { return 53; }
+ uint32 Item_decimal_notation_int_digits(const Item *item) const { return 309; }
uint32 calc_pack_length(uint32 length) const { return sizeof(double); }
Item *create_typecast_item(THD *thd, Item *item,
const Type_cast_attributes &attr) const;
View Lorry Controller Status