[rt-users] Re: query problem after 3.4.5 -> 3.6.4 upgrade

[Ruslan Zakirov]

  Hello, Brian.
Ok, I've forgotten about Status field and we're talking only about
"Requestor.EmailAddress = tom OR Owner = tom". I think that I've found
problem. Here are three queries I want you to compare. Other user can
do that too, but please replace "main.Owner = ####" with some valid
user ID as it's very important for mysql optimizer.

The first one is the query we build in 3.6.4:
SELECT SQL_NO_CACHE COUNT(DISTINCT main.id)
FROM Tickets main
    CROSS JOIN Users Users_3
    JOIN Groups Groups_1
        ON Groups_1.Domain = 'RT::Ticket-Role'
        AND Groups_1.Type = 'Requestor'
        AND Groups_1.Instance = main.id
    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
        ON CachedGroupMembers_2.MemberId = Users_3.id
        AND CachedGroupMembers_2.GroupId = Groups_1.id
WHERE
    main.Status != 'deleted'
    AND ( main.Owner = '58936'
        OR ( Users_3.EmailAddress LIKE '%tom%'
             AND CachedGroupMembers_2.id IS NOT NULL )
    )
    AND main.Type = 'ticket'
    AND main.EffectiveId = main.id;

This is one way to fix it:
SELECT SQL_NO_CACHE COUNT(DISTINCT main.id)
FROM Tickets main
    CROSS JOIN Users Users_3
    JOIN Groups Groups_1
        ON Groups_1.Domain = 'RT::Ticket-Role'
        AND Groups_1.Type = 'Requestor'
        AND Groups_1.Instance = main.id
    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
        ON CachedGroupMembers_2.MemberId = Users_3.id
        AND CachedGroupMembers_2.GroupId = Groups_1.id
WHERE
    main.Status != 'deleted'
    AND ( main.Owner = '58936' OR CachedGroupMembers_2.id IS NOT NULL )
    AND Users_3.EmailAddress LIKE '%tom%'
    AND main.Type = 'ticket'
    AND main.EffectiveId = main.id;

Another way to fix the problem:
SELECT SQL_NO_CACHE COUNT(DISTINCT main.id)
FROM Tickets main
    JOIN Groups Groups_1
        ON  Groups_1.Domain = 'RT::Ticket-Role'
        AND Groups_1.Type = 'Requestor'
        AND Groups_1.Instance = main.id
    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
        ON CachedGroupMembers_2.GroupId = Groups_1.id
    LEFT JOIN Users Users_3
        ON CachedGroupMembers_2.MemberId = Users_3.id
WHERE
    main.Status != 'deleted'
    AND main.Type = 'ticket'
    AND main.EffectiveId = main.id
    AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' );