X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 937855636F for ; Tue, 8 Mar 2005 15:37:52 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04535-10 for ; Tue, 8 Mar 2005 15:37:50 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 45ABC5636C for ; Tue, 8 Mar 2005 15:37:49 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j28FbpnX006330; Tue, 8 Mar 2005 10:37:51 -0500 (EST) To: Larry Rosenman Cc: pgsql-sql@postgresql.org Subject: Re: Query issue/8.0.1/Serendipity In-reply-to: <200503080548.17179.ler@lerctr.org> References: <200503080548.17179.ler@lerctr.org> Comments: In-reply-to Larry Rosenman message dated "Tue, 08 Mar 2005 05:48:16 -0600" Date: Tue, 08 Mar 2005 10:37:51 -0500 Message-ID: <6329.1110296271@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/132 X-Sequence-Number: 20611 Larry Rosenman 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