A couple CREATE FORCE VIEW issues I noticed

[Scott]

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] 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] 90109/90109 (Help)

I know I can work around these issues, but it seems to me like they should not be happening.

Thanks,
-Scott

[Thomas Mueller]

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

--
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 h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.