Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Dmitry Yemanov]

01.09.2021 09:29, Molnár Attila wrote:


In dialect 3 you have to cast to double always for not to loose precision.


I believe you may avoid the casts if you store everything as NUMERIC 
inside the db, but your procedures use DOUBLE (or better DECFLOAT) for 
inputs/outputs/locals.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Omacht András]

Alex,

http://www.firebirdsql.org/pdfmanual/html/isql-dialects.html

"Currently it is possible to create databases in Dialect 1 and 3 only, however 
it is recommended that you use Dialect 3 exclusively, since Dialect 1 will 
eventually be deprecated."


http://www.firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html

Deprecation of Legacy SQL Dialect 1
Starting with Firebird 4, Dialect 1 is declared deprecated. Its support will be 
removed in future Firebird versions, with Dialect 3 becoming the only dialect 
supported. Please consider migrating to Dialect 3 as soon as possible.

András

-Original Message-
From: Alex Peshkoff via Firebird-devel 
[mailto:firebird-devel@lists.sourceforge.net] 
Sent: Wednesday, September 1, 2021 6:20 PM
To: firebird-devel@lists.sourceforge.net
Cc: Alex Peshkoff 
Subject: Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable 
calculation method needed

On 9/1/21 7:13 PM, Omacht András wrote:
> (Or please support D1 and don’t force developers to rewrite existing 
> codebase.)

May be I've missed something - but did we have plans to remove Dialect 1? It 
seems that not.




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

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Mark Rotteveel]

On 2021-09-01 18:20, Alex Peshkoff via Firebird-devel wrote:

On 9/1/21 7:13 PM, Omacht András wrote:
(Or please support D1 and don’t force developers to rewrite existing 
codebase.)


May be I've missed something - but did we have plans to remove Dialect
1? It seems that not.


Technically, removal was already announced with InterBase 6.0, and it 
has been brought up several times, and Firebird 4 Release Notes repeats 
deprecation again: 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-compat-dialect1


Mark


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Alex Peshkoff via Firebird-devel]

On 9/1/21 7:13 PM, Omacht András wrote:

(Or please support D1 and don’t force developers to rewrite existing codebase.)


May be I've missed something - but did we have plans to remove Dialect 
1? It seems that not.





Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Omacht András]

Mark,

before Attila started this discussion we asked a development colleague who has 
also been developing Firebird-based systems for a long time.

Here is his sort answer:

"The truth is that we sucked it with D3 15 years ago. Therefore, when we 
started the new system, we released the numeric type and almost everything goes 
under double. At least the Forint and foreign currency amounts, etc."

And, we develop ERP systems with numbers, numbers and numbers...

All, please consider the solution proposed by Attila because it could save a 
lot of time (and brain cells) for the developers.

(Or please support D1 and don’t force developers to rewrite existing codebase.)

Thanks.

András

-Original Message-
From: Mark Rotteveel [mailto:m...@lawinegevaar.nl] 
Sent: Wednesday, September 1, 2021 5:46 PM
To: firebird-devel@lists.sourceforge.net
Subject: Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable 
calculation method needed

On 2021-09-01 15:40, Molnár Attila wrote:
> Ok, I understand it's not "wrong", works as designed/intended. Just 
> put into documentation.
> 
> Also, how do you expect to people to leave dialect 1 when dialect 3 
> gives no benefit, in both dialect extra effort needed to get 
> calculations right, plain operation and funcation usage is not enought 
> to get correct results:
> dialect 1 requires rounding everywhere because the everything is a 
> float dialect 3 requires double precition cast and rounding in case of 
> division because of low precision/scale during calculation
> 
> A new dialect or a new option should do the job.

I think most Firebird users either have no problems with the NUMERIC/DECIMAL 
behaviour in dialect 3, or have otherwise learned to live with it over the past 
21+ years.

The benefit of dialect 3 over dialect 1 is BIGINT, generators generating values 
exceeding 2^31 - 1, actual NUMERIC/DECIMAL with precision over 9 (instead of a 
DOUBLE PRECISION with a NUMERIC/DECIMAL label slapped on), TIME/DATE/TIMESTAMP 
vs DATE that is actually a TIMESTAMP, SQL standard use of single quote for 
string literals, allowing quoted object names (using double quotes), which 
allows you to have 1) case-sensitive identifiers, and 2) use reserved words as 
identifiers. I may have missed some more.

