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

[Dmitry Yemanov]

02.09.2021 12:17, Mark Rotteveel wrote:

I find the suggestion to make this configurable an interesting one, but 
this wouldn't fundamentally resolve the issue you have, it just would 
change the compound error of calculation. For example, we followed your 
suggestion and provide an option to use double precision semantics for 
division or change the calculation so it provides half-up rounding 
instead of - effectively - floor rounding), then the result of 2.00 / 
3.00 would be 0.6667 (what you want), but the result of - for example - 
2.00 / 3.00 * 100 would be 66.6700, and I guess you would expect it to 
be 66.6667 and would prefer if the entire calculation happens in double 
precision (which brings along its own host of problems, because 
fundamentally double precision is not usable for exact calculations and 
has its own set of precision problems and compound errors depending on 
order of evaluation and actual values).


Let's discuss possible options and what benefits they would bring us. 
Being compatible with other RDBMS vendors may also count as a benefit ;-)


With all that said, I suggest you create a feature request ticket 
("type: new feature") for making it configurable, so it can be tracked 
and evaluated (and voted on by the community), but I wouldn't hold my 
breath (though I'm not a Firebird core developer, which means this is 
not my decision).


I agree to consider [configurable] alternatives to how numeric division 
is handled.



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

[Mark Rotteveel]

On 02-09-2021 10:08, Omacht András wrote:

Summary: for our point of view mysql and oracle is the easiest to use as a 
programmer (and maybe a data analyst who has the right to write queries).
Postre, mssql bring the solution what at least we expected and what Attila 
outlined.

Firebird is working completly different as others even if you divide two 
numeric values.


Yes, and that is because in those systems, the scale of the result of 
division is different from Firebird (they generally either have more 
digits, or possibly use floating point as well, I haven't verified this).


If you had changed the order of calculation to n1 * 100 / n2 you would 
get 66.67 in Firebird (because then the result is 66., rounded to 
66.67 when fit into scale 2, while in the initial calculation, you first 
get n1/n2 = 0., 0. * 100 = 66.6600, fit into scale 2 = 66.66).


Or n1 / (n2/100), as you'd get 2.00 / (3.00/100) is 2.00/0.03 is 
66., fit in scale 2 is 66.67


And yes, I agree this is something that needs getting used to, or 
requires you to fiddle with order of evaluation and things)


But so far, you just keep repeating that the results don't match your 
expectations, but this is well-known and has been like this for 21+ 
years. That in and of itself is not a reason to change it, nor for the 
project to spend valuable development time to make this configurable 
(and as a result increase complexity of the codebase).


I think we need a more solid, real world use case and argument for why 
this would need to be added.


I find the suggestion to make this configurable an interesting one, but 
this wouldn't fundamentally resolve the issue you have, it just would 
change the compound error of calculation. For example, we followed your 
suggestion and provide an option to use double precision semantics for 
division or change the calculation so it provides half-up rounding 
instead of - effectively - floor rounding), then the result of 2.00 / 
3.00 would be 0.6667 (what you want), but the result of - for example - 
2.00 / 3.00 * 100 would be 66.6700, and I guess you would expect it to 
be 66.6667 and would prefer if the entire calculation happens in double 
precision (which brings along its own host of problems, because 
fundamentally double precision is not usable for exact calculations and 
has its own set of precision problems and compound errors depending on 
order of evaluation and actual values).


With all that said, I suggest you create a feature request ticket 
("type: new feature") for making it configurable, so it can be tracked 
and evaluated (and voted on by the community), but I wouldn't hold my 
breath (though I'm not a Firebird core developer, which means this is 
not my decision).


Mark
--
Mark Rotteveel


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

[Dmitry Yemanov]

31.08.2021 23:53, Mark Rotteveel wrote:

The only debatable feature of dialect 3 division is the fact the 
calculation stops (equivalent to floor rounding), while reduction of 
scale through assignment or cast applies half-up rounding


"Whether to round or truncate when performing division is 
implementation-defined." (c) SQL spec ;-)


However, being compliant does not necessarily mean being useful, the 
standard leaves too many gaps here.


this behaviour is consistent with integer division, otherwise 
NUMERIC(18,0) division and BIGINT division would have to behave 
differently (e.g. 14/3 = 5 for NUMERIC(18,0) vs = 4 for BIGINT), which 
would be confusing as hell


This is what PostgreSQL does. They use integral division only if both 
arguments are integers (not numerics!). However, this is not very 
confusing for them, because their "implementation-defined scale" is 
always the maximum possible (instead of our scaleA+scaleB), so they get:


select 14::bigint / 3::bigint = 4
select 14::numeric(18, 0) / 3::numeric(18, 0) = 4.667

the latter may produce 5 when casted to BIGINT explicitly, but from 
another side it will produce 4.67 when casted to NUMERIC(18, 2).



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]

We made a quick review with http://sqlfiddle.com

Numeric test:
create table teszt01(n1 numeric(15,2), n2 numeric(15,2), n3 numeric(15,2));
insert into teszt01(n1, n2) values (1, 3);
insert into teszt01(n1, n2) values (2, 3);
update teszt01 set n3 = n1 / n2 * 100;
select * from teszt01;

Integer test:
create table teszt02(n1 integer, n2 integer, n3 numeric(15,2));
insert into teszt02(n1, n2) values (1, 3);
insert into teszt02(n1, n2) values (2, 3);
update teszt02 set n3 = n1 / n2 * 100;
select * from teszt02;

