Re: [HACKERS] Interval aggregate regression failure (expected seems

I think I've got it. I plan to update the regression tests this
evening, but I wanted to post what I believe is a solution.

select '41 mon'::interval / 10;
?column?
---------------
4 mons 3 days
(1 row)

select '41 mon 360:00'::interval / 10 as "pos"
, '-41 mon -360:00'::interval / 10 as "neg";
pos | neg
------------------------+---------------------------
4 mons 3 days 36:00:00 | -4 mons -3 days -36:00:00
(1 row)

select '41 mon -360:00'::interval / 10 as "pos"
, '-41 mon 360:00'::interval / 10 as "neg";
pos | neg
-------------------------+---------------------------
4 mons 3 days -36:00:00 | -4 mons -3 days +36:00:00
(1 row)

If anyone sees anything untoward, please let me know and I'll do my
best to fix it. Also, should the duplicate code in interval_mul and
interval_div be refactored into its own function?

Thanks!

Michael Glaesemann
grzm seespotcode net

---8<-----
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.165
diff -c -r1.165 timestamp.c
*** src/backend/utils/adt/timestamp.c 13 Jul 2006 16:49:16 -0000 1.165
--- src/backend/utils/adt/timestamp.c 29 Aug 2006 06:20:03 -0000
***************
*** 2494,2500 ****
float8 factor = PG_GETARG_FLOAT8(1);
double month_remainder,
day_remainder,
! month_remainder_days;
Interval *result;

result = (Interval *) palloc(sizeof(Interval));
--- 2494,2502 ----
float8 factor = PG_GETARG_FLOAT8(1);
double month_remainder,
day_remainder,
! month_remainder_days,
! month_remainder_day_frac,
! month_remainder_time;
Interval *result;

result = (Interval *) palloc(sizeof(Interval));
***************
*** 2519,2526 ****
/* fractional months full days into days */
month_remainder_days = month_remainder * DAYS_PER_MONTH;
result->day += (int32) month_remainder_days;
! /* fractional months partial days into time */
! day_remainder += month_remainder_days - (int32) month_remainder_days;

#ifdef HAVE_INT64_TIMESTAMP
result->time = rint(span->time * factor + day_remainder *
USECS_PER_DAY);
--- 2521,2556 ----
/* fractional months full days into days */
month_remainder_days = month_remainder * DAYS_PER_MONTH;
result->day += (int32) month_remainder_days;
!
! month_remainder_day_frac = month_remainder_days - (int32)
month_remainder_days;
!
! #ifdef HAVE_INT64_TIMESTAMP
! month_remainder_day_frac = month_remainder_days - (int32)
month_remainder_days;
! month_remainder_time = month_remainder_day_frac * USECS_PER_DAY;
! if (rint(month_remainder_time) == USECS_PER_DAY)
! {
! result->day++;
! }
! else if ((rint(month_remainder_time)) == -USECS_PER_DAY)
! {
! result->day--;
! }
! #else
! month_remainder_time = month_remainder_day_frac * SECS_PER_DAY;
! if ((TSROUND(month_remainder_time) == SECS_PER_DAY))
! {
! result->day++;
! }
! else if ((TSROUND(month_remainder_time) == -SECS_PER_DAY))
! {
! result->day--;
! }
! #endif
! else
! {
! /* fractional months partial days into time */
! day_remainder += month_remainder_day_frac;
! }

#ifdef HAVE_INT64_TIMESTAMP
result->time = rint(span->time * factor + day_remainder *
USECS_PER_DAY);
***************
*** 2548,2558 ****
float8 factor = PG_GETARG_FLOAT8(1);
double month_remainder,
day_remainder,
! month_remainder_days;
Interval *result;

result = (Interval *) palloc(sizeof(Interval));

if (factor == 0.0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
--- 2578,2596 ----
float8 factor = PG_GETARG_FLOAT8(1);
double month_remainder,
day_remainder,
! month_remainder_days,
! month_remainder_day_frac,
! month_remainder_time;
Interval *result;

result = (Interval *) palloc(sizeof(Interval));

+ /*
+ a: (fl) select '41 mon'::interval / 10;
+ *span = { time = 0., day = 0, month = 41 }
+ factor = 10.
+ */
+
if (factor == 0.0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
***************
*** 2560,2579 ****

month_remainder = span->month / factor;
day_remainder = span->day / factor;
result->month = (int32) month_remainder;
- result->day = (int32) day_remainder;
month_remainder -= result->month;
- day_remainder -= result->day;

! /*
! * Handle any fractional parts the same way as in interval_mul.
! */

/* fractional months full days into days */
month_remainder_days = month_remainder * DAYS_PER_MONTH;
result->day += (int32) month_remainder_days;
! /* fractional months partial days into time */
! day_remainder += month_remainder_days - (int32) month_remainder_days;

#ifdef HAVE_INT64_TIMESTAMP
result->time = rint(span->time / factor + day_remainder *
USECS_PER_DAY);
--- 2598,2648 ----

month_remainder = span->month / factor;
day_remainder = span->day / factor;
+
result->month = (int32) month_remainder;
month_remainder -= result->month;

! result->day = (int32) day_remainder;
! day_remainder -= result->day;

/* fractional months full days into days */
month_remainder_days = month_remainder * DAYS_PER_MONTH;
result->day += (int32) month_remainder_days;
!
! month_remainder_day_frac = month_remainder_days - (int32)
month_remainder_days;
!
! /*
! * Add to result->day any fractional days from the month
component that
! * round to 24 hour periods after being converted to time.
! * Handle any fractional parts the same way as in interval_mul.
! */
!
! #ifdef HAVE_INT64_TIMESTAMP
! month_remainder_time = month_remainder_day_frac * USECS_PER_DAY;
! if (rint(month_remainder_time) == USECS_PER_DAY)
! {
! result->day++;
! }
! else if ((rint(month_remainder_time)) == -USECS_PER_DAY)
! {
! result->day--;
! }
! #else
! month_remainder_time = month_remainder_day_frac * SECS_PER_DAY;
! if ((TSROUND(month_remainder_time) == SECS_PER_DAY))
! {
! result->day++;
! }
! else if ((TSROUND(month_remainder_time) == -SECS_PER_DAY))
! {
! result->day--;
! }
! #endif
! else
! {
! /* fractional months partial days into time */
! day_remainder += month_remainder_day_frac;
! }

#ifdef HAVE_INT64_TIMESTAMP
result->time = rint(span->time / factor + day_remainder *
USECS_PER_DAY);