Thread: strange sql issue

hi,
i support a trac [1] installation and changed the backend from sqlite to
postgres 8.1.4
the following sql stopped working with postgres, and the fix of this
problem seems strange to me.
first the old sql, that worked with sqlite:

SELECT
p.value AS __color__,
id AS ticket, summary, status, priority ,component, t.type AS type,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter,
FROM ticket as t, permission as perm, enum as p
WHERE status IN ('new', 'assigned', 'reopened') AND perm.action =
'mf'
and p.name = t.priority AND p.type='priority'
ORDER BY priority, type, time

and the fix was, to twist the order in the FROM statement.
changing
FROM ticket as t, permission as perm, enum as p
to
FROM permission as perm, enum as p, ticket as t
works like expected!

so is this a bug, or do i get soemthing wrong (a hint where to rtfm would
be very welcome in this case)

thanx,
thomas

[1] trac.edgewall.org

Thomas Peter writes:

> the following sql stopped working with postgres, and the fix of this
> problem seems strange to me.
[...]
> and the fix was, to twist the order in the FROM statement.
> changing
> FROM ticket as t, permission as perm, enum as p
> to
> FROM permission as perm, enum as p, ticket as t
> works like expected!
>
> so is this a bug, or do i get soemthing wrong (a hint where to rtfm would
> be very welcome in this case)

If by "stopped working" you mean that the query returns an unexpected
result set, there should be a difference in the respective query plans
(explain analyze). Comparing them might give a clue what is going
wrong.

regards,
andreas