Re: [sqlite] Unable to use table alias inside parenthesis on right side of join

[Ryan Johnson]

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

[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


___
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

Re: [sqlite] Unable to use table alias inside parenthesis on right side of join

[Kevin Benson]

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
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
>
> [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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unable to use table alias inside parenthesis on right side of join

[Ryan Johnson]

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