On 7-12-2015 17:21, Carlos H. Cantu wrote: > I want to discuss an implementation/enhancement to avoid "unnecessary" > overflow errors with calculations using currently exact types > (numeric, decimal - dialect 3). > > With currently logic, the result of multiplications or divisions will > have Scale = Sum of the scale of its members. This cause nonsense > situations like the following: > > select 1.000000000 / 1.0000000000 from rdb$database > > resulting in: > Arithmetic overflow or division by zero has occurred. arithmetic > exception, numeric overflow, or string truncation. numeric value is > out of range. > > Even if currently logic is defined by the SQL Standard, for the end > user, this is usually a pain.
The SQL Standard - intentionally - does not specify behavior for division. When dialect 3 was implemented, Borland opted to use the same rules as the SQL standard specifies for multiplication. > My suggestion is to implement a "smarter" logic to be used in > calculations, to avoid such glitches whenever it is possible, without > the need to create new data types. > > In short, the idea would be: do the calculation without worrying about > scale limits, and cast the final result to fit in the desired data > type. I'm not so sure this is so easy to do. You either have additional space requirements for the calculation, or you lose precision by converting to a double precision for intermediary results. > At a first though, I would propose that for the internal calculation > (meaning Firebird doing its internal math to give the formula result), > it should not limit the scale at all. Use the maximum possible scale > (cast/round/truncate when needed), or maybe use an IEEE format in the > intermediate calcs, to avoid overflow errors due to scale limit being > reached. If final value generated a scale that cannot fit in the > desired field/variable data type, it will be automatically casted to > it. What you are proposing is akin to going back to the dialect 1 behavior, which is not precise. > For those afraid of "legacy" formulas starting to return different > results, the parser can be smart enough to apply new logic only when > needed, otherwise it would use the old (currently) logic. For > "paranoics", there could be even a parameter in fb.conf to disable new > logic at all (although, personally, I don't think this is needed). I don't think we should introduce yet another configuration option for backwards compatibility, nor have the parser apply some fuzzy logic which will only lead to hard to diagnose bugs. > Currently users already need to use "workarounds" to be able to work > with those situations, meaning that some degree of accuracy is already > being lost. Usually, they will split the formula in "groups" and use > casts. Those "legacy" formulas would still work as designed, producing > the same result, since parser would use old logic. I'd prefer if anything is changed, this is not done without resorting hackish solutions that will just create another set of confusion, but instead by using generally accepted arbitrary precision decimal calculation rules from standards like ANSI X3.274-1996 or IEEE 754R, or alternatively by looking at what Java's BigDecimal does, or maybe what other database engines do (although there it seems as confusing as with Firebird). Mark -- Mark Rotteveel ------------------------------------------------------------------------------ Go from Idea to Many App Stores Faster with Intel(R) XDK Give your users amazing mobile app experiences with Intel(R) XDK. Use one codebase in this all-in-one HTML5 development environment. Design, debug & build mobile apps & 2D/3D high-impact games for multiple OSs. http://pubads.g.doubleclick.net/gampad/clk?id=254741911&iu=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
