TO_DATE Function unintended behavior when month value is greater than 12

2018-03-01 19:34 GMT+01:00 naidu rongali <rongalinaidu@gmail.com>:

Hi,
I noticed this behavior on recent release of PG database. It will be
helpful to users, if this behavior is looked into.

When executed below SQL, got unintended output when i expected it to throw
error " not a valid month "

SQL executed:
select to_date('20171231','YYYYDDMM')

result:
2019-07-17

What is your version?

It should be fixed on PostgreSQL 10 and higher (there was not backport
because some applications used this feature)

Regards

Pavel

From: naidu rongali [mailto:rongalinaidu@gmail.com]
Sent: Thursday, March 01, 2018 1:34 PM
To: pgsql-bugs@postgresql.org
Subject: TO_DATE Function unintended behavior when month value is greater than 12

Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails.

Hi,
I noticed this behavior on recent release of PG database. It will be helpful to users, if this behavior is looked into.

When executed below SQL, got unintended output when i expected it to throw error " not a valid month "

SQL executed:
select to_date('20171231','YYYYDDMM')

result:
2019-07-17

Thank you, Naidu

I get this:
ERROR: date/time field value out of range: "20171231" SQL state: 22008

Regards,
Igor Neyman

On Thu, Mar 1, 2018 at 7:28 PM, Igor Neyman <ineyman@perceptron.com> wrote:

*From:* naidu rongali [mailto:rongalinaidu@gmail.com]
*Sent:* Thursday, March 01, 2018 1:34 PM
*To:* pgsql-bugs@postgresql.org
*Subject:* TO_DATE Function unintended behavior when month value is
greater than 12

*Attention: This email was sent from someone outside of Perceptron. Always
exercise caution when opening attachments or clicking links from unknown
senders or when receiving unexpected emails.*

Hi,

I noticed this behavior on recent release of PG database. It will be
helpful to users, if this behavior is looked into.

When executed below SQL, got unintended output when i expected it to throw
error " not a valid month "

SQL executed:
select to_date('20171231','YYYYDDMM')

result:
2019-07-17

Thank you, Naidu

I get this:

ERROR: date/time field value out of range: "20171231" SQL state: 22008

Regards,

Igor Neyman

The error is thrown in version 10 but I get the wrong results in versions
9.3 to 9.6, both in the almost latest (9.3.21, 9.4.16, 9.5.11 and 9.6.7)
and today's releases (9.3.22, ..., 9.6.8)

Pantelis Theodosiou

The error is thrown in version 10 but I get the wrong results in versions
9.3 to 9.6, both in the almost latest (9.3.21, 9.4.16, 9.5.11 and 9.6.7)
and today's releases (9.3.22, ..., 9.6.8)

​As Pavel said we fixed this in 10 but did not back-patch the behavior
change.

David J.
​

Thank you all for the update. I checked the version details. it is
"PostgreSQL 8.0.2".

On Fri, Mar 2, 2018 at 1:13 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thursday, March 1, 2018, naidu rongali <rongalinaidu@gmail.com> wrote:

Thank you all for the update. I checked the version details. it is
"PostgreSQL 8.0.2".

If that database is at all important to anyone it needs serious
professional attention. It doesn't qualify as "recent" no matter how
liberal you wish to make the definition.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thursday, March 1, 2018, naidu rongali <rongalinaidu@gmail.com> wrote:

Thank you all for the update. I checked the version details. it is
"PostgreSQL 8.0.2".

If that database is at all important to anyone it needs serious
professional attention. It doesn't qualify as "recent" no matter how
liberal you wish to make the definition.

To quantify that: 8.0.2 was released on 2005-04-07, and was obsoleted
by 8.0.3 on 2005-05-09. See release notes at
https://www.postgresql.org/docs/8.0/static/release.html

regards, tom lane

On Fri, Mar 2, 2018 at 6:52 AM, naidu rongali <rongalinaidu@gmail.com>
wrote:

though 8.2 is in deprecated versions, looks like 8.0.2 is released on
2009-02-02 as per https://www.postgresql.org/docs/8.0/static/release-8-0-
20.html

​That link points to 8.0.20 not 8.0.2 ... it would less egregious if
running 8.0.26 since at least you would have been running the most
up-to-date version of the 8.0 major release series but even that is over 7
years old at this point.

8.2[.x] is a completely different major version.

Upgrading 8.0 to a late 9 series or current 10 release will be non-trivial
but, IMO, the decision not to should be a done with the consent and
understanding of whomever ultimately owns the database and not through
inattention.

David J.

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

Thank you all for the update. I checked the version details. it is
"PostgreSQL 8.0.2".

If that database is at all important to anyone it needs serious
professional attention. It doesn't qualify as "recent" no matter how
liberal you wish to make the definition.

Tom> To quantify that: 8.0.2 was released on 2005-04-07, and was obsoleted
Tom> by 8.0.3 on 2005-05-09. See release notes at
Tom> https://www.postgresql.org/docs/8.0/static/release.html

It's probably not postgresql at all; Amazon Redshift identifies itself
as pg 8.0.2, even though it supports a lot of more recent stuff as well.

--
Andrew (irc:RhodiumToad)