> -----Ursprüngliche Nachricht----- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von Jay A. Kreibich > Gesendet: Freitag, 9. Dezember 2011 17:42 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] Unable to retrieve columns with table accessor on nested > joins. > > On Fri, Dec 09, 2011 at 01:12:26PM +0000, Fabrizio Steiner scratched on the > wall: > > 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.
I'm aware of this solution, but as you say it doesn't help for ambiguos columns or it will result in rewriting the sub-join to a subquery with field aliases. But e.g. SQL Server doesn't allow to specify an alias for a sub-join they only allow it for subqueries. I haven't taken a look onto the SQL92 specification if this is valid or not, but at least the SQL syntax page of sqlite states that it's not possible to specify an alias for join-source. https://sqlite.org/lang_select.html Defining an alias is only possible for a single qualified table or if it's a sub-query (select-statement). If this is really what was intended to be implemented, why should re-ordering make any difference, I think it really shouldn't matter if the sub-join is a right-hand sub-join or a left-hand 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. I completely agree with you for sub-quries. > 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). Yes, that is what my patch does. It only allows access to the tables of a sub-join which has been internally converted to a sub-query for execution. > Allowing > access to sub-query result set columns via their source table names > seems just as much a bug. Yes that should not be possible and that's also not possible with my patch. Regards Fabrizio _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
