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

"Thomas Peter" <usenet(at)braindumped(dot)com> writes:
> 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,

Define "stopped working" ... what was wrong exactly?

Changing the FROM order should certainly not have made any semantic
difference. It's possible that it would have changed the plan chosen,
but unlikely (unless maybe the planner had no statistics to go on?)

regards, tom lane

Am 26.9.2006 schrieb "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>Define "stopped working" ... what was wrong exactly?

oh, sorry. i forgot the error message:

Error: org.postgresql.util.PSQLException: ERROR: invalid reference to
FROM-clause entry for table "t", SQL State: 42P01, Error Code: 0

(i use squirrel-sql)

the trac (through python&pgsql) error was:
"Report execution failed: ERROR: invalid reference to FROM-clause entry
for table "t" HINT: There is an entry for table "t", but it cannot
be referenced from this part of the query."

but the code in the OP doesn't produce the error (i simplified the sql,
but propably striped the error as well)

the full code that does produce the error (and this error can be resolved
as in OP described) is:

SELECT
(CASE WHEN d.value = Null THEN '99999' ELSE d.value END)as
Wiedervorlage,
p.value AS __color__,
id AS ticket, summary, status, priority ,component, t.type AS type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS
owner,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter,
(CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS
Fachabteilung,
(CASE WHEN e.value = '0' THEN 'None' ELSE e.value END) AS Kategorie
FROM ticket as t, permission as perm, enum as p
LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name =
'fachabteilung')
LEFT Outer join ticket_custom d ON (t.id = d.ticket AND d.name =
'wiedervorlage')
LEFT Outer join ticket_custom e ON (t.id = e.ticket AND e.name =
'kategorie')
WHERE status IN ('new', 'assigned', 'reopened') AND perm.action =
'mf' and perm.username='$USER'
and p.name = t.priority AND p.type='priority'
ORDER BY wiedervorlage, priority, p.value, t.type, time

cheers,
thomas

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