Hi,
we are using MonetDB (Oct-2010) for analyzing a 250G data warehouse and have found at least one "thing" which might be a serious bug. The simplest scenario that I have found to describe it, is as follows:
SELECT comp.id as compid, comp.name, affnets.name as affid FROM toc INNER JOIN app ON toc.application_id=app.id INNER JOIN comp ON app.company_id = comp.id INNER JOIN affnets ON toc.affiliate_network_id=affnets.id GROUP BY compid, comp.name, affid
This query is supposed to return all unique company/affnet pairs which occur in the fact table "toc". However, it returns only the unique company-names, each with a random (the first?) affnet. (Of course, this could be accomplished with DISTINCT, but our aim to to aggregate other fields).
If I change the query to
select app.company_id as compid, comp.name, affnets.name as affid ...
or
select comp.id as compid, comp.name, affnets.name as affid ...
i.e. semantically the same but only with fields from 2 directly joined tables in the GROUP BY, then the result is correct.
Question: Is this a bug or are we missing something?
Thank you in advance!
Daniel