Обсуждение: 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