Thread: Is this a Postgres Bug?

First off, I've posted this question on StackOverflow in case anyone
wants to answer it:


http://stackoverflow.com/questions/11814132/postgresql-smallint-overflowing-when-creating-index-on-multiple-columns-is-th

The repro can be found here: http://sqlfiddle.com/#!1/734d7/1

I'm happy to log this as a bug, unless someone can explain to me why
this behavior is by design.  Thanks!

Mike

On 5.8.2012 07:14, Mike Christensen wrote:
> First off, I've posted this question on StackOverflow in case anyone
> wants to answer it:
>
> http://stackoverflow.com/questions/11814132/postgresql-smallint-
> overflowing-when-creating-index-on-multiple-columns-is-th
>
> The repro can be found here: http://sqlfiddle.com/#!1/734d7/1
>
> I'm happy to log this as a bug, unless someone can explain to me why
> this behavior is by design.  Thanks!

Definitely not a bug. The problem is not the index, it's the expression.
The data type of the result of deduced from the parts, and as it's an
addition of two int2 columns, the result is expected to be int2 too. Try
this:

  test=# select 32767::int2 + 10::int2;
  ERROR:  smallint out of range

No index, same result.

This is intentional, or rather expected, because the reasoning behind
this was 'we're not aware of a better solution' than 'we do want it to
behave like this.' But it clearly is not a bug.

I see two possible solutions:

1) change the column data types, e.g. to int4 - The limits will be much
   higher so you won't hit them. And the additional storage cost is
   minimal, especially considering the tuple header and UUID space
   requirements.

2) Retype the expession so that it uses different datatype. Just retype
   one of the columns like this:

     select (32767::int2 + 10::int2::int4);

   and everything should work fine. But you'll have to do the same in
   the queries, probably.

Tomas

>> First off, I've posted this question on StackOverflow in case anyone
>> wants to answer it:
>>
>> http://stackoverflow.com/questions/11814132/postgresql-smallint-
>> overflowing-when-creating-index-on-multiple-columns-is-th
>>
>> The repro can be found here: http://sqlfiddle.com/#!1/734d7/1
>>
>> I'm happy to log this as a bug, unless someone can explain to me why
>> this behavior is by design.  Thanks!
>
> Definitely not a bug. The problem is not the index, it's the expression.
> The data type of the result of deduced from the parts, and as it's an
> addition of two int2 columns, the result is expected to be int2 too. Try
> this:
>
>   test=# select 32767::int2 + 10::int2;
>   ERROR:  smallint out of range
>
> No index, same result.
>
> This is intentional, or rather expected, because the reasoning behind
> this was 'we're not aware of a better solution' than 'we do want it to
> behave like this.' But it clearly is not a bug.
>
> I see two possible solutions:
>
> 1) change the column data types, e.g. to int4 - The limits will be much
>    higher so you won't hit them. And the additional storage cost is
>    minimal, especially considering the tuple header and UUID space
>    requirements.
>
> 2) Retype the expession so that it uses different datatype. Just retype
>    one of the columns like this:
>
>      select (32767::int2 + 10::int2::int4);
>
>    and everything should work fine. But you'll have to do the same in
>    the queries, probably.

Thanks for the explanation!  Everything makes sense now.  I have three comments:

1) Is it possible to make int2 + int2 = int4?  I guess it would change
the implementation of the addition operator, which definitely sounds
like a dangerous change, plus I'm unaware if this is SQL compliant so
I'm guessing the answer is "No"

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?

3) If 1 and 2 are not possible, would it be possible to provide a
smarter, more useful error message here?  "smallint out of range" is
really hard to track down, and made me think it had to do something
with the table schema.  If the error message referred to the name of
the index, I would have noticed the problem immediately.

Thanks!

Mike

Mike Christensen <mike@kitchenpc.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.

            regards, tom lane

On Sun, Aug 5, 2012 at 1:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mike Christensen <mike@kitchenpc.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..