Comparison with DateTime? produces wrong SQL

[andyci]

Hi,

Namespace Issues

    Namespace NA

        Public Class Entity

            Private _id As Guid
            Public Overridable Property Id() As Guid
                Get
                    Return _id
                End Get
                Set(ByVal value As Guid)
                    _id = value
                End Set
            End Property

            Private _date1 As Date?
            Public Overridable Property Date1() As Date?
                Get
                    Return _date1
                End Get
                Set(ByVal value As Date?)
                    _date1 = value
                End Set
            End Property


        End Class

    End Namespace

End Namespace

Using session As ISession = OpenSession()
    Dim d = New Date(2017, 12, 2)
    Dim result = (From e In session.Query(Of Entity)()
    Where e.Date1 >= d
    Select e).ToList()
    Assert.AreEqual(0, result.Count)
End Using

produces the following strange SQL:

[ select entity0_.Id as id1_0_, entity0_.Date1 as date2_0_
    from Entity entity0_
    where coalesce(entity0_.Date1>=@p0, @p1)=1 ]
  Name:p1 - Value:02/12/2017 00:00:00  Name:p2 - Value:False

Not sure if this is VB specific or whether it happens in C# as well.

Tested on NHibernate 5.0.3

[fredericDelaporte]

Hi,
I guess Date1 is nullable: better not have to guess, can you tell what is the entity definition and its mapping please? And with which target database do you have the trouble, for which error symptom? (Invalid syntax? Something else?)

[andyci]

@fredericDelaporte I posted the entity, test case, and generated SQL in the original issue. The mapping is pretty much identical to the other test cases in the NHibernate.Test.VisualBasic project (just rename the Name property to Date1). Not sure why you can't see it? The SQL version is MSSQL 2016.

Thanks

[fredericDelaporte]

Yes sorry, there is just the db and the error message or troubles it causes which are missing.

[andyci]

Error message is:

Incorrect syntax near '>'

[hazzik]

Seems to be VB specific. But in rare cases could happen in C# as well.

[hazzik]

@andyci did it work in NH 4.x?

[hazzik]

Although it's marked as fixed I would still like to know, if that did or did not work in 4.1.