Обсуждение: timestamp handling in postgres 7.1 behaves strange

Martin Würtele (martin.wuertele@factline.com) 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

> 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

pgsql-bugs@postgresql.org 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