pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrder of items in FROM causes error?
3+ messages / 3 participants
[nested] [flat]
* Order of items in FROM causes error?
@ 2005-04-01 16:24 Rosser Schwarz <rosser.schwarz@gmail.com>
2005-04-01 16:35 ` Re: Order of items in FROM causes error? Tom Lane <tgl@sss.pgh.pa.us>
2005-04-01 16:37 ` Re: Order of items in FROM causes error? Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 3+ messages in thread
From: Rosser Schwarz @ 2005-04-01 16:24 UTC (permalink / raw)
To: pgsql-sql
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.
We're running 7.4.2. (Yes, I know; I'm about to start testing 8.0.2
on our new server.)
/rls
--
:wq
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Order of items in FROM causes error?
2005-04-01 16:24 Order of items in FROM causes error? Rosser Schwarz <rosser.schwarz@gmail.com>
@ 2005-04-01 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-04-01 16:35 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: pgsql-sql
Rosser Schwarz <rosser.schwarz@gmail.com> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Order of items in FROM causes error?
2005-04-01 16:24 Order of items in FROM causes error? Rosser Schwarz <rosser.schwarz@gmail.com>
@ 2005-04-01 16:37 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2005-04-01 16:37 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-04-01 16:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-01 16:24 Order of items in FROM causes error? Rosser Schwarz <rosser.schwarz@gmail.com>
2005-04-01 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-01 16:37 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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