[rt-users] Cannot query with more than one watcher using AND

[Schultz, Eric]

Please see this ticket I filed for RT:
http://rt3.fsck.com/Ticket/Display.html?id=7197

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 = 'jesse at bestpractical.com' AND Cc.EmailAddress = 'arthur at 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.

Thanks,
Eric Schultz