Folks,
Please consider the following schema. There is nothing really special about
it.
```
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)
);
```
Based on the schema above the following view can be successfully created
```
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
;
```
A very similar view definition, however, fails
```
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
;
```
Exception: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "SD.ID" not found
I am perfectly aware that nested joins are ugly and the view definitions
could be rewritten to avoid the problem.
Having said that it seems wrong that H2 behaves differently depending on
the join type (inner vs left outer) in this very particular case.
It looks like a defect to me.
Oleg
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/d6fff525-b6b9-4c58-98ea-879f0c83513dn%40googlegroups.com.
