Re: [HACKERS] Bug in to_timestamp().

From: amul sul <sulamul(at)gmail(dot)com>
To: Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
Cc: prabhat(dot)sahu(at)enterprisedb(dot)com, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Artur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>, Bruce Momjian <bruce(at)momjian(dot)us>, amul sul <sul_amul(at)yahoo(dot)co(dot)in>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Bug in to_timestamp().
Date: 2018-09-19 10:21:37
Message-ID: CAAJ_b95nAuZ9tdJd4ruuZd5D2qW4-mHut=w0Wv-VXJqZG0Dx8g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Wed, Sep 19, 2018 at 2:57 PM Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
>
> On Tue, Sep 18, 2018 at 4:42 PM Alexander Korotkov
> <a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> > But, I found related issue in cf9846724. Before it was:
> >
> > # select to_timestamp('2018 01 01', 'YYYY9MM9DD');
> > to_timestamp
> > ------------------------
> > 2018-01-01 00:00:00+03
> > (1 row)
> >
> > But after it becomes so.
> >
> > # select to_timestamp('2018 01 01', 'YYYY9MM9DD');
> > ERROR: invalid value "1 " for "MM"
> > DETAIL: Field requires 2 characters, but only 1 could be parsed.
> > HINT: If your source string is not fixed-width, try using the "FM" modifier.
> >
> > That happens because we've already skipped space "for free", and then
> > NODE_TYPE_CHAR eats digit. I've checked that Oracle doesn't allow
> > random charaters/digits to appear in format string.
> >
> > select to_timestamp('2018 01 01', 'YYYY9MM9DD') from dual
> > ORA-01821: date format not recognized
> >
> > So, Oracle compatibility isn't argument here. Therefore I'm going to
> > propose following fix for that: let NODE_TYPE_CHAR eat characters only
> > if we didn't skip input string characters more than it was in format
> > string. I'm sorry for vague explanation. I'll come up with patch
> > later, and it should be clear then.
>
> Please find attached patch for fixing this issue. It makes handling
> of format string text characters be similar to pre cf984672 behavior.
> See the examples in regression tests and explanation in the commit
> message. I'm going to commit this if no objections.
>

With this patch, to_date and to_timestamp behaving differently, see this:

edb=# SELECT to_date('18 12 2011', 'xDDxMMxYYYY');
to_date
--------------------
18-DEC-11 00:00:00
(1 row)

edb=# SELECT to_timestamp('18 12 2011', 'xDDxMMxYYYY');
to_timestamp
---------------------------
08-DEC-11 00:00:00 -05:00 <=========== Incorrect output.
(1 row)

Regards,
Amul

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message amul sul 2018-09-19 10:38:00 Re: [HACKERS] Bug in to_timestamp().
Previous Message Alexander Korotkov 2018-09-19 09:27:35 Re: [HACKERS] Bug in to_timestamp().