Hello,
It seems that the join conditions are not at the right place:
from
STUFF S
inner join STUFF_DETAILS SD on S.ID <http://s.id/> = SD.ID
<http://sd.id/>
inner join STUFF_MORE_DETAILS SMD on SD.ID <http://sd.id/> = SMD.ID
<http://smd.id/>
Regards,
Guillaume
Le mer. 29 déc. 2021, 11:50, Oleg Kalnichevski <oleg.kalnichev...@gmail.com>
a écrit :
> 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 h2-database+unsubscr...@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
> <https://groups.google.com/d/msgid/h2-database/d6fff525-b6b9-4c58-98ea-879f0c83513dn%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/CAHLf0iC8WG-b%3DVLhi0znkToG3ZW%3D3RzAT%3D9%3DKP82Xxis32sRTg%40mail.gmail.com.
