On Sun, Jan 20, 2008 at 05:14:42PM -0800, Bob Ippolito wrote:

MonetDB SR5 behaves unexpectedly when we've tried to use COALESCE with aggregates, here's an example:

Thank you for useing MonetDB. I tested both your problems and they are still present on the current (CVS Head) version. Could you therfor create bug reports for them. Niels

sql>CREATE TABLE tmp(a int); sql>SELECT SUM(a) FROM tmp; +-------+ | sum_a | +=======+ | 0 | +-------+ sql>SELECT MIN(a) FROM tmp; +-------------+ | min_a | +=============+ | null | +-------------+ sql>SELECT COALESCE(MIN(a), 0) FROM tmp; sql>

For the first SELECT I would expect NULL, the second SELECT is okay, but I expect a result for the third!

Here's what PosgreSQL does:

bob=# CREATE TABLE tmp(a int); CREATE TABLE bob=# SELECT SUM(A) FROM tmp; sum -----

(1 row)

bob=# SELECT MIN(a) FROM tmp; min -----

(1 row)

bob=# SELECT COALESCE(MIN(a), 0) FROM tmp; coalesce ---------- 0

-bob

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl