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
