Cannot query with more than one watcher using AND

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@bestpractical.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.

Thanks,
Eric Schultz

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@bestpractical.com’ AND Cc.EmailAddress = ‘arthur@West.NL’

And what I thought I’d asked was how many tickets arthur@west.nl was the
CC on. But now I think I get it. Could you do me the favor of trying
this query with RT 3.5.6? We’ve done a fair bit of work on that section
of the Query Building engine.

Okay, finally back from vacation and installed 3.5.6. Still doesn’t work. Other similar queries that don’t work:

Ticket has requestors: jesse@bestpractical.com, jvincent@bestpractical.com
Query: Requestor.EmailAddress LIKE ‘jesse’ AND Requestor.EmailAddress LIKE ‘jvincent’
Result: returns 0 tickets, should return one

Ticket has requestor: jesse@bestpractical.com
Ticket has AdminCc: jvincent@bestpractical.com
Query: Requestor.EmailAddress LIKE ‘jesse’ AND AdminCc.EmailAddress LIKE ‘jvincent’
Result: returns 0 tickets, should return one

There are a lot of enumerations of this that fail. I’d be glad if other people could create a few test tickets and verify this problem.

I think this requires a rethought in how watchers/principals are set up in the database (i.e., it’s not a problem with the query building engine). If you look at the raw SQL produced that is sent to the DB, you’ll see where there are collisions in the select that negate any hope for rows to be returned. I can provide a sample if need be.From: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Friday, December 09, 2005 4:46 PM
To: Schultz, Eric
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Cannot query with more than one watcher using
AND