Thread: [nhibernate-issues] [NHJIRA] Created: (NH-2521) Session.EnableFilter method does not work for UPDAT

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Session.EnableFilter method does not work for UPDATE statement
--------------------------------------------------------------

                 Key: NH-2521
                 URL: http://216.121.112.228/browse/NH-2521
             Project:  NHibernate
          Issue Type: Bug
          Components: DataProviders / Dialects
    Affects Versions: 3.0.0.GA, 3.0.0.CR1, 3.0.0.Beta2, 3.0.0.Beta1, 3.0.0.Alpha3, 3.0.0.Alpha2, 3.0.0.Alpha1, 2.1.2.GA, 2.1.1.GA,  2.0.1.GA, 1.2.1, 3.0.1
            Reporter: Hung Tran


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


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira