Re: [HACKERS] Interval aggregate regression failure (expected seems - Mailing list pgsql-patches

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);