NH-3799 - GetValueOrDefault() is broken for DateTime?

[nhibernate-bot]

Kevin Low created an issue — 16th June 2015, 23:29:49:

I have an Oracle table with a Date data type called TEST_DATE. When I try to do:

session.Query<StdntAsesmnt>()
   .Where(x => x.StdntKey == studntKey)
   .Select(x => x.TestDate.GetValueOrDefault())
   .ToList());

NHibernate.Exceptions.GenericADOException: could not execute query < select nvl(stdntasesm0_.TEST_DATE, '1/1/0001 12:00:00 AM') as col_0_0_, stdntasesm0_.TEST_DATE as col_1_0_ from EAS_ADMIN.STDNT_ASESMNT stdntasesm0_ where stdntasesm0_.STDNT_KEY=:p0 order by stdntasesm0_.ASESMNT_SUBJCT_KEY asc > Name:p1 - Value:261870 [SQL: select nvl(stdntasesm0_.TEST_DATE, '1/1/0001 12:00:00 AM') as col_0_0_, stdntasesm0_.TEST_DATE as col_1_0_ from EAS_ADMIN.STDNT_ASESMNT stdntasesm0_ where stdntasesm0_.STDNT_KEY=:p0 order by stdntasesm0_.ASESMNT_SUBJCT_KEY asc] ---> Oracle.DataAccess.Client.OracleException: ORA-01843: not a valid month Result StandardOutput: NHibernate: select nvl(stdntasesm0_.TEST_DATE, '1/1/0001 12:00:00 AM') as col_0_0_, stdntasesm0_.TEST_DATE as col_1_0_ from EAS_ADMIN.STDNT_ASESMNT stdntasesm0_ where stdntasesm0_.STDNT_KEY=:p0 order by stdntasesm0_.ASESMNT_SUBJCT_KEY asc;:p0 = 261870 <Type: Int32 (0)>

This works in NHibernate 3.1 which gives me

select nvl(stdntasesm0_.TEST_DATE, to_date('1/1/0001 12:00:00 AM', 'mm/DD/yyyy hh:MI:ss AM')

The generated SQL needs the to_date function to be valid.

[hazzik]

This is not oracle specific.

[hazzik]

Basically it was "working" in the select clause because it was translated on a client.

[vladsaftoiu]

I can confirm tha plain nullableDate.GetValueOrDefault() will produce an incomplete date format:

.OrderByDescending(x => x.CurrentDefault.GetValueOrDefault())
//will produce this:

order by nvl(profileope0_.CURRENT_DEFAULT, '01/01/0001 00:00:00') desc

which will result in "not a valid Month" exception.

passing a default value to the method will instead enforce the correct format:

.OrderByDescending(x => x.CurrentDefault.GetValueOrDefault(DateTime.MinValue))
//will produce this:

order by nvl(profileope0_.CURRENT_DEFAULT, '0001-01-01T00:00:00.0000000 [Type: DateTime (0:0:0)]' /* :p2 */) desc`

[hazzik]

There is no meaningful default for the datetime.