“Object not found” error when using multiple table expressions in WITH…AS inside of CREATE VIEW

I am trying to create a view based on complex query in HSQLDB (version 2.5.1).

The query looks like this (simplified for clarity), also includes DDL for the tables:

DROP VIEW TEST_VIEW IF EXISTS;

DROP TABLE TEST_1 IF EXISTS;
CREATE TABLE TEST_1 (
    contentid VARCHAR(10),
    contenttype VARCHAR(10),
    downloaddate TIMESTAMP
);

DROP TABLE TEST_2 IF EXISTS;
CREATE TABLE TEST_2 (
    dbid INTEGER,
    contentid VARCHAR(10),
    version VARCHAR(10)
);

CREATE VIEW TEST_VIEW AS
WITH a AS (
    SELECT CONTENTID, count(*) AS amount
    FROM TEST_2
    GROUP BY CONTENTID
),
    b AS (
        SELECT CONTENTID, amount
        FROM a
    )
SELECT b.CONTENTID, b.amount, i.DOWNLOADDATE
FROM b /* error here */
    JOIN TEST_1 i ON i.CONTENTID = b.CONTENTID
ORDER BY b.CONTENTID;

However, it fails with the following error:

[42501][-5501] user lacks privilege or object not found: JOIN in statement [CREATE VIEW TEST_VIEW AS……

The same query runs fine when used as a SELECT (without CREATE VIEW...AS).

Also, the view is created successfully if there is only one table expression in WITH...AS statement, like below:

CREATE VIEW TEST_VIEW AS
WITH a AS (
    SELECT CONTENTID, count(*) AS amount
    FROM TEST_2
    GROUP BY CONTENTID
)
SELECT a.CONTENTID, a.amount, i.DOWNLOADDATE
FROM a
    JOIN TEST_1 i ON i.CONTENTID = a.CONTENTID
ORDER BY a.CONTENTID;

It looks like in the first statement the DB engine tries to parse “JOIN” as a table alias for table “b”.

Is there a syntax error I have not noticed, or does HSQLDB not support multiple table expressions in WITH...AS inside of CREATE VIEW?

Edit: Updated example query to include table DDL for completeness.

Answer

Thanks to a suggestion by @fredt, I have confirmed that the issue is with trying to use this query in IntelliJ IDEA (2020.1). The query worked fine and the view was created successfully in the same DB when another tool was used (DbVisualizer in my case). Furthermore, after having created the view in DB, IntelliJ IDEA throws an exception on the same word “JOIN” when trying to connect to this DB – the error is similar to this: The specified database user/password combination is rejected: org.hsqldb.HsqlException: unexpected token: NOT. Similarly to a comment in the above question, I have recovered from the error by manually editing the .script file.

There are at least 2 possible options to resolve the issue:

1st solution: Refactor SQL query to only have one table in WITH clause. In my case I just moved the first table to a select expression in FROM clause, like below:

CREATE VIEW TEST_VIEW AS
WITH b AS (
        SELECT CONTENTID, amount
        FROM (
            SELECT CONTENTID, count(*) AS amount
            FROM TEST_2
            GROUP BY CONTENTID
        )
    )
SELECT b.CONTENTID, b.amount, i.DOWNLOADDATE
FROM b
    JOIN TEST_1 i ON i.CONTENTID = b.CONTENTID
ORDER BY b.CONTENTID;

2nd solution: Use a different tool to work with the DB, or have the issue fixed in IDEA.