Re: [HACKERS] Bug in to_timestamp().

From: Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
To: prabhat(dot)sahu(at)enterprisedb(dot)com
Cc: amul sul <sulamul(at)gmail(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>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Bug in to_timestamp().
Date: 2018-09-18 13:42:21
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

On Tue, Sep 18, 2018 at 2:08 PM Prabhat Sahu
<prabhat(dot)sahu(at)enterprisedb(dot)com> wrote:
> Few more findings on to_timestamp() test with HEAD.
> postgres[3493]=# select to_timestamp('15-07-1984 23:30:32',' dd- mm- yyyy hh24: mi: ss');
> to_timestamp
> ---------------------------
> 1984-07-15 23:30:32+05:30
> (1 row)
> postgres[3493]=# select to_timestamp('15-07-1984 23:30:32','9dd-9mm-99yyyy 9hh24:9mi:9ss');
> to_timestamp
> ------------------------------
> 0084-07-05 03:00:02+05:53:28
> (1 row)
> If there are spaces before any formate then output is fine(1st output) but instead of spaces if we have digit then we are getting wrong output.

This behavior might look strange, but it wasn't introduced by
cf9846724. to_timestamp() behaves so, because it takes digit have
NODE_TYPE_CHAR type. And for NODE_TYPE_CHAR we just "eat" single
character of input string regardless what is it.

But, I found related issue in cf9846724. Before it was:

# select to_timestamp('2018 01 01', 'YYYY9MM9DD');
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.

Alexander Korotkov
Postgres Professional:
The Russian Postgres Company

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-09-18 13:43:28 Re: Is it possible for postgres_fdw to push down queries on co-located tables?
Previous Message Stephen Frost 2018-09-18 12:48:35 Re: Collation versioning