Recursive Query With JPQL - Works OK for MySQL but not H2

Hibernate ORM
1

Good afternoon,

I was wondering if I should ask here about an issue with a recursive + non-recursive CTE that seems to work correctly when using MySQL as the datasource but not H2?
I didn’t want to assume it was a Hibernate issue when building the query for a specific technology (H2) vs if H2 can actually do the query.

I can try and produce a minimal working example if its appropriate here but basically I have used the “raw sql” outputted by the logs and it seems to not work in H2 but does work fine in MySQL ( just had to replace the FETCH FIRST with LIMIT 1 to get it to work for MySQL).

Its basically a hierarchical recursive query/cte, then another cte that uses the initial cte results but then orders them and limits to 1 to try get a “maximum hierarchical level” row.

A User has a parent User : ManyToOne User:User (User to Parent)

A User has an optional Wallet. OneToOne(optional) Wallet:User

Using the @MapsId so the Wallet ID (if the user has one) is the same as the User ID.

The query below is to find a users tree (from bottom to top), then find the wallets in that tree of users. The wallet that is “closest” to the root User (:rootUserId) is the one to find (so note the order by hlevel ASC limit 1

PSUEDO:

with user_hierarchy_cte ( 
   select u.id as userId, u.parent.id as parentId, 0 as hlevel
   from User u 
   where u.id = :rootUserId
union all
   select u.id as userId, u.parent.id as parentId, uhcte.hlevel + 1 as hlevel
   from User u
   join user_hierarchy_cte uhcte on u.id = uhcte.parentId
),
nearest_account_cte as (
 select w.id as walletId ,w.credit as credits, uhcte.hlevel as `level`
     from Wallet w
     join user_hierarchy_cte uhcte on uhcte.accountId = w.id
     order by hlevel ASC
     limit 1
)
select wcte.walletId as walletId, wcte.credits as credits
            from nearest_account_cte wcte

Thank you for any guidance.

2

Sounds like an H2 bug. There are plenty of bugs in H2 related to recursive CTEs. Some of the issues we try to workaround in Hibernate ORM, but this might be another one. Try the following query which just inlines the second CTE to see if that produces the desired result:

with user_hierarchy_cte ( 
   select u.id as userId, u.parent.id as parentId, 0 as hlevel
   from User u 
   where u.id = :rootUserId
union all
   select u.id as userId, u.parent.id as parentId, uhcte.hlevel + 1 as hlevel
   from User u
   join user_hierarchy_cte uhcte on u.id = uhcte.parentId
)
select wcte.walletId as walletId, wcte.credits as credits
     from (
          select w.id as walletId ,w.credit as credits, uhcte.hlevel as `level`
          from Wallet w
          join user_hierarchy_cte uhcte on uhcte.accountId = w.id
          order by hlevel ASC
          limit 1
     ) wcte
3

Thank you for the help.

Even when trying that new query with the second CTE removed and as a From Subquery there are still no results when using H2 vs MySQL.

Might just put this down to an H2 bug.

4

SQL init script for MySQL.

Boss is the “top level user” and they and a child have a “Wallet”.

SET FOREIGN_KEY_CHECKS=0;
truncate table users;
truncate table wallets;
SET FOREIGN_KEY_CHECKS=1;

insert into users(id,name,parent_id)
values
(1,'Boss',null),
(2,'Edd',1),
(3,'Frank',1),
(4,'Dave',1),
(5,'Adam',2),
(6,'Freya',5);

insert into wallets (id,credit) values (1,9999),(2,100);

H2 Init Script


SET REFERENTIAL_INTEGRITY FALSE;
truncate table users;
truncate table wallets;
SET REFERENTIAL_INTEGRITY TRUE;

insert into users(id,name,parent_id)
values
(1,'Boss',null),
(2,'Edd',1),
(3,'Frank',1),
(4,'Dave',1),
(5,'Adam',2),
(6,'Freya',5);

insert into wallets (id,credit) values (1,9999),(2,100);

I did assert the entities were persisted in both cases:

    @Test
    @Sql(scripts = "/db/init.sql")
    void getClosestWalletBottomUp() {

        Assertions.assertEquals(6,userRepo.count());
        Assertions.assertEquals(2,walletRepo.count());

    @Test
    @Sql(scripts = "/db/init-mysql.sql")
    void getClosestWalletBottomUp() {

        Assertions.assertEquals(6,userRepo.count());
        Assertions.assertEquals(2,walletRepo.count());

Test cases:

  1. User ID 5 → Expect Wallet 2
  2. User ID 6 → Expect Wallet 2
  3. User ID 1 → Expect Wallet 1

With MySQL and the ORIGINAL query (2 CTES)

  1. WalletID: 2 Level:1: Credit:100
  2. WalletID: 2 Level:2: Credit:100
  3. WalletID: 1 Level:0: Credit:9999

With H2 and the ORIGINAL query (2 CTES)

  1. No output
  2. No output
  3. No output

When using this new query with MySQL it actually doesn’t quite seem to work, the output is:

With MySQL and the NEW SUBSELECT query (1 CTE + SubSelect)

  1. WalletID: 1 Level:2: Credit:9999
  2. WalletID: 1 Level:3: Credit:9999
  3. WalletID: 1 Level:0: Credit:9999

Also no output for H2.