Re: [sqlite] Bug? "no such column" referring to a table by name, works by alias

[Drake Wilson]

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

[sqlite] Bug? "no such column" referring to a table by name, works by alias

[Igor Tandetnik]

Consider this SQLite session:

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). Looks like a bug to me 
(though, admittedly, it's rather a corner case, and there's an easy workaround).
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users