[nhibernate-issues] [NHJIRA] Updated: (NH-2746) Invalid SQL generated for MSSQL when using Filter and paging subquery together [regression from 2.1]

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

     [ 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