Menu
▾
▴
[nhibernate-issues] [NHJIRA] Commented: (NH-2521) Session.EnableFilter method does not work for UPDATE statement
|
From: Hung T. (JIRA) <nh...@gm...> - 2011-01-29 09:38:17
|
[ http://216.121.112.228/browse/NH-2521?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20533#action_20533 ]
Hung Tran commented on NH-2521:
-------------------------------
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)]
> 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: 1.2.1, 2.0.1.GA, 2.1.1.GA, 2.1.2.GA, 3.0.0.Alpha1, 3.0.0.Alpha2, 3.0.0.Alpha3, 3.0.0.Beta1, 3.0.0.Beta2, 3.0.0.CR1, 3.0.0.GA, 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
|
