H2 seems to misinterpret a valid join clause-CodePudding

Here is a simple test database schema. There is really nothing special about it. I am using H2 version 1.4.200 in Oracle compatibility mode.

create table STUFF (
    ID number(19) generated by default as identity (start with 1 increment by 1),
    NAME varchar2(128) not null,
    constraint PK_STUFF primary key (ID),
    constraint BK_STUFF unique (NAME)
);

create table STUFF_DETAILS (
    ID number(19) generated by default as identity (start with 1 increment by 1),
    BLAH varchar2(128) not null,
    constraint PK_STUFF_DETAILS primary key (ID)
);

create table STUFF_MORE_DETAILS (
    ID number(19) generated by default as identity (start with 1 increment by 1),
    BLAH_BLAH varchar2(128) not null,
    constraint PK_STUFF_MORE_DETAILS primary key (ID)
);

Here's a view definition that works fine. No objection from H2.

create or replace view V_STUFF1
(
    ID,
    NAME,
    BLAH,
    BLAH_BLAH
)
as select
    S.ID,
    S.NAME,
    SD.BLAH,
    SMD.BLAH_BLAH
from
     STUFF S
     inner join STUFF_DETAILS SD
          inner join STUFF_MORE_DETAILS SMD
               on SD.ID = SMD.ID
          on S.ID = SD.ID
;

Here's a view definition that H2 chokes on with the following error message:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "SD.ID" not found

create or replace view V_STUFF2
(
    ID,
    NAME,
    BLAH,
    BLAH_BLAH
)
as select
    S.ID,
    S.NAME,
    SD.BLAH,
    SMD.BLAH_BLAH
from
     STUFF S
     inner join STUFF_DETAILS SD
          left outer join STUFF_MORE_DETAILS SMD
               on SD.ID = SMD.ID
          on S.ID = SD.ID
;

The only difference is the type of the join (left outer vs inner) but I fail to see a reason why this should make a difference with regards to SD.ID column visibility.

To me this looks like a defect in H2 but before I raise an issue with H2 project I want to make sure I am not missing something obvious or doing something stupid.

PS: I am aware I can rewrite the view definition and make H2 accept it but ideally I would like to keep SQL code as close to the original as possible. It is a migration project.

PPS: Oracle (and DB2) have no trouble with both view definitions, so the issue appears H2 specific

CodePudding user response：

A valid Oracle view/query must have each join predicate following the name/alias of the table that is being joined.

By reordering the ON clauses the query could take the form:

create or replace view V_STUFF2
(
    ID,
    NAME,
    BLAH,
    BLAH_BLAH
)
as select
    S.ID,
    S.NAME,
    SD.BLAH,
    SMD.BLAH_BLAH
from STUFF S
inner join STUFF_DETAILS SD on S.ID = SD.ID
left outer join STUFF_MORE_DETAILS SMD on SD.ID = SMD.ID

CodePudding user response：

The issue has been acknowledged as a defect [1] by H2 developers and resolved with this PR [2]

[1] https://github.com/h2database/h2database/issues/3311

[2] https://github.com/h2database/h2database/pull/3312