MsSql2005Dialect does not handle parameters in the order by clause correctly

Description

With the following mapping:

<hibernate-mapping assembly='NhibernateLeftJoins' namespace='NhibernateLeftJoins' xmlns='urn:nhibernate-mapping-2.2'>
<class name='MasterFeedProduct' table='master_feed_product' dynamic-update='true' >
<id name='Id' column='id'>
<generator class='native'/>
</id>
<property name='BrandId' column='brand_id' />
<set name='Values' lazy="true" cascade="none" inverse="true" >
<key column="MasterFeedProductId" />
<one-to-many class="MasterFeedProductValue"/>
</set>
</class>
<class table="MasterFeedProductValue" name="MasterFeedProductValue" >
<composite-id>
<key-many-to-one name="Product" column="MasterFeedProductId" />
<key-property name="ColumnId" column="MasterFeedColumnId" />
</composite-id>
<property name="Value" type="StringClob" />
</class>
</hibernate-mapping>

The following code does not work:

IQuery query = session.CreateQuery(
@"select product
from MasterFeedProduct product
left join product.Values values
left join product.Values sortExpression
where sortExpression.ColumnId =
case
when :typeId in (sortExpression.ColumnId) then
:typeId
else
(select max(two.ColumnId) from MasterFeedProductValue two where two.Product = product)
end
and values.Value like :title
and product.BrandId = :brandId
order by case when :typeId = sortExpression.ColumnId then sortExpression.ColumnId else null end

");
IList list = query
.SetInt32("typeId", 3)
.SetString("title", "new")
.SetInt32 ( "brandId", 136 )
.SetFirstResult ( 10 )
.SetMaxResults ( 10 )
.List();

Environment

None

Attachments

2
  • 11 Aug 2008, 05:53 AM
  • 31 Jul 2008, 10:15 AM

Activity

Show:

Julian MaughanAugust 15, 2010 at 7:02 AM

David's comment that the MsSql2005 dialect "treats the 'order by' clause as a plain string" would suggest this is the same problem as NH-2133. I fixed https://nhibernate.jira.com/browse/NH-2133#icft=NH-2133 by changing the dialect to use SqlString, instead of string, to preserve parameter indices in the ORDER BY statement.

I am closing this issue because it was reported in mid-2008, and still does not have a valid test-case. Also, I believe it is most likely resolved.

Tuna ToksozMarch 1, 2009 at 2:04 PM

The patch attached has a wrong sql, it asks for sortExpression which is not defined.

David KempAugust 11, 2008 at 5:53 AM

Patch to demonstrate bug

Fabio MauloJuly 31, 2008 at 10:27 AM

Do you can send a failing test ?
Apparently you are using NH sources; please take a look in nhibernate\src\NHibernate.Test\NHSpecificTest and try to create a test in the same style
Thanks.

David KempJuly 31, 2008 at 10:15 AM

This is my attempt to fix the problem by changing it to use SqlString[] for the order by clause. However, this still doesn't work, as the parameters get re-ordered, and therefore get the wrong values assigned.

Incomplete

Details

Assignee

Reporter

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created July 31, 2008 at 10:11 AM
Updated August 15, 2010 at 7:04 AM
Resolved August 15, 2010 at 7:04 AM
Who's Looking?