On Fri, Apr 8, 2011 at 11:50 PM, Daniel Boesswetter <daniel.boesswetter@fu-berlin.de> wrote:

Hi again,

now I managed to isolate the problem even more. Attached you will find a SQL script which creates 4 tables, inserts a few tuples and then queries them twice: the first query produces too few rows, but after dropping a primary key on one of the tables (it matters which one), the second query returns the correct results.

Looks like the optimizer optimizes a little too eagerly in the presence of primary keys?

Best regards,
Daniel

Daniel Boesswetter wrote:

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


------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
 

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users