0

I am trying to implement a search that considers two different entities. These entities (Article, Story) have two fields in common:

  • id
  • status_changed_at

Due to their differences, I map the user's search request to javax.persistence.Predicate per entity. For each of these entities I have created a query like the following:

BlazeCriteriaQuery<DbStory> query = cb.createQuery(DbStory.class);
Root<DbStory> root = query.from(DbStory.class);
query.where(specification.toPredicate(root, query, cb));
CriteriaBuilder<DbStory> storyBuilder = query.createCriteriaBuilder(em);

Now, I would like to sort both results by status_changed_at and id to then paginate through the results. My first draft looks like this:

val unifiedResults = factory.create(em, Tuple.class)
  .with(StoryCTE.class, storyBuilder, false)
    .bind("id").select("id")
    .bind("statusChangedAt").select("statusChangedAt")
  end()
  .with(ArticleCTE.class, articleBuilder, false)
    .bind("id").select("id")
    .bind("statusChangedAt").select("statusChangedAt")
  .end()
  .with(OtherCTE.class, false)
    .from(StoryCTE.class, "s")
      .bind("id").select("s.id")
      .bind("statusChangedAt").select("s.statusChangedAt")
      .bind("type").select("'story'")
    .unionAll()
    .from(ArticleCTE.class, "a")
      .bind("id").select("a.id")
      .bind("statusChangedAt").select("a.statusChangedAt")
      .bind("type").select("'article'")
    .endSet()
    .end()
  .from(OtherCTE.class)
  .orderByDesc("statusChangedAt")
  .orderByAsc("id")
  .page(0, 100)
  .getResultList();

This works on PostgreSQL. However, I fail to get this running on H2. Maybe, there is a simpler way of achieving this that also works on H2?

Thanks in advance

EDIT: Added requested information.

Software used:

  • PostgreSQL 10.x
  • H2 2.1.214
  • Hibernate 5.6

H2-Query:

with recursive StoryCTE(id, status_changed_at) AS( select
    dbstory0_.id as col_0_0_,
    dbstory0_.status_changed_at as col_1_0_ 
from
    story dbstory0_ 
UNION
ALL SELECT
    NULL,
    NULL 
FROM
    DUAL 
WHERE
    1=0 ), ArticleCTE(id, status_changed_at) AS( select
    dbarticle0_.id as col_0_0_,
    dbarticle0_.status_changed_at as col_1_0_ 
from
    article dbarticle0_ 
UNION
ALL SELECT
    NULL,
    NULL 
FROM
    DUAL 
WHERE
    1=0 ), OtherCTE(id, status_changed_at, type) AS( select
    storycte0_.id as col_0_0_,
    storycte0_.status_changed_at as col_1_0_,
    'story' as col_2_0_ 
from
    StoryCTE storycte0_ 
UNION
ALL select
    articlecte0_.id as col_0_0_,
    articlecte0_.status_changed_at as col_1_0_,
    'article' as col_2_0_ 
from
    ArticleCTE articlecte0_ 
UNION
ALL SELECT
    NULL,
    NULL,
    NULL 
FROM
    DUAL 
WHERE
    1=0 ) select
    othercte0_.id as col_0_0_,
    (select
        count(*) 
    from
        OtherCTE othercte1_) as col_1_0_,
    othercte0_.id as id1_6_,
    othercte0_.status_changed_at as status_c2_6_,
    othercte0_.type as type3_6_ 
from
    OtherCTE othercte0_ 
order by
    othercte0_.status_changed_at DESC nulls last,
    othercte0_.id ASC limit ?

Error Message (H2):

2022-10-07 19:09:00,329 [thread=main] WARN  

