Catching null warnings in aggregate functions in sql

Question

How does one use the debugger in sql 2008 / 2012 to catch null values in records?

See:

drop table abc

create table abc(
a  int
)
go 
insert into abc values(1)
insert into abc values(null)
insert into abc values(2)

select max(a) from abc

(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

Now this can be rectifed by doing:

SELECT max(isNull(a,0)) FROM abc

which is fine, until I come to to 200 line queries with several levels of nesting,and a result set of 2000 odd records. -- And then have no clue which column is throwing the warning.

How do I add conditional breakpoints ( or break on warning ) in the SQL debugger? ( if it is even possible )

Why do you need to catch the warnings? SQL will simply ignore the null values, which is usually the right thing to do. In your example, if your table contains `{-1, null, -2}`, `max(a)` will return `-1`, whereas `max(isnull(a, 0))` will return `0`. — Richard Deeming, Dec 19 '12 at 13:32
I would like know where / why the warning is being thrown, in order to double check my logic — Alex, Dec 19 '12 at 13:46

e-mre · Accepted Answer · 2012-12-31T07:59:37.797

Part 1: About aggregate warnings...
Considering your several levels nesting I am afraid there is no straightforward way of seeing which records trigger those warnings.

I think your best shot would be to remove each aggregate function, one at a time, from the SELECT part of the top-level statement and run query so you can see which aggregate is causing warnings at the top level (if any)

After that you should move on to nested queries and move each sub-query that feeds the top-level aggregates to a separate window and run it there, check for warnings. You should repeat this for additional levels of nesting to find out what actually causes the warnings.

You can employ the following method also.

Part 2:About conditional breakpoints...
For the sake of debugging, you move each of you nested tables out and put its data to a temp table. After that you check for null values in that temp table. You set a breakpoint in an IF statement. I believe this is the best thing close to a conditional breakpoint. (IF clause can be altered to build other conditions)

Here is a solid example,
Instead of this:

SELECT A.col1, A.col2, SUM(A.col3) as col3
FROM (SELECT X as col1, Y as col2, MAX(Z) as col3 
      FROM (SELECT A as X, B as Y, MIN(C) as Z
            FROM myTableC
           ) as myTableB
     ) as myTableA

do this:

SELECT A as X, B as Y, MIN(C) as Z
INTO #tempTableC
FROM myTableC

IF EXISTS (SELECT * 
           FROM #tempTableC
           WHERE A IS NULL ) BEGIN
     SELECT 'A' --- Breakpoint here
END


SELECT X as col1, Y as col2, MAX(Z) as col3
INTO #tempTableB
FROM #tempTableC

IF EXISTS (SELECT *  
           FROM #tempTableB
           WHERE X IS NULL ) BEGIN
     SELECT 'B' --- Breakpoint here
END

SELECT col1, col2, SUM(col3) as col3 
FROM #tempTableB as myTableA

score 2 · Answer 2 · answered Dec 19 '12 at 13:43

aggregate functions exclude null values by definition, so you can just write

select max (a) from abc

instead of

SELECT max(isNull(a,0)) FROM abc

unless all values of a in abc are null, in which the second query would return zero instead of null.

If you want to prevent null values being entered, use a not null constraint on the table column.

score 1 · Answer 3 · answered Dec 26 '12 at 17:06

You can turn off the warning by executing:

set ansi_warnings off

This is explained here. This works, at least on the systems I've tested it on, to remove the warning when aggregating on NULL values.

This supposedly has another effect on converting numeric overflows and divide by 0s to NULLs rather than an error. However, I still get errors for divide by 0 and arithmetic overflows.

As an aside, when using SQL Server Management Studio, one rarely sees this message. When the query is successful, the message is on the "Messages" tab. However, SSMS defaults to the "Results" tab and usually there is no reason to look at the messages (although the warning is there). You only see the warning automatically when there is an error in the query, and SSMS defaults to the messages tab.

score 0 · Answer 4 · answered Dec 19 '12 at 14:12

0

You'll have to write a second query to pull out the data that you're looking for.

SELECT * FROM abc WHERE a IS NULL

You can put that into an IF statement to write an error message, or log to a table. Other than that, you're out of luck. Sorry. : /

answered Dec 19 '12 at 14:12

Nick Vaccaro

5,428
6
38
60

score 0 · Answer 5 · answered Dec 27 '12 at 10:49

0

Rather You can ignore to have rows with null values

SELECT MAX(a) FROM abc WHERE a IS NOT NULL

answered Dec 27 '12 at 10:49

Sandip Bantawa

2,822
4
31
47

Catching null warnings in aggregate functions in sql

5 Answers5