left outer join fails because "column .. does not exist in left table?"

On Wed, Jun 30, 2010 at 7:01 PM, <Rick.Casey@colorado.edu> wrote:

I have a JOIN error that is rather opaque...at least to me.

I've using other JOIN queries on this project, which seem very similar to
this one, which looks like:

SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn
FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P
 LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid)
WHERE
       D.subjectidkey=S.id
       AND STY.studyindex=D.studyindex
       AND IPJ.projects_index=P.ibg_projects_index
ORDER BY studyabrv,boxnumber,wellcolumn,wellrow

But when I run it I get this error:

ERROR:  column "dnasampleid" specified in USING clause does not exist in
left table

I am rather mystified by this, since this field is definitely in the
dnasample table, as the primary key. Nor do not see how to start debugging
such an error. Any suggestions appreciated...

Capitalization maybe? pgsql folds to lower case.

Rick.Casey@colorado.edu writes:

SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn
FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P
LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid)
WHERE
D.subjectidkey=S.id
AND STY.studyindex=D.studyindex
AND IPJ.projects_index=P.ibg_projects_index
ORDER BY studyabrv,boxnumber,wellcolumn,wellrow
ERROR: column "dnasampleid" specified in USING clause does not exist in
left table

I am rather mystified by this, since this field is definitely in the
dnasample table, as the primary key.

It appears you're used to mysql, which processes commas and JOINs
left-to-right (more or less, I've never bothered to figure out their
behavior exactly). We follow the SQL standard, which says JOIN binds
tighter than comma. Therefore, the left-hand argument of the JOIN is
only ibg_projects not the cross join of DNASample x IBG_Studies x
Subjects x ibg_projects.

You could probably get the behavior you're expecting by writing

... FROM (DNASample D CROSS JOIN IBG_Studies STY CROSS JOIN Subjects S
CROSS JOIN ibg_projects P) LEFT OUTER JOIN ibg_ps_join IPJ USING ...

Or it might be enough to rearrange to

... FROM DNASample D LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid),
IBG_Studies STY, Subjects S, ibg_projects P WHERE ...

regards, tom lane

On Wed, Jun 30, 2010 at 8:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Rick.Casey@colorado.edu writes:

SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn
FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P
  LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid)
WHERE
      D.subjectidkey=S.id
      AND STY.studyindex=D.studyindex
      AND IPJ.projects_index=P.ibg_projects_index
ORDER BY studyabrv,boxnumber,wellcolumn,wellrow
ERROR:  column "dnasampleid" specified in USING clause does not exist in
left table

I am rather mystified by this, since this field is definitely in the
dnasample table, as the primary key.

It appears you're used to mysql, which processes commas and JOINs
left-to-right (more or less, I've never bothered to figure out their
behavior exactly).

Note that even MySQL now follows the standard on this, without needing
some special strict switch or anything. Of course, a lot of folks are
still using older versions that are in fact still broken.