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