Incorrect Generated SQL from HQL for left join - Left Join in the wrong place
<p>All,</p>

<p>Using version Nhibernate 3.3.3 GA.</p>

<p>The HQL I have created is not correctly translated to SQL. Specifically,
one of the left joins (between plan: Job_Plans table and division: Code_Plan_Division_Types table) ends up as an inner join in the where clause. </p>

<p>In other words, instead of this:</p>

<pre><code>...
from Job_Info project_0_ 
 left outer join Job_Plans planlist5_ on project_0_.entity_id=planlist5_.job_id 
 left outer join Code_Plan_Division_Types plandivisi18_ on planlist5_.division_id=plandivisi18_.entity_id 
</code></pre>

<p>I get this:</p>

<pre><code>...
from Job_Info project_0_ 
 left outer join Job_Plans planlist5_ on project_0_.entity_id=planlist5_.job_id, 
Code_Plan_Division_Types plandivisi18_ 
where 
 planlist5_.division_id=plandivisi18_.entity_id
</code></pre>

<p>Here are my complete queries:</p>

<p>HQL:</p>

<pre><code>select p, plans from Project_ p 
    inner join fetch p.Client
    inner join fetch p.ProjectType
    left join fetch p.Office
    left join fetch p.Region

    left join p.PlanList plans
    left join fetch plans.PlanType
    left join fetch plans.ProjectType
    left join fetch plans.Quadrant
    left join fetch plans.Division
    left join fetch plans.County
    left join fetch plans.Municipality
    left join fetch plans.Township 

    where p.id in ( select p.id
    from Project_ p
        left join p.PlanList plans
        left join p.ReferencePlanList rplans
        left join p.AddressList addr
        left join p.ImageSets imgs 
    where p.Status = true and p.Region.Id = :region) 

    and plans.PlanNumber like :planNo 


    order by plans.PlanNumber asc, plans.Division.Code asc, replace((coalesce(plans.Lot, '')+coalesce(plans.PartLot, '')), '-', ' ') asc, replace((coalesce(plans.Block, '')+coalesce(plans.PartBlock, '')), '-', ' ') asc
</code></pre>

<p>Generated SQL:</p>

<pre><code>select TOP (100) 
project_0_.entity_id as entity1_20_0_, planlist5_.entity_id as entity1_76_1_, client_1_.entity_id as entity1_11_2_, projecttyp2_.entity_id as entity1_54_3_, office_3_.entity_id as entity1_55_4_, region_4_.entity_id as entity1_1_5_, plantype_6_.entity_id as entity1_21_6_, projecttyp7_.entity_id as entity1_54_7_, quadrant_8_.entity_id as entity1_2_8_, plandivisi9_.entity_id as entity1_12_9_, county_10_.entity_id as entity1_22_10_, municipali11_.entity_id as entity1_3_11_, township_12_.entity_id as entity1_30_12_, project_0_.plan_attached as plan2_20_0_, project_0_.notes_attached as notes3_20_0_, project_0_.status as status20_0_, project_0_.file_number as file5_20_0_, project_0_.order_date as order6_20_0_, project_0_.due_date as due7_20_0_, project_0_.due_date_fw as due8_20_0_, project_0_.lock_status as lock9_20_0_, project_0_.currency_code as currency10_20_0_, project_0_.client_reference as client11_20_0_, project_0_.pin as pin20_0_, project_0_.office_id as office13_20_0_, project_0_.job_type_id as job14_20_0_, project_0_.client_entity_id as client15_20_0_, project_0_.region_id as region16_20_0_, planlist5_.lot as lot76_1_, planlist5_.part_lot as part3_76_1_, planlist5_.lot_search as lot4_76_1_, planlist5_.block as block76_1_, planlist5_.part_block as part6_76_1_, planlist5_.plan_number as plan7_76_1_, planlist5_.section_num as section8_76_1_, planlist5_.township as township76_1_, planlist5_.range_num as range10_76_1_, planlist5_.meridian_id as meridian11_76_1_, planlist5_.job_id as job12_76_1_, planlist5_.plan_id as plan13_76_1_, planlist5_.job_type_id as job14_76_1_, planlist5_.quadrant_id as quadrant15_76_1_, planlist5_.division_id as division16_76_1_, planlist5_.county_id as county17_76_1_, planlist5_.municipality_id as municip18_76_1_, planlist5_.township_id as township19_76_1_, client_1_.status as status11_2_, client_1_.client_number as client3_11_2_, client_1_.client_name as client4_11_2_, projecttyp2_.name as name54_3_, projecttyp2_.description as descript3_54_3_, office_3_.name as name55_4_, office_3_.description as descript3_55_4_, region_4_.name as name1_5_, region_4_.description as descript7_1_5_, region_4_.country_id as country8_1_5_, plantype_6_.name as name21_6_, plantype_6_.description as descript3_21_6_, plantype_6_.display as display21_6_, projecttyp7_.name as name54_7_, projecttyp7_.description as descript3_54_7_, quadrant_8_.name as name2_8_, quadrant_8_.description as descript7_2_8_, plandivisi9_.name as name12_9_, plandivisi9_.description as descript7_12_9_, plandivisi9_.display as display12_9_, county_10_.name as name22_10_, county_10_.description as descript3_22_10_, county_10_.region_id as region4_22_10_, municipali11_.name as name3_11_, municipali11_.description as descript7_3_11_, municipali11_.county_id as county11_3_11_, township_12_.name as name30_12_, township_12_.description as descript3_30_12_, township_12_.county_id as county4_30_12_ 

