Thread: Query issue/8.0.1/Serendipity
I have an 8.0.1 server running the Blogging software serendipity, and the following query fails with "relation e not defined", but it is on the first line: "SELECT timestamp FROM serendipity_entries e, serendipity_category c, serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended ON (e.id = ep_cache_extended.entryid AND ep_cache_extended.property = 'ep_cache_extended') LEFT OUTER JOIN serendipity_entryproperties ep_cache_body ON (e.id = ep_cache_body.entryid AND ep_cache_body.property = 'ep_cache_body') LEFT OUTER JOIN serendipity_entryproperties ep_access ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access') LEFT JOIN serendipity_entryproperties ep_sticky ON (e.id = ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky') WHERE e.timestamp >= 1109656800 AND e.timestamp <= 1112335200 AND e.timestamp <= 1110241185 AND e.isdraft = 'false' AND (ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1)) AND e.id = ec.entryid AND c.categoryid = ec.categoryid AND c.category_left BETWEEN 3 AND 4" The software is at: http://www.s9y.org/ here is the exact response from Pg: serendipity=# SELECT timestamp FROM serendipity_entries e, serendipity_categoryc, serendipity-# serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties serendipity-# ep_cache_extended ON (e.id = ep_cache_extended.entryid AND serendipity(# ep_cache_extended.property = 'ep_cache_extended') LEFT OUTER JOIN serendipity-# serendipity_entryproperties ep_cache_body ON (e.id = ep_cache_body.entryid serendipity(# AND ep_cache_body.property = 'ep_cache_body') LEFT OUTER JOIN serendipity-# serendipity_entryproperties ep_access ON (e.id = ep_access.entryid AND serendipity(# ep_access.property = 'ep_access') LEFT JOIN serendipity_entryproperties serendipity-# ep_sticky ON (e.id = ep_sticky.entryid AND ep_sticky.property = serendipity(# 'ep_is_sticky') WHERE e.timestamp >= 1109656800 AND e.timestamp <= 1112335200 serendipity-# AND e.timestamp <= 1110241185 AND e.isdraft = 'false' AND (ep_access.property serendipity(# IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR serendipity(# (ep_access.value = 'private' AND e.authorid = 1)) AND e.id = ec.entryid AND serendipity-# c.categoryid = ec.categoryid AND c.category_left BETWEEN 3 AND 4; ERROR: relation "e" does not exist serendipity=# What is wrong? serendipity=# select version(); version ---------------------------------------------------------------------PostgreSQL 8.0.1 on i686-unknown-sysv5UnixWare7.1.4,compiled by cc (1 row) serendipity=# -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611
Larry Rosenman wrote: > I have an 8.0.1 server running the Blogging software serendipity, and the > following query fails with "relation e not defined", but it is on the first > line: > > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties I think this line here is the problem. You've told it to LEFT OUTER JOIN from "ec", not "e". > ep_cache_extended ON (e.id = ep_cache_extended.entryid AND > ep_cache_extended.property = 'ep_cache_extended') LEFT OUTER JOIN > serendipity_entryproperties ep_cache_body ON (e.id = ep_cache_body.entryid > AND ep_cache_body.property = 'ep_cache_body') LEFT OUTER JOIN > serendipity_entryproperties ep_access ON (e.id = ep_access.entryid AND > ep_access.property = 'ep_access') LEFT JOIN serendipity_entryproperties > ep_sticky ON (e.id = ep_sticky.entryid AND ep_sticky.property = > 'ep_is_sticky') WHERE e.timestamp >= 1109656800 AND e.timestamp <= 1112335200 > AND e.timestamp <= 1110241185 AND e.isdraft = 'false' AND (ep_access.property > IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR > (ep_access.value = 'private' AND e.authorid = 1)) AND e.id = ec.entryid AND > c.categoryid = ec.categoryid AND c.category_left BETWEEN 3 AND 4" -- Richard Huxton Archonet Ltd
On Tuesday 08 March 2005 05:48 am, Larry Rosenman wrote: > I have an 8.0.1 server running the Blogging software serendipity, and the > following query fails with "relation e not defined", but it is on the first > line: [snip] I can put up a pg_dump -s if that would help any.... LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611
Larry, Restating your SQL in a more reader-friendly form: SELECT timestamp FROM serendipity_entries e , serendipity_category c , serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended ON (e.id = ep_cache_extended.entryid AND ep_cache_extended.property= 'ep_cache_extended') LEFT OUTER JOIN serendipity_entryproperties ep_cache_body ON (e.id = ep_cache_body.entryid AND ep_cache_body.property= 'ep_cache_body') LEFT OUTER JOIN serendipity_entryproperties ep_access ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access') LEFT JOIN serendipity_entryproperties ep_sticky ON (e.id = ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky') WHERE e.timestamp >= 1109656800 AND e.timestamp <= 1112335200 AND e.timestamp <= 1110241185 AND e.isdraft = 'false' AND ( ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1) ) AND e.id = ec.entryid AND c.categoryid =ec.categoryid AND c.category_left BETWEEN 3 AND 4 I can see you're using a lot of left joins. You are beginning your left joins off of ec, so I believe neither e nor c can be referenced as join conditions. It might work if you reorder the first part of the FROM clause as the following: FROM serendipity_category c , serendipity_entrycat ec , serendipity_entries e LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended (Though I can't be sure without seeing table definitions.) It also appears you have a redundant e.timestamp constraint in the WHERE clause: if e.timestamp is >= 1110241185 it's definitely going to be <= 1112335200 Hope this helps. I find white space helps me read my own SQL much more easily. Michael Glaesemann grzm myrealbox com
On Tuesday 08 March 2005 06:21 am, Michael Glaesemann wrote: > Larry, > > Restating your SQL in a more reader-friendly form: [snip] > This is from an error page (and machine generated to boot :) ) > I can see you're using a lot of left joins. You are beginning your left > joins off of ec, so I believe neither e nor c can be referenced as join > conditions. It might work if you reorder the first part of the FROM > clause as the following: > > FROM serendipity_category c > , serendipity_entrycat ec > , serendipity_entries e > LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended > > (Thugh I can't be sure without seeing table definitions.) a pg_dump -s is at: http://www.lerctr.org/~ler/s9y.sql > > It also appears you have a redundant e.timestamp constraint in the > WHERE clause: if e.timestamp is >= 1110241185 it's definitely going to > be <= 1112335200 this query is machine generated, so I'm sure it's based on timestamps. > > Hope this helps. I find white space helps me read my own SQL much more > easily. understood, as I said, it's machine generated :) > > Michael Glaesemann > grzm myrealbox com -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611
On Tuesday 08 March 2005 06:17 am, Richard Huxton wrote: > Larry Rosenman wrote: > > I have an 8.0.1 server running the Blogging software serendipity, and the > > following query fails with "relation e not defined", but it is on the > > first line: > > > > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > > > > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties > > I think this line here is the problem. You've told it to LEFT OUTER JOIN > from "ec", not "e". > and re-arranging the FROM clause to put serendipity_entries e right before the LEFT OUTER JOIN runs. I've reported this to the s9y folks. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611
On Mar 8, 2005, at 21:28, Larry Rosenman wrote: > On Tuesday 08 March 2005 06:21 am, Michael Glaesemann wrote: >> Larry, >> >> Restating your SQL in a more reader-friendly form: > [snip] >> > This is from an error page (and machine generated to boot :) ) Regardless if it's machine-generated or not, I'd still reformat it so I could read it more easily, and definitely before sending it to a mailing list to ask for advice. But that's me. >> I can see you're using a lot of left joins. You are beginning your >> left >> joins off of ec, so I believe neither e nor c can be referenced as >> join >> conditions. It might work if you reorder the first part of the FROM >> clause as the following: >> >> FROM serendipity_category c >> , serendipity_entrycat ec >> , serendipity_entries e >> LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended So, did this work? Michael Glaesemann grzm myrealbox com
On Tuesday 08 March 2005 06:35 am, Michael Glaesemann wrote: > On Mar 8, 2005, at 21:28, Larry Rosenman wrote: > > On Tuesday 08 March 2005 06:21 am, Michael Glaesemann wrote: > >> Larry, > >> > >> Restating your SQL in a more reader-friendly form: > > > > [snip] > > > > This is from an error page (and machine generated to boot :) ) > > Regardless if it's machine-generated or not, I'd still reformat it so I > could read it more easily, and definitely before sending it to a > mailing list to ask for advice. But that's me. > > >> I can see you're using a lot of left joins. You are beginning your > >> left > >> joins off of ec, so I believe neither e nor c can be referenced as > >> join > >> conditions. It might work if you reorder the first part of the FROM > >> clause as the following: > >> > >> FROM serendipity_category c > >> , serendipity_entrycat ec > >> , serendipity_entriess e > >> LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended > > So, did this work? Yes, as I stated in another post. I've reported it to the Serendipity folks (as I'm not sure where the query is coming from. Thanks (to all, btw). LER > > Michael Glaesemann > grzm myrealbox com -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611
Larry Rosenman <ler@lerctr.org> writes:
> I have an 8.0.1 server running the Blogging software serendipity, and the
> following query fails with "relation e not defined", but it is on the first
> line:
> "SELECT timestamp FROM serendipity_entries e, serendipity_category c,
> serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties
> ep_cache_extended ON (e.id = ep_cache_extended.entryid AND
> ep_cache_extended.property = 'ep_cache_extended') ...
Broken SQL that's only ever been tested on MySQL.
Last I heard, MySQL treated this sort of construct as joining
left-to-right, ie,
FROM e CROSS JOIN c CROSS JOIN ec LEFT JOIN ...
in which case the left argument of the LEFT JOIN already contains
e, c, and ec so it's OK for the JOIN condition to use e. Unfortunately
for MySQL users everywhere, this is expressly contrary to the SQL spec:
per spec, JOIN binds more tightly than commas in the FROM-list do.
(Is this on the mysql gotchas page?)
regards, tom lane
On Tuesday 08 March 2005 09:37 am, Tom Lane wrote: > Larry Rosenman <ler@lerctr.org> writes: > > I have an 8.0.1 server running the Blogging software serendipity, and the > > following query fails with "relation e not defined", but it is on the > > first line: > > > > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties > > ep_cache_extended ON (e.id = ep_cache_extended.entryid AND > > ep_cache_extended.property = 'ep_cache_extended') ... > > Broken SQL that's only ever been tested on MySQL. > > Last I heard, MySQL treated this sort of construct as joining > left-to-right, ie, > > FROM e CROSS JOIN c CROSS JOIN ec LEFT JOIN ... > > in which case the left argument of the LEFT JOIN already contains > e, c, and ec so it's OK for the JOIN condition to use e. Unfortunately > for MySQL users everywhere, this is expressly contrary to the SQL spec: > per spec, JOIN binds more tightly than commas in the FROM-list do. Thanks, Tom. Garvin Hicking (the Serendipity Developer) confirms it works on MySQL, and I wasn't sure about the spec. Thanks for confirming it's a MySQL gotcha :) LER > > (Is this on the mysql gotchas page?) > > regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611
>On Tue, 08 Mar 2005 10:37:51 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Larry Rosenman <ler@lerctr.org> writes: > > I have an 8.0.1 server running the Blogging software serendipity, and the > > following query fails with "relation e not defined", but it is on the first > > line: > > > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties > > ep_cache_extended ON (e.id = ep_cache_extended.entryid AND > > ep_cache_extended.property = 'ep_cache_extended') ... > > Broken SQL that's only ever been tested on MySQL. > > Last I heard, MySQL treated this sort of construct as joining > left-to-right, ie, > > FROM e CROSS JOIN c CROSS JOIN ec LEFT JOIN ... > > in which case the left argument of the LEFT JOIN already contains > e, c, and ec so it's OK for the JOIN condition to use e. Unfortunately > for MySQL users everywhere, this is expressly contrary to the SQL spec: > per spec, JOIN binds more tightly than commas in the FROM-list do. > > (Is this on the mysql gotchas page?) Nope, although as my plans for the week involve evaluating Serendipity using PostgreSQL I'll look into it. Ian Barwick barwick@gmail.com
On Tuesday 08 March 2005 10:55 am, Ian Barwick wrote: > > (Is this on the mysql gotchas page?) > > Nope, although as my plans for the week involve evaluating > Serendipity using PostgreSQL I'll look into it. Garvin already made a patch to CVS to fix this in s9y. See the php-blog-devs list. LER > > Ian Barwick > barwick@gmail.com -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611