"Object not found" error when using multiple table expressions in WITH...AS inside of CREATE...

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):

CREATE VIEW REPORT_LINKED_IDS AS
WITH a AS (
SELECT CONTENTID, count(*) AS amount
FROM CONTENT_VERSION
GROUP BY CONTENTID
),
b AS (
SELECT CONTENTID, amount
FROM a
)
SELECT b.CONTENTID, b.amount, i.DOWNLOADDATE
FROM b /* error here */
JOIN CONTENT_ITEM 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 REPORT_LINKED_IDS AS WITH a 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 REPORT_LINKED_IDS AS
WITH b AS (
SELECT CONTENTID, count(*) AS amount
FROM CONTENT_VERSION
GROUP BY CONTENTID
)
SELECT b.CONTENTID, b.amount, i.DOWNLOADDATE
FROM b
JOIN CONTENT_ITEM i ON i.CONTENTID = b.CONTENTID
ORDER BY b.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?

Continue reading...