Mixed test:
create table teszt03(n1 integer, n2 numeric(15,1), n3 numeric(15,2)); -- 
Note, numeric(15,1) not 15,2!
insert into teszt03(n1, n2) values (1, 3);
insert into teszt03(n1, n2) values (2, 3);
update teszt03 set n3 = n1 / n2 * 100;
select * from teszt03;

Firebird (D3):
Numeric:
33.33, 66.66
Integer:
0, 0
Mixed:
30.00, 60.00

Oracle:
Numeric:
33.33, 66.67
Integer:
33.33, 66.67
Mixed:
33.33, 33.67

Mysql:
Numeric:
33.33, 66.67
Integer:
33.33, 66.67
Mixed:
33.33, 66.67 -- MSSQL handle this even n2 is numeric(15,1)!

MSSQL:
Numeric:
33.33, 66.67
Integer:
0, 0
Mixed:
33.33, 66.67

Postgre:
Numeric:
33.33, 66.67
Integer:
0, 0
Mixed:
33.33, 66.67 -- Postre handle this even n2 is numeric(15,1)!

Summary: for our point of view mysql and oracle is the easiest to use as a 
programmer (and maybe a data analyst who has the right to write queries).
Postre, mssql bring the solution what at least we expected and what Attila 
outlined.

Firebird is working completly different as others even if you divide two 
numeric values.

András



-Original Message-
From: Omacht András [mailto:omacht.and...@libra.hu] 
Sent: Wednesday, September 1, 2021 6:14 PM
To: For discussion among Firebird Developers 

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

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

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

[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 
> <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 understa

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

[Vlad Khorsun]

31.08.2021 23:53, Mark Rotteveel wrote:

The only debatable feature of dialect 3 division is the fact the calculation stops (equivalent to floor rounding), while reduction 
of scale through assignment or cast applies half-up rounding, on the other hand, this behaviour is consistent with integer division, 
otherwise NUMERIC(18,0) division and BIGINT division would have to behave differently (e.g. 14/3 = 5 for NUMERIC(18,0) vs = 4 for 
BIGINT), which would be confusing as hell, though technically you could solve that by applying the "The precision and scale of the 
result of division are implementation-defined." (there is no requirement that BIGINT/BIGINT is BIGINT or NUMERIC/DECIMAL with a 
scale of 0).


  Below is comment from code ArithmeticNode::divide2() at 
src\dsql\ExprNodes.cpp:

 * In the SQL standard, the precision and scale of the quotient of exact
 * numeric dividend and divisor are implementation-defined: we have 
defined
 * the precision as 18 (in other words, an SINT64), and the scale as the
 * sum of the scales of the two operands.  To make this work, we have to
 * multiply by pow(10, -2* (scale of divisor)).
...
 * To maximize the amount of information in the result, we scale up
 * the dividend as far as we can without causing overflow, then we 
perform
 * the division, then do any additional required scaling.

So, 127.13 / 3.4618 evaluates in a following way:

127.13 is NUMERIC(5, 2) with value 12713 and scale -2
3.4618 is NUMERIC(5, 4) with value 34618 and scale -4

12713 * 10^8 / 34618 = 36723669 (integer division, no rounding)

Result is NUMERIC(18, 6) with value 36723669 and scale -6, or 36.723669

Hope it helps,
Vlad


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-08-31 21:10, Dimitry Sibiryakov wrote:

Mark Rotteveel wrote 31.08.2021 19:05:

   What does SQL standard say about it?


Not much, beyond "iv) The precision and scale of the result of 
division are implementation-defined."


  So theoretically result of division of two exact numbers can be
inexact number like DOUBLE PRECISION but it has not enough precision
to make 1/3*3=1 anyway.


No, the result of the division between two exact numeric values must be 
an exact numeric value ("the declared type of both operands of a dyadic 
arithmetic operator is exact numeric and the declared type of the result 
is an implementation-defined exact numeric type", same section of 
SQL:2016-2).


The dialect 1 behaviour is not compliant because the result is not an 
exact numeric value, but an approximate numeric value (double 
precision).


The only debatable feature of dialect 3 division is the fact the 
calculation stops (equivalent to floor rounding), while reduction of 
scale through assignment or cast applies half-up rounding, on the other 
hand, this behaviour is consistent with integer division, otherwise 
NUMERIC(18,0) division and BIGINT division would have to behave 
differently (e.g. 14/3 = 5 for NUMERIC(18,0) vs = 4 for BIGINT), which 
would be confusing as hell, though technically you could solve that by 
applying the "The precision and scale of the result of division are 
implementation-defined." (there is no requirement that BIGINT/BIGINT is 
BIGINT or NUMERIC/DECIMAL with a scale of 0).


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

[Dimitry Sibiryakov]

Mark Rotteveel wrote 31.08.2021 19:05:

   What does SQL standard say about it?


Not much, beyond "iv) The precision and scale of the result of division are 
implementation-defined."


  So theoretically result of division of two exact numbers can be inexact 
number like DOUBLE PRECISION but it has not enough precision to make 1/3*3=1 anyway.


--
  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

[Mark Rotteveel]

On 31-08-2021 16:59, Dimitry Sibiryakov wrote:

Molnár Attila wrote 31.08.2021 16:19:
I think in all case value should be rounded, this behaviour is 
inconsistent, and also truncation is unexpected behaviour.


   What does SQL standard say about it?


Not much, beyond "iv) The precision and scale of the result of division 
are implementation-defined."


Mark
--
Mark Rotteveel


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 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 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

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

[Dimitry Sibiryakov]

Molnár Attila wrote 31.08.2021 16:19:
I think in all case value should be rounded, this behaviour is inconsistent, and 
also truncation is unexpected behaviour.


  What does SQL standard say about it?

--
  WBR, SD.


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