Bugs item #2132666, was opened at 2008-09-27 19:17
Message generated for change (Comment added) made by nielsnes
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 "stable"
>Status: Closed
>Resolution: Fixed
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
----------------------------------------------------------------------
>Comment By: Niels Nes (nielsnes)
Date: 2009-02-11 20:49
Message:
added test to BugTracker-2009. Seems fixed. Not 100% sure as it maybe data
related which isn't included in the example code.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2132666&group_id=56967
------------------------------------------------------------------------------
Create and Deploy Rich Internet Apps outside the browser with Adobe(R)AIR(TM)
software. With Adobe AIR, Ajax developers can use existing skills and code to
build responsive, highly engaging applications that combine the power of local
resources and data with the reach of the web. Download the Adobe AIR SDK and
Ajax docs to start building applications today-http://p.sf.net/sfu/adobe-com
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
