Thread: Order of items in FROM causes error?
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.listvaluesnameas locationdepartment , lvcat.listvaluesname as usersemploymentcategory , lvclass.listvaluesname asusersemploymentclass , lvacdcat.listcategoriesname as usersacdcategory from intranet.tbl_users u , intranet.tbl_departmentslocationsdl , intranet.tbl_listvalues lvcat , intranet.tbl_listvalues lvclass , intranet.tbl_listcategorieslvacdcat join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid join intranet.tbl_locationsl on dl.locationsid = l.locationsidwhere 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
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
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.