Menu
▾
▴
nhibernate-issues
|
From: Pete A. (JIRA) <nh...@gm...> - 2011-05-27 09:46:56
|
Invalid SQL generated for MSSQL when using Filter and paging subquery together [regression from 2.1]
----------------------------------------------------------------------------------------------------
Key: NH-2746
URL: http://216.121.112.228/browse/NH-2746
Project: NHibernate
Issue Type: Patch
Components: Core
Affects Versions: 3.1.0
Reporter: Pete Appleton
Priority: Major
Attachments: NHTest.zip, QueryParams.patch
On MS SQL Server 2005/2008, creating an ICriteria for a parent table with a join onto a child table and a paging subquery fails with a SQL exception if a filter is applied to the collection in the mapping and then enabled in code. If the filter is not enabled then the query functions as expected.
There's a very small test project provided to illustrate the problem; unfortunately, I've been unable to create a unit test using the template as I'm getting an error ["Could not find the dialect in the configuration"]; there is also a patch which appears to correct the problem, but it has not been subjected to the unit test suite.
NHibernate exception details:
NHibernate.Exceptions.GenericADOException: could not execute query
[ SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row) ]
[SQL: SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row)] ---> System.Data.SqlClient.SqlException: Must declare the scalar variable "@p1".
Incorrect syntax near the keyword 'as'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
...
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:line 247
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1349
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 413
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 243
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1694
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1703
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1601
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1595
at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 74
at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1919
at NHibernate.Impl.CriteriaImpl.List(IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 265
at NHibernate.Impl.CriteriaImpl.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 276
at NHTest.Program.TestQuery() in D:\SVN\Podium\Source\NHTest\Program.cs:line 69
at NHTest.Program.Main(String[] args) in D:\SVN\Podium\Source\NHTest\Program.cs:line 26
This is being run with .NET v4, though I don't believe that the framework version has an bearing.
--
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-05-31 12:24:56
|
[ http://216.121.112.228/browse/NH-2746?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21238#action_21238 ]
Fabio Maulo commented on NH-2746:
---------------------------------
Note fore committers:
Do not apply that patch if you don't want see other problems.
The work to do is the same done in QueryLoader with parameters backtrack.
> Invalid SQL generated for MSSQL when using Filter and paging subquery together [regression from 2.1]
> ----------------------------------------------------------------------------------------------------
>
> Key: NH-2746
> URL: http://216.121.112.228/browse/NH-2746
> Project: NHibernate
> Issue Type: Patch
> Components: Core
> Affects Versions: 3.1.0
> Reporter: Pete Appleton
> Priority: Major
> Attachments: NHTest.zip, QueryParams.patch
>
>
> On MS SQL Server 2005/2008, creating an ICriteria for a parent table with a join onto a child table and a paging subquery fails with a SQL exception if a filter is applied to the collection in the mapping and then enabled in code. If the filter is not enabled then the query functions as expected.
> There's a very small test project provided to illustrate the problem; unfortunately, I've been unable to create a unit test using the template as I'm getting an error ["Could not find the dialect in the configuration"]; there is also a patch which appears to correct the problem, but it has not been subjected to the unit test suite.
> NHibernate exception details:
> NHibernate.Exceptions.GenericADOException: could not execute query
> [ SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row) ]
> [SQL: SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row)] ---> System.Data.SqlClient.SqlException: Must declare the scalar variable "@p1".
> Incorrect syntax near the keyword 'as'.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
> ...
> at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
> at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
> at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:line 247
> at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1349
> at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 413
> at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 243
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1694
> --- End of inner exception stack trace ---
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1703
> at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1601
> at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1595
> at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 74
> at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1919
> at NHibernate.Impl.CriteriaImpl.List(IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 265
> at NHibernate.Impl.CriteriaImpl.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 276
> at NHTest.Program.TestQuery() in D:\SVN\Podium\Source\NHTest\Program.cs:line 69
> at NHTest.Program.Main(String[] args) in D:\SVN\Podium\Source\NHTest\Program.cs:line 26
> This is being run with .NET v4, though I don't believe that the framework version has an bearing.
--
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-05-31 12:24:59
|
[ http://216.121.112.228/browse/NH-2746?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fabio Maulo updated NH-2746:
----------------------------
Issue Type: Bug (was: Patch)
> Invalid SQL generated for MSSQL when using Filter and paging subquery together [regression from 2.1]
> ----------------------------------------------------------------------------------------------------
>
> Key: NH-2746
> URL: http://216.121.112.228/browse/NH-2746
> Project: NHibernate
> Issue Type: Bug
> Components: Core
> Affects Versions: 3.1.0
> Reporter: Pete Appleton
> Priority: Major
> Attachments: NHTest.zip, QueryParams.patch
>
>
> On MS SQL Server 2005/2008, creating an ICriteria for a parent table with a join onto a child table and a paging subquery fails with a SQL exception if a filter is applied to the collection in the mapping and then enabled in code. If the filter is not enabled then the query functions as expected.
> There's a very small test project provided to illustrate the problem; unfortunately, I've been unable to create a unit test using the template as I'm getting an error ["Could not find the dialect in the configuration"]; there is also a patch which appears to correct the problem, but it has not been subjected to the unit test suite.
> NHibernate exception details:
> NHibernate.Exceptions.GenericADOException: could not execute query
> [ SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row) ]
> [SQL: SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row)] ---> System.Data.SqlClient.SqlException: Must declare the scalar variable "@p1".
> Incorrect syntax near the keyword 'as'.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
> ...
> at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
> at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
> at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:line 247
> at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1349
> at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 413
> at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 243
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1694
> --- End of inner exception stack trace ---
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1703
> at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1601
> at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1595
> at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 74
> at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1919
> at NHibernate.Impl.CriteriaImpl.List(IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 265
> at NHibernate.Impl.CriteriaImpl.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 276
> at NHTest.Program.TestQuery() in D:\SVN\Podium\Source\NHTest\Program.cs:line 69
> at NHTest.Program.Main(String[] args) in D:\SVN\Podium\Source\NHTest\Program.cs:line 26
> This is being run with .NET v4, though I don't believe that the framework version has an bearing.
--
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: Pete A. (JIRA) <nh...@gm...> - 2011-05-31 13:55:57
|
[ http://216.121.112.228/browse/NH-2746?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21239#action_21239 ]
Pete Appleton commented on NH-2746:
-----------------------------------
Thanks Fabio - do you have either an issue number that I can refer to for the work you mentioned, and ideally any pointers as to what I might be doing wrong with creating the correct unit test?
> Invalid SQL generated for MSSQL when using Filter and paging subquery together [regression from 2.1]
> ----------------------------------------------------------------------------------------------------
>
> Key: NH-2746
> URL: http://216.121.112.228/browse/NH-2746
> Project: NHibernate
> Issue Type: Bug
> Components: Core
> Affects Versions: 3.1.0
> Reporter: Pete Appleton
> Priority: Major
> Attachments: NHTest.zip, QueryParams.patch
>
>
> On MS SQL Server 2005/2008, creating an ICriteria for a parent table with a join onto a child table and a paging subquery fails with a SQL exception if a filter is applied to the collection in the mapping and then enabled in code. If the filter is not enabled then the query functions as expected.
> There's a very small test project provided to illustrate the problem; unfortunately, I've been unable to create a unit test using the template as I'm getting an error ["Could not find the dialect in the configuration"]; there is also a patch which appears to correct the problem, but it has not been subjected to the unit test suite.
> NHibernate exception details:
> NHibernate.Exceptions.GenericADOException: could not execute query
> [ SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row) ]
> [SQL: SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row)] ---> System.Data.SqlClient.SqlException: Must declare the scalar variable "@p1".
> Incorrect syntax near the keyword 'as'.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
> ...
> at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
> at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
> at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:line 247
> at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1349
> at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 413
> at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 243
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1694
> --- End of inner exception stack trace ---
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1703
> at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1601
> at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1595
> at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 74
> at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1919
> at NHibernate.Impl.CriteriaImpl.List(IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 265
> at NHibernate.Impl.CriteriaImpl.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 276
> at NHTest.Program.TestQuery() in D:\SVN\Podium\Source\NHTest\Program.cs:line 69
> at NHTest.Program.Main(String[] args) in D:\SVN\Podium\Source\NHTest\Program.cs:line 26
> This is being run with .NET v4, though I don't believe that the framework version has an bearing.
--
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-05-31 15:12:58
|
[ http://216.121.112.228/browse/NH-2746?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21240#action_21240 ]
Fabio Maulo commented on NH-2746:
---------------------------------
The work to do is well known in our dev-list.
The failing test is pretty complex involving query-cache and addition of filters.
> Invalid SQL generated for MSSQL when using Filter and paging subquery together [regression from 2.1]
> ----------------------------------------------------------------------------------------------------
>
> Key: NH-2746
> URL: http://216.121.112.228/browse/NH-2746
> Project: NHibernate
> Issue Type: Bug
> Components: Core
> Affects Versions: 3.1.0
> Reporter: Pete Appleton
> Priority: Major
> Attachments: NHTest.zip, QueryParams.patch
>
>
> On MS SQL Server 2005/2008, creating an ICriteria for a parent table with a join onto a child table and a paging subquery fails with a SQL exception if a filter is applied to the collection in the mapping and then enabled in code. If the filter is not enabled then the query functions as expected.
> There's a very small test project provided to illustrate the problem; unfortunately, I've been unable to create a unit test using the template as I'm getting an error ["Could not find the dialect in the configuration"]; there is also a patch which appears to correct the problem, but it has not been subjected to the unit test suite.
> NHibernate exception details:
> NHibernate.Exceptions.GenericADOException: could not execute query
> [ SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row) ]
> [SQL: SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row)] ---> System.Data.SqlClient.SqlException: Must declare the scalar variable "@p1".
> Incorrect syntax near the keyword 'as'.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
> ...
> at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
> at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
> at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:line 247
> at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1349
> at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 413
> at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 243
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1694
> --- End of inner exception stack trace ---
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1703
> at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1601
> at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1595
> at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 74
> at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1919
> at NHibernate.Impl.CriteriaImpl.List(IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 265
> at NHibernate.Impl.CriteriaImpl.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 276
> at NHTest.Program.TestQuery() in D:\SVN\Podium\Source\NHTest\Program.cs:line 69
> at NHTest.Program.Main(String[] args) in D:\SVN\Podium\Source\NHTest\Program.cs:line 26
> This is being run with .NET v4, though I don't believe that the framework version has an bearing.
--
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-06-19 21:21:06
|
[ http://216.121.112.228/browse/NH-2746?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fabio Maulo resolved NH-2746.
-----------------------------
Resolution: Fixed
Fix Version/s: 3.2.0Beta2
As said ;)
> Invalid SQL generated for MSSQL when using Filter and paging subquery together [regression from 2.1]
> ----------------------------------------------------------------------------------------------------
>
> Key: NH-2746
> URL: http://216.121.112.228/browse/NH-2746
> Project: NHibernate
> Issue Type: Bug
> Components: Core
> Affects Versions: 3.1.0
> Reporter: Pete Appleton
> Priority: Major
> Fix For: 3.2.0Beta2
>
> Attachments: NHTest.zip, QueryParams.patch
>
>
> On MS SQL Server 2005/2008, creating an ICriteria for a parent table with a join onto a child table and a paging subquery fails with a SQL exception if a filter is applied to the collection in the mapping and then enabled in code. If the filter is not enabled then the query functions as expected.
> There's a very small test project provided to illustrate the problem; unfortunately, I've been unable to create a unit test using the template as I'm getting an error ["Could not find the dialect in the configuration"]; there is also a patch which appears to correct the problem, but it has not been subjected to the unit test suite.
> NHibernate exception details:
> NHibernate.Exceptions.GenericADOException: could not execute query
> [ SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row) ]
> [SQL: SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, children2_.T1Id as T3_3_, children2_.Id as Id3_, children2_.Id as Id1_0_, children2_.Name as Name1_0_, children2_.T1Id as T3_1_0_ FROM dbo.T1 this_ left outer join dbo.T2 children2_ on this_.Id=children2_.T1Id and @p0=children2_.Name WHERE this_.Id in (SELECT TOP (@p1) y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Id) as __hibernate_sort_row FROM dbo.T1 this_0_) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row)] ---> System.Data.SqlClient.SqlException: Must declare the scalar variable "@p1".
> Incorrect syntax near the keyword 'as'.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
> ...
> at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
> at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
> at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:line 247
> at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1349
> at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 413
> at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 243
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1694
> --- End of inner exception stack trace ---
> at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1703
> at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1601
> at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1595
> at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 74
> at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1919
> at NHibernate.Impl.CriteriaImpl.List(IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 265
> at NHibernate.Impl.CriteriaImpl.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 276
> at NHTest.Program.TestQuery() in D:\SVN\Podium\Source\NHTest\Program.cs:line 69
> at NHTest.Program.Main(String[] args) in D:\SVN\Podium\Source\NHTest\Program.cs:line 26
> This is being run with .NET v4, though I don't believe that the framework version has an bearing.
--
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
|
Thanks for helping keep SourceForge clean.
X