On Mon, 2006-10-02 at 11:38 -0400, Tom Lane wrote: > Chris Dunlop <[EMAIL PROTECTED]> writes: > > I'm not sure if this is a bug or if it's displaying my ignorance > > of this corner of SQL... > > > update a set name = ( > > select name > > from temp.a > > where temp.a.id = a.id > > ) > > Postgres treats "FROM temp.a" the same as "FROM temp.a AS a", and then > the "a.id" references that rather than the outer query's A. Try just > "select name from temp.a where temp.a.id = a.id;" to see this in action.
[Which AFAICS doesn't follow SQL:2003, since a reference to temp.a should not be allowed following its redefinition as a.] > Looking at the SQL spec, I'm having a hard time finding any verbiage > that either confirms or denies this interpretation. It says that a FROM > item without a correlation name (ie, an AS alias) exposes the table > name, and then a column reference can refer to the table name, but it's > not at all clear whether the table name must be qualified or not in the > reference. Comments anyone? In my SQL:2003 draft, there is a "Language Opportunity" here: "242 [From London] The following Opportunity exists: For language consistency, a correlation name should be permitted for the modiﬁed table in positioned and searched update and delete statements." However, somebody claiming to be Joe Celko is quoted here as saying that is not part of the SQL:2003 standard http://www.thescripts.com/forum/thread65819.html ISTM the most obvious route in this situation differs from normal usage: treat any unqualified names that match the target table as a reference to the target table, rather than potentially another table. i.e. treat this situation as a correlated sub-query rather than as an independent query. No correlation name on the target table is allowed, so there is no possibility of writing a correlation name to allow the query to be more readable: > update a correlationname set name = ( > select name > from temp.a > where temp.a.id = correlationname.a.id > ) (which is definitely not allowed by SQL:2003) Having said all of that, its clearly a grey area so no need to change this as part of beta, since we could easily cause more wierdness than we solve. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq