#1506 "natural left outer join" bug

[Carrel]

When I input following SQL statements:

Create table r (A Int, B Int, primary key (A, B));
Create table s (B Int, C Int, primary key (B, C));
Create table t (B Int, D Int, primary key (B, D));
Insert into r values (1, 2);
Insert into s values (3, 4);
Insert into t values (5, 6);

select * from (r natural left outer join s) natural left outer join t

It's supposed to return
| A | B | C | D |
| :--: | :--: | :--: | :--: |
| 1 | 2 | | |

But HSQLDB returns:

[Carrel]

The HyperSQL version is 2.4.0

[Fred Toussi]

The column order returned by HSQLDB is correct for the SELECT statement.

The NATURAL JOIN S is joined on column B. This column appears first in the table returned by the subquery following SQL Standard.

[Carrel]

The column order is OK, the key problem is that in HSQLDB, the column B is null.
First, r and s process NATURE LEFT OUTER JOIN. and return A, B, C: 1, 2, null.
Then, above result process NATURE LEFT OUTER JOIN with t and should return A, B, C, D: 1, 2, null, null following SQL Standard.

[Fred Toussi]

This will be fixed later. You can use one of the following alternatives

select * from  r natural left outer join s natural left outer join t
select * from (select * from r natural left outer join s) natural left outer join t

[Fred Toussi]

Fixed and committed to SVN.