Why doesn't recursive sql query work?

Question

SQL

The hsql database is used to test the java application. I want to execute the following sql query:

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

In response I get an error:

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

In the documentation for hsql, I did not find anything about limiting the use of recursive queries. Why can't I use it as a subquery? Tried different versions of hsql driver: 2.2.4, 2.3.0, 2.3.1 and 2.3.2 - did not work on any

Answer 1

I don't know anything about HSQLDB, but I will assume that CTE should be set before SELECT

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