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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira