Add rounding mode parameter to ROUND to control rounding behaviour

[r1gm]

Round already have scale, shouldn't Floor and Ceiling also have scale parameter/function ?

[mrotteveel]

No, floor and ceiling are operations that return a whole number. This is specified in the SQL:2023 standard (ISO/IEC 9075-2:2023):

— <floor function> computes the greatest integer less than or equal to its argument; — <ceiling function> computes the least integer greater than or equal to its argument;

with:

<floor function> ::=
  FLOOR <left paren> <numeric value expression> <right paren>
<ceiling function> ::=
  { CEIL | CEILING } <left paren> <numeric value expression> <right paren>

The function ROUND on the other hand is not specified by the SQL standard.

What you seem to be looking for is a way to configure the rounding behaviour of ROUND, maybe similar to how you can configure rounding behaviour of operations on DECFLOAT, using SET DECFLOAT ROUND?

I would suggest an (optional) parameter to ROUND to configure this with the <round_mode> values also used for SET DECFLOAT ROUND.

[mrotteveel]

I'm not sure if I should keep this open, or close it and create a new one with a request for a round-mode for ROUND.

[sim1984]

I don't think that strictly following the standard is useful in this case. Many databases extend the standard features, I think we should look at how things are in other databases with these functions.

[mrotteveel]

@sim1984 My point is that floor and ceiling have very specific meaning, so deviating from that is not a good idea. What I think the OP actually wants to do is control how ROUND behaves (as in, rounding mode "floor" or "ceiling"), which sounds useful to me in general (especially if also supporting the other rounding modes). So, adding a round-mode parameter to ROUND, which is already non-standard, seems to be a more constructive approach than modifying floor and ceiling to deviate from their standard-specified behaviour.

[aafemt]

If the standard and Firebird describe floor and ceil as functions returning integer value, they technically cannot have precision parameter because it would require decimal or float result. I would suggest to close this ticket and leave it to OP to open a new one for round (may be cast) function functionality expansion.

[r1gm]

thank you for explanation

i also agree with (optional) parameter <round_mode> in ROUND and i think that more better solution even though more typing required. i leave this matter to more capable person.

Best Regards