[rt-users] R: Setting owner with _Set breaks queries (may be a bug)

[Scotto Alberto]

Ok, thanks for the explanations.

I tried executing an UPDATE statement, but nothing has changed. The TicketSQL query "Owner.RealName = 'Nobody in particular' " still returns more tickets than it should.

Here is the UPDATE, which sets GroupMembers.MemberId with the value in Tickets.Owner:
UPDATE Tickets t join Groups g on t.id=g.Instance join GroupMembers m on m.GroupId=g.id
    SET m.MemberId=t.Owner
    WHERE
        g.Domain = 'RT::Ticket-Role' and
        g.Type = 'Owner' and
        t.Owner <> m.MemberId

Now the query that looks for tickets where GroupMembers.MemberId differs from Tickets.Owner
returns 0 rows:
select  *  from Tickets t join Groups g on t.id=g.Instance join GroupMembers m on m.GroupId=g.id
WHERE g.Domain = 'RT::Ticket-Role' and g.Type = 'Owner' and t.Owner <> m.MemberId;

What am I missing?
Thank you again

________________________________________
Da: rt-users-bounces at lists.bestpractical.com [rt-users-bounces at lists.bestpractical.com] per conto di Thomas Sibley [trs at bestpractical.com]
Inviato: martedì 9 ottobre 2012 19.14
A: rt-users at lists.bestpractical.com
Oggetto: Re: [rt-users] Setting owner with _Set breaks queries (may be a bug)



Alberto Scotto

Blue Reply
Via Cardinal Massaia, 83
10147 - Torino - ITALY
phone: +39 011 29100
al.scotto at reply.it
www.reply.it

On 10/05/2012 08:15 AM, Scotto Alberto wrote:
> Very interesting, thank you! Now I understand the relationships. So I
> think I should correct all the _Set I've used all around in my custom
> scrips :(

Yes, rarely is _Set the right answer.

> To verify if it finds the errors caused by _Set I searched in the
> ouput for "2985", which is the number of a ticket affected by the
> issue. I also searched for "12626", the ID of the owner group
> associated with that ticket. But none of them are there.

rt-validator does not validate or fix this problem.  There's a comment
in the code:

    # XXX: check that owner is only member of owner role group

> PS: (OT) if at every moment the Owner is one and only one, I wonder
> why you defined a group for role Owner. Maybe in order to have a
> history of past owners? Or for some code consistency?

All access control in RT is based on groups (even individual users have
equivalence groups containing only them).  The reason Owner is a column
on the Tickets table is for historical reasons, performance, and
convenience.

--------
Final RT training for 2012 in Atlanta, GA - October 23 & 24
  http://bestpractical.com/training

We're hiring! http://bestpractical.com/jobs



________________________________

--
The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.