Description:
FORMAT(X, D) clamps a large D to the maximum number of decimals (30): both
FORMAT(1.5, 40) and FORMAT(1.5, 2147483647) return 1.5 with 30 decimals.
But D is first cast to int, so a D of 2^31 or more is taken modulo 2^32:
- D = 4294967298 (2^32 + 2) formats with 2 decimals: '1.50';
- D = 2147483648 (2^31) becomes negative and formats with 0 decimals: '2';
- D = 4294967336 (2^32 + 40) is clamped to 30 only because 40 > 30;
- the same happens with a BIGINT column as D, and with the 3-argument
form FORMAT(X, D, locale).
No warning is given. ROUND() and TRUNCATE(), which also take a decimals
argument, were fixed for huge D by Bug #24912: ROUND(1.5, 4294967298) is 1.5.
Cause: sql/item_strfunc.cc, Item_func_format::val_str_ascii(), trunk 3b99be40:
int dec = (int)args[1]->val_int();
...
dec = set_zone(dec, 0, FORMAT_MAX_DECIMALS);
The (int) cast keeps the low 32 bits before set_zone() clamps to 0..30.
How to repeat:
SELECT FORMAT(1.5, 2) AS d2, FORMAT(1.5, 4294967298) AS d2p32_2,
FORMAT(1.5, 2147483648) AS d2p31, FORMAT(1.5, -1) AS dm1;
+------+---------+-------+------+
| d2 | d2p32_2 | d2p31 | dm1 |
+------+---------+-------+------+
| 1.50 | 1.50 | 2 | 2 |
+------+---------+-------+------+
SELECT LENGTH(FORMAT(1.5, 40)) AS len_d40,
LENGTH(FORMAT(1.5, 2147483647)) AS len_d2p31m1;
+---------+-------------+
| len_d40 | len_d2p31m1 |
+---------+-------------+
| 32 | 32 |
+---------+-------------+
SELECT FORMAT(1234567.891, 4294967297, 'de_DE') AS de_d2p32_1,
FORMAT(1234567.891, 1, 'de_DE') AS de_d1;
+-------------+-------------+
| de_d2p32_1 | de_d1 |
+-------------+-------------+
| 1.234.567,9 | 1.234.567,9 |
+-------------+-------------+
SELECT ROUND(1.5, 4294967298) AS round_d2p32_2, TRUNCATE(1.5, 4294967298) AS trunc_d2p32_2;
+---------------+---------------+
| round_d2p32_2 | trunc_d2p32_2 |
+---------------+---------------+
| 1.5 | 1.5 |
+---------------+---------------+
CREATE TABLE t016 (d BIGINT);
INSERT INTO t016 VALUES (2), (4294967298), (2147483648);
SELECT d, FORMAT(1.5, d) AS f FROM t016 ORDER BY d;
+------------+------+
| d | f |
+------------+------+
| 2 | 1.50 |
| 2147483648 | 2 |
| 4294967298 | 1.50 |
+------------+------+
Expected: any D above 30 gives 30 decimals, as for D = 40 and D =
2147483647. FORMAT(1.5, 4294967298) and FORMAT(1.5, 2147483648) should
both be '1.500000000000000000000000000000'.
Suggested fix:
Clamp before narrowing, e.g.
const longlong d = args[1]->val_int();
int dec = (d < 0 && !args[1]->unsigned_flag) ? 0
: (d > FORMAT_MAX_DECIMALS || (d < 0 && args[1]->unsigned_flag))
? FORMAT_MAX_DECIMALS : static_cast<int>(d);
(the unsigned_flag check keeps D >= 2^63 from being read as negative).
Description: FORMAT(X, D) clamps a large D to the maximum number of decimals (30): both FORMAT(1.5, 40) and FORMAT(1.5, 2147483647) return 1.5 with 30 decimals. But D is first cast to int, so a D of 2^31 or more is taken modulo 2^32: - D = 4294967298 (2^32 + 2) formats with 2 decimals: '1.50'; - D = 2147483648 (2^31) becomes negative and formats with 0 decimals: '2'; - D = 4294967336 (2^32 + 40) is clamped to 30 only because 40 > 30; - the same happens with a BIGINT column as D, and with the 3-argument form FORMAT(X, D, locale). No warning is given. ROUND() and TRUNCATE(), which also take a decimals argument, were fixed for huge D by Bug #24912: ROUND(1.5, 4294967298) is 1.5. Cause: sql/item_strfunc.cc, Item_func_format::val_str_ascii(), trunk 3b99be40: int dec = (int)args[1]->val_int(); ... dec = set_zone(dec, 0, FORMAT_MAX_DECIMALS); The (int) cast keeps the low 32 bits before set_zone() clamps to 0..30. How to repeat: SELECT FORMAT(1.5, 2) AS d2, FORMAT(1.5, 4294967298) AS d2p32_2, FORMAT(1.5, 2147483648) AS d2p31, FORMAT(1.5, -1) AS dm1; +------+---------+-------+------+ | d2 | d2p32_2 | d2p31 | dm1 | +------+---------+-------+------+ | 1.50 | 1.50 | 2 | 2 | +------+---------+-------+------+ SELECT LENGTH(FORMAT(1.5, 40)) AS len_d40, LENGTH(FORMAT(1.5, 2147483647)) AS len_d2p31m1; +---------+-------------+ | len_d40 | len_d2p31m1 | +---------+-------------+ | 32 | 32 | +---------+-------------+ SELECT FORMAT(1234567.891, 4294967297, 'de_DE') AS de_d2p32_1, FORMAT(1234567.891, 1, 'de_DE') AS de_d1; +-------------+-------------+ | de_d2p32_1 | de_d1 | +-------------+-------------+ | 1.234.567,9 | 1.234.567,9 | +-------------+-------------+ SELECT ROUND(1.5, 4294967298) AS round_d2p32_2, TRUNCATE(1.5, 4294967298) AS trunc_d2p32_2; +---------------+---------------+ | round_d2p32_2 | trunc_d2p32_2 | +---------------+---------------+ | 1.5 | 1.5 | +---------------+---------------+ CREATE TABLE t016 (d BIGINT); INSERT INTO t016 VALUES (2), (4294967298), (2147483648); SELECT d, FORMAT(1.5, d) AS f FROM t016 ORDER BY d; +------------+------+ | d | f | +------------+------+ | 2 | 1.50 | | 2147483648 | 2 | | 4294967298 | 1.50 | +------------+------+ Expected: any D above 30 gives 30 decimals, as for D = 40 and D = 2147483647. FORMAT(1.5, 4294967298) and FORMAT(1.5, 2147483648) should both be '1.500000000000000000000000000000'. Suggested fix: Clamp before narrowing, e.g. const longlong d = args[1]->val_int(); int dec = (d < 0 && !args[1]->unsigned_flag) ? 0 : (d > FORMAT_MAX_DECIMALS || (d < 0 && args[1]->unsigned_flag)) ? FORMAT_MAX_DECIMALS : static_cast<int>(d); (the unsigned_flag check keeps D >= 2^63 from being read as negative).