The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,860 other subscribers

Archive for the ‘Floating point handling’ Category

« Previous Entries
Next Entries »

On Epsilon, MachineEpsilon, and relative differences – via: I was wondering, that what is the closest value to the Zero floating point can have – G+

Posted by jpluimers on 2015/10/07

A long time ago, there was an interesting discussion here: I was wondering, that what is the closest value to the Zero floating point can have.

Recently I needed to do some calculations on series where getting close to zero could become a problem.

  • Math seems to have an Epsilon of 1E-12.
  • Sytem.Types has Epsilon of 1E-30 and Epsilon2 of 1E-40.
  • XE4+ FMX has IsEssentiallyZero and IsNotEssentiallyZero for Single values.

In practice it depends a lot on what you are doing. Sometimes absolute Epsilons are best, but at other times relative difference is much more applicable.

Then there is also a Machine Epsilon: a way to derive an Epsilon from a data type that works in all languages and platforms.

–jeroen

Posted in .NET, Algorithms, C, C#, C++, Delphi, Development, Floating point handling, Software Development | 1 Comment »

Excel: get content of a cell given the row and column numbers (ADDRESS, INDIRECT, ROW, COLUMN)

Posted by jpluimers on 2015/08/28

A while ago, I needed to do calculations on partially absolute cell references: for some number of rows, the cells needed to be fixed to the top row of each row group.

For a pure absolute cell reference, you’d prepend a dollar sign to the row or column of a cell. So A1 would become $A1 (to make column A absolute), A$1 (to make row 1 absolute) or $A$1 (to make both column A and row 1 absolute).

There is a nice short cut function key F4 to do this.

Excel does not have a built-in partially absolute cell reference solution.

To solve this, I used these addressing functions: ADDRESS, INDIRECT, COLUMN, ROW.

For all these functions, the ROW and COLUMN numbering starts at 1 (one) not 0 (zero).

The way I solved it was to added the below columns (first the reference:heading, then the formula).

The values in the formulas are for ROW 2 (cells A2..XFD2).

  1. AF: Calculation
    • =IF(D2=”*”;INDIRECT(AG2)&X2;””)
  2. AG: ZLookup
    • =ADDRESS($AH2;COLUMN(Z2))
  3. AH: Row
    • =2+12*TRUNC((ROW()-2)/12)

Column AH

Calculates the fixed row of the row group. There are 12 rows per group. ROW numbers start at 1, and there is one heading row, hence the 2+ and the -2.

Without TRUNC, the ROW result would be rounded (that is the default floating point to integer conversion that Excel uses).

There is no need to reference a specific row when calling ROW: if you leave it out, it will return the number of the current row.

Column AG

returns the address of the calculated ROW (from AH) combined with the

Column AF

Depending on the value of the D column, it calculates the outcome by combining

–jeroen

via:

Posted in Algorithms, Development, Excel, Floating point handling, Office, Office 2003, Office 2007, Office 2010, Office 2013, Power User, Software Development | 2 Comments »

Default comparers in Delphi used by TArray.Sort (via: Stack Overflow)

Posted by jpluimers on 2014/11/26

A long while ago, I posted a detailed answer on what functions the default comparers actually were calling to get a feel for if they would apply or not answering delphi – What does the default TArray.Sort comparator actually do and when would you use it? – Stack Overflow.

I needed that information recently because of some sorting issues I bumped into (sorting generic records), so finally a blog post.

First some links to documentation for even more background information:

There is the answer I gave: Read the rest of this entry »

Posted in Algorithms, Ansi, Delphi, Delphi 2009, Delphi 2010, Delphi XE, Delphi XE2, Delphi XE3, Delphi XE4, Delphi XE5, Development, Encoding, Floating point handling, Software Development, Unicode | 2 Comments »

Delphi: mapping of COM/Windows exceptions to Delphi Exceptions and run-time errors

Posted by jpluimers on 2014/07/30

Every time

---------------------------
Debugger Exception Notification
---------------------------
Project Some.exe raised exception class $C0000005 with message 'access violation at 0x00537b14: read of address 0x00000000'.
---------------------------
Break   Continue   Help
---------------------------

