November 2022 - hibernate-issues - Jboss List Archives

[JIRA] (HHH-15673) Native "with" query works in the SQL console but return no data as native query in hibernate (an inner join fails)

by Jochen Buchholz (JIRA)

1 year, 12 months

1
0
0 / 0

[JIRA] (HHH-15673) Native "with" query works in the SQL console but return no data as native query in hibernate (an inner join fails)

by Jochen Buchholz (JIRA)

Jochen Buchholz ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=70121%3... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZmE4M2MzMzM5... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15673?atlOrigin=eyJpIjoiZmE4M2... ) HHH-15673 ( https://hibernate.atlassian.net/browse/HHH-15673?atlOrigin=eyJpIjoiZmE4M2... ) Native "with" query works in the SQL console but return no data as native query in hibernate (an inner join fails) ( https://hibernate.atlassian.net/browse/HHH-15673?atlOrigin=eyJpIjoiZmE4M2... ) Issue Type: Bug Affects Versions: 5.6.10 Assignee: Unassigned Created: 04/Nov/2022 13:58 PM Environment: Linux-5.15.74-gentoo-x86_64 openjdk-17.0.3_p7-r1 Priority: Major Reporter: Jochen Buchholz ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=70121%3... ) 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 size from 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, time from 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 size from tuple_filtered t left join ohlc_filtered o on t. time = o. time where o.ohlc_id is null order by t. time ; A detailed Description and how I simplify the upper complex query is in my Stackoverflow question ( https://stackoverflow.com/questions/74244793/native-with-query-works-in-t... ). 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 size from 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 ( https://github.com/joe-bookwood/querybug/blob/main/src/test/java/de/bitc/... ) 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. ( https://hibernate.atlassian.net/browse/HHH-15673#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15673#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100210- sha1:21ade21 )

1 year, 12 months