org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 90109, SQLState: 90109
2022-10-07 19:09:00,329 [thread=main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper: View "OTHERCTE" ist ungültig: "not compiled"
View "OTHERCTE" is invalid: "not compiled"; SQL statement:
with recursive StoryCTE(id, status_changed_at) AS( select dbstory0_.id as col_0_0_, dbstory0_.status_changed_at as col_1_0_ from story dbstory0_ UNION ALL SELECT NULL, NULL FROM DUAL WHERE 1=0 ), ArticleCTE(id, status_changed_at) AS( select dbarticle0_.id as col_0_0_, dbarticle0_.status_changed_at as col_1_0_ from article dbarticle0_ UNION ALL SELECT NULL, NULL FROM DUAL WHERE 1=0 ), OtherCTE(id, status_changed_at, type) AS( select storycte0_.id as col_0_0_, storycte0_.status_changed_at as col_1_0_, 'story' as col_2_0_ from StoryCTE storycte0_ UNION ALL select articlecte0_.id as col_0_0_, articlecte0_.status_changed_at as col_1_0_, 'article' as col_2_0_ from ArticleCTE articlecte0_ UNION ALL SELECT NULL, NULL, NULL FROM DUAL WHERE 1=0 ) select othercte0_.id as col_0_0_, (select count(*) from OtherCTE othercte1_) as col_1_0_, othercte0_.id as id1_6_, othercte0_.status_changed_at as status_c2_6_, othercte0_.type as type3_6_ from OtherCTE othercte0_ order by othercte0_.status_changed_at DESC nulls last, othercte0_.id ASC limit ? [90109-214]
2022-10-07 19:09:00,331 [thread=main] ERROR com.blazebit.persistence.integration.hibernate.base.HibernateExtendedQuerySupport: Could not execute the following SQL query: with recursive StoryCTE(id, status_changed_at) AS( select dbstory0_.id as col_0_0_, dbstory0_.status_changed_at as col_1_0_ from story dbstory0_ UNION ALL SELECT NULL, NULL FROM DUAL WHERE 1=0 ), ArticleCTE(id, status_changed_at) AS( select dbarticle0_.id as col_0_0_, dbarticle0_.status_changed_at as col_1_0_ from article dbarticle0_ UNION ALL SELECT NULL, NULL FROM DUAL WHERE 1=0 ), OtherCTE(id, status_changed_at, type) AS( select storycte0_.id as col_0_0_, storycte0_.status_changed_at as col_1_0_, 'story' as col_2_0_ from StoryCTE storycte0_ UNION ALL select articlecte0_.id as col_0_0_, articlecte0_.status_changed_at as col_1_0_, 'article' as col_2_0_ from ArticleCTE articlecte0_ UNION ALL SELECT NULL, NULL, NULL FROM DUAL WHERE 1=0 ) select othercte0_.id as col_0_0_, (select count(*) from OtherCTE othercte1_) as col_1_0_, othercte0_.id as id1_6_, othercte0_.status_changed_at as status_c2_6_, othercte0_.type as type3_6_ from OtherCTE othercte0_ order by othercte0_.status_changed_at DESC nulls last, othercte0_.id ASC

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
...

And here is the working Postgre Query

    with StoryCTE(id, status_changed_at) AS( select
    dbstory0_.id as col_0_0_,
    dbstory0_.status_changed_at as col_1_0_ 
from
    story dbstory0_ ), ArticleCTE(id, status_changed_at) AS( select
    dbarticle0_.id as col_0_0_,
    dbarticle0_.status_changed_at as col_1_0_ 
from
    article dbarticle0_ ), OtherCTE(id, status_changed_at, type) AS( select
    storycte0_.id as col_0_0_,
    storycte0_.status_changed_at as col_1_0_,
    'story' as col_2_0_ 
from
    StoryCTE storycte0_ 
UNION
ALL select
    articlecte0_.id as col_0_0_,
    articlecte0_.status_changed_at as col_1_0_,
    'article' as col_2_0_ 
from
    ArticleCTE articlecte0_ ) select
    othercte0_.id as col_0_0_,
    (select
        count(*) 
    from
        OtherCTE othercte1_) as col_1_0_,
    othercte0_.id as id1_6_,
    othercte0_.status_changed_at as status_c2_6_,
    othercte0_.type as type3_6_ 
from
    OtherCTE othercte0_ 
order by
    othercte0_.status_changed_at DESC nulls last,
    othercte0_.id ASC limit ?

EDIT 2 I do not understand why but I managed to get things running on:

  • PostgreSQL using CTE
  • PostgreSQL using the Inline-Query
  • H2 using the Inline-Query

In the code above I simply replaced the following lines:

.from(ArticleCTE.class, "a")
      .bind("id").select("a.id")
      .bind("statusChangedAt").select("a.statusChangedAt")

with

.from(ArticleCTE.class, "s") // why???
  .bind("id").select("id")
  .bind("statusChangedAt").select("statusChangedAt")
Jens Gerdes
  • 1
  • 1
  • Can you share the query and error you see on H2? – Christian Beikov Oct 07 '22 at 14:49
  • Hi @ChristianBeikov, I've added some additional information – Jens Gerdes Oct 07 '22 at 17:14
  • Can you please try passing `true` to the `with()` call, since H2 doesn't "really" support CTEs. In Hibernate 6, I implemented inlining of CTEs by default due the many bugs the WITH clause has in H2. – Christian Beikov Oct 10 '22 at 08:24
  • Thanks but that results in another issue: `java.lang.IllegalArgumentException: The alias a could not be found in the query: org.hibernate.query.internal.QueryImpl@ad5bb92 at com.blazebit.persistence.integration.hibernate.base.HibernateExtendedQuerySupport.getSqlFromInfo(HibernateExtendedQuerySupport.java:233) at com.blazebit.persistence.impl.AbstractCommonQueryBuilder.collectEntityFunctionNodes(AbstractCommonQueryBuilder.java:3114)` – Jens Gerdes Oct 10 '22 at 09:28
  • Do you think you could provide me access to this example somewhere so I can try to fix the issue? – Christian Beikov Oct 10 '22 at 09:30
  • Sure. I will prepare a simplified example on github. – Jens Gerdes Oct 10 '22 at 09:34
  • 1
    [Here's the sample](https://github.com/jensgerdes/cte-issue) The README explains everything required. Thanks for your support :) – Jens Gerdes Oct 10 '22 at 11:18
  • I was able to reproduce the issue and you can watch the progress on https://github.com/Blazebit/blaze-persistence/issues/1563 – Christian Beikov Oct 11 '22 at 11:39
  • And here is a possible fix for this: https://github.com/Blazebit/blaze-persistence/pull/1564 – Christian Beikov Oct 11 '22 at 11:45

0 Answers0