Incorrect row numbers in recursive queries

[marschall]

We are using the following recursive CTE to fetch multiple sequence values in one database round trip

WITH RECURSIVE t(n, level_num) AS (
  SELECT NEXT VALUE FOR demo_squence AS n, 1 AS level_num
    UNION ALL
  SELECT NEXT VALUE FOR demo_squence AS n, level_num + 1 AS level_num
    FROM t
   WHERE level_num < ?)
SELECT n
  FROM t

With 1.4.200 this used to generate [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] but starting with 2.0.202 it generates [1, 2, 2, 2, 2, 2, 2, 2, 2, 2]. It is still broken with 2.1.212.

Full code example

public class RecursiveCteTest {

  private static final String CREATE_SEQUENCE = "CREATE SEQUENCE demo_squence "
                                                + " CACHE 10 "
                                                + " NO CYCLE ";
  
  private static final String SELECT_SEQUENCE_VALUES = 
        " WITH RECURSIVE t(n, level_num) AS ( "
      + "   SELECT NEXT VALUE FOR demo_squence AS n, 1 AS level_num "
      + "     UNION ALL "
      + "   SELECT NEXT VALUE FOR demo_squence AS n, level_num + 1 AS level_num "
      + "     FROM t "
      + "    WHERE level_num < ?) "
      + " SELECT n"
      + "   FROM t ";

  public static void main(String[] args) throws SQLException {
    try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:")) {
      try (Statement statement = connection.createStatement()) {
        statement.execute(CREATE_SEQUENCE);
      }
      List<Integer> sequenceValues = new ArrayList<>(10); 
      try (PreparedStatement selectSequenceValues = connection.prepareStatement(SELECT_SEQUENCE_VALUES)) {
        selectSequenceValues.setInt(1, 10);
        try (ResultSet resultSet = selectSequenceValues.executeQuery()) {
          while (resultSet.next()) {
            sequenceValues.add(resultSet.getInt(1));
          }
        }
      }
      System.out.println(sequenceValues);
    }
  }

}

[katzyn]

Recursive CTEs are experimental in H2 and may have any issues.

I think this query worked in H2 only due to buggy implementation of sequence value expressions in old versions of H2. New versions properly return the same value within a processed row as required by the SQL Standard in Regular and some other compatibility modes. H2 incorrectly determines all additional rows from recursive query as the same row, that's why you see only two different values, first one is produced by a first part of union, and all remaining rows are produced by its second part. Combination of two different unrelated bugs led to correct results, but when first bug was resolved the second bug was revealed.

Actually this query is overcomplicated, you can use something like

SELECT NEXT VALUE FOR sequenceName FROM SYSTEM_RANGE(1, 10);

But if you need to use standard syntax only, you can move sequence value expression outside of recursive part as a workaround:

WITH RECURSIVE T(N) AS (
  SELECT 1
  UNION ALL
  SELECT N + 1 FROM T WHERE N < 10
)
SELECT NEXT VALUE FOR sequenceName FROM T;

[marschall]

Thank you for pointing out the alternatives. That solves our issue.

[manticore-projects]

@marschall

Please retry with latest GIT 4497462. It should be fixed now. Please close when confirmed.

[katzyn]

This minor bug is still here.

[marschall]

I'm trying to retry. I'll first have to fix an issue that Hibernate does not run with -SNAPSHOT versions of H2.

[marschall]

@manticore-projects I built H2 from source with a modified Hibernate version, as @katzyn said, 4497462 does not fix the issue and the issue is still present