Thread: Column reference X is ambiguous?

Hi!

SELECT count(rf.userid) as count, rf.userid FROM read_faq rf,
faq f,deltagare_saved d, person p WHERE rf.id = f.id AND f.kursid=20
ANDd.course_id = f.kursid AND d.userid = rf.userid ANDp.userid = d.userid GROUP BY userid
ERROR:  Column reference "userid" is ambiguous


All of the tables have a userid, yes, but this query didn't
fail before 7.1.x. Is it really OK to fail in this case? I
thought SQL standard requires all GROUP|ORDER BY arguments to
acutally exist on the SELECT target list. Then, this makes me
puzzled... Are the joins making the userids get included in the
target in some hidden way?

/Palle

Palle Girgensohn <girgen@partitur.se> writes:
>  ... GROUP BY userid
>  ERROR:  Column reference "userid" is ambiguous

> All of the tables have a userid, yes, but this query didn't
> fail before 7.1.x.

Perhaps we accepted it back around 6.5 ... but AFAIK the behavior
hasn't altered between 7.0 and 7.1.

> Is it really OK to fail in this case? I
> thought SQL standard requires all GROUP|ORDER BY arguments to
> acutally exist on the SELECT target list.

ORDER BY works that way, but not GROUP BY, since GROUP BY logically
happens before the targetlist is computed.  The standard specifies
substantially different interpretations of GROUP BY and ORDER BY
items.

You might find the comments in findTargetlistEntry() enlightening:
    * Handle two special cases as mandated by the SQL92 spec:    *    * 1. Bare ColumnName (no qualifier or subscripts)
  *      For a bare identifier, we search for a matching column name    *      in the existing target list.    Multiple
matchesare an error    *      unless they refer to identical values; for example,    *      we allow    SELECT a, a
FROMtable ORDER BY a    *      but not    SELECT a AS b, b FROM table ORDER BY b    *      If no match is found, we
fallthrough and treat the identifier    *      as an expression.    *      For GROUP BY, it is incorrect to match the
groupingitem against    *      targetlist entries: according to SQL92, an identifier in GROUP BY    *      is a
referenceto a column name exposed by FROM, not to a target    *      list column.    However, many implementations
(includingpre-7.0    *      PostgreSQL) accept this anyway.  So for GROUP BY, we look first    *      to see if the
identifiermatches any FROM column name, and only    *      try for a targetlist name if it doesn't.  This ensures that
we   *      adhere to the spec in the case where the name could be both.    *      DISTINCT ON isn't in the standard,
sowe can do what we like there;    *      we choose to make it work like ORDER BY, on the rather flimsy    *
groundsthat ordinary DISTINCT works on targetlist entries.    *    * 2. IntegerConstant    *      This means to use the
n'thitem in the existing target list.    *      Note that it would make no sense to order/group/distinct by an    *
actual constant, so this does not create a conflict with our    *      extension to order/group by an expression.    *
   GROUP BY column-number is not allowed by SQL92, but since    *      the standard has no other behavior defined for
thissyntax,    *      we may as well accept this common extension.    *    * If neither special case applies, fall
throughto treat the item as    * an expression.  (This is a Postgres extension not found in SQL92).
 

In short: Postgres accepts everything the standard requires in this
area, and a lot that the standard does not require.
           regards, tom lane