Hi!
I'm porting some MySQL code to SQLite 2.8.16. I'm having problems with a few multi-tables queries used by my application: The problems seems to be related with inability of including "calculated field" names in ORDER BY and/or HAVING clauses, although I might be wrong (and most probably I am) .
The simplest of the queries:
SELECT countries.*,
coalesce(cd1.country_name, cd2.country_name) AS country_name,
coalesce(cd1.language_id, cd2.language_id) AS language_id
FROM countries
LEFT JOIN countries_description AS cd1 ON (countries.country_id=cd1.country_id AND cd1.language_id=1)
LEFT JOIN countries_description AS cd2 ON (countries.country_id=cd2.country_id AND cd2.language_id=0)
WHERE 1
GROUP BY countries.country_id
HAVING 1
ORDER BY country_name ASC;
returns error: [nativecode=ambiguous column name: country_name]
Firstly let me explain given query: I'm using fallback language substitution feature. I have one 'countries' table with country_id as primary key, and 'countries_description' table holding country names in different languages (two field primary key) - schema below:
CREATE TABLE 'countries' (
'country_id' char(2) NOT NULL default '',
PRIMARY KEY ('country_id')
);CREATE TABLE 'countries_description' (
'country_id' char(2) NOT NULL default '',
'language_id' tinyint(4) NOT NULL default '0',
'country_name' varchar(20) NOT NULL default '',
PRIMARY KEY ('country_id','language_id')
);With presented query I'd like to retrieve all country_ids with assigned name in language_id=1, and if there is no entry for that country in language_id=1, get translated entry in language_id = 0 (fallback language). Therefore i'm using two JOINS and coalesce() function.
Worth noting - i'm using PRAGMA short_column_names = 1; for compatibility in multi-table SELECT results. The code works perfectly in MySQL. SQLite complains about ambiguity of 'country_name' field in ORDER BY clause. Using cd1.country_name or cd2.country_name helps, BUT it's not what i expect of this query, i'd like to order by appropriate (fallback translated or not) entries - therefore i wanted to use calculated field 'country_name' alias.
When I change the alias name to anything else (i.e. not to existing field name)- it works OK, but is it possible to use alias name that is not causing conflict with field names WITHOUT having to rewrite code retreiving results (because of column name change)?
The same situation occurs with HAVING clause - using alias name of calculated field throws ambiguity error because i'm joining tables with fields named like alias - although they never appear as result fields directly (they are used only in expression of alias field).
If you could help me with this mater, I would be very thankful. Any tricks, hacks...
Thanks in advance,
With regards, Krzysztof Kotowicz
