Re: [Firebird-devel] Rework the error message processing for simpler and faster build
Em qui, 2 de set de 2021 16:31, Dimitry Sibiryakov escreveu: > Adriano dos Santos Fernandes wrote 02.09.2021 21:28: > > The preprocessor could be used to generate that as well. > >Another custom preprocessor in addition to CLOOP? > C/C++ preprocessor. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Rework the error message processing for simpler and faster build
Adriano dos Santos Fernandes wrote 02.09.2021 21:28: The preprocessor could be used to generate that as well. Another custom preprocessor in addition to CLOOP? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Rework the error message processing for simpler and faster build
Em qui, 2 de set de 2021 15:23, Mark Rotteveel escreveu: > > The nice thing about the current solution is that it produces a database > that is queryable, and I use that to generate the message and sqlstate > files of Jaybird, and the list of error messages and codes in the > language reference. > > To be clear, I can adjust to a new world here and parse a source file to > get the info, but maybe others rely on the database in a similar way, so > this removal of msg.fdb may have more impact beyond the build itself. > The preprocessor could be used to generate that as well. It could generate a .sql script file too. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Rework the error message processing for simpler and faster build
On 2021-09-02 17:46, Adriano dos Santos Fernandes wrote: We have still traces of the old error message processing that only complicate things. We define errors in .sql files (it was database) and then everything is as before. We have generated files in the tree, which frequently causes merge conflicts, not very simple to fix, as generated files needs to be edited. Utility that generate the files needs gpre and the engine previously built, and ready to use database, which also requires isql. Chicken and egg problem. That makes no sense IMHO and I propose a change to make things simpler and faster. We define error messages (and all necessary things) in .h files and with help of the preprocessor (from the compiler) we use them (something like done in ini.epp), where macros could be changed for different result needs. #define FB_MSG(...) ... #include "message-file.h" #undef FB_MSG For case like gds_codes.pas, preprocessor (and maybe sed) is also used to emit the generated file. And I propose to generate that file during the build and put in the output, not storing it in the source tree. So messages could be defined like this: FB_MSG(sysf_invalid_null_empty, 0, 956, -901, "22", "023", "Empty or NULL parameter @1 is not accepted") (Note that I mixed messages2.sql and system_errrors2.sql and also removed ROUTINE, MODULE and other unimportant things.) The nice thing about the current solution is that it produces a database that is queryable, and I use that to generate the message and sqlstate files of Jaybird, and the list of error messages and codes in the language reference. To be clear, I can adjust to a new world here and parse a source file to get the info, but maybe others rely on the database in a similar way, so this removal of msg.fdb may have more impact beyond the build itself. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Rework the error message processing for simpler and faster build
Adriano dos Santos Fernandes wrote 02.09.2021 18:21: Do you suggest to get rid of firebird.msg (including localized versions) altogether? No. Utility that today reads a database will be compiled including the header file and will generate firebird.msg as today. That's pity. It is a dead piece anyway. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Rework the error message processing for simpler and faster build
On 02/09/2021 13:12, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 02.09.2021 17:46: >> That makes no sense IMHO and I propose a change to make things simpler >> and faster. >> >> We define error messages (and all necessary things) in .h files and with >> help of the preprocessor (from the compiler) we use them (something like >> done in ini.epp), where macros could be changed for different result >> needs. > > Do you suggest to get rid of firebird.msg (including localized > versions) altogether? > No. Utility that today reads a database will be compiled including the header file and will generate firebird.msg as today. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Rework the error message processing for simpler and faster build
Adriano dos Santos Fernandes wrote 02.09.2021 17:46: That makes no sense IMHO and I propose a change to make things simpler and faster. We define error messages (and all necessary things) in .h files and with help of the preprocessor (from the compiler) we use them (something like done in ini.epp), where macros could be changed for different result needs. Do you suggest to get rid of firebird.msg (including localized versions) altogether? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Rework the error message processing for simpler and faster build
Hi! We have still traces of the old error message processing that only complicate things. We define errors in .sql files (it was database) and then everything is as before. We have generated files in the tree, which frequently causes merge conflicts, not very simple to fix, as generated files needs to be edited. Utility that generate the files needs gpre and the engine previously built, and ready to use database, which also requires isql. Chicken and egg problem. That makes no sense IMHO and I propose a change to make things simpler and faster. We define error messages (and all necessary things) in .h files and with help of the preprocessor (from the compiler) we use them (something like done in ini.epp), where macros could be changed for different result needs. #define FB_MSG(...) ... #include "message-file.h" #undef FB_MSG For case like gds_codes.pas, preprocessor (and maybe sed) is also used to emit the generated file. And I propose to generate that file during the build and put in the output, not storing it in the source tree. So messages could be defined like this: FB_MSG(sysf_invalid_null_empty, 0, 956, -901, "22", "023", "Empty or NULL parameter @1 is not accepted") (Note that I mixed messages2.sql and system_errrors2.sql and also removed ROUTINE, MODULE and other unimportant things.) Adriano 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
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
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
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
