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

On Thursday 30 Oct 2003 4:53 am, you wrote:
> <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

Yes that was an optimisation on haste the simplification was not
accurate. I will work on it again. But incidently both the SQLs
produced the same results which *may* mean that the query could
have been done in a simpler manner.

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

Yes the SQL generator is not doing the best things at the moment
and the author(Jesse) is aware of it and looking forward to our
help in optimising it.

>
> (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.)

I do not think the database schema is a mess.
The ACL system in RT and RT itself is quite comprehensive.
The problem is with the Query Generator.

Apologies for delayed response to your email.

Regards
Mallah.

>
> regards, tom lane