'Exception "EAccessViolation" with message "Access violation at address 00537B30 in module ''TestWordInterface.exe''. Read of address 00000000" at address 00537B30'

---------------------------
Testwordinterface
---------------------------
Access violation at address 00537B14 in module 'Some.exe'. Read of address 00000000.
---------------------------
OK
---------------------------

In system.pas:

{$IFDEF STACK_BASED_EXCEPTIONS}
procedure       MapToRunError(P: PExceptionRecord); stdcall;
const
  STATUS_ACCESS_VIOLATION         = $C0000005;
  STATUS_ARRAY_BOUNDS_EXCEEDED    = $C000008C;
  STATUS_FLOAT_DENORMAL_OPERAND   = $C000008D;
  STATUS_FLOAT_DIVIDE_BY_ZERO     = $C000008E;
  STATUS_FLOAT_INEXACT_RESULT     = $C000008F;
  STATUS_FLOAT_INVALID_OPERATION  = $C0000090;
  STATUS_FLOAT_OVERFLOW           = $C0000091;
  STATUS_FLOAT_STACK_CHECK        = $C0000092;
  STATUS_FLOAT_UNDERFLOW          = $C0000093;
  STATUS_INTEGER_DIVIDE_BY_ZERO   = $C0000094;
  STATUS_INTEGER_OVERFLOW         = $C0000095;
  STATUS_PRIVILEGED_INSTRUCTION   = $C0000096;
  STATUS_STACK_OVERFLOW           = $C00000FD;
  STATUS_CONTROL_C_EXIT           = $C000013A;
var
  ErrCode: Byte;
begin
  case P.ExceptionCode of
    STATUS_INTEGER_DIVIDE_BY_ZERO:  ErrCode := 200; { reDivByZero }
    STATUS_ARRAY_BOUNDS_EXCEEDED:   ErrCode := 201; { reRangeError }
    STATUS_FLOAT_OVERFLOW:          ErrCode := 205; { reOverflow }
    STATUS_FLOAT_INEXACT_RESULT,
    STATUS_FLOAT_INVALID_OPERATION,
    STATUS_FLOAT_STACK_CHECK:       ErrCode := 207; { reInvalidOp }
    STATUS_FLOAT_DIVIDE_BY_ZERO:    ErrCode := 200; { reZeroDivide }
    STATUS_INTEGER_OVERFLOW:        ErrCode := 215; { reIntOverflow}
    STATUS_FLOAT_UNDERFLOW,
    STATUS_FLOAT_DENORMAL_OPERAND:  ErrCode := 206; { reUnderflow }
    STATUS_ACCESS_VIOLATION:        ErrCode := 216; { reAccessViolation }
    STATUS_PRIVILEGED_INSTRUCTION:  ErrCode := 218; { rePrivInstruction }
    STATUS_CONTROL_C_EXIT:          ErrCode := 217; { reControlBreak }
    STATUS_STACK_OVERFLOW:          ErrCode := 202; { reStackOverflow }
  else                              ErrCode := 255;
  end;
  RunErrorAt(ErrCode, P.ExceptionAddress);
end;

Posted in Algorithms, Development, Floating point handling, Software Development, Uncategorized | Leave a Comment »

floating point – SQL Server: Calculation with numeric literals requires to cast to obtain the right precision (via: Stack Overflow)

Posted by jpluimers on 2013/12/24

This has bitten me so many times, so I’m glad I found the below question/answers on StackOverflow.

When you perform calculations in SQL Server involving numeric literals, you have to take into account which precision you want your result to be, and CAST/CONVERT  the literals accordingly.

The reason is condensed to this statement by Lieven Keersmaekers:

SQL Server uses the smallest possible datatype.

He follows with examples to view the actual representation of a literal/expression using SQL_VARIANT_PROPERTY (which has been there since at least SQL Server 2000).

SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale')
SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')

Read the rest of this entry »

Posted in Algorithms, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »

SQL Server: some links on BULK IMPORT format files

Posted by jpluimers on 2013/12/04

From my link archive:

Note that for importing decimal/numeric columns, you have two options:

  1. Cast through FLOAT using a FORMAT file
  2. Use OpenRowSet with VARCHAR, then CAST afterwards
    Weird rounding for decimal while doing a bulk insert from a CSV.

