The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.

asked 15 Jan '15, 11:20

DougMWat's gravatar image

DougMWat
1365512
accept rate: 0%


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.
permanent link

answered 16 Jan '15, 11:21

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
6.5k2895
accept rate: 29%

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.

permanent link

answered 16 Jan '15, 10:26

JBSchueler's gravatar image

JBSchueler
1.9k2836
accept rate: 16%

edited 16 Jan '15, 10:32

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.

permanent link

answered 16 Jan '15, 12:03

DougMWat's gravatar image

DougMWat
1365512
accept rate: 0%

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

(16 Jan '15, 12:20) Mark Culp
Replies hidden

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

(16 Jan '15, 18:17) Volker Barth

@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!

(16 Jan '15, 18:20) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×14

question asked: 15 Jan '15, 11:20

question was seen: 1,184 times

last updated: 16 Jan '15, 18:33