Issue in Query Formation using 5.6.7 jar

Hibernate ORM
1

Hi all, I am facing an issue in the query formation in 5.6.7 hibernate version.

Scenario: I have an object named “ManagedObject” and there are 45 children for this object. These children are further having its own sub tables. One of its children name is “Table1”. I am trying to fetch a “Table1” object using “name” which is being a natural-id in “ManagedObject” using below code.

final NaturalIdLoadAccess naturalIdLoadAccess = session.byNaturalId(c);
final T result = naturalIdLoadAccess.using(attributeName, value).load();

c is the class (Table1.class)
attributeName = “name”
value = “MO1”

The query which is formed using hibernate 5.6.7 jar for this fetching operation is huge and it does “left outer join” of all 45 ManagedObject tables and its sub tables which sums up to 94 "left outer join"s in the query. Since MySQL doesn’t support more than 61 joins in a query, it throws “java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join”. This also hits the performance badly.

For the same functionality, the query formed using hibernate 5.1.8 jar was crisp. It doesn’t join all the children tables and its sub tables. “Table1” object is fetched using a single query and the properties from it’s subtables are fetched using separate queries.

Why is the query not optimal in 5.6.7 version ? Please suggest some ways to resolve the issue.

Queries are available in first 2 comments below. Please check

2

Query formed using 5.1.8 jar:

Hibernate: select managedobj_.MOID as MOID1_235_ from ManagedObject managedobj_ where managedobj_.NAME=?
Hibernate: select transporte0_.MOID as MOID1_235_0_, transporte0_1_.NAME as NAME2_235_0_, transporte0_1_.DISPLAYNAME as DISPLAYN3_235_0_, transporte0_1_.TYPE as TYPE4_235_0_, transporte0_1_.MANAGED as MANAGED5_235_0_, transporte0_1_.STATUS as STATUS6_235_0_, transporte0_1_.PARENTKEY as PARENTKE7_235_0_, transporte0_1_.PARENTMOID as PARENTMO8_235_0_, transporte0_1_.CREATEDTIME as CREATEDT9_235_0_, transporte0_1_.LASTMODIFIEDTIME as LASTMOD10_235_0_, transporte0_1_.NBINAME as NBINAME11_235_0_, transporte0_1_.CLASSNAME as CLASSNA12_235_0_, transporte0_.userLabel as userLabe2_496_0_, transporte0_.nativeEmsName as nativeEm3_496_0_, transporte0_.owner as owner4_496_0_, transporte0_.softwareVersion as software5_496_0_, transporte0_.emsType as emsType6_496_0_, transporte1_.MOID as MOID1_497_1_, transporte1_.label as label3_497_1_, transporte1_.nmsPrimaryAddress as nmsPrima4_497_1_, transporte1_.nmsSecondaryAddress as nmsSecon5_497_1_, transporte1_.manufacturer as manufact6_497_1_, transporte1_.zoneType as zoneType7_497_1_, transporte1_.Discriminator as Discrimi2_497_1_ from Table1 transporte0_ inner join ManagedObject transporte0_1_ on transporte0_.MOID=transporte0_1_.MOID left outer join Table1UserData transporte1_ on transporte0_.MOID=transporte1_.MOID where transporte0_.MOID=?

3

Query formed using 5.6.7 jar:

Hibernate: select mo0_.MOID as moid1_235_41_, mo0_.NAME as name2_235_41_, mo0_.DISPLAYNAME as displayn3_235_41_, mo0_.TYPE as type4_235_41_, mo0_.MANAGED as managed5_235_41_, mo0_.STATUS as status6_235_41_, mo0_.PARENTKEY as parentke7_235_41_, mo0_.PARENTMOID as parentmo8_235_41_, mo0_.CREATEDTIME as createdt9_235_41_, mo0_.LASTMODIFIEDTIME as lastmod10_235_41_, mo0_1_.userLabel as userlabe2_495_41_, mo0_1_.nativeEmsName as nativeem3_495_41_, mo0_1_.emsType as emstype6_495_41_, mo0_2_.productName as productn7_242_41_, mo0_2_.communicationState as communic8_242_41_, mo0_2_.emsInSyncState as emsinsyn9_242_41_, mo0_2_.address as address11_242_41_, mo0_2_.manufacturer as manufac12_242_41_, mo0_2_.inventoryStatus as invento14_242_41_, mo0_2_.sourceOfME as sourceo15_242_41_, mo0_3_.transmissionParams as transmis6_351_41_, mo0_3_.protectionAssociation as protecti7_351_41_, mo0_3_.ptpId as ptpid9_351_41_, mo0_3_.sourceOfPTP as sourceo10_351_41_, mo0_3_.inventoryStatus as invento11_351_41_, mo0_4_.userLabel as userlabe2_164_41_, mo0_4_.owner as owner4_164_41_, mo0_4_.inventoryStatus as invento13_164_41_, mo0_5_.userLabel as userlabe2_51_41_, mo0_5_.SourceOfCTP as sourceo11_51_41_, mo0_5_.SourceOfCTPDerivation as sourceo12_51_41_, mo0_6_.userLabel as userlabe2_121_41_, mo0_6_.nativeEmsName as nativeem3_121_41_, mo0_6_.owner as owner4_121_41_, mo0_7_.nativeEmsName as nativeem3_119_41_, mo0_7_.expectedEquipmentObjectType as expected7_119_41_, mo0_7_.installedEquipmentObjectType as installe8_119_41_, mo0_7_.installedPartNumber as installe9_119_41_, mo0_8_.userLabel as userlabe2_482_41_, mo0_8_.nativeEmsName as nativeem3_482_41_, mo0_8_.sourceOfTL as sourceo11_482_41_, mo0_8_.inventoryStatus as invento12_482_41_, mo0_8_.tlType as tltype13_482_41_, mo0_8_.meVneTLType as mevnetl14_482_41_, mo0_9_.supportedRates as supporte6_285_41_, mo0_10_.userLabel as userlabe2_336_41_, mo0_10_.nativeEmsName as nativeem3_336_41_, mo0_10_.owner as owner4_336_41_, mo0_10_.protectionGroupType as protecti5_336_41_, mo0_11_.userLabel as userlabe2_114_41_, mo0_12_.userLabel as userlabe2_537_41_, mo0_12_.nativeEmsName as nativeem3_537_41_, mo0_12_.location as location4_537_41_, mo0_12_.remarks as remarks5_537_41_, mo0_12_.inventoryStatus as inventor6_537_41_, mo0_13_.userLabel as userlabe2_532_41_, mo0_14_.label as label2_397_41_, mo0_14_.layerRate as layerrat3_397_41_, mo0_14_.sourcePTP as sourcept4_397_41_, mo0_14_.destinationPTP as destinat5_397_41_, mo0_15_.SOURCEOFSNC as sourceo14_427_41_, mo0_15_.SNCOperation as sncoper15_427_41_, mo0_16_.state as state2_11_41_, mo0_16_.customerName as customer3_11_41_, mo0_16_.circuitId as circuiti4_11_41_, mo0_16_.label as label5_11_41_, mo0_16_.dummyStatus as dummyst12_11_41_, mo0_16_.sourceOfService as sourceo13_11_41_, mo0_16_.stitchingStatus as stitchi14_11_41_, mo0_17_.userLabel as userlabe2_157_41_, mo0_18_.userLabel as userlabe2_159_41_, mo0_18_.nativeEmsName as nativeem3_159_41_, mo0_18_.owner as owner4_159_41_, mo0_18_.adminState as adminst11_159_41_, mo0_18_.SOURCEOFFDFR as sourceo12_159_41_, mo0_18_.FDFrOperation as fdfrope13_159_41_, mo0_18_.dummyStatus as dummyst14_159_41_, mo0_19_.userLabel as userlabe2_238_41_, mo0_19_.nativeEmsName as nativeem3_238_41_, mo0_19_.owner as owner4_238_41_, mo0_19_.networkAccessDomain as networka5_238_41_, mo0_19_.flexible as flexible6_238_41_, mo0_20_.reversionMode as reversio7_504_41_, mo0_20_.layerRate as layerrat8_504_41_, mo0_20_.inventoryStatus as inventor9_504_41_, mo0_21_.userLabel as userlabe2_209_41_, mo0_21_.nativeEmsName as nativeem3_209_41_, mo0_21_.workerTP as workertp8_209_41_, mo0_21_.externalCommand as external9_209_41_, mo0_22_.userLabel as userlabe2_494_41_, mo0_22_.nativeEmsName as nativeem3_494_41_, mo0_22_.owner as owner4_494_41_, mo0_22_.trailNtwProtectionType as trailntw9_494_41_, mo0_22_.protectionGroupAName as protect10_494_41_, mo0_22_.protectionGroupZName as protect11_494_41_, mo0_22_.apsFunction as apsfunc12_494_41_, mo0_22_.networkAccessDomain as network13_494_41_, mo0_22_.tnpId as tnpid14_494_41_, mo0_22_.SourceOfTNPDerivation as sourceo18_494_41_, mo0_23_.userLabel as userlabe2_542_41_, mo0_23_.nativeEmsName as nativeem3_542_41_, mo0_23_.owner as owner4_542_41_, mo0_23_.protectionGroupType as protecti5_542_41_, mo0_23_.layerRate as layerrat8_542_41_, mo0_23_.inventoryStatus as inventor9_542_41_, mo0_24_.userLabel as userlabe2_270_41_, mo0_24_.nativeEmsName as nativeem3_270_41_, mo0_24_.reversionMode as reversio7_270_41_, mo0_25_.ODF_TYPE as odf_type2_319_41_, mo0_25_.ODF_CAPACITY as odf_capa3_319_41_, mo0_25_.SITE_NAME as site_nam4_319_41_, mo0_26_.SplittetType as splittet2_453_41_, mo0_27_.Label as label2_450_41_, mo0_27_.Capacity as capacity3_450_41_, mo0_28_.AENDOBJECTID as aendobje2_334_41_, mo0_28_.ZENDOBJECTID as zendobje3_334_41_, mo0_28_.AENDOBJECTTYPE as aendobje4_334_41_, mo0_28_.ZENDOBJECTTYPE as zendobje5_334_41_, mo0_28_.APORTID as aportid6_334_41_, mo0_28_.ZPORTID as zportid7_334_41_, mo0_28_.APORTROLE as aportro10_334_41_, mo0_28_.ZPORTROLE as zportro11_334_41_, mo0_29_.userLabel as userlabe2_420_41_, mo0_29_.BANDWIDTH as bandwidt3_420_41_, mo0_29_.SERVICE_SOURCE as service_8_420_41_, mo0_29_.ACTIVATED_DATE as activate9_420_41_, mo0_29_.DEACTIVATED_DATE as deactiv10_420_41_, mo0_30_.state as state2_37_41_, mo0_30_.direction as directio3_37_41_, mo0_30_.stitchingStatus as stitchin9_37_41_, mo0_30_.derivedServiceType as derived10_37_41_, mo0_31_.userLabel as userlabe2_409_41_, mo0_31_.zEndME as zendme5_409_41_, mo0_32_.userLabel as userlabe2_406_41_, mo0_32_.nativeEmsName as nativeem3_406_41_, mo0_32_.owner as owner4_406_41_, mo0_32_.bandwidthLockout as bandwid10_406_41_, mo0_32_.protocolState as protoco11_406_41_, mo0_32_.containingRoutingLink as contain12_406_41_, mo0_33_.sourceOfVL as sourceof5_530_41_, mo0_33_.inventoryStatus as inventor6_530_41_, mo0_33_.aendNEId as aendneid7_530_41_, mo0_33_.zendNEId as zendneid8_530_41_, mo0_34_.LABEL as label2_485_41_, mo0_34_.TOPOLOGYTYPE as topology3_485_41_, mo0_34_.TOPOLOGYLEVEL as topology4_485_41_, mo0_34_.SOURCEOFTOPOLOGY as sourceof5_485_41_, mo0_35_.userLabel as userlabe2_25_41_, mo0_35_.zEndCTPMoid as zendctpm8_25_41_, mo0_35_.roleOfZEndCTP as roleofze9_25_41_, mo0_35_.aEndPTPName as aendptp10_25_41_, mo0_35_.aEndPTPMoid as aendptp11_25_41_, mo0_35_.zEndPTPName as zendptp12_25_41_, mo0_35_.zEndPTPMoid as zendptp13_25_41_, mo0_35_.rate as rate18_25_41_, mo0_35_.connectionDirection as connect19_25_41_, mo0_35_.connectionType as connect20_25_41_, mo0_35_.connectionTechnology as connect21_25_41_, mo0_35_.sourceOfCFC as sourceo22_25_41_, mo0_36_.userLabel as userlabe2_310_41_, mo0_36_.roleOfZEndCTP as roleofze9_310_41_, mo0_36_.aEndPTPName as aendptp10_310_41_, mo0_36_.aEndPTPMoid as aendptp11_310_41_, mo0_36_.zEndPTPName as zendptp12_310_41_, mo0_36_.connectionDirection as connect19_310_41_, mo0_36_.connectionType as connect20_310_41_, mo0_36_.connectionTechnology as connect21_310_41_, mo0_36_.sourceOfNFC as sourceo22_310_41_, mo0_37_.userLabel as userlabe2_190_41_, mo0_38_.userLabel as userlabe2_194_41_, mo0_38_.nativeEmsName as nativeem3_194_41_, mo0_38_.MENAME as mename4_194_41_, mo0_38_.EXPORT_ROUTETARGET as export_r8_194_41_, mo0_39_.userLabel as userlabe2_447_41_, mo0_39_.nativeEmsName as nativeem3_447_41_, mo0_39_.direction as directio7_447_41_, mo0_39_.rates as rates8_447_41_, mo0_39_.cost as cost9_447_41_, mo0_39_.linkCapacities as linkcap10_447_41_, mo0_39_.srlgs as srlgs13_447_41_, mo0_41_.userLabel as userlabe2_232_41_, mo0_41_.vendor as vendor6_232_41_, mo0_42_.userLabel as userlabe2_231_41_, mo0_42_.nativeEmsName as nativeem3_231_41_, mo0_42_.owner as owner4_231_41_, mo0_42_.vendor as vendor9_231_41_, mo0_43_.userLabel as userlabe2_234_41_, mo0_43_.owner as owner4_234_41_, mo0_43_.tpName as tpname5_234_41_, mo0_44_.topologyLabel as topology2_278_41_, mo0_44_.topologyType as topology3_278_41_, mo0_45_.label as label2_300_41_, mo0_45_.networkProtectionType as networkp3_300_41_, mo0_45_.STITCHING_STATUS as stitchin4_300_41_, mo0_45_.lastModifiedReason as lastmodi5_300_41_, case when mo0_1_.MOID is not null then 1 when mo0_2_.MOID is not null then 2 when mo0_3_.MOID is not null then 3 when mo0_4_.MOID is not null then 4 when mo0_5_.MOID is not null then 5 when mo0_6_.MOID is not null then 6 when mo0_7_.MOID is not null then 7 when mo0_8_.MOID is not null then 8 when mo0_9_.MOID is not null then 9 when mo0_10_.MOID is not null then 10 when mo0_11_.MOID is not null then 11 when mo0_12_.MOID is not null then 12 when mo0_13_.MOID is not null then 13 when mo0_14_.MOID is not null then 14 when mo0_15_.MOID is not null then 15 when mo0_16_.MOID is not null then 16 when mo0_17_.MOID is not null then 17 when mo0_18_.MOID is not null then 18 when mo0_19_.MOID is not null then 19 when mo0_20_.MOID is not null then 20 when mo0_21_.MOID is not null then 21 when mo0_22_.MOID is not null then 22 when mo0_23_.MOID is not null then 23 when mo0_24_.MOID is not null then 24 when mo0_25_.MOID is not null then 25 when mo0_26_.MOID is not null then 26 when mo0_27_.MOID is not null then 27 when mo0_28_.MOID is not null then 28 when mo0_29_.MOID is not null then 29 when mo0_30_.MOID is not null then 30 when mo0_31_.MOID is not null then 31 when mo0_32_.MOID is not null then 32 when mo0_33_.MOID is not null then 33 when mo0_34_.MOID is not null then 34 when mo0_35_.MOID is not null then 35 when mo0_36_.MOID is not null then 36 when mo0_37_.MOID is not null then 37 when mo0_38_.MOID is not null then 38 when mo0_39_.MOID is not null then 39 when mo0_40_.MOID is not null then 40 when mo0_41_.MOID is not null then 41 when mo0_42_.MOID is not null then 42 when mo0_43_.MOID is not null then 43 when mo0_44_.MOID is not null then 44 when mo0_45_.MOID is not null then 45 when mo0_.MOID is not null then 0 end as clazz_41_, table1userdata1_.MOID as moid1_496_0_, table1userdata1_.label as label3_496_0_, table1userdata1_.nmsPrimaryAddress as nmsprima4_496_0_, table1userdata1_.nmsSecondaryAddress as nmssecon5_496_0_, table1userdata1_.manufacturer as manufact6_496_0_, table1userdata1_.zoneType as zonetype7_496_0_, table1userdata1_.Discriminator as discrimi2_496_0_, table2userdata2_.MOID as moid1_258_1_, table2userdata2_.label as label3_258_1_, table2userdata2_.isGNE as isgne4_258_1_, table2userdata2_.gneReference as gnerefer5_258_1_, table2userdata2_.firstClockSourceType as firstclo6_258_1_, table2userdata2_.firstClockSourcePort as firstclo7_258_1_, table2userdata2_.secondClockSourceType as secondcl8_258_1_, table2userdata2_.secondClockSourcePort as secondcl9_258_1_, table2userdata2_.thirdClockSourceType as thirdcl10_258_1_, table2userdata2_.thirdClockSourcePort as thirdcl11_258_1_, table2userdata2_.fourthClockSourceType as fourthc12_258_1_, table2userdata2_.fourthClockSourcePort as fourthc13_258_1_, table2userdata2_.ethernetIPAddress as etherne14_258_1_, table2userdata2_.ethernetSubnetMask as etherne15_258_1_, table2userdata2_.dcnSubNetworkMask as dcnsubn16_258_1_, table2userdata2_.domain as domain17_258_1_, table2userdata2_.communicationStateChangeTime as communi18_258_1_, table2userdata2_.backPlaneTotalCapacity as backpla19_258_1_, table2userdata2_.lsrId as lsrid20_258_1_, table2userdata2_.asonId as asonid21_258_1_, table2userdata2_.backPlaneUsedCapacity as backpla22_258_1_, table2userdata2_.fmInventoryName as fminven23_258_1_, table2userdata2_.technologyOrDomain as technol24_258_1_, table2userdata2_.deviceType as devicet25_258_1_, table2userdata2_.switchType as switcht26_258_1_, table2userdata2_.zoneType as zonetyp27_258_1_, table2userdata2_.Discriminator as discrimi2_258_1_, table3userdata3_.MOID as moid1_371_2_, table3userdata3_.label as label3_371_2_, table3userdata3_.frequency as frequenc4_371_2_, table3userdata3_.useInterTechnologyLink as useinter5_371_2_, table3userdata3_.portRate as portrate6_371_2_, table3userdata3_.portType as porttype7_371_2_, table3userdata3_.interfaceType as interfac8_371_2_, table3userdata3_.portRateSource as portrate9_371_2_, table3userdata3_.portTypeSource as porttyp10_371_2_, table3userdata3_.interfaceTypeSource as interfa11_371_2_, table3userdata3_.dynamic as dynamic12_371_2_, table3userdata3_.sendTrace as sendtra13_371_2_, table3userdata3_.receiveTrace as receive14_371_2_, table3userdata3_.traceSource as traceso15_371_2_, table3userdata3_.traceStatus as tracest16_371_2_, table3userdata3_.tracesetTime as tracese17_371_2_, table3userdata3_.tracereceiveTime as tracere18_371_2_, table3userdata3_.alarmDetectionCapability as alarmde19_371_2_, table3userdata3_.tubeColor as tubecol20_371_2_, table3userdata3_.strandColor as strandc21_371_2_, table3userdata3_.lagMaster as lagmast22_371_2_, table3userdata3_.portDomain as portdom23_371_2_, table3userdata3_.newPortType as newport24_371_2_, table3userdata3_.currentPortType as current25_371_2_, table3userdata3_.facingType as facingt26_371_2_, table3userdata3_.fmInventoryName as fminven27_371_2_, table4userdata4_.MOID as moid1_176_3_, table4userdata4_.label as label3_176_3_, table4userdata4_.ftpRate as ftprate4_176_3_, table4userdata4_.ftpType as ftptype5_176_3_, table4userdata4_.ftpDomain as ftpdomai6_176_3_, table4userdata4_.lagFtp as lagftp7_176_3_, table4userdata4_.oduFlexEnabled as oduflexe8_176_3_, table4userdata4_.lagSharingType as lagshari9_176_3_, table4userdata4_.newFtpType as newftpt10_176_3_, table4userdata4_.ftpTypeSource as ftptype11_176_3_, table4userdata4_.ftpRateSource as ftprate12_176_3_, table4userdata4_.clientOTNBindingPort as cliento13_176_3_, table4userdata4_.fmInventoryName as fminven14_176_3_, table5userdata5_.MOID as moid1_54_4_, table5userdata5_.frequency as frequenc3_54_4_, table5userdata5_.hangingType as hangingt4_54_4_, table5userdata5_.isLooped as islooped5_54_4_, table5userdata5_.isDynamic as isdynami6_54_4_, table5userdata5_.occCTPTxParam as occctptx7_54_4_, table5userdata5_.occCTPName as occctpna8_54_4_, table6userdata6_.MOID as moid1_123_5_, table6userdata6_.label as label3_123_5_, table6userdata6_.fmInventoryName as fminvent4_123_5_, table7userdata7_.MOID as moid1_125_6_, table7userdata7_.label as label3_125_6_, table7userdata7_.hardwareVersion as hardware4_125_6_, table7userdata7_.softwareVersion as software5_125_6_, table7userdata7_.firmwareVersion as firmware6_125_6_, table7userdata7_.sfpType as sfptype7_125_6_, table7userdata7_.equipType as equiptyp8_125_6_, table7userdata7_.reconEquipType as reconequ9_125_6_, table7userdata7_.equipmentPortion as equipme10_125_6_, table7userdata7_.fmInventoryName as fminven11_125_6_, table8userdata8_.MOID as moid1_484_7_, table8userdata8_.label as label3_484_7_, table8userdata8_.derivedTopologyLabel as derivedt4_484_7_, table8userdata8_.involvedTopologyLabel as involved5_484_7_, table8userdata8_.asonEnabled as asonenab6_484_7_, table8userdata8_.interEms as interems7_484_7_, table8userdata8_.interVendor as interven8_484_7_, table8userdata8_.vendorName as vendorna9_484_7_, table8userdata8_.isPhysicalCkt as isphysi10_484_7_, table8userdata8_.phyLogTLType as phylogt11_484_7_, table8userdata8_.meMETLAvailabilityForMEVNETL as memetla12_484_7_, table8userdata8_.linkDomain as linkdom13_484_7_, table8userdata8_.interDomain as interdo14_484_7_, table8userdata8_.mplsEnabled as mplsena15_484_7_, table8userdata8_.logicalTLSubType as logical16_484_7_, table8userdata8_.bitRate as bitrate17_484_7_, table8userdata8_.zoneType as zonetyp18_484_7_, table8userdata8_.zoneConnectivityType as zonecon19_484_7_, table8userdata8_.siteConnectivityType as sitecon20_484_7_, table8userdata8_.Discriminator as discrimi2_484_7_, table10userdata9_.MOID as moid1_342_8_, table10userdata9_.label as label3_342_8_, table10userdata9_.rcaPgpHash as rcapgpha4_342_8_, table12userdata10_.MOID as moid1_540_9_, table12userdata10_.label as label3_540_9_, table12userdata10_.networkedge as networke4_540_9_, table12userdata10_.type as type5_540_9_, table12userdata10_.iconType as icontype6_540_9_, table13userdata11_.MOID as moid1_535_10_, table13userdata11_.label as label3_535_10_, table15userdata12_.MOID as moid1_446_11_, table15userdata12_.label as label3_446_11_, table15userdata12_.customerName as customer4_446_11_, table15userdata12_.circuitId as circuiti5_446_11_, table15userdata12_.asonEnabled as asonenab6_446_11_, table15userdata12_.workerSNCStatus as workersn7_446_11_, table15userdata12_.protectingSNCStatus as protecti8_446_11_, table15userdata12_.workerSNCOnHomeRouteEnabled as workersn9_446_11_, table15userdata12_.protSNCOnHomeRouteEnabled as protsnc10_446_11_, table15userdata12_.sncType as snctype11_446_11_, table15userdata12_.asonWorkerPathMD5 as asonwor12_446_11_, table15userdata12_.asonProtectionPathMD5 as asonpro13_446_11_, table15userdata12_.asonCombinedPathMD5 as asoncom14_446_11_, table15userdata12_.circuitProtectionClass as circuit15_446_11_, table15userdata12_.routeTypes as routety16_446_11_, table15userdata12_.dynamicSwitchingCapable as dynamic17_446_11_, proprietar13_.MOID as moid1_348_12_, proprietar13_.LASTMODIFIEDTIME as lastmodi3_348_12_, proprietar13_1_.intended as intended2_329_12_, proprietar13_1_.actualState as actualst3_329_12_, proprietar13_1_.administrativeState as administ4_329_12_, proprietar13_1_.inUseBy as inuseby5_329_12_, proprietar13_1_.exclusive1 as exclusiv6_329_12_, proprietar13_1_.soureOfSNC as soureofs7_329_12_, proprietar13_.discriminator as discrimi2_348_12_, table16userdata14_.MOID as moid1_17_13_, table16userdata14_.interEms as interems3_17_13_, table16userdata14_.interVendor as interven4_17_13_, table16userdata14_.vendorName as vendorna5_17_13_, table16userdata14_.circuitType as circuitt6_17_13_, table16userdata14_.circuitDomain as circuitd7_17_13_, table16userdata14_.circuitSubType as circuits8_17_13_, table16userdata14_.isASON as isason9_17_13_, table16userdata14_.trailId as trailid10_17_13_, table16userdata14_.serviceEligible as service11_17_13_, table16userdata14_.aEndLocation as aendloc12_17_13_, table16userdata14_.zEndLocation as zendloc13_17_13_, table16userdata14_.isDropped as isdropp14_17_13_, table16errordetails15_.MOID as moid1_14_14_, table16errordetails15_1_.REASON as reason2_502_14_, table16errordetails15_2_.REASON as reason2_372_14_, table16errordetails15_3_.REASON as reason2_188_14_, case when table16errordetails15_1_.MOID is not null then 1 when table16errordetails15_2_.MOID is not null then 2 when table16errordetails15_3_.MOID is not null then 3 when table16errordetails15_.MOID is not null then 0 end as clazz_14_, table18userdata16_.MOID as moid1_160_15_, table18userdata16_.customerName as customer3_160_15_, table18userdata16_.ivId as ivid4_160_15_, table18userdata16_.circuitId as circuiti5_160_15_, table18userdata16_.label as label6_160_15_, table20userdata17_.MOID as moid1_508_16_, table20userdata17_.label as label3_508_16_, table21userdata18_.MOID as moid1_229_17_, table21userdata18_.label as label3_229_17_, table22userdata19_.MOID as moid1_480_18_, table22userdata19_.label as label3_480_18_, table23userdata20_.MOID as moid1_546_19_, table23userdata20_.label as label3_546_19_, table24userdata21_.MOID as moid1_274_20_, table24userdata21_.label as label3_274_20_, table25userdata22_.MOID as moid1_320_21_, table25userdata22_.label as label3_320_21_, table26userdata23_.MOID as moid1_454_22_, table26userdata23_.label as label3_454_22_, table27userdata24_.MOID as moid1_452_23_, table27userdata24_.CustomerName as customer3_452_23_, table28userdata25_.MOID as moid1_335_24_, table28userdata25_.label as label3_335_24_, table28userdata25_.PATCH_LINK_TYPE as patch_li4_335_24_, table28userdata25_.CRM_ID as crm_id5_335_24_, table29userdata26_.MOID as moid1_422_25_, table29userdata26_.DESIGNATION as designat3_422_25_, table29userdata26_.PRODUCT_TYPE as product_4_422_25_, table29userdata26_.MULTIPLE_CIRCUITS as multiple5_422_25_, table30userdata27_.MOID as moid1_35_26_, table30userdata27_.vlanOrSvlan as vlanorsv3_35_26_, table30userdata27_.cvlan as cvlan4_35_26_, table30userdata27_.interEms as interems5_35_26_, table30userdata27_.interVendor as interven6_35_26_, table30userdata27_.vendorName as vendorna7_35_26_, table30userdata27_.taggingType as taggingt8_35_26_, table30userdata27_.trailId as trailid9_35_26_, table30userdata27_.defaultCosCIRbps as default10_35_26_, table30userdata27_.defaultCosEIRbps as default11_35_26_, table30userdata27_.defaultCosPIRbps as default12_35_26_, table30userdata27_.cos0CIRbps as cos13_35_26_, table30userdata27_.cos0EIRbps as cos14_35_26_, table30userdata27_.cos0PIRbps as cos15_35_26_, table30userdata27_.cos1CIRbps as cos16_35_26_, table30userdata27_.cos1EIRbps as cos17_35_26_, table30userdata27_.cos1PIRbps as cos18_35_26_, table30userdata27_.cos2CIRbps as cos19_35_26_, table30userdata27_.cos2EIRbps as cos20_35_26_, table30userdata27_.cos2PIRbps as cos21_35_26_, table30userdata27_.cos3CIRbps as cos22_35_26_, table30userdata27_.cos3EIRbps as cos23_35_26_, table30userdata27_.cos3PIRbps as cos24_35_26_, table30userdata27_.cos4CIRbps as cos25_35_26_, table30userdata27_.cos4EIRbps as cos26_35_26_, table30userdata27_.cos4PIRbps as cos27_35_26_, table30userdata27_.cos5CIRbps as cos28_35_26_, table30userdata27_.cos5EIRbps as cos29_35_26_, table30userdata27_.cos5PIRbps as cos30_35_26_, table30userdata27_.cos6CIRbps as cos31_35_26_, table30userdata27_.cos6EIRbps as cos32_35_26_, table30userdata27_.cos6PIRbps as cos33_35_26_, table30userdata27_.cos7CIRbps as cos34_35_26_, table30userdata27_.cos7EIRbps as cos35_35_26_, table30userdata27_.cos7PIRbps as cos36_35_26_, table30userdata27_.customerName as custome37_35_26_, table30userdata27_.circuitId as circuit38_35_26_, table30userdata27_.label as label39_35_26_, table30userdata27_.serviceEligible as service40_35_26_, table30userdata27_.aEndLocation as aendloc41_35_26_, table30userdata27_.zEndLocation as zendloc42_35_26_, table30userdata27_.isDropped as isdropp43_35_26_, table31userdata28_.MOID as moid1_413_27_, table31userdata28_.label as label3_413_27_, table32userdata29_.MOID as moid1_408_28_, table32userdata29_.label as label3_408_28_, table34userdata30_.MOID as moid1_491_29_, table34userdata30_.LABEL as label3_491_29_, table34userdata30_.TECHNOLOGY as technolo4_491_29_, table34userdata30_.INVOLVEDRATES as involved5_491_29_, table34userdata30_.INVOLVEDEMSNAMES as involved6_491_29_, table34userdata30_.AGGREGATENODE1 as aggregat7_491_29_, table34userdata30_.AGGREGATENODE2 as aggregat8_491_29_, table35userdata31_.MOID as moid1_27_30_, table35userdata31_.label as label3_27_30_, table36userdata32_.MOID as moid1_316_31_, table36userdata32_.label as label3_316_31_, table36userdata32_.aEndLocation as aendloca4_316_31_, table36userdata32_.zEndLocation as zendloca5_316_31_, table37userdata33_.MOID as moid1_193_32_, table37userdata33_.label as label3_193_32_, table38userdata34_.MOID as moid1_198_33_, table38userdata34_.label as label3_198_33_, table38userdata34_.fmInventoryName as fminvent4_198_33_, table39userdata35_.MOID as moid1_449_34_, table39userdata35_.label as label3_449_34_, table41userdata36_.MOID as moid1_241_35_, table41userdata36_.label as label3_241_35_, table42userdata37_.MOID as moid1_239_36_, table42userdata37_.label as label3_239_36_, table43userdata38_.MOID as moid1_255_37_, table43userdata38_.label as label3_255_37_, table44userdata39_.MOID as moid1_282_38_, table44userdata39_.InvolvedEMSNames as involved3_282_38_, table44userdata39_.MPLSNetworkId as mplsnetw4_282_38_, protection40_.MOID as moid1_349_39_, protection40_1_.workerAEndTP as workerae2_493_39_, protection40_1_.workerZEndTP as workerze3_493_39_, protection40_1_.protectionAEndTP as protecti4_493_39_, protection40_1_.protectionZEndTP as protecti5_493_39_, protection40_1_.workerConnection as workerco6_493_39_, protection40_1_.protectionConnection as protecti7_493_39_, protection40_1_.sourceObjectType as sourceob8_493_39_, protection40_1_.sourceObjectOne as sourceob9_493_39_, protection40_1_.sourceObjectTwo as sourceo10_493_39_, protection40_1_.layerRate as layerra11_493_39_, protection40_1_.reversionMode as reversi12_493_39_, protection40_1_.wtrTime as wtrtime13_493_39_, protection40_1_.holdOffTime as holdoff14_493_39_, protection40_2_.FDFR_NAME as fdfr_nam2_127_39_, protection40_2_.FDFR_ID as fdfr_id3_127_39_, protection40_2_.FDFR_LABEL as fdfr_lab4_127_39_, protection40_2_.RPL_PORT_NAME as rpl_port5_127_39_, protection40_2_.NEIGHBOURING_RPL_PORT_NAME as neighbou6_127_39_, protection40_3_.ReversionMode as reversio2_501_39_, protection40_3_.WaitToRestoreTime as waittore3_501_39_, case when protection40_1_.moid is not null then 1 when protection40_2_.moid is not null then 2 when protection40_3_.MOID is not null then 3 when protection40_.MOID is not null then 0 end as clazz_39_, table45errordetails41_.MOID as moid1_306_40_, table45errordetails41_1_.NO_OF_GRAPHS as no_of_gr2_128_40_, case when table45errordetails41_1_.MOID is not null then 1 when table45errordetails41_.MOID is not null then 0 end as clazz_40_ from ManagedObject mo0_ left outer join Table1 mo0_1_ on mo0_.MOID=mo0_1_.MOID left outer join Table2 mo0_2_ on mo0_.MOID=mo0_2_.MOID left outer join Table3 mo0_3_ on mo0_.MOID=mo0_3_.MOID left outer join Table4 mo0_4_ on mo0_.MOID=mo0_4_.MOID left outer join Table5 mo0_5_ on mo0_.MOID=mo0_5_.MOID left outer join Table6 mo0_6_ on mo0_.MOID=mo0_6_.MOID left outer join Table7 mo0_7_ on mo0_.MOID=mo0_7_.MOID left outer join Table8 mo0_8_ on mo0_.MOID=mo0_8_.MOID left outer join Table9 mo0_9_ on mo0_.MOID=mo0_9_.MOID left outer join Table10 mo0_10_ on mo0_.MOID=mo0_10_.MOID left outer join Table11 mo0_11_ on mo0_.MOID=mo0_11_.MOID left outer join Table12 mo0_12_ on mo0_.MOID=mo0_12_.MOID left outer join Table13 mo0_13_ on mo0_.MOID=mo0_13_.MOID left outer join Table14 mo0_14_ on mo0_.MOID=mo0_14_.MOID left outer join Table15 mo0_15_ on mo0_.MOID=mo0_15_.MOID left outer join Table16 mo0_16_ on mo0_.MOID=mo0_16_.MOID left outer join Table17 mo0_17_ on mo0_.MOID=mo0_17_.MOID left outer join Table18 mo0_18_ on mo0_.MOID=mo0_18_.MOID left outer join Table19 mo0_19_ on mo0_.MOID=mo0_19_.MOID left outer join Table20 mo0_20_ on mo0_.MOID=mo0_20_.MOID left outer join Table21 mo0_21_ on mo0_.MOID=mo0_21_.MOID left outer join Table22 mo0_22_ on mo0_.MOID=mo0_22_.MOID left outer join Table23 mo0_23_ on mo0_.MOID=mo0_23_.MOID left outer join Table24 mo0_24_ on mo0_.MOID=mo0_24_.MOID left outer join Table25 mo0_25_ on mo0_.MOID=mo0_25_.MOID left outer join Table26 mo0_26_ on mo0_.MOID=mo0_26_.MOID left outer join Table27 mo0_27_ on mo0_.MOID=mo0_27_.MOID left outer join Table28 mo0_28_ on mo0_.MOID=mo0_28_.MOID left outer join Table29 mo0_29_ on mo0_.MOID=mo0_29_.MOID left outer join Table30 mo0_30_ on mo0_.MOID=mo0_30_.MOID left outer join Table31 mo0_31_ on mo0_.MOID=mo0_31_.MOID left outer join Table32 mo0_32_ on mo0_.MOID=mo0_32_.MOID left outer join Table33 mo0_33_ on mo0_.MOID=mo0_33_.MOID left outer join Table34 mo0_34_ on mo0_.MOID=mo0_34_.MOID left outer join Table35 mo0_35_ on mo0_.MOID=mo0_35_.MOID left outer join Table36 mo0_36_ on mo0_.MOID=mo0_36_.MOID left outer join Table37 mo0_37_ on mo0_.MOID=mo0_37_.MOID left outer join Table38 mo0_38_ on mo0_.MOID=mo0_38_.MOID left outer join Table39 mo0_39_ on mo0_.MOID=mo0_39_.MOID left outer join Table40 mo0_40_ on mo0_.MOID=mo0_40_.MOID left outer join Table41 mo0_41_ on mo0_.MOID=mo0_41_.MOID left outer join Table42 mo0_42_ on mo0_.MOID=mo0_42_.MOID left outer join Table43 mo0_43_ on mo0_.MOID=mo0_43_.MOID left outer join Table44 mo0_44_ on mo0_.MOID=mo0_44_.MOID left outer join Table45 mo0_45_ on mo0_.MOID=mo0_45_.MOID left outer join Table1UserData table1userdata1_ on mo0_.MOID=table1userdata1_.MOID left outer join Table2UserData table2userdata2_ on mo0_.MOID=table2userdata2_.MOID left outer join Table3UserData table3userdata3_ on mo0_.MOID=table3userdata3_.MOID left outer join Table4UserData table4userdata4_ on mo0_.MOID=table4userdata4_.MOID left outer join Table5UserData table5userdata5_ on mo0_.MOID=table5userdata5_.MOID left outer join Table6UserData table6userdata6_ on mo0_.MOID=table6userdata6_.MOID left outer join Table7UserData table7userdata7_ on mo0_.MOID=table7userdata7_.MOID left outer join Table8UserData table8userdata8_ on mo0_.MOID=table8userdata8_.MOID left outer join Table10UserData table10userdata9_ on mo0_.MOID=table10userdata9_.MOID left outer join Table12UserData table12userdata10_ on mo0_.MOID=table12userdata10_.MOID left outer join Table13UserData table13userdata11_ on mo0_.MOID=table13userdata11_.MOID left outer join Table15UserData table15userdata12_ on mo0_.MOID=table15userdata12_.MOID left outer join ProprietaryTable proprietar13_ on mo0_.MOID=proprietar13_.MOID left outer join SubProprietaryTable proprietar13_1_ on proprietar13_.MOID=proprietar13_1_.SNCMOID left outer join Table16UserData table16userdata14_ on mo0_.MOID=table16userdata14_.MOID left outer join Table16ErrorDetails table16errordetails15_ on mo0_.MOID=table16errordetails15_.MOID left outer join Table16ErrorDetails_1 table16errordetails15_1_ on table16errordetails15_.MOID=table16errordetails15_1_.MOID left outer join Table16ErrorDetails_2 table16errordetails15_2_ on table16errordetails15_.MOID=table16errordetails15_2_.MOID left outer join Table16ErrorDetails_3 table16errordetails15_3_ on table16errordetails15_.MOID=table16errordetails15_3_.MOID left outer join Table18UserData table18userdata16_ on mo0_.MOID=table18userdata16_.MOID left outer join Table20UserData table20userdata17_ on mo0_.MOID=table20userdata17_.MOID left outer join Table21UserData table21userdata18_ on mo0_.MOID=table21userdata18_.MOID left outer join Table22UserData table22userdata19_ on mo0_.MOID=table22userdata19_.MOID left outer join Table23UserData table23userdata20_ on mo0_.MOID=table23userdata20_.MOID left outer join Table24UserData table24userdata21_ on mo0_.MOID=table24userdata21_.MOID left outer join Table25UserData table25userdata22_ on mo0_.MOID=table25userdata22_.MOID left outer join Table26UserData table26userdata23_ on mo0_.MOID=table26userdata23_.MOID left outer join Table27UserData table27userdata24_ on mo0_.MOID=table27userdata24_.MOID left outer join Table28UserData table28userdata25_ on mo0_.MOID=table28userdata25_.MOID left outer join Table29UserData table29userdata26_ on mo0_.MOID=table29userdata26_.MOID left outer join Table30UserData table30userdata27_ on mo0_.MOID=table30userdata27_.MOID left outer join Table31UserData table31userdata28_ on mo0_.MOID=table31userdata28_.MOID left outer join Table32UserData table32userdata29_ on mo0_.MOID=table32userdata29_.MOID left outer join Table34UserData table34userdata30_ on mo0_.MOID=table34userdata30_.MOID left outer join Table35UserData table35userdata31_ on mo0_.MOID=table35userdata31_.MOID left outer join Table36UserData table36userdata32_ on mo0_.MOID=table36userdata32_.MOID left outer join Table37UserData table37userdata33_ on mo0_.MOID=table37userdata33_.MOID left outer join Table38UserData table38userdata34_ on mo0_.MOID=table38userdata34_.MOID left outer join Table39UserData table39userdata35_ on mo0_.MOID=table39userdata35_.MOID left outer join Table41UserData table41userdata36_ on mo0_.MOID=table41userdata36_.MOID left outer join Table42UserData table42userdata37_ on mo0_.MOID=table42userdata37_.MOID left outer join Table43UserData table43userdata38_ on mo0_.MOID=table43userdata38_.MOID left outer join Table44UserData table44userdata39_ on mo0_.MOID=table44userdata39_.MOID left outer join PROTECTION_DETAILS protection40_ on mo0_.MOID=protection40_.MOID left outer join SubProtectionDetails_1 protection40_1_ on protection40_.MOID=protection40_1_.moid left outer join SubProtectionDetails_2 protection40_2_ on protection40_.MOID=protection40_2_.moid left outer join SubProtectionDetails_3 protection40_3_ on protection40_.MOID=protection40_3_.MOID left outer join Table45ErrorDetails table45errordetails41_ on mo0_.MOID=table45errordetails41_.MOID left outer join Table45ErrorDetails_1 table45errordetails41_1_ on table45errordetails41_.MOID=table45errordetails41_1_.MOID where mo0_.NAME=?

4

Do I understand this correctly, that you have a base entity class ManagedObject which uses JOINED inheritance and you have 45 subclasses?

I don’t know what is happening exactly, but if you query for a Table1, it definitely shouldn’t join all the other subclass tables, so this might be a bug. Could you please create a reproducer test case and attach that to a newly created JIRA issue?