Views are broken after dumping and loading database with SCRIPT/RUNSCRIPT

[noahmoss]

Hi, I think I've found a bug in the SCRIPT command which breaks views under certain conditions.

On an empty H2 database, issue these commands:

CREATE TABLE bug_repro (id int);
CREATE VIEW v_bug_repro AS SELECT id FROM bug_repro;
ALTER TABLE bug_repro ADD test varchar(255);

Then run SCRIPT TO 'bug-repro-dump.sql';

Here is the contents of the script file:

-- H2 2.1.214; 
;              
CREATE USER IF NOT EXISTS "" SALT '' HASH '' ADMIN;            
CREATE FORCE VIEW "PUBLIC"."V_BUG_REPRO"("ID") AS
SELECT
    "ID"
FROM "PUBLIC"."BUG_REPRO";   
CREATE CACHED TABLE "PUBLIC"."BUG_REPRO"(
    "ID" INTEGER,
    "TEST" CHARACTER VARYING(255)
);               
-- 0 +/- SELECT COUNT(*) FROM PUBLIC.BUG_REPRO;

On a second empty H2 database run RUNSCRIPT 'bug-repro-dump.sql';

Now if you try to read the view on the second database, you get an error:

sql> SELECT * FROM v_bug_repro;
Error: org.h2.jdbc.JdbcSQLSyntaxErrorException: View "PUBLIC.V_BUG_REPRO" is invalid: "Table ""BUG_REPRO"" not found (this database is empty) [42104-214]"; SQL statement:

Unless you recompile the view first:

sql> ALTER VIEW v_bug_repro RECOMPILE;
(Update count: 0, 3 ms)
sql> SELECT * FROM v_bug_repro;
ID
(0 rows, 3 ms)

Essentially, updating the table after creating the view causes the CREATE TABLE command to be moved below the CREATE FORCE VIEW command in the script file, breaking the view when the script is run but not reporting any errors until the view is queried.

I've tested the equivalent behavior on Postgres using pg_dump and it doesn't have the same issue, so I'm assuming this is a bug in H2 and not intentional behavior.