Bugs item #2757642, was opened at 2009-04-13 06:12 Message generated for change (Comment added) made by mlkersten You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2757642&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: Open Resolution: None Priority: 5 Private: No Submitted By: John Rylander (johnrylander) Assigned to: Niels Nes (nielsnes) Summary: Inaccurate AVG of SQRT with 51M records Initial Comment: I'm using the latest stable Windows Vista x64-compatible release 5.10.2 based on the 1.28.2 kernel. On an table "biglist" with 51751608 records and just one field, a numeric 14,2 field called "rt", I executed the following: UPDATE biglist SET rt=1.21; (Rows affected 51751608) Then I tried: SELECT AVG(rt) FROM biglist; And of course it reported 1.21--fine. :) But then I tried: SELECT AVG(SQRT(rt)) from biglist; and instead of the expected 1.1, I got 0.64837467465745224. A wide variety of other SQRT aggregations also were wildly off the mark. (And I tried this earlier with a 7,2 numeric field--no joy.) Any suggestions? My apologies if this has already been fixed, or if I'm doing something wrong--I'm entirely new to MonetDB, but think it's an amazing project--amazing! I hope this can be resolved soon, so I can rely on it. :) Thanks to all the developers and testers! I hope to do some tiny part here with this report. ---------------------------------------------------------------------- >Comment By: Martin Kersten (mlkersten) Date: 2009-04-13 22:01 Message: Don't forget that SQRT works on doubles and produces doubles. This leads to rounding errors, but does not explain yours. >create table tmp(rt decimal(10,2)); insert into tmp values(1.2); insert into tmp values(1.2); insert into tmp values(1.2); insert into tmp values(1.2); insert into tmp values(1.2); select avg(rt), avg(sqrt(rt)) from tmp; &3 >&2 1 -1 >&2 1 -1 >&2 1 -1 >&2 1 -1 >&2 1 -1 > &1 2 1 2 1 % sys., sys. # table_name % scale_down_L11, L12 # name % double, double # type % 22, 22 # length [ 1.2, 1.0954451560974121 ] >select sqrt(1.2); &1 0 1 1 1 % . # table_name % sqrt_single_value # name % real # type % 13 # length [ 1.09544516 ] ---------------------------------------------------------------------- Comment By: Niels Nes (nielsnes) Date: 2009-04-13 21:46 Message: It seems the bug is already fixed or related to your db size. I tested with a small table and this works fine. Could you try your query on a small table? ---------------------------------------------------------------------- Comment By: John Rylander (johnrylander) Date: 2009-04-13 15:39 Message: If others can confirm this bug, i.e., if I'm not just doing some wrong and this hasn't already been fixed in a developer build, then I think the priority of this bug should be raised significantly. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2757642&group_id=56967 ------------------------------------------------------------------------------ This SF.net email is sponsored by: High Quality Requirements in a Collaborative Environment. Download a free trial of Rational Requirements Composer Now! http://p.sf.net/sfu/www-ibm-com _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
