2

Closed

UpForGrabs: Query: Leap year bug on CreateDateTime and CreateDateTimeOffset cannonical functions

description

"When you write something like this:
   var d = db.Ps.DefaultIfEmpty().Select(p => EntityFunctions.CreateDateTime(2012,02,29,0,0,0));
You’ll get an exception saying:
An error occurred while reading from the store provider's data reader. See the inner exception for details.
Inner exception says:
Conversion failed when converting date and/or time from character string.
The problem is in the translation:

SELECT DATEADD(year, 2012 - 1, convert (datetime2,'0001' + '-' + convert(varchar(255), 2) + '-' + convert(varchar(255), 29) + ' ' + convert(varchar(255), 0) + ':' + convert(varchar(255), 0) + ':' + str(cast(0 as float(53)), 10, 7), 121)) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
   LEFT OUTER JOIN [dbo].[P] AS [Extent1] ON 1 = 1

'0001-2-29' is an invalid date because year 1 is not a leap year. I have no idea why we translate to this awkward SQL.



Workaround:

Use this instead in LINQ:
new DateTime(2012,02,29)

Which translates into:
convert(datetime2, '2012-02-29 00:00:00.0000000', 121)"

This item was migrated from the DevDiv work item tracking system [ID=376040].
Closed Jul 22, 2013 at 10:19 PM by maumar
Verified, closing

comments

RoMiller wrote Jan 23, 2013 at 11:15 PM

EF Team Triage: Check if this was fixed in EF5

ajcvickers wrote Feb 5, 2013 at 8:38 PM

Investigation result: This bug was punted in Dev11. Email thread with additional details and repro below. Moving out of investigation for re-triage.

From: Diego Vega
Sent: Wednesday, March 7, 2012 2:12 PM
To: Pawel Kadluczka; Arwen Pond; Rowan Miller; Arthur Vickers
Cc: OpenEF Team
Subject: RE: Leap Year Bug
Importance: Low

Punted.

From: Pawel Kadluczka
Sent: Wednesday, March 07, 2012 2:07 PM
To: Diego Vega; Arwen Pond; Rowan Miller; Arthur Vickers
Cc: OpenEF Team
Subject: RE: Leap Year Bug

 The main reason I propose we don’t fix this now is because the cost of reopening the whole process to RI a change like this is relatively high and it doesn’t seem like we would get a QFE on this.
+1

Our bar for Dev11 at this point are shipstoppers and this is not one. Obviously we want to fix this post dev11.

Thanks,
Pawel

From: Diego Vega
Sent: Wednesday, March 07, 2012 1:37 PM
To: Arwen Pond; Rowan Miller; Arthur Vickers
Cc: OpenEF Team
Subject: RE: Leap Year Bug

The safest fix right now would be as simple as changing a couple of constants from 1 to 4 in the translation. From comments in the code that handles this:
          ///     PRE KATMAI: convert(varchar(255), @year) + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day)
          ///         KATMAI: DateAdd(year, @year-1, covert(@typename, '0001' + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day)  + [possibly time ], 121)     
I am not super happy with that fix for the long term because I would really like to understand if we can simplify the translation instead.

The main reason I propose we don’t fix this now is because the cost of reopening the whole process to RI a change like this is relatively high and it doesn’t seem like we would get a QFE on this.

Thanks,
Diego

From: Arwen Pond
Sent: Wednesday, March 07, 2012 10:38 AM
To: Diego Vega; Rowan Miller; Arthur Vickers
Cc: OpenEF Team
Subject: RE: Leap Year Bug

I would like to know the complexity of the fix.

Sent from my Windows Phone

From: Diego Vega
Sent: 3/7/2012 10:32 AM
To: Rowan Miller; Arthur Vickers
Cc: OpenEF Team
Subject: RE: Leap Year Bug
Any pushback from Test on punting this?

From: Rowan Miller
Sent: Wednesday, March 07, 2012 10:28 AM
To: Arthur Vickers; Diego Vega
Cc: OpenEF Team
Subject: RE: Leap Year Bug

+1 Let’s punt it

From: Arthur Vickers
Sent: Tuesday, March 06, 2012 11:08 PM
To: Diego Vega
Cc: Rowan Miller; OpenEF Team
Subject: Re: Leap Year Bug

+1

Sent from aPhone

On Mar 6, 2012, at 10:32 PM, "Diego Vega" wrote:
I filed this as #376040. We now need to triage it, so here is what I have:

It is an embarrassing bug but apparently not one that customers will hit a lot. The workaround (using the constructor of DateTime in a LINQ query) is easier to find than the canonical function, and works with constants or parameters. Even defining DateTime literals in ESQL, (e.g. DATETIME '2012-2-29') doesn’t hit the canonical function. Give this my recommendation is to punt the bug post Dev11, but I will talk to some ex-Entity Services people to see if I find why the translation is like this.

From: Diego Vega
Sent: Tuesday, March 06, 2012 4:01 PM
To: Rowan Miller; OpenEF Team
Cc: Diego Vega
Subject: RE: Leap Year Bug

