Hi All,



Since we've upgraded to 9.1.1 we've noticed that the addition of
integer4 items now returns an integer8 field. This seems to be so in
sum() and when adding an integer literal to an integer4 field.



The following code example definitly produces int4 items on 9.0.4 and in
II2.6, but not so on 9.1.1 where int8's are produced for the summations.



What do people think about this?

Is it a bug? We have had to change some code to coerce fields into int4
which has been annoying.



Martin Bowes



sql iidbdb <<SQL_END

create table intlong_test(

id integer4 not null not default,

ival integer4 not null not default

);

\p\g

-- This example adds an integer literal to in integer4 item

create view intlong_test_view as

select id, ival+1 as should_be_int4, int4(ival+1) as will_be_int4

from intlong_test;

\p\g

-- This example uses sum() to add integer4 items.

create view intlong_test_2view as

select id, sum(ival) as should_be_int4, int4(sum(ival)) as
will_be_int4

from intlong_test

group by id;

\p\g

-- The columns should all be integer 4.

select table_name, column_name, 'int' + varchar(column_length) as
column_type

from iicolumns

where table_name in ('intlong_test_view', 'intlong_test_2view')

and table_owner='ingres'

order by table_name, column_name

\p\g

rollback

\p\g

\q

SQL_END

On Apr 9, 2009, at 9:19 AM, Martin Bowes wrote:

It's a feature, more or less. There was a lot of i8-creep going on
during the
r3 / 9.x development and I was never entirely sure why. A little bit
of it
was caused by tids, the rest seemed to "just happen", maybe because i8
was available.

Karl

It was a deliberate change in behaviour as part of a fix to a bug where
integer overflows went undetected.

We've had a few issues (and eyebrows) raised since implementing this but I
can tell you there are no plans to reverse it. The message is if your code
requires a specific datatype e.g. int4, you should be casting it to that
datatype.

--
Paul Mason

I've just scanned the 'create table as select' part of the manual and it
has nothing to say about the column widths. Nor did the section on
'Integer Literals' spell out what would happen when you add things.

Perhaps this is a documentation bug?

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Karl
& Betty Schendel
Sent: 09 April 2009 14:30
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] default size of result in integer addition.


On Apr 9, 2009, at 9:19 AM, Martin Bowes wrote:

It's a feature, more or less. There was a lot of i8-creep going on
during the
r3 / 9.x development and I was never entirely sure why. A little bit
of it
was caused by tids, the rest seemed to "just happen", maybe because i8
was available.

Karl

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

Hi Paul,






I had that suspicion.





That's Cool.






That's OK. Once we got over the shock the code fix was easy and will
stay in place forever and ever.



But there probably should be a section in the manual covering the data
typeand size of columns when creating tables as select and when
creating views. If there is such a section, I can't find it at the
moment.



Marty

Hi Marty,



I have had a look in the SQL Doc and there is a matrix of "Default Type
Conversions" (page 103 in the release of the doc I have), but it is not
up-to-date as per this discussion.



So I have been through all 64 combinations and checked them and an issue
(135751) is now open for this and I have also asked that a comment is
added to the Create Table ... As Select section of the SQL guide
referring back to the Matrix.



Cheers



John



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 09 April 2009 14:58
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] default size of result in integer addition.



Hi Paul,




I had that suspicion.





That's Cool.




That's OK. Once we got over the shock the code fix was easy and will
stay in place forever and ever.



But there probably should be a section in the manual covering the data
typeand size of columns when creating tables as select and when
creating views. If there is such a section, I can't find it at the
moment.



Marty

Hi John,

Thanks heaps for the reference and for the attention to the manual. Thats a great result from my perspective.

Marty

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com on behalf of John Smedley
Sent: Thu 09/04/2009 18:55
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] default size of result in integer addition.



Hi Marty,



I have had a look in the SQL Doc and there is a matrix of "Default Type Conversions" (page 103 in the release of the doc I have), but it is not up-to-date as per this discussion.



So I have been through all 64 combinations and checked them and an issue (135751) is now open for this and I have also asked that a comment is added to the Create Table ... As Select section of the SQL guide referring back to the Matrix.



Cheers



John



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 09 April 2009 14:58
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] default size of result in integer addition.



Hi Paul,



I had that suspicion.




That's Cool.



That's OK. Once we got over the shock the code fix was easy and will stay in place forever and ever.



But there probably should be a section in the manual covering the data typeand size of columns when creating tables as select and when creating views. If there is such a section, I can't find it at the moment.



Marty