Bugs item #2757642, was opened at 2009-04-12 23:12 Message generated for change (Comment added) made by johnrylander 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: John Rylander (johnrylander) Date: 2009-04-13 15:29 Message: Agreed with both comments below. At first I suspected a compound rounding error--and maybe the problem will still prove to be some variation on that theme--so I increased the precision from 7,2 to 14,2 with no change in results. (More precisely, I remember that the results were still way off; I do not recall if they were identical.) And this is undoubtedly related to table size: small tables do not produce this error; to my mind, this doesn't seriously mitigate the concern. I am not sure where the line is drawn, and I'm not yet good enough at automating MonetDB to easily make up an iterative test case to find out. (I was hoping this would be easy for someone else out there. ;) ) BTW, I also implemented this BigList table and query in PostgreSQL 8.3.7, and it gave precisely correct answers to the queries. (Surprisingly precise, actually--I wonder if it uses quadruple-precision sometimes, but I don't know.) I'll try to upload the BigList table in BZ2 form shortly here. It's certainly my personal record for compression efficacy: a 296 MB table compressed to 17KB. :) OF course, it's nothing but the same value about 51M times, created via: copy select * from biglist INTO '....\\BigList.cpy' USING DELIMITERS ',', '\n' NULL AS '' I hope I'm right in presuming it'll be easy for others to COPY INTO a table given the above information? ---------------------------------------------------------------------- Comment By: Martin Kersten (mlkersten) Date: 2009-04-13 15: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 14: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 08: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
