Use of same aliases in single query

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Use of same aliases in single query

Rob Golsteijn
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users