Criteria Query with joined-subclass generates incorrect SQL ----------------------------------------------------------- Key: HHH-4829 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4829 Project: Hibernate Core Issue Type: Bug Components: query-criteria Affects Versions: 3.3.2 Environment: Hibernate 3.3.2 MySQL 5.0 Reporter: Rajender Aggarwal We have a scenario where joined-sublcass is used for Inheritance. There is a property called 'messageBody' that is present in two Child classes. When the query Criteria created on Parent class and the criteria contains 'messageBody' - the where clause contains like for only one table (the one that appears first in joined-subclass). I am attaching trimmed version of HBM file, Method that prepares Criteria query, Current SQL and expected SQL. Please let me know if any further information is required. rajender.aggarwal(a)gmail.com ---------------------------------------------- HBM File ------------------------------------------------ <?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping default-cascade="none"> <class name="com.myproject.core.model.Ad" abstract="true" table="Ad"> <id name="id" type="long" column="ad_id" unsaved-value="null"> <generator class="native" /> </id> <property name="subAccount" type="string"> <column name="subaccount" sql-type="text" /> </property> <many-to-one name="shortCode" column="shortcode_id" class="com.myproject.core.model.ShortCode"></many-to-one> <many-to-one name="account" column="account_id" class="com.myproject.core.model.Account" lazy="false"></many-to-one> <joined-subclass name="com.myproject.core.model.D2PAd" extends="com.myproject.core.model.Ad" table="d2pad" batch-size="50"> <key column="d2pad_id" /> <property name="createdOn" type="timestamp" column="createdOn" not-null="true" /> <property name="modifiedOn" column="modifiedOn" type="timestamp" /> <property name="activationDate" column="activation_date" type="timestamp" /> <property name="active" column="active" type="boolean" /> <property name="ageVerfReq" column="ageverfreq" type="boolean" /> <property name="deactivationDate" column="deactivation_date" type="timestamp" /> <property name="title" type="string"> <column name="title" sql-type="text" /> </property> <property name="url" type="string"> <column name="url" sql-type="text" /> </property> <property name="deleted" column="deleted" type="boolean" /> <list name="templates" cascade="all,delete-orphan"> <key column="adId" /> <index column="sequence" /> <one-to-many class="com.myproject.d2p.model.D2PAdTemplate" /> </list> <bag name="webPages" cascade="all,delete-orphan"> <key column="d2pAd" /> <one-to-many class="com.myproject.d2p.webpage.model.D2PAdWebPage" /> </bag> </joined-subclass> <joined-subclass name="com.myproject.cmm.model.CMMAd" table="cmmad" abstract="true" extends="com.myproject.core.model.Ad" batch-size="50"> <key column="cmmad_id" /> <property name="title" type="string"> <column name="title" sql-type="text" /> </property> <property name="dateTime" column="scheduled_date_time" type="timestamp" /> <many-to-one name="optInD2PAd" column="optin_d2pad" class="com.myproject.core.model.D2PAd"></many-to-one> <many-to-one name="destinationGroup" column="contactGroup_Id" class="com.myproject.contactgroup.model.ContactGroup"></many-to-one> <property name="deleted" column="deleted" type="boolean" /> <joined-subclass name="com.myproject.cmm.model.GeneralCMMAd" table="generalcmmad" extends="com.myproject.cmm.model.CMMAd" batch-size="50"> <key column="cmmad_id" /> <property name="messageBody" type="string"> <column name="messagebody" sql-type="text" /> </property> <property name="optInText" type="string"> <column name="optin_text" sql-type="text" /> </property> </joined-subclass> <joined-subclass name="com.myproject.cmm.model.D2PCMMAd" table="d2pcmmad" extends="com.myproject.cmm.model.CMMAd" batch-size="50"> <key column="cmmad_id" /> <many-to-one name="d2pAd" column="d2pad_id" class="com.myproject.core.model.D2PAd"></many-to-one> <property name="optInText" type="string"> <column name="optin_text" sql-type="text" /> </property> </joined-subclass> <joined-subclass name="com.myproject.cmm.model.OptInCMMAd" table="optincmmad" extends="com.myproject.cmm.model.CMMAd" batch-size="50"> <key column="cmmad_id" /> <property name="messageBody" type="string"> <column name="messagebody" sql-type="text" /> </property> </joined-subclass> <joined-subclass name="com.myproject.cmm.model.AppointmentCMMAd" table="appointmentcmmad" extends="com.myproject.cmm.model.CMMAd" batch-size="50"> <key column="cmmad_id" /> <property name="appointmentDateTime" column="appointment_date_time" type="timestamp" /> <list name="messageCMMAds" cascade="all,delete-orphan" lazy="false"> <key column="appointmentCMM" /> <index column="sequence" /> <one-to-many class="com.myproject.cmm.model.AppointmentOption" /> </list> </joined-subclass> </joined-subclass> </class> </hibernate-mapping> ---------------------------------------------- Criteria Query Method ------------------------------------------------ Input Parameters: pageNo = 1 accountId = 1 filterMap = [{"messageBody", "test"}] adClass = com.converdia.cmm.model.CMMAd rowCount = 10 protected <T extends Ad> Criteria createCriteriaForAd(Integer pageNo, Long accountId, Map<FilterKey, Object> filterMap, Class<T> adClass, boolean rowCount) { Criteria crt = this.getSession().createCriteria(adClass); if (accountId != null) { crt.add(Restrictions.eq("account.id", accountId)); } crt.add(Restrictions.ne("deleted", Boolean.TRUE)); for (FilterKey key : filterMap.keySet()) { switch (key.getFilterType()) { case FilterKey.CONDITION_EQUALS_TYPE: crt.add(Restrictions.eq(key.getFieldKey(), filterMap.get(key))); break; case FilterKey.CONDITION_LIKE_TYPE: String srch = ((String) filterMap.get(key)).replace('*', '%'); crt.add(Restrictions.like(key.getFieldKey(), '%' + srch + '%')); break; case FilterKey.CONDITION_GREATER: crt.add(Restrictions.ge(key.getFieldKey(), filterMap.get(key))); break; case FilterKey.CONDITION_LESSER: crt.add(Restrictions.le(key.getFieldKey(), filterMap.get(key))); break; case FilterKey.CONDITION_BETWEEN: Object[] values = (Object[]) filterMap.get(key); crt.add(Restrictions.between(key.getFieldKey(), values[0], values[1])); break; default: break; } } if (rowCount) { crt.setProjection(Projections.projectionList().add(Projections.rowCount())); } else { crt.setFirstResult((pageNo - 1) * 10).setMaxResults(10); } return crt; } ---------------------------------------------- Filter Key Class ------------------------------------------------ package com.myproject.core.helper; public class FilterKey { public static final int CONDITION_LIKE_TYPE = 1; public static final int CONDITION_EQUALS_TYPE = 2; public static final int CONDITION_GREATER = 3; public static final int CONDITION_LESSER = 4; public static final int CONDITION_BETWEEN = 5; public FilterKey() { } public FilterKey(String fieldKey, Integer filterType) { this.fieldKey = fieldKey; this.filterType = filterType; } private String fieldKey; private Integer filterType; public String getFieldKey() { return this.fieldKey; } public void setFieldKey(String fieldKey) { this.fieldKey = fieldKey; } public Integer getFilterType() { return this.filterType; } public void setFilterType(Integer filterType) { this.filterType = filterType; } } ---------------------------------------------- Generated Query ------------------------------------------------ select count(*) as y0_ from cmmad this_ inner join Ad this_1_ on this_.cmmad_id=this_1_.ad_id left outer join generalcmmad this_2_ on this_.cmmad_id=this_2_.cmmad_id left outer join d2pcmmad this_3_ on this_.cmmad_id=this_3_.cmmad_id left outer join optincmmad this_4_ on this_.cmmad_id=this_4_.cmmad_id left outer join appointmentcmmad this_5_ on this_.cmmad_id=this_5_.cmmad_id where this_.deleted<>? and this_2_.messagebody like ? ---------------------------------------------- Expected Query ------------------------------------------------ select count(*) as y0_ from cmmad this_ inner join Ad this_1_ on this_.cmmad_id=this_1_.ad_id left outer join generalcmmad this_2_ on this_.cmmad_id=this_2_.cmmad_id left outer join d2pcmmad this_3_ on this_.cmmad_id=this_3_.cmmad_id left outer join optincmmad this_4_ on this_.cmmad_id=this_4_.cmmad_id left outer join appointmentcmmad this_5_ on this_.cmmad_id=this_5_.cmmad_id where this_.deleted<>? and (this_2_.messagebody like ? or this_4_.messagebody like ?) -- 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