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 88E8956489 for ; Tue, 8 Mar 2005 16:56:05 +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 30804-03 for ; Tue, 8 Mar 2005 16:55:47 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id 1513F56385 for ; Tue, 8 Mar 2005 16:55:46 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 70so1835514wra for ; Tue, 08 Mar 2005 08:55:46 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=aebYo5PffovJEkSJbT+/FDeyTU+KtFptUTcjZ8XLcejhqiB43mwmRsQr50/X9BgTFuQpnTZoFbYJEOoOj2xP0Z8m9P0KdrElSROp3A1ZexKRqX0FiUH3VHc2smjm/bGzIy2MJcWdlYE7K420/VMciY5pKw4fOaj52vhwTsfeu34= Received: by 10.54.13.25 with SMTP id 25mr14361wrm; Tue, 08 Mar 2005 08:55:44 -0800 (PST) Received: by 10.54.5.58 with HTTP; Tue, 8 Mar 2005 08:55:44 -0800 (PST) Message-ID: <1d581afe0503080855216ad38f@mail.gmail.com> Date: Tue, 8 Mar 2005 17:55:44 +0100 From: Ian Barwick Reply-To: Ian Barwick To: Tom Lane Subject: Re: Query issue/8.0.1/Serendipity Cc: Larry Rosenman , pgsql-sql@postgresql.org In-Reply-To: <6329.1110296271@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <200503080548.17179.ler@lerctr.org> <6329.1110296271@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.213 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/134 X-Sequence-Number: 20613 >On Tue, 08 Mar 2005 10:37:51 -0500, Tom Lane wrote: > 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?) Nope, although as my plans for the week involve evaluating Serendipity using PostgreSQL I'll look into it. Ian Barwick barwick@gmail.com