Why does count(1/null) work but count(null/null) doesn't?

user_5567058 0
Viewed 78

I saw this example in a presentation lately. When you run a query like this:

SELECT COUNT(NULL)

You get the following error:

Operand data type NULL is invalid for count operator.

When you run this:

SELECT COUNT(1/NULL)

You get 0 as a result, even though the division itself yields NULL as a result. Same happens if you swap the 1 and NULL.

But if you do this:

SELECT COUNT(NULL/NULL)

You get the same error as the first query again (the division itself is legit, yields NULL).

Can anyone explain how sql server works to give these kind of results?

null tsql sql-server
3 Answers

In SQL Server certain aggregate functions such as COUNT, MIN, MAX and functions such as DATEADD require a datatype. NULL, on its own, does not have one so this gives you an error:

SELECT COUNT(NULL) -- Operand data type NULL is invalid for count operator.

Likewise for:

SELECT COUNT(NULL/NULL)

For 1/NULL the datatype is INT and the value is NULL so this works:

SELECT COUNT(1/NULL) -- 0

Likewise for:

SELECT COUNT(CAST(NULL AS INT)) -- 0
user_870150

This is actually a compiler error. When you go to run the batch, the T-SQL is parsed, and an error on the statements SELECT COUNT(NULL); and SELECT COUNT(NULL/NULL); is drawn at that time, not at execution.

If you run the batch below you'll see this quite quickly:

PRINT 'testing 1'

SELECT COUNT(NULL); --Error at compile
GO

PRINT 'testing 2'

SELECT COUNT(1/NULL); --Runs
GO

PRINT 'testing 3'

SELECT COUNT(NULL/NULL);  --Error at compile
GO

PRINT 'testing 4'

SELECT COUNT(0/0); --Error at run time

Notice that the testing 1 and testing 3 statement never appear.

The value NULL, on it's own does not have a data type. An expression where all sides of the expression are NULL will have, effectively, an unknown data type.

If you were to give the NULL a datatype, the issue does not happen:

PRINT 'testing 5'

SELECT COUNT(CONVERT(int,NULL));
GO

PRINT 'testing 6'
DECLARE @N int;

SELECT COUNT(@N);
user_20299830

The issue is with the data type involved. If we run the following we can see NULL alone has no datatype:

SELECT SQL_VARIANT_PROPERTY(NULL,'BaseType')

Which corresponds to the error you are seeing.

We can force NULL to have a chosen data type using cast in which case the error is resolved:

SELECT COUNT(cast(NULL as int))
user_36331080
Related