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