AsInteger function produces output minus 1

[miwinter]

Hi all,

To correlate that this issue also occurs in 7.5x, please also refer to viewtopic.php?t=107493

In our case we saw the same behaviour in 8.1.

The incoming field is a Varchar (19) from a sequential file. A transformer performs the below derivation in an output link:

AsInteger(StringToDecimal(FixAGREEDrill_Read_Lnk.ARR_ID))

The outbound field is a BigInt.

When the value 924587529 passes through this, it is output as 924587528. The same also happens for many other numbers in the 924xxxxxx range. However, when we stage the derivation in variables in two parts, i.e.

StringToDecimal(FixAGREEDrill_Read_Lnk.ARR_ID) into a variable of type Decimal, named SV1 and then a variable named SV2 performing AsInteger(SV1), eventually outputting the column as SV2, the inbound value remains unaffected, as it always should. That's to say, it is output as 924587529.

The only point of suspicion I have here is that the max for a Bigint is 9223372036854775807 and this issue has only come about with values around 924xxxxxx... whilst, say, 924587529 itself is well under this limit, it seems highly coincidental that this has only begun to occur with values in this range, almost as if 924587529 has been right padded with zeroes to make it effectively "out of bounds" and cause a strange behaviour. What I would say on this is that I'd expect either a warning or fatal to be logged if this was the case (one was not) and I also cannot see where this value would be right padded with zeroes (which inherently changes the number itself, as opposed to left padding it). In terms of padding, the only padding being done with the pad character variable is spaces.

In summary, we've found a workaround, but what I wanted to do was:

a) share this behaviour for others benefit
b) see if anyone has a rationale for why this did not work in its original form and needed the stage variables in the first place. To me this seems a needless step in the derivation

[miwinter]

Update...

The suspicion around breaching the bigint threshold proved unfounded, the same issue was also seen with lower values in lower ranges. The conclusion reached with the vendor showed that the same behaviour also exists in 9.1 and the solution is to perform only AsInteger on the inbound field instead. They were unable to explain why we saw the behaviour we did on some values.