Accidentally I discovered that I could use the same table aliases for 2 tables in a single sql statement.
As long as I do not use a column name that exists in both tables Sqlite accepts this.
It looks strange to me that I can use the same aliases multiple times, and that they co-exist.
As a programmer I'm not used to this, but the Sqlite docs do not seem to forbid this. Is this a bug or a feature?
I guess because of backward compatibility this behavior will not be changed. Something to document in https://www.sqlite.org/quirks.html ?
Similar behavior for result row aliases, but in that case the ambiguous aliases can even be used.
Examples duplicate table aliases:
CREATE TABLE aaa (a INT, z INT);
CREATE TABLE bbb (b INT, z INT);
SELECT 1 FROM aaa x, bbb x ON x.a = x.b; -- Same alias "x" for 2 tables, but no complaints (all column names in the query can be resolved)
SELECT 1 FROM aaa x, bbb x ON x.z = x.z; -- "Error: ambiguous column name: x.z"
SELECT * FROM aaa x, bbb x ON x.a = x.b; -- "Error: ambiguous column name: x.z" (during expansion of *)
Example duplicate result rows aliases:
x.a as y,
x.z as y -- Same alias, but no complaints
FROM aaa x;
And the result row aliases can even be used in the query.
INSERT INTO "aaa" VALUES(1,2);
INSERT INTO "aaa" VALUES(1,3);
x.a as y,
x.z as y
FROM aaa x
GROUP BY y; -- No complaints, even though "y" is ambiguous here
2|1|3 -- Looks like the first alias "y" is used.
Mapscape B.V. is ISO9001:2008 certified.This e-mail and any attachment may contain corporate proprietary information and may only be read, copied and used by the intended recipient. If you have received it by mistake, please notify us immediately by reply e-mail and delete this e-mail and its attachments from your system. We believe but do not warrant that this message and any attachments are virus free. Mapscape B.V. is registered at the Kamer van Koophandel Oost-Brabant located in Eindhoven, The Netherlands number 17210210