SpringBoot Recursive queries with h2 db is not working with spring boot 3.4. It was working with spring boot 3.2.3

Hello,

We have an application that exposes some APIs with spring boot 3.2.3. For DB layer testing, we used h2 db (version 2.3.232 (latest)) . In one of the scenario code is using Recursive queries with hibernate like below:

  @Query(value = """
   WITH ancestors (id) AS 
      (
        SELECT id as id FROM table WHERE id= :id
          UNION ALL
        SELECT table.id id
        FROM ancestors ancestor INNER JOIN table folder ON ancestor.id= table.id
      )
      SELECT
         id FROM ancestors
  """, nativeQuery = true)
  fun getParent( @Param("id") id: Int): List<Int>

This query was working fine in test cases via h2 db and spring boot 3.2.3

But when we upgraded to spring boot 3.4 then getting following error: 2025-01-21T11:01:36.614+01:00 ERROR 10684 --- [.1-28041-exec-8] o.h.e.j.s.SqlExceptionHelper : Table "ANCESTORS" not found; SQL statement:

Only the spring boot version is changed to 3.4.0. Please let us know how to solve this issue?

fyi: The same query (Recursive queries) is working fine with Oracle DB in production mode but it is failing in test cases with h2 DB.

Comment From: wilkinsona

Only the spring boot version is changed to 3.4.0.

Spring Boot itself isn't involved in turning JPA queries in database-specific SQL. It's Hibernate that does that. Changing the Spring Boot version from 3.2.3 to 3.4.0 will have upgraded Hibernate from 6.4.4 to 6.6.2. I suspect that's the cause of the problem.

Please let us know how to solve this issue?

I'd recommend spending some time creating a minimal example the reproduces the problem. You can then hopefully narrow down the cause by overriding the version of Hibernate and perhaps of H2 as well. I would also look at whether you're using H2 in Oracle compatibility mode and the dialect that Hibernate's using as these setting will influence its SQL generation.

Having done this, you may have learned how to modify your application to accommodate some changes in Hibernate. Alternatively, you may have found a bug in Hibernate in which case I'd recommend opening a Hibernate issue with a minimal sample that demonstrates the problem. If the investigation doesn't get you anywhere, I'd recommend asking a question on Stack Overflow, sharing your minimal example, to get some further guidance from the community.

Comment From: Tasades

I downgraded h2 to 2.2.224 because of this: https://github.com/h2database/h2database/issues/4100 With h2 at version 2.2.224 it still works.