[rt-devel] Generated SQL hanging

[Matthew Watson]

I have problems with this query too, Although it takes along time on my 
system for any limit-requestor query.. about 10 seconds when there 
are no other users connected (much longer when other users are connected
due to mysql locking).

Haven't got any solution... but would love to see what comes of this :)

Mat.


> -----Original Message-----
> From: rt-devel-admin at lists.fsck.com
> [mailto:rt-devel-admin at lists.fsck.com]On Behalf Of Rich Lafferty
> Sent: Wednesday, 17 July 2002 6:21 AM
> To: RT Development mailing list
> Subject: [rt-devel] Generated SQL hanging
> 
> 
> I'm having trouble figuring out why this:
> 
>   rt --limit-requestor=foo at bar.com \
>      --limit-requestor=xyzzy at quux.net  --summary
> 
> is hanging (or running longer than a couple of minutes, at least).
> 
> It generates this SQL:
> 
> SELECT DISTINCT main.* 
>     FROM Tickets main, Watchers Watchers_1, Watchers Watchers_3  
>     LEFT JOIN Users as Users_2 ON Watchers_1.Owner = Users_2.id 
>     LEFT JOIN Users as Users_4 ON Watchers_3.Owner = Users_4.id  
>     WHERE ((Watchers_3.Type = 'Requestor')) 
>       AND ((Watchers_3.Scope = 'Ticket')) 
>       AND ((main.EffectiveId = main.id))
>       AND ((Watchers_1.Scope = 'Ticket')) 
>       AND ((Watchers_1.Type = 'Requestor')) 
>       AND (  (Watchers_1.Email ='foo at bar.com') 
>           OR (Users_2.EmailAddress = 'foo at bar.com') 
>           OR (Watchers_3.Email = 'xyzzy at quux.net') 
>           OR (Users_4.EmailAddress = 'xyzzy at quux.net') )
>       AND main.id = Watchers_1.Value
>       AND main.id = Watchers_3.Value  ;
> 
> which strikes me as unnecessarily complex, but it's not immediately
> apparent *where*, because this
> 
>   rt --limit-requestor=foo at bar.com --summary
> 
> generates
> 
> SELECT DISTINCT main.* 
>     FROM Tickets main, Watchers Watchers_1 
>     LEFT JOIN Users as Users_2 ON Watchers_1.Owner = Users_2.id  
>     WHERE ((main.EffectiveId = main.id)) 
>       AND ((Watchers_1.Scope = 'Ticket'))
>       AND ((Watchers_1.Type = 'Requestor')) 
>       AND (  (Watchers_1.Email = 'foo at bar.com') 
>           OR (Users_2.EmailAddress = 'foo at bar.com') )
>       AND main.id = Watchers_1.Value
> 
> which runs instantly. 
> 
> Does anything in there jump out as obviously wrong? The two left joins
> strike me as odd, but I'm having trouble figuring out why.
> 
>   -Rich
> 
> -- 
> Rich Lafferty 
> --------------+-----------------------------------------------
>  Ottawa, Ontario, Canada    |  Save the Pacific Northwest Tree Octopus!
>  http://www.lafferty.ca/    |    http://zapatopi.net/treeoctopus.html
> rich at lafferty.ca 
> -----------+-----------------------------------------------
> 
> _______________________________________________
> rt-devel mailing list
> rt-devel at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-devel