Re: Is this a Postgres Bug?

On Sun, Aug 5, 2012 at 1:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Mike Christensen <mike(at)kitchenpc(dot)com> writes:
>> 1) Is it possible to make int2 + int2 = int4?
>
> We could do that, but why stop there? int4 + int4 can overflow, maybe
> its result should be int8? int8 + int8 can overflow, maybe its result
> should be numeric? numeric + numeric can overflow, now what? And what
> about subtraction, multiplication, and various other operators?
>
> The long and the short of it is that you've made an unwise choice of
> datatype, if you have values that are close enough to the overflow
> threshold for this to be an issue.
>
>> 2) I've noticed if I have an index on (CookTime::Int4 +
>> PrepTime::Int4), then the query WHERE (CookTime + PrepTime > 100)
>> won't use the index. However, WHERE (CookTime::Int4 + PrepTime::Int4
>> 100) *will* use the index. Is this by design, and can the query
>> planner by smarter about this scenario?
>
> Yes, and no. The planner does not know enough about the semantics of
> cross-type coercions to infer that these expressions are equivalent.
> In fact, they *aren't* equivalent, precisely because of the differing
> prospects for overflow, and so the planner would be quite exceeding its
> authority to replace one with the other.

Thanks Tom! That definitely makes sense..