Please see this ticket I filed for RT:
I’m not sure why there’s a lack of understanding, I thought it was pretty straight-forward. To re-iterate my original point… Consider the following query run at rt3.fsck.com:
Requestor.EmailAddress = ‘email@example.com’ AND Cc.EmailAddress = ‘arthur@West.NL’
This should return 8 tickets, but returns 0. There is something wrong with how the tables are joined when you do a query with more than one watcher. From looking at the raw query that gets to MySQL, it appears to be a problem with how the schema is laid out, with Groups.Type being tied to Users through GroupMembers (or CachedGroupMembers). The clobbering is especially apparent when you try to do something like:
( Owner = ‘34’ OR Watcher.Name = ‘eschultz’ ) AND Requestor.EmailAddress NOT LIKE ‘eschultz’
That is, I want all tickets that I own or am a watcher on, but not ones I have requested (just me as owner, CC, or AdminCC). But that produces SQL like this:
AND ( ( (main.Owner = ‘34’)OR ( (Users_3.Name = ‘eschultz’) ) ) AND ( (Users_3.EmailAddress NOT LIKE ‘%eschultz%’)AND(Groups_1.Type = ‘Requestor’) ) )
In other words, select from Users where it is eschultz and also where it is not eschultz. Oops.
Let me know if I should instead be asking this question on rt-devel.