http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Ken, Named parameters would solve the issue, yeah, but that is not really related to the core of the issue, which is how much trouble you have to go in trying to get paging to work. I don't care that CTE are smart. I want a simple solution to a very common and very simple problem. Smart == complex == hard to use. The reason for the query being so complex is that we need to handle a lot more scenarios in a generic fashion. In particular, take a look at [http://jira.nhibernate.org/browse/NH-1155](http://jira.nhibernate.org/browse/NH-1155) to see one issue that significantly complicated the code. http://ayende.com/3652/a-bug-story#comment13http://ayende.com/3652/a-bug-story#comment13Wed, 22 Oct 2008 16:04:43 GMTIs this the same as NH-1350 ? http://ayende.com/3652/a-bug-story#comment12http://ayende.com/3652/a-bug-story#comment12Tue, 21 Oct 2008 14:55:10 GMTAlready did, Jira NH-1533 http://ayende.com/3652/a-bug-story#comment11http://ayende.com/3652/a-bug-story#comment11Sun, 19 Oct 2008 18:10:09 GMTIs this still a problem in the trunk? If so, please create a JIRA issue http://ayende.com/3652/a-bug-story#comment10http://ayende.com/3652/a-bug-story#comment10Sun, 19 Oct 2008 17:59:16 GMTyeah, nasty bug and is not the only one if you execute an HQL like this select this.ProductID, this.Discontinued, this.ProductName, this.UnitPrice, this.UnitsInStock, this.UnitsOnOrder, this.ReorderLevel, this.QuantityPerUnit, this.Category.CategoryName, this.Category.CategoryID, this.Supplier.SupplierID, this.Supplier.CompanyName from Product as this order by this.Category.CategoryID asc,this.ProductName asc on sql server 2005 (Northwind) also using a limit and ordered by more than two fields will generate invalid SQL Server statement like this SELECT TOP 50 x0_0_, x1_0_, x2_0_, x3_0_, x4_0_, x5_0_, x6_0_, x7_0_, x8_0_, x9_0_, x10_0_, x11_0_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__, __hibernate_sort_expr_1__) as row, query.x0_0_, query.x1_0_, query.x2_0_, query.x3_0_, query.x4_0_, query.x5_0_, query.x6_0_, query.x7_0_, query.x8_0_, query.x9_0_, query.x10_0_, query.x11_0_, query.__hibernate_sort_expr_0__, query.__hibernate_sort_expr_1__ FROM (select product0_.ProductID as x0_0_, product0_.Discontinued as x1_0_, product0_.ProductName as x2_0_, product0_.UnitPrice as x3_0_, product0_.UnitsInStock as x4_0_, product0_.UnitsOnOrder as x5_0_, product0_.ReorderLevel as x6_0_, product0_.QuantityPerUnit as x7_0_, category1_.CategoryName as x8_0_, category1_.CategoryID as x9_0_, product0_.SupplierID as x10_0_, supplier2_.CompanyName as x11_0_, product0_.CategoryID asc as __hibernate_sort_expr_0__, product0_.ProductName as __hibernate_sort_expr_1__ from dbo.Products product0_, dbo.Categories category1_, dbo.Suppliers supplier2_ where product0_.CategoryID=category1_.CategoryID and product0_.SupplierID=supplier2_.SupplierID) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__, __hibernate_sort_expr_1__ Note the "product0_.CategoryID asc as __hibernate_sort_expr_0__" in the select field list. this is a new behavior starting R3860, previous the same setup was generating invalid orderby ORDER BY __hibernate_sort_expr_0____hibernate_sort_expr_1__ -> concatenating fields in the order by clause B. http://ayende.com/3652/a-bug-story#comment9http://ayende.com/3652/a-bug-story#comment9Sun, 19 Oct 2008 17:15:10 GMTThe bug was with NHibernate, the problem is that SQL Server syntax sucks http://ayende.com/3652/a-bug-story#comment8http://ayende.com/3652/a-bug-story#comment8Thu, 16 Oct 2008 16:22:12 GMTTo clarify, is the bug here a problem with NHibernate, or a bug in SQL Server 2005/2008? http://ayende.com/3652/a-bug-story#comment7http://ayende.com/3652/a-bug-story#comment7Thu, 16 Oct 2008 14:43:52 GMT@Ayende: Being the huge NHibernate fan that I am, I must admit here that the problems lay in NH this time. As Jason and Andrey has pointed out, had the query used named-parameters the 'problem' would have gone away. as for the 'broken' paging syntax: the thing is, that this syntax, although not trivial in first look, is actually very smart, and consistent with the way CTEs are used in SQL Server 2k5+. inventing a propriety SQL verbs like "LIMIT" looks cool, but it actually increases complexity. plus, the SQL syntax you show (which NH is generating) is weird. there is a redundant nesting level and the TOP is unneeded. what should be done to a query: 1. take out the ORDER BY clause and extract it's expression parts. 2. add the ORDER BY expression parts (without DESC/ASC) to the SELECT clause. number them NH_sort_0, NH_sort_1, etc. 3. rename the expressions in the ORDER BY clause to match their new designated names 4. add ROW_NUMBER() OVER(ORDER BY the_order_by ) as ROW 5. wrap the SELECT with an external select, with the field names from the original select, without the ROW and the injected orders 6. add "ROW BETWEEN the_first AND the_last" to the WHERE clause 7. add ORDER BY the_new_order_by so  SELECT   THIS_.ID AS ID0_0_,   THIS_.AREA AS AREA0_0_,   THIS_.PARENT AS PARENT0_0_,   THIS_.PARENTAREA AS PARENTAREA0_0_,   THIS_.TYPE AS TYPE0_0_,   THIS_.NAME AS NAME0_0_  FROM TREENODE THIS_  WHERE   THIS_.NAME LIKE ?   AND THIS_.ID > ?  ORDER BY (WHADEVER) A would become  SELECT   ID0_0_,   AREA0_0_,   PARENT0_0_,   PARENTAREA0_0_,   TYPE0_0_,   NAME0_0_  FROM (   SELECT    ROW_NUMBER OVER(ORDER BY (WHATEVER) AS _NH_ROW_,    THIS_.ID AS ID0_0_,    THIS_.AREA AS AREA0_0_,    THIS_.PARENT AS PARENT0_0_,    THIS_.PARENTAREA AS PARENTAREA0_0_,    THIS_.TYPE AS TYPE0_0_,    THIS_.NAME AS NAME0_0_,    (WHATEVER) AS _NH__SORT_0_   FROM TREENODE THIS_   WHERE    THIS_.NAME LIKE ?    AND THIS_.ID > ?  )  WHERE _NH_ROW_ BETWEEN 10 AND 1000  ORDER BY _NH__SORT_0_ is the middle query only for not repeating the order-by clause? that's crazy. I'm not sure but I think it will cause an extra table scan - first scan to generate the sort_field, second scan (in the middle query) to order by it. if you want to avoid the duplication, it's better to use a CTE for that I think. I'll see if I can spare a little time to look into the SQL dialect and try to make things a wee bit nicer in that aspect http://ayende.com/3652/a-bug-story#comment6http://ayende.com/3652/a-bug-story#comment6Thu, 16 Oct 2008 08:03:06 GMTAndrey, This related to the way NHibernate's works internally. http://ayende.com/3652/a-bug-story#comment5http://ayende.com/3652/a-bug-story#comment5Thu, 16 Oct 2008 06:31:43 GMT__This is when using the criteria API, and the criteria API uses positional parameters because that avoids the need to create named parameters. What do you mean by the 'need to create named parameters' ? I think it makes more sense to use named parameters (by some GUID?) that are translated into positional for the RDBMSs that do not support named. http://ayende.com/3652/a-bug-story#comment4http://ayende.com/3652/a-bug-story#comment4Thu, 16 Oct 2008 06:24:51 GMTJason, This is when using the criteria API, and the criteria API uses positional parameters because that avoids the need to create named parameters. They are actually translated to named parameters anyway, the bug happened during construction of the SQL by NH, not at the DB level http://ayende.com/3652/a-bug-story#comment3http://ayende.com/3652/a-bug-story#comment3Thu, 16 Oct 2008 05:57:51 GMTI've always avoided positional parameters because it fails to fully encapsulate the structure of the SQL statement. Refactoring the SQL has the potential to break the calling code. Surely NHibernate can be adapted to use named parameters with RDBMSes that support it. http://ayende.com/3652/a-bug-story#comment2http://ayende.com/3652/a-bug-story#comment2Thu, 16 Oct 2008 03:08:39 GMTI had never loved mssql paging syntax, and never will. http://ayende.com/3652/a-bug-story#comment1http://ayende.com/3652/a-bug-story#comment1Wed, 15 Oct 2008 22:19:32 GMT