USING throws error when joining more than two tables

This might be related to #2105? I can’t tell.

Given the following three tables:

create table ta (a integer primary key);
create table tb (b integer primary key);
create table tc (
    a integer,
    b integer,
    primary key (a, b),
    foreign key (a) references ta,
    foreign key (b) references tb
);

(i.e., a typical associative table structure) the following query that joins all three tables with using throws an error:

select * from ta join tc using (a) join tb using (b);
-- Column "B" not found; SQL statement:
-- select * from ta join tc using (a) join tb using (b) [42122-214] 42S22/42122

In contrast, all of the following queries produce the expected results:

select * from ta join tc on (ta.a = tc.a) join tb on (tb.b = tc.b);
select * from ta join tc join tb on (tb.b = tc.b) on (ta.a = tc.a);
select * from ta join tc join tb using (b) using (a);

I initially thought the last two “postfix-style” queries were nonsensical and only tried them on a hunch that something might be inverted in H2’s grammar. Much to my surprise I found they also work in PostgreSQL and MariaDB! I’m not sure whether that’s actually what the SQL standard specifies, but it certainly wouldn’t be the first time the standard was ambiguous 🙄. Over 30 years of SQL experience and I’m still finding unexpected things 😄.

Furthermore:

select * from ta join tc using (a) join tb on (tb.b = tc.b); -- works

select * from ta join tc on (ta.a = tc.a) join tb using (b); -- error
-- Column "B" not found; SQL statement:
-- select * from ta join tc on (ta.a = tc.a) join tb using (b) [42122-214] 42S22/42122

So the issue with using only seems to kick in on the second or subsequent join in a chain, whereas on appears to have no such problem. My guess is that something is slightly broken somewhere in the depths of the join expression parser.

Start with the reported SQL reproductions, especially the chained JOIN ... USING queries, and trace H2's join expression parser. Compare them with the working ON and postfix-style queries; done means the failing USING joins execute successfully without the Column "B" not found error.