One plan fails with an error. Simple statement change produces a plan that doesn't
(database backup link removed)
Msg 515, Level 16, State 2, Procedure spGetFRSubRouteNamesForDate, Line 122 Cannot insert the value NULL into column 'DefinitionEndDateTime', table 'tempdb.dbo.#sr_start_stops_____________________________________________________________________________________________________00000000005C'; column does not allow nulls. UPDATE fails.
i have forced a parallel and non-parallel version of the plans and that doesn't change things. The broken one is always broken and the fixed one is always fixed. The actual parallel plan is different from the estimated plan for the changed SQL. I can't get the actual plan for the broken one since it isn't returned when a query errors.
OPTION (RECOMPILE, QUERYTRACEON 8649) OPTION (MAXDOP 1)
Check the part of the plan that updates #sr_start_stops.
The difference is the following part of the predicate on the update of DefinitionEndDateTime. If i comment this line out, the same statement completes without error.
AND tp.effectivestartdate >= DATEADD(day, -1, @crediteddate)
UPDATE #sr_start_stops SET DefinitionStartDateTime = CASE WHEN ( SELECT DATEADD(SECOND, MIN(te.DefinitionTripStopTimeSeconds), #sr_start_stops.CalendarDate) FROM dbo.t_FRTripsPropertiesFRRouteStopExceptions te INNER JOIN dbo.t_FRTripsPropertiesFRRouteStop ts ON te.FRTripsPropertiesFRRouteStopID = ts.FRTripsPropertiesFRRouteStopID INNER JOIN dbo.t_FRTripsProperties tp ON tp.FRTripsPropertiesID = ts.FRTripsPropertiesID INNER JOIN dbo.t_FRTrips t ON t.FRTripsID = tp.FRTripsID WHERE te.ExceptionDate = #sr_start_stops.CalendarDate AND t.FRSubrouteID = #sr_start_stops.FRSubRouteID AND te.ExceptionDate BETWEEN DATEADD(day, -1, @CreditedDate) AND DATEADD(day, 1, @CreditedDate) ) < #sr_start_stops.DefinitionStartDateTime THEN ( SELECT DATEADD(SECOND, MIN(te.DefinitionTripStopTimeSeconds), #sr_start_stops.CalendarDate) FROM dbo.t_FRTripsPropertiesFRRouteStopExceptions te INNER JOIN dbo.t_FRTripsPropertiesFRRouteStop ts ON te.FRTripsPropertiesFRRouteStopID = ts.FRTripsPropertiesFRRouteStopID INNER JOIN dbo.t_FRTripsProperties tp ON tp.FRTripsPropertiesID = ts.FRTripsPropertiesID INNER JOIN dbo.t_FRTrips t ON t.FRTripsID = tp.FRTripsID WHERE te.ExceptionDate = #sr_start_stops.CalendarDate AND t.FRSubrouteID = #sr_start_stops.FRSubRouteID AND te.ExceptionDate BETWEEN DATEADD(day, -1, @CreditedDate) AND DATEADD(day, 1, @CreditedDate) ) ELSE #sr_start_stops.DefinitionStartDateTime END , DefinitionEndDateTime = CASE WHEN ( SELECT DATEADD(SECOND, MAX(te.DefinitionTripStopTimeSeconds), #sr_start_stops.CalendarDate) FROM dbo.t_FRTripsPropertiesFRRouteStopExceptions te INNER JOIN dbo.t_FRTripsPropertiesFRRouteStop ts ON te.FRTripsPropertiesFRRouteStopID = ts.FRTripsPropertiesFRRouteStopID INNER JOIN dbo.t_FRTripsProperties tp ON tp.FRTripsPropertiesID = ts.FRTripsPropertiesID INNER JOIN dbo.t_FRTrips t ON t.FRTripsID = tp.FRTripsID WHERE te.ExceptionDate = #sr_start_stops.CalendarDate AND t.FRSubrouteID = #sr_start_stops.FRSubRouteID AND te.ExceptionDate BETWEEN DATEADD(day, -1, @CreditedDate) AND DATEADD(day, 1, @CreditedDate) ) > #sr_start_stops.DefinitionEndDateTime THEN ( SELECT DATEADD(SECOND, MAX(te.DefinitionTripStopTimeSeconds), #sr_start_stops.CalendarDate) FROM dbo.t_FRTripsPropertiesFRRouteStopExceptions te INNER JOIN dbo.t_FRTripsPropertiesFRRouteStop ts ON te.FRTripsPropertiesFRRouteStopID = ts.FRTripsPropertiesFRRouteStopID INNER JOIN dbo.t_FRTripsProperties tp ON tp.FRTripsPropertiesID = ts.FRTripsPropertiesID --if i comment out this line, the error will not happen. AND tp.effectivestartdate >= DATEADD(day, -1, @crediteddate) INNER JOIN dbo.t_FRTrips t ON t.FRTripsID = tp.FRTripsID WHERE te.ExceptionDate = #sr_start_stops.CalendarDate AND t.FRSubrouteID = #sr_start_stops.FRSubRouteID AND te.ExceptionDate BETWEEN DATEADD(day, -1, @CreditedDate) AND DATEADD(day, 1, @CreditedDate) ) ELSE #sr_start_stops.DefinitionEndDateTime END
Given the same data and the same parameters, the existence of the line in my initial message is the difference in the error.
(man that sounds cocky)
i will try this again
exec spGetFRSubRouteNamesForDate ':V:^R8DataAccess#1.6^:Z:^&NRMBus#&SL#&UID,#&AGN#&DFCe#^','20131219 00:00:00',1375,NULL
in the broken version of the proc it produces the null error. In the fixed version, which is the same except for removing
AND tp.effectivestartdate >= DATEADD(day, -1, @crediteddate)
, why does this one line change the output?
To reproduce – except the backup is the 'fixed' version:
exec spGetFRSubRouteNamesForDate ':V:^R8DataAccess#1.6^:Z:^&N&#RMBus#&S&#L#&UID,#&AGN#&DFCe#^','20131219 00:00:00',1375,NULL
That will produce 3 result sets. The temp table before the UPDATE and then after. And finally the results headed to the caller.
To create the error, uncomment line 191.
DefinitionEndDateTime DATETIME NOT NULL
add the NOT
OK. i found it. Yes it me being dumb. My gut said i did it wrong but i couldn't see it and vainly wanted it to be SQL's fault.
Thanks guys for forcing me to keep looking.
The correct predicate is:
AND tp.effectiveenddate >= DATEADD(day, -1, @crediteddate)
;WITH x(cd,sr,dmin,dmax) AS ( SELECT tt.CalendarDate, tt.FRSubRouteID, DATEADD(SECOND, MIN(te.DefinitionTripStopTimeSeconds), tt.CalendarDate), DATEADD(SECOND, MAX(te.DefinitionTripStopTimeSeconds), tt.CalendarDate) FROM dbo.t_FRTripsPropertiesFRRouteStopExceptions te INNER JOIN dbo.t_FRTripsPropertiesFRRouteStop ts ON te.FRTripsPropertiesFRRouteStopID = ts.FRTripsPropertiesFRRouteStopID INNER JOIN dbo.t_FRTripsProperties tp ON tp.FRTripsPropertiesID = ts.FRTripsPropertiesID INNER JOIN dbo.t_FRTrips t ON t.FRTripsID = tp.FRTripsID INNER JOIN #sr_start_stops AS tt ON te.ExceptionDate = tt.CalendarDate AND t.FRSubrouteID = tt.FRSubRouteID WHERE te.ExceptionDate BETWEEN DATEADD(day, -1, @CreditedDate) AND DATEADD(day, 1, @CreditedDate) AND tp.effectivestartdate >= DATEADD(day, -1, @crediteddate) GROUP BY tt.CalendarDate, tt.FRSubRouteID ) UPDATE sr SET DefinitionStartDateTime = CASE WHEN x.dmin < sr.DefinitionStartDateTime THEN x.dmin ELSE sr.DefinitionStartDateTime END, DefinitionEndDateTime = CASE WHEN x.dmax > sr.DefinitionEndDateTime THEN x.dmax ELSE sr.DefinitionEndDateTime END FROM #sr_start_stops AS sr INNER JOIN x ON sr.CalendarDate = x.cd AND sr.FRSubRouteID = x.sr;