這是一個簡單的測驗資料庫架構。真的沒有什么特別之處。我在 Oracle 兼容模式下使用H2版本1.4.200。
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)
);
這是一個作業正常的視圖定義。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
;
這是 H2 阻塞的視圖定義，并顯示以下錯誤訊息：
引起：org.h2.jdbc.JdbcSQLSyntaxErrorException：未找到列“SD.ID”
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
;
唯一的區別是連接的型別（左外部與內部），但我看不出為什么這會對 SD.ID 列可見性產生影響。
對我來說，這看起來像是 H2 中的一個缺陷，但在我提出 H2 專案的問題之前，我想確保我沒有遺漏一些明顯的東西或做一些愚蠢的事情。
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
uj5u.com熱心網友回復：
一個有效的 Oracle 視圖/查詢必須在被聯接的表的名稱/別名之后具有每個聯接謂詞。
通過對ON子句重新排序，查詢可以采用以下形式：
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
uj5u.com熱心網友回復：
該問題已被 H2 開發人員確認為缺陷 [1]，并已通過此 PR [2] 解決
[1] https://github.com/h2database/h2database/issues/3311
[2] https://github.com/h2database/h2database/pull/3312
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/403150.html
標籤：