Looks like exactly the same thing, yes. No reply, unfortunately...
On 31/01/2013 1:05 PM, Kevin Benson wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
