Invalid SQL generated
Description
Environment
SQL Server 2012, Windows Server 2012 R2 64-bit
Activity
Show:
Alex ZaytsevMay 2, 2018 at 9:19 PM
Moved here.
Jukka HyvärinenJanuary 3, 2017 at 6:14 PM
Sorry that the formatting is a bit weird. It seems I can't edit the issue anymore? Also, I realized it could be a concurrency issue as well, because it seems this was happening during peak hours. Finally, @p2 seems to be correctly there, it's the "artistfors0_.Roles" part that is somehow duplicated in SQL.
Details
Details
Assignee
Unassigned
UnassignedReporter
Jukka Hyvärinen
Jukka HyvärinenLabels
Components
Affects versions
Priority
MajorWho's Looking?
Open Who's Looking?
Created January 3, 2017 at 5:54 PM
Updated May 2, 2018 at 9:22 PM
Who's Looking?
I upgraded from NH 4.0.3 to NH 4.1.0 and started getting invalid SQL errors. I'm unable to reproduce the error: it seems to happen randomly (but multiple times per day), so it's most likely related to caching of the query. I've never gotten this error with 4.0.3. I have downgraded to 4.0.3 until the issue is fixed.
Here's the stack trace:
NHibernate.Exceptions.GenericADOException could not execute query [ select artistfors0_.[Song] as col_0_0_ from dbo.ArtistsForSongs artistfors0_ inner join dbo.Songs song1_ on artistfors0_.[Song]=song1_.Id where not (song1_.[Deleted]=1) and artistfors0_.[Artist]=@p0 and not (artistfors0_.[IsSupport]=1) and (song1_.[SongType]<>@p1 or artistfors0_.[Roles] & artistfors0_.[Roles]@p2<>@p3) and artistfors0_.[Roles]<>@p4 order by song1_.PublishDate desc, song1_.[CreateDate] desc OFFSET 0 ROWS FETCH FIRST @p5 ROWS ONLY ] Name:p1 - Value:470 Name:p2 - Value:64 Name:p3 - Value:1 Name:p4 - Value:0 Name:p5 - Value:32768 Name:p6 - Value:8 [SQL: select artistfors0_.[Song] as col_0_0_ from dbo.ArtistsForSongs artistfors0_ inner join dbo.Songs song1_ on artistfors0_.[Song]=song1_.Id where not (song1_.[Deleted]=1) and artistfors0_.[Artist]=@p0 and not (artistfors0_.[IsSupport]=1) and (song1_.[SongType]<>@p1 or artistfors0_.[Roles] & artistfors0_.[Roles]@p2<>@p3) and artistfors0_.[Roles]<>@p4 order by song1_.PublishDate desc, song1_.[CreateDate] desc OFFSET 0 ROWS FETCH FIRST @p5 ROWS ONLY] System.Collections.IList DoList(NHibernate.Engine.ISessionImplementor, NHibernate.Engine.QueryParameters, NHibernate.Transform.IResultTransformer) at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Hql.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) at NHibernate.Impl.AbstractQueryImpl2.List() at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) at Remotion.Linq.QueryableBase`1.GetEnumerator() at System.Linq.Buffer`1..ctor(IEnumerable`1 source) at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) SqlException Incorrect syntax near '@p2'.As it says, the problem is
(song1_.[SongType]<>@p1 or artistfors0_.[Roles] & artistfors0_.[Roles]@p2<>@p3)which is not valid SQL. If you remove @p2 from there it's fine.
The query is very much dynamically generated from multiple expressions. Here's the C# code for the part causing the error:
return query.Where(link => (link.Song.SongType != SongType.MusicPV || (link.Roles & ArtistRoles.Animator) != ArtistRoles.Default) && link.Roles != ArtistRoles.VocalDataProvider);I got similar SQL errors from multiple other queries, not just this one.