Bug 1500

Hi,

I have found what is causing the crash described in Bug 1500. Now I
would like to fix it, but need opinions about what is the correct behaviour.

The bug can be easily duplicated when formatting interval in to_char()
using 'Mon' or 'Month' in the format string.

select to_char(now() - '20011001'::date, 'YYYYMonDD');
(server process crash follows)

What happens:
1. The formatting function used is dch_date()
(src/backend/utils/adt/formatting.c) and it works on struct pg_tm.
2. The interval2tm() (src/backend/utils/adt/timestamp.c) is used to
convert the interval into pg_tm struct.
2a. If the Interval parameter has month != 0, then month and year are
filled in pg_tm
2b. If not -> they are set to 0 and only days, hours, minutes, seconds
are filled (this is the case when the bug appears).
3. dch_date() expects the struct pg_tm to have valid 1-based month index
and directly references the months/months_full arrays using (tm->month -
1) as index to get the short/full name of the month.
4. SIGSEGV in the server process

This could be easily by not allowing the bad array indexing, but it
raises a bigger problem: How is supposed the to_char() function to
format interval datatype? What is the correct output?

Should we:
1) Try to fill the missing data (years, months) using the days (but how
many days are in one month? hardcode 30/31? how many days in 1 year
then...) and fix the formatting function to ignore string based
formatting for intervals
2) Fail the entire statement (do not support interval formatting with
to_char())

Also the general to_char() Interval formatting seems broken anyway.
Note that the following (and similar) works now, but the result doesn't
seem to be correct:

test=> select to_char(now() - '20011001'::date, 'YYYYDD');
to_char
---------
000112
(1 row)

test=> select now() - '20011001'::date;
?column?
-------------------------------
1271 days 12:48:18.1216260046
(1 row)

So this bug actually brings the issue of interval to_char() formatting.
Opinions?

Regards,
Lyubomir Petrov