Menu
▾
▴
nhibernate-issues
|
From: Fabio M. (JIRA) <nh...@gm...> - 2010-07-23 15:51:13
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19453#action_19453 ]
Fabio Maulo commented on NH-2214:
---------------------------------
Yes...we have some wrong tests
> 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
> Priority: Major
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-08-24 13:36:50
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Marcin Daczkowski updated NH-2214:
----------------------------------
Attachment: NH-2214.patch
I am attaching patch for solving this problem in 2.1.2 ga version
> 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
> Priority: Major
> Attachments: NH-2214.patch
>
>
> 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
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2010-08-24 20:30:02
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19709#action_19709 ]
Fabio Maulo commented on NH-2214:
---------------------------------
perhaps good effort but unusable.
We need a failing test and, only if you can, a patch.
Please try to follow this post
http://nhforge.org/blogs/nhibernate/archive/2008/10/04/the-best-way-to-solve-nhibernate-bugs-submit-good-unit-test.aspx
(also available in the Welcome message, top left corner, of this JIRA)
> 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
> Priority: Major
> Attachments: NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-08-30 09:23:01
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19738#action_19738 ]
Marcin Daczkowski commented on NH-2214:
---------------------------------------
Well the patch was incorrect for some cases. We fixed this today, but this is not clean solution at all I guess, so I don't even attach the patch for this.
We will work on the unit test later this week and I will upload it then.
> 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
> Priority: Major
> Attachments: NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-09-06 22:02:45
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Marcin Daczkowski updated NH-2214:
----------------------------------
Attachment: NH-2214-reproduction.zip
> 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
> Priority: Major
> Attachments: NH-2214-reproduction.zip, NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-09-06 22:04:52
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19791#action_19791 ]
Marcin Daczkowski commented on NH-2214:
---------------------------------------
The reproduction is attached. Althought the fix is more like a workaround, it proved to work for us, hence I am attaching it just in case it is useful for you/anyone.
> 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
> Priority: Major
> Attachments: NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-09-06 22:04:56
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Marcin Daczkowski updated NH-2214:
----------------------------------
Attachment: NH-2214.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
> Priority: Major
> Attachments: NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Ayende R. (JIRA) <nh...@gm...> - 2010-10-07 16:38:08
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19987#action_19987 ]
Ayende Rahien commented on NH-2214:
-----------------------------------
I can't apply the patches
Can you provide a standard SVN DIFF formatted 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
> Priority: Major
> Attachments: NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-10-11 23:34:46
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20019#action_20019 ]
Marcin Daczkowski commented on NH-2214:
---------------------------------------
to be honest I am unsure how to do that. the patch I attached was prepared using cygwin patch -crB command and should be applied using patch command as described here: http://linux.byexamples.com/archives/163/how-to-create-patch-file-using-patch-and-diff/.
The reason I followed the above approach is that I've downloaded 2.1.2GA sources from the site not from SVN, so I believe I can't use standard svn tooling (if you know how use svn diff in this case please let me know).
I am also attaching MsSql2005Dialect.cs as the change I applied is this single file only.
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-10-11 23:34:48
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Marcin Daczkowski updated NH-2214:
----------------------------------
Attachment: MsSql2005Dialect.cs
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-10-11 23:42:06
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20020#action_20020 ]
Marcin Daczkowski commented on NH-2214:
---------------------------------------
Also - would this applied for 2.x or only for 3.x? Having this fixed in 2.x would be great as we would be able to switch back to vanilla build of NH in one of our projects (we do not plan 3.x migration near soon).
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2010-10-12 16:51:01
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20023#action_20023 ]
Fabio Maulo commented on NH-2214:
---------------------------------
NH2.x is no more under development since around a year.
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-10-23 18:38:52
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20058#action_20058 ]
Marcin Daczkowski commented on NH-2214:
---------------------------------------
I don't get this. Isn't 2.x production and 3.x still in alpha? Does your answer mean you don't fix any bugs in production version at all?
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2010-10-24 23:08:53
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20063#action_20063 ]
Fabio Maulo commented on NH-2214:
---------------------------------
We have fixed a lot.
For that reason after 2.0.0 you saw 2.0.1, 2.1.0, 2.1.1 and finally 2.1.2
NH3 is not in alpha but in beta and the candidate release is closer.
There is no reason to release another 2.x.
btw you can call NH as release 5252 because for us each commit is a candidate to be used in production and even if for us are not enough you can't ignore our 2700+ tests.
The quality is not achieved by the number somebody gave to a deployment.
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Marcin D. (JIRA) <nh...@gm...> - 2010-10-28 20:38:16
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20075#action_20075 ]
Marcin Daczkowski commented on NH-2214:
---------------------------------------
"We have fixed a lot.
For that reason after 2.0.0 you saw 2.0.1, 2.1.0, 2.1.1 and finally 2.1.2 "
I don't doubt this. I appreciate your work.
"NH3 is not in alpha but in beta and the candidate release is closer.
There is no reason to release another 2.x. "
Maybe I don't get something. Would you encourage to user 3.x in production code?
"btw you can call NH as release 5252 because for us each commit is a candidate to be used in production and even if for us are not enough you can't ignore our 2700+ tests.
The quality is not achieved by the number somebody gave to a deployment."
Sorry but I don't get this completely. If this is relevant please elaborate.
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Brian G. (JIRA) <nh...@gm...> - 2011-03-31 13:30:11
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20810#action_20810 ]
Brian Gehrs commented on NH-2214:
---------------------------------
I just tried NH 3.1.0.GA, and I experienced the same duplication issue. Is there any reason that this patch was not applied to NH 3.1? I see above that this bug is only associated with these: Affects Version/s: 2.1.1.GA, 2.1.2.GA
Maybe the versions that this issue is attached to should be expanded to include NH 3?
Thanks,
Brian Gehrs
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Mario D. L. (JIRA) <nh...@gm...> - 2011-04-10 18:46:22
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20870#action_20870 ]
Mario Dal Lago commented on NH-2214:
------------------------------------
In NHibernate 3.0.0.2002 the issue still exists. My question is the same as the last comment. Is there any reason that this patch was not applied to NH 3.xx?
Thanks.
Mario Dal Lago
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2011-04-10 23:08:21
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20872#action_20872 ]
Fabio Maulo commented on NH-2214:
---------------------------------
@Mario
Perhaps the test ?
> 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
> Priority: Major
> Attachments: MsSql2005Dialect.cs, NH-2214-reproduction.zip, NH-2214.patch, NH-2214.patch
>
>
> 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
|
|
From: Mario D. L. (JIRA) <nh...@gm...> - 2011-04-11 01:54:59
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Dal Lago updated NH-2214:
-------------------------------
Attachment: NH-2214.zip
Simple test case showing the issue
> 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
> 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
|
|
From: david (JIRA) <nh...@gm...> - 2011-04-11 08:14:25
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20879#action_20879 ]
david commented on NH-2214:
---------------------------
Hi, I also notice this in the new version too.
I still have to use my work around (Custom SQL dialect) to get around this :-(
http://www.webdevbros.net/2010/11/11/nhibernate-returns-duplicate-results-on-paged-data-sets-work-around/
> 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
> 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
|
|
From: Julian M. (JIRA) <nh...@gm...> - 2011-04-11 14:48:04
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Maughan reassigned NH-2214:
----------------------------------
Assignee: Julian Maughan
> 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
|
|
From: Julian M. (JIRA) <nh...@gm...> - 2011-06-19 16:47:32
|
[ 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
|
|
From: Julian M. (JIRA) <nh...@gm...> - 2011-06-21 16:06:45
|
[ 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
|
|
From: Julian M. (JIRA) <nh...@gm...> - 2011-06-21 16:08:16
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Maughan updated NH-2214:
-------------------------------
Attachment: NH-2214.solution.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.solution.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
|
|
From: Chuck L. (JIRA) <nh...@gm...> - 2011-06-21 16:19:41
|
[ http://216.121.112.228/browse/NH-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21317#action_21317 ]
Chuck Lewin commented on NH-2214:
---------------------------------
I hope this fix gets into 3.2; my users urgently need it to be addressed and I'd rather not deal with the various workarounds presented above.
> 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.solution.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
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X