Обсуждение: 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@braindumped.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
"Thomas Peter" <thomas@braindumped.com> writes:
> the full code that does produce the error (and this error can be resolved
> as in OP described) is:
Never oversimplify a bug report.
> 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')
The above is, plain and simple, wrong. According to the SQL spec,
JOIN binds more tightly than comma in a FROM-list, so what you had was
FROM ..., (enum as p
LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name =
'fachabteilung'))
which of course fails because only p and c are visible in the JOIN's
ON condition. You fixed it by moving "t" to become part of the JOIN
structure.
I was aware that MySQL parses this sort of structure wrongly, but it's
disappointing to hear that sqlite does too :-(
regards, tom lane
Am 26.9.2006 schrieb "Tom Lane" <tgl@sss.pgh.pa.us>: >> 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') > >The above is, plain and simple, wrong. According to the SQL spec, >JOIN binds more tightly than comma in a FROM-list, so what you had was > >FROM ..., (enum as p > LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name = > 'fachabteilung')) > >which of course fails because only p and c are visible in the JOIN's >ON condition. You fixed it by moving "t" to become part of the JOIN >structure. ok, lesson learned. thank you! 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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thomas Peter Sent: Tuesday, September 26, 2006 9:15 AM To: pgsql-general@postgresql.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
Am 26.9.2006 schrieb "Tom Lane" <tgl@sss.pgh.pa.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
On Tue, 2006-09-26 at 09:19, Tom Lane wrote: > "Thomas Peter" <thomas@braindumped.com> writes: > > the full code that does produce the error (and this error can be resolved > > as in OP described) is: > > Never oversimplify a bug report. > > > 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') > > The above is, plain and simple, wrong. According to the SQL spec, > JOIN binds more tightly than comma in a FROM-list, so what you had was > > FROM ..., (enum as p > LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name = > 'fachabteilung')) > > which of course fails because only p and c are visible in the JOIN's > ON condition. You fixed it by moving "t" to become part of the JOIN > structure. > > I was aware that MySQL parses this sort of structure wrongly, but it's > disappointing to hear that sqlite does too :-( And I think MySQL fixed this abberant behaviour in the newest beta.
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Tue, 2006-09-26 at 09:19, Tom Lane wrote:
>> I was aware that MySQL parses this sort of structure wrongly, but it's
>> disappointing to hear that sqlite does too :-(
> And I think MySQL fixed this abberant behaviour in the newest beta.
Actually they seem to have fixed it awhile ago --- I get this in 5.0.22:
mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.02 sec)
mysql> create table t2(f2 int);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t1 c, t2 a join t1 b on (a.f2 = b.f1);
Empty set (0.00 sec)
mysql> select * from t1 c, t2 a join t1 b on (a.f2 = c.f1);
ERROR 1054 (42S22): Unknown column 'c.f1' in 'on clause'
mysql> select * from t1 c cross join t2 a join t1 b on (a.f2 = c.f1);
Empty set (0.01 sec)
which appears to conform to the letter of the standard.
FYI, PG 8.2 will produce a possibly more useful error message:
regression=# select * from t1 c, t2 a join t1 b on (a.f2 = c.f1);
ERROR: invalid reference to FROM-clause entry for table "c"
LINE 1: select * from t1 c, t2 a join t1 b on (a.f2 = c.f1);
^
HINT: There is an entry for table "c", but it cannot be referenced from this part of the query.
regression=#
regards, tom lane