REAL column holding values outside documented range

Question

According to MSDN, the range for REAL values is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. However, I have quite a few values beyond that range in my table.

The following query returns lots of very small values and no very large ones:

SELECT  MyColumn ,
        *
FROM    data.MyTable
WHERE   MyColumn <> 0
        AND ( MyColumn < CONVERT(REAL, 1.18E-38)
              OR MyColumn > CONVERT(REAL, 3.40E+38)
            )
        AND ( MyColumn < CONVERT(REAL, -3.40E+38)
              OR MyColumn > CONVERT(REAL, -1.18E-38)
            ) 

It is easy to show how these values end up in the table. I cannot insert them directly:

CREATE TABLE a(r REAL NULL);
GO
INSERT INTO a(r) VALUES(4.330473E-39);
GO
SELECT r FROM a
GO
DROP TABLE a;

----
0.0

But I can divide two columns and get and outside of range value:

CREATE TABLE a
  (
    r1 REAL NULL ,
    r2 REAL NULL ,
    r3 REAL NULL
  ) ;
GO
INSERT  INTO a
        ( r1, r2 )
VALUES  ( 4.330473E-38, 1000 ) ;
GO
UPDATE  a
SET     r3 = r1 / r2 ;
SELECT  r1 ,
        r2 ,
        r3
FROM    a

r1            r2            r3
------------- ------------- -------------
4.330473E-38  1000          4.330433E-41

So I guess MSDN gives wrong ranges of valid data, correct? Am I missing anything?

Several people suggested that this is a bug.

What part of this behavior exactly is a bug. is it:

1. Wrong constants documented in MSDN and used in DBCC, as well as wrong threshold for rounding down.
2. Update being able to save wrong values

Singhal

Seems like a bug, have you filed it in your favorite place? I guess you could add your own constraint something like CHECK (r3 > 3.40E-38) :-) – Photolysis

@AaronBertrand I can definitely add a constraint, but I would like to understand why SQL Server does not behave as documented. – Singhal

dbcc checktable('a') with DATA_PURITY: Column "r3" value is out of range for data type "real". Update column to a legal value. Report it... – Emplane

As I said, it seems like a bug. :-) – Photolysis

Actually, it doesn't seem like a bug. Once DATA_PURITY has been checked and enabled this should not occur anymore. – Cully

@Cully can you post an example that demonstrates it? – Singhal

@AlexKuznetsov, you are right. See my answer. DBCC CHECKDB reports an error on this. – Cully

Answer 1

Books Online documents only the normal range for single- and double-precision floating point numbers. The IEEE 754 rules also specify floating-point numbers closer to zero than the smallest non-zero normal value, known variously as denormalized, denormal, and subnormal numbers. From that last link:

Denormal numbers provide the guarantee that addition and subtraction of floating-point numbers never underflows; two nearby floating-point numbers always have a representable non-zero difference. Without gradual underflow, the subtraction a−b can underflow and produce zero even though the values are not equal. This can, in turn, lead to division by zero errors that cannot occur when gradual underflow is used.

SQL Server is following the rules for single-precision floating point calculations in the examples posted. The bug may be that DBCC checks only for normal values, and throws an incorrect error message when it encounters a stored denormal value.

Example producing a denormal single-precision value:

DECLARE
    @v1 real = 14e-39,
    @v2 real = 1e+07;

-- 1.4013e-045
SELECT @v1 / @v2;

Example showing a stored float denormal passes DBCC checks:

CREATE TABLE dbo.b (v1 float PRIMARY KEY);
INSERT b VALUES (POWER(2e0, -1075));
SELECT v1 FROM b; -- 4.94065645841247E-324
DBCC CHECKTABLE(b) WITH DATA_PURITY; -- No errors or warnings
DROP TABLE dbo.b;

Mckissick

Which part is the bug depends on the intention of the SQL Server programmers. It appears, from the documentation (and DBCC CHECKDB), that the intention was to only store normalized numbers. – Vyse

Indeed, this proves that there is a bug in DBCC CHECKDB. Being able to store denormal floats might or might not be one, depending on the design decision made by the product team. +1 – Cully

+1, but if there is a bug in DBCC, then MSDN is wrong too, correct? – Singhal

@SQLkiwi incomplete rather than wrong? Maybe. I am not sure why it would matter. – Singhal

Answer 2

This is a bug in SQL Server. The last script you post is a nice repro. Add one line to it at the end:

DBCC CHECKDB WITH data_purity

This fails with:

Msg 2570, Level 16, State 3, Line 1 Page (1:313), slot 0 in object ID 357576312, index ID 0, partition ID 1801439851932155904, alloc unit ID 2017612634169999360 (type "In-row data"). Column "r3" value is out of range for data type "real". Update column to a legal value.

This proves it is a bug. I suggest you file a bug with Microsoft Connect for SQL Server.

Cully

+1, but: What part of this behavior exactly is a bug. is it: 1. Wrong constants documented in MSDN and used in DBCC, as well as wrong threshold for rounding down. 2. Update being able to save wrong values – Singhal

@AlexKuznetsov, no. 2 is the bug: sqlskills.com/BLOGS/PAUL/post/… ("In SQL Server versions prior to 2005, it was possible to import invalid data values into a database. These invalid values could cause query execution problems, or possibly even wrong results. In 2005, when the import 'holes' were closed"). They should be closed. This isn't official documentation but I guess the team will regard it as a bug. – Cully

Thanks! I will make sure that undocumented values do not get saved via UPDATE/MERGE command. – Singhal

@SQLkiwi, can you elaborate on that? Column r3 is outside of the documented range which is exactly what DBCC CHECKDB is reporting. I think that is convincing evidence. Paul Randals post seems to support this view, too. – Cully