----- 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
