NH-3928 - Random invalid SQL generated when using bitwise operators

[nhibernate-bot]

Jukka Hyvärinen created an issue — 3rd January 2017, 17:54:46:

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 
```sql
(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.

---

Jukka Hyvärinen added a comment — 3rd January 2017, 18:14:11:

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.

[bocca]

Same (or similar) problem with Hibernate 4.1.1.4000. I suspect there is a problem with the "&" operator. Today, after adding a condition with "&" in a Linq Query, the application started to throw "SqlException: Incorrect syntax near '&'" errors in two queries. I restarted the application and the problem dissapeared. Inspecting the error logs, I noticed that the SQL code of two totally unrelated queries were mixed. Both queries uses the "&" operator, as in:

.Where(m => (m.Message.CommunicationChannel & MessageCommunicationChannel.ControlPanel) != 0)

The SQL code executed is:

select [...]
from [dbo].MessageRecipients messagerec0_
inner join [dbo].Messages message1_ on messagerec0_.MessageId=message1_.Id
where appraisee3_.Optionsmessage1_.CommunicationChannel & & @p2<>@p3

Notice the "appraisee3_.Optionsmessage1_.CommunicationChannel & & @p2<>@p3" part. Note the double "&". The correct code should be "message1_.CommunicationChannel & @p2<>@p3". The "appraisee3_.Options" part is from another unrelated query. There is no "appraisee3_" table alias in the query!

In another query, the same fagment "appraisee3_.Optionsmessage1_.CommunicationChannel & & @p2<>@p3" appeared, but this time, the correct code should be "appraisee3_.Options & @p2<>@p3", and again, the other table alias "message1_" does not appear in que query.

[hazzik]

It seems to be a regression of #280 (Fixed in #1670).

[hazzik]

@fredericDelaporte & @bocca could you please confirm?

[hazzik]

@fredericDelaporte do we want this to be back-ported to 4.1.x and 5.0.x?

[fredericDelaporte]

#1670 fix a bug that can precisely cause the trouble here. So yes, it is very likely fixing the trouble reported here.

Back-porting to 5.0.x would be better. (But there will be no-async regen for the tests, I think just porting them (async included) from 5.1.x should be good enough though).
About 4.1.x, it would be preferable too, but I do not know if the release build works well in this branch.