pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery issue/8.0.1/Serendipity
12+ messages / 5 participants
[nested] [flat]
* Query issue/8.0.1/Serendipity
@ 2005-03-08 11:48 Larry Rosenman <ler@lerctr.org>
0 siblings, 4 replies; 12+ messages in thread
From: Larry Rosenman @ 2005-03-08 11:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 12:17 Richard Huxton <dev@archonet.com>
parent: Larry Rosenman <ler@lerctr.org>
3 siblings, 1 reply; 12+ messages in thread
From: Richard Huxton @ 2005-03-08 12:17 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 12:17 Larry Rosenman <ler@lerctr.org>
parent: Larry Rosenman <ler@lerctr.org>
3 siblings, 0 replies; 12+ messages in thread
From: Larry Rosenman @ 2005-03-08 12:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 12:21 Michael Glaesemann <grzm@myrealbox.com>
parent: Larry Rosenman <ler@lerctr.org>
3 siblings, 1 reply; 12+ messages in thread
From: Michael Glaesemann @ 2005-03-08 12:21 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 12:28 Larry Rosenman <ler@lerctr.org>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 12+ messages in thread
From: Larry Rosenman @ 2005-03-08 12:28 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 12:29 Larry Rosenman <ler@lerctr.org>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 12+ messages in thread
From: Larry Rosenman @ 2005-03-08 12:29 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 12:35 Michael Glaesemann <grzm@myrealbox.com>
parent: Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 12+ messages in thread
From: Michael Glaesemann @ 2005-03-08 12:35 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 12:36 Larry Rosenman <ler@lerctr.org>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 0 replies; 12+ messages in thread
From: Larry Rosenman @ 2005-03-08 12:36 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 15:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Larry Rosenman <ler@lerctr.org>
3 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2005-03-08 15:37 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 15:41 Larry Rosenman <ler@lerctr.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Larry Rosenman @ 2005-03-08 15:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; mail@garv.in
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 16:55 Ian Barwick <barwick@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Ian Barwick @ 2005-03-08 16:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Larry Rosenman <ler@lerctr.org>; pgsql-sql
>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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query issue/8.0.1/Serendipity
@ 2005-03-08 16:58 Larry Rosenman <ler@lerctr.org>
parent: Ian Barwick <barwick@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Larry Rosenman @ 2005-03-08 16:58 UTC (permalink / raw)
To: Ian Barwick <barwick@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2005-03-08 16:58 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-08 11:48 Query issue/8.0.1/Serendipity Larry Rosenman <ler@lerctr.org>
2005-03-08 12:17 ` Richard Huxton <dev@archonet.com>
2005-03-08 12:29 ` Larry Rosenman <ler@lerctr.org>
2005-03-08 12:17 ` Larry Rosenman <ler@lerctr.org>
2005-03-08 12:21 ` Michael Glaesemann <grzm@myrealbox.com>
2005-03-08 12:28 ` Larry Rosenman <ler@lerctr.org>
2005-03-08 12:35 ` Michael Glaesemann <grzm@myrealbox.com>
2005-03-08 12:36 ` Larry Rosenman <ler@lerctr.org>
2005-03-08 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-08 15:41 ` Larry Rosenman <ler@lerctr.org>
2005-03-08 16:55 ` Ian Barwick <barwick@gmail.com>
2005-03-08 16:58 ` Larry Rosenman <ler@lerctr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox