$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
-1

"NAN"-value in a double field of a ADT Table

Oct 14, 2016 at 12:37 PM

496

avatar image

Hi Everybody,

we got an error because the content of a double Field is "NAN" in a ADT table.
ADS 11.10.0.22 is in use and the program accessing the tables is written in Delphi.
The Client uses dll's with the version 8.10.0.38.

According to - http://devzone.advantagedatabase.com/dz/content.aspx?key=48&id=72386
-> ... For ADT table we do ensure that only valid data are stored in the numeric field. ...
-> ... Normally, the higher level ADS client (For example, ADS TDataset client for Delphi), will ensure that the API is called only with valid data so there should not be any wired data in the table. ...

Questions I am not able to answer on my own:

1. How is it possible to get a "NAN" value in a double field?
2. What can we do to ensure this is not happening anymore?
3. How can I find such broken fields before an Error occurs? -> Scanning all fields of all records in a loop is not really a option, because we need to check about 70.000 ADT tables with all together about 750GB

Any ideas, questions, suggestions, ... are welcome!
Thanks to everyone who is willing to help! - Marcel

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

11 Answers

Michael Loop
Nov 15, 2016 at 08:58 PM
1

Marcel,

In ADS 10.0.0.3, we tightened up compliance with the IEEE standard for 8-byte double values. See: http://devzone.advantagedatabase.com/dz/WebHelp/Advantage11.1/index.html?master_effects_of_upgrading_to_version_10.htm. This would prevent new NaNs from being inserted or updated, but would not affect values already in the table. If this is the source of your NaNs, tthe following query might help:

SELECT artnr,bestellt
FROM "DAB410.adt"
WHERE LEFT( HEX2CHAR( CAST(bestellt AS SQL_BINARY(8))),3)='7FF'
OR LEFT( HEX2CHAR( CAST(bestellt as SQL_BINARY(8))),3)='FFF';

Note that I do not have a table containing NaNs to test with, so I don't know how well this will work.

If your NaNs are not logical NaNs as defined by the IEEE standard, but simply invalid data types such as strings, the following might help:

SELECT artnr,bestellt
FROM "DAB410.adt"
WHERE NOT bestellt IS NULL
AND NOT bestellt < 0
AND NOT bestellt => 0

Again, I do not have a straightforward way to test this. The optimizer may simply decide that the query is a no-op.

If you do need to check your whole database, you would only have to check double columns. NaNs are not a feature of other datatypes.

As to how the NaNs got into your database, the most likely cause is the changing standard for IEEE compliance, as described above. Non-ADT tables could have had NaNs inserted by non-ADS tools, but this would not apply to you. Beyond that, I could only speculate, with each possibility being more unlikely than the last... a bug in an earlier version of ADS, a faulty backup, possibly caused by an antivirus, hardware failure, etc.

I hope this helps,

Mike Loop - SAP Product Support

Share
10 |10000 characters needed characters left characters exceeded
Igor Golobrodkiy Nov 09, 2016 at 07:56 AM
-1

Maybe NAN is NULL?

This query shows such records

SELECT * FROM "tablname" WHERE bestellt is null

Share
10 |10000 characters needed characters left characters exceeded
Marcel Mayer Nov 08, 2016 at 04:31 PM
0

Did anybody have similar or comparable problems?

Share
10 |10000 characters needed characters left characters exceeded
Marcel Mayer Nov 15, 2016 at 08:20 AM
0

Hi Igor,

sorry for the delay in my answer - I thougth, I would get an email notification if I get an answer.

No, unfortuantelly NAN ist not null. A lookup on this field has produced an error.
Searching 'broken' field with your suggested where clause may find the fields, but also thousand others that are really null.

Thanks a lot for your pretty fast reply!

Share
10 |10000 characters needed characters left characters exceeded
Marcel Mayer Nov 16, 2016 at 04:21 PM
0

Hi Mike,

Thanks a lot - the idea to convert the content via binary to a HEX value is really nice!

Unfortunately I don't have a table with (an known) "NaN" value. But I have taken a short look into a one of the tables and found this:

SELECT artnr, kw_index, rue_prod , HEX2CHAR( CAST(rue_prod AS SQL_BINARY(8))) 
FROM "DAB410.ADT" WHERE LEFT( HEX2CHAR( CAST(rue_prod AS SQL_BINARY(8))),3)='7FF'
OR LEFT( HEX2CHAR( CAST(rue_prod as SQL_BINARY(8))),3)='FFF'

This Database is from a customer which updated to version 10.10.0.28 on the 06.03.2013. So all (or at least most) of these values have been written with the ADS Version 10.10.

As far as I understood the IEEE754 and you all these values should be "NaN" but the aren't - that's confusing for me...

Further on, a hardware failure (broken clusters in the HDD-raid-arry, ram, ....) maybe possible...

But I am really surprised that a antivirus program could eventually create corrupted fields in a ADS-table - did you, or one of your colleges had a case where that happened?

