[BUGS] timestamp handling in postgres 7.1 behaves strange

[pgsql-bugs]

Martin Würtele ([EMAIL PROTECTED]) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
timestamp handling in postgres 7.1 behaves strange

Long Description
version: 7.1release-4 of debian/woody

when comparing timestamps i always get
ERROR:  Bad timestamp external representation 'm'

Sample Code
this works:
select timestamp (content) from factversionelement where factid=100311 and 
elementname='newsdate';
   timestamp

 2010-01-02 16:00:00+01
(1 row)

this does not:
select factid from factversionelement where factid=100311 and elementname='newsdate' 
and timestamp (content) >= '2001-08-08';
ERROR:  Bad timestamp external representation 'm'

this also does not:
select factid from factversionelement where factid=100311 and elementname='newsdate' 
and timestamp (content) >= timestamp('2001-08-08');
ERROR:  Bad timestamp external representation 'm'


No file was uploaded with this report


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[BUGS] Re: timestamp handling in postgres 7.1 behaves strange

[Thomas Lockhart]

> when comparing timestamps i always get
> ERROR:  Bad timestamp external representation 'm'
> this works:
> select timestamp (content) from factversionelement where factid=100311 and 
>elementname='newsdate';
> 
>  2010-01-02 16:00:00+01
> this does not:
> select factid from factversionelement where factid=100311 and elementname='newsdate' 
>and timestamp (content) >= '2001-08-08';
> ERROR:  Bad timestamp external representation 'm'

In the first case, you are evaluating the timestamp only after the
"where qualifications" are met, so this is done on one row. In the
second case, the optimizer is probably having you evaluate
timestamp(content) ON ALL ROWS as the first pass. At least one of those
rows has a value which is incompatible with timestamp data.

Please state the schema of the factversionelement table. I will bet that
the "content" column is not in fact a timestamp type (it is some sort of
string type??), and does not contain strings which are entirely legal as
timestamps.

  - Thomas

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [BUGS] timestamp handling in postgres 7.1 behaves strange

[Tom Lane]

[EMAIL PROTECTED] writes:
> this works:
> select timestamp (content) from factversionelement where factid=100311 and 
>elementname='newsdate';
>timestamp
> 
>  2010-01-02 16:00:00+01
> (1 row)

> this does not:
> select factid from factversionelement where factid=100311 and elementname='newsdate' 
>and timestamp (content) >= '2001-08-08';
> ERROR:  Bad timestamp external representation 'm'

What this looks like to me is that you have some row in the table that
has content = 'm' rather than a valid timestamp representation, but
does not have (both) factid=100311 and elementname='newsdate'.  You
cannot assume that the WHERE clauses are evaluated in any particular
order.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly