Alvaro Herrera <[EMAIL PROTECTED]> writes: > Or show us where the standard mandates name munging and we > will implement it.
SQL92 says (in 7.9 <query specification>): 9) Case: a) If the i-th <derived column> in the <select list> specifies an <as clause> that contains a <column name> C, then the <column name> of the i-th column of the result is C. b) If the i-th <derived column> in the <select list> does not specify an <as clause> and the <value expression> of that <derived column> is a single <column reference>, then the <column name> of the i-th column of the result is C. c) Otherwise, the <column name> of the i-th column of the <query specification> is implementation-dependent and different from the <column name> of any column, other than itself, of a table referenced by any <table reference> contained in the SQL-statement. Rule (c) is a bit interesting since it does NOT say that a generated name has to be different from the generated names of other columns of the same SELECT; rather it has to be different from names of columns in base tables used by the SELECT. Not sure why they did that. We don't really honor this rule at present. In any case, rules (a) and (b) *directly* contradict the notion that output column names can be guaranteed unique. Also notice that generated names are "implementation-dependent" not "implementation-defined" --- that means that we do not have to document what the generation process is, nor promise that it won't change. This means that any application that depends on generated names to be particular things is violating the spec, and has only itself to blame when it breaks. You should be using AS if you want stable names to refer to these columns with. I haven't looked at SQL99 or SQL2003, but most likely they say the same thing in two or three times as many words ;-) regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly