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

5 May 2011


      Stefan: I'll file the bug.

Franck: thanks for bringing up the topic again ;-)

Best,
Daniel


Stefan.Manegold@cwi.nl wrote:
...
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