| Bug #83699 | Wrong calculation of decimals after point for IFNULL w/ GROUP BY using tmp tbl | ||
|---|---|---|---|
| Submitted: | 4 Nov 2016 21:18 | Modified: | 13 Oct 2017 17:36 |
| Reporter: | Dag Wanvik | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Oct 2017 17:36]
Paul DuBois
Posted by developer: Fixed in 8.0.4, 9.0.0. IFNULL(decimal, int) could lose a digit after the decimal point when used in a query that included GROUP BY and was executed using a temporary table.
Description: IFNULL and IF should produce similar result in the example below; they do not. How to repeat: CREATE TABLE t (i1 INT, d1 DOUBLE, e2 DECIMAL(5,2)); INSERT INTO t VALUES ( 6, 6.0, 10.0/3), ( null, 9.0, 10.0/3), ( 1, null, 10.0/3), ( 2, 2.0, null ); --echo IFNULL and IF should yield same number for decimals after point here SELECT IFNULL(e2,i1) `ifnull`, IF(e2 IS NULL,i1,e2) `if`, SUM(d1) FROM t GROUP BY e2,i1 ORDER BY `ifnull`; +--------+------+---------+ | ifnull | if | SUM(d1) | +--------+------+---------+ | 2.0 | 2.00 | 2 | | 3.3 | 3.33 | 6 | | 3.3 | 3.33 | 9 | | 3.3 | 3.33 | NULL | +--------+------+---------+ As can be seen, the ifnull gets one decimal less that the if.