NULL operator type not allowed for max operator

Question

This code works fine:

select fk, max(case when 1 = 0 then 1 else null end) maxx from (values(1, null), (1, null)) x(fk, a) group by fk; 

exit:

 fk maxx ----------- ----------- 1 NULL 

from:

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

But this code:

 select fk, max(a) maxx from (values(1, null), (1, null)) x(fk, a) group by fk; 

enter the error:

Msg 8117, Level 16, State 1, Line 5 Operand data type NULL is invalid for max operator.

In both cases, sql server compute max from null and null ? Is not it?

Answer 1

In the first case, you implicitly specify the data type, namely an integer. It is derived from then , which will never be achieved. The fact that then will not then does not matter for the sql server. In fact , the way sql server determines the type of the return value is: "the highest precedence from the set of types in result_expressions and optional else_result_expression". Thus, the return type is selected before actual execution from all potential data types that must be returned in then and else . in other words, before the sql server “realizes” that some statements cannot be reached.

Since the data type is known, max .

In the second case, you do not specify the data type, so the sql server cannot know how to implement max . A max for varchar is different than max for integers.

Answer 2

This is neat. In my opinion, this should probably be implicitly converted to int .

The docs mention this for inserts :

the values ​​specified in the multi-line insert follow the conversion properties of the union all syntax data type. - Constructor of table values ​​(Transact-SQL)

---

Both of these parameters turn null into an int value:

 select null as MyNullCol into dbo.tmp union all select null as MyNullCol select fk, a into dbo.tmp2 from (values(1, null), (1, null)) x(fk, a) select c.name, t.name from sys.columns c inner join sys.types t on c.user_type_id = t.user_type_id where c.name in ('a','mynullcol') 

registry: http://rextester.com/VWMT9189

returns

 +-----------+------+ | name | name | +-----------+------+ | MyNullCol | int | | a | int | +-----------+------+