Description:
The intuitive interpretation of
select * from temp_1 join temp_2 using(x,y);
is that it selects records from temp_1 and temp_2 that having matching values of x and y. But it does not so, if the records match but x or y is null.
using(x,y) is implemented as an abbreviation for
temp_1.x=temp_2.x and temp_1.y=temp_2.y
Wouldn't it be more intuitive and useful if it were implemented as
temp_1.x<=>temp_2.x and temp_1.y<=>temp_2.y
(That is where 'null' matches 'null'),
This isn't that big a deal, since the long-hand "<=>" can be used EXCEPT that the other rules imposed in V5 that make using() have that side effect of coalescing the columns and then REQUIRING the coalecsing by making non-unique column names an error, as described in bug 18365.
How to repeat:
drop table if exists temp_1,temp_2;
create table temp_1 (x int, y int, key(x,y) ) select 1 as x, null y;
create table temp_2 (x int, y int, key(x,y) ) select 1 as x, null y;
/* yields no rows: */
select * from temp_1 join temp_2 using(x,y);
/* yields 1 row */
select * from temp_1 join temp_2 on temp_1.x<=>temp_2.x and temp_1.y<=>temp_2.y;
Suggested fix:
change using(X) to be true when both X's are null.
