One plan fails with an error. Simple statement change produces a plan that doesn't

Dan Holmes 2014-01-06 15:48:54

There is one statement in the procedure that produces an error – not null violation in the table. I have changed the query very slighty and in fact it was an index optimization that i removed. When i have done this the error goes away. It feels brittle (and a possible SQL bug) so i am a bit hesitant to release this because something else might show up.

(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
Aaron Bertrand 2014-01-06 18:39:46
This isn't an answer, but it affected the answer I was working on. Can you explain why that AND condition belongs only in the THEN portion?
SQLkiwi 2014-01-06 18:44:08
I'll second that. There are a number of things to say about this query (and suggestions to improve it), but the first question is why the AND tp.effectivestartdate >= DATEADD(day, -1, @crediteddate) predicate does not appear in the WHEN part. I'll bet fixing that removes the 'problem'.
Dan Holmes 2014-01-06 19:04:58
It should have been in all four (start seconds too). That changed the plan for the better and solved the problem. It doesn't address the original question though.
Aaron Bertrand 2014-01-06 19:16:59
So you're saying that even when the WHEN and THEN subqueries match exactly, you still get the error?
Dan Holmes 2014-01-06 19:23:49
yes. Even when there isn't anything that would cause the table to update. I am querying for 20131219. There is only data in the exception table for 12/18. The data in Exception table is all non-null so it isn't possible for the output to be NULL. On the 18th there are two rows that should be replaced.

Given the same data and the same parameters, the existence of the line in my initial message is the difference in the error.

Aaron Bertrand 2014-01-06 19:27:06
So in the case where you are getting an error, change the definition of the #temp table to allow NULLs in that column. Does the query work as expected? And do you get rows with NULLs?
Dan Holmes 2014-01-06 19:29:18
yes that is what happens.
Aaron Bertrand 2014-01-06 19:32:03
So if you get rows with NULLs, this conflicts with your earlier statement that NULLs are not possible. So is there an error in your query logic (or your understanding of the underlying data), or are you suggesting there is a bug in SQL Server that is producing NULLs when it shouldn't? Can you provide a full repro?
Dan Holmes 2014-01-06 19:36:15
I am suggesting the latter. Something about this plan is producing an errant NULL. Feels difficult to believe but based on this single change and dumping intermediate results from the proc, i don't know what else it could be.
(man that sounds cocky)
i will try this again
SQLkiwi 2014-01-06 19:43:18
Sorry Dan I accidentally removed the wrong comment chain when removing my redundant comment – humble apologies. Anyway, the question is a bit confusing to me now – any chance you could tidy it up with a clear explanation of what works and what doesn't?
Aaron Bertrand 2014-01-06 19:43:31
Looking into what happened to the comment chain here.
Dan Holmes 2014-01-06 19:52:05
i have the following execution string:

exec spGetFRSubRouteNamesForDate ':V:^R8DataAccess#1.6^:Z:^&N&#RMBus#&S&#L#&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?

Aaron Bertrand 2014-01-06 19:55:55
The condition must change the output, but since we don't have access to the structure, the data, the expected output, etc. it's hard for us to tell you why.
SQLkiwi 2014-01-06 20:01:00
Thanks, but it's still not clear which version of the query you are talking about. In the listing in the question, having different text in the WHEN and THEN clauses could conceivably generate a NULL. If you are referring to a 'fixed' version where the clauses contained identical subqueries, I'd need to look more deeply into it. Please make the question clear for those of us that do not have access to your schema or data.
Dan Holmes 2014-01-06 20:21:13
i have added a reduced size database that produces this problem.

To reproduce – except the backup is the 'fixed' version:
exec spGetFRSubRouteNamesForDate ':V:^R8DataAccess#1.6^:Z:^&N&#RMBus#&S&#L#&UID&#44#&AGN&#1#&DFC&#101#^','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.

Dan Holmes 2014-01-06 20:28:03
oops, left off the change to line 111:
DefinitionEndDateTime DATETIME NOT NULL
add the NOT
Aaron Bertrand 2014-01-06 20:29:36
I haven't looked yet, but if you get the error by changing only one line, doesn't that imply that the WHEN and THEN don't match, which – as we've both already suggested – could very well legitimately lead to NULLs?
Dan Holmes 2014-01-06 20:41:02
Aaaarrrrrrggggggghhhhhhhh (he wouldn't have bothered to carve….except that i did)!!!

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)
Aaron Bertrand 2014-01-06 22:09:07
Glad you solved it. Hope you have the bandwidth to make the query more concise…
Aaron Bertrand 2014-01-06 19:09:05
If the error is occurring and it's not possible for a NULL to be occurring in this output, I still think this would only happen if the conditions don't match. But even if there is a bug and SQL Server is determining in advance that a NULL could come out of it, and is wrong, you can simplify this query greatly as follows:

;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;
SQLkiwi 2014-01-06 19:13:52
This certainly makes the query easier to read and understand, while also making it less prone to typing errors. The expressions in the CTE will still be evaluated multiple times, of course.
Aaron Bertrand 2014-01-06 19:20:37
Yes, true, I certainly didn't mean to imply otherwise. Of course to avoid that you could dump the results of the aggregation into a separate #temp table.
Dan Holmes 2014-01-06 19:19:57
This does work and is easier to read but is more costly than the confusing version. 43k read vs. 302k reads. Duration/CPU is 53/47 vs 135/140 ms
Aaron Bertrand 2014-01-06 19:21:40
I'm sure there are more opportunities for tuning here, but initially you need to focus on either having a correct query or having a fast query. I know which I prefer (and I was merely trying to eliminate potential problems with mismatched predicates). 🙂 If the performance is still an issue, even though the query now works, you could try dumping the aggregation from the CTE query into a #temp table first, and UPDATE using that in a join.
SQLkiwi 2014-01-06 19:24:40
Making a habit of agreeing with Aaron here – but I do. Persisting the updates to a temporary table before applying them in a separate step will help avoid/track down logic errors, and may even introduce optimization opportunities e.g. avoiding the redundant updates.