Re: change the order of FROM selection to make query work

I think you're doing different join types. SQLite is probably doing
CROSS JOINs. PostgreSQL is probably doing at least one INNER JOIN.

>From http://www.sqlite.org/lang_select.html:
"If multiple tables names are separated by commas, then the query is
against the cross join of the various tables."

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Thomas Peter
Sent: Tuesday, September 26, 2006 9:15 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] change the order of FROM selection to make query work

hi,
posted this twice allready, but didn't seem to make it to the list.
so one more try:

i support a trac [1] installation and migrated the backend from sqlite
to
postgres 8.1.4, which worked fine, but:
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 put the 'ticket as t' at the end 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster