Is the cross database seek on a CASTed value supposed to work this way?
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
link to the database with only this data. this is a 2008r2 compressed backup. It is 8MB in size.
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)
and this behavior is consistent on 2012 and 2014 both RTM
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.