Обсуждение: Re: strange behaviour in 9.0.2 / ERROR: 22003: value out of range: overflow

On 20 Mai, 22:55, rudi <rudi.stras...@gmail.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?
>
> 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 hope anyone can give me a hint how to proceed....
>
> Best regards,
> Rudi

I found out that the issues is caused by overflows in floating point
(REAL) operation. After some additional debug info using "VERBOSITY"
that became more or less evident. 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. In a productive
environment this would scare the hell out of me.

In case someone has ideas how such conditions can be avoided, please
forward me some hints. I tend to believe that there must be a better
soluation than the one which is currently in place.

Best regards,
Rudi

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

> On 20 Mai, 22:55, rudi <rudi.stras...@gmail.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
andthe 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
largeis 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
columndata-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
itneeds fixing. 

Alban Hertroys

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


!DSPAM:737,4ddb717d11921570220170!