当递归谓词使用绑定变量时，递归CTE不起作用 [英] Recursive CTE don't work when recursion predicate uses a bind variable

问题描述

在 H2 中使用递归CTE时(我知道，实验功能)，以下查询无效:

When using recursive CTE in H2 (I know, experimental feature), the following query doesn't work:

Connection con = getConnection();
System.out.println("Wrong result:");
PreparedStatement stmt = con.prepareStatement(
    "WITH recursive t(f) AS ( "+
    "    SELECT 1             "+
    "    UNION ALL            "+
    "    SELECT t.f + 1       "+
    "    FROM t               "+
    "    WHERE t.f < ?        "+
    ")                        "+
    "SELECT t.f               "+
    "FROM t                   "
);
stmt.setInt(1, 10);
ResultSet rs = stmt.executeQuery();

while (rs.next())
    System.out.println(rs.getInt(1));

产生的输出是:

1

预期结果将是:

1
2
3
4
5
6
7
8
9
10

出什么问题了?

推荐答案

这是一个已知问题

This is a known issue (see here). H2 has problems when recursive queries contain bind variables. The following query doesn't use bind values and works as expected:

System.out.println("Correct result:");
rs = con.createStatement().executeQuery(
    "WITH recursive t(f) AS ( "+
    "    SELECT 1             "+
    "    UNION ALL            "+
    "    SELECT t.f + 1       "+
    "    FROM t               "+
    "    WHERE t.f < 10       "+
    ")                        "+
    "SELECT t.f               "+
    "FROM t                   "
);

while (rs.next())
    System.out.println(rs.getInt(1));

这篇关于当递归谓词使用绑定变量时，递归CTE不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！