The ECN No Name Newsletter is no longer being published. This is an archived issue.

Date Error Found In Unify On Vaxes

Unify is a relational database management system that operates on the ECN Vax computers. Recently a bug has been discovered in the current 3.2 version that affects searches involving a DATE field. Unify stores a DATE value as a two-byte short integer. For November 15, 1989 this value is octal 0177777. For November 16, 1989 the value 'rolls over' to octal 0. Unfortunately the 'order by' function in the Unify Structured Query Language (SQL) does not handle the 'roll over' correctly and thinks that November 16, 1989 is an earlier date than November 15, 1989! This is a problem for every Unify database on an ECN Vax that searches entries based on a DATE field.

As an example, suppose you had a Unify database that stores some information based on an entry date. The information could be stored in a table called 'mytab' that contains a DATE field called 'e_date' for the entry date. It is reasonable to use the following SQL script to print out all the e_date values in order:

select e_date from mytab order by e_date /

The output should look something like:

                            --------
                            10/12/88
                            11/15/89
                            11/16/89
                            05/20/90

However the print out from Unify 3.2 is certainly not expected!

                            ---------
                            11/16/89
                            05/20/90
                            10/12/88
                            11/15/89

A work-around is to create a dummy date field, say 'd_date' and copy each e_date value to d_date reduced by a constant number of days, such as 5000. The SQL script would be:

            update mytab set d_date = e_date - 5000 /

Now all the d_date values are below the 11/15/89 roll over date and the following SQL script will work:

           select e_date from mytab order by d_date /

                            ---------
                            10/12/88
                            11/15/89
                            11/16/89
                            05/20/90

Unify 3.2 was installed in 1985 and has been used by only a few groups of people on the ECN. The DATE error showed up in one of Agricultural Engineering's databases at the end of the 1989-90 fiscal year when reports were generated. Newer versions of Unify have the DATE problem corrected.

HTML