Redshift ROUND function doesn't round in some cases?

Question

I can find a workaround, but it is really annoying and I may certainly be missing something. Redshift's ROUND function doesn't round to the number of decimals specified.

For example,

select round(cast(176 as float)/cast(492 as float),4) as result;

Above select statement will return 0.35769999999999996.

However, this statement:

select round(cast(229 as float)/cast(491 as float),4) as result;

... will return 0.4664.

Why? I can work around this, but seems like it should work and return only four decimal places.

Battista

229/491 = 0.46639511201 . . . The answer in Redshift looks correct to me. – Oconner

Answer 1

If your issues is all those 9999s, then the issue is floating point representation. Convert to a decimal to get fixed-point precision:

select round(cast(176 as float)/cast(492 as float), 4)::decimal(10, 4) as result;

Oconner

That does work. I guess I view that as an alternative to ROUND. Your solution doesn't require a ROUND. I'll just assume that ROUND has a bug but I find it strange. Thanks for the suggestion. – Battista

@MarkEvans . . . ROUND() is fine. The issue is the floating point representation, which is tricky -- but rarely needed. – Oconner

I'm really curious how ROUND() is fine? Not being sarcastic. Is it not supposed to return a number rounded to the decimal places specified? Even a type float? Many of the values do return rounded to 4 places, but scattered in are those that are not. This is the intended behavior of ROUND? For my purposes, ROUND isn't useful to do what the documentation describes. I'm casting into a decimal type instead. – Battista

just adding this from the AWS page on ROUND: "INTEGER, DECIMAL, and FLOAT data types are supported." – Battista

Answer 2

Elaborating more on Gordon's answer -

So you’ve written some absurdly simple code, say for example:

0.1 + 0.2

and got a really unexpected result:

0.30000000000000004

Because internally, computers use a format (binary floating-point) that cannot accurately represent a number like 0.1, 0.2 or 0.3 at all.

When the code is compiled or interpreted, your “0.1” is already rounded to the nearest number in that format, which results in a small rounding error even before the calculation happens.

What can I do to avoid this problem?

That depends on what kind of calculations you’re doing.

• If you really need your results to add up exactly, especially when you work with money: use a decimal datatype.
• If you just don’t want to see all those extra decimal places: simply format your result rounded to a fixed number of decimal places when displaying it.

Shamelessly stolen from : Floating Point

Saxton

Answer 3

try multiplying by 10 to the power of your desired places after the decimal point, rounding, and then dividing it out again:

-- exclude decimal point inside ROUND(), include outside ROUND()
SELECT ROUND(10000 * 176 / 492) / 10000.0

which will return the expected 0.3577.

Columbous

Answer 4

The "Why?" has been answered, and at time of writing AWS says ROUND handles floats, so the behaviour highlighted by the OP should probably be labelled a bug.

Here's an alternative work-around that hasn't been explicitly mentioned:

Cast your numerator or denominator to DECIMAL, and ROUND works as expected.

select ROUND(176::DECIMAL / 492, 4) as result;

Lucy