Type inferred from arithmetics between BigDecimal and double is incorrect?
Description
Activity
Gavin King April 29, 2024 at 4:58 PM
I think the only sensible way to deal with this would be to automatically add an explicit cast to double precision when a literal of type Double is multiplied/divided/added/subtracted to/from a BigDecimal.
Gavin King April 29, 2024 at 4:05 PM
Hah! Interesting. So casting numeric to varchar appends trailing zeros, not only on postgres, but also on mysql and h2. Maybe on other SQL dialects too??
It’s definitely not a bug per se, but for consistency, perhaps we should explicitly cast expressions of type Double to double precision before casting them to String.
Interesting.
Victor Noël April 29, 2024 at 3:37 PM
Instead of explaining your actual problem, you jumped straight to proposing an (incorrect) solution.
Yes, my bad, sorry about that.
I guess it’s that the generated SQL for the
round()function is usingfloor(1e2*x+0.5)/1e2instead ofround(x,2)but why is that an actual problem?
Well, in my exact particular case, the problem is that I convert the result of that rounding to text and I end up with a lot of trailing zeros in the result:
I suspect this is not a bug in itself (but maybe it is?) and that’s why I didn’t report it initially ^^
My second more generic problem is that I think it’s strange and incorrect for Hibernate to generate some very complex arithmetic formula when it should be able to use the native round. I guess this is more an improvement request than a bug?
Gavin King April 29, 2024 at 3:13 PMEdited
I guess it’s that the generated SQL for the round() function is using floor(1e2*x+0.5)/1e2 instead of round(x,2) but why is that an actual problem?
Gavin King April 29, 2024 at 3:10 PM
I mean I guess I don’t understand what it even is that you’re bothered by.
Instead of explaining your actual problem, you jumped straight to proposing an (incorrect) solution.
Details
Details
Assignee
Reporter
Victor NoëlComponents
Priority
Major
Hi,
I have a custom function registered in my dialect (extending PostgreSQLDialect) as follows:
And I noticed that when I used round with it in HQL, e.g.,
round(cast_to_numeric("55.00")*1.015, 2)then in PostgreSQL, the resulting SQL was using the emulated round implementation fromPostgreSQLTruncRoundFunctionusingfloor. From reading the code and debugging I understand that it is because the arithmetic expressioncast_to_numeric("55.00")*1.015is not considered as a decimal but as a double.But I would have expected that a multiplication between
BigDecimaland any number would result inBigDecimal, and that is indeed the behaviour in PostgreSQL:pg_typeof(cast("50.22" as numeric) * 1.0003)isnumeric, notdouble! And thus round can be used safely without resorting to the emulated floor-based implementation.Am I missing something or is that a bug?