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.
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
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 22.214.171.124.
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").