Bugs item #2645133, was opened at 2009-02-27 13:10 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=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 CVS Head 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 ---------------------------------------------------------------------- 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
