[rt-users] Re: query problem after 3.4.5 -> 3.6.4 upgrade

[Ruslan Zakirov]

On 7/25/07, Brian Kerr <kerrboy at gmail.com> wrote:
> On 7/23/07, Brian Kerr <kerrboy at gmail.com> wrote:
>
> > > When doing a search by requestor/owner email address now, we are
> > > getting a nasty query produced.  Here are the details of the query.
> > > Let me know if you need any more information.
> >
> > Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom'
>
> Here is what the query looks like when formed in the 3.4.5 query
> builder.  It returns results in less than a second.
But as far as I can see the query below returns wrong results and
that's exactly what we've fixed.
The following query finds tickets where requestor's address has 'tom'
substring *and* owner is tom. So it doesn't match the condition you
build.

>
> SELECT COUNT(DISTINCT main.id) FROM (((Tickets main  JOIN Groups
> Groups_1  ON ( Groups_1.Instance = main.id))  LEFT JOIN
> CachedGroupMembers CachedGroupMembers_2  ON (
> CachedGroupMembers_2.GroupId = Groups_1.id) AND (
> (CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId)))
> LEFT JOIN Users Users_3  ON ( Users_3.id =
> CachedGroupMembers_2.MemberId))   WHERE ((Groups_1.Domain =
> 'RT::Ticket-Role')) AND ((Groups_1.Type = 'Requestor')) AND
> ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
> ((main.Type = 'ticket')) AND ( ( (main.Owner = '58936') ) AND (
> (Users_3.EmailAddress LIKE '%tom%') ) )
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>


-- 
Best regards, Ruslan.