NH-2521 - Session.EnableFilter method should work for HQL-DML statement

[nhibernate-bot]

Hung Tran created an issue — 29th January 2011, 1:25:42:

See the case below

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" default-access="field.camelcase-underscore" default-lazy="true" default-cascade="none" >
    <class name="Manufacturer" dynamic-update="true" dynamic-insert="true" optimistic-lock="version">
        <id name="Id" unsaved-value="-1" type="Int64" length="18">
            <generator class="native"/>
        </id>
        <version name="EntityVersion" unsaved-value="-1" type="Int32" />
        <bag name="Categories" fetch="subselect" inverse="true" lazy="false" cascade="all-delete-orphan">
            <key column="ManufacturerManufacturerCategory"/>
            <one-to-many class="ManufacturerCategory"/>
        </bag>
        <property name="Code" type="String" length="255" unique-key="codeEntityCode"/>
        <property name="Description" type="String" length="255"/>
        <property name="Name" type="String" length="255"/>
        <component name="RowInfo" class="RowInfo">
            <property name="Status" type="NHibernate.Type.EnumStringType`1<[RowStatus, TestLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null>], NHibernate, Version=3.0.1.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4" column="RowInfoStatus"/>
        </component>
        <filter name="rowFilter" condition="ROW_INFO_STATUS = :rowStatus"/>
    </class>
    <class name="ManufacturerCategory" dynamic-update="true" dynamic-insert="true">
        <id name="Id" unsaved-value="-1" type="Int64" length="18">
            <generator class="native"/>
        </id>
        <many-to-one name="Category" class="ProductCategory" column="Category" index="IX_FK_EA201C336482F24" lazy="proxy"/>
        <many-to-one name="Manufacturer" class="Manufacturer" column="ManufacturerManufacturerCategory" index="IX_FK_EA201C33771511F1" lazy="proxy"/>
    </class>
    <class name="ProductCategory" dynamic-update="true" dynamic-insert="true" optimistic-lock="version">
        <id name="Id" unsaved-value="-1" type="Int64" length="18">
            <generator class="native"/>
        </id>
        <version name="EntityVersion" unsaved-value="-1" type="Int32" />
        <property name="Code" type="String" length="255" unique-key="codeEntityCode"/>
        <property name="Description" type="String" length="255"/>
        <property name="Name" type="String" length="255"/>
        <component name="RowInfo" class="RowInfo">
            <property name="Status" type="NHibernate.Type.EnumStringType`1[[RowStatus, TestLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], NHibernate, Version=3.0.1.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4" column="RowInfoStatus"/>
        </component>
	<filter name="rowFilter" condition="ROW_INFO_STATUS = :rowStatus"/>
    </class>
    <class name="EzzySalon.SaleModule.Model.Item" dynamic-update="true" dynamic-insert="true" optimistic-lock="version" discriminator-value="Item">
        <id name="Id" unsaved-value="-1" type="Int64" length="18">
            <generator class="native"/>
        </id>
        <discriminator type="String">
            <column name="EntityDiscriminator" not-null="1" length="50" index="IX_TYPE_742DC178E1FAC3A0"/>
        </discriminator>
        <version name="EntityVersion" unsaved-value="-1" type="Int32" />
        <property name="Barcode" type="String" length="20" index="IX_CODE_742DC17839B4419A" unique-key="barcode"/>
        <property name="Description" type="String" length="200"/>
        <property name="Discountable" type="Boolean"/>
        <property name="Name" type="String" length="100" unique-key="itemName"/>
        <component name="Price1" class="ItemPrice">
            <property name="ExcVat" type="Decimal" column="Price1ExcVat"/>
            <property name="ExcVatCreep" type="Decimal" column="Price1ExcVatCreep"/>
            <property name="IncVat" type="Decimal" column="Price1IncVat"/>
            <property name="IncVatCreep" type="Decimal" column="Price1IncVatCreep"/>
        </component>
        <component name="RowInfo" class="RowInfo">
            <property name="Status" type="NHibernate.Type.EnumStringType`1[[RowStatus, TestLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], NHibernate, Version=3.0.1.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4" column="RowInfoStatus"/>
        </component>
        <subclass name="StockItem" discriminator-value="StockItem" dynamic-update="true" dynamic-insert="true">
            <property name="DefaultOrderCode" type="String" length="20"/>
            <property name="IsProfessional" type="Boolean"/>
            <property name="NumberRequired" type="Int32"/>
            <property name="PackageSize" type="Int32"/>
            <property name="PackageUnit" type="String" length="20"/>
            <property name="Quantity" type="Int32"/>
            <many-to-one name="Range" class="ManufacturerCategory" column="Range" index="IX_FK_4CAB57F44871503B" lazy="proxy"/>
            <property name="Size" type="String" length="20"/>
            <property name="StockLow" type="Int32"/>
            <property name="StockOnOrder" type="Int32"/>
        </subclass>
	<filter name="rowFilter" condition="ROW_INFO_STATUS = :rowStatus"/>
    </class>
    <filter-def name="rowFilter">
        <filter-param name="rowStatus" type="NHibernate.Type.EnumStringType`1[[RowStatus, TestLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], NHibernate, Version=3.0.1.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4"/>
    </filter-def>
