0

To eliminate null values from fields for some reason the use of too many coalesce results in BCD Overflow errors

I eliminated the error to 1 line in the select part.

  • If any (of the 4) coalesces are replaced by a fixed value it's ok
  • If any coalesce is removed, the result is also ok
  • If for any field another is used, the error remains

Replacing the '/' with '-' also gives a result but not the correct value.

All fields are numeric(10,4).
It's Firebird version 2.5.8

select 
coalesce(Field1, 0) * coalesce(Field2, 0) * ((100 - coalesce(Field3, 0)) / 100) * ((100 + coalesce(Field4, 0)) / 100)
from Table

A calculated column is expected, the actual result is a BCD overflow

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Herman
  • 11
  • 3

2 Answers2

1

Not quite an answer, just a test.

Firebird 2.1.7, IBExpert, SQL Dialect 3

create table t58096187 (
 f1 numeric(10,4),
 f2 numeric(10,4),
 f3 numeric(10,4),
 f4 numeric(10,4)
);

insert into t58096187 values ( 50, 50, 50, 50 );

select
 coalesce(F1, 0) * coalesce(F2, 0) * ((100 - coalesce(F3, 0)) / 100) * ((100 + coalesce(F4, 0)) / 100)
from t58096187;

ERROR: Unsuccesful execution ... integer overflow ... cause the most significant bit of the result to carry

•Dialect 3 databases allow numbers (DECIMAL and NUMERIC data types) to be stored as INT64 when the precision is greater than 9

Same error with Numeric(9,4) which should avoid use if int64 internal datatype.

If I declare fields as float though, the select yields result 1875, which is well within Numeric(10,4) datatype.

Maybe some INTERMEDIATE result in reverse-polish goes out of bounds?

This also works if to avoid use of INT64 using

recreate table t58096187 (
 f1 numeric(6,2),
 f2 numeric(6,2),
 f3 numeric(6,2),
 f4 numeric(6,2)
)

It seems Firebird 3.0.5 is also affected - https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=57729b31e0a5019aea68a136638d9f50

There is no error - but no results either!

Numeric-as-Int32 works: https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=c4a4230e855b0ce4fd2b0c7b3b697cda

Reported as https://www.sql.ru/forum/1317439-a/

Mark's assumption of fractional part accuracy as causing this is probably correct.

recreate table t58096187 (
 f1 numeric(10,4),
 f2 numeric(10,4),
 f3 numeric(10,4),
 f4 numeric(10,3)
)

Still works.

https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=29a6c15d6e1854be230d29aea30307cf

Coalesce seems to have nothing with this my problem, dunno if it is relevant to the topic starter's problem, as it is ambiguous what he meant by "removing coalesce". After I removed coalesce it becomes like that

select
 F1 * F2 * F3 * F4
from t58096187

And likely the same error

https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=1f29aff4102ace57e8fa27d83e59b93f

