Re: [MonetDB-users] wrong results for group by queries

Hi, please bear with us. We're also only humans with 24 hours to spend per day. ;-) I suppose this is a bug; looks like it might be a too eager attempt to (in this case, i.e., after the join, incorrectly) exploit the uniqueness of the primary key of table c when determining the groups. Could you --- Franck or Daniel --- please file a bug report using Daniel's detailed example? I'll try to fix it as soon as our agendas allow so ... Thanks, Stefan On Thu, May 05, 2011 at 09:55:37AM +0200, Franck Routier wrote:

Hi list,

this seems particularly annoying, to say the least, and I don't see any follow up on the list. Is this confirmed and a known problem (maybe with a bug report), or is it solved, or what ?

Franck

Le samedi 09 avril 2011 à 05:57 +0300, Michael Sioutis a écrit :

...

I am getting same results with latest development release:

+------+------+------+ | name | id | name | +======+======+======+ | b1 | 1 | c1 | | b1 | 2 | c2 | | b1 | 3 | c3 | +------+------+------+ 3 tuples

+------+------+------+ | name | id | name | +======+======+======+ | b1 | 1 | c1 | | b1 | 2 | c2 | | b1 | 3 | c3 | | b2 | 1 | c1 | | b2 | 2 | c2 | | b2 | 3 | c3 | | b3 | 1 | c1 | | b3 | 2 | c2 | | b3 | 3 | c3 | +------+------+------+ 9 tuples

Mike

On Fri, Apr 8, 2011 at 11:50 PM, Daniel Boesswetter 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

------------------------------------------------------------------------------ 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

------------------------------------------------------------------------------ WhatsUp Gold - Download Free Network Management Software The most intuitive, comprehensive, and cost-effective network management toolset available today. Delivers lowest initial acquisition cost and overall TCO of any competing solution. http://p.sf.net/sfu/whatsupgold-sd _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |