Wondering if anyone has encountered this issue as of yet.

Unless I am missing something the Criteria.createAlias() is not creating the appropriate inner join when trying to associate on an Object within a primary key.

For example, given identical mappings the following in HQL


produces the following 'correct' syntax
select * from ( select pricedetai0_.EFFECTIVE_DATE as EFFECTIV1_0_, pricedetai0_.UPDATE_DATE as UPDATE_D2_0_, pricedetai0_.TYPE as TYPE0_, pricedetai0_.PRICE_ID as PRICE_ID0_, price1_.PRICE_ID as PRICE_ID1_, pricedetai0_.AMT as AMT0_, pricedetai0_.UPDATE_USER as UPDATE_U6_0_, pricedetai0_.PRICE_COMPLETE_DATE as PRICE_CO7_0_, pricedetai0_.LOCKED_FLG as LOCKED_FLG0_, pricedetai0_.MAX_UPDATE_DATE_FLG as MAX_UPDA9_0_, pricedetai0_.AS_OF_DATE as AS_OF_DATE0_, price1_.DESCRIPTION as DESCRIPT2_1_, price1_.EXCHANGE_RATE_TYPE as EXCHANGE3_1_, price1_.PRICE_GROUP as PRICE_GR4_1_, price1_.MILEAGE_COMP as MILEAGE_5_1_, price1_.INACTIVE_DATE as INACTIVE6_1_, price1_.UPDATE_USER as UPDATE_U7_1_, price1_.UPDATE_DATE as UPDATE_D8_1_, price1_.UPPER_DESCRIPTION as UPPER_DE9_1_, price1_.SEPARATE_ON_INVOICE_IND as SEPARAT10_1_, price1_.INDICATE_USAGE_ON_INVOICE as INDICAT11_1_, price1_.DESCRIPTION_PART2 as DESCRIP12_1_, price1_.SURCHARGE_PRICE_ID as SURCHAR13_1_, price1_.PRICE_SUBGROUP as PRICE_S14_1_, price1_.NOTES_ID as NOTES_ID1_, price1_.PRICE_CAT_CODE as PRICE_C16_1_, price1_.CURRENCY_CODE as CURRENC17_1_, price1_.UOM_CODE as UOM_CODE1_, price1_.DELIVERY_ADDR_ID as DELIVER19_1_, price1_.SOURCE_ADDR_ID as SOURCE_20_1_, price1_.CARRIER_ID as CARRIER_ID1_, price1_.ITEM_CODE as ITEM_CODE1_ from PRICE_D pricedetai0_ inner join PRICE_H price1_ on pricedetai0_.PRICE_ID=price1_.PRICE_ID ) where rownum <= ?

Notice inner join on the PRICE_H table.

Now, trying to do what I think is the same thing with Criteria



produces the following SQL syntax
select * from ( select this.EFFECTIVE_DATE as EFFECTIV1_0_, this.UPDATE_DATE as UPDATE_D2_0_, this.TYPE as TYPE0_, this.PRICE_ID as PRICE_ID0_, this.AMT as AMT0_, this.UPDATE_USER as UPDATE_U6_0_, this.PRICE_COMPLETE_DATE as PRICE_CO7_0_, this.LOCKED_FLG as LOCKED_FLG0_, this.MAX_UPDATE_DATE_FLG as MAX_UPDA9_0_, this.AS_OF_DATE as AS_OF_DATE0_ from PRICE_D this where 1=1 ) where rownum <= ?

Notice that is seems to ignore my alias altogether and doesn't even try to offer an inner join, nor does it blow an exception saying that it doesn't know what the association is (which it does do if I omit the 'id.').

Am I completely crazy in thinking that the above should work? I'm going to go back to tracing through the code to see if anything jumps out but it is almost like it just ignores anything with the "id." when using the criteria.

While the HQL will work it would be nice if the Criteria did also since I'm refining a query layer that hides all of the session handling from normal humanoids which is based on the criteria API that works wonderfully under most circumstances.

The Criteria API is wonderously flexible where having to build up dynamic queries based on the information a user requests and I want to continue exploiting that.

This is not what createAlias does in the criteria api. reread the documentation.

Michael, thanks for the very quick reply.

The createAlias() api doc states:
"Join an association, assigning an alias to the joined entity "

If I use createAlias() on any other given many-to-one it creates an inner join. I can then traverse my associations down multiple levels.

I want to join an association off of my primary key object and then query some property off of it.

as an example.



Produces the following sql

