Operand data type NULL is invalid for max operator

Question

This code works ok:

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;

output:

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

with:

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;

give error:

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

In both cases sql server calculate max from null and null? Isn't it?

Answer 1

In the first case you implicitly specify a datatype, namely an integer. This is inferred from the then which will never be reached. The fact that the then will not be executed, doesn't matter for sql server. In fact, the way sql server determines the return type: "the highest precedence type from the set of types in result_expressions and the optional else_result_expression". So the return type is chosen before actual execution, from all potential datatypes to be returned in the then and else. in other words, before sql server 'realises' that some statements cannot possibly be reached.

Since the datatype is known, max can be applied.

In the second case you don't specify a datatype, so sql server can not know how to implement the max. A max for varchar is different than a max for integers.

Answer 2

That is neat. In my opinion it probably should be implicitly converted to int.

The docs mention this for inserts:

the values specified in a multi-row insert statement follow the data type conversion properties of the union all syntax. - Table Value Constructor (Transact-SQL)

---

Both of these turn the null value to an int:

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')

rextester demo: http://rextester.com/VWMT9189

returns

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