Why can't I select certain numerical rows?

unknown

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.

mpb

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.