Re: Ugly problem with USING vs ON in JOIN

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Thanks, Charles.  I don't remember seeing your post, but I don't 
remember the section in the memo to users, either.  I'm sure I read it 
but it just didn't sink in.  So it wasn't an ugly problem, just a 
backwards compatibility issue combined with a problem behind the 
keyboard <grin>.

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