from Job_Info project_0_ 
inner join Companies client_1_ on project_0_.client_entity_id=client_1_.entity_id 
inner join Code_Job_Types projecttyp2_ on project_0_.job_type_id=projecttyp2_.entity_id 
left outer join code_office_types office_3_ on project_0_.office_id=office_3_.entity_id 
left outer join Code_Region_Types region_4_ on project_0_.region_id=region_4_.entity_id 
left outer join Job_Plans planlist5_ on project_0_.entity_id=planlist5_.job_id 
left outer join Code_Plan_ID_Types plantype_6_ on planlist5_.plan_id=plantype_6_.entity_id 
left outer join Code_Job_Types projecttyp7_ on planlist5_.job_type_id=projecttyp7_.entity_id 
left outer join Code_Quadrant_Types quadrant_8_ on planlist5_.quadrant_id=quadrant_8_.entity_id 
left outer join Code_Plan_Division_Types plandivisi9_ on planlist5_.division_id=plandivisi9_.entity_id 
left outer join code_county_types county_10_ on planlist5_.county_id=county_10_.entity_id 
left outer join Code_Municipality_Types municipali11_ on planlist5_.municipality_id=municipali11_.entity_id 
left outer join Code_Township_Types township_12_ on planlist5_.township_id=township_12_.entity_id, 
Code_Plan_Division_Types plandivisi18_ 


where planlist5_.division_id=plandivisi18_.entity_id 
and (project_0_.entity_id in 


(select project_13_.entity_id 
from Job_Info project_13_ 
left outer join Job_Plans planlist14_ on project_13_.entity_id=planlist14_.job_id 
left outer join Job_Underlying_Rplan referencep15_ on project_13_.entity_id=referencep15_.job_id 
left outer join Job_Address addresslis16_ on project_13_.entity_id=addresslis16_.JobId 
left outer join Job_Images_Set imagesets17_ on project_13_.entity_id=imagesets17_.job_id 
where project_13_.status=1 and project_13_.region_id=1)) 
and (planlist5_.plan_number like '%plan%') 

order by planlist5_.plan_number asc, plandivisi18_.name asc, replace(coalesce(planlist5_.lot, '')+coalesce(planlist5_.part_lot, ''), '-', ' ') asc, replace(coalesce(planlist5_.block, '')+coalesce(planlist5_.part_block, ''), '-', ' ') asc
</code></pre>

<p><strong>UPDATE:</strong>
Found the cause: I should NOT be referencing plans.Division.Code in my order by (or even where clause) because of the "fetch" keyword. See answer for more details.</p>

<p>The cause is the order by clause. By removing plans.Division.Code from the order by the query is correct now. The changed order by:</p>

<p>HQL:</p>

<pre><code>order by plans.PlanNumber asc, replace((coalesce(plans.Lot, '')+coalesce(plans.PartLot, '')), '-', ' ') asc, replace((coalesce(plans.Block, '')+coalesce(plans.PartBlock, '')), '-', ' ') asc
</code></pre>

<p>Generated SQL:</p>

<pre><code>order by planlist5_.plan_number asc, replace(coalesce(planlist5_.lot, '')+coalesce(planlist5_.part_lot, ''), '-', ' ') asc, replace(coalesce(planlist5_.block, '')+coalesce(planlist5_.part_block, ''), '-', ' ') asc
</code></pre>