Re: strange behaviour in 9.0.2 / ERROR: 22003: value out of range: overflow

On 23 May 2011, at 22:53, rudi wrote:

> On 20 Mai, 22:55, rudi <rudi(dot)stras(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>> Hi all,
>>
>> I feel like I hit a bug in postgres 9.0.2 with a query like this
>> (there's actually a quite complicated view hidden behind), however
>> note
>> the 'IN' selection contains two identical keys. When I execute a
>> similar query without the duplicate, the query returns, so I would
>> conclude it shoud be a bug.
>>
>> This query fails as you can tell from the output:
>>
>> mydb=# select * from cpcpk_by_lot where foundry='x' and lot='valerie'
>> and epclass='wac' and area='device' and parameter in
>> ('RVT_2P_NOM_1UX5_N_VTSAT','RVT_2P_NOM_1UX5_N_VTSAT');
>> ERROR: 22003: value out of range: overflow
>> LOCATION: float4mul, float.c:750
>>
>> while the a practically identical query returns with the expected
>> result?

What's in that view? Aggregations and something like a UNION ALL maybe? I'm guessing the difference between this query and the below one is in the result of some aggregation that happens twice if the value occurs in the IN-list twice.

Apparently you're using a numeric field somewhere for which 22003 is too large a value. Finding out why it becomes too large is what you need to do.

If my guess is correct, you probably have other (smaller) values that got the same issue, but that didn't overflow the column data-type. It's only a select statement, but you'd still be returning wrong information.

>> mydb=# select * from cpcpk_by_lot where foundry='x' and lot='valerie'
>> and epclass='wac' and area='device' and parameter in
>> ('RVT_2P_NOM_1UX5_N_VTSAT');

> I wonder whether the behaviour can
> be optimized, such that the individual value can be set to 'nan'
> instead of causing a fail for the entire query.

That would be a really bad idea. It is a number, so saying it's not is just wrong. An overflowing number still is a number, and throwing an error when it overflows is the correct thing to do. There's something wrong in your queries and it needs fixing.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4ddb717d11921570220170!