Error: ambiguous column name

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Error: ambiguous column name

Rob Golsteijn
Hi List,



Given a table created as:   create table aaa(a);

Sqlite reports an error "ambiguous column name: main.aaa.a" for the following query.


select * from aaa, aaa;
Error: ambiguous column name: main.aaa.a



And also for similar queries



select * from aaa INNER JOIN aaa;
select * from aaa CROSS JOIN aaa;

select * from aaa JOIN aaa;



Tested with sqlite version 3.21.0 and an old version 3.8.4.3.



I think the query is valid and should not result in an error. Typically Sqlite would name the resulting columns "a" and "a:1" in this case.

Workaround: add an alias for one of the tables in the join (both columns will be called "a").



Met Vriendelijke Groet, Kind Regards, 谨致问候,

Rob Golsteijn
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Error: ambiguous column name

Hick Gunter
A bare field name is legal in SQL only when it is unique; otherwise it needs to be qualified by table name or table alias. In a self join, the table name is identical, so using aliases is mandatory.

Result column names are not well defined in the SQL standard (apart from the use of the AS clause in the SELECT list), but most SQL engines will attempt to use something from either the SELECT statement itself (e.g. the text of an expression), or the table declaration. Result column names are not required to be unique, nor are they guranteed not to change between releases of the underlying SQL engine.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Rob Golsteijn
Gesendet: Dienstag, 14. November 2017 16:38
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Error: ambiguous column name

Hi List,



Given a table created as:   create table aaa(a);

Sqlite reports an error "ambiguous column name: main.aaa.a" for the following query.


select * from aaa, aaa;
Error: ambiguous column name: main.aaa.a



And also for similar queries



select * from aaa INNER JOIN aaa;
select * from aaa CROSS JOIN aaa;

select * from aaa JOIN aaa;



Tested with sqlite version 3.21.0 and an old version 3.8.4.3.



I think the query is valid and should not result in an error. Typically Sqlite would name the resulting columns "a" and "a:1" in this case.

Workaround: add an alias for one of the tables in the join (both columns will be called "a").



Met Vriendelijke Groet, Kind Regards, 谨致问候,

Rob Golsteijn
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users