H2 (1.4.200) seems to misinterpret a valid join clause
89 views
Skip to first unread message
Oleg Kalnichevski
Dec 29, 2021, 5:50:53 AM12/29/21
to H2 Database
Folks,
(
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
;
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)
);
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
;
(
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
Evgenij Ryazanov
Dec 29, 2021, 9:29:35 AM12/29/21
to H2 Database
Oleg Kalnichevski
Dec 30, 2021, 8:44:42 AM12/30/21
to H2 Database
Hi Evgenij
Thank you so much for such an impressively quick turnaround and a resolution of the issue. How often do you, guys, cut GA releases? When could one expect a release with this fix?
Cheers
Oleg
Guillaume de GENTILE
Dec 30, 2021, 2:38:40 PM12/30/21
to h2-da...@googlegroups.com
Hello,
It seems that the join conditions are not at the right place:
Regards,
Guillaume
--
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 h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d6fff525-b6b9-4c58-98ea-879f0c83513dn%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages