Bug #18365 Rules on Using() are making life too tough!
Submitted: 20 Mar 2006 20:09 Modified: 4 Oct 2008 19:41
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:V5 OS:Any (n/a)
Assigned to: CPU Architecture:Any

[20 Mar 2006 20:09] Dave Pullin
Description:
Rules in V5 are making it too hard to write some complex queries because you have combined two capabilities into a single function -- capabilities that are not separately accessible.
In: 

 select * from x join y USING(Z)

USING(Z) is both an abbreviation for "x.z=y.z" and an instruction the coalesce columns x.z, y.z.

If there is an additional condition, you can't use USING, you must use ON ...

select * from x join y on x.Z=y.Z AND STATUS='1'

But now the columns x.Z,y.Z are not coalesced, which wasn't a problem (for me)  except that the real life statement is inside another select ...
 select * from (
      select * from x join y on x.Z=y.Z AND STATUS='1'
 ) as subtable

and V5 now requires that the column names be unique, so it fails with ON but not with USING. So I can't use either.

Of course, I could replace '*' with an explicit list of all the (unique) columns, and change the code everytime the list changes, but that's not only tedious, but obviates that value of the '*' capability.

How to repeat:
 select * from (
      select * from x join y USING(Z) AND STATUS='1'
 ) as subtable

Suggested fix:
Allow USING() (with its implied coalecse) be used, AS A PART, of more complex join conditions

For example:

 select * from x join y USING(Z) and STATUS=1

or
 select * from x join y ON STATUS=1 and USING(Z) 

This enables the implied coalesce function to be used when wanted.
[24 Mar 2006 12:36] Hartmut Holzgraefe
Sort of makes sense but would be non-standard behavior.
Verified for now as a feature request but not likely to happen ...
[4 Oct 2008 19:41] Konstantin Osipov
We do not plan to change USING semantics to non-standard.
Thank you for your interest in MySQL.