SAP SQL Anywhere

Question

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.

Accepted Answer

3

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]

permanent link

answered 10 Mar '15, 14:14

Nick Elson S...
7.3k●35●107
accept rate: 32%

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.

(10 Mar '15, 14:17) Justin Willey

flat view

Answer 2

0

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)

permanent link

answered 10 Mar '15, 14:49

Nick Elson S...
7.3k●35●107
accept rate: 32%

edited 10 Mar '15, 14:49

2

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).

(10 Mar '15, 15:10) Breck Carter

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.

(10 Mar '15, 15:58) Nick Elson S...

flat view

SAP SQL Anywhere

Numeric data types and wrapping

Follow this question

Related questions