Date: 2020-08-21 12:44:39 +0200
From: @Mytherin
To: SQL devs <>
Version: 11.37.11 (Jun2020-SP1)
CC: @njnes

Last updated: 2020-10-19 11:06:21 +0200

Comment 28001

Date: 2020-08-21 12:44:39 +0200
From: @Mytherin

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1.2 Safari/605.1.15
Build Identifier:

Rounding a decimal with a non-scalar precision gives an incorrect result. The correct result is obtained when a scalar precision is used.

Consider the following SQL statements:

CREATE TABLE decimals(d DECIMAL(4,1), prec INTEGER);
INSERT INTO decimals VALUES ('999.9', 0);
SELECT ROUND(d, prec) FROM decimals;
-- expected: 1000, actual: 100
SELECT ROUND(CAST(999.9 AS DECIMAL(4,1)), 0);
-- correct result: 1000
SELECT ROUND(d, 0) FROM decimals;
-- correct result: 1000.0
SELECT ROUND(d, 0.1) FROM decimals;
-- expected: error or 1000.0, got 100.0
SELECT ROUND(CAST(999.9 AS DECIMAL(4,1)), 0.1);
-- same result: got 100.0

Reproducible: Always

Comment 28008

Date: 2020-08-24 13:28:47 +0200
From: @njnes

fixed type conversion in case of round(dec, dec).