Blaze Persistence Join on subquery issue

Question

I am trying to do a join on subquery by using blaze bit persistence, I have a criteria builder like below,

cbf.create(em, b.class, "foo")
   .innerJoinOnSubquery(aCTE.class, "maxEnt")
   .from(a.class, "subFoo")
   .bind("account_id").select("subFoo.accountId","account_id")
   .bind("id").select("subFoo.id","id")
   .where("subFoo.accountId").eq(100L)
   .end()
   .on("maxEnt.account_id").eqExpression("foo.accountId")
   .end()
   .where("foo.accountId").eq(100L);

when I execute it I am getting sql query like below

SELECT s0_.id FROM   b s0_ 
   INNER JOIN (SELECT t0_.account_id     AS col_0_0_, 
                      t0_.id AS col_1_0_ 
               FROM   a t0_ 
               WHERE  t0_.account_id = 100 ) t1_ 
           ON ( ( NULL IS NULL ) 
                AND s0_.account_id = t1_.account_id) WHERE  s0_.account_id = 100 

The problem occurs in the on clause, when i try to compare the two table account_id in the on cluase I am getting column missing error since the alias is formed for the account_id defaultly in the subquery. Please let me know how to resolve it. I am using the hibernate5.4 JPA vendor.

Issue: DB::Exception: There's no column 't1_.account_id' in table 't1_': While processing t1_.account_id.

Answer 1

This might be a bug. I opened https://github.com/Blazebit/blaze-persistence/issues/1285 for this purpose. Could you maybe tell me which Hibernate version you are using and if the SQL query works if you add a closing parenthesis before the WHERE clause? It seems that parenthesis is missing here, but I don't know yet why.

UPDATE:

Try this as a workaround:

cbf.create(em, b.class, "foo")
   .with(aCTE.class) // Maybe pass false as second argument to avoid inlining
     .from(a.class, "subFoo")
     .bind("account_id").select("subFoo.accountId","account_id")
     .bind("id").select("subFoo.id","id")
     .where("subFoo.accountId").eq(100L)
   .end()
   .innerJoinOn(aCTE.class, "maxEnt")
     .on("maxEnt.account_id").eqExpression("foo.accountId")
   .end()
   .where("foo.accountId").eq(100L);