Search

Storing IEEE 754 Floats in SQL Server 2005 (NaN, +/- Infinity) by James C. Papp

Active

54
1
Sign in
 to vote
Type: Bug
ID: 239674
Opened: 11/20/2006 12:18:12 PM
Access Restriction: Public
3
Workaround(s)
21
User(s) can reproduce this bug
Umachandar Jayachandran recommend that I post this issue on MS Connect. The problem is a breaking change from SQL Server 2000, which prevents us from switching over to SQL Server 2005. The change eliminates the ability to store NaN and Positive and Negative infinity values for double precision floats.

I have a hard time understanding way that this is not supported, or more accurately, why it is prevented in SQL Server 2005 (even if it requires some form of a special field type or extension). It is incredible limitation that affects all of our scientific applications and the storage of our simulation data.

It should be noted that you can store these values even in MS Access. Oracle has specific support for IEEE values as well. In SQL Server 2005, it seems that a decision was made to change the way it validates data being passed from the client. If it encounters one of these special values, it now throws an exception under .NET with the following text:

`The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@PropertyValue"): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.'

My question is why NaN, +/- infinity are considered "bad" values? They shouldn't be. If someone wants financial accuracy they should be using decimal fields, not floating point. Why have the floating point fields at all if you cannot store the IEEE 754 double values in them? Even just letting the values be stored in the database with limitations on doing TSQL queries directly on them would still be an improvement them rejecting them outright. How are you dealing with C# 3.0 and DLinq support? Don't you have the same issues there?

Is there is any chances of getting a hot fix to restore the behavior of SQL Server 2000? Or is there time to work on a solution with the upcoming Service Pack 2? It really is burden to scientific applications.

More information can be found here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=935356&SiteID=1

Thanks much!
James C. Papp

11/26/2006:
This is issue was close without explanation, so I reactivated it. Your help is greatly appreciated!

11/30/2006: Respose to comment follows...

Thanks for the reply, but I still have a few concerns. First, if you still come to the conclusion that this is by design, you should seriously consider enhancing SQL Server so it can handle these special floating type values internally. Oracle has support for these with their BINARY_FLOAT and BINARY_DOUBLE data types and it would be nice to see SQL Server at the same level.

With that said, I can see your position with NaN values, but are you sure you were not too draconian in your decision to excluded positive and negative infinity? With NaN, there is no defined sort order unless one is arbitrary assigned, so I can see potential problems with building indexes and such. However, positive and negative infinity do have a well defined sort order, and work fine in calculations, so I still do not understand why you need to excluded these values along with NaN.

These changes seem to stem from Microsoft Article ID 900335 (http://support.microsoft.com/kb/900335) and related documents, but as far as I can tell; most of the information singles out NaN as the culprit (as well as your comment), not positive and negative infinity. I just want to make sure that the "fix" was not overzealous in excluding values that it does not really need to. We could certainly work around not having NaN, by mapping these values to null, but in the future; especially for scientific applications like ours, it would be nice to see full support for the IEEE 754 spec for all special values and conditions, including positive and negative zero.

As far as your solution using varbinary(8) columns, we have considered this, but the main problem is enabling are analysts' access to the data. They use a variety of tools (many with only ODBC connectivity) as well as Microsoft Office (particularly Excel) and the varbinary data type does not come over well or map correctly as it does when stored as a floating point data type. If you have a way to work around this or have other solutions I'd love to hear it. Thanks.
Details (expand)
Product Language
English

Version

SQL Server 2005 - Express Edition

Category

SQL Engine

Operating System

Windows XP SP2 Professional
Operating System Language
US English
Steps to Reproduce
Try storing doubles that are NaN or +/- Infinity
Actual Results
`The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@PropertyValue"): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.'
Expected Results
To work without exception as it does in SQL Server 2000

Platform

32
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 1/10/2012 at 12:39 PM
Hello,
Thanks for your feedback. I just want to post a comment that there is IEEE 754R spec that describes 128-bit and decimal formats. We will consider this request and the new spec when we evaluate our options. Adding a new type in SQL Server or modifying existing types has lot of challenges due to the changes required across the entire stack. There is also backward compatibility impact. So we will take a look at this for a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Posted by TamusJRoyce on 12/2/2011 at 9:34 AM
Infinity minus Infinity is undefined because one Infinity could be approaching the concept of infinity faster than the other or vs versa. If the second Infinity goes to the concept of Infinity faster than the first, it would be negative infinity. Therefore, doing math with Infinity in a lot of cases results in undefined values.

And I say NAN is in the same position, then. It would be nice for storage only. But the concept of not allowing these does adhere. Which I agree is thinking outside the box. I have to take back my previous statement. Even if the standard allows it.

