Is the cross database seek on a CASTed value supposed to work this way?

Dan Holmes 2014-10-28 21:05:03

reading left to right, the first loop join index seeks into a table on a different database. If you check the details of that node you will see that it is doing an index lookup on the column based on a dateadd of the right side of join predicate.

What it SHOULD do is a scan of the entire table and filter for the value (or hash or something). This produces incorrect results. The cast to SMALLDATETIME removes seconds from the value which makes the join work. In this case the cast filter comes after the seek. This looks wrong to me.

Does it look wrong to anyone else?

2008r2 sp2 cu10

-edit-
link to the database with only this data. this is a 2008r2 compressed backup. It is 8MB in size.

https://drive.google.com/file/d/0BzB_5iArZkcma21DR1JzWElWWTA/view?usp=sharing

The sql has now been reduced to the following:

SELECT *
FROM dbo.gps l
LEFT JOIN dbo.instance_metric_history inst ON CAST(inst.MeasurementTimeUTC AS SMALLDATETIME) = DATEADD(hour, 4, l.eachmin)

-2nd edit-
and this behavior is consistent on 2012 and 2014 both RTM

SQLkiwi 2014-10-29 00:41:38
I have encountered this before. It is indeed incorrect behaviour, and very similar to the fixed issue with datetimeoffset (not datetime2(0) as in your case) and smalldatetime.

I felt sure this had been reported already, but I have been unable to locate it. In any case, the issue is localized to datetime2(0) – no other precision is affected. With datetime2(0) there is a missing GetRangeThroughConvert, so the index seeks the wrong range. Repro below:

-- No bug with datetime2(1), change to datetime2(0)
DECLARE @T1 AS table (dt datetime2(1) PRIMARY KEY);
DECLARE @T2 AS table (dt smalldatetime NOT NULL);
 
INSERT @T1 (dt) VALUES ('20140101 00:00:01');
INSERT @T2 (dt) VALUES ('20140101 00:00:00');
 
SELECT T2.dt FROM @T2 AS T2;
SELECT CONVERT(smalldatetime, T1.dt) FROM @T1 AS T1;
 
SELECT * 
FROM @T1 AS T1
JOIN @T2 AS T2
    ON T2.dt = CONVERT(smalldatetime, T1.dt);

Workaround: do not use CAST or CONVERT from datetime2(0) to smalldatetime to round date/time types, use an alternative T-SQL formulation using the built-ins DATEADD and DATEPART instead.

Dan Holmes 2014-10-29 13:08:10
Just installed SP3 for 2008r2. Problem still exists. I suppose i can open a connect item against it but i really don't have any hope they will do anything about it. They closed all my prior cases. Even one that got a sizable number of votes and comments.
http://dnhlmssql.blogspot.com/2014/10/it-is-morning-of-bad-new-for-my-connect.html

-edit-
Connect item here:

SQLkiwi 2014-10-29 20:51:21
Don't take Connect close reasons too literally. Things are often Closed As Won't Fix for the current version, but later fixed in a major release. Reporting stuff at least means we did what we could to draw attention to the issue.
Dan Holmes 2015-07-10 12:42:25
Marked Fixed:
Posted by Microsoft on 6/30/2015 at 10:42 AM
We have fixed this issue in our code base. It will be part of SQL Server 2016.
Dan Holmes 2014-11-02 01:45:18
alternative workaround:
CAST(CAST(T2.dt AS DATETIME2(1)) AS SMALLDATETIME)
Since DATETIME2(1) already works, i made the engine think it had one of those.
SQLkiwi 2015-07-10 15:01:53
@Dan That's great news! Thanks for the update.