No worries, Victor :) 2018-02-27 16:32 GMT+01:00 <[email protected]>:
> So simple (and so dumb) :| > > Sorry for the noise and thanks for the link! > > Victor > > Le mardi 27 février 2018 16:29:53 UTC+1, Lukas Eder a écrit : >> >> Hi Victor, >> >> Well, I hope you will draw as a conclusion from this experience, that you >> should never use double or any other floating point type for monetary >> amounts again, neither in Java, nor in SQL :-) >> >> The correct type is DECIMAL or NUMERIC. See also: >> https://stackoverflow.com/q/15726535/521799 >> >> Cheers, >> Lukas >> >> 2018-02-27 12:12 GMT+01:00 <[email protected]>: >> >>> Hi, >>> >>> I'm facing something strange and I don't know how to find the culprit. >>> >>> I know it happens between jOOQ and PostgreSQL 10. >>> >>> Basically I have this query : >>> select sum("price"), "line"."code" >>> from "public"."line" >>> where "public"."line"."date" between date '2016-09-01' and date >>> '2017-08-31' >>> group by "line"."code" >>> >>> Or in java >>> context >>> .select(DSL.sum(priceField)) >>> .select(grouping.fields()) >>> .from(Tables.LINE) >>> .where(filter) >>> .groupBy(grouping.fields()) >>> >>> "price" has the type "double precision" in PostgreSQL. >>> >>> There is exactly one row (of the group by) for which, if I print on the >>> console the record, sometimes it looks like this: >>> +----+------------------+ >>> | sum| code | >>> +----+------------------+ >>> | 0 | ArticleCode_3436 | >>> +----+------------------+ >>> >>> And sometimes like this: >>> +------------------------+------------------+ >>> | sum | code | >>> +------------------------+------------------+ >>> | 1.1368683772161603E-13 | ArticleCode_3436 | >>> +------------------------+------------------+ >>> >>> It is not clear what triggers one or the other output, maybe concurrent >>> requests or something like that in the application… >>> >>> In the database, there are 4 rows matching this particular "code", they >>> contains for "price" : 284.81, -284.81, 569.62, -569.62, hence the 0 result. >>> >>> So either it is a problem with the database, with the way I configured >>> the schema or it is a problem with the way jOOQ convert the result to >>> BigDecimal >>> >>> I don't know where to look now to find the reason for this situation. >>> >>> Thanks a lot for any help :) >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "jOOQ User Group" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
