Subscribe Bookmark RSS Feed

Why can't I select certain numerical rows?

I’m having an inexplicable problem with a script, shortened version below:

Clear Globals();

New Table( "A", New Column( "I", numeric, continuous ) );

aggregateColumn = {};

For( j = 1, j <= 5, j++,

     

allCurrents = {};

      For( k = 0.0, k <= 6.0, k += 0.1, Insert Into(allCurrents, k); );

      Column("I") << Set Values(allCurrents);

     

      Insert Into(aggregateColumn, As List(Column("I") << Get Values));

);

aggTable = New Table( "B",

      New Column( "I", numeric, continuous, Set Values(aggregateColumn) )

);

Now, in the the resulting data table “B”, the command:

Current Data Table() << Select Where(I == 0.5);

Successfully selects the row with 0.5, but:

Current Data Table() << Select Where(I == 1.0);

Selects nothing!  Also won't select 0.0, 0.3, 0.4, 0.8, 0.9 and many others.  What’s going on here?

Thanks.

20 REPLIES
mpb

Super User

Joined:

Jun 23, 2011

If you change the format of column I in table B to, say, Fixed Decimal (25,18), you will see that the values generated are very slightly "off". This due to

     1. Inability of decimal values to be represented exactly

     2. Imprecision arising from numerical operations on imprecisely represented decimal values

For this reason some care must be taken if you intend to do row selection based on comparison of continuous numerical values.

You can make a new column called, say, Reference, as numeric, continuous, and enter by hand the values 0, 0.1, 0.2, ..., 0.9, 1. Then, if you make a new numeric, continuous column called Diff with a formula of :I-:Reference you will see that several rows are different from zero. So testing the difference reveals the issue.

If you change the formula to Round(:I-:Reference, 1) then you should see agreement. In particular this formula should select the row corresponding to 1.0 in your table:

Current Data Table() << Select Where(Round(I - 1.0, 1) == 0);

Corrected Round formula.

The odd thing is that I considered the possibility of a floating-point rounding error, but had the same result even when using fixed precision, for example:

Clear Globals();

New Table( "A", New Column( "I", Fixed Decimal (5,2), continuous ) );

aggregateColumn = {};

For( j = 1, j <= 5, j++,

    

allCurrents = {};

      For( k = 0.0, k <= 6.0, k += 0.1, Insert Into(allCurrents, k); );

      Column("I") << Set Values(allCurrents);

    

      Insert Into(aggregateColumn, As List(Column("I") << Get Values));

);

aggTable = New Table( "B",

      New Column( "I", Fixed Decimal (5,2), continuous, Set Values(aggregateColumn) )

);

In this case, when I increase the width and precision of column I in table B I see:

0.0000000000000000

0.1000000000000000

0.2000000000000000

0.3000000000000001

...

Apparently the numbers in the for loop are numerics, but shouldn't they be casted to fixed precision in the New Column() call?  This suggests that the cast from numeric -> fixed always uses the maximum possible precision behind the scenes.

mpb

Super User

Joined:

Jun 23, 2011

Changing the format doesn't change how the value is stored, only how it is displayed.

Is it possible to cast the constants in the loop to fixed precision?

tom

Community Trekker

Joined:

Jul 9, 2011

ROUND the values before storing them.

mpb

Super User

Joined:

Jun 23, 2011

Unfortunately that doesn't help here. E.g. Round(0.3, 1)  (round to 1 decimal place) is stored same as 0.3.in JMP.

tom

Community Trekker

Joined:

Jul 9, 2011

So?  Then if you test if the stored value matches the constant that you have typed into your code it will match.

mpb

Super User

Joined:

Jun 23, 2011

No. It's a hardware issue. In base 10 numbers like 0.1 or 0.3 are exact values. However in the base that the computer uses, they are not exact.

This should be true for numerics but not fixed precision, unless fixed precision is just cosmetic.  In `fixed precision(2,1)`, 0.3 == 0.3 by definition.

I believe this question remains unanswered for the case of fixed precision. 

Are the JMP data types actually documented anywhere?