Bug #761: Date format ambiguity while insertion!!!!

Shubhankar Dasgupta (dasgupta(at)intelesoftech(dot)com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Date format ambiguity while insertion!!!!

Long Description
I am using Postgres 7.2.2 on Redhat Linux 7.2

My datestyle settings have been set as "ISO US(non european)"
This would mean that my date values should get inserted in the mm-dd-yyyy format. "09-01-2002" is interpreted as Sepetember 1st, 2002 while "01-09-2002" is interpreted as January 9th, 2002.
This works perfectly most of the time except during certain cases where my month field exceeds 12.
As an example--consider "13-01-2002". As per my datesyle settings, postgres should have tried to interpret this as the 1st day of some "UNKNOWN" month of 2002 and should have thrown an error. However this date gets inserted and gets stored as 13th January, 2002 (interperted in the dd-mm-yyyy format).
Its only when both the first two fields exceed 12(something like 13-13-2002), that I am given an error "Bad date external representation '13-13-2002'. If either of the first two fields is less than or equal to 12, postgres accepts that as the month field irrespective of my datestyle settings.
This may be a flexibility feature but then this I believe is extremely dangerous as it would accept user-errors without the user knowing it.

Sample Code
ims=> insert into testdate values('1','09-01-2002');
INSERT 1590456 1
ims=> insert into testdate values('2','01-09-2002');
INSERT 1590457 1
ims=> insert into testdate values('3','13-09-2002');
INSERT 1590458 1
ims=> insert into testdate values('4','09-13-2002');
INSERT 1590459 1
ims=> insert into testdate values('5','13-13-2002');
ERROR: Bad date external representation '13-13-2002'
ims=> select * from testdate ;
dateid | mydate
--------+------------
1 | 2002-09-01
2 | 2002-01-09
3 | 2002-09-13
4 | 2002-09-13
(4 rows)

No file was uploaded with this report

> My datestyle settings have been set as "ISO US(non european)" This would
> mean that my date values should get inserted in the mm-dd-yyyy format.

No.

First, "ISO" is only the output format. Input is still accepted in most
"reasonable" forms. Second, the "US" setting only defines how to resolve
input if there is ambiguity. Third, the format you are using wouldn't
qualify as ISO either way you look at it.

> This may be a flexibility feature but then this I believe is extremely
> dangerous as it would accept user-errors without the user knowing it.

Yes, that's a good point. But narrowing down the accepted formats would
probably break a lot of code, since the acceptable date formats tend to
vary heavily in the pretends-to-be-SQL world.

--
Peter Eisentraut peter_e(at)gmx(dot)net