Some more links on this:

–jeroen

Posted in Algorithms, CSV, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »

Delphi – Direct3D and the wrong FPU state: Now() function returns a wrong value (via: StackOverflow)

Posted by jpluimers on 2013/10/31

The question datetime – Delphi Now() function returns a wrong value – Stack Overflow is similar to my article Delphi – Michael Justin had strange floating point results when his 8087 FPU Control Word got hosed.

Good that stackoverflow user Anton Zhuchkov found out the cause himself: his answer indicates the Precision Control (and rounding) part of the FPU state got hosed by wrongly initializing the Direct3D device.

I edited his answer with some extra links to documentation.

Finally I’ve found the solution. I needed to specify the D3DCREATE_FPU_PRESERVE flag when creating a D3D device by D3D.CreateDevice.

Otherwise, without that flag, all floating point operations are performed with single precision. As the TDateTime is a simple Double, and Now() functions is consist of simple addition of date value to time value, it all get messed up by DirectX “smart” override.

Problem solved. It was a tricky one indeed. :)

–jeroen

via: datetime – Delphi Now() function returns a wrong value – Stack Overflow.

Posted in 8087, Algorithms, Delphi, Delphi 2005, Delphi 2006, Delphi 2007, Delphi 2009, Delphi 2010, Delphi 5, Delphi 6, Delphi 7, Delphi XE, Delphi XE2, Delphi XE3, Development, Floating point handling, Software Development | 2 Comments »

Tables with SQL Server and .NET data types

Posted by jpluimers on 2013/10/23

Thanks StackOverflow users George Stocker for asking, Örjan Jämte and Sir Crispalot for answering.

Below is the SQL Server 2012 table, in which I added links to the various data types.

I also added two columns with linked references to the types from the  C# data types, C# Keywords, Reference Tables for Types (C# Reference) and Data Type Summary (Visual Basic).

One of the things I need to check is against the LINQ SQL-CLR Type Mapping.

It is very important to keep in mind that in SQL, each combination of precision and digits gets you a different decimal type, and all of them are different from the .NET decimal type. See for instance the answers on these questions:

Read the rest of this entry »

Posted in .NET, .NET 2.0, .NET 3.0, .NET 3.5, .NET 4.0, .NET 4.5, Algorithms, C#, C# 1.0, C# 2.0, C# 3.0, C# 4.0, C# 5.0, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »

Table with the Numeric Data Types in SQL Server

Posted by jpluimers on 2013/10/22

I couldn’t find a table with numeric data types in SQL Server 2012 on MSDN, but since they have not changed since SQL Server 2008,  I copied the table from Understand the 9 Numeric Data Types in SQL Server 2008, added an entry for bit, and links to the relevant SQL Server 2012 pages at MSDN.

Edit: somehow the WordPress editing system suppressed all the superscripts (for the powers of 2 and 10), so I replaced them with caret signs and powers of 2 and 10 to make it more clear and verified them against Floating point numbers and these Wikipedia pages:

Data Type Range of Values Storage Space

Data Type Range of Values Storage Space
tinyint 0 to 255 1 byte
smallint –32,768 to 32,767 2 bytes
int –2^31 to 2^31–1 4 bytes
bigint –2^63 to 2^63–1 8 bytes
decimal(p,s)
numeric(p,s)		 –10^38+1 to 10^38–1 5 to 17 bytes
smallmoney –214,748.3648 to 214,748.3647 4 bytes
money –922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
real –3.4*10^38 to –1.18*10^38, 0, and 1.18*10^38 to 3.4*10^38 4 bytes
float(n) –1.79*10^308 to –2.23*10^308, 0, and 2.23*10^308 to 1.79*10^308 4 bytes or 8 bytes
bit 0 to 1 0+ bytes

Later I found an even more complete table at SQL Server Data Types Reference – ConnectionStrings.com.

–jeroen

via: Understand the 9 Numeric Data Types in SQL Server 2008.

Posted in Algorithms, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »

A small table that shows differences between decimal, double and float (Single)

Posted by jpluimers on 2013/10/16

Though you programmers all should have read What Every Computer Scientist Should Know About Floating-Point Arithmetic.

