Bug #18366 USING() should mean <=>, rather than =
Submitted: 20 Mar 2006 20:43 Modified: 24 Mar 2006 12:26
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:V5 OS:n/a
Assigned to: CPU Architecture:Any

[20 Mar 2006 20:43] Dave Pullin
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.
[24 Mar 2006 12:26] Hartmut Holzgraefe
USING (foo) is defined as being equivalent to t1.foo=t2.foo by the standard, 
<=> behavior would violate the standard, so it won't happen