Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)

<mallah(at)trade-india(dot)com> writes:
> Actually PostgreSQL is at par with MySQL when the query is being
> Properly Written(simplified)

These are not the same query, though. Your original looks like

SELECT DISTINCT main.*
FROM Groups main , Principals Principals_1, ACL ACL_2
WHERE
((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser'))
AND ((ACL_2.PrincipalId = Principals_1.id AND
ACL_2.PrincipalType = 'Group' AND
(main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND
main.id = Principals_1.id)
OR
(((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR
(main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND
main.Type = ACL_2.PrincipalType AND
main.id = Principals_1.id))
AND (ACL_2.ObjectType = 'RT::System' OR
(ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25))
ORDER BY main.Name ASC

where the replacement is

SELECT DISTINCT main.*
FROM Groups main join Principals Principals_1 using(id)
join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id)
WHERE
((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser'))
AND ((ACL_2.PrincipalType = 'Group' AND
(main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence'))
OR
(((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR
(main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND
main.Type = ACL_2.PrincipalType))
AND (ACL_2.ObjectType = 'RT::System' OR
(ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25))
ORDER BY main.Name ASC ;

You have made the condition "ACL_2.PrincipalId = Principals_1.id"
required for all cases, where before it appeared in only one arm of an
OR condition. If the second query is correct, then the first one is
wrong, and your real problem is that your SQL generator is broken.

(I'd argue that the SQL generator is broken anyway ;-) if it generates
such horrible conditions as that. Or maybe the real problem is that
the database schema is a mess and needs rethinking.)

regards, tom lane