IDS Forum

Re: Leap Year Issue

Posted By: Jonathan Leffler
Date: Wednesday, 29 February 2012, at 3:33 p.m.

In Response To: Leap Year Issue (WALTER MILAN)

On Wed, Feb 29, 2012 at 11:20, WALTER MILAN <walter.d.milan@gmail.com>wrote:

> Wonder if you guys are also experiencing this error. All My queries running
> something like this are failing
>
> select today - 2 units year from systables
>

This is the way that Informix has worked since DATETIME was introduced in
version 4.00 back in about 1988.

Two years ago, there was no 29th of February, and the error is telling you
that.

You get similar results with subtracting a number of months for any date
from the 29th-31st of a month too. Mostly, subtracting years works fine -
but Leap Day is the exception (when the number of years subtracted is not a
multiple of 4, and even that could run into problems if the target year is
1700, 1800, 1900, 2100, 2200, 2300 etc which were not themselves leap
years, or will not be leap years).

Workaround is fairly simple and now are combing our code to see if we can
> change it for something like this
>
> select today - (2 * 365) from systables
>
> We had this issue 8 years ago too, but it was fixed on version 10. Now
> this is
> happening on 11.1, 11.5 and 11.7.
>
> It is weird how bugs became alive after a while. Zombie Bugs! Just a
> couple of
> hours ago a PMR was opened with IBM but I have not heard anything back
> yet. I
> suspect they are furiously writing a FixPack or may be let the day pass ...
>

What do you mean 'it was fixed in 10.00'? Do you have a bug number to
justify that assertion? TTBOMK, it has never been fixed, though the issue
has been raised once more in the last few weeks.

Note that one of the many problems with not generating an error is that you
can end up with different answers depending on how the SQL optimizer
chooses to process:

(DATETIME(2012-02-29) - 2 UNITS YEAR) + 2 UNITS YEAR

If it observes that the the constant subtracted and added are the same, it
might eliminate that calculation, giving you a result 2012-02-29. If it is
fixed to give DATETIME(2010-02-28) as the result of subtracting 2 years,
then the addition gives you 2012-02-28. So the double computation gives a
different answer from what you get by omitting 'adding 0' to a date. This
is at best confusing. The error is much the safest way to do business.

That said, customer pressure may end up requiring we change 20+ years of
behaviour, but that opens up the absurdities of ((x - y) + y) != x.

(The problem is actually more extreme for months:

(DATETIME(2012-05-31) - 1 UNITS MONTH) + 1 UNITS MONTH == 2012-05-30

(DATETIME(2012-05-31) - 2 UNITS MONTH) + 2 UNITS MONTH == 2012-05-31

(DATETIME(2012-05-31) - 3 UNITS MONTH) + 3 UNITS MONTH == 2012-05-29

(DATETIME(2011-05-31) - 3 UNITS MONTH) + 3 UNITS MONTH == 2011-05-28

Etcetera.)

--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

--f46d04088d8bc2c29904ba203f72

Messages In This Thread

• Leap Year Issue
  WALTER MILAN -- Wednesday, 29 February 2012, at 2:20 p.m.
  • Re: Leap Year Issue
    Jonathan Leffler -- Wednesday, 29 February 2012, at 3:33 p.m.
    • Re: Leap Year Issue
      WALTER MILAN -- Wednesday, 29 February 2012, at 4:10 p.m.
  • Re: Leap Year Issue
    Hrvoje Zokovic -- Wednesday, 29 February 2012, at 4:14 p.m.
    • Re: Leap Year Issue
      rroussey@comcast.net -- Wednesday, 29 February 2012, at 4:58 p.m.
      • Re: Leap Year Issue
        Long Nguyen -- Thursday, 1 March 2012, at 12:28 a.m.
        • Re: Leap Year Issue
          Mike Aubury -- Thursday, 1 March 2012, at 2:55 a.m.
          • Re: Leap Year Issue
            Long Nguyen -- Thursday, 1 March 2012, at 6:38 a.m.
    • Re: Leap Year Issue
      FRANK J. COMPUTER -- Wednesday, 2 May 2012, at 11:29 p.m.
  • Re: Leap Year Issue
    Hrvoje Zokovic -- Wednesday, 29 February 2012, at 4:56 p.m.
    • Re: Leap Year Issue
      Alan D. -- Thursday, 1 March 2012, at 5:19 a.m.
  • Re: Leap Year Issue
    Alan D. -- Thursday, 1 March 2012, at 5:16 a.m.
  • Re: Leap Year Issue
    Hrvoje Zokovic -- Thursday, 1 March 2012, at 6:44 a.m.
  • Re: Leap Year Issue
    FRANK J. COMPUTER -- Wednesday, 2 May 2012, at 11:43 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.