select * from ( select this.PRICE_ID as PRICE_ID24_, this.DESCRIPTION as DESCRIPT2_24_, this.EXCHANGE_RATE_TYPE as EXCHANGE3_24_, this.PRICE_GROUP as PRICE_GR4_24_, this.MILEAGE_COMP as MILEAGE_5_24_, this.INACTIVE_DATE as INACTIVE6_24_, this.UPDATE_USER as UPDATE_U7_24_, this.UPDATE_DATE as UPDATE_D8_24_, this.UPPER_DESCRIPTION as UPPER_DE9_24_, this.SEPARATE_ON_INVOICE_IND as SEPARAT10_24_, this.INDICATE_USAGE_ON_INVOICE as INDICAT11_24_, this.DESCRIPTION_PART2 as DESCRIP12_24_, this.SURCHARGE_PRICE_ID as SURCHAR13_24_, this.PRICE_SUBGROUP as PRICE_S14_24_, this.NOTES_ID as NOTES_ID24_, this.PRICE_CAT_CODE as PRICE_C16_24_, this.CURRENCY_CODE as CURRENC17_24_, this.UOM_CODE as UOM_CODE24_, this.DELIVERY_ADDR_ID as DELIVER19_24_, this.SOURCE_ADDR_ID as SOURCE_20_24_, this.CARRIER_ID as CARRIER_ID24_, this.ITEM_CODE as ITEM_CODE24_, pricecateg1_.PRICE_CAT_CODE as PRICE_CA1_0_, pricecateg1_.DESCRIPTION as DESCRIPT2_0_, pricecateg1_.TYPE as TYPE0_, pricecateg1_.FORMULA_FLG as FORMULA_4_0_, pricecateg1_.UPDATE_USER as UPDATE_U5_0_, pricecateg1_.UPDATE_DATE as UPDATE_D6_0_, pricecateg1_.SURCHARGE_FLG as SURCHARG7_0_, pricecateg1_.TRADING_GROUP_FLG as TRADING_8_0_, pricecateg1_.NUM_CALC_DECIMALS as NUM_CALC9_0_, pricecateg1_.LOCKING_METHOD_TYPE as LOCKING10_0_, pricecateg1_.GENERATE_COST_FOR_BU_UNIT_IND as GENERAT11_0_, pricecateg1_.SURCHARGE_CAT_CODE as SURCHAR12_0_, pricecateg1_.INCLUDE_IN_NET_PRICE_IND as INCLUDE13_0_, pricecateg1_.FUTURE_PRICE_FLG as FUTURE_14_0_, pricecateg1_.FREQUENCY_CODE as FREQUEN15_0_, pricecateg1_.EXCLUDE_IN_NGL_RPT_IND as EXCLUDE16_0_, pricecateg1_.RISK_TYPE as RISK_TYPE0_, pricecateg2_.TYPE as TYPE1_, pricecateg2_.DESCRIPTION as DESCRIPT2_1_, pricecateg2_.RISK_CATEGORY as RISK_CAT3_1_, pricecateg2_.REPORTING_ORDER as REPORTIN4_1_, pricecateg2_.INACTIVE_DATE as INACTIVE5_1_, currency3_.CURRENCY_CODE as CURRENCY1_2_, currency3_.DESCRIPTION as DESCRIPT2_2_, currency3_.INACTIVE_DATE as INACTIVE3_2_, currency3_.UPDATE_USER as UPDATE_U4_2_, currency3_.UPDATE_DATE as UPDATE_D5_2_, currency3_.CURRENCY_SYMBOL as CURRENCY6_2_, currency3_.DISPLAY_FORMAT as DISPLAY_7_2_, currency3_.COUNTRY_CODE as COUNTRY_8_2_, jurisdicti4_.JURISDICTION_CODE as JURISDIC1_3_, jurisdicti4_.NAME as NAME3_, jurisdicti4_.TYPE as TYPE3_, jurisdicti4_.SENIOR_JURISDICTION_CODE as SENIOR_J4_3_, jurisdicti4_.UPDATE_USER as UPDATE_U5_3_, jurisdicti4_.UPDATE_DATE as UPDATE_D6_3_, jurisdicti4_.PHONE_EDIT_MASK as PHONE_ED7_3_, jurisdicti4_.DFLT_CURRENCY_CODE as DFLT_CUR8_3_, jurisdicti4_.STATE_GEOCODE as STATE_GE9_3_, jurisdicti4_.COUNTY_GEOCODE as COUNTY_10_3_, jurisdicti4_.CITY_GEOCODE as CITY_GE11_3_, jurisdicti4_.HOME_RULE_FLG as HOME_RU12_3_, jurisdicti4_.DEFAULT_VOL_UOM as DEFAULT13_3_, jurisdicti4_.DEFAULT_ENERGY_UOM as DEFAULT14_3_, uom5_.UOM_CODE as UOM_CODE4_, uom5_.DESCRIPTION as DESCRIPT2_4_, uom5_.TYPE as TYPE4_, uom5_.INACTIVE_DATE as INACTIVE4_4_, uom5_.UPDATE_USER as UPDATE_U5_4_, uom5_.UPDATE_DATE as UPDATE_D6_4_, uom5_.SI as SI4_, uom5_.EDIT_MASK as EDIT_MASK4_, uom5_.SHORT_DESCRIPTION as SHORT_DE9_4_, location6_.ADDR_ID as ADDR_ID5_, location6_.ADDR_LINE1 as ADDR_LINE15_, location6_.ADDR_LINE2 as ADDR_LINE25_, location6_.ADDR_LINE3 as ADDR_LINE35_, location6_.CITY as CITY5_, location6_.POST_ZIP_CODE as POST_ZIP6_5_, location6_.X_COORD as X_COORD5_, location6_.Y_COORD as Y_COORD5_, location6_.Z_COORD as Z_COORD5_, location6_.DESCRIPTION as DESCRIP10_5_, location6_.EDI_ACCOUNT as EDI_ACC11_5_, location6_.INACTIVE_DATE as INACTIV12_5_, location6_.UPDATE_USER as UPDATE_13_5_, location6_.UPDATE_DATE as UPDATE_14_5_, location6_.TIME_ZONE as TIME_ZONE5_, location6_.CUTOFF_TIME as CUTOFF_16_5_, location6_.FACILITY_BALANCE_DEFAULT as FACILIT17_5_, location6_.OPERATOR_ID as OPERATO18_5_, location6_.DFLT_UOM_CODE as DFLT_UO19_5_, location6_.DFLT_TEMP_UOM_CODE as DFLT_TE20_5_, location6_.RECEIPT_VOLUME_TYPE as RECEIPT21_5_, location6_.OPERATOR_REF_REQ_IND as OPERATO22_5_, location6_.DIRECT_SHIP_CITY as DIRECT_23_5_, location6_.BANK_IDENTIFIER_NUMBER as BANK_ID24_5_, location6_.BANK_ACCOUNT_NUMBER as BANK_AC25_5_, location6_.SEQ_TICKET_REF_FLG as SEQ_TIC26_5_, location6_.SEQ_TICKET_REF_PREFIX as SEQ_TIC27_5_, location6_.NOTES_ID as NOTES_ID5_, location6_.ADDR_LINE4 as ADDR_LINE45_, location6_.SUBTYPE as SUBTYPE5_, location6_.DISPLAY_ON_MAP_FLG as DISPLAY31_5_, location6_.TERTIARY_JURISD_CODE as TERTIAR32_5_, location6_.PROV_ST_JURISD_CODE as PROV_ST33_5_, location6_.COUNTRY_JURISD_CODE as COUNTRY34_5_, location6_.TYPE as TYPE5_, jurisdicti7_.JURISDICTION_CODE as JURISDIC1_6_, jurisdicti7_.NAME as NAME6_, jurisdicti7_.TYPE as TYPE6_, jurisdicti7_.SENIOR_JURISDICTION_CODE as SENIOR_J4_6_, jurisdicti7_.UPDATE_USER as UPDATE_U5_6_, jurisdicti7_.UPDATE_DATE as UPDATE_D6_6_, jurisdicti7_.PHONE_EDIT_MASK as PHONE_ED7_6_, jurisdicti7_.DFLT_CURRENCY_CODE as DFLT_CUR8_6_, jurisdicti7_.STATE_GEOCODE as STATE_GE9_6_, jurisdicti7_.COUNTY_GEOCODE as COUNTY_10_6_, jurisdicti7_.CITY_GEOCODE as CITY_GE11_6_, jurisdicti7_.HOME_RULE_FLG as HOME_RU12_6_, jurisdicti7_.DEFAULT_VOL_UOM as DEFAULT13_6_, jurisdicti7_.DEFAULT_ENERGY_UOM as DEFAULT14_6_, jurisdicti8_.JURISDICTION_CODE as JURISDIC1_7_, jurisdicti8_.NAME as NAME7_, jurisdicti8_.TYPE as TYPE7_, jurisdicti8_.SENIOR_JURISDICTION_CODE as SENIOR_J4_7_, jurisdicti8_.UPDATE_USER as UPDATE_U5_7_, jurisdicti8_.UPDATE_DATE as UPDATE_D6_7_, jurisdicti8_.PHONE_EDIT_MASK as PHONE_ED7_7_, jurisdicti8_.DFLT_CURRENCY_CODE as DFLT_CUR8_7_, jurisdicti8_.STATE_GEOCODE as STATE_GE9_7_, jurisdicti8_.COUNTY_GEOCODE as COUNTY_10_7_, jurisdicti8_.CITY_GEOCODE as CITY_GE11_7_, jurisdicti8_.HOME_RULE_FLG as HOME_RU12_7_, jurisdicti8_.DEFAULT_VOL_UOM as DEFAULT13_7_, jurisdicti8_.DEFAULT_ENERGY_UOM as DEFAULT14_7_, jurisdicti9_.JURISDICTION_CODE as JURISDIC1_8_, jurisdicti9_.NAME as NAME8_, jurisdicti9_.TYPE as TYPE8_, jurisdicti9_.SENIOR_JURISDICTION_CODE as SENIOR_J4_8_, jurisdicti9_.UPDATE_USER as UPDATE_U5_8_, jurisdicti9_.UPDATE_DATE as UPDATE_D6_8_, jurisdicti9_.PHONE_EDIT_MASK as PHONE_ED7_8_, jurisdicti9_.DFLT_CURRENCY_CODE as DFLT_CUR8_8_, jurisdicti9_.STATE_GEOCODE as STATE_GE9_8_, jurisdicti9_.COUNTY_GEOCODE as COUNTY_10_8_, jurisdicti9_.CITY_GEOCODE as CITY_GE11_8_, jurisdicti9_.HOME_RULE_FLG as HOME_RU12_8_, jurisdicti9_.DEFAULT_VOL_UOM as DEFAULT13_8_, jurisdicti9_.DEFAULT_ENERGY_UOM as DEFAULT14_8_, locationty10_.TYPE as TYPE9_, locationty10_.DESCRIPTION as DESCRIPT2_9_, locationty10_.LONG_DESCRIPTION as LONG_DES3_9_, locationty10_.DOI_ALLOWED_FLG as DOI_ALLO4_9_, locationty10_.DFLT_DISPLAY_ON_MAP_FLG as DFLT_DIS5_9_, locationty10_.SUBTYPES_AVAILABLE_FLG as SUBTYPES6_9_, locationty10_.INACTIVE_DATE as INACTIVE7_9_, sourceLocation.ADDR_ID as ADDR_ID10_, sourceLocation.ADDR_LINE1 as ADDR_LINE110_, sourceLocation.ADDR_LINE2 as ADDR_LINE210_, sourceLocation.ADDR_LINE3 as ADDR_LINE310_, sourceLocation.CITY as CITY10_, sourceLocation.POST_ZIP_CODE as POST_ZIP6_10_, sourceLocation.X_COORD as X_COORD10_, sourceLocation.Y_COORD as Y_COORD10_, sourceLocation.Z_COORD as Z_COORD10_, sourceLocation.DESCRIPTION as DESCRIP10_10_, sourceLocation.EDI_ACCOUNT as EDI_ACC11_10_, sourceLocation.INACTIVE_DATE as INACTIV12_10_, sourceLocation.UPDATE_USER as UPDATE_13_10_, sourceLocation.UPDATE_DATE as UPDATE_14_10_, sourceLocation.TIME_ZONE as TIME_ZONE10_, sourceLocation.CUTOFF_TIME as CUTOFF_16_10_, sourceLocation.FACILITY_BALANCE_DEFAULT as FACILIT17_10_, sourceLocation.OPERATOR_ID as OPERATO18_10_, sourceLocation.DFLT_UOM_CODE as DFLT_UO19_10_, sourceLocation.DFLT_TEMP_UOM_CODE as DFLT_TE20_10_, sourceLocation.RECEIPT_VOLUME_TYPE as RECEIPT21_10_, sourceLocation.OPERATOR_REF_REQ_IND as OPERATO22_10_, sourceLocation.DIRECT_SHIP_CITY as DIRECT_23_10_, sourceLocation.BANK_IDENTIFIER_NUMBER as BANK_ID24_10_, sourceLocation.BANK_ACCOUNT_NUMBER as BANK_AC25_10_, sourceLocation.SEQ_TICKET_REF_FLG as SEQ_TIC26_10_, sourceLocation.SEQ_TICKET_REF_PREFIX as SEQ_TIC27_10_, sourceLocation.NOTES_ID as NOTES_ID10_, sourceLocation.ADDR_LINE4 as ADDR_LINE410_, sourceLocation.SUBTYPE as SUBTYPE10_, sourceLocation.DISPLAY_ON_MAP_FLG as DISPLAY31_10_, sourceLocation.TERTIARY_JURISD_CODE as TERTIAR32_10_, sourceLocation.PROV_ST_JURISD_CODE as PROV_ST33_10_, sourceLocation.COUNTRY_JURISD_CODE as COUNTRY34_10_, sourceLocation.TYPE as TYPE10_, jurisdicti12_.JURISDICTION_CODE as JURISDIC1_11_, jurisdicti12_.NAME as NAME11_, jurisdicti12_.TYPE as TYPE11_, jurisdicti12_.SENIOR_JURISDICTION_CODE as SENIOR_J4_11_, jurisdicti12_.UPDATE_USER as UPDATE_U5_11_, jurisdicti12_.UPDATE_DATE as UPDATE_D6_11_, jurisdicti12_.PHONE_EDIT_MASK as PHONE_ED7_11_, jurisdicti12_.DFLT_CURRENCY_CODE as DFLT_CUR8_11_, jurisdicti12_.STATE_GEOCODE as STATE_GE9_11_, jurisdicti12_.COUNTY_GEOCODE as COUNTY_10_11_, jurisdicti12_.CITY_GEOCODE as CITY_GE11_11_, jurisdicti12_.HOME_RULE_FLG as HOME_RU12_11_, jurisdicti12_.DEFAULT_VOL_UOM as DEFAULT13_11_, jurisdicti12_.DEFAULT_ENERGY_UOM as DEFAULT14_11_, jurisdictionByProvStJurisdCode.JURISDICTION_CODE as JURISDIC1_12_, jurisdictionByProvStJurisdCode.NAME as NAME12_, jurisdictionByProvStJurisdCode.TYPE as TYPE12_, jurisdictionByProvStJurisdCode.SENIOR_JURISDICTION_CODE as SENIOR_J4_12_, jurisdictionByProvStJurisdCode.UPDATE_USER as UPDATE_U5_12_, jurisdictionByProvStJurisdCode.UPDATE_DATE as UPDATE_D6_12_, jurisdictionByProvStJurisdCode.PHONE_EDIT_MASK as PHONE_ED7_12_, jurisdictionByProvStJurisdCode.DFLT_CURRENCY_CODE as DFLT_CUR8_12_, jurisdictionByProvStJurisdCode.STATE_GEOCODE as STATE_GE9_12_, jurisdictionByProvStJurisdCode.COUNTY_GEOCODE as COUNTY_10_12_, jurisdictionByProvStJurisdCode.CITY_GEOCODE as CITY_GE11_12_, jurisdictionByProvStJurisdCode.HOME_RULE_FLG as HOME_RU12_12_, jurisdictionByProvStJurisdCode.DEFAULT_VOL_UOM as DEFAULT13_12_, jurisdictionByProvStJurisdCode.DEFAULT_ENERGY_UOM as DEFAULT14_12_, jurisdicti14_.JURISDICTION_CODE as JURISDIC1_13_, jurisdicti14_.NAME as NAME13_, jurisdicti14_.TYPE as TYPE13_, jurisdicti14_.SENIOR_JURISDICTION_CODE as SENIOR_J4_13_, jurisdicti14_.UPDATE_USER as UPDATE_U5_13_, jurisdicti14_.UPDATE_DATE as UPDATE_D6_13_, jurisdicti14_.PHONE_EDIT_MASK as PHONE_ED7_13_, jurisdicti14_.DFLT_CURRENCY_CODE as DFLT_CUR8_13_, jurisdicti14_.STATE_GEOCODE as STATE_GE9_13_, jurisdicti14_.COUNTY_GEOCODE as COUNTY_10_13_, jurisdicti14_.CITY_GEOCODE as CITY_GE11_13_, jurisdicti14_.HOME_RULE_FLG as HOME_RU12_13_, jurisdicti14_.DEFAULT_VOL_UOM as DEFAULT13_13_, jurisdicti14_.DEFAULT_ENERGY_UOM as DEFAULT14_13_, locationty15_.TYPE as TYPE14_, locationty15_.DESCRIPTION as DESCRIPT2_14_, locationty15_.LONG_DESCRIPTION as LONG_DES3_14_, locationty15_.DOI_ALLOWED_FLG as DOI_ALLO4_14_, locationty15_.DFLT_DISPLAY_ON_MAP_FLG as DFLT_DIS5_14_, locationty15_.SUBTYPES_AVAILABLE_FLG as SUBTYPES6_14_, locationty15_.INACTIVE_DATE as INACTIVE7_14_, stakeholde16_.STKHLDR_ID as STKHLDR_ID15_, stakeholde16_.TYPE as TYPE15_, stakeholde16_.NAME_LAST_ORG as NAME_LAS3_15_, stakeholde16_.NAME_FIRST_DEPT as NAME_FIR4_15_, stakeholde16_.NAME_OTHER as NAME_OTHER15_, stakeholde16_.NOTES_ID as NOTES_ID15_, stakeholde16_.INACTIVE_DATE as INACTIVE7_15_, stakeholde16_.UPDATE_USER as UPDATE_U8_15_, stakeholde16_.UPDATE_DATE as UPDATE_D9_15_, stakeholde16_.CREATE_DATE as CREATE_10_15_, stakeholde16_.LEGAL_ENTITY_FLG as LEGAL_E11_15_, stakeholde16_.LEGAL_ENTITY_ID as LEGAL_E12_15_, stakeholde16_.UNALLOCATED_PAYMENT_TOTAL as UNALLOC13_15_, stakeholde16_.LAST_PAYMENT_ID as LAST_PA14_15_, stakeholde16_.LAST_PAYMENT_DATE as LAST_PA15_15_, stakeholde16_.NON_RESIDENT_FLG as NON_RES16_15_, item.ITEM_CODE as ITEM_CODE16_, item.DESCRIPTION_LONG as DESCRIPT2_16_, item.DESCRIPTION_SHORT as DESCRIPT3_16_, item.LOCATION_CONTROL_FLG as LOCATION4_16_, item.DATE_AVAILABLE as DATE_AVA5_16_, item.INACTIVE_DATE as INACTIVE6_16_, item.UPDATE_USER as UPDATE_U7_16_, item.UPDATE_DATE as UPDATE_D8_16_, item.TEMP_CORRECTION_FACTOR as TEMP_COR9_16_, item.REPORT_SEQUENCE as REPORT_10_16_, item.DISPLAY_ORDER as DISPLAY11_16_, item.INVENTORY_KEPT_FLG as INVENTO12_16_, item.ITEM_GROUP_CODE as ITEM_GR13_16_, item.CLASS_CODE as CLASS_CODE16_, item.TYPE as TYPE16_, item.SHIP_MODE as SHIP_MODE16_, itemgroup18_.ITEM_GROUP_CODE as ITEM_GRO1_17_, itemgroup18_.DESCRIPTION as DESCRIPT2_17_, itemgroup18_.ALT_UOM_FLG as ALT_UOM_3_17_, itemgroup18_.MODIFIER_TYPE1 as MODIFIER4_17_, itemgroup18_.MODIFIER_TYPE2 as MODIFIER5_17_, itemgroup18_.MODIFIER_TYPE3 as MODIFIER6_17_, itemgroup18_.MOD1_LIST_FLG as MOD1_LIS7_17_, itemgroup18_.MOD2_LIST_FLG as MOD2_LIS8_17_, itemgroup18_.MOD3_LIST_FLG as MOD3_LIS9_17_, itemgroup18_.ACCT_AMORT_TYPE as ACCT_AM10_17_, itemgroup18_.ACCT_AMORT_RATE as ACCT_AM11_17_, itemgroup18_.INACTIVE_DATE as INACTIV12_17_, itemgroup18_.UPDATE_USER as UPDATE_13_17_, itemgroup18_.UPDATE_DATE as UPDATE_14_17_, itemgroup18_.REPORT_SEQUENCE as REPORT_15_17_, itemgroup18_.SCHEDULE_TYPE_CODE as SCHEDUL16_17_, itemgroup18_.FORECAST_ALLOC_METHOD_CDE as FORECAS17_17_, itemgroup18_.GRAVITY_COL_VISIBLE_IND as GRAVITY18_17_, itemgroup18_.USE_ORDERS_ON_RISK_RPTS as USE_ORD19_17_, itemgroup18_.VAR_HISTORY_PERIOD as VAR_HIS20_17_, itemgroup18_.UNIT_FLG as UNIT_FLG17_, itemgroup18_.TRAILER_FLG as TRAILER22_17_, itemgroup18_.M2M_HISTORY_PERIOD as M2M_HIS23_17_, itemgroup18_.VAR_HOLDING_PERIOD as VAR_HOL24_17_, itemgroup18_.VAR_SEASONALITY_FLG as VAR_SEA25_17_, itemgroup18_.VAR_START_LEAD as VAR_STA26_17_, itemgroup18_.VAR_END_LAG as VAR_END27_17_, itemgroup18_.ACT_VOL_REQUIRED_REG_CODE as ACT_VOL28_17_, itemgroup18_.ACT_VOL_DISALLOWED_REG_CODE as ACT_VOL29_17_, itemgroup18_.VOL_ENABLED_REG_CODE as VOL_ENA30_17_, itemgroup18_.VAR_HISTORY_INTERVAL_UOM_CODE as VAR_HIS31_17_, itemgroup18_.SKU_UOM_CODE as SKU_UOM32_17_, itemgroup18_.ACCT_CODE as ACCT_CODE17_, uom19_.UOM_CODE as UOM_CODE18_, uom19_.DESCRIPTION as DESCRIPT2_18_, uom19_.TYPE as TYPE18_, uom19_.INACTIVE_DATE as INACTIVE4_18_, uom19_.UPDATE_USER as UPDATE_U5_18_, uom19_.UPDATE_DATE as UPDATE_D6_18_, uom19_.SI as SI18_, uom19_.EDIT_MASK as EDIT_MASK18_, uom19_.SHORT_DESCRIPTION as SHORT_DE9_18_, uom20_.UOM_CODE as UOM_CODE19_, uom20_.DESCRIPTION as DESCRIPT2_19_, uom20_.TYPE as TYPE19_, uom20_.INACTIVE_DATE as INACTIVE4_19_, uom20_.UPDATE_USER as UPDATE_U5_19_, uom20_.UPDATE_DATE as UPDATE_D6_19_, uom20_.SI as SI19_, uom20_.EDIT_MASK as EDIT_MASK19_, uom20_.SHORT_DESCRIPTION as SHORT_DE9_19_, account21_.ACCT_CODE as ACCT_CODE20_, account21_.DESCRIPTION as DESCRIPT2_20_, account21_.TYPE as TYPE20_, account21_.SUBLEDGER_TYPE as SUBLEDGE4_20_, account21_.TAX_VALUE as TAX_VALUE20_, account21_.RESP_CENTER_REQ_FLG as RESP_CEN6_20_, account21_.ADDR_REQ_FLG as ADDR_REQ7_20_, account21_.STKHLDR_REQ_FLG as STKHLDR_8_20_, account21_.INVENTORY_REQ_FLG as INVENTOR9_20_, account21_.INACTIVE_DATE as INACTIV10_20_, account21_.UPDATE_DATE as UPDATE_11_20_, account21_.UPDATE_USER as UPDATE_12_20_, account21_.QTY_REQ_FLG as QTY_REQ13_20_, account21_.COST_CENTER_REQ_FLG as COST_CE14_20_, account21_.AGRMT_REQ_FLG as AGRMT_R15_20_, account21_.ACTIVITY_REQ_FLG as ACTIVIT16_20_, account21_.COST_ELMT_REQ_FLG as COST_EL17_20_, account21_.INCOME_STATEMENT_FLG as INCOME_18_20_, account21_.FS_SIGN_REVERSAL_FLG as FS_SIGN19_20_, account21_.FS_TOTAL_ACCT_CODE as FS_TOTA20_20_, account21_.FS_TOTAL_UNDERLINE as FS_TOTA21_20_, account21_.NEXT_CHEQUE_NUMBER as NEXT_CH22_20_, account21_.BUSINESS_UNIT_ID as BUSINES23_20_, account21_.SUPER_USER_ACCT_FLG as SUPER_U24_20_, account21_.SHOW_HEADING_FLG as SHOW_HE25_20_, account21_.ROLL_UP_LEVEL as ROLL_UP26_20_, account21_.GENERATE_PAYMENT_INTERFACE_FLG as GENERAT27_20_, account21_.BANK_IDENTIFIER_NUMBER as BANK_ID28_20_, account21_.BANK_ACCOUNT_NUMBER as BANK_AC29_20_, account21_.ITEM_REQ_FLG as ITEM_RE30_20_, account21_.CURRENCY_CODE as CURRENC31_20_, itemclass22_.CLASS_CODE as CLASS_CODE21_, itemclass22_.DESCRIPTION as DESCRIPT2_21_, itemclass22_.TYPE as TYPE21_, itemclass22_.INACTIVE_DATE as INACTIVE4_21_, itemclass22_.UPDATE_USER as UPDATE_U5_21_, itemclass22_.UPDATE_DATE as UPDATE_D6_21_, itemtype23_.TYPE as TYPE22_, itemtype23_.INVENTORY_BALANCE_FLG as INVENTOR2_22_, itemtype23_.DESCRIPTION as DESCRIPT3_22_, itemtype23_.CREATE_DATE as CREATE_D4_22_, itemtype23_.CREATE_USER as CREATE_U5_22_, itembleh.SHIP_MODE as SHIP_MODE23_, itembleh.DESCRIPTION as DESCRIPT2_23_, itembleh.UOM_CODE as UOM_CODE23_, itembleh.LAYCAN_ENABLED_FLG as LAYCAN_E4_23_ from PRICE_H this left outer join PRICE_CATEGORY pricecateg1_ on this.PRICE_CAT_CODE=pricecateg1_.PRICE_CAT_CODE left outer join PRICE_CATEGORY_RISK_TYPE pricecateg2_ on pricecateg1_.RISK_TYPE=pricecateg2_.TYPE left outer join CURRENCY_H currency3_ on this.CURRENCY_CODE=currency3_.CURRENCY_CODE left outer join JURISDICTION jurisdicti4_ on currency3_.COUNTRY_CODE=jurisdicti4_.JURISDICTION_CODE left outer join UOM uom5_ on this.UOM_CODE=uom5_.UOM_CODE left outer join ADDRESS location6_ on this.DELIVERY_ADDR_ID=location6_.ADDR_ID left outer join JURISDICTION jurisdicti7_ on location6_.TERTIARY_JURISD_CODE=jurisdicti7_.JURISDICTION_CODE left outer join JURISDICTION jurisdicti8_ on location6_.PROV_ST_JURISD_CODE=jurisdicti8_.JURISDICTION_CODE left outer join JURISDICTION jurisdicti9_ on location6_.COUNTRY_JURISD_CODE=jurisdicti9_.JURISDICTION_CODE left outer join ADDRESS_TYPE locationty10_ on location6_.TYPE=locationty10_.TYPE inner join ADDRESS sourceLocation on this.SOURCE_ADDR_ID=sourceLocation.ADDR_ID left outer join JURISDICTION jurisdicti12_ on sourceLocation.TERTIARY_JURISD_CODE=jurisdicti12_.JURISDICTION_CODE inner join JURISDICTION jurisdictionByProvStJurisdCode on sourceLocation.PROV_ST_JURISD_CODE=jurisdictionByProvStJurisdCode.JURISDICTION_CODE left outer join JURISDICTION jurisdicti14_ on sourceLocation.COUNTRY_JURISD_CODE=jurisdicti14_.JURISDICTION_CODE left outer join ADDRESS_TYPE locationty15_ on sourceLocation.TYPE=locationty15_.TYPE left outer join STKHLDR stakeholde16_ on this.CARRIER_ID=stakeholde16_.STKHLDR_ID inner join ITEM item on this.ITEM_CODE=item.ITEM_CODE left outer join ITEM_GROUP itemgroup18_ on item.ITEM_GROUP_CODE=itemgroup18_.ITEM_GROUP_CODE left outer join UOM uom19_ on itemgroup18_.VAR_HISTORY_INTERVAL_UOM_CODE=uom19_.UOM_CODE left outer join UOM uom20_ on itemgroup18_.SKU_UOM_CODE=uom20_.UOM_CODE left outer join ACCOUNT account21_ on itemgroup18_.ACCT_CODE=account21_.ACCT_CODE left outer join ITEM_CLASS itemclass22_ on item.CLASS_CODE=itemclass22_.CLASS_CODE left outer join ITEM_TYPE itemtype23_ on item.TYPE=itemtype23_.TYPE inner join SHIP_MODE itembleh on item.SHIP_MODE=itembleh.SHIP_MODE where jurisdictionByProvStJurisdCode.JURISDICTION_CODE=? and lower(sourceLocation.ADDR_LINE1) like ? and lower(item.DESCRIPTION_LONG) like ? ) where rownum <= ?

Notice that any of the aliases on the many-to-ones I create are translated into INNER JOINs. I was expecting I could do the same with a many-to-one that just happened to be part of my primary key but it seems to be ignored. Since I can create the inner join through HQL I can only guess that something is happening in the Criteria API or I am on dope. Right now that could be a 50/50 chance :)

Now, before anyone says anything, don't get me started on this legacy datamodel :P

Sorry for the necro but was this ever resolved? I see references all over Google for such problems where createAlias doesn't work in conjunction with composite keys, but no real solution :(

Thanks!