Re: Column reference X is ambiguous?

Palle Girgensohn <girgen(at)partitur(dot)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 matches are an error
* unless they refer to identical values; for example,
* we allow SELECT a, a FROM table ORDER BY a
* but not SELECT a AS b, b FROM table ORDER BY b
* If no match is found, we fall through and treat the identifier
* as an expression.
* For GROUP BY, it is incorrect to match the grouping item against
* targetlist entries: according to SQL92, an identifier in GROUP BY
* is a reference to a column name exposed by FROM, not to a target
* list column. However, many implementations (including pre-7.0
* PostgreSQL) accept this anyway. So for GROUP BY, we look first
* to see if the identifier matches 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, so we can do what we like there;
* we choose to make it work like ORDER BY, on the rather flimsy
* grounds that ordinary DISTINCT works on targetlist entries.
*
* 2. IntegerConstant
* This means to use the n'th item 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 this syntax,
* we may as well accept this common extension.
*
* If neither special case applies, fall through to 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