Re: [HACKERS] failed datetime regression tests

I'm taking a MS level database design class this semester, and didn't
really want to use the school's ORACLE database on their machines, so...

I built the postgres-6.3.2 package on my NetBSD-current/pc532 machine
over the last few days. For those of you who care slightly, this is
a NS32532 based machine from the early '90s and I had to add a couple
of lines af assembler to get the thing to build 8-) I'll send a set
of diffs in when I think it works.

My problem is that it failed several of the regression tests. int2 and int4
scared me until I saw that my machine said something like "number too big"
and the expected output was something like "bad format"... However, I
got the following lines out of the test with diff -c:

*** expected/datetime.out Sun Jan 4 19:35:27 1998
--- results/datetime.out Mon Aug 31 18:38:04 1998
***************
*** 19,31 ****
QUERY: SELECT ('current'::datetime = 'now'::datetime) as "True";
True
----
! t
(1 row)

QUERY: SELECT ('now'::datetime - 'current'::datetime) AS "ZeroSecs";
! ZeroSecs
! --------
! @ 0
(1 row)

QUERY: CREATE TABLE DATETIME_TBL( d1 datetime);
--- 19,31 ----
QUERY: SELECT ('current'::datetime = 'now'::datetime) as "True";
True
----
! f
(1 row)

QUERY: SELECT ('now'::datetime - 'current'::datetime) AS "ZeroSecs";
! ZeroSecs
! ---------------------------------------
! @ 486 days 22 hours 22 mins 13 secs ago
(1 row)

QUERY: CREATE TABLE DATETIME_TBL( d1 datetime);

That doesn't seem right 8-) Any suggestions?
Jon Buller

> I'll send a set
> of diffs in when I think it works.

Great. Always happy to add one more notch to our pistol :)

> and the expected output was something like "bad format"... However, I
> got the following lines out of the test with diff -c:
> --- results/datetime.out Mon Aug 31 18:38:04 1998
> QUERY: SELECT ('now'::datetime - 'current'::datetime) AS "ZeroSecs";
> ! ZeroSecs
> ! ---------------------------------------
> ! @ 486 days 22 hours 22 mins 13 secs ago
> (1 row)

This seems consistant with 'current' coming up zero, since datetime is
stored as a double float in seconds from 2000-01-01. Don't know why that
would be, but you might want to enable debugging print statements in
backend/utils/adt/dt.c by doing the following:
1) edit the Makefile in that directory to add
CFLAGS += -DDATEDEBUG

2) do a "make clean" in that directory, then rebuild your executables.

3) run postmaster from a window, so you can see the results of print
statements.

4) connect a psql session from another window, and try some sample
queries like your failed ones above. The print statements might give you
some clues if you follow them through the code in a third window :)

Let me know if you get any evidence...

- Tom