Bugs item #2645133, was opened at 2009-02-27 13:10 Message generated for change (Comment added) made by stmane You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2645133&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: SQL "stable" Status: Open Resolution: None Priority: 5 Private: Yes Submitted By: Jens Oehlschlägel (jens_oehl) Assigned to: Niels Nes (nielsnes) Summary: Scary: CASE SUM counts wrong Initial Comment: We found a difference between MonetDB and Access that boils down to MonetDB doing wrong calculation if counts are mimicked via SUM(CASE WHEN <cond> 1 ELSE 0 END): sql>SELECT more> SUM( CASE WHEN postingYear=2005 THEN 1 ELSE 0 END ) as CNT_2005 more>, SUM( CASE WHEN postingYear=2006 THEN 1 ELSE 0 END ) as CNT_2006 more>FROM voc.t_base_sales more>; +----------+----------+ | cnt_2005 | cnt_2006 | +==========+==========+ | 11535 | 7393 | +----------+----------+ 1 tuple sql> 0 tuples sql>-- correct would be 0 tuples sql>SELECT postingYear, count(*) as cc more>FROM voc.t_base_sales more>GROUP BY postingYear more>; +-------------+-------+ | postingyear | cc | +=============+=======+ | 2006 | 72929 | | 2005 | 77071 | +-------------+-------+ Stefan: please put the long attached SQL into autotests as #92. MonetDB server v5.10.0 (32-bit), based on kernel v1.28.0 (32-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Configured for prefix: C:\Documents and Settings\sjoerd\My Documents\src\stable\vs32\NT32 Libraries: libpcre: 7.7 2008-05-07 libxml2: 2.6.32 Compiled by: sjo...@gans Compilation: cl -GF -W3 -WX -wd4273 -wd4102 -MD -nologo -Ox Linking : cl -GF -W3 -WX -wd4273 -wd4102 -MD -nologo -Ox ---------------------------------------------------------------------- >Comment By: Stefan Manegold (stmane) Date: 2009-02-28 08:01 Message: added test autotest_92 ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-02-27 18:05 Message: Niels, as an initial attempt to fix(?) this problem, I came up with the attached patch that basically makes sql_bind_aggr() find the last match instead of the first one (i.e., largest super-type instead of smallest super-type) --- while working fine with the Periscope tests, it makes many other SQL tests fail, maily due to type mismatches in calc.ifthenelse() calls during key- & uniques- constraint checking with bulk loading and updates --- hence, my attempt was apparently a bit short-sighted ... Stefan File Added: sql_bind_aggr.patch ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-02-27 14:20 Message: added test as CASE_SUM_WRONG_COUNT.SF-2645133.sql ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-02-27 13:52 Message: for Niels: maybe, aggregations should always go for the largest result/target type (dbl/lng), and "downcase" only one we later see everything does fit in a smaller type ... ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-02-27 13:48 Message: for Niels: sum of >=32767 bte does not (necessarily) fit in a sht for all: we need to check for and consider adding proper overflow checks where ever they might occur ... ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2645133&group_id=56967 ------------------------------------------------------------------------------ Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