Personally, I consider the division behaviour of dialect 3 an improvement over 
dialect 1, but on the other hand I think I hardly ever use division in SQL.

Mark


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

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Mark Rotteveel]

On 2021-09-01 15:40, Molnár Attila wrote:

Ok, I understand it's not "wrong", works as designed/intended. Just
put into documentation.

Also, how do you expect to people to leave dialect 1 when dialect 3
gives no benefit, in both dialect extra effort needed to get
calculations right, plain operation and funcation usage is not enought
to get correct results:
dialect 1 requires rounding everywhere because the everything is a 
float

dialect 3 requires double precition cast and rounding in case of
division because of low precision/scale during calculation

A new dialect or a new option should do the job.


I think most Firebird users either have no problems with the 
NUMERIC/DECIMAL behaviour in dialect 3, or have otherwise learned to 
live with it over the past 21+ years.


The benefit of dialect 3 over dialect 1 is BIGINT, generators generating 
values exceeding 2^31 - 1, actual NUMERIC/DECIMAL with precision over 9 
(instead of a DOUBLE PRECISION with a NUMERIC/DECIMAL label slapped on), 
TIME/DATE/TIMESTAMP vs DATE that is actually a TIMESTAMP, SQL standard 
use of single quote for string literals, allowing quoted object names 
(using double quotes), which allows you to have 1) case-sensitive 
identifiers, and 2) use reserved words as identifiers. I may have missed 
some more.


Personally, I consider the division behaviour of dialect 3 an 
improvement over dialect 1, but on the other hand I think I hardly ever 
use division in SQL.


Mark


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Molnár Attila]

Ok, I understand it's not "wrong", works as designed/intended. Just put into 
documentation.

Also, how do you expect to people to leave dialect 1 when dialect 3 gives no 
benefit, in both dialect extra effort needed to get calculations right, plain 
operation and funcation usage is not enought to get correct results: 
dialect 1 requires rounding everywhere because the everything is a float
dialect 3 requires double precition cast and rounding in case of division 
because of low precision/scale during calculation

A new dialect or a new option should do the job.

-Eredeti üzenet-
Feladó: Dimitry Sibiryakov [mailto:s...@ibphoenix.com] 
Küldve: 2021. szeptember 1., szerda 11:27
Címzett: For discussion among Firebird Developers 

Tárgy: Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable 
calculation method needed

Molnár Attila wrote 01.09.2021 8:29:
> The problem: why the storage format has affect on calculation. It just feels 
> wrong.

   It is ordinary thing in all programming languages when data types decide 
calculation rules. In the Delphi product of integer division used to be 
floating 
point, but that's Pascal exclusive rules. SQL (including Firebird) is based 
rather on C where division of two integers produce integer.

> What is the point of dialect 3 when it does not spare work, it just replace 
> one boilerplate code writing to an other?

   If it doesn't work the way you expect it doesn't mean that it works wrong. 
Just accept that rules of arithmetic are different in different programming 
languages and perform calculations in the language which comply with your goals.

-- 
   WBR, SD.


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

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Dimitry Sibiryakov]

Molnár Attila wrote 01.09.2021 8:29:

The problem: why the storage format has affect on calculation. It just feels 
wrong.


  It is ordinary thing in all programming languages when data types decide 
calculation rules. In the Delphi product of integer division used to be floating 
point, but that's Pascal exclusive rules. SQL (including Firebird) is based 
rather on C where division of two integers produce integer.



What is the point of dialect 3 when it does not spare work, it just replace one 
boilerplate code writing to an other?


  If it doesn't work the way you expect it doesn't mean that it works wrong. 
Just accept that rules of arithmetic are different in different programming 
languages and perform calculations in the language which comply with your goals.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Molnár Attila]

"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,723669, this shoud be 36,723670
>    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 
> 
> 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 )

> #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.667. 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