public inbox for [email protected]
help / color / mirror / Atom feedto_date()/to_timestamp() silently accept month=0 and day=0
2+ messages / 2 participants
[nested] [flat]
* to_date()/to_timestamp() silently accept month=0 and day=0
@ 2026-04-22 14:18 Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Ayush Tiwari @ 2026-04-22 14:18 UTC (permalink / raw)
To: [email protected]
Hi,
I found what looks like a bug in to_date() / to_timestamp(). (Saw it in
master branch)
Inputs with month = 00 or day = 00 are accepted silently and normalized to
January / day 1, instead of being rejected as out of range.
Simple repro steps:
SELECT to_date('2024-00-15', 'YYYY-MM-DD');
SELECT to_date('2024-01-00', 'YYYY-MM-DD');
SELECT to_timestamp('2024-00-15', 'YYYY-MM-DD');
SELECT to_timestamp('2024-01-00', 'YYYY-MM-DD');
Observed results here:
to_date('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15
to_date('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01
to_timestamp('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15 00:00:00+05:30
to_timestamp('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01 00:00:00+05:30
I would expect all four calls to error, similar to how an invalid date
literal
is rejected.
Looking at the root cause in src/backend/utils/adt/formatting.c, the issue
seems
rooted in the interaction between ZERO_tm() and do_to_timestamp().
When to_date()/to_timestamp() initializes pg_tm, it uses ZERO_tm() which
defaults tm->tm_mday = 1 and tm->tm_mon = 1. The TmFromChar struct (tmfc),
which is used to collect the parsed inputs, stores these fields as plain
integers without independent presence flags (has_mm or has_dd have
booleans).
In do_to_timestamp(), the parsed values are applied back into the pg_tm
struct
using simple truthiness checks:
if (tmfc.mm) tm->tm_mon = tmfc.mm;
if (tmfc.dd) tm->tm_mday = tmfc.dd;
Because an explicitly parsed 00 evaluates to false, the zeros are never
copied
into pg_tm. Thus, the 1 defaults from ZERO_tm() remain untouched, and the
subsequent validation function ValidateDate() never actually sees the 0 to
throw
an out-of-bounds error.
Fixing this likely requires adding boolean flags to TmFromChar to
distinguish
between an omitted field and an explicitly parsed 0?
Regards,
Ayush
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
@ 2026-04-23 07:57 Michael Paquier <[email protected]>
parent: Ayush Tiwari <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Paquier @ 2026-04-23 07:57 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: [email protected]
On Wed, Apr 22, 2026 at 07:48:00PM +0530, Ayush Tiwari wrote:
> Inputs with month = 00 or day = 00 are accepted silently and normalized to
> January / day 1, instead of being rejected as out of range.
>
> Simple repro steps:
>
> SELECT to_date('2024-00-15', 'YYYY-MM-DD');
> SELECT to_date('2024-01-00', 'YYYY-MM-DD');
> SELECT to_timestamp('2024-00-15', 'YYYY-MM-DD');
> SELECT to_timestamp('2024-01-00', 'YYYY-MM-DD');
>
> Observed results here:
>
> to_date('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15
> to_date('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01
> to_timestamp('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15 00:00:00+05:30
> to_timestamp('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01 00:00:00+05:30
>
> I would expect all four calls to error, similar to how an invalid date
> literal is rejected.
While I agree with your feeling that it would be less confusing if
these patterns are rejected, throwing an error could also mean an
impact on existing applications that relied on the existing historical
behavior of replacing these zeroes defined in input, where they'd
expect a 01. So that would be a silent behavior change introduced in
a minor release.
Perhaps we could consider strengthening such inputs on HEAD once v20
opens for business? It would be really a scary thing to backpatch,
still a major release is a different thing.
Any thoughts or opinions from others?
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-23 07:57 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-22 14:18 to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-23 07:57 ` Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox