Quoth Igor Tandetnik , on 2010-09-24 08:16:07 -0400:
> sqlite> create table A (id integer primary key);
> sqlite> create table B (id integer primary key, name text);
> sqlite> select * from A as X order by (select name from B where B.id = A.id);
> Error: no such column: A.id
> sqlite> select * from A as X order by (select name from B where B.id = X.id);
> sqlite>
>
> One would expect that table name and alias should work
> interchangeably except when the name is ambiguous (which is not the
> case here).
I wouldn't, actually. I would expect the table alias to "overwrite"
the original table name as the source name; essentially a table named
"t" when used in a SELECT statement generates a source named "t" by
default, and "t AS s" or "t s" generates a source named "s" instead.
Tables are not themselves sources in the strictest sense; a source
references a table (and "is" the table for many purposes).
http://archives.postgresql.org/pgsql-general/2000-08/msg00740.php
claims that SQL92 requires this behavior.
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users