It's now imposible search by two requestors

First attachment is current SELECT(3.0.4 but also checked with delta
#211 with same result)
Second is right select that must be generated in case.

I know that we could say that mySQL can’t optimize this simple query,
but it’s obviouse that this is very-very bad query.

bad_search.sql.orig (1.25 KB)

good_search1.sql (638 Bytes)

First attachment is current SELECT(3.0.4 but also checked with delta
#211 with same result)
Second is right select that must be generated in case.

Can you please verify that this is also the case with stock RT 3.0.5?

Also, in the future, please do NOT send messages To: both rt-devel and
rt-bugs. If you’re going to do that, please either send to rt-bugs
first and get a ticket number to include in the rt-devel posting OR bcc
rt-bugs. When you send mail to both, it ends up with every single
reply creating a new ticket.

Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.

Jesse Vincent wrote:

First attachment is current SELECT(3.0.4 but also checked with delta
#211 with same result)
Second is right select that must be generated in case.

Can you please verify that this is also the case with stock RT 3.0.5?

Done. Same result. mySQL(4.0.last) can’t finish this query in small time.
____ QUERY____
SELECT count(DISTINCT main.id) FROM Tickets main, Groups Groups_1,
Principals Principals_2, CachedGroupMembers CachedGroupMembers_3, Users
Users_4, Groups Groups_5, Principals Principals_6, CachedGroupMembers
CachedGroupMembers_7, Users Users_8 WHERE ((main.EffectiveId =
main.id)) AND ((main.Type = ‘ticket’)) AND ( ( ( (Users_4.EmailAddress
LIKE ‘%test%’)AND(Groups_1.Domain = ‘RT::Ticket-Role’)AND(Groups_1.Type
= ‘Requestor’)AND(Principals_2.PrincipalType = ‘Group’) ) OR (
(Users_8.EmailAddress LIKE ‘%cubic%’)AND(Groups_5.Domain =
‘RT::Ticket-Role’)AND(Groups_5.Type =
‘Requestor’)AND(Principals_6.PrincipalType = ‘Group’) ) ) AND (
(main.Status = ‘resolved’) ) ) AND Groups_1.Instance = main.id AND
Groups_1.id = Principals_2.ObjectId AND Principals_2.id =
CachedGroupMembers_3.GroupId
AND CachedGroupMembers_3.MemberId = Users_4.id AND Groups_5.Instance =
main.id AND Groups_5.id = Principals_6.ObjectId AND Principals_6.id =
CachedGroupMembers_7.GroupId AND CachedGroupMembers_7.MemberId = Users_8.id
_/QUERY
Sorry, but I track all your changes and there was only one delta
according to issue and no more. I just don’t have time to finish with
repositories, when I do this I’ll be send reports for current unstable
versions.

Also, in the future, please do NOT send messages To: both rt-devel and
rt-bugs. If you’re going to do that, please either send to rt-bugs
first and get a ticket number to include in the rt-devel posting OR bcc
rt-bugs. When you send mail to both, it ends up with every single
reply creating a new ticket.

Understood. Sorry for that. I’ve got into passion. Last time I’ve got
many complaints from our users about RT and in some cases I know that
they are right, but can’t do anything with it. E.g. they don’t want that
someone could create ticket through WebUI without all custom fields
filled in… and other… and other… they always complaint… I get
tired… :slight_smile:

I hate users they are capricious :slight_smile: Like me for you… :slight_smile:

Can you please verify that this is also the case with stock RT 3.0.5?

Done. Same result. mySQL(4.0.last) can’t finish this query in small time.

Thanks Grr. That was supposed to have bee nfixed.

Sorry, but I track all your changes and there was only one delta
according to issue and no more. I just don’t have time to finish with
repositories, when I do this I’ll be send reports for current unstable
versions.

nod Over the years, I’ve had an awful lot of “bugs” reported to me
that turned out to be due to a site’s particular customizations or the
“really, it’s just like the released version” that they happen to be
using. For paying customers, I’m happy to go the extra mile and do all
the debugging on my end, but there are just too many users for me to be
able to do it for everybody and still have time to sleep and work on
new development :wink:

I hate users they are capricious :slight_smile: Like me for you… :slight_smile:

Heh.

Best,
Jesse

Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.

Can you please verify that this is also the case with stock RT 3.0.5?

Done. Same result. mySQL(4.0.last) can’t finish this query in small time.

Thanks Grr. That was supposed to have been fixed.

So. I just fired up a vanilla RT 3.0.5 instance. I clicked on the
“General” quicksearch. Then I typed “jesse@fsck.com” into the
“Requestor” email address box. Then I typed “jonas@example.com” into the
email address box. It works fine for me.

The search it generated was:

SELECT DISTINCT main.* FROM Tickets main, Groups Groups_1, Principals
Principals_2, CachedGroupMembers CachedGroupMembers
_3, Users Users_4 WHERE ((main.EffectiveId = main.id)) AND ((main.Type
= ‘ticket’)) AND ( ( ( ( (Users_4.EmailAddress LIKE
‘%jonas@example.com%’)OR(Users_4.EmailAddress LIKE ‘%jesse@fsck.com%’) ) AND(Groups_1.Domain =
‘RT::Ticket-Role’)AND(Groups_1.Type =
‘Requestor’)AND(Principals_2.PrincipalType = ‘Group’) )
) AND ( (main.Status = ‘new’)OR(main.Status = ‘open’) ) AND (
(main.Queue = ‘1’) ) ) AND Groups_1.Instance = main.id AND
Groups_1.id = Principals_2.ObjectId AND Principals_2.id = CachedGroupMembers_3.GroupId AND
CachedGroupMembers_3.MemberId = Users_4.id ORDER BY main.id ASC LIMIT
50

Which returns in milliseconds.

Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.

Jesse Vincent wrote:

Can you please verify that this is also the case with stock RT 3.0.5?

Done. Same result. mySQL(4.0.last) can’t finish this query in small time.

Thanks Grr. That was supposed to have been fixed.

So. I just fired up a vanilla RT 3.0.5 instance. I clicked on the
"General" quicksearch. Then I typed "jesse@fsck.com" into the
"Requestor" email address box. Then I typed "jonas@example.com" into the
email address box. It works fine for me.

I see it too… don’t do anything after last email, but now everything
ok with it. :frowning:
Now I hate myself. :frowning: As I think I’ve do misstake when was chosing
browser window where check results(production server or my test) or may
be Apache restart.

Bug is closed. :slight_smile:
Sorry for this noise…
Best regards. Ruslan.