pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Column reference X is ambiguous? 2+ messages / 2 participants [nested] [flat]
* Column reference X is ambiguous? @ 2001-05-27 05:32 Palle Girgensohn <girgen@partitur.se> 0 siblings, 0 replies; 2+ messages in thread From: Palle Girgensohn @ 2001-05-27 05:32 UTC (permalink / raw) To: pgsql-sql 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 AND d.course_id = f.kursid AND d.userid = rf.userid AND p.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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Column reference X is ambiguous? @ 2001-05-28 06:09 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2001-05-28 06:09 UTC (permalink / raw) To: Palle Girgensohn <girgen@partitur.se>; +Cc: pgsql-sql 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 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-05-28 06:09 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2001-05-27 05:32 Column reference X is ambiguous? Palle Girgensohn <girgen@partitur.se> 2001-05-28 06:09 Re: Column reference X is ambiguous? Tom Lane <tgl@sss.pgh.pa.us>
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