Numeric data types and wrapping

[justin_willey]

I'm trying to work out when the behaviour changed for numeric data types between v110.0.1 and v16.0.0 as I can't see it in the docs.

In v10.0.1.4310:

create variable banana smallint;
set banana = 32767;
set banana = banana + 1;
select banana;


produces -32768 and 
create variable banana2 int;
set banana2 = 2147483647;
set banana2 = banana2 + 1;
select banana2;


-2147483648. But in v16.0.0.2018 you get an error:
Could not execute statement.
Value 2147483647 + 1 out of range for destination
SQLCODE=-158, ODBC 3 State="22003"
Line 0, column 0


This caused us a problem on upgrade with some counters that relied on the wrapping behaviour. Easily fixed, but unexpected.

[VolkerBarth]

To reduce the "EBF readme search space": Both addition operations do fail with the (rather old) v12.0.1.3324, too...

[justin_willey]

To continue the bracketing process - they work fine in 11.0.1.2960 - so it must have happened in 12.0.0 or 12.0.1

[Former Member · Accepted solution]

I expect this is all a part of the change introduced in 12.0. This was documented as Better handling of overflow errors

Not certain why you didn't get an error with the first couple of examples, but that could be due to better signed integer handling ... [if that matters I can look further into that more]

[justin_willey · Accepted solution]

Thanks Nick - that would make sense. Its a great help to know that it is a specific change not something random we are encountering - though the error message is specific and relevant.

[Former Member]

Looks like the 2nd part of this is you must have set the ansi_integer_overflow option to 'off' to allow that and now it is supposed to be ignored (ie. support for that was removed in 11.0.0 and should have been throwing errors there too)

[Breck_Carter]

FWIW this should have been documented in the "Behavior Changes" rather than "New Features". Also, the phrase "the overflow resulted in an undefined value" is very misleading... the result may not have been described in the documentation but it was FAR from unpredictable which is the impression left by "undefined".

Closing loopholes may be a noble pursuit for some, but one person's undocumented behavior may be another person's feature. The Behavior Changes documentation should be a thorough roadmap for testing... far, far more important for many folks than New Features... some folks upgrade because they have to, not because they want to (e.g., government suppliers who are not allowed to use unsupported software).

[Former Member]

Actually it follows the behavior changes section as a separate "deprecated and discontinued" section in the 11.0.0 doc set ... (an organization that was corrected for 11.0.1 and more recent versions) ... But I agree that created another corner for sometimes interesting dust to collect where nobody is looking for it.