MySQL Bugs: #15357: Natural join with nested cross-join produces incorrect columns

Bug #15357	Natural join with nested cross-join produces incorrect columns
Submitted:	30 Nov 2005 16:45	Modified:	7 Dec 2005 16:17
Reporter:	Timour Katchaounov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.17	OS:
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When the operand of a natural join is a cross-join, the
resulting columns are not the correct ones. In addition,
when the join operands are swapped, the result contains
different columns, while it should contain the same
columns, but in different order.

In addition, the extended semantics of the nested ","
operator in not clear. At the top-level of a FROM clause,
"," has lower priority than JOIN (as in ANSI), but this
doesn't seem natural for nested joins.

How to repeat:
drop table t1,t2,t3,t4,t5;
create table t1 (c int, b int);
create table t2 (a int, b int);
create table t3 (b int, c int);
create table t4 (y int, c int);
create table t5 (y int, z int);
create table t6 (a int, c int);

insert into t1 values (10,1);
insert into t1 values (3 ,1);
insert into t1 values (3 ,2);
insert into t2 values (2, 1);
insert into t3 values (1, 3);
insert into t3 values (1,10);
insert into t4 values (11,3);
insert into t4 values (2, 3);
insert into t5 values (11,4);

select * from t5 natural join ((t1 natural join t2), (t3 natural join t4));
select * from  ((t1 natural join t2),  (t3 natural join t4)) natural join t5;
select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4));
select * from  ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5;

Suggested fix:
We have to treat ',' differently if it is inside a nested join.

In addition to the above queries, the following query should
return a name resolution error, but it works:

create table t6 (a int, c int);
insert into t6 values (2, 3);

select * from t6 natural join ((t1 natural join t2),  (t3 natural join t4));

while this equivalent query produces an error as it should:

select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;

This query also must return error with duplicate column 'c',
but it doesn't:

select * from (t1 natural join t2)
              natural join
              (t3 join (t4 natural join t5) on (b < z));

This bug is duplicate of BUG#15229.

Notice that my initial guess that the problem is due to
different handling of ',' inside nested joins was wrong.
The problem with this bug was the same as that of
BUG#15229.