A faulty backup in this case is not possible, because the company hadn't restored their DB's from a backup - they change the hardware before it's broken.

I am really looking forward to your reply!

All the best - Marcel


Share
10 |10000 characters needed characters left characters exceeded
Marcel Mayer Nov 28, 2016 at 08:17 AM
0

Hi,

Is anybody able to explain, why the field values (like FFFFFFFFFF9F9E40) from the SQL-search don't show NaN but numeric results?

According to the IEEE 754 all this Fields should be NAN!

Thanks anybody!

Share
10 |10000 characters needed characters left characters exceeded
Michael Loop
Dec 05, 2016 at 09:50 PM
0

Marcel,

Sorry for the delay.

I think that the results you are seeing suggest that my SQL statement may not be as reliable as I would have hoped. Either there is something wrong with the query, or the HEX2CHAR or CAST functions have a problem. I can't find any way to reverse-engineer the double values from the hex values in your output.

Note that I tested my query against the sample database that ships with Advantage Data Architect, and I got the values one would hope for: no NaNs, and all hex values begin with 8 zeroes if the WHERE clause is commented out.

Can you tell me what proportion of your data converted to these apparent NaNs? Was it all rows, most of them, or just a small fraction?

We have not specifically seen this type of problem as the result of an antivirus. Usually a third-party utility would lock a table and prevent it from backing up at all. However, in some cases an antivirus or backup program can hold a partial lock on a file, leading to unpredictable results. This is rare, but if I recall correctly the usual problem that can result is corruption of a referential integrity relationship. You should always exclude database table files from on-access antivirus scans and try to prevent concurrent third-party backups. Online backups by third-party utilities are inherently unreliable because they have no way of ensuring that all values in memory have been written to disk.

As I mentioned, each hypothetical cause of the NaNs is less likely than the one before. If an antivirus could cause this, it is unlikely that we would ever be able to reproduce it. I was just speculating.

Regards,

Mike Loop
Senior Support Engineer
SAP Support

Share
10 |10000 characters needed characters left characters exceeded
Marcel Mayer Dec 06, 2016 at 10:15 AM
0

Hi Mike,

I have done some research on the two relevant tables of our customer. The DAB410, the one I already mentioned, is a table containing the open processes (buying, selling, producing, ..) of all products. The DAB020 contains all datasets and is quite big. In the live-system the history in the DAB020 only contains the the last two years, because too much entries caused problems in the past.

At the Moment the DAB020 contains 15,1E+6 entries and only 77,6E+3 have double fields starting with 'FFF' or '7FF' -> 0,5 % of the entries, which surely is a small fraction. So the SQL-statement might work correctly.

Some of these products exist for a long time, so it would be possible that the 'wrong' double fields have been written the first time in an ADS-Version 8.X . So these (possibly) corrupted double values may be a burden from 'old days', and only modified in the "valid" part?! I had the idea that the NaN value may be the result of a 'threshold value problem' when we perform a calculation with one or two 'corrupted' double values. What do you think about this theory - do you thing that this hypothetical cause is more likely than the others?

The database is excluded from all antivirus-software (live) protection - I was thinking about a AV on the client side and there the AV should not be possible to cause NaN or any other corrupted data. Our customer uses the ADS-backup because the have a 24/7 production, so (hopefully) the backup is not causing the problem ;-)

All the best Marcel

Share
10 |10000 characters needed characters left characters exceeded
Michael Loop
Dec 06, 2016 at 02:03 PM
0

Marcel,

If the NaNs are the result of a lax standard before version 10, I would still expect that they would be very rare, much less than 0,5%. They would surely be the result of threshold values or corner cases, but I really couldn't guess the circumstances with any reliability. What confuses me about your most recent screenshot is that somehow NaNs are being represented as distinct numeric values, and no simple operation, such as bit shifting or converting the Fs to zeroes yields the values we see.

You should open a support incident so that we can investigate more in depth. You should also consider whether you have any affected tables you could attach to the incident without compromising your customers' sensitive data. Perhaps you could remove all columns except the double.

If you could establish whether any of the NaNs exists in data that has been added in since you upgraded past version 10, it would tell us whether the older standards are to blame.

ADS backup should not cause corruption. That is the whole point of having a proprietary backup. Client side antivirus and other security scans also should have no detrimental effect.

Regards,

Mike Loop
Senior Support Engineer
SAP Support

Share
10 |10000 characters needed characters left characters exceeded
Marcel Mayer Dec 12, 2016 at 04:39 PM
0

Hi Mike,

today our customer has given his approval, I only have to remove some columns.

In the "DAB020.ADT", I am preparing for you, all data where written with ADS 11.X - some of the entries may be based on older data (even ADS 9.X or earlier). It could take hours to go back the path of a single record in the DAB020 to its first corrupted entry (I am not sure if the old Backups still exist anyway).

I have never opened a support incident - can you tell me what I have to do?

Thanks a Lot!
Marcel

Share
10 |10000 characters needed characters left characters exceeded