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 0A4325296F for ; Fri, 1 Apr 2005 17:35:30 +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 82321-09 for ; Fri, 1 Apr 2005 16:35:29 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D790353394 for ; Fri, 1 Apr 2005 17:35:28 +0100 (BST) 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 j31GZI9K007052; Fri, 1 Apr 2005 11:35:18 -0500 (EST) To: Rosser Schwarz Cc: "pgsql-sql@postgresql.org" Subject: Re: Order of items in FROM causes error? In-reply-to: <37d451f705040108246e6be935@mail.gmail.com> References: <37d451f705040108246e6be935@mail.gmail.com> Comments: In-reply-to Rosser Schwarz message dated "Fri, 01 Apr 2005 10:24:12 -0600" Date: Fri, 01 Apr 2005 11:35:18 -0500 Message-ID: <7051.1112373318@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/14 X-Sequence-Number: 20907 Rosser Schwarz writes: > 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. No, it isn't, because JOIN binds more tightly than comma. (People coming from MySQL tend to misunderstand this, because MySQL gets it wrong...) regards, tom lane