Re: Bug in to_timestamp().

On Thu, Jun 23, 2016 at 12:16 PM, Alex Ignatov <a.ignatov@postgrespro.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.ignatov@postgrespro.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.

​

​