Operand data type void type is invalid for sum operator

Question

I would have a question about the use of null.

If I run this query

SELECT SUM(x)
FROM (SELECT NULL AS x) AS DerivedTable

I get the error message

"Operand data type void type is invalid for sum operator."

However, if I create a table with a nullable field and use it within the sum aggregate, it will ignore the NULL value and basically add up all the rest of the records.

CREATE TABLE #temp1(x int)
insert into #temp values (NULL),(3),(5)

SELECT SUM(x) FROM #temp1 

Result is 8.

Is there a logical explanation of this one? Would it be because of the data type of NULL has not been declared on the query with the derived table but was declared with the temp table? Also, I would really like to know if there is a way to produce this error message when run from a table or a derived table, just like the second #temp1 example. Any example which produces this error message when run from a table or a table expression is highly welcome.

Thank you all

Answer 1

This is indeed because the type of the column cannot be inferred in your derived table scenario.

If you change the statement and explicitly use a type on the column, the select will work.

SELECT SUM(x) FROM (SELECT CAST(NULL AS INTEGER) AS x) AS DerivedTable

as will the following statement where the type can be inferred

SELECT SUM(x) FROM (SELECT NULL AS x UNION SELECT 1) AS DerivedTable

SQL Fiddle

---

This would also answer your second question. As you can not create a table without declaring the types, there's no way to produce a statement that would generate that specific error message.