Почему не работает рекурсивный sql-запрос?

Question

[Николай Ступак]

Для тестирования java-приложения используется база данных hsql. Хочу выполнить следующий sql-запрос:

SELECT 
    T0E0.id AS ID_0, 
    T0E0.name AS NAME_1, 
    T1E0.tenantId AS TENANTID_2, 
    T1E0.groupId AS GROUPID_3, 
    T1E0.userId AS USERID_4
FROM tstsfSection T0E0  
LEFT OUTER JOIN sscAttributes T1E0 ON T1E0.id =T0E0.id 
WHERE 
    (WITH RECURSIVE _childsCTE(id, root) AS ( 
            SELECT T0E2.id AS id_0, T0E2.id AS id_1 FROM tstsfSection T0E2  WHERE T0E2.parent =T0E0.id 
            UNION  
            SELECT T0E2.id AS id_0, T1E2.root AS root_1 FROM tstsfSection T0E2  JOIN _childsCTE T1E2 ON T0E2.parent =T1E2.id
    ) SELECT count(T0E1.id) AS aggr_0_0 FROM _childsCTE T0E1  ) > 0
ORDER BY T0E0.name

В ответ получаю ошибку:

Caused by: org.hsqldb.HsqlException: unexpected token: WITH
	at org.hsqldb.error.Error.parseError(Unknown Source)
	at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
	at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
	at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
	at org.hsqldb.ParserDQL.XreadRowElementList(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
	at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
	at org.hsqldb.ParserDQL.XreadRowOrCommonValueExpression(Unknown Source)
	at org.hsqldb.ParserDQL.XreadRowValuePredicand(Unknown Source)
	at org.hsqldb.ParserDQL.XreadPredicateRightPart(Unknown Source)
	at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
	at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
	at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
	at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
	at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
	at org.hsqldb.ParserDQL.readWhereGroupHaving(Unknown Source)
	at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
	at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
	at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
	at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
	at org.hsqldb.ParserCommand.compilePart(Unknown Source)
	at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
	at org.hsqldb.Session.compileStatement(Unknown Source)
	at org.hsqldb.StatementManager.compile(Unknown Source)
	at org.hsqldb.Session.execute(Unknown Source)
	... 70 more

В документации к hsql я ничего не нашел про ограничение использования рекурсивных запросов. Почему я не могу использовать его в виде подзапроса? Пробовал разные версии драйвера hsql: 2.2.4, 2.3.0, 2.3.1 и 2.3.2 - не заработало ни на одной

Answer 1

[Кирилл Таран]

Ничего не знаю про HSQLDB, но предположу, что CTE должно задаваться до SELECT

WITH RECURSIVE _childsCTE(id, root) AS (...)
SELECT 
    *
FROM tstsfSection T0E0  
LEFT OUTER JOIN sscAttributes T1E0 ON T1E0.id =T0E0.id 
WHERE

Comments

[Николай Ступак]

CTE должно задаваться до SELECT-а, который его использует. Может в HSQLDB конечно другие правила, но, например, в postgresql это запрос отрабатывает правильно