[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=21316#action_21316 ] 

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

I've spent additional time on this issue, and reached a reasonable solution. A patch for the current code-line is attached, and I will ask Fabio to review it to see if it can be included in 3.2.

The solution I'm putting forward is to only use the improved paging query when necessary; i.e. only when the statement to be 'paged' is a SELECT DISTINCT statement. Non-distinct queries use the current SQL. There is still an edge case that fails: a query with distinct results, and ordered by a non-projected/selected column. It fails because SQL Server can't handle 'ROW_NUMBER() OVER(ORDER BY column1)' if 'column1' isn't in the query's SELECT list - it is the scenario I explained above. On a positive note, my code checks for this case and an exception is thrown to explain the reason.

> 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