Обсуждение: Re: [BUGS] bug in timestamp and out of range values

On Thursday 02 November 2006 17:48, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > pagila=# select to_date('3232098', 'MM/DD/YYYY');
> >     to_date
> > ---------------
> >  4568-06-26 BC
> > (1 row)
>
> to_date's absymal lack of error checking is well known.  It should
> surely refuse that input altogether, given that format string.
> Feel free to send a patch ...
>
> As for the range issue, date_in does refuse negative Julian dates:
>
> regression=# select '4714-01-27 BC'::date;
> ERROR:  date out of range: "4714-01-27 BC"
>
> but again to_date doesn't:
>
> regression=# select to_date('4714-01-27 BC', 'YYYY-MM-DD BC');
>     to_date
> ---------------
>  4714-01-27 BC
> (1 row)
>

I'm not concerned about to_date so much as I am that timestamp_in lets you 
store values you can't read with timestamp_out.  Once the value is in there 
you can happily move it around with create table as and such... 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

>> but again to_date doesn't:
>>
>> regression=# select to_date('4714-01-27 BC', 'YYYY-MM-DD BC');
>>     to_date
>> ---------------
>>  4714-01-27 BC
>> (1 row)
>>
> 
> I'm not concerned about to_date so much as I am that timestamp_in lets you 
> store values you can't read with timestamp_out.  Once the value is in there 
> you can happily move it around with create table as and such... 

Hmmm... if that is the case, I would also have a pretty significant
concern. We have basically created an environment that is unreliable
during a restore. Not to mention violating data type constraints.

postgres=# create table timetest (test date);
CREATE TABLE
postgres=# insert into timetest          values (to_date('4714-01-27 BC', 'YYYY-MM-DD BC'));
INSERT 159911984 1

postgres=# select '4714-01-27 BC'::date;
ERROR:  date out of range: "4714-01-27 BC"
postgres=# select cast(test as date) from timetest;    test
---------------4714-01-27 BC
(1 row)

postgres=#
postgres=# select cast('4714-01-27 BC' as date);
ERROR:  date out of range: "4714-01-27 BC"
postgres=#

This seems pretty broken.

Joshua D. Drake






-- 
     === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997            http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

> 
> postgres=# select '4714-01-27 BC'::date;
> ERROR:  date out of range: "4714-01-27 BC"
> postgres=# select cast(test as date) from timetest;
>      test
> ---------------
>  4714-01-27 BC
> (1 row)
> 
> postgres=#
> postgres=# select cast('4714-01-27 BC' as date);
> ERROR:  date out of range: "4714-01-27 BC"
> postgres=#
> 
> This seems pretty broken.
> 
> Joshua D. Drake

And further this with timestamp instead of date:

postgres=# create table timestamptest(test timestamp);
CREATE TABLE
postgres=#  insert into timestamptest           values (to_date('4714-01-27 BC', 'YYYY-MM-DD BC'));
INSERT 159911988 1
postgres=# select * from timestamptest;
ERROR:  timestamp out of range

Sincerely,

Joshua D. Drake


-- 
     === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997            http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Robert Treat <xzilla@users.sourceforge.net> writes:
> I'm not concerned about to_date so much as I am that timestamp_in lets you 
> store values you can't read with timestamp_out.

Your example does not demonstrate any such thing.  What it demonstrates
is that to_date will let an out-of-range date into the system, not that
timestamp_in will.  Counterexample:

regression=# select '4714-01-27 BC'::timestamp;
ERROR:  timestamp out of range: "4714-01-27 BC"
        regards, tom lane