I just came across a couple (related?) issues with the forced creation of views. I was using previously generated database content saved using the "SCRIPT" command in a unit test which started failing with some recent schema changes added modifying views. The error indicated a table was not found, even though the table exists.
The problem appears to be related to forced creation of views when the underlying tables or views do not yet exist or themselves are not yet complete. I worked out some small test cases for each of 2 different cases I noticed. The simpler one first that only indicates a missing column: > -- works > drop view c if exists; > drop view b if exists; > drop table T1 if exists; > drop table T2 if exists; > create force view c as select value from b,t1 where b.id = t1.id; > CREATE TABLE PUBLIC.T1 (id serial not null primary key, value varchar); > CREATE TABLE PUBLIC.T2 (id serial not null primary key, value varchar); > create force view b as select id from T2 WHERE value = '123'; > alter table T1 add column deleted boolean default 'f'; > create or replace view c as select value from b,T1 where b.id = T1.id and > deleted != 't'; > > -- doesn't work > drop view c if exists; > drop view b if exists; > drop table T1 if exists; > drop table T2 if exists; > create force view b as select id from T2 WHERE value = '123'; > create force view c as select value from b,t1 where b.id = t1.id; > CREATE TABLE PUBLIC.T1 (id serial not null primary key, value varchar); > CREATE TABLE PUBLIC.T2 (id serial not null primary key, value varchar); > alter table T1 add column deleted boolean default 'f'; > create or replace view c as select value from b,T1 where b.id = T1.id and > deleted != 't'; > Produces the error: Column "B.ID" not found; SQL statement: create or replace view c as select value from b,T1 where b.id = T1.id and deleted != 't' [42122-164]<http://localhost:8083/query.do?jsessionid=8ba1ff54f014060966b338c710528868#> 42S22/42122 And a more complicated one that produces the interesting table not found message: > -- works > drop view configuration_view if exists; > drop view last_view if exists; > drop view data_view if exists; > drop table data if exists; > drop table configuration if exists; > > CREATE TABLE CONFIGURATION (id bigint not null primary key); > CREATE TABLE DATA (id bigint not null primary key, configuration_id > bigint, status int, data varchar); > > CREATE FORCE VIEW PUBLIC.CONFIGURATION_VIEW AS > SELECT C.ID, S.DATA FROM CONFIGURATION C LEFT OUTER JOIN PUBLIC.LAST_VIEW > LS ON LS.CONFIGURATION_ID = C.ID LEFT OUTER JOIN data_view S ON S.ID = > LS.DATA_ID; > > CREATE FORCE VIEW PUBLIC.LAST_VIEW(DATA_ID, CONFIGURATION_ID) AS > SELECT MAX(ID) AS DATA_ID, CONFIGURATION_ID > FROM PUBLIC.DATA > GROUP BY CONFIGURATION_ID; > > CREATE FORCE VIEW DATA_VIEW AS SELECT * FROM data; > > alter table data add column deleted boolean default 'f'; > CREATE OR REPLACE VIEW DATA_VIEW AS SELECT * FROM data; > > DROP VIEW CONFIGURATION_VIEW; > > CREATE VIEW PUBLIC.CONFIGURATION_VIEW AS > SELECT C.ID, S.DATA FROM CONFIGURATION C LEFT OUTER JOIN PUBLIC.LAST_VIEW > LS ON LS.CONFIGURATION_ID = C.ID LEFT OUTER JOIN DATA_VIEW S ON S.ID = > LS.DATA_ID WHERE deleted != 't'; > > -- doesn't work > drop view configuration_view if exists; > drop view last_view if exists; > drop view data_view if exists; > drop table data if exists; > drop table configuration if exists; > > CREATE FORCE VIEW PUBLIC.CONFIGURATION_VIEW AS > SELECT C.ID, S.DATA FROM CONFIGURATION C LEFT OUTER JOIN PUBLIC.LAST_VIEW > LS ON LS.CONFIGURATION_ID = C.ID LEFT OUTER JOIN data_view S ON S.ID = > LS.DATA_ID; > > CREATE FORCE VIEW PUBLIC.LAST_VIEW(DATA_ID, CONFIGURATION_ID) AS > SELECT MAX(ID) AS DATA_ID, CONFIGURATION_ID > FROM PUBLIC.DATA > GROUP BY CONFIGURATION_ID; > > CREATE FORCE VIEW DATA_VIEW AS SELECT * FROM data; > > CREATE TABLE CONFIGURATION (id bigint not null primary key); > CREATE TABLE DATA (id bigint not null primary key, configuration_id > bigint, status int, data varchar); > > alter table data add column deleted boolean default 'f'; > CREATE OR REPLACE VIEW DATA_VIEW AS SELECT * FROM data; > > DROP VIEW CONFIGURATION_VIEW; > > CREATE VIEW PUBLIC.CONFIGURATION_VIEW AS > SELECT C.ID, S.DATA FROM CONFIGURATION C LEFT OUTER JOIN PUBLIC.LAST_VIEW > LS ON LS.CONFIGURATION_ID = C.ID LEFT OUTER JOIN DATA_VIEW S ON S.ID = > LS.DATA_ID WHERE deleted != 't'; > The error for this one: View "PUBLIC.LAST_VIEW" is invalid: "Table ""DATA"" not found [42102-164]"; SQL statement: CREATE VIEW PUBLIC.CONFIGURATION_VIEW AS SELECT C.ID, S.DATA FROM CONFIGURATION C LEFT OUTER JOIN PUBLIC.LAST_VIEW LS ON LS.CONFIGURATION_ID = C.ID LEFT OUTER JOIN DATA_VIEW S ON S.ID = LS.DATA_ID WHERE deleted != 't' [90109-164]<http://localhost:8083/query.do?jsessionid=8ba1ff54f014060966b338c710528868#> 90109/90109 (Help)<http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c90109> I know I can work around these issues, but it seems to me like they should not be happening. Thanks, -Scott -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/FnGNPrqb6sYJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
