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 1EEAC53358 for ; Fri, 1 Apr 2005 17:38:05 +0100 (BST) 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 84799-02 for ; Fri, 1 Apr 2005 16:38:03 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id E211B52A30 for ; Fri, 1 Apr 2005 17:38:02 +0100 (BST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 26CA6355FB; Fri, 1 Apr 2005 08:37:59 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 24B07355FA; Fri, 1 Apr 2005 08:37:59 -0800 (PST) Date: Fri, 1 Apr 2005 08:37:59 -0800 (PST) From: Stephan Szabo To: Rosser Schwarz Cc: "pgsql-sql@postgresql.org" Subject: Re: Order of items in FROM causes error? In-Reply-To: <37d451f705040108246e6be935@mail.gmail.com> Message-ID: <20050401083347.P35655@megazone.bigpanda.com> References: <37d451f705040108246e6be935@mail.gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/15 X-Sequence-Number: 20908 On Fri, 1 Apr 2005, Rosser Schwarz wrote: > A colleague has the following query, which errors with: relation "dl" > does not exist. (See the second item in the FROM clause.) If that > item is moved to immediately precede the first JOIN item however, the > query works as expected. > > select u.usersfirstname || ' ' || u.userslastname as userssupervisorsname > , l.locationsname || ' >> ' || lv.listvaluesname as locationdepartment > , lvcat.listvaluesname as usersemploymentcategory > , lvclass.listvaluesname as usersemploymentclass > , lvacdcat.listcategoriesname as usersacdcategory > from intranet.tbl_users u > , intranet.tbl_departmentslocations dl > , intranet.tbl_listvalues lvcat > , intranet.tbl_listvalues lvclass > , intranet.tbl_listcategories lvacdcat > join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid > join intranet.tbl_locations l on dl.locationsid = l.locationsid > where u.usersid = 199 > and dl.listvaluesid = 13 > and lvcat.listvaluesid = 23 > and lvclass.listvaluesid = 27 > and lvacdcat.listcategoriesid = 6 > > This strikes me as a bug. Is it known behavior? A quick search > doesn't turn up much, but that may be a weakness in my google-fu. SQL seems to say that join binds more tightly than commas, so I don't believe dl is in scope for either of those ON clauses in the explicit join syntax.