Home > Applications > The problem with numbers

The problem with numbers

UniData, like other weakly-typed systems, makes some programming tasks easier by not needing the developer to declare and adhere to a data type with variables. The general pros and cons of this have been debated many times across many languages and hence will not discussed here. What will be discussed is specific cases where this can cause you unexpected headaches.

A posting was made on the u2ug forums by Koglan Naicker about some unexpected issues when finding duplicates in a data-set.

In short, he found that when strings contained large numbers, it would sometimes incorrectly evaluate two different strings as equal. For example:

IF '360091600172131297' EQ '360091600172131299' THEN CRT "Equal"

The above code results in “Equal” being displayed on the screen. This is caused by a combination of 2 factors.

The first being that UniData is weakly typed. This means that it does not explicitly distinguish between strings and numbers, but attempts to determine the data type by examining the data. In this case, since the strings are numeric, it automatically treats them as numbers.

The second part of this issue is because now that it is treating those 2 strings as numbers, it needs to handle them in an appropriate data type on the CPU. Since the 2 strings are too large to be treated as an integer, they get converted to a floating-point number. Due to rounding that occurs, this actually results in both of these strings being converted to the same float-point representation! A better method may have been to use something such as Bignum instead of converted to floating-point. There would be a speed trade-off, but surely that would have been better than potentially incorrect programs.

Some people suggest prefixing or appending a non-number character to each string to force them to be treated as a string. Not entirely elegant and can have performance implications. Fortunately, UniData does have proper functions to handle these situations. In the case where you will be comparing strings that may consist of only numeric characters, you should use the SCMP function. This function compares two strings as strings, regardless of the actual data in them. Using this when you need to be certain how the comparison is performed can save you a lot of headaches in the future.

Also of interest is that this issue doesn’t just apply to UniBasic, but can also affect UniQuery!

It should be noted though, this only affects UniQuery when the dictionary item is right-aligned with the format field (eg, 20R in attribute 5).

You can tested this by creating a file and creating 3 records with the @ID of ‘360091600172130474’, ‘360091600172131297’ and ‘360091600172131299’.

Now, select upon the file where the @ID = ‘360091600172131297″ and you can see that 2 records are returned!

Results of selection

Non Unique Select

When explicitly selected a record via a unique key, this isn’t the result a database should return.

So, when dealing with large, potentially numeric fields with UniQuery, you may need 2 dictionary items. A left-aligned one for selecting on and a right-aligned one if you require numerical sorting.

  1. Ross Morrissey
    October 11, 2010 at 5:10 pm

    Very interesting stuff Dan, but I bet you meant EQ in your first example instead of AND. I’m sure far more people are caught by the “evaluates to something other than zero in what looks like a boolean expression” trap than your very very big int trap.

  2. October 11, 2010 at 5:49 pm

    Yes, yes I did :). Thanks for the spot.

    I completely agree, more so if you include other languages. The hard part about this one is that you may not have every been caught out by this one in another language before and the reason isn’t immediately obvious.

    I highly doubt anyone would have to seriously consider this when deal explicitly with numbers, but it is more than probably if you are dealing with strings that might just occasionally be numeric by chance. When you deal with vast quantities of data, it is amazing how common edge-cases can be!

  3. Marina Rozman
    September 24, 2012 at 12:29 am

    Hi Dan,
    Does attribute location makes a difference if file is indexed on that attribute?
    WOuld you have any useful links on select performance improvement?

    • October 4, 2012 at 7:39 pm

      Hi Marina,
      If the index is utilized, no*. However, there are many situations that would result in the engine ignoring the index (such as SELECTing using wildcards).

      * When updating a record, it will take slightly longer as it has to search deeper in the record to grab the attributes value to write in the index. I wouldn’t be concerned with this as it would take some very extreme situation for this to make even a trivial difference to your system.

      No links I can think of currently.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: