Simon Slavin wrote:
> As best I can find, SQL92 does not specify what happens when you choose
> an AS clause giving a value name the same as a column.
| 7.3 <table expression>
|
| Function
|
| Specify a table or a grouped table.
|
| Format
|
| <table expression> ::=
| <from clause>
| [ <where clause> ]
| [ <group by clause> ]
| [ <having clause> ]
|
| [...]
| General Rules
|
| 1) If all optional clauses are omitted, then the result of the <ta-
| ble expression> is the same as the result of the <from clause>.
| Otherwise, each specified clause is applied to the result of
| the previously specified clause and the result of the <table ex-
| pression> is the result of the application of the last specified
| clause.
|
| [...]
|
| 7.9 <query specification>
|
| Function
|
| Specify a table derived from the result of a <table expression>.
|
| Format
|
| <query specification> ::=
| SELECT [ <set quantifier> ] <select list> <table expression>
|
| <select list> ::=
| <asterisk>
| | <select sublist> [ { <comma> <select sublist> }... ]
|
| <select sublist> ::=
| <derived column>
| | <qualifier> <period> <asterisk>
|
| <derived column> ::= <value expression> [ <as clause> ]
|
| Syntax Rules
|
| 1) Let T be the result of the <table expression>.
| [...]
|
| 6) Each <column reference> directly contained in each <value ex-
| pression> ... shall unambiguously reference a column of T.
|
| [...]
| General Rules
|
| 1) a) ii) [...] each <value expression> is applied to each row of T
| yielding a table of M rows, where M is the cardinality of T.
| The i-th column of the table contains the values derived by
| the evaluation of the i-th <value expression>.
In other words, aliases in the SELECT clause are evaluated _after_ the
FROM and WHERE clauses are done.
The order of the SELECT/WHERE clauses in the SQL syntax is misleading;
the actual behaviour would be better represented by something like this:
( FROM test
WHERE name = 'foo!' )
SELECT test.name || '!' AS name;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