(Sorry it took longer than I expected. I am back from meetings, but just for a while)

There is indeed a leap year bug in the canonical function  Fortunately there is an easy workaround: just use LINQ:

new DateTime(2012,02,29)

Which translates into:

convert(datetime2, '2012-02-29 00:00:00.0000000', 121)

I have responded to the customer in his blog and I will file this as soon as I get back on a connection.

Details
When you write something like this:
using System;
using System.Data.Entity;
using System.Linq;
using System.Data.Objects;
 
namespace ConsoleApplication5
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new Context())
            {
                var d = db.Ps.DefaultIfEmpty().Select(p => EntityFunctions.CreateDateTime(2012,02,29,0,0,0));
                Console.WriteLine(d.FirstOrDefault());
            }
        }
    }
 
    public class P
    {
        public int Id { get; set; }
    }
 
    public class Context : DbContext
    {
        public DbSet<P> Ps { get; set; }
    }
}
You’ll get an exception saying:

An error occurred while reading from the store provider's data reader. See the inner exception for details.

Inner exception says:
Conversion failed when converting date and/or time from character string.
The problem is in the translation:
SELECT DATEADD(year, 2012 - 1, convert (datetime2,'0001' + '-' + convert(varchar(255), 2) + '-' + convert(varchar(255), 29) + ' ' + convert(varchar(255), 0) + ':' + convert(varchar(255), 0) + ':' + str(cast(0 as float(53)), 10, 7), 121)) AS [C1]
FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
       LEFT OUTER JOIN [dbo].[P] AS [Extent1] ON 1 = 1
The highlighted part is an intermediary result but it is an invalid date because year “1” is not a leap year. I have no idea why we translate to this awkward SQL.

Notice that the customer had a bigger problem in his snipped because he had the month and the day swapped.

Thanks,
Diego

From: Diego Vega
Sent: Tuesday, March 06, 2012 12:52 PM
To: Rowan Miller; OpenEF Team
Subject: RE: Leap Year Bug

I started looking at this. Will reply back in a few minutes.

From: Rowan Miller
Sent: Tuesday, March 06, 2012 11:56 AM
To: OpenEF Team
Subject: Leap Year Bug

Hey,

Anyone seen this before or done any investigation? http://www.morgia.info/BlogEngine/post/2012/02/29/Entity-Framework-EntityFunctionsCreateDateTime-and-Leap-Year.aspx

Regards,
Rowan Miller
Program Manager | ADO.Net Entity Framework

maumar wrote Jul 17, 2013 at 1:31 AM

This is still broken for dates before year 1000 (i.e. where year component is not 4 digits long).

e.g.:

ctx.Entities.Select(e => EntityFunctions.CreateDateTime(999, 1, 1, 1, 1, 1)).ToList().

This used to work before the fix

maumar wrote Jul 17, 2013 at 5:51 PM

Stack trace:
Unhandled Exception: System.Data.SqlClient.SqlException: Conversion failed when
converting date and/or time from character string.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection, Action
1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Bo
olean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.Entity.Core.Objects.Internal.BufferedDataReader.Initialize(Str
ing providerManifestToken, DbProviderServices providerSerivces) in d:\entityfram
ework\src\EntityFramework\Core\Objects\Internal\BufferedDataReader.cs:line 186
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[
TResultType](ObjectContext context, ObjectParameterCollection parameterValues) i
n d:\entityframework\src\EntityFramework\Core\Objects\Internal\ObjectQueryExecut
ionPlan.cs:line 135
at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClassb.<GetResul
ts>b__a() in d:\entityframework\src\EntityFramework\Core\Objects\ObjectQuery
.cs
:line 735
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func
1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction,
Boolean releaseConnectionOnSuccess) in d:\entityframework\src\EntityFramework\Co
re\Objects\ObjectContext.cs:line 3175
at System.Data.Entity.Core.Objects.ObjectQuery
1.<>c__DisplayClassb.<GetResul
ts>b__9() in d:\entityframework\src\EntityFramework\Core\Objects\ObjectQuery.cs
:line 734
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](
Func
1 operation) in d:\entityframework\src\EntityFramework.SqlServer\DefaultSql
ExecutionStrategy.cs:line 52
at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMer
geOption) in d:\entityframework\src\EntityFramework\Core\Objects\ObjectQuery.cs
:line 733
at System.Data.Entity.Core.Objects.ObjectQuery
1.<System.Collections.Generic.
IEnumerable<T>.GetEnumerator>b__0() in d:\entityframework\src\EntityFramework\Co
re\Objects\ObjectQuery.cs:line 567
at System.Lazy
1.CreateValue()
at System.Lazy1.LazyInitValue()
at System.Lazy
1.get_Value()
at System.Data.Entity.Internal.LazyEnumerator1.MoveNext() in d:\entityframew
ork\src\EntityFramework\Internal\LazyEnumerator
.cs:line 38
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Verify193.Program.Main(String[] args) in d:\Projects\Verify193\Verify193\P
rogram.cs:line 50

emilcicos wrote Jul 19, 2013 at 5:52 PM

Added code to ensure 4 digits year.