sql - 使用 HSQLDB 生成基于 CTE 的序列

Question

我正在使用递归公用表表达式来获取一批序列号。以下查询适用于 Postgres、SQL Server 和 H2(减去 VALUES 部分)。

WITH RECURSIVE t(n, level_num) AS (
    SELECT next value for seq_parent_id as n,
           1 as level_num
      FROM (VALUES(0))

    UNION ALL

    SELECT next value for seq_parent_id as n,
           level_num + 1 as level_num
      FROM t
     WHERE level_num < ?)
SELECT n FROM t

但是使用 HSQLDB 2.4.0 我得到以下异常

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: T
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    ... 
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: T
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserDQL.readTableName(Unknown Source)
    at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
    at org.hsqldb.ParserDQL.XreadFromClause(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.XreadSetOperation(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSubqueryTableBody(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableNamedSubqueryBody(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.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 37 more

这个特定的用例也可以通过 UNNEST 的组合来解决。和 SEQUENCE_ARRAY但我想避免引入特定于 HSQLDB 的代码路径。

Answer 1

我将从不使用序列和硬编码限制的最简单形式的递归查询开始，然后逐渐向其中添加额外的位。

基于文档中的示例 With Clause and Recursive Queries语法应如下所示:

WITH RECURSIVE
t(level_num)
AS
(
    VALUES(1)

    UNION ALL

    SELECT
        level_num + 1
    FROM t
    WHERE level_num < 10
)
SELECT level_num 
FROM t
;

顺便说一下，文档说:

HyperSQL limits recursion to 265 rounds. If this is exceeded, an error is raised.

我会尝试最简单的查询，如上面的查询，确保它有效，然后尝试使用，例如 1000而不是 10并查看它返回什么错误。如果它与您最初遇到的错误相同，那么您找到了原因。

旁注:我会使用一个永久的数字表，而不是为这种任务递归地生成它们。我们的系统中有一个包含 10 万个数字的表格。它很简单，可以在任何 DBMS 中工作。填充一次并根据需要使用。我知道在 SQL Server 中递归查询的速度要慢得多(在这种任务中)，但我不了解 HyperSQL。此外，递归深度限制为仅 265 是相当苛刻的。最有可能的是，由于递归深度的限制如此之低，因此不可能检测到性能上的任何差异。但是，同样，265 个数字足以满足您的目的吗？