I wonder if this earlier mention is related? http://osdir.com/ml/sqlite-users/2012-07/msg00054.html -- -- -- --Ô¿Ô-- K e V i N
On Thu, Jan 31, 2013 at 12:04 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca>wrote: > Hi all, > > Strange one here... consider the following schema: > > R(a,b) > S(b,c) > > These queries all work fine: > > select R1.* from R R1 join S S1 using(b); > select S1.* from R R1 join S S1 using(b); > select R1.* from (R R1 join S S1 using(b)); > select S1.* from (R R1 join S S1 using(b)); > select R1.* from (R R1 join S1 using(b)) join (R R2 join S2 using(b)) > using (a,c); > select S1.* from (R R1 join S1 using(b)) join (R R2 join S2 using(b)) > using (a,c); > > But these two do not: > > select R2.* from (R R1 join S S1 using(b)) join (R R2 join S S2 using(b)) > using (a,c); > >> Error: no such table: R2 >> > select S2.* from (R R1 join S S1 using(b)) join (R R2 join S S2 using(b)) > using (a,c); > >> Error: no such table: S2 >> > > Why are R1/S1 aliases visible at top level but R2/S2 aren't? It seems that > if the parentheses made those joined pairs into nested queries, then R1/S1 > should be lost; if parentheses are only for grouping, then R2/S2 should be > visible. Either way, something seems a bit off. > > The official syntax diagrams [1] suggest that parentheses imply only > grouping in this context. Then again, they also suggest it should be a > syntax error for a table alias to follow a join source in parentheses... > and that actually works fine, other than making the inner table aliases > unavailable (which kind of makes sense): > > select J1.* from (R R1 join S S1 using(b)) J1 join (R R2 join S S2 > using(b)) J2 using (a,c); > select J2.* from (R R1 join S S1 using(b)) J1 join (R R2 join S S2 > using(b)) J2 using (a,c); > select R1.* from (R R1 join S S1 using(b)) J1 join (R R2 join S S2 > using(b)) J2 using (a,c); > >> Error: no such table: R1 >> > > And yes, I've since ditched the USING syntax and gone back to WHERE > clauses, though the resulting is 20% longer and arguably much less clear > [2]: > > select R1.*, R2.* from (R R1 join S using(b)) join (R R2 join S using(b)) > using(a,c) where R1.b != R2.b > select R1.*, R2.* from R R1, R R2, S S1, S S2 where R1.b=S1.b and > R2.b=S2.b and R1.a=R2.a and S1.c=S2.c and R1.b != R2.b > > [1] > http://www.sqlite.org/**syntaxdiagrams.html#join-**source<http://www.sqlite.org/syntaxdiagrams.html#join-source> > > [2] Note that I added a predicate in, which I had stripped from the > earlier test cases for clarity > > Thoughts? > Ryan > > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