Arioch 'The
  • 15,799
  • 35
  • 62
  • The problem is probably with the rules applied for numeric/decimal multiplication and division (that is new scale = scale_lhs + scale_rhs), which causes the scale to become too big (in your example the scale would become 16, which would leave insufficient room for the value before the decimal point. – Mark Rotteveel Sep 25 '19 at 13:55
  • @MarkRotteveel sure, multiplication and boundaries are tricky. Assembler just doubles datatype: int16 * in16 = int32, etc. But high-level languages like C or Java do not... That said, it should be underflow then :-) – Arioch 'The Sep 25 '19 at 14:02
  • The result type of your multiplication is a NUMERIC(18,16), the value 1875 doesn't fit, which makes it an overflow. – Mark Rotteveel Sep 25 '19 at 14:04
  • why should it be `NUMERIC(18,16)` and not `NUMERIC(10,4)` or `NUMERIC(40,4)` ? @MarkRotteveel – Arioch 'The Sep 25 '19 at 14:06
  • at very least it is not documented in "Fixed-Point Data Types" FB 2.5. manual reference – Arioch 'The Sep 25 '19 at 14:13
  • Because of [Exact Numerics - Functional Specification](https://www.ibphoenix.com/resources/documents/design/doc_154) (which also matches the requirements of the SQL standard except for division, which the standard defines as implementation specific): _"If two operands OP1 and OP2 are exact numeric with scale S1 and S2 respectively, then OP1+OP2 and OP1-OP2 are exact numeric with precision 18 and scale the larger of S1 and S2, while OP1*OP2 and OP1/OP2 are exact numeric with precision 18 and scale S1+S2."_ – Mark Rotteveel Sep 25 '19 at 14:14
  • And it can't be `NUMERIC(40, 4)`, because Firebird doesn't support that high a precision (although Firebird 4 will support a max precision of 38). – Mark Rotteveel Sep 25 '19 at 14:16
  • this is also documented in original FB 2.5 manual: http://www.ibase.ru/files/firebird/langref25rus/index.html#types-fixed but I wonder if it stems fro mSQL standard or from Interbase implementation details... – Arioch 'The Sep 25 '19 at 14:16
  • I meant of course "ideal goal" abstracted from implementation. Also, `Numeric(38,16)` will still manage to contain 1875. It seems not intuitive to try so hard to preserve less significant digits and do absolutely nothing to preserver most significant ones. – Arioch 'The Sep 25 '19 at 14:18
  • To repeat, it is defined in the SQL standard for addition, subtraction, and multiplication (and division is implementation specific). The Firebird behaviour follows the requirements of the SQL standard. – Mark Rotteveel Sep 25 '19 at 14:18
  • @MarkRotteveel "The precision of the result of multiplication is implementation-defined, and the scale is S1 + S2." at least in ISO/IEC 9075-2:1999 - 6.26 , and same in ISO/IEC 9075-2:2003 (E) – Arioch 'The Sep 25 '19 at 14:24
  • See https://stackoverflow.com/a/26129680/466862 for a quote from the SQL:2011 standard. And your quote matches the behaviour: the precision in Firebird is 18 (so implementation defined), and the scale is S1+S2, which is also the case in Firebird. – Mark Rotteveel Sep 25 '19 at 14:26
  • Well, at least I had reported bug against db-fiddle :-) Also It is weird that the last example works with 18-(4+4+4+3) = 3 and 1875 is 4 digits, perhaps sheer luck... – Arioch 'The Sep 25 '19 at 14:31
  • That is because a NUMERIC(18,x) is backed by a bigint and fits values with 19 digits (just not the full range). – Mark Rotteveel Sep 25 '19 at 14:34
  • okay, now I am in doubt whether to delete this "answer" and all the chat under it, at least unless TS purifies his problem to the crux of it, or to keep it for TS to read and maybe have some insights. Personally I am concerned with ambiguity of what "remove coalesce" should mean... To me it should mean "remove function call but preserve expression branch value and datatype", but his mileage may vary... @MarkRotteveel – Arioch 'The Sep 25 '19 at 14:38
  • You could flag one of the comments and leave a custom comment to ask a moderator to remove all the comments. I think your answer can remain, but hopefully the OP updates his question to provide a clear [mre]. – Mark Rotteveel Sep 25 '19 at 14:44
-2

Apparently the problem is a little more specific: The result of an integer operation caused the most significant bit of the result to carry.

So applying the suggested solution (define the format for the resulting number we want to use) we would do:

select
    cast( (cast(coalesce(f1, 0) as numeric(10,4)) * cast(coalesce(f2, 0) as numeric(10,4)) ) as numeric(10,4)) * cast(((100 - coalesce(f3, 0)) / 100) as numeric(10,4)) * cast(((100 + coalesce(f4, 0)) / 100) as numeric(10,4))
-- cast(coalesce(f1, 0) as numeric(10,4)) * cast(coalesce(f2, 0) as numeric(10,4)) * cast(((100 - coalesce(f3, 0)) / 100) as numeric(10,4)) * cast(((100 + coalesce(f4, 0)) / 100) as numeric(10,4)) -- sigue dando el mismo error
-- coalesce(F1, 0) * coalesce(F2, 0) * ((100 - coalesce(F3, 0)) / 100) * ((100 + coalesce(F4, 0)) / 100)
from t58096187;

Thanks to Arioch 'The for the great analysis and example to reproduce the error!

Fernando Murrieta
  • 25
  • 6