Re: Bug in to_timestamp().

On Thu, Jun 23, 2016 at 12:16 PM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>
wrote:

>
> On 23.06.2016 16:30, Bruce Momjian wrote:
>
>> On Thu, Jun 23, 2016 at 07:41:26AM +0000, amul sul wrote:
>>
>>> On Monday, 20 June 2016 8:53 PM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>
>>> wrote:
>>>
>>>
>>> On 13.06.2016 18:52, amul sul wrote:
>>>>>
>>>> And it wont stop on some simple whitespace. By using to_timestamp you
>>>> can get any output results by providing illegal input parameters values:
>>>> postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD
>>>> HH24:MI:SS');
>>>> to_timestamp
>>>> ------------------------
>>>> 2016-01-06 14:40:39+03
>>>>
>>>> (1 row)
>>>>
>>> We do consume extra space from input string, but not if it is in format
>>> string, see below:
>>>
>>> postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD
>>> HH24:MI:SS');
>>> to_timestamp
>>> ------------------------
>>> 2016-06-13 15:43:36-07
>>> (1 row)
>>>
>>> We should have same treatment for format string too.
>>>
>>> Thoughts? Comments?
>>>
>> Well, the user specifies the format string, while the input string comes
>> from the data, so I don't see having them behave the same as necessary.
>>
>>
> To be honest they not just behave differently. to_timestamp is just
> incorrectly handles input data and nothing else.There is no excuse for
> such behavior:
>
> postgres=# SELECT TO_TIMESTAMP('20:-16-06:13: 15_43:!36', 'YYYY/MM/DD
> HH24:MI:SS');
> to_timestamp
> ------------------------------
> 0018-08-05 13:15:43+02:30:17
> (1 row)
>

T
​o be honest I don't see how this is relevant to quoted content. And
you've already made this point quite clearly - repeating it isn't
constructive. This behavior has existed for a long time and I don't see
that changing it is a worthwhile endeavor. I believe a new function is
required that has saner behavior. Otherwise given good input and a
well-formed parse string the function does exactly what it is designed to
do. Avoid giving it garbage and you will be fine. Maybe wrap the call to
the in a function that also checks for the expected layout and RAISE
EXCEPTION if it doesn't match.

​David J.
​
​