The returned column names depend on the exact path taken in the query optimizer. This may change if the shape of your data changes (analyze is run).
The SQL standard only requires that column references be either unique or qualified by table. -----Ursprüngliche Nachricht----- Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Josef Kucera Gesendet: Donnerstag, 07. Juli 2016 17:42 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Query Flattener vs. result-set column names ----- Original Message ----- From: "Simon Slavin" <slav...@bigfraud.org> To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org> Sent: Thursday, July 07, 2016 5:11 PM Subject: Re: [sqlite] Query Flattener vs. result-set column names > > On 7 Jul 2016, at 3:37pm, Josef Kučera <jokusoftw...@gmail.com> wrote: > >> Imagine a query like "SELECT A.F1, B1.F FROM A JOIN (SELECT F2 AS F FROM >> B WHERE F3=0) B1". If the query flattener is active the result-set has >> columns "A.F1" and "B1.F". If it is disabled the result-set columns are >> "F1" and "F". The "short_column_names" option is set. >> >> I thought the optimization used, would not change the result-set column >> names. Was I wrong? > > Yes. There is no documentation for result-set column names unless you > specify them using "AS". They can be anything, and they can change from > one version of SQLite to another. Never rely on them unless you're using > "AS". > Yeah, I understand the column names are not defined, but I expected them to be static for a specific SQLite version, SQL command and database. This is a wrong assumption then? > Also, your natural JOIN (sometimes called ANSI JOIN) is a little dangerous > since it can collapse if you change column names or definitions. People > have been complaining about the problem for over a decade. An example is > when a program stops working because someone added 'info' columns to both > tables and the JOIN command is now looking only for cases where they > match. > > It's better to define the connection between the two tables explicitly. I > would hope to see something more like > > SELECT A.F1, B.F FROM A JOIN B ON B.x = A.y > > This lets you define column names all in one place more neatly: > > SELECT A.F1 AS F1, B.F AS F1 FROM A JOIN B ON B.x = A.y > > rather than having to put some "AS" at the beginning and other "AS" in the > sub-select. > Thank you for the detailed explanation. The natural JOIN was used only to simplify the sample command, I always explicitly declare the columns used for the JOIN operation for the same reasons you mentioned. > Simon. Joe _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
