v5.3.2 Invalid cast on nullable custom type with Linq Equals Method

[gokhanabatay]

Hi,
We are trying to upgrade NH 5.3.2.
We come across an issue, I implemented below test cases, we are using Equals method instead of == operator as a workaround to remove unnecessary IS NULL conditions from queries.

https://github.com/gokhanabatay/NHibernateBugTest/blob/master/NHibernateBugTest/CustomTypeSelectFailsTest.cs

Get_DateCustomType_NullableDateValueEqualsV2_IncorrectCast

 Message: 
    NHibernate.Exceptions.GenericADOException : could not execute query
    [ select usersessio0_.GUID as guid1_5_, usersessio0_.MBR_ID as mbr2_5_, usersessio0_.USER_CODE as user3_5_, usersessio0_.OPEN_DATE as open4_5_, usersessio0_.EXPIRE_DATE_TIME as expire5_5_, usersessio0_.IS_OPEN as is6_5_, usersessio0_.REMOTE_IP_ADDRESS as remote7_5_, usersessio0_.REMOTE_PORT as remote8_5_, usersessio0_.LOCAL_IP_ADDRESS as local9_5_, usersessio0_.LOCAL_PORT as local10_5_, usersessio0_.DEVICE_ID as device11_5_, usersessio0_.CLAIMS as claims12_5_ from USER_SESSION usersessio0_ where usersessio0_.MBR_ID = :p0 and usersessio0_.OPEN_DATE=:p1 ]
      Name:p1 - Value:8/14/2020 1:51:13 AM
      ----> Npgsql.PostgresException : 42883: operator does not exist: integer = timestamp without time zone
    Data:
      Severity: ERROR
      InvariantSeverity: ERROR
      SqlState: 42883
      MessageText: operator does not exist: integer = timestamp without time zone
      Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
      Position: 551
      File: parse_oper.c
      Line: 731
      Routine: op_error
      actual-sql-query: select usersessio0_.GUID as guid1_5_, usersessio0_.MBR_ID as mbr2_5_, usersessio0_.USER_CODE as user3_5_, usersessio0_.OPEN_DATE as open4_5_, usersessio0_.EXPIRE_DATE_TIME as expire5_5_, usersessio0_.IS_OPEN as is6_5_, usersessio0_.REMOTE_IP_ADDRESS as remote7_5_, usersessio0_.REMOTE_PORT as remote8_5_, usersessio0_.LOCAL_IP_ADDRESS as local9_5_, usersessio0_.LOCAL_PORT as local10_5_, usersessio0_.DEVICE_ID as device11_5_, usersessio0_.CLAIMS as claims12_5_ from USER_SESSION usersessio0_ where usersessio0_.MBR_ID = :p0 and usersessio0_.OPEN_DATE=:p1
    
  Stack Trace: 
    Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
    Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
    QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters)
    QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)
    HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)
    SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results, Object filterConnection)
    SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results)
    AbstractSessionImpl.List[T](IQueryExpression query, QueryParameters parameters)
    AbstractQueryImpl2.List[T]()
    DefaultQueryProvider.ExecuteList[TResult](Expression expression)
    IEnumerable<T>.GetEnumerator()
    List`1.AddEnumerable(IEnumerable`1 enumerable)
    List`1.ctor(IEnumerable`1 collection)
    Enumerable.ToList[TSource](IEnumerable`1 source)
    CustomTypeSelectFailsTest.Get_DateCustomType_NullableDateValueEqualsV2_IncorrectCast() line 134
    --PostgresException
    <<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
    <<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
    NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
    NpgsqlDataReader.NextResult()
    NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
    NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
    NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    AbstractBatcher.DoExecuteReader(DbCommand cmd)
    AbstractBatcher.ExecuteReader(DbCommand cmd)
    Loader.GetResultSet(DbCommand st, QueryParameters queryParameters, ISessionImplementor session, IResultTransformer forcedResultTransformer)
    Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
    Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
    Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)

Get_DateCustomType_NullableDateLetUsage_FailsWithEqualsOperator

Message: 
    System.NotSupportedException : Boolean Equals(System.Object)
  Stack Trace: 
    HqlGeneratorExpressionVisitor.VisitMethodCallExpression(MethodCallExpression expression)
    HqlGeneratorExpressionVisitor.VisitExpression(Expression expression)
    QueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
    WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
    QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
    QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
    QueryModelVisitor.Visit()
    QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root, Nullable`1 rootReturnType)
    NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter)
    ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
    QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
    AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
    AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)
    DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query)
    DefaultQueryProvider.ExecuteList[TResult](Expression expression)
    IEnumerable<T>.GetEnumerator()
    List`1.AddEnumerable(IEnumerable`1 enumerable)
    List`1.ctor(IEnumerable`1 collection)
    Enumerable.ToList[TSource](IEnumerable`1 source)
    CustomTypeSelectFailsTest.Get_DateCustomType_NullableDateLetUsage_FailsWithEqualsOperator() line 109

[gokhanabatay]

#2437
#1996

[maca88]

we are using Equals method instead of == operator as a workaround to remove unnecessary IS NULL conditions from queries.

I don't quite understand why using Equals method if you can use x.OpenDate.Value == DateTime.Now, which generates the same query. Is this just a preference?

For Get_DateCustomType_NullableDateValueEqualsV2_IncorrectCast I've made a fix as it is a regression, but for Get_DateCustomType_NullableDateLetUsage_FailsWithEqualsOperator it doesn't work even in 5.2.x. The query can be fixed by using Value instead of Equals:

where userSession.OpenDate.Value == DateTime.Now

the query will be executed without adding the IS NULL condition.

[gokhanabatay]

Before 5.3 if we use == operator NH adding unnecessary IS NULL condition, then @maca88 fixes that issue at 5.3.
"Why we use Equals" as a workaround before 5.3 we use Equals() operator so NH does not add unnecessary IS NULL condition.

As I understand we need to change Equals method with == operator.

[fredericDelaporte]

Closing as not an issue, because one part is now somehow reported and closed by #2488, and the other part has to be fixed by adjustments in user code.