But I know you don’t, so below is a small difference on which floating point comparisons fail when using float (f, Single), double (d) and decimal (m, money) values in C#:

0.1 fd_cast_none fd_cast_up fm_cast_none dm_cast_none
0.3 fd_cast_none fd_cast_up fm_cast_none dm_cast_none
0.7 fd_cast_none fd_cast_up fm_cast_none dm_cast_none
0.5 fm_cast_none dm_cast_none
0.9 fd_cast_none fd_cast_up fm_cast_none dm_cast_none

The can help you decide what kind of floating point type you want to use, for instance to answer c# – When should I use double instead of decimal? – Stack Overflow.

I specifically choose the values 0.1, 0.3, 0.5, 0.7 and 0.9 to stress the difference between binary and decimal representations. Apart from the decimal type, you cannot store these decimal values in a binary representation. You can see the decimal representation for a double using the DoubleConverter class (thanks Jon Skeet!)

If you are have a problem that isn’t suite for floating point, then don’t use it. Use rational types, IntX, BigInteger or Complex from the System.Numerics namespace, or arbitraty precision floating point numbers.

The failing methods are part of a bigger DecimalDoubleSingleTestProject, for which you will find the source at BeSharp.net repository (you can browse the sources, and access it through SVN and TFS).

That project contains more checks (see the table at the end which includes 0.100000000001 and 0.1222222222222222222221 based on the accuracy you can get with float/double/decimal) and the failing/succeeding methods are the same.

This is what the failing fd_cast_none, fd_cast_up, fm_cast_none and dm_cast_none methods do:

//...
    [TestClass]
    public class UnitTestBase
    {
        protected float f { get; private set; }
        protected double d { get; private set; }
        protected decimal m { get; private set; }
//...
        public void TestMethod_fd_cast_none()
        {
                Assert.AreEqual(f, d);
        }
//...
        public void TestMethod_fd_cast_up()
        {
                Assert.AreEqual((double)f, d);
        }
//...
        public void TestMethod_fm_cast_none()
        {
                Assert.AreEqual(f, m);
        }
//...
        public void TestMethod_dm_cast_none()
        {
                Assert.AreEqual(d, m);
        }
    }

The methods seem to succeed, but that is just the deceiving part: when you carefully select the values you compare, all checks will eventually fail.

The table at the end shows some more literals that fail other tests. It is caused by all these types have different storage formats.

<h3>Conclusion</h3>

When comparing floating point literals, make sure they are of the same type, and select the type according to what precision or representation features you need.

Note this gets even worse when you start calculating with floating points. You will almost always loose accuracy, watch rounding errors and you cannot even do direct AreEqual comparisons any more. Read the articles by Eric Lippert tagged floating+point+arithmetic – Fabulous Adventures In Coding.

--jeroen

Full table:

0.1 fd_cast_none fd_cast_up fm_cast_none dm_cast_none
0.3 fd_cast_none fd_cast_up fm_cast_none dm_cast_none
0.5 fm_cast_none dm_cast_none
0.7 fd_cast_none fd_cast_up fm_cast_none dm_cast_none
0.9 fd_cast_none fd_cast_up fm_cast_none dm_cast_none
0.100000000001 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none
0.300000000003 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none
0.500000000005 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none
0.700000000007 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none
0.900000000009 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none
0.1222222222222222222221 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_up
0.3222222222222222222223 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_up
0.5222222222222222222225 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_up
0.7222222222222222222227 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_down dm_cast_up
0.9222222222222222222229 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_up
144444444444444444444.1 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_down dm_cast_up
344444444444444444444.3 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_up
544444444444444444444.5 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_up
744444444444444444444.7 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_up
944444444444444444444.9 fd_cast_none fd_cast_up fm_cast_none fm_cast_up dm_cast_none dm_cast_up

Posted in .NET, .NET 1.x, .NET 2.0, .NET 3.0, .NET 3.5, .NET 4.0, .NET 4.5, Algorithms, C#, C# 1.0, C# 2.0, C# 3.0, C# 4.0, C# 5.0, Development, Floating point handling, Jon Skeet, Software Development | Leave a Comment »

« Previous Entries
Next Entries »