Hi, Currently, invalid views stay invalid until you recompile them using
alter view b recompile; Is this not how other databases work? If not, patches are welcome to auto-recompile the view on demand. Regards, Thomas On Friday, April 6, 2012, Scott wrote: > 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]<javascript:_e({}, 'cvml', > '[email protected]');> > . > To unsubscribe from this group, send email to > [email protected] <javascript:_e({}, 'cvml', > 'h2-database%[email protected]');>. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. 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.
