On Sunday, 5 January, 2020 04:42, Richard Hipp <d...@sqlite.org> wrote:
>On 1/5/20, Keith Medcalf <kmedc...@dessus.com> wrote: >> Hrm. Inconsistent/incorrect results. Consider: >> create table a(id integer primary key, a); >> insert into a values (1,1), (2,1), (3,1); >> create table b(id integer primary key, b); >> insert into b values (1,2), (3,2), (4,2); >> create table c(id integer primary key, c); >> insert into c values (1,3), (4,3), (5,3); >> select * from a, b, c using (id); -- very strange result >> id a id b c >> ---------- ---------- ---------- ---------- ---------- >> 1 1 1 2 3 >> 1 1 3 2 3 >> 1 1 4 2 3 >PostgreSQL and MySQL process the query as follows: > SELECT * FROM a, (b JOIN c USING(id)); >SQLite processes the query like this: > SELECT * FROM (a,b) JOIN c USING (id); >I don't know which is correct. Perhaps the result is undefined. >Note that both MySQL and SQLite do allow you to use parentheses, as >shown in my examples, to define the order of evaluation. PostgreSQL >does not, sadly. >MS-SQL does not (as far as I can tell) support the USING syntax on a >join. Aha! So as far as SQLite is concerned the syntax "... JOIN <x> USING (<y>)" is effectively binding the using expression for the nested nested loop descent into into table <y> only and does not bind against the immediately preceeding LHS JOIN table. By adding some indexes and order by that cause the nesting order to change it appear that "a, b join c using (id)" is always processed as selecting the first lexically named id column irrepective of nesting order (that is "a, b join c using (id)" always becomes "a, b, c where a.id == c.id" and that "b, a join c using (id)" always becomes "a, b, c where b.id == c.id" even when the optimizer chooses to re-arrange the nesting order (such as by additional indexes and order by's)). Since the "id" column to use is ambiguous for descent into "c" should not an "ambiguous column name" error be thrown? The ambiguity only does not exist if ALL columns named "id" (for all tables that could be in an outer loop respective to "c") are constrained equal -- that is a,b,c using (id) -> a, b, c where a.id == b.id and a.id == c.id and b.id == c.id. Since "natural join" devolves into a using, does not the same problem exist there as well? Since any change is likely to have an effect on already existing and functional applications, could the behaviour be documented somewhere perhaps? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
