IDS 7.3 makes COUNT(foo)-1 floating point

Running IDS 7.3UC7 under Red Hat Linux 5.2 (2.0 kernel), an expression COUNT(foo)-1 used to define a view column is interpreted as floating point (DECIMAL(18)). With the SQL create table foo (a int, b int); create view v (a, c) as select a, count(b) - 1 from foo group by a; insert into foo values (1, 1); insert into foo values (1, 2); insert into foo values (2, 1); insert into foo values (2, 2); select * from v; I get 2 1.00000000000000 1 1.00000000000000 I would guess the problem isn't in dbaccess (which is what I used to execute the above) since getting the table/view information from dbaccess for v shows: Column name Type Nulls a integer yes c decimal(18) yes so the column is indeed floating point. Without the "- 1" after the count, everything's fine and the column in the created view is integral. Am I missing something? --Malcolm -- Malcolm Beattie <mbeattie@sable.ox.ac.uk> Oxford University Computing Services "I permitted that as a demonstration of futility" --Grey Roger

Malcolm Beattie wrote: > > Running IDS 7.3UC7 under Red Hat Linux 5.2 (2.0 kernel), an > expression COUNT(foo)-1 used to define a view column is > interpreted as floating point (DECIMAL(18)). With the SQL > > create table foo (a int, b int);> > create view v (a, c) as > select a, count(b) - 1 > from foo > group by a;> > insert into foo values (1, 1); > insert into foo values (1, 2); > insert into foo values (2, 1); > insert into foo values (2, 2);> > select * from v;> > I get > > 2 1.00000000000000 > 1 1.00000000000000 > > I would guess the problem isn't in dbaccess (which is what I used to > execute the above) since getting the table/view information from > dbaccess for v shows: > > Column name Type Nulls > > a integer yes > c decimal(18) yes > > so the column is indeed floating point. Without the "- 1" after the > count, everything's fine and the column in the created view is > integral. Am I missing something? Hmm; I'm surprised that the -1 makes any odds. A number of the aggregates changed types recently, simply because the value can exceed the largest representable integer. That includes COUNT() in general, if you have fragmented tables. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>