[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5804) Left outer join has extra incorrect join components
YU (JIRA)
noreply at atlassian.com
Tue Dec 21 23:22:05 EST 2010
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5804?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=39412#action_39412 ]
YU commented on HHH-5804:
--------------------------
Actually, even one left join can trigger this problem to occur:
HQL: select ResolveActionResult.sysId, ResolveActionResult.task.UName from com.resolve.persistence.model.ResolveActionResult ResolveActionResult left join ResolveActionResult.task where ResolveActionResult.sysId in ( '8a81838b2d09d253012d09d8d20903e0')
Generated SQL by hibernate 3.6 or 3.5:
select
resolveact0_.sys_id as col_0_0_,
resolveact2_.u_name as col_1_0_
from
resolve3.resolve_action_result resolveact0_
left outer join
resolve3.resolve_action_task resolveact1_
on resolveact0_.u_actiontask=resolveact1_.sys_id,
resolve3.resolve_action_task resolveact2_
where
resolveact0_.u_actiontask=resolveact2_.sys_id
and (
resolveact0_.sys_id in (
'8a81838b2d09d253012d09d8d20903e0'
)
)
we can see incorrect extra join components were added by 3.6 hibernate:
resolve3.resolve_action_task resolveact2_
resolveact0_.u_actiontask=resolveact2_.sys_id
But if HQL is changed to this:
select ResolveActionResult.sysId from com.resolve.persistence.model.ResolveActionResult ResolveActionResult left join ResolveActionResult.task where ResolveActionResult.sysId in ( '8a81838b2d09d253012d09d8d20903e0')
Then the generated SQL is correct ( no above extra join components):
select
resolveact0_.sys_id as col_0_0_
from
resolve3.resolve_action_result resolveact0_
left outer join
resolve3.resolve_action_task resolveact1_
on resolveact0_.u_actiontask=resolveact1_.sys_id
where
resolveact0_.sys_id in (
'8a81838b2d09d253012d09d8d20903e0'
)
So basically, any join table column reference, like "ResolveActionResult.task.UName", in a HQL select clause would trigger Hibernate 3.6 to add inner join components to its generated SQL output, even if HQL is an outer join query like my example.
So this problem should be very easy to replicate by anybody.
> Left outer join has extra incorrect join components
> ---------------------------------------------------
>
> Key: HHH-5804
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5804
> Project: Hibernate Core
> Issue Type: Bug
> Components: core
> Affects Versions: 3.6.0
> Environment: Hibernate 3.6.0 final, oracle, mysql etc.
> Reporter: YU
>
> Hibernate 3.3.2 GA was used by our application. After upgrade to hibernate 3.6.0 final, our application was broken. After turned on sql debug, we found that the same HQL query had generated different native sql queries by hibernate 3.6 and 3.3.2. The problem has been verified on Oracle and MySql. It seems some extra unnecessary join components were added to native query by 3.6. Here is an example.
> HQL query:
> select ResolveActionResult.sysId , ResolveActionResult.sysId , ResolveActionResult.UTimestamp , ResolveActionResult.task.UName , ResolveActionResult.task.sysId , ResolveActionResult.UCompletion , ResolveActionResult.UCondition , ResolveActionResult.USeverity , ResolveActionResult.USummary , ResolveActionResult.process.UNumber , ResolveActionResult.process.sysId , ResolveActionResult.executeRequest.UNumber , ResolveActionResult.executeRequest.sysId , ResolveActionResult.UDuration , ResolveActionResult.UAddress , ResolveActionResult.target.UName , ResolveActionResult.UDetail , ResolveActionResult.executeResult.UCommand , ResolveActionResult.executeResult.UReturncode from com.resolve.persistence.model.ResolveActionResult ResolveActionResult left join ResolveActionResult.task left join ResolveActionResult.process left join ResolveActionResult.executeRequest left join ResolveActionResult.target left join ResolveActionResult.executeResult left join ResolveActionResult.problem where ResolveActionResult.sysId in ( '8a81838b2d09d253012d09d8d20903e0') order by ResolveActionResult.UTimestamp desc
> Native SQL created by hibernate 3.3.2 from above HQL:
> select
> resolveact0_.sys_id as col_0_0_,
> resolveact0_.sys_id as col_1_0_,
> resolveact0_.u_timestamp as col_2_0_,
> resolveact1_.u_name as col_3_0_,
> resolveact0_.u_actiontask as col_4_0_,
> resolveact0_.u_completion as col_5_0_,
> resolveact0_.u_condition as col_6_0_,
> resolveact0_.u_severity as col_7_0_,
> resolveact0_.u_summary as col_8_0_,
> resolvepro2_.u_number as col_9_0_,
> resolveact0_.u_process as col_10_0_,
> resolveexe3_.u_number as col_11_0_,
> resolveact0_.u_execute_request as col_12_0_,
> resolveact0_.u_duration as col_13_0_,
> resolveact0_.u_address as col_14_0_,
> resolvetar4_.u_name as col_15_0_,
> resolveact0_.u_detail as col_16_0_,
> resolveexe5_.u_command as col_17_0_,
> resolveexe5_.u_returncode as col_18_0_
> from
> resolve3.resolve_action_result resolveact0_
> left outer join
> resolve3.resolve_action_task resolveact1_
> on resolveact0_.u_actiontask=resolveact1_.sys_id
> left outer join
> resolve3.resolve_process_request resolvepro2_
> on resolveact0_.u_process=resolvepro2_.sys_id
> left outer join
> resolve3.resolve_execute_request resolveexe3_
> on resolveact0_.u_execute_request=resolveexe3_.sys_id
> left outer join
> resolve3.resolve_target resolvetar4_
> on resolveact0_.u_target=resolvetar4_.sys_id
> left outer join
> resolve3.resolve_execute_result resolveexe5_
> on resolveact0_.u_execute_result=resolveexe5_.sys_id
> left outer join
> resolve3.worksheet worksheet6_
> on resolveact0_.u_problem=worksheet6_.sys_id
> where
> resolveact0_.sys_id in (
> '8a81838b2d09d253012d09d8d20903e0'
> )
> Native query created by Hibernate 3.6:
> select
> resolveact0_.sys_id as col_0_0_,
> resolveact0_.sys_id as col_1_0_,
> resolveact0_.u_timestamp as col_2_0_,
> resolveact7_.u_name as col_3_0_,
> resolveact0_.u_actiontask as col_4_0_,
> resolveact0_.u_completion as col_5_0_,
> resolveact0_.u_condition as col_6_0_,
> resolveact0_.u_severity as col_7_0_,
> resolveact0_.u_summary as col_8_0_,
> resolvepro8_.u_number as col_9_0_,
> resolveact0_.u_process as col_10_0_,
> resolveexe9_.u_number as col_11_0_,
> resolveact0_.u_execute_request as col_12_0_,
> resolveact0_.u_duration as col_13_0_,
> resolveact0_.u_address as col_14_0_,
> resolvetar10_.u_name as col_15_0_,
> resolveact0_.u_detail as col_16_0_,
> resolveexe11_.u_command as col_17_0_,
> resolveexe11_.u_returncode as col_18_0_
> from
> resolve3.resolve_action_result resolveact0_
> left outer join
> resolve3.resolve_action_task resolveact1_
> on resolveact0_.u_actiontask=resolveact1_.sys_id
> left outer join
> resolve3.resolve_process_request resolvepro2_
> on resolveact0_.u_process=resolvepro2_.sys_id
> left outer join
> resolve3.resolve_execute_request resolveexe3_
> on resolveact0_.u_execute_request=resolveexe3_.sys_id
> left outer join
> resolve3.resolve_target resolvetar4_
> on resolveact0_.u_target=resolvetar4_.sys_id
> left outer join
> resolve3.resolve_execute_result resolveexe5_
> on resolveact0_.u_execute_result=resolveexe5_.sys_id
> left outer join
> resolve3.worksheet worksheet6_
> on resolveact0_.u_problem=worksheet6_.sys_id,
> where
> resolveact0_.u_actiontask=resolveact7_.sys_id
> and resolveact0_.u_process=resolvepro8_.sys_id
> and resolveact0_.u_execute_request=resolveexe9_.sys_id
> and resolveact0_.u_target=resolvetar10_.sys_id
> and resolveact0_.u_execute_result=resolveexe11_.sys_id
> and (
> resolveact0_.sys_id in (
> '8a81838b2d09d253012d09d8d20903e0'
> )
> )
> order by
> resolveact0_.u_timestamp desc
> Obviously, Hibernate 3.6 generated wrong sql because extra query tables and where conditions were added in generated query such as
> resolve3.resolve_action_task resolveact7_,
> resolve3.resolve_process_request resolvepro8_,
> resolve3.resolve_execute_request resolveexe9_,
> resolve3.resolve_target resolvetar10_,
> resolve3.resolve_execute_result resolveexe11_
> resolveact0_.u_actiontask=resolveact7_.sys_id
> and resolveact0_.u_process=resolvepro8_.sys_id
> and resolveact0_.u_execute_request=resolveexe9_.sys_id
> and resolveact0_.u_target=resolvetar10_.sys_id
> and resolveact0_.u_execute_result=resolveexe11_.sys_id
> I searched Hibernate forums, but not able to find anything yet. Does anybody know anything about this issue? Thanks a lot.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list