Re: date manip. prob with AIX & OnLine 5.05.UC6
On Thu, 6 Nov 1997, Nigel Gall wrote:
> I reproduced the error on my platform, but I didn't set my system date to
> Nov 1st. Instead, I executed the following SQL today (November 6, 1997):
>
> select extend(today - 6, month to month),
> extend(today - 6, day to day)
> from systables
> where tabid = 1
>
> If I subtract 5 or 7 from today, I get the results I expect. It seems the
> error only pops up when I try to get to Oct. 31st. Strange, but that shows
> that this error may not be platform specific and it exists in 5.05.UC1 and
> 5.05.UC6.
It is a generic problem, and is bug B55557.
> As another test, I subtracted 37 from today, to see if I'd get the problem
> trying to get Sept. 30, and it worked fine (returned 9 30 as expected).
> What's so special about October 31st, 1997?
The gist of the problem is that November has only 30 days, so the datetime
code assumes that no month can have more than 30 days, or thereabouts.
It's more subtle than that, but the problem only shows up in February,
April, June, September and November.
> My platform is Digital Unix 3.0B, OnLine 5.05.UC1.
According to the information I have available, it is still present in
5.10.UC1 (and in 6.00); it is coded in 7.22, 8.20, and 9.00 upwards.
Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>
BUG NUMBER 55557
Product: BACKEND Date: 06/20/96 Dup. Bug ID:
Component Component Version
--------- -----------------
GENLIB 7.20.UC1
Type: SW Submitter: johnl
---- Short Description ----
SUBTRACTING DATETIMES SOMETIMES GIVES THE WRONG ANSWER, SUCH AS ZERO
FROM EXTEND(DATETIME(1995-12-31) YEAR TO DAY,DAY TO DAY)-DATETIME(1)
DAY TO DAY
---- Long Description ----
Given the table and data below, the SELECT statement:
SELECT (EXTEND(X, DAY TO DAY) - DATETIME(1) DAY TO DAY) FROM Dt;
produces the answer 0 (an INTERVAL DAY TO DAY), rather than the expected
30. It may or may not be coincidental that this was tested in a 30-day
month (June 1996).
CREATE TEMP TABLE Dt(X DATETIME YEAR TO DAY NOT NULL);
INSERT INTO Dt VALUES (DATETIME(1995-12-31) YEAR TO DAY);
This was tested using DB-Access and OnLine 7.20.UC1 on Solaris 2.4, and
also with 6.00.UE1 DB-Access and OnLine, so it is not a brand new bug.
The EXTEND is not critical to reproducing the problem -- this SELECT also
returns zero when it should return 30:
SELECT (DATETIME(31) DAY TO DAY) - DATETIME(1) DAY TO DAY
FROM SysTables WHERE Tabid = 1;
More interesting was testing with 5.03.UC1 OnLine; for the SysTables
version, it gave -201 "syntax error" until I changed the 31 into a 30,
whereupon it gave the correct answer -- 29. I think the error message is
inappropriate, but the 30-day month part may be significant. I'm not
clear that it should matter, but it appears to do so.
The full context of the problem is shown in the email below.
===========================================================================
Subject: Problem with DATETIME arithmetic (fwd)
To: johnl@informix.com (Jonathan Leffler)
Date: Thu, 20 Jun 1996 12:56:08 -0700 (PDT)
Yes, this is definitely a problem. I was able to reproduce it in
Falcon. I haven't had time to track it down. Go ahead and PTS it.
> Date: Mon, 17 Jun 1996 16:04:05 -0700
> From: johnl@informix.com (Jonathan Leffler)
> Subject: Problem with DATETIME arithmetic
>
> I've run into a problem with the SQL script after my signature line, which
> can be run with DB-Access. It produces the output:
>
> 1995-12-30 1994-11-01 30 29 1994-11-30
> 1995-12-31 1994-11-01 31 00 1994-11-01
> 1996-01-30 1994-12-01 30 29 1994-12-30
> 1996-01-31 1994-12-01 31 00 1994-12-01
> 1996-02-28 1995-01-01 28 27 1995-01-28
> 1996-02-29 1995-01-01 29 28 1995-01-29
> 1996-03-30 1995-02-01 30 29 1995-03-02
> 1996-03-31 1995-02-01 31 00 1995-02-01
> 1996-04-29 1995-03-01 29 28 1995-03-29
> 1996-04-30 1995-03-01 30 29 1995-03-30
> 1996-05-30 1995-04-01 30 29 1995-04-30
> 1996-05-31 1995-04-01 31 00 1995-04-01
> 1997-03-28 1996-02-01 28 27 1996-02-28
> 1997-03-29 1996-02-01 29 28 1996-02-29
> 1997-03-30 1996-02-01 30 29 1996-03-01
> 1997-03-31 1996-02-01 31 00 1996-02-01
> 1997-04-01 1996-03-01 01 00 1996-03-01
> 1997-04-02 1996-03-01 02 01 1996-03-02
>
> Now, that fourth column is the result of subtracting DATETIME(1) DAY TO DAY
> from the third column. All the zeros except the second last are not
> explicable to me. Either there should be an error (presumably because
> there are only 30 days in June, though the third column should have the
> same problem), or it should give a valid value of 30. Any idea what gives?
>
> This is basically a GENLIB question, I think. I'm running with 7.20.UC1
> OnLine.
>
> Yours,
> Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
>
> ===========================================================================
>
> CREATE TEMP TABLE Dt(X DATETIME YEAR TO DAY NOT NULL); >
> INSERT INTO Dt VALUES ( DATETIME(1997-04-02) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1997-04-01) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1997-03-31) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1997-03-30) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1997-03-29) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1997-03-28) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-05-31) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-04-30) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-03-31) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-02-29) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-01-31) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1995-12-31) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-05-30) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-04-29) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-03-30) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-02-28) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1996-01-30) YEAR TO DAY );
> INSERT INTO Dt VALUES ( DATETIME(1995-12-30) YEAR TO DAY ); >
> SELECT X,
> EXTEND((EXTEND(X, YEAR TO MONTH) - INTERVAL(1-1) YEAR TO MONTH), YEAR TO DAY)
> AS P1,
> EXTEND(X, DAY TO DAY)
> AS P2,
> (EXTEND(X, DAY TO DAY) - DATETIME(1) DAY TO DAY)
> AS P3, > EXTEND((EXTEND(X, YEAR TO MONTH) - INTERVAL(1-1) YEAR TO MONTH), YEAR TO DAY) +
> (EXTEND(X, DAY TO DAY) - DATETIME(1) DAY TO DAY)
> AS Z2
> FROM Dt