On Fri, Dec 09, 2011 at 01:12:26PM +0000, Fabrizio Steiner scratched on the
wall:
> Hello
>
> I'm currently facing a problem with nested right hand joins. I've also
> reported this to the mailing list over one month ago, but haven't
> received any reply. In the meantime I've investigated the problem in
> the SQLite source and sorted some things out.
>
> Let's first start with an example which reproduces the problem:
>
> CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
> CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
> CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);
>
> INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'data1');
> INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'data2');
> INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'data3');
>
> Exeuting the following query works as expected and results?:
> data1 | null | null
>
> SELECT t1_title, t2_title, t3_title
> FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2_id;
>
> If you now use the tablename t2 or t3 to access the columns like in the
> following query?:
>
> SELECT t1_title, t2.t2_title, t3.t3_title
> FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2.t2_id;
>
> You will receive?SQL Error: no such column: t2.t2_title
If you name the result of the sub-join, making it a "top level"
object, things work fine:
SELECT t1_title, sub.t2_title, sub.t3_title
FROM t1 LEFT JOIN
( t2 INNER JOIN t3 ON t2_id = t3_id ) AS sub
ON t1_id = sub.t2_id;
This avoids having to re-order the query, although I suppose it
doesn't solve the problem of ambiguous column names in the sub-join.
> This is perfectly fine if it's a subquery but if the subquery
> represents a nested join it has to be possible to access the tables
> used in the subquery. At least it's possible with all the database
> systems I'm working with in daily business.
Some SQL engines actually require sub-queries to be named. The
columns lose their association with their source tables in a
sub-query, so column level access required giving the result a name,
not unlike I have done above.
As you pointed out, that means that when the sub-join takes on the
context of a full sub-query, the naming conventions follow, and that
might be considered a bug. The system needs to distinguish between a
sub-join converted to a sub-query and a full sub-query, however, and
only allow "deeper" access for sub-joins (I didn't have a chance to
review your patch to see if you account for this or not). Allowing
access to sub-query result set columns via their source table names
seems just as much a bug.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
