CODEWITHSUNDEEP
sql-serversql-server-2019
Seva Alekseyev
Seva Alekseyev

Reputation: 61388

COUNT(*) can't be a NULL, can it?

Directly related to this.

There is a SQL SELECT where one of the columns is a subquery with a COUNT(*):

select 
    wee, hee, 
    (select count(*) from bar where bar.wee = foo.wee) as n
from 
    foo

When I inspect the datatypes of the recordset using DataReader.GetSchemaTable(), the n column comes across as a nullable int.

Is it ever possible that a query along the lines of select count(*) from ... returns a null? Testing suggests not. Even on an empty table it returns a zero.

Looks like a bug in the datatype inference facility of SQL Server, but maybe I'm missing something.

FYI, surrounding the subquery with isnull((select ...), 0) as n makes the reported datatype of n a nonnullable int, as intended.

Upvotes: 0

Views: 95

Answers (0)

Related Questions