Re: Ugly problem with USING vs ON in JOIN

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

On further examination, the USING rules are not as clear cut as they 
might otherwise be.

TRUE:
This works at v5r4 but not 7.1:
select a.key, b.key, a.data, b.data
from fileA A join fileb B using (key)


and

this works at 7.1 but not v5r4:
select key, a.data, b.data
from fileA A join fileb B using (key)


However:

This works at 7.1:
select a.key, a.*
from fileA A join fileb B using (key)


but this does not work at 7.1:
select b.key, a.*
from fileA A join fileb B using (key)


I don't quite get the rules.  I also don't understand how a.key + a.* 
doesn't give me either a syntax error or a duplicate column.  Either key 
is part of a or it isn't, and it seems that the SQL engine isn't quite sure.

I hate to stop using USING, because it really is helpful especially when 
you have more than one field in the JOIN, so I need to get a better 
handle on this.

Joe


> Joe,
>
> The problem is that while IBM added USING at v5r4, they didn't
> implement it correctly.
>
> It got fixed at 6.1, but not PTF'ed back to v5r4.
>
> This works at v5r4 but not 6.1:
> select a.key, b.key, a.data, b.data
> from fileA A join fileb B using (key)
>
> this works at 6.1 but not 5.4
> select key, a.data, b.data
> from fileA A join fileb B using (key)
>
> The fix is good because at 6.1 you can do this:
> select key, a.data, b.data, c.data
> from fileA A
>           join fileb B using (key)
>           join filec C using (key)
>
> There should be some posts from me from a while ago discussing the
> issue.  My decisions was to only use JOIN USING at v5r4 in ad-hoc
> stuff.  Production SQL had to continue to use the JOIN ON syntax.
>
> Charles
>
>
> On Mon, Mar 14, 2011 at 11:27 AM, Joe Pluta<joepluta@xxxxxxxxxxxxxxxxx>  wrote:
> > Here's a real beauty.  Try this with any file:
> >
> > with t1 as (select key1, data1 from myfile)
> >    select t1.* from t1 join myfile using (key1)
> >
> > Then try this:
> >
> > with t1 as (select key1, data1 from myfile)
> >    select t1.* from t1 join myfile on t1.key1 = myfile.key1
> >
> >
> > myfile is any file with a single unique key field.  key1 is that key
> > field.  data1 is any other field in the file.  Actually, it doesn't HAVE
> > to be that way; you can use any two fields.  But if you don't use a
> > unique field you may end up with a REALLY big result set.
> >
> > The first select will return only data1 in the result set, while the
> > second will return both key1 and data1.  I'm one behind on DB2 group
> > PTFs, but this is a pretty big one and it just bit me badly.
> >
> > Please note that this is a completely stripped down query and I realize
> > that it effectively does "select key1, data1 from myfile".  The actual
> > query is much more involved.  But this reproduces the problem in the
> > fewest steps.
> >
> > Now I have to start looking for USING in all my queries.
> >
> > Joe