"I suggested you start a discussion about your wish that dialect 1 would not be dropped" Oh, sorry for the misunederstanding.
After your comment on the GitHub issue I understand how the calcuation works. The problem: why the storage format has affect on calculation. It just feels wrong. I did not suggesed to throw away current method, I understand the concept of backward compability. I asked for a new configuration. In dialect 1 you have to round always because of the float format storage. In dialect 3 you have to cast to double always for not to loose precision. So both dialect you have to write unnecessary boilerplace code. If I want divide, it just not enough to write a divide. I want a mode, where calcuation do not require boilerplate round or cast: - numeric storage format is like dialect 3: exact value stored - calcuations are made in double precision float like dialect 1, OR made as in dialect 3, but with higher precision, and the rounding happens automatically when you assign/store a double value to a numeric. What is the point of dialect 3 when it does not spare work, it just replace one boilerplate code writing to an other? (The project is ~20 years old started with Delphi 5, which did not supported dialect 3, so dialect 1 was not a deliberate choice, but the only option.) Attila -----Eredeti üzenet----- Feladó: Mark Rotteveel [mailto:m...@lawinegevaar.nl] Küldve: 2021. augusztus 31., kedd 19:02 Címzett: firebird-devel@lists.sourceforge.net Tárgy: Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed On 31-08-2021 16:19, Molnár Attila wrote: > 127.13 / 3.4618 = 36,72366976717315 It isn't, taken literally in dialect 3, the result is 36,723669 ;). > EXECUTE BLOCK > RETURNS ( > c0 NUMERIC(15,10), > c1 NUMERIC(15,10), > c2 NUMERIC(15,6), > c3 NUMERIC(15,5), > c4 NUMERIC(15,4)) > AS > DECLARE VARIABLE a NUMERIC(15,2); > DECLARE VARIABLE b NUMERIC(15,4); > BEGIN > a = 127.13; > b = 3.4618; > c0 = a / CAST(b AS DOUBLE PRECISION);--36,7236697672, this is OK > c1 = a / b;--36,7236690000, this shoud be 36,7236700000 > c2 = a / b;--36,723669, this shoud be 36,723670 > c3 = a / b;--36,72367, this is OK > c4 = a / b;--36,7237, this is OK > SUSPEND; > END > > If return variable scale is bigger or equal than operation result scale, > then computed value is truncated, but when return variable scale is > smaller than operation result scale then computed value is rounded. > I think in all case value should be rounded, this behaviour is > inconsistent, and also truncation is unexpected behaviour. > > I opened a GitHub issue , but was closed : > https://github.com/FirebirdSQL/firebird/issues/6928 > <https://github.com/FirebirdSQL/firebird/issues/6928> > Mark suggested to start a conversation here. I didn't suggest you start a discussion about this behaviour, I suggested you start a discussion about your wish that dialect 1 would not be dropped ("If you want to discuss retention of dialect 1, then please take that to firebird-devel."). This has been the behaviour of NUMERIC/DECIMAL division in dialect 3 since InterBase 6.0. Changing that behaviour now would break or invalidate all applications that have been built with dialect 3 semantics of NUMERIC/DECIMAL division in mind. It complies with the SQL standard. Though to be honest, that is a pretty low bar, as the specification doesn't specify much for NUMERIC/DECIMAL division other than that precision and rounding is implementation specific ("iv) The precision and scale of the result of division are implementation-defined." - SQL:2016-2, 6.29 <numeric value expression>) > #1 : This behaviour should be documented, I found nothing regarding > this, and this result is unexpected. Calculation do not happens in > double precision, and calculation stops when result scale is reached. The result is unexpected *to you* and does not match what you've been relying on in dialect 1. Yes, it is not explicitly documented other than the resulting precision, but neither is the behaviour of division in dialect 1 other than that it results in a DOUBLE PRECISION. You can think of NUMERIC/DECIMAL division in dialect 3 as a form of integer division like in most programming language. That also stops when the final digit has been calculated. For example, 14/3 = 4, not 5, 14.0/3 = 4.6, not 4.7, 14.0/3.0 = 4.66, not 4.67, etc, in dialect 1, all those calculations produce 4.666666666666667. For dialect 3, you can think of 14.0/3.0 as doing 14000/30 and then shifting the decimal by two positions (14000/30 = 466, shift decimal by 2 = 4.66). You haven't brought forth any real arguments why 21+ years of dialect 3 behaviour should be thrown out the window, other than that you don't like it because it isn't the same as in dialect 1. I understand this is a bitter pill, but this difference is likely the reason why you've been using dialect 1 all this time. > #2 : Calculation method should be cofigurable > #2/A: possible configuration modes > > -default: current mode > > -double precision: operands converted to double, calculations happens in > double, then result rounded and converted back to result type If you want such behaviour, then you need to either stay on dialect 1, or cast one of the operands explicitly to double precision. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
