Script stops working on HSQLDB 1.9.0-rc6

Question

DROP VIEW V1 IF EXISTS;
DROP TABLE T1 IF EXISTS;

CREATE TABLE T1 (id INT, name VARCHAR(32), age int);
CREATE VIEW V1 AS (SELECT * FROM T1 WHERE age < 21);

---

I have no problem to execute the SQL statement above in one JDBC statement or on the SWING Manager from HSQLDB. Now it stops working on 1.9.0-rc6. This is error message I got - "user lacks privilege or object not found: T1 / Error Code: -5501 / State: 42501"

Does anyone know what have changed in 1.9.0-XX which made it not working?

Thanks

Answer 1

In HSQLDB 1.9, it has changed to compile entire script instead of interpreting it line by line, so CREATE VIEW v1 will throw an error of not seeing T1 available.

The script has to be broken into 2 executions - first create table, then create view.

---

DROP TABLE T1 IF EXISTS;
CREATE TABLE T1 (id INT, name varchar(32));
INSERT INTO T1 VALUES(0, 'Eric');
INSERT INTO T1 VALUES(0, 'Tom');

The sql above will not work in one single JDBC statement anymore in 1.9.0-rc.