Why am I getting this ANSI warning about NULL values and aggregate functions?

This is the exact warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

I understand what it means in general, i.e. that there's a null value in one or more rows for a column to which an aggregate function is applied, but I can't quite figure out why it's happening for my particular query or how to rewrite it such that I don't get the warning.

This is the query. The table tracks unit responses to an incident, and the purpose of the query is to find the last unit to arrive that was among the first units to be notified of the incident. Notification times may vary, and not all units will arrive.

SELECT inci_no
    ,unit
    ,notif_dttm
    ,arv_dttm
    ,resp_code
    ,alm_date
FROM (
    SELECT inci_no
        ,alm_date
        ,unit
        ,notif_dttm
        ,arv_dttm
        ,resp_code
        ,row_num = row_number() OVER (PARTITION BY inci_no ORDER BY arv_dttm DESC, unit_id ASC)
    FROM inc_unit AS u
    WHERE resp_code = 'E'
        AND arv_dttm IS NOT NULL
        AND notif_dttm = '2016-01-01'

The warning started appearing when I corrected a logical error that was returning incorrect results. That's the commented line above. When it's removed, I get the warning, but when it's included I do not get the warning.

At first I thought it was tied to the window function, but now I think it's more likely to be the min(notif_dttm) in the innermost sub-query. I think the line I removed from the sub-query just happened to be filtering out rows w/ NULL in the notif_dttm column. Logically, I shouldn't get any such rows because of the date filter on the last line.

The worst part is that I can't reproduce the problem on a smaller data set. I scripted out a simplified schema and some sample data to post with this question, but I can't reproduce the error, which makes me think I don't really understand the root of the problem.

I'm familiar with logical processing order for queries (https://msdn.microsoft.com/en

From what I can see you understand the problem perfectly. It sounds like min(notif_dttm) is causing you the grief. You can check this pretty easily by running code like this:

SELECT *
FROM inc_unit AS notif
WHERE notif_dttm IS NULL
  AND EXISTS (SELECT 1 FROM inc_unit u
                WHERE notif.inci_no = u.inci_no
                  AND resp_code = 'E'
                  AND arv_dttm IS NOT NULL)

If you get any rows then that's probably your problem.

Here is some easy sample code that will reproduce the warning you are getting.

CREATE TABLE #test (nullableCol1 int)
INSERT INTO #test VALUES (NULL),(1),(2)
SELECT MIN(nullableCol1) FROM #test

As far as your problem goes there is a property in SSIS that tells it how many errors are acceptable. It's called MaximumErrorCount. Increase that for that object and each of it's containers. That way you can have the warning (which isn't really a big deal) and still have your code move on correctly.

Edit Additional order by information you wanted can be found here.

But basically SQL processes a query in this order:

• FROM

• ON

• OUTER

• WHERE

• GROUP BY

• CUBE | ROLLUP

• HAVING

• SELECT

• DISTINCT

• ORDER BY

• TOP

So if I take your subquery and place it in the order it's processed it would look like this

FROM inc_unit AS notif                  -- Find the table
        WHERE notif.inci_no = u.inci_no         -- Restrict the rows used
            --AND notif.arv_dttm IS NOT NULL    -- 
        GROUP BY notif.inci_no                  -- Group up what's left
        SELECT min(notif_dttm)                  -- Process any aggregates

You can see it's a pretty logical progression. We can't GROUP BY until we remove the appropriate rows using the WHERE clause. And then we can't check the MIN until we have finished grouping everything together.

StackExchange Database Administrators Q#138375, answer score: 5