Posted by TamusJRoyce on 12/2/2011 at 9:09 AM
This is another example of microsoft half-ing it. Sorry for not being professional. But I think this is uncalled for. Going against IEEE standards is a habitual mistake Microsoft makes.

You have a NULL attribute to allow/disallow NULL. Use that as doubling for allowing +-Infinity/NAN. This way there is no syntax change for allowing/disallowing NAN/Infinity.

Example (using NULL functionality):

Doing math with NAN results in a NAN. The same exact way concatenating NULL with a string of any type results in NULL. Comparison with NAN would work the same way comparison with NULL works. NULL trumphs NAN. But otherwise, the same result forms (just a different name...it's in the IEEE standard, so no extra space).

+- Infinity are constants. Adding, subtracting, multiplying, and dividing (other than infinity, zero, and opposite sign for multiplication/division which changes the infinity sign) results in the same constant value (similar to NULL). Adding/subtracting/dividing/multiplying these Infinite constants would result in +-Infinity or Zero respectively.

When compared with the way NULL works in databases (which is thinking outside-the-box, and I applaud), it isn't too different than functionality Sql Server already has. Just need to think of NULL functionality in a loosely-coupled way, and apply it to this issue.
Posted by codekaizen on 10/2/2011 at 10:46 PM
What is the status of this for Denali? While I'd prefer allowing NaN, treated as a null (they are, as TomThomson points out, at least semantically equivalent), I understand disallowing it since null is the same meaning. I can't understand not having +/- infinity.
Posted by TomThomson on 4/8/2010 at 5:34 AM
The problems with NaN and indexing are precisely equivalent to the problems with NULL and indexing, so to add NaN would require either adding a NOT NaN constraint to a coulmn, and generating domain errors when an insert or update would violate this; I think that would be better than forbidding NaN altogether. Since order by clauses can already cope with columns which permit NULL, they can easily be made to cope with columns which permit NaN. I can't imagine any reason why SQL Server shouldn't permit the two infinities internally.
Posted by arghhhhhhhhhhh on 3/24/2010 at 2:51 PM
Err, I was confused. The problem I observed was error when reading back NaN/Infinity from a SQL 2000 database restored on SQL 2005. SQL 2000 allowed insertion of such values (using .NET) while SQL 2005 does not.
.NET fails on reading out the values regardless of SQL 2000/2005.

Conclusion: I agree with the new (default) behavior to disallow NaN/Infinity, but an option to enable it for those who need it (e.g. ARITHUNREAL) would not hurt.
Posted by arghhhhhhhhhhh on 3/24/2010 at 5:57 AM
Whats really crazy (observed with SQL 2005 and .NET 2.0) is that I can store/write NaN/Infinity without warnings/errors, but when I try to read it back I get Arithmetic Overflow (both in .NET and Management Studio), regardless of ARITHABORT is ON/OFF. Someones mind is not straight.
Posted by Microsoft on 12/19/2007 at 10:17 AM
Hello James,

Just letting you know that we are looking at allowing +/- infinity. As you suggest, these values, unlike NaN, should fit well into our sorting. I can't promise that we'll be able to address this in the SQL Server 2008 timeframe, but we are still considering it.

Cheers,
-Isaac
Posted by James C. Papp on 11/30/2006 at 12:33 PM
I've added additional information for your review above...
Posted by Microsoft on 11/27/2006 at 1:27 PM
SQL server doesn't support NaN/Infinity internally, therefore they are considered invalid values. They can't be compared, as well as calling various arithmetic builtins. For example, if you have a NaN in the column, building index could fail, and adding two numbers could cause exceptions. It is more destructive to allow the data in and then fail random operations later.

If you just use SQL Server to store and retrieve such data, without indexing, comparison, or arithmetic operations, could you consider store it in a varbinary(8)column?
Sign in to post a workaround.
Posted by Insight6 on 5/23/2012 at 9:15 PM
Could use Double.MaxValue and Double.MinValue as placeholders for +ve and -ve infinity, and DBNULL as a placeholder for NaN (as long as the .NET Double being represented isn't nullable) - but having to do all that tiptoeing conversion around database reads and writes is a gigantic pain. It creates the need for a whole other layer of abstraction that shouldn't have to be there.
Posted by Insight6 on 5/23/2012 at 9:13 PM
Using two fields to store one number is pretty dirty. I'd rather just store the full precision string representation and then Double.Parse() it back in. If SQL can't deal with IEEE 754 doubles, I don't want it doing math for me anyway.
Posted by TamusJRoyce on 12/2/2011 at 9:37 AM
Use a nvarchar and convert it to a double using conversion based on it not holding 'NAN', 'Inf' or '-Inf'. Otherwise, add a char field which describes NAN, Inf, -Inf with 'N', 'I', and 'M'. Or something similar.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement