Re: [HACKERS] Bug in to_timestamp().

From: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
To: Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, amul sul <sulamul(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(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-02-09 15:31:14
Message-ID: CA+q6zcX6Svkn1Cf0GDvSqCEV-SFC6ET2bxjXQpWaoZYWAordUA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

> On 7 February 2018 at 22:51, Dmitry Dolgov <9erthalion6(at)gmail(dot)com> wrote:
>> On 6 February 2018 at 10:17, Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru> wrote:
>> It is strange. I still can apply both v9 [1] and v10 [2] via 'git
>> apply'. And Patch Tester [3] says that it is applied. But maybe
>> it is because of my git (git version 2.16.1).
>>
>> You can try also 'patch -p1':
>
> Yes, looks like the problem is on my side, sorry.

I went through this thread, and want to summarize a bit:

From what I see this patch addresses most important concerns that were
mentioned in the thread, i.e. to make `to_timestamp` less confusing and be
close to Oracles behavior. The code itself looks clear and sufficient, with the
required documentation and green tests.

Looks like there are just two questions left so far:

* I've noticed what I think a difference between current that was introduced in
this patch and Oracle. In the following case we can have any number of spaces
after a separator `+` in the input string

SELECT to_timestamp('2000+JUN', 'YYYY/MON');
to_timestamp
------------------------
2000-06-01 00:00:00+02
(1 row)

SELECT to_timestamp('2000+ JUN', 'YYYY/MON');
to_timestamp
------------------------
2000-06-01 00:00:00+02
(1 row)

But no spaces before it (it actually depends on how many separators do we have
in the format string)

SELECT to_timestamp('2000 +JUN', 'YYYY/MON');
ERROR: 22007: invalid value "+JU" for "MON"
DETAIL: The given value did not match any of the allowed values
for this field.
LOCATION: from_char_seq_search, formatting.c:2410

SELECT to_timestamp('2000 +JUN', 'YYYY//MON');
to_timestamp
------------------------
2000-06-01 00:00:00+02
(1 row)

SELECT to_timestamp('2000 +JUN', 'YYYY//MON');
ERROR: 22007: invalid value "+JU" for "MON"
DETAIL: The given value did not match any of the allowed values
for this field.
LOCATION: from_char_seq_search, formatting.c:2410

Judging from this http://rextester.com/l/oracle_online_compiler in Oracle it's
possible to have any number of spaces before or after `+` independently from
the number of separators in an input string. Is it intended?

SELECT to_timestamp('2000 + JUN', 'YYYY/MON') FROM dual
01.06.2000 00:00:00

* About usage of locale dependent functions e.g. `isalpha`. Yes, looks like
it's better to have locale-agnostic implementation, but then I'm confused - all
functions except `isdigit`/`isxdigit` are locale-dependent, including
`isspace`, which is also in use.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Claudio Freire 2018-02-09 16:05:43 Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem
Previous Message Konstantin Knizhnik 2018-02-09 15:28:18 Re: Built-in connection pooling