[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5804) Left outer join has extra incorrect join components

[YU (JIRA)]

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