CODEWITHSUNDEEP
tsqlt
Hamish Gunn
Hamish Gunn

Reputation: 161

tSQLt Assert Failure Message Numeric Precision

How can I increase the precision of FLOAT failed assertion messages in tSQLt?

For example

        DECLARE @Expected FLOAT = -5.4371511392520810
    PRINT STR(@Expected, 40, 20)
    DECLARE @Actual FLOAT = @Expected - 0.0000000001
    PRINT STR(@Actual, 40, 20)
    EXEC tSQLt.AssertEquals @Expected, @Actual

gives

                 -5.4371511392520810
                 -5.4371511393520811

[UnitTest].[test A] failed: Expected: <-5.43715> but was: <-5.43715>

Upvotes: 2

Views: 443

Answers (1)

Reversed Engineer
Reversed Engineer

Reputation: 1242

In most computer languages (including T-SQL) floating point values are approximate, so comparing FLOAT variables for being equal is often a bad idea (especially after doing some maths on them) E.g. a FLOAT variable is only accurate to about 15 digits (by default)

You can see this by adding the following line at the end of your sample code:

PRINT STR((@Actual - @Expected) * 1000000, 40, 20)

which returns -0.0001000000082740

So you could either

  • Use the built in SQL function ROUND to allow numbers approximately the same to be viewed as equal:

    EXEC tSQLt.AssertEquals ROUND (@Expected, 14), ROUND (@Actual, 14)

  • Use an exact type for the variables, like NUMERIC (38, 19). Replacing every FLOAT in your example with NUMERIC (38, 19) seems to give the same result, but when you add the PRINT STR((@Actual - @Expected) * 1000000, 40, 20) mentioned above, it now prints exactly -0.0001000000000000, showing that there is an inaccuracy in the PRINT statement as well

Of course your tSQLt.AssertEquals test will still fail since the values are different in the 10th digit after the decimal point. (one number is ...925... and the other is ...935...). If you want it to pass even then, round the values off to 9 digits with ROUND

Further information:

See David Goldberg's excellent article What Every Computer Scientist Should Know About Floating-Point Arithmetic here or here under the heading Rounding Errors.

http://msdn.microsoft.com/en-us/library/ms173773.aspx

http://www.informit.com/library/content.aspx?b=STY_Sql_Server_7&seqNum=93

Upvotes: 3

Related Questions