[nhibernate-issues] [NHJIRA] Commented: (NH-2214) Distinct and Row_number problem

[Julian M. (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21305#action_21305 ] 

Julian Maughan commented on NH-2214:
------------------------------------

There is unfortunately a problem with the proposed solution that causes it to fail some of NHibernate's existing tests. For example, Criteria.CriteriaQueryTest.SubqueryPagination generates the following SQL where OVER ORDER BY is on a column that is not in the sub-query SELECT list.

SELECT TOP (1000) y0_ 
FROM ( 
	SELECT *, ROW_NUMBER() OVER(ORDER BY ???.studentId) as __hibernate_sort_row  
	FROM (
		SELECT this_0_.Name as y0_ 
		FROM Student this_0_ 
		WHERE this_0_.studentId > 1) as q_) as query 
WHERE query.__hibernate_sort_row > 1 
ORDER BY query.__hibernate_sort_row

Where the question marks are shown, there is no valid alias or table reference that will allow this statement to work. The column would have to be included in the inner-most sub-query's SELECT list, but this will cause DISTINCT queries to fail - which was the whole point of this patch.

> Distinct and Row_number problem
> -------------------------------
>
>                 Key: NH-2214
>                 URL: http://216.121.112.228/browse/NH-2214
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 2.1.1.GA, 2.1.2.GA
>            Reporter: Carlos Martinez
>            Assignee: Julian Maughan
>            Priority: Major
>         Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch, NH-2214.zip
>
>
> Microsoft.NET Framework 3.5
> MSSQL 2008
> Select distinct T.ID, T.Name from T
> inner join T2 on T.ID = T2.TID
> order by T.Name
> With this native query (using ISQLQuery) and the statement "DISTINCT" is necessary because the union wiht T2 produces duplicate results of T. 
> if I use
> SetFirstResult(index)
> SetMaxResults(max)
> It generates the following Query
> Select top max
>   ID,
>   Name  
>   from
>         (select distinct
>           ID,
>           Name,
>           ROW_NUMBER () over (order by T.Name) as __hibernate_sort_row
>           from T
>           inner join T2) as query
>     where __hibernate_sort_row > index
> first results are generated with ROW_NUMER, for example
> T.ID  T.Name   __hibernate_sort_row
> 2     Carlos   1
> 1     Juan     2
> 1     Juan     3
> 3     Paulina  4
> 4     Zunio    5
> 4     Zunio    6
> 4     Zunio    7
> Then "DISTINCT" no longer makes sense, because all records are different due to ROW_NUMBER.
> In version 2.0.1 GA works perfectly

-- 
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