[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/24/07, Ruslan Zakirov <ruz at bestpractical.com> wrote:
> > > 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.
>
> Makes sense.  You are right that query isn't right in 3.4.5, I didn't see that.
>
> Changing the 3.4.5 mysql query to use OR instead of AND works fine.  I
> think that cross join is killing us in 3.6.4.
As far as I know CROSS JOIN must work in the same way as comma ','
according to SQL standard and docs of all DBs we're using as
back-ends. Try to change all CROSS JOINs to ',' .

>
> >
> > >
> > > 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%') ) )
>


-- 
Best regards, Ruslan.