MDEV-20732 MDB now correctly estimates a length of the FORMAT() result for

doubles in scientific notation with a big integer part.

1 files changed, 710 insertions, 0 deletions

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 #