Re: Possible Feb 29, 2000 Bug?

In message <Pine.GSO.3.96.980811105647.17056K- 100000@osiris.informix.com>, Jonathan Leffler <jleffler@informix.com> writes >On Tue, 11 Aug 1998, Larry Kemmerling wrote: >> While doing Y2K testing, our organization came across >> a problem trying to subtract a 1 year interval from a >> datetime column with the value "2000-02-29 00:00:00". > >This is actually not a Y2K problem at all. It is also a FAQ, though >whether it is in the official Informix FAQ is another matter. > Yes, in the latest release... >> Here's some sample code: >> >> CREATE TEMP TABLE tFoo >> ( DateVal Datetime year to second >> ) WITH NO LOG;>> >> INSERT INTO tFoo >> ( DateVal ) >> VALUES >> ( "2000-02-29 00:00:00" );>> >> SELECT ( DateVal - INTERVAL (365) DAY(9) TO DAY ) >> FROM tFoo; >> >> SELECT ( DateVal - INTERVAL (366) DAY(9) TO DAY ) >> FROM tFoo; >> >> SELECT ( DateVal - INTERVAL (1) YEAR(9) TO YEAR ) >> FROM tFoo; >> >> >> The last select statement causes error -1267. Error -1267 >> says: >> >> -1267 The result of a datetime computation is out of range. >> >> In this statement, a DATETIME computation has produced a value that >> cannot be stored. This situation can occur, for example, if a very >> large interval is added to a DATETIME. Review the expressions in the >> statement, and see if you can change the sequence of operations to >> avoid the overflow. >> >> I might have expected error -1206, but not the 1267 error. Is there >> something I'm missing? > >You are mixing an INTERVAL YEAR TO MONTH value with a DATETIME value tha >includes DAY..FRACTION components, and the results of such computations are >unpredictable when you are working with the tail end of a month (any date >after the 28th in general). There is no 29th February 1999; the >computation for 1 year earlier than 29th February 2000 produces that date, >which is invalid. We can argue the toss over error numbers, but the >calculation is erroneous. > >In my view, all such mixed mode calculations should be banned outright >because they are not unambiguous for all possible values, but they are >allowed and they cause this FAQ. Admittedly, this is a slightly different >spin on the standard question, but the net result is the same. > > ************************************************************* > ** Don't mix calculations with intervals in the YEAR/MONTH ** > ** class with DATETIME values which include and components ** > ** from the DAY..FRACTION class. ** > ************************************************************* > >> The value of DBCENTURY has no effect whatsoever. > >Of course not; DBCENTURY only affects the conversion of 2-digit years in >strings into DATE values. Period. There isn't a DATE value in sight, nor >a string -- hence there is no way DBCENTURY could have an effect. > >> I found this problem running against 7.30.UC3 on Solaris 2.6. I've >> confirmed the problem exists with 7.14.UD1 on Solaris 2.5 and 5.04.UC1 >> on SunOs 4.1.4. >> >> I've opened a case with tech support, but I'm wondering if anyone else >> has encountered this problem. > >Lots of people, lots of times. It is not a Y2K issue, although your >example superficially appears like one (change 2000 in your examples to >1996, or 1904, or any other leap year, and you get the same results). The >last calculation is not properly defined and produces an erroneous answer. >Always has; probably always will. > >> Thanks for your time. > >No problem; just let your Informix Tech Support person know I've read you >the riot act and that this is a FAQ and the code in question is faulty. If >you need me to talk to the Tech Support person, tell me who I should >contact. > >Yours, >Jonathan Leffler (jleffler@informix.com) #include <witticism.h> >Guardian of DBD::Informix v0.59 -- http://www.perl.com/CPAN >Informix IDN for D4GL & Linux -- http://www.informix.com/idn > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care