Bugs item #2132666, was opened at 2008-09-27 21:17
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2132666&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: MonetDB5 5.6
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Pierre BOIZOT (pirboazo)
Assigned to: Niels Nes (nielsnes)
Summary: Avg on integer and smallint : false
Initial Comment:
When the column is integer or smallint, than the sum of a group is greater
than the max value of the type the avg is false .
Bug view on ubuntu 8.04 and windows XP.
Pierre
Example:
sql>alter table car add column iprice integer ;
Timer 8906.000 msec 0 rowssql>alter table car add column iprice integer ;
Timer 8906.000 msec 0 rows
sql>alter table car add column sage smallint ;
Timer 5218.000 msec 0 rows
sql>\d car
CREATE TABLE "pib"."car" (
"age" int,
"gender" char(1),
"price" bigint,
"category" varchar(20),
"class" varchar(20),
"codville" char(3),
"sage" smallint,
"iprice" int
);
sql>update car set iprice=price;
Rows affected 24000000
Timer 18640.000 msec 0 rows
sql>update car set sage = age ;
Rows affected 24000000
Timer 15094.000 msec 0 rows
With smallint value NULL.
sql>select count(*), gender , sum (sage), avg(sage) from car group by gender;
+--------------+-------+----------+-----------------------+
| count_gender |gender |sum_sage |avg_sage |
+==============+=======+==========+=======================+
| 12000000 |f |494872440 | null |
| 12000000 |m |494872440 | null |
+--------------+-------+----------+-----------------------+
Timer 1656.000 msec 2 rows
sql>select count(*), gender , sum (age), avg(age) from car group by gender;
+--------------+-------+----------+-----------------------+
| count_gender |gender |sum_age |avg_age |
+==============+=======+==========+=======================+
| 12000000 |f |494872440 | 41.239370000000001 |
| 12000000 |m |494872440 | 41.239370000000001 |
+--------------+-------+----------+-----------------------+
Timer 1797.000 msec 2 rows
With integer value negative :
sql>select gender, count(*),sum(iprice), avg(price), avg(iprice) from car group
by gender;
+-------+--------+-------------+----------------------+-----------------------+
| gende |count_g |sum_iprice |avg_price |avg_iprice |
: r |ender | | | |
+=======+========+=============+======================+=======================+
| f |1200000 |455067160680 | 37922.26339 | -16.614391333333334 |
: |0 | | | |
| m |1200000 |455067160680 | 37922.26339 | -16.614391333333334 |
: |0 | | | |
+-------+--------+-------------+----------------------+-----------------------+
Timer 2516.000 msec 2 rows
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2132666&group_id=56967
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
