Message Title

I have a long native query in Hibernate, that is split in multiple queries (with table as ...). This query use inner joins that not work in hibernate (empty result), but work excellent in the SQL Console. Also if I copy/paste the query from the hibernate logs into the console and it works also.

with params as (select ca.id as calculation_id, chart_id, range_size, range_size * interval '1' minute as sizefrom calculation ca
                         inner join chart c on c.id = ca.chart_id
                         inner join time_range tr on tr.id = c.time_range_id
                where ca.id = :calculationid),
     tuple_diff as (select t.calculation_id,
                           t.id,
                           t.ohlc_id,
                           t.time, t.time - lag(t.time, 1) over (order by t.time) as diff
                     from tuple t inner join params p
                       on t.calculation_id = p.calculation_id),
     ohlc_diff as (select o.chart_id,
                          o.id,
                          o.time,
                          o.time - lag(o.time, 1) over (order by o.time) as diff
                   from ohlc o inner join params p on o.chart_id = p.chart_id),
     tuple_filtered as (select id, ohlc_id, time, diff, p.size, p.range_size
                         from tuple_diff t
                                  inner join params p on chart_id = p.chart_id
                         where diff <> p.size),
     ohlc_filtered as (select o.chart_id, o.id as ohlc_id, timefrom ohlc_diff o
                                inner join params p on o.chart_id = p.chart_id
                       where o.diff <> p.size)
select t.time as time, t.range_size as sizefrom tuple_filtered t
         left join ohlc_filtered o on t.time = o.timewhere o.ohlc_id is nullorder by t.time;

A detailed Description and how I simplify the upper complex query is in my Stackoverflow question. I simplified the complex query step by step (all working simplification steps are in the test). Here is the resulting reduced simplified query with the failed join:

with params as (select ca.id as ca_id,
                       chart_id,
                       range_size, 
                       range_size * interval '1' minute as sizefrom calculation ca
                         inner join chart c on c.id = ca.chart_id
                         inner join time_range tr on tr.id = c.time_range_id
                where ca.id = :calculationid),
     tuple_diff as (select t.calculation_id,
                           t.id,
                           t.ohlc_id,
                           t.time,
                           t.time - lag(t.time, 1) over (order by t.time) as diff
                    from tuple t
                             inner join params p on p.ca_id = t.CALCULATION_ID)
select calculation_id
from tuple_diff
order by calculation_id;

I have generated a Jhipster sample with a integration test on github. The test describe the problem and feel free to check it out. I used directly the createNativeQuery method from the injected Entity Manager to prevent spring data side effects. The integration test prepare directly the database with test data.

I use in my real project a PostgreSQL database in production and in the integration test. In my development profile I use a H2 database. The result is the same, I got an empty result when I execute the query in Hibernate and a expected result in the sql console.