Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bG7rb-00082u-TI for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Jun 2016 16:52:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bG7rb-00061y-FI for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Jun 2016 16:52:23 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bG7qE-0004XG-9V for pgsql-hackers@postgresql.org; Thu, 23 Jun 2016 16:50:58 +0000 Received: from newmail.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bG7qB-00019W-4B for pgsql-hackers@postgresql.org; Thu, 23 Jun 2016 16:50:56 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id DAA3E21C8FE3; Thu, 23 Jun 2016 19:50:52 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru Received: from mail.postgrespro.ru ([127.0.0.1]) by localhost (mail.postgrespro.ru [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id culcP5bZAGCe; Thu, 23 Jun 2016 19:50:51 +0300 (MSK) Received: from [192.168.27.30] (unknown [192.168.27.1]) by mail.postgrespro.ru (Postfix) with ESMTPSA id 8C58221C8FB4; Thu, 23 Jun 2016 19:50:51 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1466700651; bh=SYVd8ANiwKQQvVm2rvnAzmwYnc3TWTtZyDP0siBANOk=; h=Subject:To:References:Cc:From:Date:In-Reply-To; b=nAFmoKnX4hA2KLaY3fm+jTx0iJsmLxNwR6i9sr0zkOTGH4JZQIigzjqbPbhVcIbqf +l/Jlhe/+IS91gXHVU7qUKy/lpgpdL/HrEwt9CGMfglfP4kb5jVFoZDZEcgY4XSPjx 5eq5Idx2+XU02OatpSW/BWJ7AJthLmR7SLNtGfGY= Subject: Re: Bug in to_timestamp(). To: "David G. Johnston" References: <1873520224.1784572.1465833145330.JavaMail.yahoo.ref@mail.yahoo.com> <1873520224.1784572.1465833145330.JavaMail.yahoo@mail.yahoo.com> <884791796.213185.1466667686999.JavaMail.yahoo@mail.yahoo.com> <20160623133001.GF21246@momjian.us> <1877e60a-a970-18b5-453c-e342f96150e6@postgrespro.ru> Cc: Bruce Momjian , amul sul , PostgreSQL-development From: Alex Ignatov Message-ID: Date: Thu, 23 Jun 2016 19:50:53 +0300 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101 Thunderbird/45.1.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------0FD8ABD8AAE80AC5596D00E8" X-Pg-Spam-Score: -3.4 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------0FD8ABD8AAE80AC5596D00E8 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 23.06.2016 19:37, David G. Johnston wrote: > On Thu, Jun 23, 2016 at 12:16 PM, Alex Ignatov > >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 > > 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. > ​ > ​ Arguing just like that one can say that we don't even need exception like "division by zero". Just use well-formed numbers in denominator... Input data sometimes can be generated automagically. Without exception throwing debugging stored function containing to_timestamp can be painful. --------------0FD8ABD8AAE80AC5596D00E8 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 

    
On 23.06.2016 19:37, David G. Johnston
      wrote:

    

    

      

        
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.

              

            

                
​

                ​

          

        

      

    

    Arguing just like that one can say that we don't even need exception
    like "division by zero". Just use well-formed numbers in
    denominator... 

    Input data  sometimes can be generated automagically. Without
    exception throwing debugging stored function containing to_timestamp
    can be painful. 

    

  


--------------0FD8ABD8AAE80AC5596D00E8--