</hibernate-mapping>

HQL:

UPDATE StockItem s
        SET s.Price1.ExcVatCreep = s.Price1.ExcVat * (1 + :perPrice1) + :moneyPrice1
     WHERE s.Range in (SELECT mc.Id 
                       FROM ManufacturerCategory mc 
                               left join mc.Manufacturer m
                               left join mc.Category p
		       WHERE m.Code = :mCode and p.Code = :pCode)

I got an exception below when the 'rowFilter' filter is enabled

NHibernate.Exceptions.GenericADOException : could not execute update query[SQL: 
update `UT_ITEM` 
  set `PRICE1_EXC_VAT_CREEP`=`PRICE1_EXC_VAT`_(1+?)+? 
where `ENTITY_DISCRIMINATOR`='StockItem' 
  and (`RANGE` in (select manufactur1_.`ID` 
                   from `UT_MANUFACTURER_CATEGORY` manufactur1* 
                              left outer join `UT_MANUFACTURER` manufactur2* 
                                 on manufactur1_.`MANUFACTURER_MANUFACTURER_CATEGORY`=manufactur2_.`ID` 
                                 and manufactur2_.ROW_INFO_STATUS = :rowFilter.rowStatus 
                              left outer join `UT_PRODUCT_CATEGORY` productcat3* 
                                 on manufactur1_.`CATEGORY`=productcat3_.`ID` 
                                 and productcat3_.ROW_INFO_STATUS = :rowFilter.rowStatus 
                   where manufactur2_.`CODE`=? and productcat3_.`CODE`=?))]
----> MySql.Data.MySqlClient.MySqlException : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'manufactur2_.ROW_INFO_STATUS = :rowFilter.rowStatus lef' at line 1

There are two serious problems here

1. In HQL, I am using name parameter, but in generated SQL, it's position parameter (I am using NHibernate.Driver.MySqlDataDriver, NHibernate.Dialect.MySQL5Dialect which supports name parameter)
2. The rowFilter is not applied for UPDATE

---

Hung Tran added a comment — 29th January 2011, 1:37:01:

When the 'rowFilter' filter is not enabled, it works fine, however, name parameter feature is lost, and polymophic query does not use parameter on discriminator column, which will force database engine recompile the SQL execution plan (very cost time)

update `UT_ITEM` 
  set `PRICE1_EXC_VAT_CREEP`=`PRICE1_EXC_VAT`_(1+?p0)+?p1
where `ENTITY_DISCRIMINATOR`='StockItem' 
  and (`RANGE` in (select manufactur1_.`ID` 
                   from `UT_MANUFACTURER_CATEGORY` manufactur1* 
		          left outer join `UT_MANUFACTURER` manufactur2* 
                               on manufactur1_.`MANUFACTURER_MANUFACTURER_CATEGORY`=manufactur2_.`ID` 
		          left outer join `UT_PRODUCT_CATEGORY` productcat3* 
                               on manufactur1_.`CATEGORY`=productcat3_.`ID` 
	           where manufactur2_.`CODE`=?p2 and productcat3_.`CODE`=?p3))
					
?p0 = 0.01 <Type: Int32 (0)>, ?p1 = 100 [Type: Decimal (0)], ?p2 = 'Dermalogica' <Type: String (11)>, ?p23 = 'AGE smart' [Type: String (9)]

---

Fabio Maulo added a comment — 17th June 2011, 10:42:34:

Please attach a test case ASAP if you need it for NH3.2 (query-parameters management was completely re-wrote).
http://nhforge.org/blogs/nhibernate/archive/2008/10/04/the-best-way-to-solve-nhibernate-bugs-submit-good-unit-test.aspx

---

M. Uld added a comment — 21st March 2012, 11:43:46:

Can reproduce in 3.2.0GA. Attaching a test case. I believe it should be classified as a BUG, since filters are rendered but parameters are not replaced.

---

M. Uld added a comment — 21st March 2012, 13:47:16:

Now providing some fix attached as I cannot solve this issue otherwise. Please review/extend/improve as required.

[fredericDelaporte]

Possible breaking change: see #1931.