NUMERIC(precision,scale) LOAD stores extra digits in Sybase 12

[former_SQLA_member1694954]

A couple of similar questions have been posted previously about SQL Anywhere 10 and 11, but I haven't seen this on SQL Anywhere 12. It appears to be fixed for SQL Anywhere 16.

Create a file C:\\tmp\\tnumtest.txt with three records (no extra blanks):

1.23
1.235
1.2345

Execute the following ISQL, connected to a database with DBA permissions (this drops and creates a table called "NumTest"):

DROP TABLE IF EXISTS NumTest;
CREATE TABLE NumTest ( myval NUMERIC(4,2) );
DESCRIBE NumTest;
-- shows: Column / Type / Nullable / Primary Key
--   myval / numeric(4,2) / 1 / 0
INSERT INTO NumTest VALUES(1.23);
INSERT INTO NumTest VALUES(1.235);
INSERT INTO NumTest VALUES(1.2345);
SELECT myval,myval+0.001,myval+0.0001 FROM NumTest;
-- shows (correctly): myval / NumTest.myval+.001 / NumTest.myval+.0001
-- 1.23 / 1.231 / 1.2301
-- 1.24 / 1.241 / 1.2401
-- 1.23 / 1.231 / 1.2301
DELETE FROM NumTest;
LOAD TABLE NumTest FROM 'c:/tmp/tnumtest.txt';
SELECT myval,myval+0.001,myval+0.0001 FROM NumTest;
-- shows (incorrectly): myval / NumTest.myval+.001 / NumTest.myval+.0001
-- 1.23 / 1.231 / 1.2301
-- 1.24 / 1.236 / 1.2351
-- 1.23 / 1.236 / 1.2346


In the second and third rows, the second and third column values are all incorrect given that myval is NUMERIC(4,2) and shouldn't ever store anything like 1.235 or 1.2345.  I thought it might be a cache issue except the database can be stopped and restarted between the LOAD and SELECT and the same results are produced.

[Former Member]

This looks like a bug that has been fixed. QTS# 708252 fixed an issue that is likely the root cause of this behaviour. All (past) versions could have been affected. The fix was checked into 12.0.1 build# 4004 and 16.0 build#1719

================(Build #nnnn  Engineering Case #708252)================

The LOAD TABLE statement may insert invalid data values into columns of type
NUMERIC. For type NUMERIC the server may insert values that exceeded the
precision and scale of the column type definition.
This has been fixed.  Now values for NUMERIC columns will be cast to
the column data type if needed.

[former_SQLA_member1694954]

Rats. We're using 12.0.1.3817. ISQL pops up daily with "Updates Are Available" but whenever I've followed the various "click here" links I've wound up at pages with login prompts that don't work for me and then given up.

Lesson for me and everyone else - probably shouldn't ignore "Updates Are Available" messages.

FYI - SELECT connection_property('precision'),connection_property('scale') gives 30 and 6

I also just checked and the "Release notes" link from www.ianywhere.com/update_checker/... (linked from the SQL Anywhere "Do you want to view them now? Yes" button) requires a username/password that stymies me. Can Release Notes at least be made public? Anyway, I'm chasing down how to get myself validated. Early retirement also has some appeal. Thanks all for the help with this.

[MarkCulp]

Regarding release (SP) readme notes: see http://sqlasupport.sap.com/readme/index.html

[VolkerBarth]

Can Release Notes at least be made public?

That's a wish several others have uttered, too - e.g. in the discussion on that CR note availability / SAP marketplace issue...

And apparently Mark and others have created a solution:)

[VolkerBarth]

@Mark: Wow, is that a new link page? If so, that's a great service - and the URL is even human-readable, other than the typical SAP ... DOC-<nnnnn> links...

Please annouce that separately!

[jack_schueler]

What version / build are you using? This is what I see for the last result set using 12.0.1.4183 (SP 82).

myval,NumTest.myval+.001,NumTest.myval+.0001
1.23,1.231,1.2301
1.24,1.241,1.2401
1.23,1.231,1.2301


It's identical to the previous result set.
Please also check your precision, scale.
select connection_property('precision'),connection_property('scale')
You should see something like 30 and 6.