[rt-devel] Generated SQL hanging

[Rich Lafferty]

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 -----------+-----------------------------------------------