Hi list, 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: SELECT 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); SELECT count(), x.a as y, x.z as y FROM aaa x GROUP BY y; -- No complaints, even though "y" is ambiguous here count()|y|y 2|1|3 -- Looks like the first alias "y" is used. Tested with versions 3.27.2 and 3.15.2. Regards, Rob Golsteijn Met Vriendelijke Groet, Kind Regards, 谨致问候, Rob --- Rob Golsteijn Software Engineer Mapscape Luchthavenweg 34 | 5657 EB Eindhoven | The Netherlands Phone +31 (0)40 7113583 | Fax: +31 (0)40 711 3599 www.mapscape.eu <http://www.mapscape.eu/> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
