Re: Insert SHRT_MIN fails for int2 column

When inserting SHRT_MIN into an int2 column, the odbc driver comes up
with the following SQL:

insert into dbc_test ( i16 ) values ( -32768::int2 );

This fails with an out of range error, and should be either:

insert into dbc_test ( i16 ) values ( (-32768)::int2 );

Or have the ::int2 cast removed altogether.

The ::int2 cast is added in convert.c on line 3024 (for 08.00.0005)
preceded by a comment stating it is necessary:

/* needs cast because there is no automatic downcast from
int4 constants */
CVT_APPEND_STR(qb, "::int2");

Is this really necessary? On my 7.4.2 database, the following works
just fine:

create table tst ( i16 int2 );
insert into tst ( i16 ) values ( -32768 );
insert into tst ( i16 ) values ( 32767 );

-Kelly

Kelly Burkhart <kelly(at)tradebotsystems(dot)com> writes:
> The ::int2 cast is added in convert.c on line 3024 (for 08.00.0005)
> preceded by a comment stating it is necessary:

> /* needs cast because there is no automatic downcast from
> int4 constants */
> CVT_APPEND_STR(qb, "::int2");

> Is this really necessary? On my 7.4.2 database, the following works
> just fine:

> create table tst ( i16 int2 );
> insert into tst ( i16 ) values ( -32768 );

In that context the down-conversion will work because it's considered an
assignment coercion. However there are other cases where an explicit
cast is indeed needed, for instance

select foo(42::int2)

where foo is declared as taking an int2 parameter.

The best thing would be to leave the cast in place but add parentheses
around the value being casted.

regards, tom lane

On Mon, 2005-03-07 at 11:44, Tom Lane wrote:
> Kelly Burkhart <kelly(at)tradebotsystems(dot)com> writes:
> > The ::int2 cast is added in convert.c on line 3024 (for 08.00.0005)
> > preceded by a comment stating it is necessary:
>
> > /* needs cast because there is no automatic downcast from
> > int4 constants */
> > CVT_APPEND_STR(qb, "::int2");
>
> > Is this really necessary? On my 7.4.2 database, the following works
> > just fine:
>
> > create table tst ( i16 int2 );
> > insert into tst ( i16 ) values ( -32768 );
>
> In that context the down-conversion will work because it's considered an
> assignment coercion. However there are other cases where an explicit
> cast is indeed needed, for instance
>
> select foo(42::int2)
>
> where foo is declared as taking an int2 parameter.
>
> The best thing would be to leave the cast in place but add parentheses
> around the value being casted.

Attached is a patch to resolve this. BTW, is it appropriate for me to
send pgsqlodbc patches to this list? Or should I send all patches to
psql-patches?

-K

Am Montag, 7. März 2005 19:42 schrieb Kelly Burkhart:
> > The best thing would be to leave the cast in place but add parentheses
> > around the value being casted.
>
> Attached is a patch to resolve this.

Patch installed.

> BTW, is it appropriate for me to send pgsqlodbc patches to this list?

Yes.

> Or should I send all patches to psql-patches?

No.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/