[Spacewalk-list] Error 500 on snapshots pages

[Pierre Casenove]

Hello,
My queries were wrong, in BZ 724963, Mickael has corrected my queries :

The issue has been fixed by
commit 95b35c52805616f5710dcf9dbc53d5d511edf02c
    724963 - use ANSI joins
commit 2bed66e27af71e46beebe994fce79920166f1607
    724963 - use ANSI joins
commit c0c583f116e22417bdf4ab0e64463ac4203064b3
    724963 - use ANSI joins
commit cdc961ae76a68266fdc7b197a099e63a0f736e41
    724963 - use LEFT JOIN instead of MINUS


There is still a problem in one reformatted queries though:

In file SystemGroup_queries.xml, request "groups_a_system_is_in", a CAST as
numeric has to be done:

SELECT VSGM.group_id AS ID,
                        SG.name AS GROUP_NAME,
                        SGT.label AS GROUP_TYPE_LABEL,
                        (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id =
:user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS
                FROM
                        rhnServerGroup SG,
                        rhnVisServerGroupMembership VSGM
                LEFT JOIN rhnServerGroupType SGT
                        ON CAST(VSGM.group_type as numeric) = SGT.id,
                        rhnUserServerGroupPerms USGP
                WHERE VSGM.org_id = :org_id
                        AND VSGM.server_id = :sid
                        AND VSGM.group_id = SG.id
                        AND USGP.user_id = :user_id
                        AND USGP.server_group_id = SG.id
                ORDER BY UPPER(SG.name)

I think we're getting close!


I've also proposed modifications yesterday evening on the list for the
few last queries that fail under pgsql. I've modified 5 queries, and 2
others are still failing.


Thanks,


2011/8/4 Jan Pazdziora <jpazdziora at redhat.com>

> On Sat, Jul 30, 2011 at 09:11:32AM +0000, Pierre Casenove wrote:
> > Crapy hotmail...Please find attached the diff.I've created BZ 724963
> also.
>
> > diff config_queries.xml config_queries.xml.orig
> > 85,90c85,90
> > <   FROM rhnConfigFile CF
> > <   LEFT OUTER JOIN rhnConfigRevision CR ON (CR.config_file_id = CF.id)
> > <   LEFT OUTER JOIN rhnSnapshotConfigRevision SCR ON
> (SCR.config_revision_id = CR.id)
> > <   LEFT OUTER JOIN rhnConfigFileName CFN ON (CFN.id =
> CF.config_file_name_id)
> > <   LEFT OUTER JOIN rhnConfigContent CCon ON (CCon.id =
> CR.config_content_id)
> > <   LEFT OUTER JOIN rhnChecksumView Csum ON (Csum.id = CCon.checksum_id)
> > ---
> > >   FROM rhnConfigContent CCon,
> > >        rhnConfigFileName CFN,
> > >        rhnConfigFile CF,
> > >        rhnConfigRevision CR,
> > >        rhnSnapshotConfigRevision SCR,
> > >        rhnChecksumView Csum
> > 91a92,96
> > >    AND SCR.config_revision_id = CR.id
> > >    AND CR.config_content_id = CCon.id (+)
> > >    AND CR.config_file_id = CF.id
> > >    AND CF.config_file_name_id = CFN.id
> > >    AND  CCon.checksum_id = Csum.id (+)
>
> This is incorrect. There are two outer joins in the original, and five
> in your version. You mustn't just replace every join with outer join
> -- the semantics is quite different.
>
> --
> Jan Pazdziora
> Principal Software Engineer, Satellite Engineering, Red Hat
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20110804/5ff78dbc/attachment.htm>