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 <firebird-devel@lists.sourceforge.net> 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