The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

[Yoda] Deja vu strong with this one it is. [/Yoda]

In 11.0.1.2276 a DECIMAL ( 30, 6 ) COMPUTE column value displays correctly as 0.000000 in dbisql but magically becomes super-duper-precise when put through UNLOAD: 000000000022222222222204938271604951714677

Is the extra crap precision actually stored in the database?

BEGIN
DECLARE LOCAL TEMPORARY TABLE x (
   a DECIMAL ( 30, 6 ),
   b DECIMAL ( 30, 6 ),
   x DECIMAL ( 30, 6 ) COMPUTE ( ( ( a - b ) / b ) * 100.0 ) );
INSERT x ( a, b ) VALUES ( 999999999999999999999999, 999999999999777777777777 );
SELECT @@VERSION, * FROM x;
UNLOAD SELECT @@VERSION, * FROM x TO 'C:\temp\x_table.txt';
END;

/*
-- The first SELECT in dbisql...

@@VERSION,a,b,x
'11.0.1.2276',999999999999999999999999.000000,999999999999777777777777.000000,0.000000

-- The file produced by the UNLOAD...

'11.0.1.2276',999999999999999999999999,999999999999777777777777,.000000000022222222222204938271604951714677
*/

asked 20 Nov '10, 15:07

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

edited 29 Nov '10, 21:35

Comment Text Removed

Just a similar effect with DOUBLE: When comparing the UNLOAD result for a migrated database with different SA versions, I remember to have had situations where the same double value was unloaded to different numbers. The differences were apparently in the range beyond the significant 15 digits, but I still was stumbled that a value could produce different results before and after a migration. I asked in the newsgroups but didn't get a real explanation... For DECIMAL, it's even more of a surprise:)

(20 Nov '10, 20:49) Volker Barth

[Volker said: For DECIMAL, it's even more of a surprise:)] ...yeah, it was a complete Deer In The Headlights moment, woulda gone viral on Youtube if a camera had been pointed at me :)

(21 Nov '10, 10:07) Breck Carter
2

The extra precision does appear to be getting stored. As far as I can tell, UNLOAD is just printing the value that it found in the column so the problem would appear to be a lack of an appropriate cast/rounding during the insert. I'll let someone from query processing comment / look into it.

(22 Nov '10, 21:50) John Smirnios

I trust John's answer that's not an answer. Seems like an awful waste of points to let it drop though so here:

The extra precision does appear to be getting stored. As far as I can tell, UNLOAD is just printing the value that it found in the column so the problem would appear to be a lack of an appropriate cast/rounding during the insert. I'll let someone from query processing comment / look into it.

-John Smirnios

permanent link

answered 29 Nov '10, 15:05

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

edited 29 Nov '10, 15:09

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644

Well, maybe it's Breck's tradition to "embarrass" folks with bounties that keeps'em from answering...

(29 Nov '10, 15:09) Volker Barth

Looks like I was otherwise occupied when the bounty expired... sigh :)

(29 Nov '10, 21:37) Breck Carter

It's probably best to report it as a bug through tech support and it will get assigned to the QP team. The code involved is probably rather intricate and it's understandable if they don't delve deep into every issue posted here right away. From a high level, numerics are implemented in a library that supports "significant digits" and an exponent rather than precision+scale. Significant digits+exponent is definitely the thing you want for intermediate results but care must take to round back to precision+scale when assigned to a database NUMERIC type. One such rounding appears to be missing.

(02 Dec '10, 01:43) John Smirnios
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:

×101

question asked: 20 Nov '10, 15:07

question was seen: 989 times

last updated: 29 Nov '10, 21:35